C Programming Interface of SQLite database (3) prepared statement (Prepared statements) -- Reading Notes of using SQLite.

After the sqlite3 database is connected, you can execute the SQL command. The prepare and step functions described below are used to operate and execute SQL commands.

Typical function operation process (pseudo code ):

/ * create a statement from an SQL string * /
sqlite3_stmt * stmt = NULL;
sqlite3_prepare_v2 (db, sql_str, sql_str_len, & stmt, NULL);

/ * use the statement as many times as required * /
while (...)
{
    / * bind any parameter values * /
    sqlite3_bind_xxx (stmt, param_idx, param_value ...);
    ...
    / * execute statement and step over each row of the result set * /
    while (sqlite3_step (stmt) == SQLITE_ROW)
    {
        / * extract column values from the current result row * /
        col_val = sqlite3_column_xxx (stmt, col_index);
        ...
    }

    / * reset the statement so it may be used again * /
    sqlite3_reset (stmt);
    sqlite3_clear_bindings (stmt); / * optional * /
}

/ * destroy and release the statement * /
sqlite3_finalize (stmt);
stmt = NULL;
This program first calls the sqlite3_prepare_v2 function to convert a SQL command string into a prepared statement and stores it in a sqlite3_stmt type structure. Then call sqlite3_bind_xxx functions to bind parameters to this prepared statement. Then call the sqlite3_step function continuously to execute this prepared statement, get each row of data in the result set, and call the qlite3_column_xxx function from each row of data to obtain useful column data, until all the rows in the result set are processed.

The prepared statement can be reset (calling the sqlite3_reset function), and then it can be re-executed after rebinding parameters. The sqlite3_prepare_v2 function is expensive, so it is common to reuse prepared statements whenever possible. Finally, when this prepared statement is not in use, the sqlite3_finalize function is called to release all internal resources and the sqlite3_stmt data structure, effectively deleting the prepared statement.

Pre-processing

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 * /
);

int sqlite3_prepare16 (
  sqlite3 * db, / * Database handle * /
  const void * zSql, / * SQL statement, UTF-16 encoded * /
  int nByte, / * Maximum length of zSql in bytes. * /
  sqlite3_stmt ** ppStmt, / * OUT: Statement handle * /
  const void ** pzTail / * OUT: Pointer to unused portion of zSql * /
);

int sqlite3_prepare16_v2 (
  sqlite3 * db, / * Database handle * /
  const void * zSql, / * SQL statement, UTF-16 encoded * /
  int nByte, / * Maximum length of zSql in bytes. * /
  sqlite3_stmt ** ppStmt, / * OUT: Statement handle * /
  const void ** pzTail / * OUT: Pointer to unused portion of zSql * /
);
These functions are used to convert SQL command strings into prepared statements. The parameter db is a pointer to the database connection returned by the sqlite3_open function. The parameter zSql is a SQL command string encoded in UTF-8 or UTF-16, and the parameter nByte is the byte length of zSql. If nByte is negative, the prepare function automatically calculates the byte length of zSql, but make sure that zSql passes in a NULL-terminated string. If the SQL command string contains only one SQL statement, it does not need to end with ";". The parameter ppStmt is a pointer to a pointer, which is used to return a pointer to the newly created sqlite3_stmt structure. The sqlite3_stmt structure holds the converted SQL statement. If the SQL command string contains multiple SQL statements and the parameter pzTail is not NULL, it will point to the next SQL statement in the SQL command string. The v2 version of the above 4 functions is an enhanced version, which has the same parameters as the original version, except that the internal performance of the function for the sqlite3_stmt structure is different. Ignore the details, try to use the v2 version.

After the sqlite3_prepare function converts a statement, you can bind parameters to the statement. Statement parameters allow us to insert a special placeholder, then bind the parameter placeholder to the specified value, and then execute it. After execution is complete, you can also reset the statement, bind new parameter values, and re-execute the statement. Like the INSERT operation, each time you execute the INSERT command, you bind different values and insert different data. Parameter binding is a somewhat complicated in-depth topic, which you will learn in the next section.

Step

int sqlite3_step (sqlite3_stmt *);
The sqlite3_prepare function parses and converts the SQL command string into a series of command bytecodes. These bytecodes are eventually transmitted to the SQlite3 virtual database engine (VDBE: Virtual Database Engine) for execution. . For example, a SELECT query operation, each call to the sqlite3_step function will return one row in the result set, until there are no more valid data rows. Each time the sqlite3_step function is called, if SQLITE_ROW is returned, it means that a valid data row has been obtained. You can use the sqlite3_column function to extract the value of a column. If calling sqlite3_step function returns SQLITE_DONE, it means that the prepared statement has been executed to the end, and there is no valid data. Many commands return SQLITE_DONE the first time they call the sqlite3_step function, because these SQL commands do not return data. For INSERT, UPDATE, and DELETE commands, they will return the row number they modified-a single row and single column value.

Result Columns

int sqlite3_column_count (sqlite3_stmt * pStmt);
Returns the number of columns in the result set.

const char * sqlite3_column_name (sqlite3_stmt *, int N);
const void * sqlite3_column_name16 (sqlite3_stmt *, int N);
Returns the column name of the specified column in the result set. The sequence number of the column starts with 0. For example, a SQL statement: SELECT pid AS person_id ..., then calling the sqlite3_column_name function returns the column name of the 0th column in the result set is person_id. The returned string pointer will remain valid until invalidated when the sqlite3_column_name function is called again and the column name of the column is read again.

int sqlite3_column_type (sqlite3_stmt *, int iCol);
This function returns the local storage type of the specified column in the result set, such as SQLITE_INTEGER, SQLITE_FLOAT, SQLITE_TEXT, SQLITE_BLOB, SQLITE_NULL. In order to get the correct type, this function should be called before any function call that tries to extract data. The SQlite3 database allows different types of data to be stored in the same column, so calling this function for columns with the same index in different rows may obtain different column types.

const void * sqlite3_column_blob (sqlite3_stmt *, int iCol);
Returns a pointer to a BLOB value of the given column.

double sqlite3_column_double (sqlite3_stmt *, int iCol);
Returns a 64-bit floating point value from the given column.

int sqlite3_column_int (sqlite3_stmt *, int iCol);
Returns a 32-bit signed integer from the given column. If the integer value contained in the column cannot be represented as a 32-bit value, it will be truncated without any warning.

sqlite3_int64 sqlite3_column_int64 (sqlite3_stmt *, int iCol);
Returns a 64-bit signed integer from the given column.

const unsigned char * sqlite3_column_text (sqlite3_stmt *, int iCol);

const void * sqlite3_column_text16 (sqlite3_stmt *, int iCol);
Returns a pointer to a UTF-8 or UTF-16 encoded string for a given column, the string ending in NULL.

sqlite3_value * sqlite3_column_value (sqlite3_stmt *, int iCol);
Returns a pointer to an unprotected sqlite3_value structure. This structure cannot perform safe data type conversion, so the sqlite3_value_xxx function cannot be called to extract the original value from this structure. If you want to extract the original value, you can only call other sqlite3_column_xxx functions. For the pointer returned by this function, the safe usage is to call sqlite3_bind_value function with it as a parameter to bind a prepared statement, or call sqlite3_result_value function with it as a parameter to get the return of a user-defined SQL function value.

For the pointers returned by these sqlite3_column_xxx functions, they fail when the sqlite3_column_xxx functions are called again and operate on the same columns, or after sqlite3_step, sqlite3_reset, sqlite3_finalize and other function calls are invalidated.

If the version of the sqlite3_column_xxx function used to extract the column value is different from the native data type of the original value, the SQlite database will convert it. Conversion principle:

 

 

int sqlite3_value_bytes (sqlite3_value *);
int sqlite3_value_bytes16 (sqlite3_value *);
For BLOB and text types, the sqlite3_column_blob and sqlite3_column_text functions will return a buffer pointer. The buffer byte length can be obtained through the sqlite3_value_bytes function. For the text type, this byte length will include a string terminator.

It should be noted that if you first call the sqlite3_column_text function to obtain a pointer to a UTF-8 encoded string, and then call sqlite3_column_bytes16 to obtain the buffer size on the same column, then the string in this column will be converted from UTF-8 encoding. It is encoded in UTF-16, causing the pointer previously returned by the sqlite3_column_text function to become invalid.

The correct way is to use the function of extracting the value and the function of obtaining the size of the value buffer to match the same type, such as:

/ * correctly extract a blob * /
buf_ptr = sqlite3_column_blob (stmt, n);
buf_len = sqlite3_column_bytes (stmt, n);

/ * correctly extract a UTF-8 encoded string * /
buf_ptr = sqlite3_column_text (stmt, n);
buf_len = sqlite3_column_bytes (stmt, n);

/ * correctly extract a UTF-16 encoded string * /
buf_ptr = sqlite3_column_text16 (stmt, n);
buf_len = sqlite3_column_bytes16 (stmt, n);
Reset and Finalize

int sqlite3_reset (sqlite3_stmt * pStmt);
When the sqlite3_step function call returns SQLITE_DONE, it means that the statement has completed execution. At this time, if you want to reuse the prepared statement, you need to call the sqlite3_reset function to reset it. Or, for example, if we only want to extract the first six rows of the result set, then we can call the sqlite3_step function 6 times in succession, and then call the sqlite3_reset function to reset the prepared statement for the next use.

int sqlite3_finalize (sqlite3_stmt * pStmt);
Destroy the prepared statement and release resources. Before closing the database connection, for prepared statements that are no longer used, be sure to call the sqlite3_finalize function to destroy,

Statement (statement) transitions

A statement can be in different states. For a new or newly reset statement, they are in the "ready" state, indicating that they are ready to execute, but have not yet started execution. A statement may also be in the "running" state, indicating that the statement has started execution but has not yet completed. There is also a state called "done", which indicates that a statement has completed execution.

Some API functions can only be executed when a certain statement is in a specific state. For example, the sqlite3_bind_xxx function can only be called when a statement is in the "ready" state, otherwise the function will return an SQLITE_MISUSE error code. Shows the different states a statement is in, and how the states transition between them.

Sample code

(1)

sqlite3_stmt * stmt = NULL;
/ * ... open database ... * /
 

rc = sqlite3_prepare_v2 (db, "CREATE TABLE tbl (str TEXT)", -1, & stmt, NULL);
if (rc! = SQLITE_OK) exit (-1);
rc = sqlite3_step (stmt);
if (rc! = SQLITE_DONE) exit (-1);

sqlite3_finalize (stmt);
/ * ... close database ... * /
The CREATE TABLE statement has no return value. Call the sqlite3_step function to execute this statement. Finally, call sqlite3_finalize to destroy this statement before closing the database.

(2)

const char * data = NULL;
sqlite3_stmt * stmt = NULL;
/ * ... open database ... * /
rc = sqlite3_prepare_v2 (db, "SELECT str FROM tbl ORDER BY 1", -1, & stmt, NULL);
if (rc! = SQLITE_OK) exit (-1);

while (sqlite3_step (stmt) == SQLITE_ROW) {
    data = (const char *) sqlite3_column_text (stmt, 0);
    printf ("% s \ n", data? data: "[NULL]");
}

sqlite3_finalize (stmt);
/ * ... close database ... * /
This code loops through all the rows of the table tbl and prints the value of column 0 of each row.