[Database] sqlite3 usage summary 2

(2) SQL statement operations
, O + B6 U1 z0 V! W (A This section describes how to use SQLite to Execute standard SQL syntax.
6 F) '8 ^ 2 m, C; k; G (A * P; ^

4 Y8 X: rjl 'j8 H: R "G7 Q5 K * r) x # L2 r, f. B
I .1 Execute SQL statements

Copy content to clipboard

Code:

int sqlite3_exec(sqlite3*, const char *sql, sqlite3_callback, void *,   char **errmsg );

This is the function used to execute an SQL statement.
/U1 K + l "d, Y: V! S9 W #\
% L8 X5 N7 t' M8 l % T7 N0 ~ S/Y4 J 1st parameters are the pointer obtained by the open function. It is the key data structure.
) Q! S. Q) H4 L6 U5 s # O) L) j9 C # U) I (z0 Z ;~ ('5 L
The 2nd const char * SQL is an SQL statement ending with \ 0. /R. Y % P.} * l 'F' D 'y4 ~ & Z '{
-Y2 Z2 K + W. L! ]-U
The 3rd sqlite3_callback parameter is a callback. After this statement is executed, sqlite3 will call the function you provided. (What is a callback function? Learn from other materials.) + j9 u! {7 9? % C; M3 H0 K
# Z % E2 Z9 B: C $ I #\
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. If you do not need to pass the pointer to the callback function, you can enter null. Let's look at the method of the callback function and the use of this parameter .; P3 Q $ m; m n # Y5 E2 Z
: G,} 0 I * T % G! K -{
The first parameter char ** errmsg is the error message. Note the pointer. Sqlite3 contains many fixed error messages. After sqlite3_exec is executed, you can view this pointer (directly printf ("% s \ n", errmsg) When execution fails to get a string, this string of Information tells you where the error is. The sqlite3_exec function points the pointer you provided to the error message by modifying the pointer you passed in, so that the sqlite3_exec function can use this char * to get a specific error message. 0 E: G: \ % Q, Z

! U! [2 I * I! V4 R: i8 n Description: It is common. Either sqlite3_callback or void * Can be null. If this parameter is set to null, callback is not required. For example, if you perform insert and delete operations, there is no need to use callback. When you do select, you need to use a callback, because sqlite3 checks the data and uses a callback to tell you what data has been found.
2 z5 F + q (K! Z7 W
5} "] 2 v2 K. S * s # Y" C2 {
% G & F7 N:] 7 \ 1 | 9 D9 B6 |-} & O
I .2 exec callback

Copy content to clipboard

Code:

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:
5 E9 d $ K3 C "U (A9 U & l5 d: S * \ 3 G7 R; j9 i8 O5 Q
// Sqlite3 callback function
. J! R % B (D "U + U. B; L6 M $ B
9 C6 Z; @/\ (V + O "I3 P (J5g // each time SQLite finds a record, this callback is called once.

Copy content to clipboard

Code:

    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;

}