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语句的执行操作变的更加优雅。
[cpp] view plaincopy 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 */ ); [cpp] view plaincopy int sqlite3_step(sqlite3_stmt*); 下面以一段iOS中的selection查询为例说明二者的用法:
[cpp] view
plaincopy
-(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);
// The table has not been created. Most likely, this is the first time we create the database.
// Therefore, create all tables in it
char * sql = "Create TABLE [FileObjects] ([FileId] VARCHAR(128),[FileExt] VARCHAR(128), [FileName] VARCHAR(128), [FileUrl] VARCHAR(128), [FileType] INT );"; // NOXLATE
char * errMsg;
result = sqlite3_exec(mDatabase, sql, NULL, NULL, &errMsg);
}
else
{
// Select returns OK, initialize the memory model from the result
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")等等。