C语言练习03：操作SQLite数据库

从SQLite官方站点上下载这些文件：

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:LibSQLiteBin>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

如何在程序中使用SQLite 3的C/C++接口？方法有三种：

方法一：直接把sqlite3.h 和 sqlite3.c 包含在我们的工程文件中即可。

方法二：使用动态链接库sqlite3.dll。

例如：假设我们已经把sqlite3.h拷贝到 C:LibSQLiteInclude 目录下，把sqlite3.lib贝到 C:LibSQLiteLib 目录下。

在 Property Pages | Configuration Properties | VC++ Directories 中，作如下设置：

1. Include Directories: C:LibSQLiteInclude
2. Library Directories: C:LibSQLiteLib

这样，我们就可以在代码中添加下面两行代码了：

#include <sqlite3.h>
#pragma comment(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: %sn",sqlite3_errmsg(db));
		sqlite3_close(db);
		exit(1);
	}
	
	sqlite3_close(db);
	return 0;
}

下面我们进行一些实际的数据库操作：

在 PrimeSeed.db3 的表PrimeSeed里面，插入一系列长整型数值。

先在SQLite的命令行工具中 sqlite3.exe 尝试以下命令：

insert into PrimeSeed(key,offset) values(2,0);
select * from PrimeSeed;
delete from PrimeSeed;

在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);

// 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: %sn",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 - %sn",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 - %sn",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 - %sn",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 - %sn",sqlite3_errmsg(db));
			sqlite3_close(db);
			exit(1);
		}
	}

	retCode = sqlite3_exec(db,commitTransactionSql,"Commit Transaction SQL error: %sn",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: %dt",sqlite3_column_int(stmt,0));
		printf("offset: %dn",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;
}

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!