[Reprint] SQLite C/C ++ API Learning

[Reprint] http://blog.csdn.net/jgood/article/details/4640577

SQLite is my favorite file database. It is small, convenient, fast, supports most standard SQL statements, open source, free... it has many advantages. Since I met her, I have moved access into the cold Palace and vowed that I will no longer use access. We can download the source code of SQLite from the official website of SQLite. The compiled result is only over 200 kb, which is too small and so cool. If manual compilation is troublesome, you can download binary executable files or dynamic link libraries from the official website. The executable files can be created and operated on the database using the command line. The dynamic link library can be used to operate the SQLite database using a program. Today, I will introduce how to use C/C ++ to operate SQLite ~~~

Preparations

The SQLite version I used here is 3.x, so first download the dynamic link library sqlite3.dll from the official website; then download the API header file sqlite3.h. Create a C ++ console project in code: Block, add a reference to sqlite3.dll, and add sqlite3.h to the project. I also used sqlitespy to visualize the database.

Start using API

Let's start using the API ~. All SQLite APIs can be found inHereFound.

Sqlite3_opensqlite3_execsqlite3_close

Looking at these functions, I believe you can probably guess the functions of these functions. Sqlite3_open is used to open a database and initialize a database connection. sqlite3_close is used to close the connection. sqlte3_exec is used to execute SQL statements. The following code creates a database test. dB and a data table test_for_cpp.

// ----------------------------------------------// http://blog.csdn.net/JGood

// Create a database // sqlite3_open, sqlite3_exec, sqlite3_close // ------------------------------------------------ sqlite3 * conn = NULL; // database connection. Char * err_msg = NULL; // cause of operation failure. Char SQL [200]; // open the database and create a connection if (sqlite_ OK! = Sqlite3_open ("test. DB ", & conn) {printf (" can't open the database. "); exit (-1);} // execute sqlsprintf (SQL," CREATE TABLE [test_for_cpp] (/[ID] int, [name] varchar (20 ), [age] INT) "); If (sqlite_ OK! = Sqlite3_exec (Conn, SQL, 0, 0, & err_msg) {printf ("operate failed: % s", err_msg); exit (-1 );} // close the connection. If (sqlite_ OK! = Sqlite3_close (conn) {printf ("can't close the database: % s/n", sqlite3_errmsg (conn); exit (-1 );} printf ("operate successfully. /n ");

The above sample code is very simple. The main function sqlite3_exec is complex, and its prototype is:

int sqlite3_exec(  sqlite3*,                                  /* 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 */);

The third parameter is a function pointer, indicating the callback after the function is executed. The fourth parameter is the parameter passed to the callback function. The fifth parameter indicates the cause of execution failure. The following is a simple example to demonstrate the detailed use of this function:

// Explain // insert, select // detailed usage of sqlite3_exec // -------------------------------------------------------- sqlite3 * conn = NULL; // database connection. Char * err_msg = NULL; // cause of operation failure. Char SQL [200]; // callback function declaration. Extern int sqlite3_exec_callback (void * data, int n_columns, char ** col_values, char ** col_names); // open the database and create a connection to sqlite3_open ("test. DB ", & conn); // Add 10 records for (INT I = 0; I <10; I ++) {// Execute SQL sprintf (SQL, "insert into [test_for_cpp] ([ID], [name], [age])/values (% d, '% s', % d)", I, "jgood", I); sqlite3_exec (Conn, SQL, 0, 0, & err_msg) ;}// query sprintf (SQL, "select * from [test_for_cpp]"); sqlite3_exec (Co Nn, SQL, & sqlite3_exec_callback, 0, & err_msg); // close the connection. Sqlite3_close (conn );//... ...

/** Sqlite3_exec callback. ** Print the query result to the console. ** @ Param in data refers to the data passed to the callback function. * @ Param in n_columns the number of columns in the sqlite3_exec execution result set. * @ Param in col_values sqlite3_exec data of each column in the execution result set. * @ Param in col_names sqlite3_exec name of each column in the execution result set. * @ Return status code. */INT sqlite3_exec_callback (void * data, int n_columns, char ** col_values, char ** col_names) {for (INT I = 0; I <n_columns; I ++) {printf ("% S/T", col_values [I]);} printf ("/N"); Return 0 ;}

The execution result is as follows:

Use transactions

In the above example, we inserted 10 pieces of data into the database and then read it from the database. Careful, you don't know if you have found that the same transaction is not used when executing insert. In many cases, we need to use transactions to ensure the atomicity of database operations. SQLite supports transactions and is very easy to use: the SQL statement "begin;" indicates the start of the transaction, "rollback;" indicates the rollback of the transaction, "commit; "indicates the commit of the transaction. Next we will modify the code in the above example to add transaction support for the insert operation:

//...... Bool is_succed = true; sqlite3_exec (Conn, "begin;", 0, 0, 0); // start the transaction // Add 10 records for (INT I = 0; I <10; I ++) {// Execute SQL sprintf (SQL, "insert into [test_for_cpp] ([ID], [name], [age]) /values (% d, '% s', % d) ", I," jgood ", I); If (sqlite_ OK! = Sqlite3_exec (Conn, SQL, 0, 0, & err_msg) {is_succed = false; break ;}} if (is_succed) sqlite3_exec (Conn, "commit;", 0, 0, 0); // submit the transaction else sqlite3_exec (Conn, "rollback;", 0, 0, 0); // roll back the transaction //......

Use SQL Parameters

Basically, you can use the sqlite3_open, sqlite3_close, and sqlite3_exec functions to complete most of the work. But not yet complete. In the above example, the database is operated directly in the form of SQL statements, which is easy to be injected. Therefore, it is necessary to use SQL parameters.

Sqlite3_preparesqlite3_bind _ * sqlite3_stepsqlite3_column _ * struct sqlite3_stmtsqlite3_finalize

Sqlite3_prepare is used to compile SQL statements. Before an SQL statement is executed, it must be compiled into bytecode. Sqlite3_stmt is a struct that represents the bytecode after the SQL statement is compiled. Sqlite3_step is used to execute compiled SQL statements. Sqlite3_bind _ * is used to bind SQL parameters to SQL statements. Sqlite3_column _ * is used to obtain data from the query results. Sqlite3_finalize is used to release the sqlite3_stmt object. The code can best describe the functions of the function. Here is an example for demonstration ~~

// ----------------------------------------------// http://blog.csdn.net/JGood

// Increment, increment _ *, sqlite3_step, sqlite3_column _ *, increment // increment, increment, sqlite3_reset // query // specify sqlite3 * conn = NULL; sqlite3_stmt * stmt = NULL; const char * err_msg = NULL; // column data type char col_types [] [10] = {"", "integer", "float", "text", "blob ", "null"}; sqlite3_open ("test. DB ", & conn); sqlite3_prepare (Conn," Se Lect * from [test_for_cpp] Where [ID]>? ",-1, & stmt, & err_msg); sqlite3_bind_int (stmt, 1, 5); While (sqlite_row ==sqlite3_step (stmt) {int col_count = sqlite3_column_count (stmt ); // Number of columns in the result set const char * col_0_name = sqlite3_column_name (stmt, 0); // obtain the column name int id = sqlite3_column_int (stmt, 0); int id_type = sqlite3_column_type, 0); // obtain the column data type const char * col_2_name = sqlite3_column_name (stmt, 2); int age = sqlite3_column_int (stmt, 2); int age_type = sqlite3_column_type (stmt, 2); const char * col_1_name = sqlite3_column_name (stmt, 1); char name [80]; strncpy (name, (const char *) sqlite3_column_text (stmt, 1), 80 ); int name_type = sqlite3_column_type (stmt, 1); // print the result printf ("col_count: % d, % s = % d (% s ), % s = % s (% s), % s = % d (% s)/n ", col_count, col_0_name, ID, col_types [id_type], col_2_name, name, col_types [name_type], col_1_name, age, col_types [age_type]);} sqlite3_finalize (stmt); // release sqlite3_stmt sqlite3_close (conn );

This code queries all records with an ID number greater than 5 and displays them on the console. The final result is

Other functions

In the preceding example, other functions, such as sqlite3_column_count, are used to obtain the number of columns in the result set, sqlite3_column_name is used to obtain the column name, And sqlite3_column_type is used to obtain the column data type; sqlite3_errcode is used to get the error code of the last operation error; sqlite3_errmsg is used to get the error description of the last operation error. There are many functions in the SQLite API. With the above foundation, I believe that you can quickly master the APIS not described in this article by querying official documents.

String Encoding

When viewing the SQLite API on the official website, we found that the names of the functions are very similar, but "_ 16" was added at the end, such as sqlite3_open and sqlite3_open16, sqlite3_errmsg and sqlite3_errmsg16, and so on. In fact, added the "16" suffix function, mainly used to support the UTF-16 encoding string. For example, sqlite3_open16 can receive the database path encoded by the UTF-16.

On SourceForge, there is an open-source project sqlitex, which encapsulates these APIs, making it easier to operate SQLite databases. The source code of sqlitex is very simple. If you are interested, you can download it and study it yourself.

• Reference:
• SQLite Official Website: http://www.sqlite.org/
• SQLite Chinese community: http://www.sqlite.com.cn/