Lightweight open source Embedded relational database SQLite basic use

< One, >1, install for centos/reahat;

Yum-y Install SQLite sqlite-devel;

2,sqlite C/c++api Interface, core object

OCI interface (Oracle call Interface)
2_1, core objects :d atabase_connection and prepared_statement;

The Database_connection object is created and returned by the Sqlite3_open () interface function, which must be called before the application can use any other SQLite interface function to obtain the Database_connection object. In subsequent APIs calls, you need to database_connection the object as an input parameter to do the work, prepared_statement it as a compiled SQL statement, All SQL statement execution related functions require the object to be changed as an input parameter to complete the specified SQL operation.

2_2, Core interface 01)

Sqlite3_open operates the entry function of the SQLite database, the Database_connection object returned by this function is a handle parameter for many other SQLite APIs, which allows us to open the existing database file. You can also create a new database file that returns the Database_connection object, and we can share pointers to that object across multiple threads to complete any database-related operations and create multiple database connection objects for accessing multiple databases. Because the attach command that comes with SQLite can easily access multiple databases in one connection;

2), Sqlite3_prepar

The function converts the SQL text to a Prepared_statement object, and Bin returns the object pointer after the function executes, and the function does not evaluate the parameter to specify the SQL statement, it simply initializes the SQL text to the state to be executed, SQLITE3_PREPARE_V2 equivalent;

3), Sqlite_step

The function is used to evaluate the Prepared_statement object returned by the Sqlite3_prepare function, and after the function is executed, the inner pointer of the Prepared_statement object points to the first row of the result set it returns, and the data row after the iteration. It is necessary to call this function continuously until the data rows are traversed, and for DML statements such as insert,update and delete, the sqlite_step can be completed once;

4), Sqlite3_column _ ....

function to get the data for the specified column of the current row.

Sqlite3_column_blob,bytes,bytes16,double,int,int64,text,text16,type,value,count;

Where the Sqlite3_column_count function is used to get the field data in the current result set

Case pseudo Code,

Iterate over each row of data in the result set using the Sqlite3_step and Sqlite_column functions

int fieldcount = Sqlite3_column_count ();

while (Sqlite3_step () <>eof)

{

for (int i = 0;i < fieldcount;i++)

{

int v = sqlite3_column_int ();

}

}

5), Sqlite3_finalize

function is used to destroy the Prepared_statement object, otherwise it will cause a memory leak

6), Sqlite3_close

The Database_connection function is used to close the previously opened object, where all Prepared_statement objects associated with the object must be destroyed before this;

3, parameter variable binding;

SQLite's SQL text also supports variable binding to reduce the number of times SQL statements are parsed dynamically, to improve data query and operation efficiency, to complete this operation, but also need to provide 2 additional interfaces SQLite, Sqlite3_reset and Sqlite3_bind;

1 voidtest_parameter_binding () {2 //1. Inserting more than one data without parameter binding3 Charstrsql[ -];4  for(inti =0; I < max_rows;++i)5 {6sprintf (strSQL,"INSERT into testtable values (%d)", i);7 sqlite3_prepare_v2 (..., strSQL);8 Sqlite3_step (prepared_statement);9 sqlite3_finalize (prepared_statement);Ten } One //2, inserting multiple data in the case of parameter binding A stringStrsqlwithparameter ="INSERT into testtable values (?)"; - sqlite3_prepare_v2 (..., strSQL); -  for(inti =0; I < max_rows;++i) the { - Sqlite3_bind (); - sqlite3_step (); - Sqlite3_reset (); + } - sqlite3_finalize (); +  A}

< two,> database, table CLI statement;

Creates a table in the specified database;

With attach database file path, can exist or create a new ' as database name, the same as the previous database file name;

When you create a table, you still need to specify the name of the database, or the SQL-created data table will be created into the database of our sqlite3 database name;

Simple database Backup Recovery