Sqllite (2): sqlite3_open, sqlite3_exec, slite3_close

Import sqllite library and introduce header files

Libsqlite3.dylib is a link, which points to libsqlite3.0.dylib. That is to say, adding libsqlite3.dylib and adding libsqlite3.0.dylib are actually adding the same file. There is no difference. Why should we add libsqlite3.dylib? The reason is that libsqlite3.dylib always points to the latest sqlite3 dynamic library. That is to say, if a new dynamic library (such as libsqlite3.1.dylib) appears, libsqlite3.dylib points to this new dynamic library (libsqlite3.1.dylib) instead of libsqlite3.0.dylib! Therefore, we recommend that you add libsqlite3.dylib.

Note:

On Mac OS X, frameworks are just libraries, packed into a bundle. Within
Bundle you will find an actual dynamic library (libwhatever. dylib ). the difference between a bare Library and the framework on Mac is that a framework can contain in multiple different versions of the library. it can contain extra resources (images, localized
Strings, XML data files, UI objects, etc.) and unless the framework is released to public, it usually contains the necessary. H files you need to use the library.

A
Library is just that, "A library ". it is a collection of objects/functions/methods (depending on your language) and your application "Links" against it and thus can use the objects/functions/methods. it is basically a file containing re-usable code that can
Usually be shared among multiple applications (you don't have to write the same code over and over again ).

Use sqlite3_open to open a database

Prototype:

int sqlite3_open(  const char *filename,   /* Database filename (UTF-8) */  sqlite3 **ppDb          /* OUT: SQLite db handle */);

Use this function to start database operations. You need to input two parameters. One is the database file name, for example, E:/test. DB. In iOS, it may be:

NSArray * documentPath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);NSString * dbPath = [[documentPath objectAtIndex:0] stringByAppendingPathComponent:@“test.db”];

The file name does not need to exist. If the file does not exist, SQLite will automatically create it. If it exists, try to open it as a database file. The second is sqlite3 **, which is the key data structure mentioned above. Do not worry about the underlying details of this structure. The Return Value of the function indicates whether the operation is correct. If it is sqlite_ OK, the operation is normal. Related return values SQLite defines some macros. For more information about these macros, see the sqlite3.h file. There are detailed definitions in it (by the way, sqlite3 code annotation rate claims to be very high, but it is indeed very high. As long as you can read English, SQLite
You can learn a lot ).

Close the database connection sqlite3_close usage

Prototype:

int sqlite3_close(sqlite3 *ppDb);

Ppdb is the link to the database opened with sqlite3_open.

SQL statement execution sqlite3_exec usage

Prototype:

 int sqlite3_exec(   sqlite3* ppDb,                             /* An open database */   const char *sql,                           /* SQL to be evaluated */   int (*callback)(void*,int,char**,char**),  /* Callback function */   void *,                                    /* 1st argument to callback */   char **errmsg                              /* Error msg written here */ );

This is the function used to execute an SQL statement. The first parameter is a pointer obtained by the open function. The 2nd constchar * 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. If you do not need to pass the 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 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 when execution fails (cout <errmsg gets a string of information, which 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: Normally, either sqlite3_callback or void * after it 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.

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: // sqlite3 callback function // each time SQLite finds a record, it calls this callback int loadmyinfo (void * para, int n_column, char ** column_value, char ** column_name );

// Para is the void * parameter you pass in sqlite3_exec. Through the Para parameter, you can pass in some special pointers (such as class pointers and structure pointers ), then, it is forcibly converted to the corresponding type (void * type here, which must be converted to your type only ). 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

Instance:

1 # include <iostream> 2 using namespace STD; 3 # include "SQLite/sqlite3.h" 4 int callback (void *, Int, char **, char **); 5 Int main () 6 {7 sqlite3 * dB; 8 int nresult = sqlite3_open ("test. DB ", & dB); 9 If (nresult! = Sqlite_ OK) 10 {11 cout <"failed to open database:" <sqlite3_errmsg (db) <Endl; 12 Return 0; 13} 14 else15 {16 cout <"database opened successfully" <Endl; 17} 18 19 char * errmsg; 20 21 nresult = sqlite3_exec (dB, "create table mytable (ID integer primary key autoincrement, name varchar (100)", null, null, & errmsg); 22 if (nresult! = Sqlite_ OK) 23 {24 sqlite3_close (db); 25 cout <errmsg; 26 sqlite3_free (errmsg); 27 return 0; 28} 29 string strsql; 30 strsql + = "begin; \ n "; 31 for (INT I = 0; I <100; I ++) 32 {33 strsql + =" insert into mytable values (null, 'heh '); \ n "; 34} 35 strsql + =" commit; "; 36 // cout <strsql <Endl; 37 38 nresult = sqlite3_exec (dB, strsql. c_str (), null, null, & errmsg); 39 40 if (nresult! = Sqlite_ OK) 41 {42 sqlite3_close (db); 43 cout <errmsg <Endl; 44 sqlite3_free (errmsg); 45 return 0; 46} 47 48 strsql = "select * From mytable"; 49 nresult = sqlite3_exec (dB, strsql. c_str (), callback, null, & errmsg); 50 if (nresult! = Sqlite_ OK) 51 {52 sqlite3_close (db); 53 cout <errmsg <Endl; 54 sqlite3_free (errmsg); 55 return 0; 56} 57 58 sqlite3_close (db ); 59 return 0; 60} 61 62 int callback (void *, int ncount, char ** pvalue, char ** pname) 63 {64 string S; 65 for (INT I = 0; I <ncount; I ++) 66 {67 S + = pname [I]; 68 s + = ":"; 69 S + = pvalue [I]; 70 s + = "\ n"; 71} 72 cout <S <Endl; 73 return 0; 74}