Use SQLite to Execute standard SQL syntax

1. Execute SQL statements

Int sqlite3_exec (sqlite3 *, const char * SQL, sqlite3_callback, void *, char ** errmsg)
This is the function used to execute an SQL statement.
The first parameter is a pointer obtained by the open function. It is the key data structure.
The 2nd const char * SQL is an SQL statement ending with/0.
The 3rd sqlite3_callback parameter is a callback. After this statement is executed, sqlite3 will call the function you provided.
The third parameter void * is the pointer you provide. You can pass any pointer parameter here. This parameter will eventually be passed to the callback function. This pointer is important, it can be used to pass parameters. If you do not need to pass a pointer to the callback function, you can enter null. Let's take a look at the method of the callback function and the use of this parameter.
The
The five parameters char ** errmsg are error messages. Note the pointer. Sqlite3 contains many fixed error messages. Execute sqlite3_exec
When the execution fails, you can view the pointer (directly
Printf ("% s/n", errmsg) gets a string that tells you where the error is. The sqlite3_exec function modifies the pointer
Point the pointer you provided to the error message, so that the sqlite3_exec function can use this char * to get a specific error message.
Note: normally,
Sqlite3_callback and void * after sqlite3_callback can both be null. If this parameter is set to null, callback is not required. For example, if you do insert
Operation, do delete operation, there is no need to use callback. When you do select, you need to use the callback, because sqlite3
Check the data and use the callback to tell you what data has been found.

2. Exec callback

Typedef int (* sqlite3_callback) (void *, Int, char **, char **);
Your callback function must be defined as the type of the above function. The following is a simple example:
// Each time SQLite finds a record, this callback is called once.
Int loadmyinfo (void * para, int n_column, char ** column_value, char ** column_name)
{
// Para is the void * parameter you pass in sqlite3_exec
// Using the Para parameter, you can pass in some special pointers (such as class pointers and structure pointers), and then forcibly convert them to the corresponding type (here it is the void * type, must be forcibly converted to your type ). Then operate on the data
// N_column indicates the number of fields in the record (that is, the number of columns in the record)
// Char ** column_value is a key value. The retrieved data is saved here. It is actually a one-dimensional array (not a two-dimensional array ), each element is a char * value and a field content (expressed as a string and ended with/0)
// Char ** column_name corresponds to column_value, indicating the field name of this field
// Here, I do not use the Para parameter. Ignore its existence.
Int I;
Printf ("The record contains % d fields/N", n_column );
For (I = 0; I <n_column; I ++)
{
Printf ("field name: % s region> Field Value: % s/n", column_name [I], column_value [I]);
}
Printf ("------------------/N");
Return 0;
}

Int main (INT, char **)
{
Sqlite3 * dB;
Int result;
Char * errmsg = NULL;
Result = sqlite3_open ("C: // dcg_database.db", & dB );
If (result! = Sqlite_ OK)
{
// Failed to open the database
Return-1;
}
// Database operation code
// Create a test table named mytable_1 with two fields: ID and name. ID is an automatically added type. This field can not be specified during future insert operations. It will increase from 0.

Result = sqlite3_exec (dB, "create table mytable_1 (ID integer primary
Key autoincrement, name nvarchar (32) ", null, null, & errmsg );
If (result! = Sqlite_ OK)
{
Printf ("failed to create table, error code: % d, error cause: % s/n", result, errmsg );
}
// Insert some records
Result = sqlite3_exec (dB, "insert into mytable_1 (name) values ('Walk ')", 0, 0, & errmsg );

If (result! = Sqlite_ OK)
{
Printf ("Insert record failed, error code: % d, error cause: % s/n", result, errmsg );
}

Result = sqlite3_exec (dB, "insert into mytable_1 (name) values ('bike jacking')", 0, 0, & errmsg );
If (result! = Sqlite_ OK)
{
Printf ("Insert record failed, error code: % d, error cause: % s/n", result, errmsg );
}

Result = sqlite3_exec (dB, "insert into mytable_1 (name) values ('chelay')", 0, 0, & errmsg );
If (result! = Sqlite_ OK)
{
Printf ("Insert record failed, error code: % d, error cause: % s/n", result, errmsg );
}

// Start querying the database
Result = sqlite3_exec (dB, "select * From mytable_1", loadmyinfo, null, & errmsg );

// Close the database
Sqlite3_close (db );
Return 0;
}

The above example shows how to open a database and perform basic database operations.
With this knowledge, you can basically cope with a lot of database operations.

3. Do not use callback to query the database

The sqlite3_exec described above uses callback to execute select
Operation. There is also a way to directly query without callback. However, I personally think the callback is good, because the code can be more neat, but it is very troublesome to use the callback. You have to declare a function. If this function
Is a class member function, you have to declare it as static
(Why? This is the basis of C ++. The C ++ member function actually hides a parameter: This, when C ++ calls a class member function, it implicitly treats the class pointer as the first parameter of the function.
Passed in. The result is inconsistent with the parameters of the SQLite callback function described above. Only when the member function is declared as static
).
Although the callback code looks neat, sometimes you still want select queries that are not called back. This can be done through the sqlite3_get_table function.
Int sqlite3_get_table (sqlite3 *, const char * SQL, char *** resultp, int * nrow, int * ncolumn, char ** errmsg );
The 1st parameters are no longer mentioned. refer to the previous example.
The 2nd parameters are SQL statements, which are the same as SQL statements in sqlite3_exec. It is a common char * string ending with/0.
The first parameter is the query result, and it is still a one-dimensional array (do not think it is a two-dimensional array, or a three-dimensional array ). Its memory layout is: the first line is the field name, followed by the value of each field. The following is an example.
The 4th parameter indicates how many records are queried (that is, how many rows are queried ).
The number of fields (columns) of the 5th parameter ).
The first parameter is the error message, which is the same as above.
The following is a simple example:

Int main (INT, char **)
{
Sqlite3 * dB;
Int result;
Char * errmsg = NULL;
Char ** dbresult; // It is of the char ** type and has two * numbers.
Int nrow, ncolumn;
Int I, J;
Int index;

Result = sqlite3_open ("C: // dcg_database.db", & dB );

If (result! = Sqlite_ OK)
{
// Failed to open the database
Return-1;
}

// Database operation code
// Assume that the mytable_1 table has been created.
// Start the query. The passed dbresult is already char **. Here, an & get address character is added, and the passed result is Char ***
Result = sqlite3_get_table (dB, "select * From mytable_1", & dbresult, & nrow, & ncolumn, & errmsg );
If (sqlite_ OK = Result)
{
// Query successful
Index = ncolumn; // the data in the first row before dbresult is the field name, which is the real data starting from the ncolumn index.
Printf ("% d records/N", nrow );

For (I = 0; I <nrow; I ++)
{
Printf ("% d records/N", I + 1 );
For (j = 0; j <ncolumn; j ++)
{
Printf ("field name: % s success> Field Value: % s/n", dbresult [J], dbresult [Index]);
+ Index; // The Field Value of dbresult is continuous. From The 0th index to the ncolumn-1 index, the field name starts from the ncolumn index, followed by the field value, it uses a flat form to represent a two-dimensional table (traditional row-column representation ).
}
Printf ("-------/N ");
}
}

// Here, the char query result is released no matter whether the database query is successful or not, and the function provided by SQLite is used for release.
Sqlite3_free_table (dbresult );

// Close the database
Sqlite3_close (db );
Return 0;
}

Until this example, the common usage of sqlite3 has been described.
With the above method, coupled with SQL statements, can fully meet the needs of the vast majority of databases.
However, in one case, the preceding method cannot be used: insert and Select Binary are required. The preceding method cannot be used to process binary data. The following section describes how to insert binary data.
(2) binary operation

SQLite requires an auxiliary data type to operate binary data: sqlite3_stmt *.
This
Each data type records an "SQL statement ". Why do I use double quotation marks for "SQL statements? Because you can regard the content represented by sqlite3_stmt *
SQL statement, but it is not actually a well-known SQL statement. It is an internal data structure that has resolved SQL statements and marked records by SQLite itself.
Because this structure has been parsed, you can insert binary data into this statement. Of course, inserting binary data into the sqlite3_stmt structure cannot directly use memcpy or use a + number like STD: string. The function provided by SQLite must be used for insertion.
I .1 write binary data

The following describes the steps for writing binary data.
To insert binary data, make sure that the field type of this table is blob. I suppose there is such a table:
Create Table tbl_2 (ID integer, file_content BLOB)
First declare
Sqlite3_stmt * Stat;
Then, resolve an SQL statement to the stat structure:
Sqlite3_prepare (dB, "insert into tbl_2 (ID, file_content) values (10 ,? ) ",-1, & stat, 0 );
The above function completes SQL statement parsing. The first parameter, like the previous one, is a sqlite3 * type variable, and the second parameter is an SQL statement.
What is special about this SQL statement is that there is? . In the sqlite3_prepare function ,? Number indicates an undefined value, which is inserted only when its value is equal.
The third parameter is-1, which indicates the length of the preceding SQL statement. If it is less than 0, SQLite automatically calculates the length of the statement (the SQL statement is treated as a string ending with/0 ).
The fourth parameter is the pointer of sqlite3_stmt. The parsed SQL statement is placed in this structure.
I do not know what the fifth parameter is. It can be set to 0.
If this function is successfully executed (sqlite_ OK is returned and stat is not null), you can start to insert binary data.
Sqlite3_bind_blob (stat, 1, pdata, (INT) (length_of_data_in_bytes), null); // pdata is the data buffer, length_of_data_in_bytes is the data size, in bytes
This function has a total of five parameters.
1st parameters: the sqlite3_stmt * type variable obtained by prepare.
The
Two parameters :? Number index. In the preceding prepare SQL statement, is there? Number. If there are multiple? How can I insert a number? The method is to change bind_blob.
Function 2nd parameters. This parameter is set to 1, indicating that the value inserted here must replace stat.
First? (Here, the index starts counting from 1, not from 0 ). If you have multiple? Number, write multiple bind_blob
And change their 2nd parameters to different ones? . If yes? No. SQLite is null.
3rd parameters: Start pointer of binary data.
4th parameters: the length of binary data, in bytes.
5th parameters: it is a callback function that tells SQLite to call this function after processing data to analyze your data. I have not used this parameter, so I cannot understand it deeply. However, it is generally null, and the memory to be released is released using code.
After the BIND is complete, the binary data enters your "SQL statement. Now you can save it to the database:
Int result = sqlite3_step (STAT );
With this statement, the SQL statement indicated by stat is written to the database.
Finally, release the sqlite3_stmt structure:
Sqlite3_finalize (STAT); // analyze the content just allocated

I .2 Read Binary
The following describes how to read binary data.
As before, declare the sqlite3_stmt * type variable first:
Sqlite3_stmt * Stat;
Then, resolve an SQL statement to the stat structure:
Sqlite3_prepare (dB, "select * From tbl_2",-1, & stat, 0 );
After the prepare is successful (the returned value is sqlite_ OK), query the data.
Int result = sqlite3_step (STAT );
The return value of this statement is sqlite_row, indicating success (not sqlite_ OK ).
You can run the sqlite3_step function cyclically to query a record at a time. If the returned value is not sqlite_row, the query is complete.
Then obtain the value of the first field: ID. ID is an integer. Use the following statement to obtain its value:
Int id = sqlite3_column_int (stat, 0); // The first field content is obtained. The value starts from 0 because the ID field of my table is the first field, so here I enter 0

Next we will get the value of file_content, because file_content is binary, so I need to get its pointer and its length:
Const void * pfilecontent = sqlite3_column_blob (stat, 1 );
Int Len = sqlite3_column_bytes (stat, 1 );
In this way, the binary value is obtained.
After saving the content of pfilecontent, do not forget to release the sqlite3_stmt structure:
Sqlite3_finalize (STAT); // analyze the content just allocated

I .3 reuse sqlite3_stmt Structure
If you need to repeat the sqlite3_stmt structure parsed by sqlite3_prepare, you need to use the function: sqlite3_reset.
Result = sqlite3_reset (STAT );
In this way, the stat structure becomes the status when sqlite3_prepare is completed, and you can re-bind the content for it.