C语言封装sqlite3_API

来源：互联网发布：淘宝质量问题赔钱编辑：程序博客网时间：2024/05/16 05:41

//执行类似insert,update,create,drop这些不需要结果的sql语句。

int executeNoQuery(sqlite3 *db,const char *sql) {    sqlite3_stmt *pstmt = NULL;      if(sqlite3_prepare_v2(db,sql,strlen(sql),&pstmt,NULL)  !=  SQLITE_OK)     {         if(pstmt != NULL)             sqlite3_finalize(pstmt);        fprintf(stderr,"%s\n",sqlite3_errmsg(db));         return -1;     }     if(sqlite3_step(pstmt)  !=  SQLITE_DONE)     {         sqlite3_finalize(pstmt);         fprintf(stderr,"%s\n",sqlite3_errmsg(db));         return -1;    }     if(pstmt != NULL)         sqlite3_finalize(pstmt);     return 0; }

//执行select查询语句

void executeWithQuery(sqlite3 *db,char ***result,int *col,const char *sql)  {      int ret,row;      char *errMsg;        ret = sqlite3_get_table(db,sql,result,&row,col,&errMsg);      if(ret != SQLITE_OK)      {          fprintf(stderr,"%s\n",errMsg);          sqlite3_free(errMsg);          return;      }      (*result)[(row+1)*(*col)] = NULL;      return;  }

调用规则：

executeNoQuery：

只需传入数据库连接，和想要执行的sql语句

executeWithQuery：除了需要传入数据库连接，以及最后一个参数sql语句，还需要传入char **array,和int ncol的地址

下面是一个例子：

#include <stdio.h>#include <stdlib.h>#include <string.h>#include <sqlite3.h>#define PRINTERROR(str)\do{\perror(str);\exit(EXIT_FAILURE);\}while(0)const char *createSQL = "create table if not exists T_Students\(S_id integer PRIMARY KEY NOT NULL,\S_name varchar(128),S_age integer,\Time TimeStamp NOT NULL DEFAULT (datetime('now','localtime')));";const char *deleteSQL = "delete from T_Students where S_name='sagarfan';";const char *updateSQL = "update T_Students set S_age=25 where S_name='sagarfan';";const char *insertSQL = "insert into T_Students (S_name,S_age) values('sagarfan',23);";const char *selectSQL = "select S_name,S_age from T_Students;";int executeNoQuery(sqlite3 *db,const char *sql);int executeWithQuery(sqlite3 *db,char ***result,int *col,const char *sql);int main(){sqlite3 *conn = NULL;int i,ret,ncol;char sql[1024]={0}; char **array;ret = sqlite3_open("stu.sqlite",&conn);if(ret != SQLITE_OK)PRINTERROR(sqlite3_errmsg(conn));//create tableret = executeNoQuery(conn,createSQL);if(ret == -1)PRINTERROR(sqlite3_errmsg(conn));ret = executeNoQuery(conn,insertSQL);if(ret == -1)PRINTERROR(sqlite3_errmsg(conn));ret = executeWithQuery(conn,&array,&ncol,selectSQL);if(ret == -1)PRINTERROR(sqlite3_errmsg(conn));i=0;while(array[i+ncol] != NULL){printf("Name=%s\n",array[i+ncol]);printf("Age=%s\n",array[i+1+ncol]);i+=ncol;}sqlite3_free_table(array);ret = executeNoQuery(conn,updateSQL);if(ret == -1)PRINTERROR(sqlite3_errmsg(conn));if(conn != NULL)sqlite3_close(conn);return 0;}int executeNoQuery(sqlite3 *db,const char *sql){sqlite3_stmt *pstmt = NULL;if(sqlite3_prepare_v2(db,sql,strlen(sql),&pstmt,NULL) != SQLITE_OK){if(pstmt != NULL)sqlite3_finalize(pstmt);return -1;}if(sqlite3_step(pstmt) != SQLITE_DONE){sqlite3_finalize(pstmt);return -1;}sqlite3_finalize(pstmt);return 0;}int executeWithQuery(sqlite3 *db,char ***result,int *col,const char *sql){int ret,row;ret = sqlite3_get_table(db,sql,result,&row,col,NULL);if(ret != SQLITE_OK){return -1;  } (*result)[(row+1)*(*col)] = NULL;return 0;}

注意：

调用完executeWithQuery之后，记得释放array

0 0