Sqlite Common functions Recommended

Sqlite Common functions

1. Open the database:

Description: Open a database, file name does not have to exist, if this file does not exist, SQLite will be created automatically. The first parameter refers to the file name, the second parameter is the defined sqlite3 * * struct pointer (critical data structure), which is the underlying detail of the structure, and you don't have to worry about it.

Const /* *//**/

Return value: Indicates whether the operator is correct (SQLITE_OK operation is OK)

2. Close the database:
Note: If you open a database with Sqlite3_open, do not forget to close the database with this function at the end.
int Sqlite3_close (sqlite3*); parameter is just the struct, which is the database handle

3. Execute SQL statement:
Note: The function is to execute one or more SQL statements, separated by the ";" sign between the SQL statements. It is recommended that you specify the third parameter callback function when executing one or more SQL statements, and you can get a detailed procedure for executing SQL in the callback function, if all SQL execution is completed, you should return 0, otherwise the execution is not completely successful. Fifth parameter: If execution fails (no return 0), you can view the fifth elaborated value. To view detailed error information.

intsqlite3_exec (Sqlite3*,/*database handles that have been opened*/ConstChar *Sql/*the SQL statement to execute*/Sqlite_callback,/*callback function*/void*,/*arguments passed to the callback function*/ Char **ErrMsg/*Save error message*/ ); 

Usually both the Sqlite3_callback and the void* behind it can be NULL, indicating that no callback is required. For example, if you do an insert operation and do a delete operation, there is no need to use callbacks. As a SELECT, you use callbacks, because sqlite3 the data to tell you what data was detected by a callback.

4. EXEC callback
typedef int (*sqlite3_callback) (void*, int, char**, char**);
Description: Your callback function must be defined as the type of the function above.
For example:

intLoadmyinfo (void*ParaintN_column,Char **Column_value,Char **column_name) { //Para is the void you passed into the sqlite3_exec.*Parameters//With the para parameter, you can pass in some special pointers (such as class pointers, struct pointers), and then cast them into the corresponding type (which is void*type must be cast to your type before it is available). Then manipulate the data//N_column is the number of fields in this record (that is, how many columns this record has) www.jbxue.com// Char **Column_value is a key value, the data is stored here, it is actually a1Dimension Array (do not assume that it is2Dimension array), each element is aChar *Value, which is a field content (represented by a string, to \0end)//Char **COLUMN_NAME is corresponding to Column_value, which indicates the field name of the field.

5. Take the current insertion position:

Function: Returns the previous position you inserted, starting with 1, sqlite3* the handle you get for opening the database.
Long Long int sqlite3_last_insert_rowid (sqlite3*);
6, non-callback select query:
Function: Executes a query SQL once and returns a recordset.

intsqlite3_get_table (Sqlite3*,/*database handles that have been opened*/ConstChar *Sql/*the SQL statement to execute*/ Char ***RESULTP,/*save Pointer to return recordset*/ int *Nrow,/*returns the number of records (and how many rows are detected)*/ int *Ncolumn,/*number of fields returned (how many columns)*/ Char **ErrMsg/*return error message*/ ) 

Description: (www.jbxue.com) The third parameter is the query result, which is a one-dimensional array with memory layout: The first row is the field name, followed by the value of each field.

Instance:

intMainint,Char **) {sqlite3*db;intresult;Char *ErrMsg= NULL; Char **Dbresult;intNrow, Ncolumn;intI, J;int Index; result=Sqlite3_open ("C:\\dcg_database.db",&db); if(Result!=SQLITE_OK) { return -1; } //Database Operation code//assuming that the Mytable_1 table has been created earlier//To start the query, the incoming Dbresult is alreadyChar **, here's another one.&Take the address character, and it's passed in.Char ***result=Sqlite3_get_table (DB, "Select *  fromMytable_1 ",&Dbresult,&Nrow,&Ncolumn,&errmsg); if(SQLITE_OK==result) { //Query SuccessfulIndex =Ncolumn;//I said before, Dbresult. The first row of data is the field name, starting with the Ncolumn index is the real data printf ("Find%d record \ 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 value of the Dbresult is continuous, from the first0Index to page Ncolumn- 1The index is the field name, starting with the Ncolumn index, followed by the field value, which represents a two-dimensional table (the traditional row and column notation) in a flat form .-------\ n "); } } //Here, regardless of whether the database query is successful, releaseChar**query results, using the functionality provided by SQLite to release sqlite3_free_table (Dbresult);//Close Database Sqlite3_close (db);return 0; }

7. Release the query result:

Function: Frees the memory occupied by the recordset of the current query
void Sqlite3_free_table (char **result);

Example: (A simple C-language instance of a SQLite database using a callback function)
Copy the code code as follows:

#include<Stdio.h>#include<Sqlite3.h>StaticintCallback (void*NotUsed,intargcChar **ArgvChar **azcolname) { inti; for(I=0; I<argc I++) {printf ("%S= %S\n ", Azcolname[I]Argv[I]? Argv[I]: "NULL" ); } printf ("\ n"); return 0; } intMainintargcChar **argv) {Sqlite3*db;Char *Zerrmsg= 0; intRC;if(argc!=3{fprintf (stderr, "Usage:%SDATABASESql-Statement\n ", argv[0]); return 1; } RC=Sqlite3_open (argv[1],&db); if(RC) {fprintf (stderr, "Can'T Open database:%s\n ", sqlite3_errmsg (db)); Sqlite3_close (DB); return 1; rc = sqlite3_exec (db, Argv[2], callback, 0, &zerrmsg); if (RC!=SQLITE_OK) {fprintf (stderr, "SQL Error:%s\n", zerrmsg); Sqlite3_close (db); return 1;} sqlite3_close (db); return 0; }

Compile:

[Email protected] test]# gcc sql.c-o sql-l sqlite3