概述
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是指向以' '结尾的UTF-8字符串的指针数组;假设N为行数,M为列数;阵列中有(N+1)*M个元素;
举例: 假设表内容为
Name | Age ----------------------- Alice | 43 Bob | 28 Cindy | 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个指针指向以' '结尾的字符串,内容为列的名称; 其余条目都指向查询结果;
结果表可能包含一个或多个内存分配,将结果表直接传递给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 = %dn",nCol);
for(int i = 0 ;i < nCol; i++){
printf("%s = %sn", 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 %sn",sqlite3_errmsg(ppDb));
return -1;
}
printf("open config.db okn");
/* crate sql table */
char *sql = NULL;
sql = "CREATE TABLE COMPANY("
"ID INT PRIMARY KEY NOT NULL,"
"NAME TEXT NOT NULL,"
"AGE INT NOT NULL,"
"ADDRESS CHAR(50),"
"SALARY REAL);" ;
char *errMsg = NULL;
ret = sqlite3_exec(ppDb,sql, NULL ,NULL, &errMsg);
if(ret != SQLITE_OK){
printf("sqlite3_exec create err %sn",errMsg);
sqlite3_free(errMsg);
}
printf("crate config.db table okn");
/* 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 %sn",errMsg);
sqlite3_free(errMsg);
}
printf("insert config.db table okn");
/* select sql */
sql = "SELECT * from COMPANY";
#if 0 //法1
ret = sqlite3_exec(ppDb,sql, sql_callback ,NULL, &errMsg);
if(ret != SQLITE_OK){
printf("sqlite3_exec select err %sn",errMsg);
sqlite3_free(errMsg);
}
/*
open config.db ok
crate config.db table ok
insert config.db table ok
nCol = 5
ID = 1
NAME = bkWu
AGE = 26
ADDRESS = KaiYang
SALARY = 12000.0
nCol = 5
ID = 2
NAME = xlLiu
AGE = 27
ADDRESS = GaoYun
SALARY = 5000.0
nCol = 5
ID = 3
NAME = xWang
AGE = 32
ADDRESS = GuiYang
SALARY = 14000.0
nCol = 5
ID = 4
NAME = rgCheng
AGE = 24
ADDRESS = QiangNan
SALARY = 6000.0
nCol = 5
ID = 5
NAME = jjXian
AGE = 24
ADDRESS =
SALARY = 6000.0
*/
#else //法2
int nRow, nCol;
char **pazResult = NULL;
ret = sqlite3_get_table(ppDb, sql, &pazResult, &nRow, &nCol, &errMsg);
if(ret != SQLITE_OK){
printf("sqlite3_get_table err %sn",errMsg);
sqlite3_free(errMsg);
return -1;
}
printf("nRow = %d , nCol = %dn", nRow, nCol);
for(int i = 0; i < nCol*(nRow+1); i++){
printf("pazResult[%d] = %sn",i,pazResult[i]);
}
/*
open config.db ok
crate config.db table ok
insert config.db table ok
nRow = 5 , nCol = 5
pazResult[0] = ID
pazResult[1] = NAME
pazResult[2] = AGE
pazResult[3] = ADDRESS
pazResult[4] = SALARY
pazResult[5] = 1
pazResult[6] = bkWu
pazResult[7] = 26
pazResult[8] = KaiYang
pazResult[9] = 12000.0
pazResult[10] = 2
pazResult[11] = xlLiu
pazResult[12] = 27
pazResult[13] = GaoYun
pazResult[14] = 5000.0
pazResult[15] = 3
pazResult[16] = xWang
pazResult[17] = 32
pazResult[18] = GuiYang
pazResult[19] = 14000.0
pazResult[20] = 4
pazResult[21] = rgCheng
pazResult[22] = 24
pazResult[23] = QiangNan
pazResult[24] = 6000.0
pazResult[25] = 5
pazResult[26] = jjXian
pazResult[27] = 24
pazResult[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 %sn",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 %sn",errMsg);
sqlite3_free(errMsg);
}
sqlite3_close(ppDb);
return 0;
}
最后
以上就是优秀钢笔为你收集整理的sqlite3数据库API-执行sql语句(三)的全部内容，希望文章能够帮你解决sqlite3数据库API-执行sql语句(三)所遇到的程序开发问题。
如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。
本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。
发表评论 取消回复