SQlite資料庫的C編程介面（三） 預先處理語句（Prepared Statements） ——《Using SQlite》讀書筆記 .

SQlite3資料庫連接完成之後，就可以執行SQL命令了。下面將要介紹的prepare和step函數都是用來操作和執行SQL命令的。

典型的函數操作流程（虛擬碼）：

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

這段程式首先調用sqlite3_prepare_v2函數，將一個SQL命令字串轉換成一條prepared語句，儲存在sqlite3_stmt類型結構體中。隨後調用sqlite3_bind_xxx函數給這條prepared語句綁定參數。然後不停的調用sqlite3_step函數執行這條prepared語句，擷取結果集中的每一行資料，從每一行資料中調用qlite3_column_xxx函數擷取有用的列資料，直到結果集中所有的行都被處理完畢。

prepared語句可以被重設（調用sqlite3_reset函數），然後可以重新綁定參數之後重新執行。sqlite3_prepare_v2函數代價昂貴，所以通常儘可能的重用prepared語句。最後，這條prepared語句確實不在使用時，調用sqlite3_finalize函數釋放所有的內部資源和sqlite3_stmt資料結構，有效刪除prepared語句。

預先處理（Prepare）

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

這些函數的作用是將SQL命令字串轉換為prepared語句。參數db是由sqlite3_open函數返回的指向資料庫連接的指標。參數zSql是UTF-8或者UTF-16編碼的SQL命令字串，參數nByte是zSql的位元組長度。如果nByte為負值，則prepare函數會自動計算出zSql的位元組長度，不過要確保zSql傳入的是以NULL結尾的字串。如果SQL命令字串中只包含一條SQL語句，那麼它沒有必要以“;”結尾。參數ppStmt是一個指向指標的指標，用來傳回一個指向建立的sqlite3_stmt結構體的指標，sqlite3_stmt結構體裡面儲存有轉換好的SQL語句。如果SQL命令字串包含多條SQL語句，同時參數pzTail不為NULL，那麼它將指向SQL命令字串中的下一條SQL語句。上面4個函數中的v2版本是加強版，與原始版函數參數相同，不同的是函數內部對於sqlite3_stmt結構體的表現上。細節不去理會，盡量使用v2版本。

在sqlite3_prepare函數轉換一條語句完畢之後，可以給這條語句綁定參數。語句參數允許我們插入一個特殊的預留位置，然後給這個參數預留位置綁定指定的值，然後再執行它。執行完成之後，還可以重設語句，綁定新的參數值，再重新執行該語句。像INSERT操作，每次執行INSERT命令，綁定不同的值，插入不同的資料。參數綁定是一個有點複雜的深入話題，在下一節中學習。

步進（Step）

int sqlite3_step(sqlite3_stmt*);

sqlite3_prepare函數將SQL命令字串解析並轉換為一系列的命令位元組碼，這些位元組碼最終被傳送到SQlite3的虛擬資料庫引擎（VDBE: Virtual Database Engine）中執行，完成這項工作的是sqlite3_step函數。比如一個SELECT查詢操作，sqlite3_step函數的每次調用都會返回結果集中的其中一行，直到再沒有有效資料行了。每次調用sqlite3_step函數如果返回SQLITE_ROW，代表獲得了有效資料行，可以通過sqlite3_column函數提取某列的值。如果調用sqlite3_step函數返回SQLITE_DONE，則代表prepared語句已經執行到終點了，沒有有效資料了。很多命令第一次調用sqlite3_step函數就會返回SQLITE_DONE，因為這些SQL命令不會返回資料。對於INSERT，UPDATE，DELETE命令，會返回它們所修改的行號——一個單行單列的值。

結果列（Result Columns）

int sqlite3_column_count(sqlite3_stmt *pStmt);

返回結果集的列數。

const char *sqlite3_column_name(sqlite3_stmt*, int N);  const void *sqlite3_column_name16(sqlite3_stmt*, int N);  

返回結果集中指定列的列名，列的序號以0開始。比如一條SQL語句：SELECT pid AS person_id...，那麼調用sqlite3_column_name函數返回結果集中第0列的列名就是person_id。返回的字串指標將一直有效，直到再次調用sqlite3_column_name函數並再次讀取該列的列名時失效。

int sqlite3_column_type(sqlite3_stmt*, int iCol);  

該函數返回結果集中指定列的本機存放區類型，如SQLITE_INTEGER，SQLITE_FLOAT，SQLITE_TEXT，SQLITE_BLOB，SQLITE_NULL。為了擷取正確的類型，該函數應該在任何試圖提取資料的函數調用之前被調用。SQlite3資料庫允許不同類型的資料存放區在同一列中，所以對於不同行的相同索引的列調用該函數擷取的列類型可能會不同。

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

返回一個指標，指向給定列的BLOB類型值。

double sqlite3_column_double(sqlite3_stmt*, int iCol);  

從給定列返回一個64位浮點值。

int sqlite3_column_int(sqlite3_stmt*, int iCol);  

從給定列返回一個32位有符號整數，如果該列中包含的整型值無法用32位元值表示，那它將會在沒有任何警告的情況下被截斷。

sqlite3_int64 sqlite3_column_int64(sqlite3_stmt*, int iCol);  

從給定列返回一個64位有符號整數。

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

返回一個指標，指向給定列的UTF-8或者UTF-16編碼的字串，該字串以NULL結尾。

sqlite3_value *sqlite3_column_value(sqlite3_stmt*, int iCol);  

返回一個指標，指向一個無保護的sqlite3_value結構，該結構無法進行安全的資料類型轉換，所以無法調用sqlite3_value_xxx函數從這個結構體中提取原始數值。如果想提取原始數值，只能調用其它的sqlite3_column_xxx函數。對於該函數返回的指標，安全的用法是以它為參數調用sqlite3_bind_value函數給一個prepared語句綁定參數，或者以它為參數調用sqlite3_result_value函數得到一個使用者自訂的SQL函數的傳回值。

對於這些sqlite3_column_xxx函數返回的指標，當再次調用sqlite3_column_xxx函數並操作相同的列的時失效，或者在sqlite3_step、sqlite3_reset、sqlite3_finalize等函數調用之後失效。

如果提取列值時使用的sqlite3_column_xxx函數版本與原始值的本機資料類型不同，SQlite資料庫將進行轉換。轉換原則：

 

 

int sqlite3_value_bytes(sqlite3_value*);int sqlite3_value_bytes16(sqlite3_value*);

對於BLOB和text類型，sqlite3_column_blob和sqlite3_column_text函數將會返回一個buffer指標。通過sqlite3_value_bytes函數可以得到buffer位元組長度，對於text類型，這個位元組長度將包括一個字串結尾符。

需要注意的是：假如先調用sqlite3_column_text函數擷取一個指向UTF-8編碼的字串指標，之後又調用了sqlite3_column_bytes16在相同的列上擷取buffer大小，那麼該列的字串將會從UTF-8編碼轉換為UTF-16編碼，導致之前由sqlite3_column_text函數返回的指標失效。

正確做法是提取值時的函數和擷取值buffer大小的函數，以相同類型匹配使用，如：

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

sqlite3_step函數調用返回SQLITE_DONE時，則代表這條語句已經完成執行，這時如果還想重用這條prepared語句，就需要調用sqlite3_reset函數進行重設。或者，比如我們只想提取結果集的前六行資料，那麼我們就可以連續調用6次sqlite3_step函數，之後調用sqlite3_reset函數重設prepared語句，以備下一次使用。

int sqlite3_finalize(sqlite3_stmt *pStmt);  

銷毀prepared語句，釋放資源。在關閉資料庫連接之前，對於不再使用的prepared語句，一定要調用sqlite3_finalize函數進行銷毀，

語句（狀態）轉換（Statement Transitions）

一條語句可以處於不同狀態，對於一條新的或者剛剛被reset的語句，它們處於“ready”狀態，代表它們已經準備好執行，但還沒有開始執行。一條語句也有可能處於“running”狀態，表明這條語句已經開始執行，但還沒有完成。還有一種狀態叫做“done”，表明一條語句已經執行完成。

對於有些API函數，只能在某條語句處於特定狀態下才可以執行，比如sqlite3_bind_xxx函數，只有在一條語句處於“ready”狀態時才可以被調用，否則函數將會返回SQLITE_MISUSE錯誤碼。展示了一條語句所處於的不同狀態，以及不同狀態之間是如何轉換的。

範例程式碼

（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 ... */

CREATE TABLE語句沒有傳回值，調用sqlite3_step函數執行這條語句，最後在關閉資料庫之前調用sqlite3_finalize銷毀這條語句。

（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 ... */

這段代碼迴圈提取表tbl的所有行，並把每一行第0列值列印出來。