sqlite3的事务功能实例

代码功能：

从sql.txt中读出SQL语句，并一一执行，若有一条执行失败，则所有执行全部不生效。回滚到执行前的状态。

#include <stdio.h>
#include <sqlite3.h>
#define SQL_FILE "./sql.txt"
int commitSQL(sqlite3 *db)
{
	int ret=0;         
	FILE *fp;          

	char buf[100]={0};
	char *errmsg = 0;
	char **azResult;
	int i,nRow=0,nColumn=0;
	fp = fopen(SQL_FILE,"r");
	if(NULL == fp)     
		return -1;     
	ret = sqlite3_exec(db,"BEGIN EXCLUSIVE",NULL,&errmsg);    
	if(ret != SQLITE_OK)
	{
		printf("ret = %d,BEGIN EXCLUSIVE: %s",ret,errmsg);
	}
	while( NULL != fgets(buf,100,fp))
	{                       
		printf("%sn",buf);
		if(0 == strncmp(buf,"select",6))
		{
			ret += sqlite3_get_table(db,buf,&azResult,&nRow,&nColumn,&errmsg);
			if(ret != SQLITE_OK)
			{
				printf("ret = %d,search table fail: %sn",errmsg);
			}
			printf("row:%d column=%dn",nRow,nColumn);
			for(i=nColumn;i<(nRow+1)*nColumn;i++)
			{
				printf("azResult[%d] = %sn",i,azResult[i]);
			}
			sqlite3_free_table(azResult);
		}
		else
			ret += sqlite3_exec(db,&errmsg); 
		if(SQLITE_OK != ret)
		{
			printf("ret = %d,sql: %sn",errmsg);
			break;
		}
	}      
	if(ret == SQLITE_OK)
	{
		ret=sqlite3_exec(db,"COMMIT",&errmsg);
		if(ret != SQLITE_OK)
		{
			printf("ret = %d,COMMIT: %sn",errmsg);
		}
	}
	else
	{
		ret=sqlite3_exec(db,"ROLLBACK",ROLLBACK: %sn",errmsg);
		}
		sqlite3_free(errmsg);
		fclose(fp); 
		return -2;
	}
	sqlite3_free(errmsg);
	fclose(fp); 

	return ret;
}

int main()
{
	sqlite3 *db;
	char *dbPath="test.db";  
	int ret = 0;        
	ret = sqlite3_open(dbPath,&db);  
	if(ret == SQLITE_OK)
	{  
		printf("open succeedn");      
	}  
	else
	{              
		printf("open failn");         
	}  
	ret = commitSQL(db);
	printf("ret = %dn",ret);

	sqlite3_close(db);
}

（编辑：李大同）

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