//插入及更新操作
int nRet = 0;
sqlite3 * db = NULL;
CString strFilePath; //数据库存储路径
//路径赋值(省略)
//连接数据库
nRet = sqlite3_open16(strFilePath, &db); //有几个打开函数可供调用,使用WCHAR类型路径用这个
if(nRet != SQLITE_OK)
{
LogD(_Q_D,L"数据库打开失败");
return -4;
}
CString strSQLSentence;
char *szSQLSentence = NULL;
组织SQL语句
**********************
{
//更新操作
strSQLSentence.Format(L"update tbl_ad set AD_TITLE='%s', AD_INFO='%s', BEGIN_TIME='%s', END_TIME='%s' where AD_ID='%s';", strADTitle, strADInfo, strADBeginTime, strADEndTime, strADId);
}
{
//插入操作
strSQLSentence.Format(L"insert into tbl_Advertisement values('%s','%s','%s','%s','%s');", strADId, strADTitle, strADInfo, strADBeginTime, strADEndTime);
}
**********************
//将SQL语句转换成sqlite函数能执行的UTF8格式
nLen = 0;
nLen = WideCharToMultiByte(CP_UTF8, 0, strSQLSentence, -1, NULL, 0, NULL, 0);
szSQLSentence = new char[nLen+1];
if (szSQLSentence == NULL)
{
return -6;
}
memset(szSQLSentence, 0, nLen+1);
WideCharToMultiByte(CP_UTF8, 0, strSQLSentence, -1, szSQLSentence, nLen+1, NULL, 0);
//锁定数据库
if(SQLiteLock(L"terminalplat.db",30*1000) == 0) //SQLiteLock为自写的一套锁机制中的函数
{
// 准备执行SQL
nRet = sqlite3_exec( db, "begin;", 0, 0, 0);
if(nRet == SQLITE_OK)
{
BOOL bSuccess = TRUE;
char * pErrMsg = NULL;
// 执行SQL
nRet = sqlite3_exec( db, szSQLSentence, 0, 0, &pErrMsg);
if(nRet != SQLITE_OK)
{
LogBD(_Q_D,"SQL执行失败 [%s]",pErrMsg);
bSuccess = FALSE;
}
else
{
LogBD(_Q_D,"SQL执行成功");
}
if(bSuccess)
{
//提交
sqlite3_exec( db, "commit;", 0, 0, 0);
}
else
{
//回滚
sqlite3_exec( db, "rollback;", 0, 0, 0);
hReturnVale = -7;
}
}
//解锁
SQLiteUnLock(L"terminalplat.db");
}
else
{
hReturnVale = -8;
}
// 关闭数据库
sqlite3_close(db);
return hReturnVale ;
************************************分割线************************************
//查询操作
//说明sqlite3_exec可以让数据库直接执行SQL语句,查询也不例外,但是如果是查询语句,需要定义一个回调函数来解析查询结果。
//在此,用的是sqlite3_get_table这一套函数,我访问的是本地数据库,还算稳定
//组织查询语句
CString strSQLSentence;
strSQLSentence.Format(L"select * from tbl_Advertisement where 1=1 and AD_ID='%s';", strADId);
char szSQLSentence[1024];
int nLen = 0;
memset(szSQLSentence, 0, sizeof(szSQLSentence));
nLen = WideCharToMultiByte(CP_UTF8, 0, strSQLSentence, -1, NULL, 0, NULL, 0);
WideCharToMultiByte(CP_UTF8, 0, strSQLSentence, -1, szSQLSentence, nLen+1, NULL, 0);
int result;
HRESULT hReturn = 0;
char * errmsg = NULL;
char **dbResult = NULL;
int nRow, nColumn;
int index=0;
//数据库查询
CString strTemp;
result = sqlite3_get_table( db, szSQLSentence, &dbResult, &nRow, &nColumn, &errmsg );
if( (SQLITE_OK == result))
{
if (nRow >= 1)
{
//查询成功,有记录
hReturn = 1;
}
else
{
//无符合条件的结果
hReturn = 0;
}
}
else
{
//查询失败
LogD(_Q_D, L"判断本地数据库中是否存在该广告--[查询失败],errmsg=%s result=%d, nRow=%s, nColumn=%s", CString(errmsg), result, nRow, nColumn);
hReturn = -1;
}
//释放查询结果
sqlite3_free_table( dbResult ); //注意一定要调用该函数对释放结果集