uint32 CBrainMemory::GetAllPartOfSpeech(int64 ParentID){ CppSQLite3Buffer SQL; char a[30],b[30],c[30]; uint32 PartOfSpeech = 0; // if(!RBrainHasTable(ID))return 0; ToRBrain(ParentID); int64toa(ParentID,a); int64toa(PARTOFSPEECH_START,b); int64toa(PARTOFSPEECH_END,c); SQL.format("select %s from \"%s\" where %s > \"%s\" AND %s< \"%s\";", RB_SPACE_TYPE, a, RB_SPACE_TYPE, b, RB_SPACE_TYPE, c ); CppSQLite3Table t = BrainDB.getTable(SQL); for (int row = 0; row < t.numRows(); row++) { t.setRow(row); int64 type = t.getInt64Field(0); PartOfSpeech |= (uint32)((type-PARTOFSPEECH_START)); } return PartOfSpeech; }
int CDbMeter::JoinTableDelete(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); if( t.numRows() == 0 ) { XDEBUG("Failure: Don't have JoinTable\r\n"); } else { db.execDML("DROP TABLE joinTable;"); } } 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; }
int32 CLogicThread::GetActionRoom(int64 ParentID,ClauseLogicSense* cls){ CppSQLite3Buffer SQL; char a[30],b[30]; uint32 PartOfSpeech = 0; // if(!RBrainHasTable(ID))return 0; map<int64,MeaningPos>& RoomList = cls->ClauseMeaning; ToRBrain(ParentID); int64toa(ParentID,a); int64toa(MEMORY_INSTINCT,b); SQL.format("select %s,%s from \"%s\" where %s = \"%s\" ;", RB_SPACE_ID, RB_SPACE_VALUE, a, RB_SPACE_TYPE, b ); CppSQLite3Table t = BrainDB.getTable(SQL); for (int row = 0; row < t.numRows(); row++) { t.setRow(row); int64 ID = t.getInt64Field(0); int64 Value = t.getInt64Field(1); MeaningPos Pos; Pos.RoomID = ID; Pos.ParamTokenPos = cls->NextTokenPos; RoomList[Value] = Pos; } return RoomList.size(); }
int32 CBrainMemory::GetAllPartOfSpeechRoom(int64 ParentID,map<int64,int64>& RoomList) { CppSQLite3Buffer SQL; char a[30],b[30],c[30]; uint32 PartOfSpeech = 0; // if(!RBrainHasTable(ID))return 0; ToRBrain(ParentID); int64toa(ParentID,a); int64toa(PARTOFSPEECH_START,b); int64toa(PARTOFSPEECH_END,c); SQL.format("select %s,%s from \"%s\" where %s > \"%s\" AND %s< \"%s\";", RB_SPACE_ID, RB_SPACE_TYPE, a, RB_SPACE_TYPE, b, RB_SPACE_TYPE, c ); CppSQLite3Table t = BrainDB.getTable(SQL); for (int row = 0; row < t.numRows(); row++) { t.setRow(row); int64 ID = t.getInt64Field(0); int64 Type = t.getInt64Field(1); RoomList[ID] = Type; } return RoomList.size(); }
void __fastcall TFrmAlarmDetailList::grdErrorDetailSelectCell(TObject *Sender, int ACol, int ARow, bool &CanSelect) { TStringGrid* pGrid = ( TStringGrid* )Sender; MemoSolution->Clear(); MemoCause->Clear(); if(CanSelect = true) { if(pGrid->Cells[ 1 ][ ARow ] != "" && pGrid->Cells[ 1 ][ ARow ] != "0" ) { AnsiString szQuery = "SELECT * FROM " + g_szDBList[_nTableIndex]; INT nErrCode = pGrid->Cells[ 1 ][ ARow ].ToInt(); CppSQLite3DB dbMain; dbMain.open( AnsiString( g_MainDBPath ).c_str() ); CppSQLite3Table tblAlarm = dbMain.getTable( szQuery.c_str() ); tblAlarm.setRow( nErrCode-1 ); const char* szCause = tblAlarm.getStringField( "cause", "" ); const char* szSolution = tblAlarm.getStringField( "solution", "" ); MemoCause->Lines->Add( szCause ); MemoSolution->Lines->Add( szSolution ); dbMain.close(); pGrid->Refresh(); } } }
int CDbMeter::JoinTableAdd(const char* dbfilename, char* szID) { 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 WHERE EUI64ID=%Q", szID); t = db.getTable(bufSQL); if( t.numRows() == 0 ) { bufSQL.clear(); bufSQL.format("INSERT INTO jointable(EUI64ID, jointry) VALUES(%Q, %d);", szID, 0); db.execDML(bufSQL); } else XDEBUG("Jointable ID Duplicate!!\r\n"); db.close(); } catch( CppSQLite3Exception& e ) { XDEBUG("%s\r\n",e.errorMessage()); return e.errorCode(); } return SQLITE_OK; }
void __fastcall TFrmAlarmDetailList::ChartAlarmClickSeries(TCustomChart *Sender, TChartSeries *Series, int ValueIndex, TMouseButton Button, TShiftState Shift, int X, int Y) { String strStartDateTime = FormatDateTime("yyyy-mm-dd 오전 00:00:00", DateTimeAlarmStart->Date); String strEndDateTime = FormatDateTime("yyyy-mm-dd 오전 00:00:00", DateTimeAlarmEnd->Date + 1); try { AnsiString szQuery = " select *, count(Alarm_idx) AS Frequency from AlarmLog " " where Set_date BETWEEN '" + strStartDateTime + "' AND '" + strEndDateTime + "'" " AND Alarm_idx < 1000 group by Alarm_idx order by Frequency DESC;"; CppSQLite3Table OrderedTable = dbMain.getTable(szQuery.c_str()); OrderedTable.setRow(ValueIndex); AnsiString strCode = (String)OrderedTable.fieldValue("Alarm_idx"); AnsiString szAlarmQuery = " select * from AlarmLog, Alarm, Groups, Recipe where Alarm_idx like " + strCode + " AND Set_date BETWEEN '" + strStartDateTime + "' AND '" + strEndDateTime + "'" " AND AlarmLog.Alarm_idx = Alarm.idx" " AND AlarmLog.Group_idx = Groups.idx" " AND AlarmLog.group_idx = Recipe.group_idx" " AND AlarmLog.Recipe_idx = Recipe.idx ORDER BY Set_Date DESC"; CppSQLite3Table SelectedTable = dbMain.getTable(szAlarmQuery.c_str()); for(int i = 1; i < grdAlarmLog->RowCount; i++) { grdAlarmLog->Rows[i]->Clear(); } grdAlarmLog->RowCount = 2; if (SelectedTable.numRows() > 0) { for(int row = 0; row < SelectedTable.numRows(); row++) { SelectedTable.setRow(row); grdAlarmLog->Cells[0][row+1] = IntToStr(row+1); grdAlarmLog->Cells[1][row+1] = (String)SelectedTable.fieldValue(7); // Start date grdAlarmLog->Cells[2][row+1] = (String)SelectedTable.fieldValue(8); // End date grdAlarmLog->Cells[3][row+1] = (String)SelectedTable.fieldValue(9); // Alarm Code grdAlarmLog->Cells[4][row+1] = (String)SelectedTable.fieldValue(11); // Alarm Name grdAlarmLog->RowCount = row+2; } } } catch (CppSQLite3Exception& e) { cerr << e.errorCode() << ":" << e.errorMessage() << endl; } }
int CDbMeter::GroupDelete(const char* dbfilename, int nGroupKey) { try { CppSQLite3DB db; db.open(dbfilename); // 그룹테이블에 그룹이름이 있느지 확인 CppSQLite3Buffer bufSQL; //CppSQLite3Query q; CppSQLite3Table t; bufSQL.format("SELECT GroupKey FROM groups WHERE GroupKey=%d;", nGroupKey); t = db.getTable(bufSQL); if( t.numRows() == 0 ) { XDEBUG("ERROR: %d GroupKey doesn't exist in Groups Table!!\r\n", nGroupKey); return IF4ERR_GROUP_NAME_NOT_EXIST; } else { bufSQL.clear(); bufSQL.format("DELETE FROM groups WHERE GroupKey=%d;", nGroupKey); db.execDML(bufSQL); XDEBUG("%d GroupKey deleted in Groups Table!!\r\n", nGroupKey); //bufSQL.format("SELECT Groups.GroupName FROM groupmember LEFT OUTER JOIN groups ON groupmember.GroupKey=groups.GroupKey LEFT OUTER JOIN member ON groupmember.EUI64ID=member.EUI64ID WHERE groups.GroupName = %s;", szGroupName); } bufSQL.clear(); bufSQL.format("SELECT GroupKey FROM groupmember WHERE GroupKey=%d;", nGroupKey); t = db.getTable(bufSQL); if( t.numRows() == 0 ) { XDEBUG("ERROR: %d GroupKey doesn't exist!! in Groupmember Table!!\r\n", nGroupKey); return IF4ERR_GROUP_NAME_NOT_EXIST; } else { bufSQL.clear(); bufSQL.format("DELETE FROM groupmember WHERE GroupKey=%d;", nGroupKey); db.execQuery(bufSQL); XDEBUG("%d GroupKey deleted in Groupmember Table!!\r\n", nGroupKey); } db.close(); } catch( CppSQLite3Exception& e ) { XDEBUG("%s\r\n",e.errorMessage()); return e.errorCode(); } return SQLITE_OK; }
void __fastcall TFrmAlarmDetailList::BtnTimeReadDataClick(TObject *Sender) { for( INT nRow = 0; nRow < grdAlarmLog->RowCount; nRow++ ) { grdAlarmLog->Rows[ nRow+1 ]->Clear(); } grdAlarmLog->RowCount = 2; AnsiString strStartDateTime = FormatDateTime("yyyy-mm-dd", DateTimeAlarmStart->Date); AnsiString strEndDateTime = FormatDateTime("yyyy-mm-dd", DateTimeAlarmEnd->Date + 1); try { AnsiString szQuery = "SELECT AlarmLog.set_date, AlarmLog.clear_date, Alarm.idx, Alarm.name, Groups.name, Recipe.name" " FROM Alarm, AlarmLog, Groups, Recipe" " WHERE AlarmLog.set_date BETWEEN '" + strStartDateTime + "' AND '" + strEndDateTime + "'" " AND AlarmLog.alarm_idx = Alarm.idx" " AND AlarmLog.group_idx = Groups.idx" " AND AlarmLog.group_idx = Recipe.group_idx" " AND AlarmLog.recipe_idx = recipe.idx" " ORDER BY AlarmLog.set_date DESC"; CppSQLite3Table tblAlarmLog = dbMain.getTable( szQuery.c_str() ); for( INT nRow = 0; nRow < tblAlarmLog.numRows(); nRow++ ) { tblAlarmLog.setRow( nRow ); grdAlarmLog->Cells[ 0 ][ nRow+1 ] = nRow+1; grdAlarmLog->Cells[ 1 ][ nRow+1 ] = tblAlarmLog.fieldValue( 0 ); grdAlarmLog->Cells[ 2 ][ nRow+1 ] = tblAlarmLog.fieldValue( 1 ); grdAlarmLog->Cells[ 3 ][ nRow+1 ] = tblAlarmLog.fieldValue( 2 ); grdAlarmLog->Cells[ 4 ][ nRow+1 ] = tblAlarmLog.fieldValue( 3 ); grdAlarmLog->RowCount = nRow+2; } AlarmFrequencyCount(); } catch( Exception& e ) { FrmCdBox->QueryMsg( "DB READ FAIL", "AlarmLog 데이터를 불러오는데 실패 하였습니다.", CD_OK ); } }
int CDbMeter::GroupAddMember(const char* dbfilename, int nGroupKey, char* szMemberId) { try { CppSQLite3DB db; db.open(dbfilename); CppSQLite3Buffer bufSQL; CppSQLite3Query q; CppSQLite3Table t; int nFindgroupkey = -1; bufSQL.format("SELECT EUI64ID FROM member WHERE EUI64ID=%Q;", szMemberId); t = db.getTable(bufSQL); if( t.numRows() == 0 ) { bufSQL.clear(); bufSQL.format("INSERT INTO member VALUES(%Q, 'Meter', 'none', '1.0.0', '1.0.0', 'Nomal', 'None');", szMemberId); db.execDML(bufSQL); } bufSQL.clear(); bufSQL.format("SELECT * FROM groups WHERE GroupKey=%d;", nGroupKey); t = db.getTable(bufSQL); if( t.numRows() == 1) nFindgroupkey = atoi(t.fieldValue(0)); else nFindgroupkey = -1; if( nFindgroupkey >= -1) { bufSQL.clear(); bufSQL.format("INSERT INTO groupmember(EUI64ID, GroupKey) VALUES(%Q, %d);", szMemberId, nGroupKey); db.execDML(bufSQL); } db.close(); } catch( CppSQLite3Exception& e ) { XDEBUG("%s\r\n",e.errorMessage()); return -1; } return SQLITE_OK; }
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; }
void TFrmAlarmDetailList::AlarmFrequencyCount() { String strStartDateTime = FormatDateTime("yyyy-mm-dd 오전 00:00:00", DateTimeAlarmStart->Date); String strEndDateTime = FormatDateTime("yyyy-mm-dd 오전 00:00:00", DateTimeAlarmEnd->Date + 1); try { AnsiString szQuery = "select *, count(Alarm_idx) AS Frequency from AlarmLog" " where Set_date between '" + strStartDateTime + "' AND '" + strEndDateTime + "'" " and Alarm_idx < 1000 group by Alarm_idx order by Frequency DESC"; CppSQLite3Table tblAlarmLog = dbMain.getTable( szQuery.c_str() ); int nFreq = 0; String strCode = ""; int nFieldCnt = tblAlarmLog.numRows(); if(nFieldCnt > 0) { ChartAlarm->Series[0]->Clear(); for( int i = 0; i < nFieldCnt; i++ ) { if(i >= 10) { return; } tblAlarmLog.setRow(i); nFreq = ((String)tblAlarmLog.fieldValue("Frequency")).ToInt(); strCode = (String)tblAlarmLog.fieldValue("alarm_idx"); ChartAlarm->Series[0]->Add(nFreq, strCode, (Graphics::TColor)(0x20000000)); } } } catch (CppSQLite3Exception& e) { } }
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; }
void TfrmUseSkipViewer::LoadUseSkipNameFromDB() { CppSQLite3DB db; String szSelectQuery = "SELECT * FROM UseSkip"; try { if( FileExists( g_MainDBPath ) == true ) { db.open( AnsiString( g_MainDBPath ).c_str() ); CppSQLite3Table table = db.getTable( AnsiString( szSelectQuery ).c_str() ); if( table.numRows() != 0 ) { for( int row = 0; row < table.numRows(); row++ ) { table.setRow( row ); String szName = table.getStringField( "name", "NULL" ); if( szName == "NULL" || szName == "" ) { szName = "UseSkip" + IntToStr( row ); } ComboBoxUseSkip->AddItem( szName, NULL ); } } db.close(); } } catch(Exception &e) { db.close(); ShowMessage("Use Skip DB select error! : " + e.Message); } }
int CDbMeter::GroupDeleteMember(const char* dbfilename, int nGroupKey, char* szMemberId) { try { CppSQLite3DB db; db.open(dbfilename); CppSQLite3Buffer bufSQL; //CppSQLite3Query q; CppSQLite3Table t; bufSQL.format("SELECT * FROM groupmember WHERE GroupKey=%d AND EUI64ID=%Q;", nGroupKey, szMemberId); //q = db.execQuery(bufSQL); t = db.getTable(bufSQL); if( t.numRows() == 0 ) { XDEBUG("ERROR: %s EUI64ID doesn't exist in %d Groupkey Table!!\r\n", szMemberId, nGroupKey); return IF4ERR_GROUP_NAME_NOT_EXIST; } else { bufSQL.clear(); bufSQL.format("DELETE FROM groupmember WHERE GroupKey=%d AND EUI64ID=%Q;", nGroupKey, szMemberId); db.execQuery(bufSQL); XDEBUG("%s Groupmember of %d GroupKey deleted in Groupmember Table!!\r\n", szMemberId, nGroupKey); } db.close(); } catch( CppSQLite3Exception& e ) { XDEBUG("%s\r\n",e.errorMessage()); return e.errorCode(); } return SQLITE_OK; }
int64 CBrainMemory::HasMeaningRoom(int64 ParentTable,int64 Meaning,int64 MeaningType){ CppSQLite3Buffer SQL; char a[30],b[30]; if(!RBrainHasTable(ParentTable))return 0; ToRBrain(ParentTable); int64toa(ParentTable,a); int64toa(MeaningType,b); SQL.format("select %s, %s from \"%s\" where %s = \"%s\";", RB_SPACE_ID, RB_SPACE_VALUE, a, RB_SPACE_TYPE, b ); CppSQLite3Table t = BrainDB.getTable(SQL); if(Meaning == 0){ for (int row = 0; row < t.numRows(); row++) { t.setRow(row); int64 ID = t.getInt64Field(0); int64 Value = t.getInt64Field(1); if(ID == Value){ return ID; //已经记忆过了,直接返回 } } }else{ for (int row = 0; row < t.numRows(); row++) { t.setRow(row); int64 ID = t.getInt64Field(0); int64 Value = t.getInt64Field(1); if(Value = Meaning){ return ID; //已经记忆过了,直接返回 } } } return 0; }
void __fastcall TFrmAlarmDetailList::BtnCodeReadDataClick(TObject *Sender) { for(int i = 1; i <=grdAlarmLog->RowCount; i++) { grdAlarmLog->Cells[0][i] =""; grdAlarmLog->Cells[1][i] =""; grdAlarmLog->Cells[2][i] =""; grdAlarmLog->Cells[3][i] =""; } if(EditAlarmCodeSearch->Text != "") { String strStartDateTime = FormatDateTime("yyyy-mm-dd 오전 00:00:00", DateTimeAlarmStart->Date); String strEndDateTime = FormatDateTime("yyyy-mm-dd 오전 00:00:00", DateTimeAlarmEnd->Date + 1); String CodeName = EditAlarmCodeSearch->Text.Trim(); grdAlarmLog->RowCount = 2; try { AnsiString szQuery = " select * from AlarmLog, Alarm, Groups, Recipe where Alarm_idx like " + CodeName + " AND Set_date BETWEEN '" + strStartDateTime + "' AND '" + strEndDateTime + "'" + " AND AlarmLog.Alarm_idx = Alarm.idx" + " AND AlarmLog.Group_idx = Groups.idx" + " AND AlarmLog.Group_idx = Recipe.group_idx" + " AND AlarmLog.Recipe_idx = Recipe.idx;"; CppSQLite3Table tblAlarmLog = dbMain.getTable(szQuery.c_str()); if (tblAlarmLog.numRows() > 0) { for (int row = 0; row < tblAlarmLog.numRows(); row++) { tblAlarmLog.setRow(row); grdAlarmLog->Cells[0][row+1] = IntToStr(row+1); grdAlarmLog->Cells[1][row+1] = (String)tblAlarmLog.fieldValue(7); // Start date grdAlarmLog->Cells[2][row+1] = (String)tblAlarmLog.fieldValue(8); // End date grdAlarmLog->Cells[3][row+1] = (String)tblAlarmLog.fieldValue(9); // Alarm Code grdAlarmLog->Cells[4][row+1] = (String)tblAlarmLog.fieldValue(11); // Alarm Name grdAlarmLog->RowCount = row+2; } } } catch (Exception &e) { } } }
////////////////////////////////////////////////////////////////////////// // [3/23/2011 DHKim] // 성공적으로 그룹테이블에 그룹이 추가되면 GroupKey를 리턴한다. 실패시 -1을 리턴 ////////////////////////////////////////////////////////////////////////// int CDbMeter::GroupAdd(const char* dbfilename, char* szGroupName) { int nGroupKey = -1; try { CppSQLite3DB db; db.open(dbfilename); CppSQLite3Buffer bufSQL; CppSQLite3Table t; bufSQL.format("INSERT INTO groups(GroupName) VALUES(%Q);", szGroupName); db.execDML(bufSQL); XDEBUG("Success: INSERT Command!!\r\n"); bufSQL.clear(); bufSQL.format("SELECT GroupKey FROM groups WHERE GroupName=%Q;", szGroupName); //CppSQLite3Query q = db.execQuery(bufSQL); t = db.getTable(bufSQL); if( t.numRows() == 0 ) { XDEBUG("Failure: SELECT Command!!\r\n"); return nGroupKey; } else { // 동일 이름시 마지막에 추가된 그룹 키를 리턴한다. int nFinalRow = t.numRows() - 1; t.setRow(nFinalRow); if( !t.fieldIsNull(nFinalRow) ) nGroupKey = atoi(t.fieldValue(nFinalRow)); else { XDEBUG("Error: GroupKey is NULL!!\r\n"); return nGroupKey; } XDEBUG("Success: SELECT Command!!\r\n"); } //nGroupKey = q.getIntField("GroupKey", -1); db.close(); } catch( CppSQLite3Exception& e ) { XDEBUG("%s\r\n",e.errorMessage()); return -1; } return nGroupKey; }
void CDlgAnswerList::ShowPage(int nPageIndex) { CString str = _T(""); int i = 0; int nStartIndex = 0; int nOffset = 0; CppSQLite3Table t; CppSQLite3Query q; CppSQLite3DB db; db.open(PATH_SQLITE_DB_808); //打开数据库 char szSqlBuffer[1024]; memset(szSqlBuffer, 0, sizeof(szSqlBuffer)); //查询记录总数量 // sprintf(szSqlBuffer, "SELECT COUNT(*) FROM answer \ // WHERE UID IN (SELECT UID FROM question ORDER BY question_datatime DESC LIMIT %d, 1) \ // ORDER BY answer_ID;", // m_nQuestionIndex); // m_nRecordCount = db.execScalar( szSqlBuffer ); sprintf(szSqlBuffer, "SELECT * FROM answer where UID = '%d';", m_nQuestionIndex); t = db.getTable(szSqlBuffer); m_nRecordCount = t.numRows(); //计算总页数 if(m_nRecordCount > 0) m_nPageCount = (m_nRecordCount-1)/elist_count+1; else m_nPageCount = 1; memset(szSqlBuffer, 0, sizeof(szSqlBuffer)); sprintf(szSqlBuffer, "SELECT * FROM question where UID = '%d';", m_nQuestionIndex); q = db.execQuery(szSqlBuffer); int nFlag = q.getIntField("flag"); const char* ccText = q.fieldValue("question_content"); str = ccText; //在数据库中查询第nPageIndex页的elist_count条数据 // sprintf(szSqlBuffer, "SELECT * FROM answer \ // WHERE UID IN (SELECT UID FROM question ORDER BY question_datatime DESC LIMIT %d, 1) \ // ORDER BY answer_ID LIMIT %d, %d;", // m_nQuestionIndex, nPageIndex*elist_count, elist_count); // q = db.execQuery(szSqlBuffer); memset(szSqlBuffer, 0, sizeof(szSqlBuffer)); sprintf(szSqlBuffer, "SELECT * FROM answer where UID = '%d' LIMIT %d, %d;", m_nQuestionIndex, (nPageIndex)*elist_count, elist_count); q = db.execQuery(szSqlBuffer); for( i = 0; i < elist_count; i++ ) { if ( !q.eof() ) //数据行 { m_nAnswerID[i] = q.getIntField("answer_ID"); m_list[i].chChar = q.fieldValue("answer_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(); //关闭数据库 if(nFlag) TTSSpeaking( str ); return; }
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; } }
void CPhoneLog::ShowPage(int nPageIndex) { CppSQLite3DB db_808; db_808.open(PATH_SQLITE_DB_808); //打开数据库 char szSqlBuffer[512]; memset(szSqlBuffer, 0, sizeof(szSqlBuffer)); CppSQLite3Query q, p; CppSQLite3Table t; switch(enLog) { case PLACEDCALL: { //查询记录总数量 t = db_808.getTable("select * from phone_log where flag = 1;"); m_nRecordCount = t.numRows(); m_nTag = 1; //在数据库中查询第nPageIndex页的SDCount条数据 sprintf(szSqlBuffer, "SELECT * FROM phone_log where UID and flag = %d ORDER BY log_datatime DESC LIMIT %d, %d;", m_nTag, (nPageIndex-1)*5, 5); q = db_808.execQuery(szSqlBuffer); } break; case RECEIVEDCALL: { //查询记录总数量 t = db_808.getTable("select * from phone_log where flag = 2;"); m_nRecordCount = t.numRows(); m_nTag = 2; //在数据库中查询第nPageIndex页的SDCount条数据 sprintf(szSqlBuffer, "SELECT * FROM phone_log where UID and flag = %d ORDER BY log_datatime DESC LIMIT %d, %d;", m_nTag, (nPageIndex-1)*5, 5); q = db_808.execQuery(szSqlBuffer); } break; case MISSEDCALL: { //查询记录总数量 t = db_808.getTable("select * from phone_log where flag = 3;"); m_nRecordCount = t.numRows(); m_nTag = 3; //在数据库中查询第nPageIndex页的SDCount条数据 sprintf(szSqlBuffer, "SELECT * FROM phone_log where UID and flag = %d ORDER BY log_datatime DESC LIMIT %d, %d;", m_nTag, (nPageIndex-1)*5, 5); q = db_808.execQuery(szSqlBuffer); } break; default: break; } //计算总页数 if(m_nRecordCount > 0) m_nTotalPage = (m_nRecordCount-1)/5+1; else m_nTotalPage = 1; for(int k=0; k<5; k++) { m_strTimeLen[k] = _T(""); m_strTime[k] = _T(""); ItemStr[k] = _T(""); m_Item[k].chChar = _T(""); } for( int i=0; i<5; i++ ) { if ( !q.eof() ) //数据行 { const char* number = q.fieldValue("phone_number"); //号码 m_Item[i].chChar = number; ItemStr[i] = m_Item[i].chChar; //m_strPhoneNum[i] = m_ItemRecord[i][1].chChar; sprintf(szSqlBuffer, "SELECT * FROM phone_book where phone_number = '%s' ;", number); p = db_808.execQuery(szSqlBuffer); m_strName[i] = p.fieldValue("name"); //名字 if (m_strName[i] != _T("")) { ItemStr[i] = m_strName[i]; } m_strTime[i] = q.fieldValue("log_datatime"); m_strTimeLen[i] = q.fieldValue("call_duration"); //m_strName[i] = m_ItemRecord[i][0].chChar; m_Item[i].nState = BTN_STATE_NORMAL; q.nextRow(); } else //空白行 { m_strTimeLen[i] = _T(""); m_strTime[i] = _T(""); ItemStr[i] = _T(""); m_Item[i].chChar = _T(""); m_Item[i].nState = BTN_STATE_NORMAL; } } q.finalize(); //释放statement db_808.close(); }
void CUserAcessSetDlg::ReloadUserLevelDB() { m_FlexGrid.Clear(); m_FlexGrid.put_Cols(13); m_FlexGrid.put_TextMatrix(0,1,_T("Grid 2")); m_FlexGrid.put_TextMatrix(0,1,_T("User")); m_FlexGrid.put_TextMatrix(0,2,_T("Serial")); m_FlexGrid.put_TextMatrix(0,3,_T("ID")); m_FlexGrid.put_TextMatrix(0,4,_T("Floor")); m_FlexGrid.put_TextMatrix(0,5,_T("Room")); m_FlexGrid.put_TextMatrix(0,6,_T("Main Screen")); m_FlexGrid.put_TextMatrix(0,7,_T("Parameter")); m_FlexGrid.put_TextMatrix(0,8,_T("Output ")); // m_FlexGrid.put_TextMatrix(0,9,_T("Network Ctr")); m_FlexGrid.put_TextMatrix(0,9,_T("Graphic")); // m_FlexGrid.put_TextMatrix(0,10,_T("Building Set")); m_FlexGrid.put_TextMatrix(0,10,_T("Burn Hex")); m_FlexGrid.put_TextMatrix(0,11,_T("Load Config")); m_FlexGrid.put_TextMatrix(0,12,_T("All Screen")); m_FlexGrid.put_ColWidth(0,700); m_FlexGrid.put_ColWidth(1,700); m_FlexGrid.put_ColWidth(2,700); m_FlexGrid.put_ColWidth(3,1000); m_FlexGrid.put_ColWidth(4,1000); m_FlexGrid.put_ColWidth(5,1000); m_FlexGrid.put_ColWidth(6,1100); m_FlexGrid.put_ColWidth(7,1100); m_FlexGrid.put_ColWidth(8,1100); m_FlexGrid.put_ColWidth(9,1100); m_FlexGrid.put_ColWidth(10,1100); m_FlexGrid.put_ColWidth(11,1100); m_FlexGrid.put_ColWidth(12,1100); for(int i=0;i<13;i++) { m_FlexGrid.put_ColAlignment(i,4); } CppSQLite3Table table; CppSQLite3Query q; CppSQLite3DB SqliteDBBuilding; SqliteDBBuilding.open((UTF8MBSTR)g_strCurBuildingDatabasefilePath); CString strSql; //m_pRsTemp->Open((_variant_t)("select * from user_level where username = '******'"),_variant_t((IDispatch *)m_pConTmp,true),adOpenStatic,adLockOptimistic,adCmdText); // strSql.Format(_T("select * from user_level where MainBuilding_Name='%s' and Building_Name='%s'"),m_strMainBuilding,m_strSubNetName); strSql.Format(_T("select * from user_level where MainBuilding_Name='%s' and Building_Name='%s' and username='******'"),m_strMainBuilding,m_strSubNetName,m_strUserName); q = SqliteDBBuilding.execQuery((UTF8MBSTR)strSql); table = SqliteDBBuilding.getTable((UTF8MBSTR)strSql); USERLEVEL UserLevelV; int temp_row=0; int nTemp; CString strTemp; _variant_t temp_variant; m_FlexGrid.put_Rows(table.numRows()+1); for(int i=1;i<table.numRows()+1;i++) { for(int k=0;k<=12;k++) { if (i%2==0) { m_FlexGrid.put_Row(i);m_FlexGrid.put_Col(k);m_FlexGrid.put_CellBackColor(COLOR_CELL); } } } while(!q.eof()) { ++temp_row; strTemp=q.getValuebyName(L"MainBuilding_Name"); UserLevelV.strMainBuildingName=strTemp; strTemp=q.getValuebyName(L"Building_Name"); UserLevelV.strSubBuildingName=strTemp; strTemp=q.getValuebyName(L"username"); UserLevelV.strUserName=strTemp; nTemp=q.getIntField("serial_number"); UserLevelV.nSerialNumber=nTemp; nTemp=q.getIntField("product_id"); UserLevelV.ProductID=nTemp; strTemp=q.getValuebyName(L"floorname"); UserLevelV.strFloorName=strTemp; strTemp=q.getValuebyName(L"roomname"); UserLevelV.strRoomName=strTemp; nTemp=q.getIntField("mainscreen_level"); UserLevelV.MainLevel=nTemp; nTemp=q.getIntField("parameter_level"); UserLevelV.ParamerLevel=nTemp; nTemp=q.getIntField("outputtable_level"); UserLevelV.Outputtable_level=nTemp; nTemp=q.getIntField("graphic_level"); UserLevelV.Graphic_level=nTemp; nTemp=q.getIntField("burnhex_level"); UserLevelV.Burnhex_level=nTemp; nTemp=q.getIntField("loadconfig_level"); UserLevelV.Loadconfig_level=nTemp; nTemp=q.getIntField("allscreen_level"); UserLevelV.Allscreen_level=nTemp; if(UserLevelV.Allscreen_level==1) { for(int k=0;k<12;k++) { m_FlexGrid.put_Row(temp_row);m_FlexGrid.put_Col(k);m_FlexGrid.put_CellBackColor(RGB(215,215,215)); } } else { for(int k=0;k<=12;k++) { if (temp_row%2==0) { m_FlexGrid.put_Row(temp_row);m_FlexGrid.put_Col(k);m_FlexGrid.put_CellBackColor(COLOR_CELL); } else { m_FlexGrid.put_Row(temp_row);m_FlexGrid.put_Col(k);m_FlexGrid.put_CellBackColor(RGB(255,255,240)); } } } q.nextRow(); m_FlexGrid.put_TextMatrix(temp_row,1,UserLevelV.strUserName); CString strTemp;; strTemp.Format(_T("%d"),UserLevelV.nSerialNumber); m_FlexGrid.put_TextMatrix(temp_row,2,strTemp); strTemp.Format(_T("%d"),UserLevelV.ProductID); m_FlexGrid.put_TextMatrix(temp_row,3,strTemp); m_FlexGrid.put_TextMatrix(temp_row,4,UserLevelV.strFloorName); m_FlexGrid.put_TextMatrix(temp_row,5,UserLevelV.strRoomName); switch (UserLevelV.MainLevel) { case 0:strTemp=_T("Read & write");break; case 1:strTemp=_T("Read only");break; default:strTemp=_T("Read & write"); } m_FlexGrid.put_TextMatrix(temp_row,6,strTemp); switch (UserLevelV.ParamerLevel) { case 0:strTemp=_T("Read & write");break; case 1:strTemp=_T("Read only");break; default:strTemp=_T("Read & write"); } m_FlexGrid.put_TextMatrix(temp_row,7,strTemp); switch (UserLevelV.Outputtable_level) { case 0:strTemp=_T("Read & write");break; case 1:strTemp=_T("Read only");break; default:strTemp=_T("Read & write"); } m_FlexGrid.put_TextMatrix(temp_row,8,strTemp); switch (UserLevelV.Graphic_level) { case 0:strTemp=_T("Read & write");break; case 1:strTemp=_T("Read only");break; default:strTemp=_T("Read & write"); } m_FlexGrid.put_TextMatrix(temp_row,9,strTemp); switch (UserLevelV.Burnhex_level) { case 0:strTemp=_T("Enable");break; case 1:strTemp=_T("Unenable");break; default:strTemp=_T("Enable"); } m_FlexGrid.put_TextMatrix(temp_row,10,strTemp); switch (UserLevelV.Loadconfig_level) { case 0:strTemp=_T("Enable");break; case 1:strTemp=_T("Unenable");break; default:strTemp=_T("Enable"); } m_FlexGrid.put_TextMatrix(temp_row,11,strTemp); switch (UserLevelV.Allscreen_level) { case 0:strTemp=_T("Enable");break; case 1:strTemp=_T("Unenable");break; default:strTemp=_T("Enable"); } m_FlexGrid.put_TextMatrix(temp_row,12,strTemp); } SqliteDBBuilding.closedb(); }
int CDbMeter::GroupCommand(const char* dbfilename, int nGroupKey, vector<char*> vbufMember, MIBValue* pValue, int cnt) { try { CppSQLite3DB db; db.open(dbfilename); CppSQLite3Buffer bufSQL; CppSQLite3Query q; CppSQLite3Table t; FILE *fp; char szCmd[12] = {0, }; char szFileName[20] = {0, }; bufSQL.format("SELECT groups.GroupKey, groups.GroupName, groupmember.EUI64ID FROM groups LEFT OUTER JOIN groupmember ON groups.GroupKey=groupmember.GroupKey WHERE groupmember.GroupKey=%d;", nGroupKey); t = db.getTable(bufSQL); for ( int row = 0; row < t.numRows(); row++) { t.setRow(row); if( !t.fieldIsNull(1)) { VARAPI_OidToString(&pValue[1].stream.id, szCmd); bufMember.push_back((char*)t.fieldValue(2)); XDEBUG("%d | %s | %s\r\n", atoi(t.fieldValue(0)), t.fieldValue(1), t.fieldValue(2) ); bufSQL.clear(); bufSQL.format("SELECT datetime('now', 'localtime');"); q = db.execQuery(bufSQL); sprintf(szFileName,"/app/sqlite/%s.txt", t.fieldValue(2)); fp = fopen(szFileName,"a+t"); for (int paramcnt=2; paramcnt<cnt; paramcnt++) { if( VARAPI_OidCompare( &(pValue[paramcnt].oid), "1.11") == 0 ) { fprintf(fp, "%s", pValue[paramcnt].stream.p); } else if( VARAPI_OidCompare( &(pValue[paramcnt].oid), "1.5") == 0 || VARAPI_OidCompare( &(pValue[paramcnt].oid), "1.8") == 0 ) { fprintf(fp, "%d", pValue[paramcnt].stream.u16); } else if( VARAPI_OidCompare( &(pValue[paramcnt].oid), "1.6") == 0 || VARAPI_OidCompare( &(pValue[paramcnt].oid), "1.9") == 0) { fprintf(fp, "%d", pValue[paramcnt].stream.u32); } else { fprintf(fp, "%d", pValue[paramcnt].stream.u8); } if(paramcnt < cnt - 1) fprintf(fp, "|"); } fprintf(fp,"\n"); fclose(fp); XDEBUG("Success: File write!!\r\n"); bufSQL.clear(); XDEBUG("CMD: %s\r\n", szCmd); bufSQL.format("INSERT INTO command(GroupKey, EUI64ID, GroupName, CommandState, Command, CommandCreateTime, ResultErrorCode, ParamPathInfo) VALUES(%d, %Q, %Q, %d, %s, %Q, %d, %Q);", nGroupKey, t.fieldValue(2), t.fieldValue(1), 1, szCmd, q.fieldValue(0), 0, "CmdParamPathInfo.txt" ); db.execDML(bufSQL); } } db.close(); } catch( CppSQLite3Exception& e ) { XDEBUG("%s\r\n",e.errorMessage()); return e.errorCode(); } return SQLITE_OK; }
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; }
int main(int argc, char** argv) { try { int row; CppSQLite3DB db; cout << "SQLite Header Version: " << CppSQLite3DB::SQLiteHeaderVersion() << endl; cout << "SQLite Library Version: " << CppSQLite3DB::SQLiteLibraryVersion() << endl; cout << "SQLite Library Version Number: " << CppSQLite3DB::SQLiteLibraryVersionNumber() << endl; remove(gszFile); db.open(gszFile); //////////////////////////////////////////////////////////////////////////////// // Demonstrate getStringField(), getIntField(), getFloatField() //////////////////////////////////////////////////////////////////////////////// db.execDML("create table parts(no int, name char(20), qty int, cost number);"); db.execDML("insert into parts values(1, 'part1', 100, 1.11);"); db.execDML("insert into parts values(2, null, 200, 2.22);"); db.execDML("insert into parts values(3, 'part3', null, 3.33);"); db.execDML("insert into parts values(4, 'part4', 400, null);"); cout << endl << "CppSQLite3Query getStringField(), getIntField(), getFloatField() tests" << endl; CppSQLite3Query q = db.execQuery("select * from parts;"); while (!q.eof()) { cout << q.getIntField(0) << "|"; cout << q.getStringField(1) << "|"; cout << q.getInt64Field(2) << "|"; cout << q.getFloatField(3) << "|" << endl; q.nextRow(); } cout << endl << "specify NULL values tests" << endl; q = db.execQuery("select * from parts;"); while (!q.eof()) { cout << q.getIntField(0) << "|"; cout << q.getStringField(1, "NULL") << "|"; cout << q.getIntField(2, -1) << "|"; cout << q.getFloatField(3, -3.33) << "|" << endl; q.nextRow(); } cout << endl << "Specify fields by name" << endl; q = db.execQuery("select * from parts;"); while (!q.eof()) { cout << q.getIntField("no") << "|"; cout << q.getStringField("name") << "|"; cout << q.getInt64Field("qty") << "|"; cout << q.getFloatField("cost") << "|" << endl; q.nextRow(); } cout << endl << "specify NULL values tests" << endl; q = db.execQuery("select * from parts;"); while (!q.eof()) { cout << q.getIntField("no") << "|"; cout << q.getStringField("name", "NULL") << "|"; cout << q.getIntField("qty", -1) << "|"; cout << q.getFloatField("cost", -3.33) << "|" << endl; q.nextRow(); } q.finalize(); //////////////////////////////////////////////////////////////////////////////// // Demonstrate getStringField(), getIntField(), getFloatField() // But this time on CppSQLite3Table //////////////////////////////////////////////////////////////////////////////// cout << endl << "CppSQLite3Table getStringField(), getIntField(), getFloatField() tests" << endl; CppSQLite3Table t = db.getTable("select * from parts;"); for (row = 0; row < t.numRows(); row++) { t.setRow(row); cout << t.getIntField(0) << "|"; cout << t.getStringField(1) << "|"; cout << t.getIntField(2) << "|"; cout << t.getFloatField(3) << "|" << endl; } cout << endl << "specify NULL values tests" << endl; for (row = 0; row < t.numRows(); row++) { t.setRow(row); cout << t.getIntField(0, -1) << "|"; cout << t.getStringField(1, "NULL") << "|"; cout << t.getIntField(2, -1) << "|"; cout << t.getFloatField(3, -3.33) << "|" << endl; } 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++) { switch (q.fieldDataType(i)) { case SQLITE_INTEGER : cout << "SQLITE_INTEGER|"; break; case SQLITE_FLOAT : cout << "SQLITE_FLOAT |"; break; case SQLITE_TEXT : cout << "SQLITE_TEXT |"; break; case SQLITE_BLOB : cout << "SQLITE_BLOB |"; break; case SQLITE_NULL : cout << "SQLITE_NULL |"; break; default: cout << "***UNKNOWN TYPE***"; break; } } q.nextRow(); cout << endl; } nRows = db.execDML("delete from types where no = 1 or no = 1.1 or no = 'a' or no is null;"); cout << endl << nRows << " rows deleted, leaving binary row only" << endl; q = db.execQuery("select * from types;"); const unsigned char* pBlob; int nLen; pBlob = q.getBlobField(0, nLen); cout << "Field 1 BLOB length: " << nLen << endl; pBlob = q.getBlobField(1, nLen); cout << "Field 2 BLOB length: " << nLen << endl; pBlob = q.getBlobField(2, nLen); cout << "Field 3 BLOB length: " << nLen << endl; pBlob = q.getBlobField(3, nLen); cout << "Field 4 BLOB length: " << nLen << endl; q.finalize(); nRows = db.execDML("delete from types;"); cout << endl << nRows << " rows deleted, leaving empty table" << endl; unsigned char bin[256]; for (int i = 0; i < sizeof bin; i++) { bin[i] = i; } stmt = db.compileStatement("insert into types values(?,0,0,0);"); stmt.bind(1, bin, sizeof bin); stmt.execDML(); cout << "Stored binary Length: " << sizeof bin << endl; q = db.execQuery("select * from types;"); pBlob = q.getBlobField(0, nLen); cout << "Field 1 BLOB length: " << nLen << endl; for (i = 0; i < sizeof bin; i++) { if (pBlob[i] != i) { cout << "Problem: i: ," << i << " BLOB[i]: " << pBlob[i] << endl; } } } catch (CppSQLite3Exception& e) { cerr << e.errorCode() << ":" << e.errorMessage() << endl; } //////////////////////////////////////////////////////////////////////////////// // Loop until user enters q or Q //////////////////////////////////////////////////////////////////////////////// char c(' '); while (c != 'q' && c != 'Q') { cout << "Press q then enter to quit: "; cin >> c; } return 0; }