Common SQLite usage

SQLite is a lightweight database. Supports mainstream operating systems such as Windows, Linux, and UNIX, and can be combined with many programming languages. Resource occupation is very low, but the processing speed is quite fast. I recently made a project on SQLite. I learned some basic usage. The following is a summary:

1. Obtain the sqlite3.lib File

On the official SQLite website, you can only download SQLite DLL and the corresponding source code. To use it in VC, you must also use the sqlite3.lib file (of course, you can also use loadlibrary and getproaddress, however, many functions provided by SQLite are used in general projects, which is difficult to avoid ). The problem was found online. The specific process is as follows:

1. Download SQLite DLL;

2. Use the Lib command under Microsoft Visual Studio \ vc98 \ bin to generate. Lib through the command line. See the following example.

C: \ Program Files \ Microsoft Visual Studio \ vc98 \ bin> lib/out: D :\\ test \ sqlite3.lib/machine: ix86/DEF: D: \ test \ sqlite3.def

Generate sqlite3.lib Based on D: \ test \ sqlite3.def and put it in the path D: \ test \

2. With the sqlite3.lib file, you can use the SQLite database in VC.

1. Connect/Close the database

Int sqlite3_open (const char * filename, sqlite3 ** ppdb)

Filename indicates the database name. If it is not in the current project path, you must add an absolute path. Otherwise, an empty database with the same name will be created under the current project.

Ppdb is the address of the pointer variable pointing to the struct sqlite3.

Returned value: If the returned value is sqlite_ OK, the operation is successful. otherwise, the error code is returned. you can find the meaning of the error number in the sqlite3.h file, or use the sqlite3_errmsg (sqlite3 *) function to obtain the error information.

Int sqlite3_close (sqlite3 *) closes the database

2. Execute SQL statements (query the database)

Method 1:

Int sqlite3_exec (sqlite3 *, const char * SQL, sqlite3_callback, void *, char ** errmsg)

Parameter description:

SQL: SQL statement ending with "\ 0;

Sqlite3_callback: This is the callback function for executing SQL statements. It does not need to be null. The following describes the original form of the callback function;

The void * variable is the first parameter of the callback function.

Errmsg is an error message that may be returned when the function is modified.

Original callback function: typedef int (* sqlite3_callback) (void * para, int colun_count, char ** column_values, char ** column_names)

Para: The fourth parameter in sqlite3_exec Function

Colun_count: the number of rows in this query (if the SQL statement is select * from .., it is the number of columns in a record in the database. When the SQL statement is select field1, field2 form, the number of fields you can query will be used)

Column_names: pointer to the two-dimensional array of the field name

Column_values: values corresponding to column_names;

Method 2:

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

Resultp is the query result, and its memory layout is: the first line is the field name, followed by the value of each field

Other parameters are the same as above.

Corresponding layout (two fields are queried ):

Name ID

Tom 1

Bill 2

John 3

However, we generally think that it is a one-dimensional array composed of strings.

Corresponds

Result [0] = "name"; 0 = 0 * (* ncolumn) + 0

Result [1] = "ID"; 1 = 0 * (* ncolumn) + 1

Result [2] = "Tom"; 2 = 1 * (* ncolumn) + 0

Result [3] = "1 ";

Result [4] = "bill ";

Result [5] = "2 ";

Result [6] = "John

Result [7] = "3;

Note: After method 2 is used, sqlite3_free_table (azresult) must be used to release resources.

3. Execute other SQL statements

The sqlite3_exec function is still used to execute the SQL statements for adding, deleting, modifying, and creating tables in the response;

4. obtain information about the specified database

A. Obtain information about all tables in the specified database.

Execute SQL statements

"Select * From sqlite_master where type = 'table' order by name"

The name field indicates the name of the data table.

B. obtain the information of a specified table, including the field name and the type of a field.

I resolve the SQL statement "select * Form tab_name" to the sqlite3_prepare structure using the sqlite3_stmt function.

Int sqlite3_prepare (sqlite3 * dB, const char * zsql, int nbyte,

Sqlite3_stmt ** ppstmt, char ** pztail)

Nbyte indicates the length of an SQL statement. If it is-1, the length of the SQL statement is automatically calculated,

Pztail generally gives 0 (null.

Ii. Use the sqlite3_step function to execute the SQL statement that has just been parsed to sqlite3_stmt. It is enough to query a record (one row is enough)

Int sqlite3_step (sqlite3_stmt *);

Iii. Use functions to obtain required information

// Obtain the field name of column N (starting from 0)

Const char * sqlite3_column_name (sqlite3_stmt *, int N );

// Obtain the Declaration type when column N is created (declared as varchar (10) and obtained as varchar (10 ))

Const char * sqlite3_column_decltype (sqlite3_stmt *, int icol)

// Obtain the SQLite type (INT type)

Int sqlite3_column_type (sqlite3_stmt *, INT );

# Define sqlite_integer 1

# Define sqlite_float 2

# Define sqlite_text3 3

# Define sqlite_blob 4

# Define sqlite_null 5

IV. The sqlite3_stmt structure obtained before sqlite3_finalize is released. For example:

Sqlite3_finalize (STAT );

C. Obtain the number of records in a specified table

Execute the SQL statement "select count (*) from" and the result is the number of records in the specified table. Convert the result from char * to int.

3. Operations on binary data.

The preceding operations on Data Query and writing are performed without binary fields. SQLite provides a separate method to query and write binary tables.

1. Write binary data

// First, parse the query statement to the sqlite3_stmt structure (tab_blob2 field IDs and data ).

Sqlite3_stmt * Stat;

Sqlite3_prepare (dB, "insert into tab_blob (ID, data) values (10 ,? ) ",-1, & stat, 0)

"?" Indicates that the data field is still unknown and will be inserted only when

Note: The condition for successful execution of the function is: Return Value sqlite_ OK and stat is not null.

// Bind binary data

Sqlite3_bind_blob (stat, 1, pdata, (INT) (length_of_data_in_bytes), null)

The second parameter is the front side "?" The sequence number in the table (starting from 0). Here 1 indicates the second field.

If there are multiple "?" What should I do?

Then execute sqlite3_bind_blob several times and change the second parameter.

Pdata is the starting address of binary data in memory.

Length_of_data_in_bytes is the length of binary data in bytes.

5th parameters: it is a callback function that tells SQLite to call this function after processing data to analyze your data. Generally, null is entered. The memory to be released is released using code.

// Write the data to the database

Int result = sqlite3_step (STAT );

// Release sqlite3_stmt data

Sqlite3_finalize (STAT );

2. Read Binary data

Sqlite3_prepare (dB, "select * From tab_blob",-1, & stat, 0 );

After the prepare function returns sqlite_ OK, it indicates that the operation is successful.

Query data through int result = sqlite3_step (STAT;

Each time a row is queried, the returned value is sqlite_row, indicating that the query is successful. If the returned value is not sqlite_row, the query ends.

Then, the values of each field are obtained through the function corresponding to the field type.

// Obtain the int type

Int sqlite3_column_int (sqlite3_stmt *, int icol );

// Obtain the Duble type

Double sqlite3_column_double (sqlite3_stmt *, int icol );

// Obtain the text type

Const unsigned char * sqlite3_column_text (sqlite3_stmt *, int icol );

// Obtain the binary type

Const void * sqlite3_column_blob (sqlite3_stmt *, int icol); // obtain binary data at the memory address

Int sqlite3_column_bytes (sqlite3_stmt *, int icol); // obtain the length of binary data

Iv. Handling of file opening failure in Chinese path

Most of the time, we will find that when the database file is in the Chinese path, it will fail to open the file. This is mainly because SQLite uses the utf8 format, so it needs to be converted to the corresponding format. Function

Widechartomultibyte (cp_utf8,...) for conversion. Of course, if your path is multi-byte, you must first convert the path to widechar.

Ey:

Int sqlite_data_operate: connect_database (const char * database_full_name)
{
Ca2t lpt_str (database_full_name );
Char data_base [1024] = {0 };
 
Int size = widechartomultibyte (cp_utf8, 0, lpt_str,-1, data_base, 0, null, null );
Widechartomultibyte (cp_utf8, 0, lpt_str,-1, data_base, size, null, null );
 
Return sqlite3_open (data_base, & m_db );
}