SQLite3 basic SQL syntax usage-wince6.0

Note: Applications and tests run in the following Environments

This project is in VS2005, C ++, sqlite3.7.14, Development Board mini2440, wince6.0

Project first copy: WinceSQLite_DLL.lib, WinceSQLite_DLL.dll, sqlite3.h and database file Temp-Humi.db, to the corresponding project file

 

SQLite 3.0 provides many API functions, including some data structures and predefined values (# defines ). however, you can rest assured that these interfaces are not complex to use. the simplest program can still use three functions: sqlite3_open (), sqlite3_exec (), and sqlite3_close (). to better control the execution of the database engine, you can use the provided sqlite3_prepare () function to compile the SQL statement into bytecode, and then use sqlite3_step () function to execute the compiled bytecode.
A group of API functions starting with sqlite3_column _ are used to obtain information in the query result set. many interface functions appear in pairs, both UTF-8 and UTF-16 versions. A set of functions are provided to execute User-Defined SQL functions and text sorting functions.

 

1. Open the database function:

Int sqlite3_open (const char *, sqlite3 **);

The first parameter is the file path. Note that there is only absolute path strength in wince. For details about how to obtain the absolute path in wince, refer to another article:

The second parameter is the pointer of the sqlite3 pointer. It is used to obtain open files.

 

2. query database functions-Use callback Functions

The sqlite3_exec function still undertakes a lot of work like it does in SQLite2. the second parameter of the function can compile and execute zero or multiple SQL statements. the query result is returned to the callback function. in SQLite3, sqlite3_exec is generally encapsulated by the prepared SQL statement interface.

Typedef int (* sqlite_callback) (void *, int, char **, char **);

Int sqlite3_exec (sqlite3 *, const char * SQL, sqlite_callback, void *, char **);

The first parameter is a pointer obtained by the open function.

The second parameter const char * SQL is an SQL statement ending with \ 0.

The third parameter sqlite3_callback is the callback. After this statement is executed, sqlite3 will call the function you provided.

The fourth 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.

The fifth 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.

Note: 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 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. query database functions-do not use callback Functions

The sqlite3_exec described above uses a callback to execute the select Operation. There is also a way to directly query without callback. The advantage of callback is that the code can be more neat, but it is very troublesome to Use callback. You must declare a function. If this function is a class member function, you have to declare it static (because the C ++ member function actually hides a parameter: this, when C ++ calls a class member function, the class pointer is passed as the first parameter of the function. The result is inconsistent with the parameters of the sqlite callback function described above. Only when the member functions are declared
Static ).

You can use the sqlite3_get_table function to query data without using the callback function.

Int sqlite3_get_table (sqlite3 *, const char * SQL, char *** resultp, int * nrow, int * ncolumn, char ** errmsg );

The first parameter is no longer mentioned. Let's look at the previous example.

The second parameter is an SQL statement, which is the same as the SQL statement in sqlite3_exec. It is a common char * string ending with \ 0.

The third parameter is the query result, which 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 fourth parameter is how many records are queried (that is, how many rows are queried ).

The fifth parameter is the number of fields (columns ).

The sixth parameter is the error message.

Engineering files, source code download http://download.csdn.net/detail/mjx91282041/4743958

Test code:

 

Sqlite3 * dB; // int RC; char * zerrmsg; // character pointer returned by SQLite execution errors // open the SQLite database file. // Note: if the file cannot be found, SQLite will create a DB file with the same name rc = sqlite3_open ("Program Files \ wincesqlitetest2 \ Temp-Humi.db", & dB); If (RC! = Sqlite_ OK) {sqlite3_close (db); printf ("The sqlite3 database open error! ");} // Insert data in both formats. The ID is primary // char * pstr =" insert into sensordata (ID, sensorid, temperature, humidity, time) values (null, 1003, 31.0, 23.5, '123') "; char * pstr =" insert into \ "sensordata \" values (null, 20120501421900, 1003, 31.0, '20140901'); "; rc = sqlite3_exec (dB, pstr, 0, 0, & zerrmsg); If (rc = sqlite_ OK) {printf ("insert data to the Temp-Humi.db successed");} elseprintf ("insert record failed, error code: % d, error cause: % s \ n", RC, zerr MSG); // There are two ways to query the database: callback function and non-callback function // Use callback function // rc = sqlite3_exec (dB, "select sensorid, time from sensordata ", sqlitequeryresultcallback, null, & zerrmsg); // query data without using the callback function char ** presult; int nrow; int ncol; rc = sqlite3_get_table (dB, "select sensorid, time from sensordata ", & presult, & nrow, & ncol, & zerrmsg); If (RC! = Sqlite_ OK) {sqlite3_close (db); sqlite3_free (zerrmsg) ;}// online display example/* string Strout; int nindex = ncol; For (INT I = 0; I <nrow; I ++) {for (Int J = 0; j <ncol; j ++) {Strout + = presult [J]; Strout + = ":"; strout + = presult [nindex]; Strout + = "\ n"; ++ nindex ;}} sqlite3_free_table (presult); cout <Strout <Endl; * /// after understanding the memory layout, display it by yourself. // The Memory layout of presult is: field name, followed by the value of each field int nindex = ncol; for (INT I = 0; I <nrow; I ++) {for (Int J = 0; j <ncol; j ++) {printf ("% s: % s \ r \ n ", presult [J], presult [nindex ++]) ;}} sqlite3_free_table (presult); // close the database sqlite3_close (db );