sqlite3.exe sqlite3.dll sqlite3.def sqlite3.h sqlite3.c
注:sqlite3.h 和 sqlite3.c 包含在 sqlite-amalgamation-3xxxxxx.zip 中。
然后,用 sqlite3.dll 和 sqlite3.def 生成 sqlite3.lib。
C:\Lib\sqlite\Bin>sqlite3 PrimeSeed.db3 sqlite version 3.7.8 2011-09-19 14:49:19 Enter ".help" for instructions Enter sql statements terminated with a ";" sqlite> create table Range(latestValue integer not null default(0)); sqlite> create table PrimeSeed(key integer primary key not null,offset integer not null default(0)); sqlite> .q
在Visual C++ 2010 Express中创建一个C语言控制台工程。(方法见:《 Visual C++ 2010 Express Tips: 编写C语言程序》)
如何在程序中使用sqlite 3的C/C++接口?方法有三种:
方法一:直接把sqlite3.h 和 sqlite3.c 包含在我们的工程文件中即可。
例如:假设我们已经把sqlite3.h拷贝到 C:\Lib\sqlite\Include 目录下,把sqlite3.lib贝到 C:\Lib\sqlite\Lib 目录下。
在 Property Pages | Configuration Properties | VC++ Directories 中,作如下设置:
1. Include Directories: C:\Lib\sqlite\Include
2. Library Directories: C:\Lib\sqlite\Lib
#include <sqlite3.h> #pragma comment(lib,"sqlite3.lib")
在编译中我们需要用到这个 sqlite3.lib 。运行时,我们需要把 sqlite3.dll 拷贝到运行目录下。
本例中我们采用方法二。
先试验下面这段代码,注意在命令行中执行的时候第一个命令行参数就是我们生成的sqlite 3数据库文件:
// TestOpenCloseDB.c : Defines the entry point for the console application. // #include <stdio.h> #include <sqlite3.h> #pragma comment(lib,"sqlite3.lib") int main(int argc,char* argv[]) { char* dbFile = NULL; sqlite3 *db = NULL; char *errMsg = NULL; int retCode = 0; /* dbFile = "PrimeSeed.db3"; */ if(argc != 2) { fprintf(stderr,"Usage: %s <DATABASE_File>\n",argv[0]); exit(1); } dbFile = argv[1]; retCode = sqlite3_open(dbFile,&db); if(retCode != sqlITE_OK) { fprintf(stderr,"Can't open database: %s\n",sqlite3_errmsg(db)); sqlite3_close(db); exit(1); } sqlite3_close(db); return 0; }
如果这段程序运行无误,就证明我们可以打开和关闭sqlite数据库了。
下面我们进行一些实际的数据库操作:
在 PrimeSeed.db3 的表PrimeSeed里面,插入一系列长整型数值。
先在sqlite的命令行工具中 sqlite3.exe 尝试以下命令:
insert into PrimeSeed(key,offset) values(2,0); select * from PrimeSeed; delete from PrimeSeed;
下面,在程序中,我们要动态创建类似于 insert into PrimeSeed(key,0); 这样的sql语句。
在sqlite3的C/C++接口中,提供了 sqlite3_stmt 这个结构来作为动态sql语句。相关的函数有:
sqlITE_API int sqlite3_prepare( sqlite3 *db,/* Database handle */ const char *zsql,/* sql statement,UTF-8 encoded */ int nByte,/* Maximum length of zsql in bytes. */ sqlite3_stmt **ppStmt,/* OUT: Statement handle */ const char **pzTail /* OUT: Pointer to unused portion of zsql */ ); sqlITE_API int sqlite3_bind_int64(sqlite3_stmt*,int,sqlite3_int64); sqlITE_API int sqlite3_step(sqlite3_stmt*); sqlITE_API int sqlite3_finalize(sqlite3_stmt *pStmt);
下面是实现一系列insert然后再执行select的例子:
// TestOpenCloseDB.c : Defines the entry point for the console application. // #include <stdio.h> #include <sqlite3.h> #pragma comment(lib,"sqlite3.lib") #define sql_MAXLEN 128 int main(int argc,char* argv[]) { char *dbFile = NULL; sqlite3 *db = NULL; int retCode = 0; char szsql[sql_MAXLEN] = { 0 }; sqlite3_stmt *stmt; int i; char *szErrorMsg = NULL; const char insertsql[] = "insert into PrimeSeed(key,offset) values(?,?);"; const char beginTransactionsql[] = "begin transaction;"; const char commitTransactionsql[] = "commit transaction;"; const char querysql[] = "select key,offset from PrimeSeed where key >= ? and key <= ?;"; /* dbFile = "PrimeSeed.db3"; */ if(argc != 2) { fprintf(stderr,argv[0]); exit(1); } dbFile = argv[1]; /* Open sqlite database connection */ retCode = sqlite3_open(dbFile,sqlite3_errmsg(db)); sqlite3_close(db); exit(1); } retCode = sqlite3_exec(db,beginTransactionsql,NULL,&szErrorMsg); if(retCode != sqlITE_OK) { fprintf(stderr,"Begin Transaction sql error: %s\n",szErrorMsg); sqlite3_free(szErrorMsg); sqlite3_close(db); exit(1); } /* Insert */ strcpy_s(szsql,sql_MAXLEN,insertsql); for(i=1; i<=5; i++) { retCode = sqlite3_prepare(db,szsql,&stmt,NULL); if(retCode != sqlITE_OK) { fprintf(stderr,"Error occurs in sqlite3_prepare(%s): 0x%08X - %s\n",retCode,sqlite3_errmsg(db)); sqlite3_close(db); exit(1); } retCode = sqlite3_bind_int64(stmt,1,i); /* Notice: the first sql parameter's index is 1 */ retCode = sqlite3_bind_int64(stmt,2,2 * i); /* Notice: the first sql parameter's index is 1 */ if(retCode != sqlITE_OK) { fprintf(stderr,"Error occurs in sqlite3_bind_int64(): 0x%08X - %s\n",sqlite3_errmsg(db)); sqlite3_close(db); exit(1); } retCode = sqlite3_step(stmt); if((retCode != sqlITE_OK) && (retCode != sqlITE_DONE) && (retCode != sqlITE_ROW)) { fprintf(stderr,"Error occurs in sqlite3_step(): 0x%08X - %s\n",sqlite3_errmsg(db)); sqlite3_close(db); exit(1); } retCode = sqlite3_finalize(stmt); if(retCode != sqlITE_OK) { fprintf(stderr,"Error occurs in sqlite3_finalize(): 0x%08X - %s\n",sqlite3_errmsg(db)); sqlite3_close(db); exit(1); } } retCode = sqlite3_exec(db,commitTransactionsql,"Commit Transaction sql error: %s\n",szErrorMsg); sqlite3_free(szErrorMsg); sqlite3_close(db); exit(1); } /* Query */ strcpy_s(szsql,querysql); retCode = sqlite3_prepare(db,NULL); if(retCode != sqlITE_OK) { fprintf(stderr,sqlite3_errmsg(db)); sqlite3_close(db); exit(1); } retCode = sqlite3_bind_int64(stmt,2); retCode = sqlite3_bind_int64(stmt,5); retCode = sqlite3_step(stmt); if((retCode != sqlITE_OK) && (retCode != sqlITE_DONE) && (retCode != sqlITE_ROW)) { fprintf(stderr,sqlite3_errmsg(db)); sqlite3_close(db); exit(1); } while(retCode == sqlITE_ROW) { printf("key: %d\t",sqlite3_column_int(stmt,0)); printf("offset: %d\n",1)); retCode = sqlite3_step(stmt); } retCode = sqlite3_finalize(stmt); if(retCode != sqlITE_OK) { fprintf(stderr,sqlite3_errmsg(db)); sqlite3_close(db); exit(1); } if(szErrorMsg) { free(szErrorMsg); } /* Close sqlite database connection */ sqlite3_close(db); return 0; }上面的代码,由于每执行一次数据库操作都要进行一次错误判断,所以代码显得很冗长,其实这个过程很简单。