sqlite3数据库API-执行sql语句(三)

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       | 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个指针指向以'\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 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 %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 //法1
	ret = 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 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 %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 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 %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;
}