SQLite Study Notes (7)

First, pay attention to handle Association. Next, let's look at the query parameterization content.

Query parameterization:

API supports binding parameters in SQL statements and providing values for parameters later. The bound parameters are used together with sqlite3_prepare. If no parameter is bound, sqlite3_step () uses null as the parameter value by default.

After the statement is prepared, the sqlite3_bind_xxx () function is used to bind the parameter value.

Function declaration: sqlite3_bind_xxx (

Sqlite3_stmt *,/* Statement handle */

Int I,/* Number of parameters */

Xxx value,/* bound value */

);

Binding declaration for different types of parameters:

Int sqlite3_bind_xxx (sqlite3_stmt *, Int, XXX). xxx indicates int, double, int64, null, zeroblob, blob, text, and text16. The first four values are used as scalar values, the last three are used for arrays. The sqlite3_mprintf () function automatically transfers the quotation mark characters in sqlite3_bind_text. Blob variant type:

Int sqlite3_bind_bolb (

Sqlite3_stmt *;/* Statement handle */

Int,/* Order */

Const void *,/* point to BLOB Data */

Int N,/** Data byte length/

Void (* (void *),/* clear byte Program */

);

The predefined values of the two special meanings provided by the API for clearing the handle:

# Define sqlite_static (void (*) (void *) 0) indicates that the top Function Array Memory stays in the unmanaged space, and SQLite will not try to clear the space

# Define sqlite_transient (void (*) (void *)-1) indicates that the Array Memory frequently changes. SQLite needs its own data copy. The copy is automatically cleared at the end of the statement.

Cleanup function: void cleanup_fn (void *). If an automatic cleanup function is provided, it is automatically called at the end of the statement.

After the parameter is bound, sqlite3_step () accepts the top value of the parameter helper, replaces the SQL text, and runs the statement.

The difference between the four parameter binding methods is the parameter representation (location parameter, explicitly defining the function number, letter Digital Number name), and parameter Allocation Number.

Parameter number: allows you to specify a number for a parameter without using an internal sequence. The syntax of the parameter number is that the question mark is followed by a number.

For example, insert into table_name values (? Num ,? Num ,? Num ......); The value of num ranges from 1 to 999, and the smaller the value, the better. When num is the same, it can save time.

Parameter Name: bind a name to the parameter in the specified parameter number. The parameter number is prefixed with a question mark, indicating that the name parameter is preceded by a colon (:) or the @ symbol.

For example, insert into table_name values (: value,: value, @ value); sqlite3_prepare () is automatically assigned a number for the named parameter. Although the number is unknown, you can use the sqlite3_bind_parameter_index () function () obtain the ID.

TCL parameter: use the $ symbol.

Errors and exceptions:

Error handling:

The API may return an incorrect integer result code. For example: Commit (), commit () sqlite3_exec (), sqlite3_bind_xxx (), sqlite3_close (), commit (), commit (), sqlite3_create_function (), commit (), commit (), sqlite3_reset ()
And sqlite3_step.

Function for obtaining incorrect function information: sqlite3_errmsg ().

Statement: const char * sqlite3_errmsg (sqlite3 *); the statement handle is a unique parameter and returns the most recent error generated by the API call on this link. If no error exists, "not an error" is returned ".

SQLite result code: "SQLite authoritative guide" page195.

Handling of Busy Conditions: sqlite3_busy_handler (), sqlite3_busy_timeout (). Pay attention to the start of the emergency plan and set a reasonable timeout time.

Pattern Change processing: From the locking point of view, the pattern change time is between sqlite3_prepare () call and sqlite3_step () call. The solution is to repeat the changes. Causes of sqlite_schema: detach, modify, or install user-defined functions or aggregate, modify or install User-Defined sorting rules, modify or install authorization functions, and clear database space. This error is related to vdbe.

Trace SQL: Use the sqlite3_trace () function ().

Function declaration: void * sqlite3_trace (SQLite *, void (* xtrace) (void *, const char *), void *); the function is similar to that of a bug.

Operation Control:

It is basically a function that monitors database connections and transactions.

Submit HOOK: sqlite3_commit_hook () monitors transaction commit events on the connection.

Declaration: void * sqlite_commit_hook (

SQLite * CNX,/* database handle */

INT (* xcallback) (void * data),/* callback function */

Void * data,/* Application Data */

);

Rollback HOOK: sqlite3_rollback_hook () monitors transaction commit events on the connection.

Declaration: void * sqlite_rollback_hook (

SQLite * CNX,/* database handle */

INT (* xcallback) (void * data),/* callback function */

Void * data,/* Application Data */

);

Note that automatic rollback cannot trigger the callback function.

Update HOOK: sqlite3_update_hook () monitors transaction commit events on the connection.

Declaration: void * sqlite_update_hook (

SQLite * CNX,/* database handle */

INT (* xcallback) (void * data),/* callback function */

Void * data,/* Application Data */

);

In this case, the row type of the callback function is void callback (void * data,/* update the third parameter of the hook */

Int operation_code,/* Corresponds to sqlite_update, sqlite_insert, and sqlite_delete operations */

Char const * db_name,

Char const * table_name,

Sqlite_int64 rowid,

);

Authorization function:

Event Filter: sqlite3_set_authorizer ()

Declaration: int sqlite3_set_authorizer (

Sqlite3 *,

INT (* XAUTH) (void *, Int,

Const char *, const char *,

Const char *, const char *,

),

Void * puserdata;

);

Note: The authorization function has a callback function:

Int auth (

Void *,/* User Data */

Int,/* Event code */

Const char *,/* parameters related to the event */

Const char *,/* parameters related to the event */

Const char *,/* database name */

Const char *,/* trigger or view name */

)

Return values of the authorization function: sqlite_ OK, sqlite_deny (end the entire SQL statement), and sqlite_ignore. Pay attention to the help of interactive programs. Page209.