Common functions of Sqlite Recommended

1. Open the database:
Note: When opening a database, the file name does not necessarily exist. If this file does not exist, sqlite will be created automatically. The first parameter refers to the file name, and the second parameter is the defined sqlite3 ** structure pointer (key data structure). You don't have to worry about the underlying details of this structure.
int sqlite3_open(
const char *filename, /* Database filename (UTF-8) */
sqlite3 **ppDb /* OUT: SQLite db handle */
);
Return value: indicates whether the operation is correct (SQLITE_OK operation is normal)

2. Close the database:
Note: If you open a database with sqlite3_open, don't forget to use this function to close the database at the end.
int sqlite3_close(sqlite3*); // The parameter is the structure just now, that is, the database handle

3. Execute SQL statements:
Explanation: The function of this function is to execute one or more SQL statements, separated by the ";" sign. It is recommended that when executing one or more SQL statements, specify the third parameter callback function. The detailed process of executing Sql can be obtained in the callback function. If all Sql executions are completed, 0 should be returned. Otherwise, it means that the execution has not been completely successful. The fifth parameter: If the execution fails (no 0 is returned), you can view the fifth exposition value. To view detailed error information.
int sqlite3_exec(
sqlite3*, /* The database handle that has been opened */
const char *sql, /* Sql statement to be executed */
sqlite_callback, /* callback function */
void *, /* Parameters passed to the callback function */
char **errmsg /* Save error message */
);
Usually, both sqlite3_callback and void* after it can be filled in NULL to indicate that no callback is needed. For example, if you do insert operation or delete operation, there is no need to use callbacks. When used as select, you need to use a callback, because when sqlite3 finds out the data, you have to tell you what data it found through the callback.

4. Exec callback
typedef int (*sqlite3_callback)(void*, int, char**, char**);
Note: Your callback function must be defined as the type of the above function.
For example:
int LoadMyInfo( void * para, int n_column, char ** column_value, char ** column_name )
{
//para is the void * parameter you passed in sqlite3_exec
// Through the para parameter, you can pass in some special pointers (such as class pointers and structure pointers), and then cast it to the corresponding type (the void* type here, and it must be cast to your type before it can be available). Then operate this data
//n_column is how many fields there are in this record (that is, how many columns there are in this record)
// char ** column_value is a key value, and the data found are stored here. It is actually a 1-dimensional array (don't think it is a 2-dimensional array). Each element is a char * value, which is a field content (denoted by a string, ending with \0)
//char ** column_name corresponds to column_value, indicating the field name of this field

5. Take the current insertion position:
Function: Return to the location you inserted the previous time, starting from 1, sqlite3* gets the handle you get when you open the database.
long long int sqlite3_last_insert_rowid(sqlite3*);
6. Non-callback select query:
Function: Execute a query Sql and return a record set.
int sqlite3_get_table(
sqlite3*, /* The database handle that has been opened */
const char *sql, /* Sql statement to be executed */
char ***resultp, /* Save pointer to return record set */
int *nrow, /* Returns the number of records (and finds out how many rows) */
int *ncolumn, /* Returns the number of fields (how many columns) */
char **errmsg /* Return error message */
)
Description: The third parameter is the query result, which is a one-dimensional array, and the memory layout is: the first line is the field name, followed by the value of each field.
Example:
int main( int , char ** )
{
sqlite3 * db;
int result;
char * errmsg = NULL;
char **dbResult;
int nRow, nColumn;
int i , j;
int index;
result = sqlite3_open( “c:\\Dcg_database.db”, &db );
if( result != SQLITE_OK )
{
return -1;
}
// Database operation code
// Assume that the MyTable_1 table has been created before
// Start querying. The passed dbResult is already char ** . Another & address character is added here, and the passed in becomes char ***
result = sqlite3_get_table( db, “select * from MyTable_1”, &dbResult, &nRow, &nColumn, &errmsg );
if( SQLITE_OK == result )
{
// The query is successful
index = nColumn; // As mentioned earlier, the first line of data in front of dbResult is the field name, and the real data is started from the nColumn index
printf( " %d record found \n", nRow );
for( i = 0; i < nRow ; i++ )
{
printf( "%d record \n", i+1);
for( j = 0 ; j < nColumn; j++ )
{
printf(" Field name:%s ?> Field value:%s\n", dbResult[j], dbResult [index] );
++index; // The field values ​​of dbResult are continuous, from the 0th index to the nColumn-1th index are all field names, starting from the nColumn-th index, followed by field values. It represents a two-dimensional table (traditional row and column notation) in a flat form.
}
printf( “-------\n” );
}
}
// At this point, regardless of whether the database query is successful or not, the char** query result will be released, and the functions provided by sqlite are used to release it.
sqlite3_free_table( dbResult );
// Close the database
sqlite3_close( db );
return 0;
}

7. Release the query result:
Function: Release the memory occupied by the record set currently queried
void sqlite3_free_table(char **result);

Example: (Simple C instance of SQLite database using callback functions)

Compilation:
[root@localhost test]# gcc -o sql -l sqlite3