void CAcqHive::RebuildTable(const std::string& tab_name, std::vector<std::string>& vec_field, const std::string& tab_location) throw(base::Exception) { if ( tab_name.empty() ) { throw base::Exception(ACQERR_REBUILD_TABLE, "[HIVE] Can not rebuild! 库表名为空! [FILE:%s, LINE:%d]", __FILE__, __LINE__); } if ( vec_field.empty() ) { throw base::Exception(ACQERR_REBUILD_TABLE, "[HIVE] 没有库表 [%s] 的字段信息! [FILE:%s, LINE:%d]", tab_name.c_str(), __FILE__, __LINE__); } std::string sql_drop = "DROP TABLE IF EXISTS " + tab_name; std::string sql_create = "CREATE TABLE " + tab_name + " ( "; const size_t VEC_SIZE = vec_field.size(); for ( size_t i = 0; i < VEC_SIZE; ++i ) { std::string& str_field = vec_field[i]; if ( str_field.empty() ) { throw base::Exception(ACQERR_REBUILD_TABLE, "[HIVE] 缺少库表 [%s] 的第 [%lu] 个字段的信息! [FILE:%s, LINE:%d]", tab_name.c_str(), (i+1), __FILE__, __LINE__); } if ( i != 0 ) { sql_create += ", " + str_field + " string"; } else { sql_create += str_field + " string"; } } sql_create += " ) row format delimited fields terminated by '|' stored as textfile"; // 测试环境:不指定建表的 location if ( !tab_location.empty() ) { // 指定建表的 location sql_create += " location '" + tab_location + tab_name + "'"; } try { m_pLog->Output("[HIVE] Try to drop table: %s", tab_name.c_str()); ExecuteSQL(sql_drop); m_pLog->Output("[HIVE] Drop table OK."); m_pLog->Output("[HIVE] Create table: %s", tab_name.c_str()); ExecuteSQL(sql_create); m_pLog->Output("[HIVE] Create table ---- done!"); } catch ( const base::Exception& ex ) { throw base::Exception(ACQERR_REBUILD_TABLE, "[HIVE] Rebuild table \"%s\" failed! %s [FILE:%s, LINE:%d]", tab_name.c_str(), ex.What().c_str(), __FILE__, __LINE__); } }
/*! \brief Load feature properties from DB \param poFeature pointer to OGR feature \return OGRERR_NONE on success or OGRERR_FAILURE on failure */ OGRErr VFKFeatureSQLite::LoadProperties(OGRFeature *poFeature) { CPLString osSQL; osSQL.Printf("SELECT * FROM %s WHERE rowid = %d", m_poDataBlock->GetName(), m_iRowId); if (ExecuteSQL(osSQL.c_str()) != OGRERR_NONE) return OGRERR_FAILURE; for (int iField = 0; iField < m_poDataBlock->GetPropertyCount(); iField++) { if (sqlite3_column_type(m_hStmt, iField) == SQLITE_NULL) /* skip null values */ continue; OGRFieldType fType = poFeature->GetDefnRef()->GetFieldDefn(iField)->GetType(); if (fType == OFTInteger) poFeature->SetField(iField, sqlite3_column_int(m_hStmt, iField)); else if (fType == OFTReal) poFeature->SetField(iField, sqlite3_column_double(m_hStmt, iField)); else poFeature->SetField(iField, (const char *) sqlite3_column_text(m_hStmt, iField)); } FinalizeSQL(); return OGRERR_NONE; }
void svStorageEnginePostgreSQL::GetHostKey( const string &dev, const string &org, svHostKey &key) { string sql(sql_query_hostkey); string value(dev); EscapeString(value); if (PrepareSQL(sql, "%d", value) == 0) throw svExStorageQuery("Invalid SQL, missing match token: device"); value = org; EscapeString(value); PrepareSQL(sql, "%o", value); ExecuteSQL(sql); if (PQresultStatus(pg_res) != PGRES_TUPLES_OK) { throw svExStorageQuery(string("PQexec: ") + PQresultErrorMessage(pg_res)); } if (PQntuples(pg_res) == 0) throw svExStorageRecordNotFound(name + ": Device not found: " + dev); key.SetAge(time(NULL)); key.SetKey(PQgetvalue(pg_res, 0, 0)); PQclear(pg_res); pg_res = NULL; }
void svStorageEngineMySQL::GetHostKey( const string &dev, const string &org, svHostKey &key) { string sql(sql_query_hostkey); string value(dev); EscapeString(value); if (PrepareSQL(sql, "%d", value) == 0) throw svExStorageQuery("Invalid SQL, missing match token: device"); value = org; EscapeString(value); PrepareSQL(sql, "%o", value); ExecuteSQL(sql); if (mysql_num_rows(mysql_res) == 0) throw svExStorageRecordNotFound(name + ": Device not found: " + dev); MYSQL_ROW row; if (!(row = mysql_fetch_row(mysql_res))) { throw svExStorageQuery(name + string(": mysql_fetch_row: ") + mysql_error(mysql_conn)); } key.SetAge(time(NULL)); key.SetKey(row[0]); mysql_free_result(mysql_res); mysql_res = NULL; }
void svStorageEngineMySQL::InsertPoolClient(const string &node, const string &pool_name, const string &dev, const string &org, svPoolClientState state) { if (sql_insert_pool_client.size() == 0) throw svExStorageQuery("No SQL query set: insert-pool-client"); string sql(sql_insert_pool_client); string value(dev); EscapeString(value); if (PrepareSQL(sql, "%d", value) == 0) throw svExStorageQuery("Invalid SQL, missing match token: device"); value = node; EscapeString(value); PrepareSQL(sql, "%n", value); value = pool_name; EscapeString(value); PrepareSQL(sql, "%p", value); value = org; EscapeString(value); PrepareSQL(sql, "%o", value); ostringstream os; map<svPoolClientState, string>::iterator i; i = pool_client_state.find(state); if (i == pool_client_state.end()) os << state; else os << i->second; PrepareSQL(sql, "%s", os.str()); ExecuteSQL(sql); }
/*! \brief Load data block definitions (&B) Call VFKReader::OpenFile() before this function. \return number of data blocks or -1 on error */ int VFKReaderSQLite::ReadDataBlocks() { int nDataBlocks = -1; CPLString osSQL; const char *pszName, *pszDefn; IVFKDataBlock *poNewDataBlock; sqlite3_stmt *hStmt; osSQL.Printf("SELECT table_name, table_defn FROM %s", VFK_DB_TABLE); hStmt = PrepareStatement(osSQL.c_str()); while(ExecuteSQL(hStmt) == OGRERR_NONE) { pszName = (const char*) sqlite3_column_text(hStmt, 0); pszDefn = (const char*) sqlite3_column_text(hStmt, 1); poNewDataBlock = (IVFKDataBlock *) CreateDataBlock(pszName); poNewDataBlock->SetGeometryType(); poNewDataBlock->SetProperties(pszDefn); VFKReader::AddDataBlock(poNewDataBlock, NULL); } if (m_nDataBlockCount == 0) { CPL_IGNORE_RET_VAL(sqlite3_exec(m_poDB, "BEGIN", NULL, NULL, NULL)); /* CREATE TABLE ... */ nDataBlocks = VFKReader::ReadDataBlocks(); CPL_IGNORE_RET_VAL(sqlite3_exec(m_poDB, "COMMIT", NULL, NULL, NULL)); StoreInfo2DB(); } return nDataBlocks; }
void svStorageEngineMySQL::UpdatePoolClient(const string &node, const string &pool_name, const string &dev, const string &org, svPoolClientState state) { if (sql_update_pool_client.size() == 0) throw svExStorageQuery("No SQL query set: update-pool-client"); string sql(sql_update_pool_client); string value(dev); EscapeString(value); if (PrepareSQL(sql, "%d", value) == 0) throw svExStorageQuery("Invalid SQL, missing match token: device"); value = node; EscapeString(value); PrepareSQL(sql, "%n", value); value = pool_name; EscapeString(value); PrepareSQL(sql, "%p", value); value = org; EscapeString(value); PrepareSQL(sql, "%o", value); ostringstream os; map<svPoolClientState, string>::iterator i; i = pool_client_state.find(state); if (i == pool_client_state.end()) os << state; else os << i->second; PrepareSQL(sql, "%s", os.str()); ExecuteSQL(sql); my_ulonglong affected_rows = mysql_affected_rows(mysql_conn); if (affected_rows == 0) throw svExStorageRecordNotFound("Pool client not found"); }
/*! \brief Create DB table from VFKDataBlock (SQLITE only) \param poDataBlock pointer to VFKDataBlock instance */ void VFKReaderSQLite::AddDataBlock(IVFKDataBlock *poDataBlock, const char *pszDefn) { CPLString osCommand, osColumn; VFKPropertyDefn *poPropertyDefn; sqlite3_stmt *hStmt; /* register table in 'vfk_blocks' */ osCommand.Printf("SELECT COUNT(*) FROM vfk_blocks WHERE " "table_name = '%s'", poDataBlock->GetName()); hStmt = PrepareStatement(osCommand.c_str()); if (ExecuteSQL(hStmt) == OGRERR_NONE && sqlite3_column_int(hStmt, 0) == 0) { osCommand.Printf("CREATE TABLE '%s' (", poDataBlock->GetName()); for (int i = 0; i < poDataBlock->GetPropertyCount(); i++) { poPropertyDefn = poDataBlock->GetProperty(i); if (i > 0) osCommand += ","; osColumn.Printf("%s %s", poPropertyDefn->GetName(), poPropertyDefn->GetTypeSQL().c_str()); osCommand += osColumn; } osColumn.Printf(",%s integer", FID_COLUMN); osCommand += osColumn; if (poDataBlock->GetGeometryType() != wkbNone) { osColumn.Printf(",%s blob", GEOM_COLUMN); osCommand += osColumn; } osCommand += ")"; ExecuteSQL(osCommand.c_str()); /* CREATE TABLE */ osCommand.Printf("INSERT INTO 'vfk_blocks' (file_name, table_name, " "num_records, num_geometries, table_defn) VALUES " "('%s', '%s', -1, 0, '%s')", m_pszFilename, poDataBlock->GetName(), pszDefn); ExecuteSQL(osCommand.c_str()); sqlite3_finalize(hStmt); } return VFKReader::AddDataBlock(poDataBlock, NULL); }
/*! \brief Create index If creating unique index fails, then non-unique index is created instead. \param name index name \param table table name \param column column(s) name \param unique TRUE to create unique index */ void VFKReaderSQLite::CreateIndex(const char *name, const char *table, const char *column, bool unique) { CPLString osSQL; if (unique) { osSQL.Printf("CREATE UNIQUE INDEX %s ON %s (%s)", name, table, column); if (ExecuteSQL(osSQL.c_str()) == OGRERR_NONE) { return; } } osSQL.Printf("CREATE INDEX %s ON %s (%s)", name, table, column); ExecuteSQL(osSQL.c_str()); }
/*! \brief VFKReaderSQLite constructor */ VFKReaderSQLite::VFKReaderSQLite(const char *pszFilename) : VFKReader(pszFilename) { const char *pszDbNameConf; CPLString pszDbName; CPLString osCommand; VSIStatBufL sStatBuf; bool bNewDb; /* open tmp SQLite DB (re-use DB file if already exists) */ pszDbNameConf = CPLGetConfigOption("OGR_VFK_DB_NAME", NULL); if (pszDbNameConf) { pszDbName = pszDbNameConf; } else { pszDbName.Printf("%s.db", m_pszFilename); } if (CSLTestBoolean(CPLGetConfigOption("OGR_VFK_DB_SPATIAL", "YES"))) m_bSpatial = TRUE; /* build geometry from DB */ else m_bSpatial = FALSE; /* store also geometry in DB */ bNewDb = TRUE; if (VSIStatL(pszDbName, &sStatBuf ) == 0) { if (CSLTestBoolean(CPLGetConfigOption("OGR_VFK_DB_OVERWRITE", "NO"))) { bNewDb = TRUE; /* overwrite existing DB */ VSIUnlink(pszDbName); } else { bNewDb = FALSE; /* re-use exising DB */ } } else { CPLError(CE_Warning, CPLE_AppDefined, "SQLite DB not found. Reading VFK data may take some time..."); } CPLDebug("OGR-VFK", "New DB: %s Spatial: %s", bNewDb ? "yes" : "no", m_bSpatial ? "yes" : "no"); if (SQLITE_OK != sqlite3_open(pszDbName, &m_poDB)) { CPLError(CE_Failure, CPLE_AppDefined, "Creating SQLite DB failed"); } else { char* pszErrMsg = NULL; sqlite3_exec(m_poDB, "PRAGMA synchronous = OFF", NULL, NULL, &pszErrMsg); sqlite3_free(pszErrMsg); } if (bNewDb) { /* new DB, create support metadata tables */ osCommand = "CREATE TABLE 'vfk_blocks' " "(file_name text, table_name text, num_records integer, " "num_geometries integer, table_defn text)"; ExecuteSQL(osCommand.c_str()); } }
BOOL Sqlite3DB::ExecuteSQL( LPCWSTR unicodeSql, int* pAffectedRows /*= NULL*/ ) { std::string utf8Sql; if (! Unicode2Utf8(unicodeSql, utf8Sql)) { return FALSE; } return ExecuteSQL(utf8Sql.c_str(), pAffectedRows); }
bool Database::RollbackTransaction() { if (!IsOpen()) { m_iLastError=SQLITE_ERROR; return false; } m_iLastError = ExecuteSQL(_T("ROLLBACK TRANSACTION")); return m_iLastError==SQLITE_OK; }
bool Database::CommitTransaction() { if (!IsOpen()) { m_iLastError=SQLITE_ERROR; return false; } m_iLastError = ExecuteSQL(_T("COMMIT TRANSACTION")); return m_iLastError==SQLITE_OK; }
bool Database::BeginTransaction() { if (!IsOpen()) { m_iLastError=SQLITE_ERROR; return false; } m_iLastError = ExecuteSQL(_T("BEGIN TRANSACTION")); return m_iLastError==SQLITE_OK; }
//*======================================================================================== //*函数: bool CSmartJZSRCTable::SvaeToDB(CString strSQL) //*功能: 保存到数据库 //*参数: 略 //*返回: 成功与否 //*说明: 虚基类程序 //*======================================================================================== bool CSmartJZSRCTable::SaveToDB(CString strSQL1, CString strSQL2) { long nDatabase = GetDBHandle(DATABASE_NORMAL); if( nDatabase <= 0 ) { ReportLog(RET_DBBUSY, "保存扎帐数据", "不能得到数据库句柄!"); return false; } // printf("%s\n", strSQL1.GetBuffer(0)); long nRec = ExecuteSQL(nDatabase, strSQL1.GetBuffer(0)); if( nRec != RET_OK ) { printf("%s\n", strSQL1.GetBuffer(0)); CString strText; strText = "create table " + m_strTableName ; strText += "(smt_rowid integer not null primary key,smt_authno number(10,0) not null,"; strText += "smt_data varchar(100) not null,smt_CRC number(2) , smt_datetime date ,"; strText += "smt_dealwith number(2),smt_dealwithdatetime date,smt_adjustcode varchar(4)) "; ExecuteSQL(nDatabase, strText.GetBuffer(0)); strText = "create sequence " + m_strTableName + "_rowid start with 1 increment by 1"; ExecuteSQL(nDatabase, strText.GetBuffer(0)); if( ExecuteSQL(nDatabase, strSQL1.GetBuffer(0)) != RET_OK ) { FreeDBHandle(nDatabase); return false; } } // printf("%s\n", strSQL2.GetBuffer(0)); nRec = ExecuteSQL(nDatabase, strSQL2.GetBuffer(0)); if( nRec != RET_OK ) { printf("%s\n", strSQL2.GetBuffer(0)); FreeDBHandle(nDatabase); return false; } if( m_bUpdate ) { nRec = ExecuteSQL(nDatabase, strUpdate.GetBuffer(0)); if( nRec != RET_OK ) { printf("%s\n", strUpdate.GetBuffer(0)); FreeDBHandle(nDatabase); return false; } } FreeDBHandle(nDatabase); return true; }
/*! \brief Read FID from DB */ OGRErr VFKFeatureSQLite::SetFIDFromDB() { CPLString osSQL; osSQL.Printf("SELECT %s FROM %s WHERE rowid = %d", FID_COLUMN, m_poDataBlock->GetName(), m_iRowId); if (ExecuteSQL(osSQL.c_str()) != OGRERR_NONE) return OGRERR_FAILURE; m_nFID = sqlite3_column_int(m_hStmt, 0); FinalizeSQL(); return OGRERR_NONE; }
void CUpdateDBThread::DoUpdateDB() { CString strSQL; //如果表不存在则创建 // 2011/07/15-8201-gxx: 本地消费表,保存本地用户结账后的信息 if (!TableExists(_T("LocalConsume"))) { IBA_LOG0(_T("create table LocalConsume")); strSQL.Empty(); strSQL = _T("CREATE TABLE `LocalConsume` (\ `submitTime` datetime NOT NULL,\ `netBarId` smallint(6) NOT NULL default '0',\ `refNo` int(11) NOT NULL default '0',\ `serialNo` int(11) default NULL,\ `serialNum` varchar(20) default NULL,\ `name` varchar(20) default NULL,\ `idNumber` varchar(50) default NULL,\ `memberId` int(11) default NULL,\ `creditAmount` int(11) default NULL,\ `consumeAmount` int(11) default NULL,\ `returnAmount` int(11) default NULL,\ `checkinTime` datetime default NULL,\ `checkoutTime` datetime default NULL,"); strSQL += _T("\ `termId` varchar(8) default NULL,\ `ClassId` tinyint(4) default '0',\ `PayType` tinyint(4) default '0',\ `PCClass` tinyint(4) default '0',\ `timeConsume` int(11) default NULL,\ `operator` varchar(30) default NULL,\ `dataStatus` tinyint(4) default NULL,\ `checkData` varchar(32) default NULL,\ PRIMARY KEY (`submitTime`,`netBarId`,`refNo`),\ KEY `lc_serialnum` (`serialNum`),\ KEY `lc_memberId` (`memberId`),\ KEY `lc_termId` (`termId`),\ KEY `lc_name` (`name`),\ KEY `lc_idnumber` (`idNumber`)\ ) ENGINE=InnoDB DEFAULT CHARSET=gbk"); ExecuteSQL(strSQL); }
//////////////////////////////////////////////////////////////////////////////// //with NON_TRANSACTIONAL, it makes durable all of the modifications to active //incarnations whose PIDs are contained in the pids parameter, regardless of the //transactional context of the calling thread // //with TRANSACTIONAL, it behaves as follows: //$$ if the invoking thread is accociated with a transcation context, it makes // durable all state modifications made in the current transactional scope for // incarnations whose PIDs are contained in the pids parameter, flushing them // to the underying datastore. //$$ if the invoking thread is not associated with a transactional context, the // standard exception TRANSACTION_REQUIRED is raised. //if the session pool implementation is unable to reconcile the changes and make //them durable, then the PERSISTENT_STORE standard exception is raised //////////////////////////////////////////////////////////////////////////////// void SessionPoolImpl::flush_by_pids(const PidList& pids) { DEBUG_OUT("CatalogBaseImpl::flush_by_pids() is called"); if( !CanTransact() ) NORMAL_OUT( "CatalogBaseImpl::flush_by_pids() - Database do not support transaction, flush is errorprone!" ); if( access_mode==READ_ONLY ) { NORMAL_ERR( "CatalogBaseImpl::flush_by_pids() - Session pool is read-only!" ); return; } std::string strFlush = ""; std::vector<Pid> vPidList; //there is no function for deleting Pid from PidList, so the PidList should //be first converted in a list of Pid for(CORBA::ULong i=0; i<pids.length(); i++) vPidList.push_back(pids[i]); for( homeBaseIter_=lHomeBases_.begin(); homeBaseIter_!=lHomeBases_.end(); homeBaseIter_++ ) strFlush += (*homeBaseIter_)->getFlushByPid(vPidList); if(ExecuteSQL(strFlush.c_str())) { SQLRETURN ret; ret = SQLEndTran(SQL_HANDLE_DBC, hDbc_, SQL_COMMIT); if(ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO) { // the flush is successfull, we set the modified-value of each // storage object back to FALSE for( homeBaseIter_=lHomeBases_.begin(); homeBaseIter_!=lHomeBases_.end(); homeBaseIter_++ ) (*homeBaseIter_)->setBatchUnModified(); } else { NORMAL_ERR( "CatalogBaseImpl::flush_by_pids() - Transaction is not successful!" ); } } else { NORMAL_ERR( "CatalogBaseImpl::flush_by_pids() - flush_by_pids is not executed!" ); } }
void svStorageEngineMySQL::PurgePoolClients( const string &node, const string &org) { if (sql_purge_pool_clients.size() == 0) throw svExStorageQuery("No SQL query set: purge-pool-clients"); string sql(sql_purge_pool_clients); string value(node); EscapeString(value); if (PrepareSQL(sql, "%n", value) == 0) throw svExStorageQuery("Invalid SQL, missing match token: node"); value = org; EscapeString(value); PrepareSQL(sql, "%o", value); ExecuteSQL(sql); }
/*! \brief Store header info to VFK_DB_HEADER */ void VFKReaderSQLite::StoreInfo2DB() { CPLString osSQL; const char *value; char q; for(std::map<CPLString, CPLString>::iterator i = poInfo.begin(); i != poInfo.end(); ++i) { value = i->second.c_str(); q = (value[0] == '"') ? ' ' : '"'; osSQL.Printf("INSERT INTO %s VALUES(\"%s\", %c%s%c)", VFK_DB_HEADER, i->first.c_str(), q, value, q); ExecuteSQL(osSQL); } }
bool SensorDB::AddData(SensorDataSet dataset) { string channel_num=""; string datatype_id=""; string operator_id=""; string device_id=""; string position_id=""; string activity_id=""; string activitybeginframe_id=""; string activityendframe_id=""; string samplerate=""; string createtime=""; for (int i = 0; i < dataset.Count(); i++) { string cmd = "INSERT INTO [dbo].[SensorDataTable](DataTypeID,ActivityID,DeviceID,OperatorID,PositionID,SampleRate,CreateTime,TotalChannelNum,ActivityBeginFrameID,ActivityEndFrameID"; for (int j = 1; j <= dataset.GetSensorData(i).GetTotalChannelNum(); j++) { cmd += (",channel_"+int2string(j)); } cmd+=") VALUES ("; channel_num = ",'"+int2string(dataset.GetSensorData(i).GetTotalChannelNum())+"'"; datatype_id = "'"+int2string(dataset.GetSensorData(i).GetDataTypeID())+"'"; operator_id = ",'"+int2string(dataset.GetSensorData(i).GetOperatorID())+"'"; device_id = ",'"+int2string(dataset.GetSensorData(i).GetDeviceID())+"'"; position_id = ",'"+int2string(dataset.GetSensorData(i).GetPositionID())+"'"; activity_id = ",'"+int2string(dataset.GetSensorData(i).GetActivityID())+"'"; activitybeginframe_id = ",'"+int2string(dataset.GetSensorData(i).GetActivityBeginFrameID())+"'"; activityendframe_id = ",'"+int2string(dataset.GetSensorData(i).GetActivityEndFrameID())+"'"; samplerate= ",'"+double2string(dataset.GetSensorData(i).GetSampleRate())+"'"; createtime= ",'"+dataset.GetSensorData(i).GetCreateTime().toString("yyyy-MM-dd hh:mm:ss").toStdString()+"'"; cmd+=(datatype_id+activity_id+device_id+operator_id+position_id+samplerate+createtime+channel_num+activitybeginframe_id+activityendframe_id); for (int j = 1; j <= dataset.GetSensorData(i).GetTotalChannelNum(); j++) { cmd+=(",'"+dataset.GetSensorData(i).GetChannel(j-1).ToString()+"'"); } cmd+=");"; if(!ExecuteSQL(cmd)){ return false; } } return true; }
//////////////////////////////////////////////////////////////////////////////// //write to disk any cached modifications of storage object incarnations managed //by this catalog. //////////////////////////////////////////////////////////////////////////////// void CatalogBaseImpl::flush() { DEBUG_OUT("CatalogBaseImpl::flush() is called"); if( !CanTransact() ) NORMAL_OUT( "CatalogBaseImpl::flush() - Database do not support transaction, flush is errorprone!" ); if( access_mode==READ_ONLY ) { NORMAL_ERR( "CatalogBaseImpl::flush() - Session is read-only!" ); return; } std::string strFlush = ""; for( homeBaseIter_=lHomeBases_.begin(); homeBaseIter_!=lHomeBases_.end(); homeBaseIter_++ ) strFlush += (*homeBaseIter_)->getFlush(); if(ExecuteSQL(strFlush.c_str())) { SQLRETURN ret; ret = SQLEndTran(SQL_HANDLE_DBC, hDbc_, SQL_COMMIT); if(ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO) { // the flush is successfull, we set the modified-value of each // storage object back to FALSE for( homeBaseIter_=lHomeBases_.begin(); homeBaseIter_!=lHomeBases_.end(); homeBaseIter_++ ) (*homeBaseIter_)->setBatchUnModified(); } else { NORMAL_ERR( "CatalogBaseImpl::flush() - Database transaction is not successful!" ); } } else { NORMAL_ERR( "CatalogBaseImpl::flush() - flush is not executed!" ); } }
void svStorageEnginePostgreSQL::UpdatePoolClient(const string &node, const string &pool_name, const string &dev, const string &org, svPoolClientState state) { if (sql_update_pool_client.size() == 0) throw svExStorageQuery("No SQL query set: update-pool-client"); string sql(sql_update_pool_client); string value(dev); EscapeString(value); if (PrepareSQL(sql, "%d", value) == 0) throw svExStorageQuery("Invalid SQL, missing match token: device"); value = node; EscapeString(value); PrepareSQL(sql, "%n", value); value = pool_name; EscapeString(value); PrepareSQL(sql, "%p", value); value = org; EscapeString(value); PrepareSQL(sql, "%o", value); ostringstream os; map<svPoolClientState, string>::iterator i; i = pool_client_state.find(state); if (i == pool_client_state.end()) os << state; else os << i->second; PrepareSQL(sql, "%s", os.str()); ExecuteSQL(sql); if (PQresultStatus(pg_res) != PGRES_COMMAND_OK) { throw svExStorageQuery(string("PQexec: ") + PQresultErrorMessage(pg_res)); } uint32_t affected_rows = (uint32_t)atoi(PQcmdTuples(pg_res)); if (affected_rows == 0) throw svExStorageRecordNotFound("Pool client not found"); }
void CAcqHive::ExecuteAcqSQL(std::vector<std::string>& vec_sql) throw(base::Exception) { if ( vec_sql.empty() ) { throw base::Exception(ACQERR_EXECUTE_ACQSQL, "[HIVE] No sql to be executed! [FILE:%s, LINE:%d]", __FILE__, __LINE__); } try { const size_t VEC_SIZE = vec_sql.size(); for ( size_t i = 0; i < VEC_SIZE; ++i ) { std::string& hive_sql = vec_sql[i]; ExecuteSQL(hive_sql); } } catch ( const base::Exception& ex ) { throw base::Exception(ACQERR_EXECUTE_ACQSQL, "[HIVE] Execute sql failed! %s [FILE:%s, LINE:%d]", ex.What().c_str(), __FILE__, __LINE__); } }
void svStorageEnginePostgreSQL::PurgePoolClients( const string &node, const string &org) { if (sql_purge_pool_clients.size() == 0) throw svExStorageQuery("No SQL query set: purge-pool-clients"); string sql(sql_purge_pool_clients); string value(node); EscapeString(value); if (PrepareSQL(sql, "%n", value) == 0) throw svExStorageQuery("Invalid SQL, missing match token: node"); value = org; EscapeString(value); PrepareSQL(sql, "%o", value); ExecuteSQL(sql); if (PQresultStatus(pg_res) != PGRES_COMMAND_OK) { throw svExStorageQuery(string("PQexec: ") + PQresultErrorMessage(pg_res)); } }
int DBSpool::ExecuteSQL(const string& strSQL, RESDataList* res, int &iRelt, unsigned long long* iInsertId) { MYSQL_RES* SQLRes = NULL; short shIdt = 0; int iRet = ExecuteSQL(strSQL, &SQLRes, shIdt, iRelt, iInsertId); if (iRet == SQL_TYPE_SELECT){ //MYSQL_FIELD* fields; MYSQL_ROW row; int irows, ifields; string strValue; irows = mysql_num_rows(SQLRes); if(irows == 0) { ReleaseConnection(shIdt); return iRet; } ifields = mysql_num_fields(SQLRes); if(ifields == 0){ ReleaseConnection(shIdt); return iRet; } //fields = mysql_fetch_fields(SQLRes); for(int i=0; i<irows; i++){ if((row = mysql_fetch_row(SQLRes)) == NULL){ break; } for(int j=0; j<ifields; j++) { if (row[j]){ strValue = row[j]; }else{ strValue = ""; } res->push_back(strValue); } } ReleaseConnection(shIdt); } return iRet; }
/*! \brief Load data records (&D) Call VFKReader::OpenFile() before this function. \param poDataBlock limit to selected data block or NULL for all \return number of data records or -1 on error */ int VFKReaderSQLite::ReadDataRecords(IVFKDataBlock *poDataBlock) { int nDataRecords; int iDataBlock; const char *pszName; CPLString osSQL; IVFKDataBlock *poDataBlockCurrent; sqlite3_stmt *hStmt; pszName = NULL; if (poDataBlock) { /* read records only for selected data block */ /* table name */ pszName = poDataBlock->GetName(); /* check for existing records (re-use already inserted data) */ osSQL.Printf("SELECT num_records FROM %s WHERE " "table_name = '%s'", VFK_DB_TABLE, pszName); hStmt = PrepareStatement(osSQL.c_str()); nDataRecords = -1; if (ExecuteSQL(hStmt) == OGRERR_NONE) { nDataRecords = sqlite3_column_int(hStmt, 0); } sqlite3_finalize(hStmt); } else { /* read all data blocks */ /* check for existing records (re-use already inserted data) */ osSQL.Printf("SELECT COUNT(*) FROM %s WHERE num_records = -1", VFK_DB_TABLE); hStmt = PrepareStatement(osSQL.c_str()); if (ExecuteSQL(hStmt) == OGRERR_NONE && sqlite3_column_int(hStmt, 0) == 0) nDataRecords = 0; /* -> read from DB */ else nDataRecords = -1; /* -> read from VFK file */ sqlite3_finalize(hStmt); } if (nDataRecords > -1) { /* read records from DB */ /* read from DB */ long iFID; int iRowId; VFKFeatureSQLite *poNewFeature = NULL; poDataBlockCurrent = NULL; for (iDataBlock = 0; iDataBlock < GetDataBlockCount(); iDataBlock++) { poDataBlockCurrent = GetDataBlock(iDataBlock); if (poDataBlock && poDataBlock != poDataBlockCurrent) continue; poDataBlockCurrent->SetFeatureCount(0); /* avoid recursive call */ pszName = poDataBlockCurrent->GetName(); CPLAssert(NULL != pszName); osSQL.Printf("SELECT %s,_rowid_ FROM %s ", FID_COLUMN, pszName); if (EQUAL(pszName, "SBP")) osSQL += "WHERE PORADOVE_CISLO_BODU = 1 "; osSQL += "ORDER BY "; osSQL += FID_COLUMN; hStmt = PrepareStatement(osSQL.c_str()); nDataRecords = 0; while (ExecuteSQL(hStmt) == OGRERR_NONE) { iFID = sqlite3_column_int(hStmt, 0); iRowId = sqlite3_column_int(hStmt, 1); poNewFeature = new VFKFeatureSQLite(poDataBlockCurrent, iRowId, iFID); poDataBlockCurrent->AddFeature(poNewFeature); nDataRecords++; } /* check DB consistency */ osSQL.Printf("SELECT num_features FROM %s WHERE table_name = '%s'", VFK_DB_TABLE, pszName); hStmt = PrepareStatement(osSQL.c_str()); if (ExecuteSQL(hStmt) == OGRERR_NONE) { int nFeatDB; nFeatDB = sqlite3_column_int(hStmt, 0); if (nFeatDB > 0 && nFeatDB != poDataBlockCurrent->GetFeatureCount()) CPLError(CE_Failure, CPLE_AppDefined, "%s: Invalid number of features " CPL_FRMT_GIB " (should be %d)", pszName, poDataBlockCurrent->GetFeatureCount(), nFeatDB); } sqlite3_finalize(hStmt); } } else { /* read from VFK file and insert records into DB */ /* begin transaction */ ExecuteSQL("BEGIN"); /* INSERT ... */ nDataRecords = VFKReader::ReadDataRecords(poDataBlock); /* update VFK_DB_TABLE table */ poDataBlockCurrent = NULL; for (iDataBlock = 0; iDataBlock < GetDataBlockCount(); iDataBlock++) { poDataBlockCurrent = GetDataBlock(iDataBlock); if (poDataBlock && poDataBlock != poDataBlockCurrent) continue; osSQL.Printf("UPDATE %s SET num_records = %d WHERE " "table_name = '%s'", VFK_DB_TABLE, poDataBlockCurrent->GetRecordCount(), poDataBlockCurrent->GetName()); ExecuteSQL(osSQL); } /* commit transaction */ ExecuteSQL("COMMIT"); } return nDataRecords; }
/*! \brief Create DB table from VFKDataBlock (SQLITE only) \param poDataBlock pointer to VFKDataBlock instance */ void VFKReaderSQLite::AddDataBlock(IVFKDataBlock *poDataBlock, const char *pszDefn) { const char *pszBlockName; const char *pszKey; CPLString osCommand, osColumn; bool bUnique; VFKPropertyDefn *poPropertyDefn; sqlite3_stmt *hStmt; bUnique = !CPLTestBool(CPLGetConfigOption("OGR_VFK_DB_IGNORE_DUPLICATES", "NO")); pszBlockName = poDataBlock->GetName(); /* register table in VFK_DB_TABLE */ osCommand.Printf("SELECT COUNT(*) FROM %s WHERE " "table_name = '%s'", VFK_DB_TABLE, pszBlockName); hStmt = PrepareStatement(osCommand.c_str()); if (ExecuteSQL(hStmt) == OGRERR_NONE && sqlite3_column_int(hStmt, 0) == 0) { osCommand.Printf("CREATE TABLE '%s' (", pszBlockName); for (int i = 0; i < poDataBlock->GetPropertyCount(); i++) { poPropertyDefn = poDataBlock->GetProperty(i); if (i > 0) osCommand += ","; osColumn.Printf("%s %s", poPropertyDefn->GetName(), poPropertyDefn->GetTypeSQL().c_str()); osCommand += osColumn; } osColumn.Printf(",%s integer", FID_COLUMN); osCommand += osColumn; if (poDataBlock->GetGeometryType() != wkbNone) { osColumn.Printf(",%s blob", GEOM_COLUMN); osCommand += osColumn; } osCommand += ")"; ExecuteSQL(osCommand.c_str()); /* CREATE TABLE */ /* create indices */ osCommand.Printf("%s_%s", pszBlockName, FID_COLUMN); CreateIndex(osCommand.c_str(), pszBlockName, FID_COLUMN, !EQUAL(pszBlockName, "SBP")); pszKey = ((VFKDataBlockSQLite *) poDataBlock)->GetKey(); if (pszKey) { osCommand.Printf("%s_%s", pszBlockName, pszKey); CreateIndex(osCommand.c_str(), pszBlockName, pszKey, bUnique); } if (EQUAL(pszBlockName, "SBP")) { /* create extra indices for SBP */ CreateIndex("SBP_OB", pszBlockName, "OB_ID", FALSE); CreateIndex("SBP_HP", pszBlockName, "HP_ID", FALSE); CreateIndex("SBP_DPM", pszBlockName, "DPM_ID", FALSE); CreateIndex("SBP_OB_HP_DPM", pszBlockName, "OB_ID,HP_ID,DPM_ID", bUnique); CreateIndex("SBP_OB_POR", pszBlockName, "OB_ID,PORADOVE_CISLO_BODU", FALSE); CreateIndex("SBP_HP_POR", pszBlockName, "HP_ID,PORADOVE_CISLO_BODU", FALSE); CreateIndex("SBP_DPM_POR", pszBlockName, "DPM_ID,PORADOVE_CISLO_BODU", FALSE); } else if (EQUAL(pszBlockName, "HP")) { /* create extra indices for HP */ CreateIndex("HP_PAR1", pszBlockName, "PAR_ID_1", FALSE); CreateIndex("HP_PAR2", pszBlockName, "PAR_ID_2", FALSE); } else if (EQUAL(pszBlockName, "OB")) { /* create extra indices for OP */ CreateIndex("OB_BUD", pszBlockName, "BUD_ID", FALSE); } /* update VFK_DB_TABLE meta-table */ osCommand.Printf("INSERT INTO %s (file_name, table_name, " "num_records, num_features, num_geometries, table_defn) VALUES " "('%s', '%s', -1, 0, 0, '%s')", VFK_DB_TABLE, m_pszFilename, pszBlockName, pszDefn); ExecuteSQL(osCommand.c_str()); sqlite3_finalize(hStmt); } return VFKReader::AddDataBlock(poDataBlock, NULL); }
/*! \brief VFKReaderSQLite constructor */ VFKReaderSQLite::VFKReaderSQLite(const char *pszFilename) : VFKReader(pszFilename) { const char *pszDbNameConf; CPLString osDbName; CPLString osCommand; VSIStatBufL sStatBufDb, sStatBufVfk; /* open tmp SQLite DB (re-use DB file if already exists) */ pszDbNameConf = CPLGetConfigOption("OGR_VFK_DB_NAME", NULL); if (pszDbNameConf) { osDbName = pszDbNameConf; } else { osDbName = CPLResetExtension(m_pszFilename, "db"); } size_t nLen = osDbName.length(); if( nLen > 2048 ) { nLen = 2048; osDbName.resize(nLen); } m_pszDBname = new char [nLen+1]; std::strncpy(m_pszDBname, osDbName.c_str(), nLen); m_pszDBname[nLen] = 0; CPLDebug("OGR-VFK", "Using internal DB: %s", m_pszDBname); if (CPLTestBool(CPLGetConfigOption("OGR_VFK_DB_SPATIAL", "YES"))) m_bSpatial = TRUE; /* build geometry from DB */ else m_bSpatial = FALSE; /* store also geometry in DB */ m_bNewDb = TRUE; if (VSIStatL(osDbName, &sStatBufDb) == 0) { if (CPLTestBool(CPLGetConfigOption("OGR_VFK_DB_OVERWRITE", "NO"))) { m_bNewDb = TRUE; /* overwrite existing DB */ CPLDebug("OGR-VFK", "Internal DB (%s) already exists and will be overwritten", m_pszDBname); VSIUnlink(osDbName); } else { if (VSIStatL(pszFilename, &sStatBufVfk) == 0 && sStatBufVfk.st_mtime > sStatBufDb.st_mtime) { CPLDebug("OGR-VFK", "Found %s but ignoring because it appears\n" "be older than the associated VFK file.", osDbName.c_str()); m_bNewDb = TRUE; VSIUnlink(osDbName); } else { m_bNewDb = FALSE; /* re-use existing DB */ } } } /* if (m_bNewDb) { CPLError(CE_Warning, CPLE_AppDefined, "INFO: No internal SQLite DB found. Reading VFK data may take some time..."); } */ CPLDebug("OGR-VFK", "New DB: %s Spatial: %s", m_bNewDb ? "yes" : "no", m_bSpatial ? "yes" : "no"); if (SQLITE_OK != sqlite3_open(osDbName, &m_poDB)) { CPLError(CE_Failure, CPLE_AppDefined, "Creating SQLite DB failed"); } else { char* pszErrMsg = NULL; CPL_IGNORE_RET_VAL(sqlite3_exec(m_poDB, "PRAGMA synchronous = OFF", NULL, NULL, &pszErrMsg)); sqlite3_free(pszErrMsg); } if (m_bNewDb) { /* new DB, create support metadata tables */ osCommand.Printf("CREATE TABLE %s (file_name text, table_name text, num_records integer, " "num_features integer, num_geometries integer, table_defn text)", VFK_DB_TABLE); ExecuteSQL(osCommand.c_str()); /* header table */ osCommand.Printf("CREATE TABLE %s (key text, value text)", VFK_DB_HEADER); ExecuteSQL(osCommand.c_str()); } }
/*! \brief Add feature \param poDataBlock pointer to VFKDataBlock instance \param poFeature pointer to VFKFeature instance */ OGRErr VFKReaderSQLite::AddFeature(IVFKDataBlock *poDataBlock, VFKFeature *poFeature) { CPLString osCommand; CPLString osValue; const char *pszBlockName; OGRFieldType ftype; const VFKProperty *poProperty; VFKFeatureSQLite *poNewFeature; pszBlockName = poDataBlock->GetName(); osCommand.Printf("INSERT INTO '%s' VALUES(", pszBlockName); for (int i = 0; i < poDataBlock->GetPropertyCount(); i++) { ftype = poDataBlock->GetProperty(i)->GetType(); poProperty = poFeature->GetProperty(i); if (i > 0) osCommand += ","; if (poProperty->IsNull()) osValue.Printf("NULL"); else { switch (ftype) { case OFTInteger: osValue.Printf("%d", poProperty->GetValueI()); break; case OFTReal: osValue.Printf("%f", poProperty->GetValueD()); break; case OFTString: if (poDataBlock->GetProperty(i)->IsIntBig()) osValue.Printf("%s", poProperty->GetValueS()); else osValue.Printf("'%s'", poProperty->GetValueS(TRUE)); break; default: osValue.Printf("'%s'", poProperty->GetValueS()); break; } } osCommand += osValue; } osValue.Printf("," CPL_FRMT_GIB, poFeature->GetFID()); if (poDataBlock->GetGeometryType() != wkbNone) { osValue += ",NULL"; } osValue += ")"; osCommand += osValue; if (ExecuteSQL(osCommand.c_str(), TRUE) != OGRERR_NONE) return OGRERR_FAILURE; if (EQUAL(pszBlockName, "SBP")) { poProperty = poFeature->GetProperty("PORADOVE_CISLO_BODU"); if( poProperty == NULL ) { CPLError(CE_Failure, CPLE_AppDefined, "Cannot find property PORADOVE_CISLO_BODU"); return OGRERR_FAILURE; } if (!EQUAL(poProperty->GetValueS(), "1")) return OGRERR_NONE; } poNewFeature = new VFKFeatureSQLite(poDataBlock, poDataBlock->GetRecordCount(RecordValid) + 1, poFeature->GetFID()); poDataBlock->AddFeature(poNewFeature); return OGRERR_NONE; }