SQLite Study Notes (6)

Advantages of preparing for query:

Callback interfaces are not required, and the encoding is simple and clear. functions that provide information are associated to obtain the column storage type, declaration type, mode name, table name, and database name. sqlite3_exec () the interface only provides the column name. The preparation query provides a method to obtain fields and column values outside the text, which can be obtained in the C data type. The preparation query can be run again, you can reuse compiled SQL statements. Prepare SQL statements that support parameterization.

 

Check for changes:

You can obtain the number of affected records from sqlite3_changes () and provide the number of records affected by the last update, insert, or delete statement. Does not contain the results of the original command trigger. The final field information can be obtained through sqlite3_last_inert_rowid.

 

Query tables:

Function: sqlite3_get_table () returns the result set of a command in a separate function call. It encapsulates sqlite3_exec () and returns data as much as possible, making it easier to obtain records.

Function declaration:

Int sqlite3_get_table (SQLite *,/* opened database */

Const char * SQL,/* SQL statement to be executed */

Char *** resultp,/* The result is written to the char * [] */

Int * nrow,/* number of rows in the result set */

Int * ncolumn,/* Number of columns in the result set */

Char ** errmsg/* error message */

);

The function receives all records returned by SQL statements, uses the heap lock declared memory, and uses sqlite3_malloc () to store in resultp. You must use sqlite3_free_table () to release the memory. The unique parameter of this function is the resultp pointer.

 

Query preparation:

Each query function has its own row and column acquisition method. Compared with the preparation query, sqlite3_exec () and sqlite3_get_table () provide fewer functions. It is worth mentioning that sqlite3_exec () is suitable for running commands to modify databases, A function call can complete the work.

Prepare to query columns and rows using a set of special functions. The column value can be obtained using the sqlite3_column_xxx () function. XXX is the data type of the returned value;

The three steps for preparing a query are compilation, execution, and completion.

Sqlite3_prepare_v2 (): Compile the query. The query is executed by sqlite3_step () distribution, and sqlite3_finalizer () is used to close the query. Sqlite3_reset () can be reused for compilation.

Compile:

SQL statement → vbde bytecode. Completed by sqlite3_prepare_v2.

Function declaration: int sqlite3_prepare_v2 (

SQLite * dB,/* opened database */

Const char * zsql,/* SQL text */

Int nbytes,/* zsql byte length */

Sqlite3_stmt ** ppstmt,/* Statement handle */

Const char ** pztail/* points to unused parts of zsql */

);

Allocate all the resources required to execute the statement, and associate the bytecode with a single statement handle. The parameter ppstmt (the struct of sqlite3_stmt) is specified. This data structure contains the command bytecode, bound parameters, B-tree cursor, execution context, and other data required for sqlite3_step () to query the status during execution. Sqlite3_prepare_v2 () does not affect the connection or database, nor start transactions or obtain locks. He can work directly through the compiler without executing the query preparation. The statement handle is highly dependent on the compiled database mode.
The changes to the database mode may affect the preparation of the query statement. automatic re-compilation may be enabled. When re-compilation fails, the sqlite3_step () function returns the sqlite_schema error. In this case, sqlite3_errmsg () is required to check for errors ().

Run:

The premise is that the query statement is ready and executed using sqlite3_step (). Declaration: int sqlite3_step (sqlite3_stmt * pstmt );

Sqlite3_step () directly accepts the statement handle to communicate with vdbe and generates the vdbe bytecode.

When the first time sqlite3_step () D is called, vdbe obtains a database lock for executing commands. If the lock cannot be obtained and there is no busy processing program, such as the mail service, the sqlite_busy error will be returned. When there is a busy processing program, it will be called.

If the SQL statement does not return data, the first call will complete the work and return the indicative code of the completion result. To return data, the first call locates the Statement on the B-tree cursor of the first record, and sqlite3_step () of the subsequent call locates the cursor on the subsequent record of the result set. Before arriving at the end, sqlite3_step () returns sqlite_row for each record in the result set. If sqlite_down () is returned, the cursor reaches the end of The result set.

Completed and reset:

Function: int sqlite3_finalize (sqlite3_stmt * pstmt );

Int sqlite3_reset (sqlite3_stmt * pstmt );

Sqlite3_finalize () closes the statement, releases the resource, commits or rolls back any implicit transactions, clears the log file, and releases the associated lock.

Sqlite3_reset () retains compiled SQL statements by resetting duplicate statements, but submits changes to the current statements to the database. After automatic submission is started, log files are released and locked to clear. Compared with sqlite3_finalize (), sqlite3_finalize () retains the resource related to the statement and can be executed repeatedly, avoiding multiple calls to sqlite3_prepare.

Function: sqlite3_complete () is a tool proposed by the prompt.

 

Retrieve records:

Functions: sqlite3_column_count () and sqlite3_data_count ();

Function declaration:

Int sqlite3_column_count (sqlite3_stmt * pstmt); returns the number of fields related to the statement handle;

Int sqlite3_column_count (sqlite3_stmt * pstmt); after sqlite3_step () returns sqlite_row, this function returns the number of columns in the current record, but requires an active cursor on the statement handle.

 

Obtain field information:

Function sqlite3_column_name ();

Function declaration: const char * sqlite_column_name (

Sqlite3_stmt *,/* Statement handle */

Int icol,/* field order */

);

Obtain the associated storage classes for all fields in the current record:

Function declaration: int sqlite3_column_type (

Sqlite3_stmt *,/* Statement handle */

Int icol;,/* field order */

);

This function returns the integer (native data type or storage class) corresponding to the five storage classes ):

# Define sqlite_integer 1;

# Define sqlite_float 2;

# Define sqlite_text 3;

# Define sqlite_blob 4;

# Define sqlite_null 5;

You can use the sqlite3_column_decltype () function to obtain the data type declared by the field in Table mode definition. function declaration:

Const char * sqlite3_column_decltype (

Sqlite3_stmt *,/* Statement handle */

Int;,/* field order */

);

Obtain the field value:

Sqlite3_column_xxx () returns the field value. General Form:

Xxx sqlite3_column_xxx (

Sqlite3_stmt *,/* Statement handle */

Int,/* field order */

);

The most commonly used sqlite3_column_xxx () function:

Int sqlite3_column_int (sqlite3_stmt *, int icol );

Double sqlite3_column_double (sqlite3_stmt *, int icol );

Long long int sqlite3_column_int64 (sqlite3_stmt *, int icol );

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

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

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

Column types can be converted to each other. For specific conversion rules, refer to the SQLite authoritative guide page187.

For blob columns, the length must be specified during replication. After obtaining the length, use sqlite3_column_blob () to copy binary data.