C++ CppSQLite3DB::execScalar方法代码示例

void CDlgEventList::ShowPage(int nPageIndex)
{
	CString str;
	int i = 0;
	int nStartIndex = 0;
	int nOffset = 0;

	CppSQLite3DB db;
	db.open(PATH_SQLITE_DB_808);	//打开数据库
	
	//查询记录总数量
	m_nRecordCount = db.execScalar("SELECT count(*) FROM event_info;");
	//计算总页数
	if(m_nRecordCount > 0)
		m_nPageCount = (m_nRecordCount-1)/elist_count+1;
	else
		m_nPageCount = 1;

	//在数据库中查询第nPageIndex页的elist_count条数据
	char szSqlBuffer[512];
	sprintf(szSqlBuffer, "SELECT * FROM event_info ORDER BY event_ID DESC LIMIT %d, %d;", nPageIndex*elist_count, elist_count);
	CppSQLite3Query q = db.execQuery(szSqlBuffer);

	for( i = 0; i < elist_count; i++ )
	{
		if ( !q.eof() )	//数据行
		{
			m_nEvent_ID[i]		= q.getIntField("event_ID");
			m_list[i].chChar	= q.fieldValue("event_content");
			m_list[i].nState	= BTN_STATE_NORMAL;
			q.nextRow();
		}
		else			//空白行
		{
			m_ItemState[i]		= 0;
			m_list[i].chChar	= _T("");
			m_list[i].nState	= BTN_STATE_DISABLE;
		}
	}
	//释放statement
	q.finalize();

	db.close();	//关闭数据库
	return;
}

void CDlgEventList::ChangeReadStatus()
{
	CppSQLite3DB db;
	db.open(PATH_SQLITE_DB_808);	//打开数据库
	int nUnReadSMS = 0;	//未读短信数量
	const char* pszSQL;
	//查询中心信息未读短信总数量
	pszSQL = "select count(*) from text_info where read_status = 0;";
	nUnReadSMS = db.execScalar(pszSQL);
	if(nUnReadSMS > 0)
	{
		//更新状态:未读->已读
		pszSQL = "update text_info set read_status = 1;";
        db.execDML(pszSQL);
	}
	db.close();
	m_bSMSCenter = FALSE;//将新信息提示关闭
}

void testCppSQLite()
{
    try
    {
        int i, fld;
        time_t tmStart, tmEnd;
        
        remove(gszFile);
        
        CppSQLite3DB* db = getSQLiteDB();
        cout << "SQLite Version: " << db->SQLiteVersion() << endl;
        
        cout << endl << "Creating emp table" << endl;
        db->execDML("create table emp(empno int, empname char(20));");
        ///////////////////////////////////////////////////////////////
        // Execute some DML, and print number of rows affected by each one
        ///////////////////////////////////////////////////////////////
        cout << endl << "DML tests" << endl;
        int nRows = db->execDML("insert into emp values (7, 'David Beckham');");
        cout << nRows << " rows inserted" << endl;
        
        nRows = db->execDML(
                           "update emp set empname = 'Christiano Ronaldo' where empno = 7;");
        cout << nRows << " rows updated" << endl;
        
        nRows = db->execDML("delete from emp where empno = 7;");
        cout << nRows << " rows deleted" << endl;
        
        /////////////////////////////////////////////////////////////////
        // Transaction Demo
        // The transaction could just as easily have been rolled back
        /////////////////////////////////////////////////////////////////
        int nRowsToCreate(50000);
        cout << endl << "Transaction test, creating " << nRowsToCreate;
        cout << " rows please wait..." << endl;
        tmStart = time(0);
        db->execDML("begin transaction;");
        
        for (i = 0; i < nRowsToCreate; i++)
        {
            char buf[128];
            sprintf(buf, "insert into emp values (%d, 'Empname%06d');", i, i);
            db->execDML(buf);
        }
        
        db->execDML("commit transaction;");
        tmEnd = time(0);
        
        ////////////////////////////////////////////////////////////////
        // Demonstrate CppSQLiteDB::execScalar()
        ////////////////////////////////////////////////////////////////
        cout << db->execScalar("select count(*) from emp;") 
        << " rows in emp table in ";
        cout << tmEnd-tmStart << " seconds (that was fast!)" << endl;
        
        ////////////////////////////////////////////////////////////////
        // Re-create emp table with auto-increment field
        ////////////////////////////////////////////////////////////////
        cout << endl << "Auto increment test" << endl;
        db->execDML("drop table emp;");
        db->execDML(
                   "create table emp(empno integer primary key, empname char(20));");
        cout << nRows << " rows deleted" << endl;
        
        for (i = 0; i < 5; i++)
        {
            char buf[128];
            sprintf(buf, 
                    "insert into emp (empname) values ('Empname%06d');", i+1);
            db->execDML(buf);
            cout << " primary key: " << db->lastRowId() << endl;
        }
        
        ///////////////////////////////////////////////////////////////////
        // Query data and also show results of inserts into auto-increment field
        //////////////////////////////////////////////////////////////////
        cout << endl << "Select statement test" << endl;
        CppSQLite3Query q = db->execQuery("select * from emp order by 1;");
        
        for (fld = 0; fld < q.numFields(); fld++)
        {
            cout << q.fieldName(fld) << "(" << q.fieldDeclType(fld) << ")|";
        }
        cout << endl;
        
        while (!q.eof())
        {
            cout << q.fieldValue(0) << "|";
            cout << q.fieldValue(1) << "|" << endl;
            q.nextRow();
        }
        
        ///////////////////////////////////////////////////////////////
        // SQLite's printf() functionality. Handles embedded quotes and NULLs
        ////////////////////////////////////////////////////////////////
        cout << endl << "SQLite sprintf test" << endl;
        CppSQLite3Buffer bufSQL;
        bufSQL.format("insert into emp (empname) values (%Q);", "He's bad");
        cout << (const char*)bufSQL << endl;
        db->execDML(bufSQL);
//.........这里部分代码省略.........

//.........这里部分代码省略.........

        cout << endl << "Specify fields by name" << endl;
        for (row = 0; row < t.numRows(); row++)
        {
            t.setRow(row);
            cout << t.getIntField("no") << "|";
            cout << t.getStringField("name") << "|";
            cout << t.getIntField("qty") << "|";
            cout << t.getFloatField("cost") << "|" << endl;
        }

        cout << endl << "specify NULL values tests" << endl;
        for (row = 0; row < t.numRows(); row++)
        {
            t.setRow(row);
            cout << t.getIntField("no") << "|";
            cout << t.getStringField("name", "NULL") << "|";
            cout << t.getIntField("qty", -1) << "|";
            cout << t.getFloatField("cost", -3.33) << "|" << endl;
        }


		////////////////////////////////////////////////////////////////////////////////
		// Demonstrate multi-statement DML
		// Note that number of rows affected is only from the last statement
		// when multiple statements are used
		////////////////////////////////////////////////////////////////////////////////
        cout << endl << "Multi-Statement execDML()" << endl;
		const char* szDML = "insert into parts values(5, 'part5', 500, 5.55);"
							"insert into parts values(6, 'part6', 600, 6.66);"
							"insert into parts values(7, 'part7', 700, 7.77);";
		int nRows = db.execDML(szDML);
		cout << endl << nRows << " rows affected" << endl;
        cout << db.execScalar("select count(*) from parts;") << " rows in parts table" << endl;
		            szDML = "delete from parts where no = 2;"
							"delete from parts where no = 3;";
		nRows = db.execDML(szDML);
        cout << endl << nRows << " rows affected" << endl;
        cout << db.execScalar("select count(*) from parts;") << " rows in parts table" << endl;


		////////////////////////////////////////////////////////////////////////////////
		// Demonstrate new typing system & BLOBS
		// ANy data can be stored in any column
		////////////////////////////////////////////////////////////////////////////////
        cout << endl << "Data types and BLOBs()" << endl;
        db.execDML("create table types(no int, "
				"name char(20), qty float, dat blob);");
        db.execDML("insert into types values(null, null, null, null);");
        db.execDML("insert into types values(1, 2, 3, 4);");
        db.execDML("insert into types values(1.1, 2.2, 3.3, 4.4);");
        db.execDML("insert into types values('a', 'b', 'c', 'd');");

        CppSQLite3Statement stmt = db.compileStatement("insert into types values(?,?,?,?);");
        unsigned char buf[256];
		memset(buf, 1, 1); stmt.bind(1, buf, 1);
		memset(buf, 2, 2); stmt.bind(2, buf, 2);
		memset(buf, 3, 3); stmt.bind(3, buf, 3);
		memset(buf, 4, 4); stmt.bind(4, buf, 4);
		stmt.execDML();
        cout << db.execScalar("select count(*) from types;") << " rows in types table" << endl;

        q = db.execQuery("select * from types;");
        while (!q.eof())
        {
			for (int i = 0; i < q.numFields(); i++)

BOOL RemoveOldEntries(bool checkIdleTime)
{
	Log(StrF(_T("Beginning of RemoveOldEntries MaxEntries: %d - Keep days: %d"), CGetSetOptions::GetMaxEntries(), CGetSetOptions::GetExpiredEntries()));

	try
	{
		CppSQLite3DB db;
		CString csDbPath = CGetSetOptions::GetDBPath();
		db.open(csDbPath);

		if(CGetSetOptions::GetCheckForMaxEntries())
		{
			long lMax = CGetSetOptions::GetMaxEntries();
			if(lMax >= 0)
			{
				CClipIDs IDs;
				int clipId;
				
				CppSQLite3Query q = db.execQueryEx(_T("SELECT lID, lShortCut, lParentID, lDontAutoDelete, stickyClipOrder, stickyClipGroupOrder FROM Main WHERE bIsGroup = 0 ORDER BY clipOrder DESC LIMIT -1 OFFSET %d"), lMax);
				while(q.eof() == false)
				{
					int shortcut = q.getIntField(_T("lShortCut"));
					int dontDelete = q.getIntField(_T("lDontAutoDelete"));
					int parentId = q.getIntField(_T("lParentID"));
					double stickyClipOrder = q.getFloatField(_T("stickyClipOrder"));
					double stickyClipGroupOrder = q.getFloatField(_T("stickyClipGroupOrder"));

					//Only delete entries that have no shortcut and don't have the flag set and aren't in groups and 
					if(shortcut == 0 && 
						dontDelete == 0 &&
						parentId <= 0 &&
						stickyClipOrder == 0.0 &&
						stickyClipGroupOrder == 0.0)
					{
						clipId = q.getIntField(_T("lID"));
						IDs.Add(clipId);
						Log(StrF(_T("From MaxEntries - Deleting Id: %d"), clipId));
					}

					q.nextRow();
				}

				if(IDs.GetCount() > 0)
				{
					IDs.DeleteIDs(false, db);
				}
			}
		}
		
		if(CGetSetOptions::GetCheckForExpiredEntries())
		{
			long lExpire = CGetSetOptions::GetExpiredEntries();
			
			if(lExpire)
			{
				CTime now = CTime::GetCurrentTime();
				now -= CTimeSpan(lExpire, 0, 0, 0);
				
				CClipIDs IDs;
				
				CppSQLite3Query q = db.execQueryEx(_T("SELECT lID FROM Main ")
													_T("WHERE lastPasteDate < %d AND ")
													_T("bIsGroup = 0 AND lShortCut = 0 AND lParentID <= 0 AND lDontAutoDelete = 0 AND stickyClipOrder = 0 AND stickyClipGroupOrder = 0"), (int)now.GetTime());

				while(q.eof() == false)
				{
					IDs.Add(q.getIntField(_T("lID")));

					Log(StrF(_T("From Clips Expire - Deleting Id: %d"), q.getIntField(_T("lID"))));

					q.nextRow();
				}
				
				if(IDs.GetCount() > 0)
				{
					IDs.DeleteIDs(false, db);
				}
			}
		}

		int toDeleteCount = db.execScalar(_T("SELECT COUNT(clipID) FROM MainDeletes"));

		Log(StrF(_T("Before Deleting emptied out data, count: %d, Idle Seconds: %f"), toDeleteCount, IdleSeconds()));

		//Only delete 1 at a time, was finding that it was taking a long time to delete clips, locking the db and causing other queries
		//to lock up
		CppSQLite3Query q = db.execQueryEx(_T("SELECT * FROM MainDeletes LIMIT %d"), CGetSetOptions::GetMainDeletesDeleteCount());
		int deleteCount = 0;

		while(q.eof() == false)
		{
			double idleSeconds = IdleSeconds();
			if(checkIdleTime == false || idleSeconds > CGetSetOptions::GetIdleSecondsBeforeDelete())
			{
				//delete any data items sitting out there that the main table data was deleted
				//this was done to speed up deleted from the main table
				deleteCount = db.execDMLEx(_T("DELETE FROM MainDeletes WHERE clipID=%d"), q.getIntField(_T("clipID")));
			}
			else
			{
//.........这里部分代码省略.........