Interface description for lightweight database SQLite

Original address: http://www.cnblogs.com/kfqcome/archive/2011/06/27/2136999.html

A Use process

To use SQLite, you need to download from the SQLite website to three files, Sqlite3.lib,sqlite3.dll,sqlite3.h, and then in their own project configuration of the header files and library files, while the DLL files in the current directory, the completion of the configuration can use SQLite.

The procedure used is broadly divided into the following processes according to the functions used:

• Sqlite3_open ()
• Sqlite3_prepare ()
• Sqlite3_step ()
• Sqlite3_column ()
• Sqlite3_finalize ()
• Sqlite3_close ()

These processes are conceptually described, not entirely the process of running a program, such as Sqlite3_column () represents the individual operations of a column that queries the data within a row, and in fact there is no such function in SQLite.

1. Sqlite3_open (): Open database

Before you manipulate the database, you first open the database. This function opens a connection to the SQLite database file and returns a database connection object. This operation is the first call of the SQLite function in the program, and is also a prerequisite for other SQLite APIs. Many of the SQLite interface functions require a pointer to a database connection object as their first parameter.

function definition

int Sqlite3_open (

const char *filename,/* Database filename (UTF-8) */

Sqlite3 **ppdb/* Out:sqlite DB handle */

);

int Sqlite3_open16 (

const void *filename,/* Database filename (UTF-16) */

Sqlite3 **ppdb/* Out:sqlite DB handle */

);

int Sqlite3_open_v2 (

const char *filename,/* Database filename (UTF-8) */

Sqlite3 **ppdb,/* out:sqlite DB handle */

int flags,/* flags */

const char *ZVFS/* Name of VFS module to use */

);

Description

If the data file to be opened does not exist, a database file with the same name will be created. If you use Sqlite3_open and SQLITE3_OPEN_V2, the database will be encoded in UTF-8, Sqlite3_open16 with the UTF-16 encoding method

return value:

If the SQLite database is opened (or created) successfully, SQLITE_OK will be returned, otherwise an error code will be returned. Sqlite3_errmsg () or sqlite3_errmsg16 can be used to obtain an English description of the database open error code, which is defined as:

const char *sqlite3_errmsg (SQLITE3*);

const void *sqlite3_errmsg16 (SQLITE3*);

Parameter description:

FileName: The file name of the database file that needs to be opened, this parameter is UTF-8 encoded in Sqlite3_open and SQLITE3_OPEN_V2, and UTF-16 encoded in Sqlite3_open16

PPDB: A database connection handle is returned to this parameter, even if an error occurs. The only thing is that if SQLite cannot allocate memory to hold SQLite objects, PPDB will be returned with a null value.

Flags: As an additional control parameter for database connections, it can be sqlite_open_readonly,sqlite_open_readwrite and sqlite_open_readwrite| One of the sqlite_open_create, used to control how the database is opened, can be Sqlite_open_nomutex,sqlite_open_fullmutex, Sqlite_open_sharedcache, As well as Sqlite_open_privatecache, detailed information can be consulted in the documentation

2. Sqlite3_prepare ()

This function converts the SQL text into a prepared statement (prepared statement) object and returns a pointer to the object. This interface requires a database connection pointer and a text to be prepared that contains the SQL statement. It does not actually execute (evaluate) This SQL statement, it simply prepares the SQL statement for execution

function definition (listed only for UTF-8)

int Sqlite3_prepare (

Sqlite3 *db,/* Database handle */

const char *zsql,/* SQL statement, UTF-8 encoded */

int Nbyte,/* Maximum length of zsql in bytes. */

Sqlite3_stmt **ppstmt,/* out:statement handle */

const char **pztail/* out:pointer to unused portion of Zsql */

);

int Sqlite3_prepare_v2 (

Sqlite3 *db,/* Database handle */

const char *zsql,/* SQL statement, UTF-8 encoded */

int Nbyte,/* Maximum length of zsql in bytes. */

Sqlite3_stmt **ppstmt,/* out:statement handle */

const char **pztail/* out:pointer to unused portion of Zsql */

);

Parameters:

DB: Data pointer

Zsql:sql statements, using UTF-8 encoding

Nbyte: If Nbyte is less than 0, the function takes out the contents of the Zsql from the beginning to the first 0 terminator, and if the nbyte is not negative, it is the maximum number of bytes that the function can read from the Zsql. If nbytes is not negative, zsql terminates at the first meeting of '/000/or ' u000 '

Pztail: The above mentioned Zsql end after meeting the Terminator or the nbyte to achieve the set, if there is any remaining content, then the remaining content is stored in Pztail, not including The Terminator

PPSTMT: A pointer to a compiled prepared statement that can be executed using sqlite3_step (), if an error occurs, it is set to NULL, such as if the input text does not include an SQL statement. The calling procedure must be responsible for removing the compiled SQL statement after it is finished using Sqlite3_finalize ().

Description

If execution succeeds, returns SQLITE_OK, otherwise an error code is returned. It is recommended to use the SQLITE3_PREPARE_V2 function in any program now, Sqlite3_prepare only for forward compatibility

Note

<1> Prepare statement (prepared statement) object

typedef struct SQLITE3_STMT sqlite3_stmt;

The Prepare statement (prepared statement) object represents an instance of a simple SQL statement object, which is often referred to as a "prepared statement" or "compiled SQL statement" or directly called a "statement."

The life cycle of a statement object undergoes such a process:

L use SQLITE3_PREPARE_V2 or related functions to create this object

L use sqlite3_bind_* () to bind values to host parameters (host parameters)

L execute this SQL by calling Sqlite3_step one or more times

L use Sqlite3--reset () to reset this statement, and then go back to the 2nd step, this process done 0 or more times

L destroy this object using Sqlite3_finalize ()

In SQLite, there is no specific content of the SQLITE3_STMT structure, it is just an abstract type, which is usually manipulated by its pointers during use, while the pointer to the SQLITE3_STMT type is actually a pointer to the VDBE structure

<2> host parameters (host parameters)

In the SQL statement text passed to SQLITE3_PREPARE_V2 () or its variables, the text that satisfies the following template is replaced with a parameter:

L?

L? NNN,NNN representative Numbers

L:VVV,VVV representative character

L @VVV

L $VVV

In the above templates, nnn represents a number, VVV represents an alphanumeric marker (for example: 222 for a marker with a name of 222), and the arguments (variables) in the SQL statement are specified by several templates above, such as

"Select?" From? "This statement specifies two parameters, and the index value of the first parameter in the SQLite statement is 1, knowing that the two parameters in the statement are indexed 1 and 2, using"? " will be automatically given the index value, and use "? NNN "You can specify the index value of the parameter yourself, which indicates that the index value of this parameter is NNN. ": VVV" means a parameter named "VVV", which also has an index value, which is automatically specified.

You can use sqlite3_bind_* () to bind values to these parameters

3. SQLITE3_SETP ()

This procedure is used to execute a prepared statement with the previous sqlite3_prepare created. This statement executes to the location where the first line of the result is available. To move on to the second line of the result, simply call SQLITE3_SETP () again. Continue calling SQLITE3_SETP () to know that the statement is complete, those statements that do not return a result (such as: insert,update, or delete), Sqlite3_step () is only executed once to return

function definition

int Sqlite3_step (sqlite3_stmt*);

return value

The return value of the function is based on the function used to create the sqlite3_stmt parameter, and if the old version of the interface Sqlite3_prepare () and Sqlite3_prepare16 () is used, the return value is Sqlite_busy, Sqlite_done, Sqlite_row, Sqlite_error, or Sqlite_misuse, and the V2 version of the interface Sqlite3_prepare_v2 () and SQLITE3_PREPARE16_V2 () return both these result codes and extended result codes.

For all V3.6.23.1 and all versions preceding it, it is necessary to call Sqlite3_reset () after Sqlite3_step (), before the subsequent sqlite3_ step. Failure to call Sqlite3_reset to reset the prepare statement will cause sqlite3_ step to return Sqlite_misuse, but in V3. After 6.23.1, Sqlite3_step () will automatically call Sqlite3_reset.

int Sqlite3_reset (sqlite3_stmt *pstmt);

Sqlite3_reset is used to reset a prepared statement object to its initial state, and then prepares to be re-executed. All SQL statement variables use the sqlite3_bind* bound value, and the bindings are reset using Sqlite3_clear_bindings. The Sqlite3_reset interface resets the prepared statement to the beginning of its code. Sqlite3_reset does not change any of the binding values on the prepared statement, then it is assumed that the statement may have undergone other changes during execution, and then the statement resets it to the state of the bound value.

4. Sqlite3_column ()

This process returns a column from the current row of the result set that executes the sqlite3_step () execution of a prepared statement. Each time Sqlite3_step gets a column for a result set, the process can be called multiple times to query the values of the columns of the row. There are multiple functions for column operations, all prefixed with sqlite3_column

const void *sqlite3_column_blob (sqlite3_stmt*, int icol);

int sqlite3_column_bytes (sqlite3_stmt*, int icol);

int SQLITE3_COLUMN_BYTES16 (sqlite3_stmt*, int icol);

Double sqlite3_column_double (sqlite3_stmt*, int icol);

int Sqlite3_column_int (sqlite3_stmt*, int icol);

Sqlite3_int64 Sqlite3_column_int64 (sqlite3_stmt*, int icol);

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

const void *sqlite3_column_text16 (sqlite3_stmt*, int icol);

int Sqlite3_column_type (sqlite3_stmt*, int icol);

Sqlite3_value *sqlite3_column_value (sqlite3_stmt*, int icol);

Description

The first parameter is a pointer to the prepared statement object returned from Sqlite3_prepare, and the second parameter specifies the index of the column in this row that you want to return. The leftmost column has an index number of 0, and the number of columns in the row can be obtained using Sqlite3_colum_count ().

These processes convert the type of the numeric value according to the situation, and SQLite uses sqlite3_snprintf () to automate the conversion, and here is a detailed table of conversions:

Internal type	Type of request	Transformation
Null	INTEGER	The result is 0.
Null	FLOAT	The result is 0.0.
Null	TEXT	The result is null
Null	Blob	The result is null
INTEGER	FLOAT	Converting from shaping to floating-point
INTEGER	TEXT	ASCII code display for shaping
INTEGER	Blob	Ditto
FLOAT	INTEGER	Floating-point conversion to shaping
FLOAT	TEXT	ASCII display of floating-point type
FLOAT	Blob	Ditto
TEXT	INTEGER	Using Atoi ()
TEXT	FLOAT	Using Atof ()
TEXT	Blob	No conversions
Blob	INTEGER	Go to text first, then use Atoi
Blob	FLOAT	Go to text first, then use Atof
Blob	TEXT	Add 0 Terminator if needed

Note: BLOB data type refers to binary data blocks, such as to store a picture in the database, this picture will be stored in binary form, the corresponding data type in SQLite is the blob

int sqlite3_column_bytes (sqlite3_stmt*, int icol) int sqlite3_column_bytes16 (sqlite3_stmt*, int icol) Two functions returns the number of bytes for the contents of the corresponding column , the number of bytes does not include the 0 terminator that was added to the subsequent type conversion process.

Here are a few of the safest and simplest usage strategies

• First Sqlite3_column_text (), then Sqlite3_column_bytes ()
• First Sqlite3_column_blob (), then Sqlite3_column_bytes ()
• First sqlite3_column_text16 (), then SQLITE3_COLUMN_BYTES16 ()

5. Sqlite3_finalize

int sqlite3_finalize (sqlite3_stmt *pstmt);

This process destroys the prepared statements that were previously created by Sqlite3_prepare, and each prepared statement must use this function to destroy to prevent memory leaks.

Calling this function on a null pointer has no effect, and can be prepared at any point in the life cycle of the statement to invoke the function: After the statement is executed, after calling Sqlite_reset one or more times, or after sqlite3_step any call, regardless of whether the statement completes execution

6. Sqlite3_close

This process closes the database connection that was previously opened using Sqlite3_open, and any prepared statement related to the connection must be released before the shutdown function is called

Interface description for lightweight database SQLite