WCE Sqlite30数据库使用源码

#include "DbMonitor.h" /*#include "sckapi.h"*/ #include <windows.h> /*#include "debug_Socket.h"*/ #include "shareID.h" // C RunTime Header Files #include <stdlib.h> #include <malloc.h> #include <memory.h> #include <tchar.h> #define TAB_NAME_PB L"PhoneBook" #define TAB_NAME_REVSMS L"RevSms" #define PB_ITEM_NAME L"Name" #define PB_ITEM_NUMBER L"Number" #define PB_ITEM_NUMBER2 L"Number1" #define PB_ITEM_NOTE L"Note" #define LS_ITEM_NAME L"Name" #define LS_ITEM_NUMBER L"Number" #define LS_ITEM_TIME L"Time" #define LS_ITEM_FLAG L"Flag" #define LS_ITEM_SIM L"SimAddr" #define LS_ITEM_CONTENT L"Content" #define SQL_STR_LEN 255 CDbMonitor::CDbMonitor(void) { } CDbMonitor::~CDbMonitor(void) { } bool CDbMonitor::InitDbFile(LPCTSTR pFileName) { bool fTest; TCHAR csSql[SQL_STR_LEN]={0}; //_T("\\ResidentFlash\\SQLite3Test.db"); fTest = m_sqlite.Open(pFileName); if ( !fTest ) { TCHAR szError[255]={0}; _stprintf(szError,_T("Could not open %s"),pFileName); ::MessageBox(0, szError, 0, 0); return false; } _stprintf(csSql,L"CREATE TABLE %s (%s, %s, %s, %s, %s, %s)",TAB_NAME_REVSMS,LS_ITEM_NAME,LS_ITEM_NUMBER,LS_ITEM_TIME,LS_ITEM_FLAG,LS_ITEM_SIM,LS_ITEM_CONTENT); fTest = m_sqlite.DirectStatement(csSql); if ( !fTest ) { // AfxMessageBox(_T("Couldn't create table")); } _stprintf(csSql,L"CREATE TABLE %s (%s, %s, %s, %s)",TAB_NAME_PB,PB_ITEM_NAME,PB_ITEM_NUMBER,PB_ITEM_NUMBER2,PB_ITEM_NOTE); fTest = m_sqlite.DirectStatement(csSql); if ( !fTest ) { // AfxMessageBox(_T("Couldn't create table")); } return true; } bool CDbMonitor::DbInsertLocSms(DWORD dwFlag,DWORD dwSimAdd,LPCTSTR pName,LPCTSTR pNumber,LPCTSTR pTime,LPCTSTR pContent) { // TODO: 在此添加控件通知处理程序代码 BOOL fTest; DWORD dwLen; dwLen = _tcslen(pContent)+128; TCHAR* pSql = (TCHAR*)malloc(sizeof(TCHAR)*dwLen); _stprintf(pSql,L"INSERT INTO %s VALUES ('%s', %s,'%s', %d,%d,'%s')",TAB_NAME_REVSMS,pName,pNumber,pTime,dwFlag,dwSimAdd,pContent); fTest = m_sqlite.DirectStatement(pSql); free(pSql); return fTest; } bool CDbMonitor::GetNameByNumber(LPTSTR pName,LPCTSTR pNumber) { //这里还未完成以号码在数据库中查询名字 也就是电话薄功能 //wcscpy(pName,pNumber); wcscpy(pName,L"Name1"); return true; } bool CDbMonitor::DbDelAllSimSms() { // TODO: 在此添加控件通知处理程序代码 BOOL fTest; TCHAR pSql[MAX_PATH]={0}; _stprintf(pSql,L"delete from %s where %s = %d",TAB_NAME_REVSMS,LS_ITEM_FLAG,F_LOC_NEW); fTest = m_sqlite.DirectStatement(pSql); _stprintf(pSql,L"delete from %s where %s = %d",TAB_NAME_REVSMS,LS_ITEM_FLAG,F_LOC_READ); fTest = m_sqlite.DirectStatement(pSql); _stprintf(pSql,L"delete from %s where %s = %d",TAB_NAME_REVSMS,LS_ITEM_FLAG,F_SIM); fTest = m_sqlite.DirectStatement(pSql); return fTest; } bool CDbMonitor::DbDelSms(LPCTSTR pTime,DWORD dwSimAdd) { // TODO: 在此添加控件通知处理程序代码 BOOL fTest; TCHAR pSql[MAX_PATH]={0}; _stprintf(pSql,L"delete from %s where %s = %s & %s = %d",TAB_NAME_REVSMS, LS_ITEM_TIME,pTime,LS_ITEM_SIM,dwSimAdd); fTest = m_sqlite.DirectStatement(pSql); return fTest; } bool CDbMonitor::DbDelSms(LPCTSTR pTime,LPCTSTR pNumber) { // TODO: 在此添加控件通知处理程序代码 BOOL fTest; TCHAR pSql[MAX_PATH]={0}; _stprintf(pSql,L"delete from %s where (%s = \"%s\") & (%s = %s)",TAB_NAME_REVSMS, LS_ITEM_TIME,pTime,LS_ITEM_NUMBER,pNumber); //_stprintf(pSql,L"delete from %s where %s = \"%s\"",TAB_NAME_REVSMS, // LS_ITEM_TIME,pTime); fTest = m_sqlite.DirectStatement(pSql); return fTest; } bool CDbMonitor::DbMarkNewSmsReaded(DWORD dwSimAdd) { BOOL fTest; TCHAR pSql[MAX_PATH]={0}; _stprintf(pSql,L"UPDATE %s SET %s = %d where (%s=%d)&(%s=%d)",TAB_NAME_REVSMS, LS_ITEM_FLAG,F_LOC_READ,LS_ITEM_SIM,dwSimAdd,LS_ITEM_FLAG,F_LOC_NEW); RETAILMSG(1,(L"pSql: %s\n",pSql)); fTest = m_sqlite.DirectStatement(pSql); return fTest; } bool CDbMonitor::DbMarkNewSmsReaded(LPCTSTR pTime,DWORD dwSimAdd) { BOOL fTest; TCHAR pSql[MAX_PATH]={0}; _stprintf(pSql,L"UPDATE %s SET %s = %d where (%s = \"%s\")&(%s=%d)&(%s=%d)",TAB_NAME_REVSMS, LS_ITEM_FLAG,F_LOC_READ,LS_ITEM_TIME,pTime,LS_ITEM_SIM,dwSimAdd,LS_ITEM_FLAG,F_LOC_NEW); RETAILMSG(1,(L"pSql: %s\n",pSql)); fTest = m_sqlite.DirectStatement(pSql); return fTest; } bool CDbMonitor::DelLocSmsPastN(int iNum) { BOOL fTest; #if 1 TCHAR pSql[MAX_PATH]={0}; //实际上这条语句只能删除一条记录，待完善。。。 _stprintf(pSql,L"delete from %s where rowid = (select rowid from %s order by %s limit 0,%d)",TAB_NAME_REVSMS,TAB_NAME_REVSMS, LS_ITEM_TIME,iNum); RETAILMSG(1,(L"pSql: %s\n",pSql)); fTest = m_sqlite.DirectStatement(pSql); #endif return fTest; } int CDbMonitor::GetLocSmsNum() { int iCount=0; BOOL fTest; TCHAR pSql[MAX_PATH]={0}; _stprintf(pSql,L"SELECT COUNT(*) count FROM %s",TAB_NAME_REVSMS); CSqlStatement* stmt = m_sqlite.Statement(pSql); iCount = stmt->GetRowNum(); if(stmt) delete stmt; return iCount; } int CDbMonitor::GetLocNewSmsNum() { int iCount = 0; BOOL fTest = FALSE; TCHAR pSql[MAX_PATH] = {0}; CSqlStatement* stmt = NULL; _stprintf(pSql,L"SELECT COUNT(*) count FROM %s where (%s=%d)",TAB_NAME_REVSMS,LS_ITEM_FLAG,F_LOC_NEW); stmt = m_sqlite.Statement(pSql); if (NULL != stmt) { iCount = stmt->GetRowNum(); wprintf(L"========DataBase::GetLocNewSmsNum; the count is %d\r\n", iCount); } if(NULL != stmt) delete stmt; return iCount; } bool CDbMonitor::DbReadNewSms(DWORD dwSimAdd,LPTSTR pName,LPTSTR pNumber,LPTSTR pTime,LPTSTR pContent) { BOOL fTest=FALSE; TCHAR pSql[MAX_PATH]={0}; int iNextRet = 0; _stprintf(pSql,L"SELECT * FROM %s where (%s=%d)&(%s=%d) order by %s desc",TAB_NAME_REVSMS, LS_ITEM_FLAG,F_LOC_NEW,LS_ITEM_SIM,dwSimAdd,LS_ITEM_TIME); wprintf(L"========DataBase::Fun:%s; pSql: %s\r\n", __FUNCTIONW__, pSql); CSqlStatement* stmt = m_sqlite.Statement(pSql); if (NULL != stmt) { int nFields = stmt->Fields(); TCHAR tcFieldName[128]={0}; while (SQLITE_ROW == (iNextRet = stmt->NextRow())) { for ( int nCol=0; nCol < nFields; nCol++ ) { _tcscpy(tcFieldName,stmt->FieldName(nCol)); if(!_tcscmp(tcFieldName,LS_ITEM_NAME)) { _tcscpy(pName,stmt->ValueString(nCol)); } else if(!_tcscmp(tcFieldName,LS_ITEM_NUMBER)) { _tcscpy(pNumber,stmt->ValueString(nCol)); } else if(!_tcscmp(tcFieldName,LS_ITEM_TIME)) { _tcscpy(pTime,stmt->ValueString(nCol)); } else if(!_tcscmp(tcFieldName,LS_ITEM_CONTENT)) { _tcscpy(pContent,stmt->ValueString(nCol)); } } fTest=TRUE; wprintf(L"Fun:%s; Ok,we get the sms at %d num is %s\r\n", __FUNCTIONW__, dwSimAdd,pNumber); break; // 通常这里只会有一条，所以直接退出 } } if(stmt) delete stmt; return fTest; } int CDbMonitor::DbReadBatSms(LPVOID* ppSmsNode) { int iRetCode = 0, nCol = 0, nRow = 0, nFields = 0, iNextRet = 0; TCHAR pSql[MAX_PATH] = {0}, tcFieldName[MAX_MES_LEN] = {0}; PSMS_NODE pSmsNodeHead = NULL, pNewNode = NULL, pIndexNode = NULL; CSqlStatement* stmt = NULL; LPTSTR pDataValue = NULL; _stprintf(pSql,L"SELECT * FROM %s order by %s desc",TAB_NAME_REVSMS,LS_ITEM_TIME); wprintf(L"========DataBase::Fun:%s; pSql: %s\r\n", __FUNCTIONW__, pSql); stmt = m_sqlite.Statement(pSql); if (NULL != stmt) { nFields = stmt->Fields(); while (SQLITE_ROW == (iNextRet = stmt->NextRow())) { pNewNode = new SMS_NODE; if (NULL == pNewNode) { wprintf(L"========DataBase::Fun:%s; exhaust memory\r\n", __FUNCTIONW__); iRetCode = -2; break; } memset(pNewNode,0,sizeof(SMS_NODE)); pNewNode->pNext = NULL;