SQLite之C接口
1、sqlite之C接口简介1.1 sqlite3_openint sqlite3_open ( const char *filename,/* Database filename (UTF-8) */ /* 数据库将采用UTF-8的编码方式,sqlite3_open16采用UTF-16的编码方式 */ sqlite3 **ppDb /* OUT: SQLite db handle */ /* 数据库连接对象 */ ): 打开一个sqlite数据库文件的连接并且返回一个数据库连接对象。 1.2 sqlite3_prepare_v2int sqlite3_prepare_v2( sqlite3 *db,/* Database handle */ /* 成功打开的数据库句柄 */ const char *zSql,/* SQL statement,UTF-8 encoded */ /* UTF8编码的 SQL 语句 */ int nByte,/* Maximum length of zSql in bytes. */ /* 参数 sql 的字节数,包含 ' ' */ sqlite3_stmt **ppStmt,/* OUT: Statement handle */ /* 输出:预编译语句句柄 */ const char **pzTail /* OUT: Pointer to unused portion of zSql */ /* 输出:指向 sql 语句中未使用的部分 */ ); 这个函数将sql文本转换成一个准备语句(prepared statement)对象,同时返回这个对象的指针。 1.3 sqlite3_stepint sqlite3_step(sqlite3_stmt *stmt); 调用一次或者多次被编译的语句句柄。 1.4 sqlite3_resetint sqlite3_reset (sqlite3_stmt *stmt); sqlite3_reset用于重置一个准备语句对象到它的初始状态,然后准备被重新执行。所有sql语句变量使用sqlite3_bind_XXX绑定值,使用sqlite3_clear_bindings清除这些绑定。Sqlite3_reset接口重置准备语句到它代码开始的时候。sqlite3_reset并不改变在准备语句上的任何绑定值,那么这里猜测,可能是语句在被执行的过程中发生了其他的改变,然后这个语句将它重置到绑定值的时候的那个状态。 1.5 sqlite3_execint sqlite3_exec( sqlite3*,/* An open database */ const char *sql,/* SQL to be evaluated */ int (*callback)(void*,int,char**,char**),/* Callback function */ void *,/* 1st argument to callback */ char **errmsg /* Error msg written here */ ); 该函数用来执行多条以“;”分隔的 SQL 语句。封装了 sqlite3_prepare(),sqlte3_step() 和 sqlite3_finalize() 函数。 int callback(void *,int argc,char **argv,char **azColName); First: 为sqlite3_exec中的第四个参数,即你所传递的参数。 1.6 sqlite3_bind_XXXint sqlite3_bind_double(sqlite3_stmt*,double); int sqlite3_bind_int(sqlite3_stmt*,int); int sqlite3_bind_null(sqlite3_stmt*,int); int sqlite3_bind_text(sqlite3_stmt*,const char*,void(*)(void*)); 给参数绑定值。 1.7 sqlite3_column_XXXdouble sqlite3_column_double(sqlite3_stmt*,int iCol); int sqlite3_column_int(sqlite3_stmt*,int iCol); const unsigned char *sqlite3_column_text(sqlite3_stmt*,int iCol); int sqlite3_column_type(sqlite3_stmt*,int iCol); 这类函数从sqlite3_step()返回的结果集中提取某一列。 2、示例2.1 打开数据库#include <sqlite3.h> #include <stdio.h> int main(void) { sqlite3 *db; sqlite3_stmt *res; int rc = sqlite3_open("lab.db",&db); if (rc != SQLITE_OK) { fprintf(stderr,"Cannot open database: %sn",sqlite3_errmsg(db)); sqlite3_close(db); return 1; } sqlite3_close(db); printf ("Succeed to open the database!n"); return 0; } $ gcc -o open open.c -lsqlite3 $ ./open Succeed to open the database! 2.2 创建目标数据表#include <sqlite3.h> #include <stdio.h> int main () { sqlite3 *db; sqlite3_stmt *res; int rc = sqlite3_open("lab.db",&db); if (rc != SQLITE_OK) { fprintf(stderr,sqlite3_errmsg(db)); sqlite3_close(db); return 1; } char *SQL = "CREATE TABLE IF NOT EXISTS testtable (ID INTEGER PRIMARY KEY,name TEXT,gender TEXT,age REAL,value REAL)"; if(sqlite3_prepare_v2(db,SQL,-1,&res,0)) { printf("Failed to create table!n"); if (res) sqlite3_finalize(res); sqlite3_close(db); return 1; } if (sqlite3_step(res) != SQLITE_DONE) { sqlite3_finalize(res); sqlite3_close(db); return 1; } sqlite3_finalize(res); printf ("Succeed to create testtable now.n"); sqlite3_close(db); return 0; } $ gcc -o create create.c -lsqlite3 $ ./create Succeed to create testtable now! 2.3 数据表操作#include <sqlite3.h> #include <stdio.h> int main () { sqlite3 *db; sqlite3_stmt *res; int rc = sqlite3_open("lab.db",sqlite3_errmsg(db)); sqlite3_close(db); return 1; } char *err_msg = 0; /* 想实现哪项功能,就将注释去掉 */ //char *SQL = "INSERT INTO testtable VALUES(NULL,'xiao','male',100,0.1)";//向数据表中插入一条数据 //char *SQL = "DELETE FROM testtable WHERE gender = males AND age = 26";//删除数据表中的多条数据 //char *SQL = "DELETE FROM testtable";//删除数据表中所有数据 //char *SQL = "UPDATE testtable SET gender = female,age=30 WHERE ID = 1";//更新数据表中的多条数据 //char *SQL = "DROP TABLE IF EXISTS testtable";//删除数据表 int rc2 = sqlite3_exec(db,&err_msg); if(rc2 != SQLITE_OK) { fprintf(stderr,"Failed to drop tablen"); fprintf(stderr,"SQL error: %sn",err_msg); sqlite3_free(err_msg); sqlite3_close(db); return; } sqlite3_close(db); printf ("Succeed!n"); return 0; } $ gcc -o table table.c -lsqlite3 $ ./table Succeed! 2.4 批量插入数据#include <sqlite3.h> #include <stdio.h> #include <string.h> int main () { sqlite3 *db; int rc = sqlite3_open("lab.db",sqlite3_errmsg(db)); sqlite3_close(db); return 1; } /* 手动开启事物 */ sqlite3_stmt* stmtb = NULL; const char* beginSQL = "BEGIN TRANSACTION"; if (sqlite3_prepare_v2(db,beginSQL,strlen(beginSQL),&stmtb,NULL) != SQLITE_OK) { if (stmtb) sqlite3_finalize(stmtb); sqlite3_close(db); return 1; } if (sqlite3_step(stmtb) != SQLITE_DONE) { sqlite3_finalize(stmtb); sqlite3_close(db); return 1; } sqlite3_finalize(stmtb); /*****************************/ char *err_msg = 0; char *sql = "INSERT INTO testtable VALUES(NULL,?,?)"; sqlite3_stmt *stmt = NULL; if (sqlite3_prepare_v2(db,sql,&stmt,NULL) != SQLITE_OK) { if (stmt) sqlite3_finalize(stmt); sqlite3_close(db); return 1; } char *name = "xiao"; char *m = "male"; char *f = "female"; for (int i = 1; i <= 10; i++) { sqlite3_bind_null (stmt,0); sqlite3_bind_text(stmt,1,name,SQLITE_TRANSIENT); if(i % 2 == 0) sqlite3_bind_text(stmt,2,m,SQLITE_TRANSIENT); else sqlite3_bind_text(stmt,f,SQLITE_TRANSIENT); sqlite3_bind_int(stmt,3,i + 24); double fl = i * 9.9; sqlite3_bind_double(stmt,4,fl); if (sqlite3_step(stmt) != SQLITE_DONE) { sqlite3_finalize(stmt); sqlite3_close(db); return 1; } sqlite3_reset(stmt); } sqlite3_finalize(stmt); /* 手动关闭事物 */ sqlite3_stmt* stmtc = NULL; const char *commitSQL = "COMMIT TRANSACTION"; if (sqlite3_prepare_v2(db,commitSQL,strlen(commitSQL),&stmtc,NULL) != SQLITE_OK) { if (stmtc) sqlite3_finalize(stmt); sqlite3_close(db); return 1; } if (sqlite3_step(stmtc) != SQLITE_DONE) { sqlite3_finalize(stmtc); sqlite3_close(db); return 1; } sqlite3_finalize(stmtc); /*********************************/ sqlite3_close(db); return 0; } $ gcc -o insert_datas insert_datas.c -lsqlite3 $ ./insert_data $ sqlite3 lab.db sqlite3> SELECT * FROM testtable; 1|xiao|female|25.0|9.9 2|xiao|male|26.0|19.8 3|xiao|female|27.0|29.7 4|xiao|male|28.0|39.6 5|xiao|female|29.0|49.5 6|xiao|male|30.0|59.4 7|xiao|female|31.0|69.3 8|xiao|male|32.0|79.2 9|xiao|female|33.0|89.1 10|xiao|male|34.0|99.0 2.5 显示数据表的数据(non call_back)#include <sqlite3.h> #include <stdio.h> #include <string.h> int main () { sqlite3 *db; int rc = sqlite3_open("lab.db",sqlite3_errmsg(db)); sqlite3_close(db); return 1; } const char *sql = "SELECT * FROM testtable"; sqlite3_stmt *stmt = NULL; if (sqlite3_prepare_v2(db,NULL) != SQLITE_OK) { if (stmt) sqlite3_finalize(stmt); sqlite3_close(db); return 1; } int fieldcout = sqlite3_column_count (stmt); do { int r = sqlite3_step(stmt); if (r == SQLITE_ROW) { int i; for (i = 0; i < fieldcout; ++i) { int vtype = sqlite3_column_type(stmt,i); if (vtype == SQLITE_INTEGER) { int v = sqlite3_column_int(stmt,i); printf ("%dn",v); } else if (vtype == SQLITE_FLOAT) { double v = sqlite3_column_double(stmt,i); printf ("%fn",v); } else if (vtype == SQLITE_TEXT) { const char *v = (const char *)sqlite3_column_text(stmt,i); printf("%sn",v); } else if (vtype == SQLITE_NULL) { printf ("NULLn"); } } } else if (r == SQLITE_DONE) { printf ("Select finish.n"); break; } else { printf ("Failed to select.n"); sqlite3_finalize(stmt); sqlite3_close(db); return 1; } } while (1); sqlite3_finalize(stmt); sqlite3_close(db); return 0; } $ gcc -o select select.c -lsqlite3 $ ./select 1 xiao female 25.000000 9.900000 2 xiao male 26.000000 19.800000 ... ... ... 10 xiao male 34.000000 99.000000 2.6 call_back#include <sqlite3.h> #include <stdio.h> int callback(void *,char **,char **); int main(void) { sqlite3 *db; char *err_msg = 0; int rc = sqlite3_open("lab.db",&db); if(rc != SQLITE_OK) { fprintf(stderr,sqlite3_errmsg(db)); sqlite3_close(db); return 1; } char *sql = "PRAGMA table_info(testtable)"; rc = sqlite3_exec(db,callback,&err_msg); if(rc != SQLITE_OK) { fprintf(stderr,"Failed to select datan"); fprintf(stderr,err_msg); sqlite3_free(err_msg); sqlite3_close(db); return 1; } sqlite3_close(db); return 0; } int callback(void *NotUsed,char **azColName) { NotUsed = 0; int i; for (i = 0; i < argc; i++) { printf ("%s = %sn",azColName[i],argv[i]?argv[i]:"NULL"); } printf("n"); return 0; } $ gcc -o callback callback.c -lsqlite3 $ ./callback cid = 0 name = ID type = INTEGER notnull = 0 dflt_value = NULL pk = 1 ... ... ... cid = 4 name = value type = REAL notnull = 0 dflt_value = NULL pk = 0 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |