int CDbMeter::JoinTableShow(const char* dbfilename) { try { CppSQLite3DB db; db.open(dbfilename); CppSQLite3Buffer bufSQL; CppSQLite3Table t; db.execDML("CREATE TABLE IF NOT EXISTS jointable (joinkey INTEGER PRIMARY KEY, EUI64ID CHAR(17), jointry INTEGER);"); bufSQL.format("SELECT * FROM jointable;"); t = db.getTable(bufSQL); for ( int row = 0; row < t.numRows(); row++) { t.setRow(row); for (int fld=0; fld<t.numFields(); fld++) { if( !t.fieldIsNull(fld)) XDEBUG("%s | ", t.fieldValue(fld)); } XDEBUG("\r\n"); } } catch( CppSQLite3Exception& e ) { XDEBUG("%s\r\n",e.errorMessage()); return e.errorCode(); } return SQLITE_OK; }
BOOL Maxthon3PlugIn::IsWorked() { BOOL bRet = FALSE; if (m_pMemFavoriteDB) { CppSQLite3DB objSqliteDatabase; objSqliteDatabase.openmem(m_pMemFavoriteDB, ""); bRet = objSqliteDatabase.tableExists("MyFavNodes"); if (bRet) { CppSQLite3Table objSqliteTable = objSqliteDatabase.getTable("select * from MyFavNodes"); if (14 != objSqliteTable.numFields()) { bRet = FALSE; } } } return bRet; }
EXPORT_C gint32 CContactDb::InitEntityDb(gchar* dbName) /* fill fields name */ { strcpy(m_dbName, dbName); OpenDatabase(); CppSQLite3Table contactTable = m_dbBeluga.getTable("select * from contact limit 1;"); m_pFieldsName = g_ptr_array_sized_new(contactTable.numFields()); if (!m_pFieldsName) { CloseDatabase(); return ERROR(ESide_Client, EModule_Db, ECode_No_Memory); } for (int i=0; i<contactTable.numFields(); i++) g_ptr_array_add(m_pFieldsName, g_string_new((gchar*)contactTable.fieldName(i))); CloseDatabase(); return 0; }
int main(int argc, char **argv) { testCppSQLite(); try { DrRobot::Parser file = DrRobot::Parser("files/simple.csv"); CppSQLite3DB* db = getSQLiteDB(); string statement = "create table _data ( "; string headers = ""; //Print headers for(int j=0;j<file.columnCount();j++){ std::cout << file.getHeaderElement(j) << "\t\t"; statement += file.getHeaderElement(j)+" TEXT,"; headers += file.getHeaderElement(j); if(j<file.columnCount()-1) { headers += ","; } } statement+="id INTEGER PRIMARY KEY ASC)"; std::cout<<std::endl; std::cout<<statement<<std::endl; db->execDML(statement.c_str()); for(int i=0;i<file.rowCount();i++) { statement = "insert into _data Values("; for(int j=0;j<file.columnCount();j++){ std::cout << file[i][j] << "\t\t"; statement += "\""+file[i][j]+"\","; } statement += "null)"; std::cout << "\n" <<statement << "\t\t"; db->execDML(statement.c_str()); std::cout<<std::endl; } CppSQLite3Table t = db->getTable("select * from _data;"); for (int fld = 0; fld < t.numFields(); fld++) { cout << t.fieldName(fld) << "|"; } cout << endl; for (int row = 0; row < t.numRows(); row++) { t.setRow(row); for (int fld = 0; fld < t.numFields(); fld++) { if (!t.fieldIsNull(fld)) cout << t.fieldValue(fld) << "|"; else cout << "NULL" << "|"; } cout << endl; } } catch (DrRobot::Error &e) { std::cerr << e.what() << std::endl; } shutDownDB(); return 0; }
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); bufSQL.format("insert into emp (empname) values (%Q);", NULL); cout << (const char*)bufSQL << endl; db->execDML(bufSQL); //////////////////////////////////////////////////////////////////// // Fetch table at once, and also show how to // use CppSQLiteTable::setRow() method ////////////////////////////////////////////////////////////////// cout << endl << "getTable() test" << endl; CppSQLite3Table t = db->getTable("select * from emp order by 1;"); for (fld = 0; fld < t.numFields(); fld++) { cout << t.fieldName(fld) << "|"; } cout << endl; for (int row = 0; row < t.numRows(); row++) { t.setRow(row); for (int fld = 0; fld < t.numFields(); fld++) { if (!t.fieldIsNull(fld)) cout << t.fieldValue(fld) << "|"; else cout << "NULL" << "|"; } cout << endl; } //////////////////////////////////////////////////////////////////// // Test CppSQLiteBinary by storing/retrieving some binary data, checking // it afterwards to make sure it is the same ////////////////////////////////////////////////////////////////// cout << endl << "Binary data test" << endl; db->execDML("create table bindata(desc char(10), data blob);"); unsigned char bin[256]; CppSQLite3Binary blob; for (i = 0; i < sizeof bin; i++) { bin[i] = i; } blob.setBinary(bin, sizeof bin); bufSQL.format("insert into bindata values ('testing', %Q);", blob.getEncoded()); db->execDML(bufSQL); cout << "Stored binary Length: " << sizeof bin << endl; q = db->execQuery("select data from bindata where desc = 'testing';"); if (!q.eof()) { blob.setEncoded((unsigned char*)q.fieldValue("data")); cout << "Retrieved binary Length: " << blob.getBinaryLength() << endl; } const unsigned char* pbin = blob.getBinary(); for (i = 0; i < sizeof bin; i++) { if (pbin[i] != i) { cout << "Problem: i: ," << i << " bin[i]: " << pbin[i] << endl; } } ///////////////////////////////////////////////////////// // Pre-compiled Statements Demo ///////////////////////////////////////////////////////////// cout << endl << "Transaction test, creating " << nRowsToCreate; cout << " rows please wait..." << endl; db->execDML("drop table emp;"); db->execDML("create table emp(empno int, empname char(20));"); tmStart = time(0); db->execDML("begin transaction;"); CppSQLite3Statement stmt = db->compileStatement( "insert into emp values (?, ?);"); for (i = 0; i < nRowsToCreate; i++) { char buf[16]; sprintf(buf, "EmpName%06d", i); stmt.bind(1, i); stmt.bind(2, buf); stmt.execDML(); stmt.reset(); } db->execDML("commit transaction;"); tmEnd = time(0); cout << db->execScalar("select count(*) from emp;") << " rows in emp table in "; cout << tmEnd-tmStart << " seconds (that was even faster!)" << endl; cout << endl << "End of tests" << endl; } catch (CppSQLite3Exception& e) { cerr << e.errorCode() << ":" << e.errorMessage() << endl; } }
int CDbMeter::GroupShowCommand(const char* dbfilename, int nGroupKey, GROUPCOMMANDINFO *pGroupCommandinfo) { try { CppSQLite3DB db; db.open(dbfilename); CppSQLite3Buffer bufSQL; CppSQLite3Table t; char szTime[16] = {0,}; char szTmpTime[5] = {0,}; if (0 < nGroupKey) bufSQL.format("SELECT * FROM command WHERE GroupKey=%d;", nGroupKey); else bufSQL.format("SELECT * FROM command;"); t = db.getTable(bufSQL); for( int fld = 0; fld < t.numFields(); fld++) XDEBUG("%s | ", t.fieldName(fld)); XDEBUG("\r\n"); for ( int row = 0; row < t.numRows(); row++) { t.setRow(row); for (int fld=0; fld<t.numFields(); fld++) { if( !t.fieldIsNull(fld)) { switch(fld) { case 0: pGroupCommandinfo->nCommandKey = atoi( t.fieldValue(fld) ); break; case 1: pGroupCommandinfo->nGroupKey = atoi( t.fieldValue(fld) ); break; case 2: StrToEUI64( t.fieldValue(fld), &(pGroupCommandinfo->id) ); break; case 3: memcpy( pGroupCommandinfo->pGroupName, t.fieldValue(fld), strlen(t.fieldValue(fld)) ); break; case 4: pGroupCommandinfo->nCommandState = atoi( t.fieldValue(fld)); break; case 5: VARAPI_StringToOid( t.fieldValue(fld), &(pGroupCommandinfo->commandOID) ); break; case 6: // Create TimeStamp memcpy(szTime, t.fieldValue(fld), strlen(t.fieldValue(fld))); memcpy(szTmpTime, szTime, 4); //Year pGroupCommandinfo->commandCreateTime.year = atoi(szTmpTime); memcpy(szTmpTime, &(szTime[4]), 2 ); //Month pGroupCommandinfo->commandCreateTime.mon = atoi(szTmpTime); memcpy(szTmpTime, &(szTime[6]), 2 ); //Day pGroupCommandinfo->commandCreateTime.day = atoi(szTmpTime); memcpy(szTmpTime, &(szTime[8]), 2 ); //Hour pGroupCommandinfo->commandCreateTime.hour = atoi(szTmpTime); memcpy(szTmpTime, &(szTime[10]), 2 ); //Min pGroupCommandinfo->commandCreateTime.min = atoi(szTmpTime); memcpy(szTmpTime, &(szTime[12]), 2 ); //Sec pGroupCommandinfo->commandCreateTime.sec = atoi(szTmpTime); break; case 7: memcpy(szTime, t.fieldValue(fld), strlen(t.fieldValue(fld))); memcpy(szTmpTime, szTime, 4); //Year pGroupCommandinfo->commandExecTime.year = atoi(szTmpTime); memcpy(szTmpTime, &(szTime[4]), 2 ); //Month pGroupCommandinfo->commandExecTime.mon = atoi(szTmpTime); memcpy(szTmpTime, &(szTime[6]), 2 ); //Day pGroupCommandinfo->commandExecTime.day = atoi(szTmpTime); memcpy(szTmpTime, &(szTime[8]), 2 ); //Hour pGroupCommandinfo->commandExecTime.hour = atoi(szTmpTime); memcpy(szTmpTime, &(szTime[10]), 2 ); //Min pGroupCommandinfo->commandExecTime.min = atoi(szTmpTime); memcpy(szTmpTime, &(szTime[12]), 2 ); //Sec pGroupCommandinfo->commandExecTime.sec = atoi(szTmpTime); break; case 8: pGroupCommandinfo->nResult = 0; break; case 9: break; } XDEBUG("%s | ", t.fieldValue(fld)); } } XDEBUG("\r\n"); } db.close(); } catch( CppSQLite3Exception& e ) { XDEBUG("%s\r\n",e.errorMessage()); return e.errorCode(); } return SQLITE_OK; }