sqlite3 开发包的使用

1、sqliteTest.h

#include <ctype.h>
#include <Windows.h>
#include <assert.h>
#include <string>
#include "sqlite3.h"开发包提供
#include "SqlUtils.h"//开发包提供
#include "Utils.h" //开发包提供

#pragma  comment(lib, "../Bin/sqlite3.lib")  //开发包提供

#define  TASK_STATE_SUCCEED	1
#define  TASK_STATE_FAIL	2

static sqlite3 * g_user_data = 0;

//declare
int AccessSqlite3Init();
int AccessSqlite3Clear();
int SetTaskState(const char* taskid, int nTaskState);
int GetTaskState(const char* taskid, int *pTaskState);


//definitions
int AccessSqlite3Init()
{
	int error = -1;
	
	//create table
	const char * szSqlExec[] = {
		"CREATE TABLE IF NOT EXISTS tbl_task_state "\
		"(taskid CHAR(36), state INT, PRIMARY KEY (taskid)); ",
		NULL
	};
	//set path
	char szConfigPath[MAX_PATH] = {0};
	DWORD dwPathLen = GetModuleFileNameA(NULL,  szConfigPath, sizeof szConfigPath);
	char *pReveseSlash = strrchr(szConfigPath,'\\');
	if (pReveseSlash)
	{
		*pReveseSlash = 0;
	}
	strncat(szConfigPath, "\\TaskDataBase.sqlite", sizeof szConfigPath);

	//open config
	int nCode = sqlite3_threadsafe();
	assert(nCode == 1);
	nCode = sqlite3_config(SQLITE_CONFIG_SERIALIZED);
	assert(SQLITE_OK == nCode);
	error = sqlite3_open_ansi(szConfigPath, &g_user_data);
	assert(error == 0);
	assert(g_user_data != NULL);

	char *pErrMsg = NULL;
	error = sqlite3_exec(g_user_data, szSqlExec[0], 0, 0, &pErrMsg);
	if (error != 0)
	{
		RaiseException(0, 0, 0, NULL);
		sqlite3_free(pErrMsg);
	}

	return error;
}


//clear
int AccessSqlite3Clear()
{
	int error = -1;
	assert(g_user_data != NULL);
	sqlite3_close(g_user_data);
	g_user_data = NULL;
	error =0;

	return error;

}

int SetTaskState(const char* taskid, int nTaskState)
{
	int error = -1;
	SqlParameter TaskUserDataParam;
	const char* szSqlExec[] = {
		"INSERT OR IGNORE INTO tbl_task_state(taskid, state) "\
		"values(@taskid, @state) ",
		NULL
	};

	TaskUserDataParam.Add("@taskid", taskid);
	TaskUserDataParam.Add("@state", nTaskState);
	std::string strSqlText;
	TaskUserDataParam.SqlFormat(szSqlExec[0], strSqlText);
	char *pErrMsg = NULL;
	error = sqlite3_exec(g_user_data, strSqlText.c_str(),0 ,0, &pErrMsg);
	if (error != 0)
	{
		RaiseException(0, 0 ,0, NULL);
		sqlite3_free(pErrMsg);
	}

	return error;
}


int GetTaskState(const char* taskid, int *pTaskState)
{
	int error = -1;
	int found = 0;
	const char *szSqlExec[] = {
		"SELECT state FROM tbl_task_state WHERE taskid=@taskid; ",
		NULL
	};

	SqlParameter taskUserDataSqlParam;
	taskUserDataSqlParam.Add("@taskid", taskid);
	std::string strSqlText;
	taskUserDataSqlParam.SqlFormat(szSqlExec[0], strSqlText);

	//execute
	sqlite3_stmt *pSqlStmt = NULL;
	error = sqlite3_prepare(g_user_data, strSqlText.c_str(), -1, &pSqlStmt, NULL);
	assert(error == SQLITE_OK);

	error = sqlite3_step(pSqlStmt);
	if (error == SQLITE_ROW)
	{
		*pTaskState = sqlite3_column_int(pSqlStmt, 0);
		error = sqlite3_step(pSqlStmt);
		found = 1;
	}

	assert(error == SQLITE_DONE);
	
	error = sqlite3_finalize(pSqlStmt);
	assert(error == SQLITE_OK);
	return found;
}

2、sqliteTest.cpp

#include "sqliteTest.h"
#include <iostream>

using namespace  std;

#define  taskid_1 "e32ac270-2ea8-4843-8c2b-96852ca9da5b"
#define  taskid_2 "fe32ac270-2ea8-4843-8c2b-96852ca9da5"

int _tmain(int argc, _TCHAR* argv[])
{
	AccessSqlite3Init();
	SetTaskState(taskid_1, TASK_STATE_SUCCEED);
	SetTaskState(taskid_2, TASK_STATE_FAIL);

	int nTaskStat = 0;
	GetTaskState(taskid_1, &nTaskStat);
	cout<<"taskid_1: "<<taskid_1<<"\tState: "<<nTaskStat<<endl;
	GetTaskState(taskid_2, &nTaskStat);
	cout<<"taskid_2: "<<taskid_2<<"\tState: "<<nTaskStat<<endl;


	AccessSqlite3Clear();
	
	return 0;
}