SQLLite (三)：sqlite3_prepare_v2,sqlite3_step

如果既不想写回调函数，又想避免 sqlite3_get_table之后麻烦 的一维数组遍历，那么利用sqlite3_prepare_v2执行sql select语句，让后sqlite3_step遍历select执行的返回结果是一个非常方便的solution. 当然，你必须要明白sqlite3_prepare_v2不仅仅能够执行table的query selection，也能方便地进行sql Delete, Insert, Update等其他一些操作。它能帮你把sql语句的执行操作变的更加优雅。

int sqlite3_prepare_v2(
  sqlite3 *db,            <span style="color:#009900;">/* Database handle */</span>
  const char *zSql,       <span style="color:#009900;">/* SQL statement, UTF-8 encoded */</span>
  int nByte,              <span style="color:#009900;">/* Maximum length of zSql in bytes. */</span>
  sqlite3_stmt **ppStmt,  <span style="color:#009900;">/* OUT: Statement handle */</span>
  const char **pzTail     <span style="color:#009900;">/* OUT: Pointer to unused portion of zSql */</span>
);

int sqlite3_step(sqlite3_stmt*);

下面以一段iOS中的selection查询为例说明二者的用法：

-(void)InitializeFilesTable
{
    const char * query = "SELECT * FROM [FileObjects]";
    sqlite3_stmt * stmt;
    int result = sqlite3_prepare_v2(mDatabase, query, -1, &stmt, NULL);
    if(SQLITE_OK != result)
    {
        sqlite3_finalize(stmt);
        <span style="color:#009900;">// The table has not been created. Most likely, this is the first time we create the database.
        // Therefore, create all tables in it</span>
        char * sql = "Create TABLE [FileObjects] ([FileId] VARCHAR(128),[FileExt] VARCHAR(128), [FileName] VARCHAR(128), [FileUrl] VARCHAR(128), [FileType] INT  );"; <span style="color:#009900;">// NOXLATE</span>
        char * errMsg;
        result = sqlite3_exec(mDatabase, sql, NULL, NULL, &errMsg);
    }
    else
    {
        <span style="color:#009900;">// Select returns OK, initialize the memory model from the result</span>
        NSMutableDictionary * files = [NSMutableDictionary new];
        while(sqlite3_step(stmt) == SQLITE_ROW)
        {
            FileObject * file = [FileObject new];
            
            const char * str = (const char *)sqlite3_column_text(stmt, 0);
            file.FileId = str? [[NSString alloc] initWithUTF8String:str] : @"";
            
            str = (const char *)sqlite3_column_text(stmt, 1);
            file.FileExt = str? [[NSString alloc] initWithUTF8String:str] : @"";
            
            str = (const char *)sqlite3_column_text(stmt, 2);
            file.FileName = str? [[NSString alloc] initWithUTF8String:str] : @"";
            
            str = (const char *)sqlite3_column_text(stmt, 3);
            file.FileUrl = str? [[NSString alloc] initWithUTF8String:str] : @"";
            
            file.FileType = sqlite3_column_int(stmt, 4);
            
            [files setObject:file forKey:file.FileId];
        }
        
        sqlite3_finalize(stmt);
        [mFiles setDictionary:files];
    }
}

这其中包括对sqlite3_exec的调用。sqlite3_exec可以执行任何sql语句，包括事务( "BEGIN TRANSACTION" )、回滚( "ROLLBACK")和提交( "COMMIT")等等。