1.sqlite3_get_table() - 非回调执行sql语句
SQLITE_API int sqlite3_get_table(sqlite3 *db,/* An open database 已经打开的数据库句柄*/const char *zSql,/* SQL to be evaluated sql语句*/char ***pazResult, /* Results of the query 查询结果*/int *pnRow, /* Number of result rows written here 查询结果的行数*/int *pnColumn, /* Number of result columns written here 查询结果的列数*/char **pzErrmsg /* Error msg written here 发生错误时错误信息*/);
查询结果表pazResult是指向以'\0'结尾的UTF-8字符串的指针数组;假设N为行数,M为列数;阵列中有(N+1)*M个元素;
举例: 假设表内容为
Name | Age-----------------------Alice | 43Bob | 28Cindy | 21
则该表有2列(M==2); 3行(N==3); 总共有(3+1)*2=8个条目;
则pazResult指向数据的内容为:
azResult[0] = "Name";azResult[1] = "Age";azResult[2] = "Alice";azResult[3] = "43";azResult[4] = "Bob";azResult[5] = "28";azResult[6] = "Cindy";azResult[7] = "21";
前M个指针指向以'\0'结尾的字符串,内容为列的名称; 其余条目都指向查询结果;
结果表可能包含一个或多个内存分配,将结果表直接传递给sqlite3_free()是不安全的,应该使用sqlite3_free_table()正确且安全的释放;
2.sqlite3_free_table()
作用: 释放结果表指针；
SQLITE_API void sqlite3_free_table(char **result);
sqlite3_get_table()接口又调用了sqlite3_exec();sqlite3_get_table()不会访问SQLite的任何内部数据结构;它只使用此处定义的公共接口;
3.sqlite3_exec() - 回调执行sql语句
typedef int (*sqlite3_callback)(void*,int,char**, char**);SQLITE_API int sqlite3_exec(sqlite3*, /* An open database 打开的数据库句柄*/const char *sql, /* SQL to be evaluated sql语句*/int (*callback)(void*,int,char**,char**), /* Callback function 回调函数 */void *,/* 1st argument to callback 传递给回调函数参数*/char **errmsg/* Error msg written here 发生错误时存储错误信息*/);
若sql语句为NULL或只包含空格和sql注释的指针,那么不会计算任何sql语句,也不会更改数据库;
若回调为NULL,则不会调用任何回调,并忽略结果行;
回调函数的参数:
参数1: sqlite3_exec传递的参数;
参数2: 结果中的列数;
参数3:指向字符串的指针数组;就像sqlite3_column_text()中获取的一样,每列一个;（等同于行数组）；
参数4: 指向字符串的指针数组,其中每个条目从sqlite3_column_name()获得的相应结果列的名称;(等同于列的名称)
5. 示例
#include <sqlite3.h>#include <stdio.h>//typedef int (*sqlite3_callback)(void*,int,char**, char**);static int sql_callback(void *args, int nCol, char **rowArr, char **colName){printf("nCol = %d\n",nCol);for(int i = 0 ;i < nCol; i++){printf("%s = %s\n", colName[i], rowArr[i] ? rowArr[i] : "NULL");}printf("\n");return 0;}int main(int argc, const char *argv[]){int ret;sqlite3 *ppDb = NULL;//不存在创建并打开;ret = sqlite3_open("./config.db", &ppDb);if(ret != SQLITE_OK){printf("open config.db failed %s\n",sqlite3_errmsg(ppDb));return -1;}printf("open config.db ok\n");/* crate sql table */char *sql = NULL;sql = "CREATE TABLE COMPANY(" \"ID INT PRIMARY KEYNOT NULL," \"NAMETEXT NOT NULL," \"AGE INTNOT NULL," \"ADDRESSCHAR(50)," \"SALARYREAL);" ;char *errMsg = NULL;ret = sqlite3_exec(ppDb,sql, NULL ,NULL, &errMsg);if(ret != SQLITE_OK){printf("sqlite3_exec create err %s\n",errMsg);sqlite3_free(errMsg);}printf("crate config.db table ok\n");/* insert sql */sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)" \"VALUES (1, 'bkWu', 26, 'KaiYang', 12000.0);" \"INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)" \"VALUES (2, 'xlLiu', 27, 'GaoYun', 5000); "\"INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)" \"VALUES (3,'xWang', 32, 'GuiYang', '14000');"\"INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)" \"VALUES (4, 'rgCheng',24,'QiangNan',6000);"\"INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)" \"VALUES (5, 'jjXian',24,'',6000);";ret = sqlite3_exec(ppDb,sql, NULL ,NULL, &errMsg);if(ret != SQLITE_OK){printf("sqlite3_exec insert err %s\n",errMsg);sqlite3_free(errMsg);}printf("insert config.db table ok\n");/* select sql */sql = "SELECT * from COMPANY";#if 0 //法1ret = sqlite3_exec(ppDb,sql, sql_callback ,NULL, &errMsg); if(ret != SQLITE_OK){printf("sqlite3_exec select err %s\n",errMsg);sqlite3_free(errMsg);}/*open config.db okcrate config.db table okinsert config.db table oknCol = 5ID = 1NAME = bkWuAGE = 26ADDRESS = KaiYangSALARY = 12000.0nCol = 5ID = 2NAME = xlLiuAGE = 27ADDRESS = GaoYunSALARY = 5000.0nCol = 5ID = 3NAME = xWangAGE = 32ADDRESS = GuiYangSALARY = 14000.0nCol = 5ID = 4NAME = rgChengAGE = 24ADDRESS = QiangNanSALARY = 6000.0nCol = 5ID = 5NAME = jjXianAGE = 24ADDRESS = SALARY = 6000.0*/#else //法2int nRow, nCol;char **pazResult = NULL;ret = sqlite3_get_table(ppDb, sql, &pazResult, &nRow, &nCol, &errMsg);if(ret != SQLITE_OK){printf("sqlite3_get_table err %s\n",errMsg);sqlite3_free(errMsg);return -1;}printf("nRow = %d , nCol = %d\n", nRow, nCol);for(int i = 0; i < nCol*(nRow+1); i++){printf("pazResult[%d] = %s\n",i,pazResult[i]);}/*open config.db okcrate config.db table okinsert config.db table oknRow = 5 , nCol = 5pazResult[0] = IDpazResult[1] = NAMEpazResult[2] = AGEpazResult[3] = ADDRESSpazResult[4] = SALARYpazResult[5] = 1pazResult[6] = bkWupazResult[7] = 26pazResult[8] = KaiYangpazResult[9] = 12000.0pazResult[10] = 2pazResult[11] = xlLiupazResult[12] = 27pazResult[13] = GaoYunpazResult[14] = 5000.0pazResult[15] = 3pazResult[16] = xWangpazResult[17] = 32pazResult[18] = GuiYangpazResult[19] = 14000.0pazResult[20] = 4pazResult[21] = rgChengpazResult[22] = 24pazResult[23] = QiangNanpazResult[24] = 6000.0pazResult[25] = 5pazResult[26] = jjXianpazResult[27] = 24pazResult[28] = pazResult[29] = 6000.0*///正确且安全的释放内存sqlite3_free_table(pazResult);#endif /* update sql & select */sql = "UPDATE COMPANY set SALARY = 20000.0 where ID=3;" \"UPDATE COMPANY set SALARY = 15000.0 where ID=4;" \"SELECT * from COMPANY";ret = sqlite3_exec(ppDb,sql, sql_callback ,NULL, &errMsg); if(ret != SQLITE_OK){printf("sqlite3_exec select err %s\n",errMsg);sqlite3_free(errMsg);}/* delete sql */sql = "DELETE from COMPANY where ID=2;" \"DELETE from COMPANY where ID=5;" \"SELECT * from COMPANY";ret = sqlite3_exec(ppDb,sql, sql_callback ,NULL, &errMsg); if(ret != SQLITE_OK){printf("sqlite3_exec select err %s\n",errMsg);sqlite3_free(errMsg);}sqlite3_close(ppDb);return 0;}
