wxArrayString FirebirdDatabaseLayer::GetViews()
{
wxArrayString returnArray;
DatabaseResultSet* pResult = NULL;
#ifndef DONT_USE_DATABASE_LAYER_EXCEPTIONS
try
{
#endif
wxString query = _("SELECT RDB$RELATION_NAME FROM RDB$RELATIONS WHERE RDB$SYSTEM_FLAG=0 AND RDB$VIEW_BLR IS NOT NULL");
pResult = ExecuteQuery(query);
while (pResult->Next())
{
returnArray.Add(pResult->GetResultString(1).Trim());
}
#ifndef DONT_USE_DATABASE_LAYER_EXCEPTIONS
}
catch (DatabaseLayerException& e)
{
if (pResult != NULL)
{
CloseResultSet(pResult);
pResult = NULL;
}
throw e;
}
#endif
if (pResult != NULL)
{
CloseResultSet(pResult);
pResult = NULL;
}
return returnArray;
}
wxArrayString wxPostgresDatabase::GetViews()
{
wxArrayString returnArray;
wxDatabaseResultSet* pResult = NULL;
#if wxUSE_DATABASE_EXCEPTIONS
try
{
#endif
wxString query = _("SELECT table_name FROM information_schema.tables WHERE table_type='VIEW' AND table_schema='public';");
pResult = ExecuteQuery(query);
while (pResult->Next())
{
returnArray.Add(pResult->GetResultString(1));
}
#if wxUSE_DATABASE_EXCEPTIONS
}
catch (wxDatabaseException& e)
{
if (pResult != NULL)
{
CloseResultSet(pResult);
pResult = NULL;
}
throw e;
}
#endif
if (pResult != NULL)
{
CloseResultSet(pResult);
pResult = NULL;
}
return returnArray;
}
wxArrayString PostgresDatabaseLayer::GetTables()
{
wxArrayString returnArray;
DatabaseResultSet* pResult = NULL;
#ifndef DONT_USE_DATABASE_LAYER_EXCEPTIONS
try
{
#endif
wxString query = _("SELECT table_name FROM information_schema.tables WHERE table_type='BASE TABLE' AND table_schema='public';");
pResult = ExecuteQuery(query);
while (pResult->Next())
{
returnArray.Add(pResult->GetResultString(1));
}
#ifndef DONT_USE_DATABASE_LAYER_EXCEPTIONS
}
catch (DatabaseLayerException& e)
{
if (pResult != NULL)
{
CloseResultSet(pResult);
pResult = NULL;
}
throw e;
}
#endif
if (pResult != NULL)
{
CloseResultSet(pResult);
pResult = NULL;
}
return returnArray;
}
/*Method to execute sql statements like SELECT and return Cursor
Inputs:
query- string containing sql query
qlength - length of query (for binary data). if 0 then assume null terminated.
Output: NULL cursor on error
*/
DBCursor *DBConnection_POSTGRESQL::sqlQuery(char *p_query, DBString *p_arguments, int p_argument_count, int p_rows)
{
PGresult *t_postgres_result;
t_postgres_result = ExecuteQuery(p_query, p_arguments, p_argument_count);
ExecStatusType t_status;
t_status = PQresultStatus(t_postgres_result);
DBCursor_POSTGRESQL *t_cursor;
t_cursor = NULL;
if (t_status == PGRES_TUPLES_OK)
{
int t_column_count;
t_column_count = PQnfields(t_postgres_result);
if (t_column_count != 0)
{
t_cursor = new DBCursor_POSTGRESQL();
if (!t_cursor -> open((DBConnection *)this, t_postgres_result))
{
delete t_cursor;
t_cursor = NULL;
}
else
addCursor(t_cursor);
}
t_postgres_result = NULL;
}
// OK-2007-09-10 : Bug 5360, if the query succeeded, we clear the error message, otherwise we set it.
if (t_cursor != NULL)
errorMessageSet(NULL);
else
errorMessageSet(PQerrorMessage(dbconn));
return (DBCursor *)t_cursor;
}
// -------------------------------------------------------------------------------- //
void guDbRadios::GetRadioGenresList( const int source, const wxArrayInt &ids, guListItems * listitems, wxArrayInt * radioflags )
{
wxString query;
wxSQLite3ResultSet dbRes;
if( ids.Count() )
{
query = wxT( "SELECT radiogenre_id, radiogenre_name, radiogenre_flags FROM radiogenres WHERE " );
query += wxString::Format( wxT( "radiogenre_source = %i AND " ), source );
query += ArrayToFilter( ids, wxT( "radiogenre_id" ) );
query += wxT( " ORDER BY radiogenre_name COLLATE NOCASE;" );
//guLogMessage( query );
dbRes = ExecuteQuery( query );
while( dbRes.NextRow() )
{
listitems->Add( new guListItem( dbRes.GetInt( 0 ), dbRes.GetString( 1 ) ) );
if( radioflags )
radioflags->Add( dbRes.GetInt( 2 ) );
}
dbRes.Finalize();
}
}
/*Method to execute quick and fast sql statements like UPDATE and INSERT
Inputs:
query- string containing sql query
qlength - length of query (for binary data). if 0 then assume null terminated.
affectedrows - will recieve number of rows updated or inserted
Output: False on error
*/
Bool DBConnection_ORACLE::sqlExecute(char *p_query, DBString *p_arguments, int p_argument_count, unsigned int &p_affected_rows)
{
if (!isConnected)
return False;
Cda_Def *t_cursor;
t_cursor = ExecuteQuery(p_query, p_arguments, p_argument_count);
if (t_cursor == NULL)
{
// OK-2007-09-10 : Bug 5360
char t_error_message[512];
oerhms((cda_def *)getLDA(), lda.rc, (text *)t_error_message, (sword) sizeof(t_error_message));
errorMessageSet(t_error_message);
return False;
}
int t_affected_rows;
t_affected_rows = (int)t_cursor -> rpc;
p_affected_rows = t_affected_rows;
oclose(t_cursor);
errorMessageSet(NULL);
return True;
}
// -------------------------------------------------------------------------------- //
int guDbRadios::GetRadioStations( guRadioStations * Stations )
{
wxString query;
wxString querydb;
wxString subquery;
wxSQLite3ResultSet dbRes;
guRadioStation * Station;
if( !GetRadioFiltersCount() )
{
query = wxT( "SELECT DISTINCT radiostation_name, radiostation_id, radiostation_scid, " \
"radiostation_source, radiostation_genreid, radiostation_link, radiostation_type, " \
"radiostation_br, radiostation_lc, radiostation_ct " \
"FROM radiostations WHERE " );
query += wxString::Format( wxT( "radiostation_source = %u " ), m_RadioSource );
query += wxT( "GROUP BY radiostation_name, radiostation_br " );
}
else
{
if( m_RadioSource == guRADIO_SOURCE_USER )
{
//SELECT * FROM radiostations, radiosetlabels WHERE radiosetlabel_stationid = radiostation_id AND radiosetlabel_labelid IN ( 1 )
query = wxT( "SELECT DISTINCT radiostation_name, radiostation_id, radiostation_scid, radiostation_source, radiostation_genreid, radiostation_link, radiostation_type, radiostation_br, radiostation_lc, radiostation_ct " );
querydb = wxT( "FROM radiostations " );
//else
wxString subquery = wxEmptyString;
if( m_RaLaFilters.Count() )
{
querydb += wxT( ", radiosetlabels " );
subquery += wxT( "WHERE radiostation_scid = radiosetlabel_stationid AND radiostation_source = 1" );
subquery += wxT( " AND " ) + ArrayToFilter( m_RaLaFilters, wxT( "radiosetlabel_labelid" ) );
}
else
{
subquery += wxT( "WHERE radiostation_source = 1 " );
}
if( m_RaTeFilters.Count() )
{
//querydb += wxT( ", radiogenres " );
//subquery += wxT( "AND radiostation_genreid = radiogenre_id " );
subquery += wxT( "AND " ) + RadioFiltersSQL();
}
subquery += wxT( "GROUP BY radiostation_name, radiostation_br " );
query = query + querydb + subquery;
}
else
{
//SELECT * FROM radiostations, radiosetlabels WHERE radiosetlabel_stationid = radiostation_id AND radiosetlabel_labelid IN ( 1 )
query = wxT( "SELECT DISTINCT radiostation_name, radiostation_id, radiostation_scid, radiostation_source, " \
"radiostation_genreid, radiostation_link, radiostation_type, radiostation_br, radiostation_lc, radiostation_ct " \
"FROM radiostations, radiogenres" );
//else
wxString subquery = wxEmptyString;
if( m_RaLaFilters.Count() )
{
query += wxT( ", radiosetlabels WHERE radiostation_genreid = radiogenre_id AND radiostation_scid = radiosetlabel_stationid AND " );
subquery += ArrayToFilter( m_RaLaFilters, wxT( "radiosetlabel_labelid" ) );
}
else
{
query += wxT( " WHERE radiostation_genreid = radiogenre_id " );
}
subquery += wxString::Format( wxT( " AND radiostation_source = %i " ), m_RadioSource );
if( m_RaGeFilters.Count() )
{
subquery += wxT( " AND " ) + ArrayToFilter( m_RaGeFilters, wxT( "radiostation_genreid" ) );
}
if( m_RaTeFilters.Count() )
{
subquery += wxT( " AND " ) + RadioFiltersSQL();
}
if( !subquery.IsEmpty() )
{
query = query + subquery;
}
query += wxT( "GROUP BY radiostation_name, radiostation_br " );
}
}
query += wxT( " ORDER BY " );
if( m_StationsOrder == guRADIOSTATIONS_ORDER_NAME )
query += wxT( "radiostation_name COLLATE NOCASE" );
else if( m_StationsOrder == guRADIOSTATIONS_ORDER_BITRATE )
query += wxT( "radiostation_br" );
else if( m_StationsOrder == guRADIOSTATIONS_ORDER_LISTENERS )
query += wxT( "radiostation_lc" );
else if( m_StationsOrder == guRADIOSTATIONS_ORDER_TYPE )
query += wxT( "radiostation_type" );
else //if( m_StationsOrder == guRADIOSTATIONS_COLUMN_NOWPLAYING )
query += wxT( "radiostation_ct" );
if( m_StationsOrderDesc )
query += wxT( " DESC;" );
//guLogMessage( wxT( "GetRadioStations\n%s" ), query.c_str() );
dbRes = ExecuteQuery( query );
while( dbRes.NextRow() )
{
Station = new guRadioStation();
Station->m_Name = dbRes.GetString( 0 );
Station->m_Id = dbRes.GetInt( 1 );
Station->m_SCId = dbRes.GetInt( 2 );
Station->m_Source = dbRes.GetInt( 3 );
Station->m_GenreId = dbRes.GetInt( 4 );
Station->m_Link = dbRes.GetString( 5 );
Station->m_Type = dbRes.GetString( 6 );
Station->m_BitRate = dbRes.GetInt( 7 );
Station->m_Listeners = dbRes.GetInt( 8 );
Station->m_NowPlaying = dbRes.GetString( 9 );
Stations->Add( Station );
}
dbRes.Finalize();
return Stations->Count();
}
/*
* ExecCreateTableAs -- execute a CREATE TABLE AS command
*/
void
ExecCreateTableAs(CreateTableAsStmt *stmt, const char *queryString,
ParamListInfo params, char *completionTag)
{
Query *query = (Query *) stmt->query;
IntoClause *into = stmt->into;
bool is_matview = (into->viewQuery != NULL);
DestReceiver *dest;
Oid save_userid = InvalidOid;
int save_sec_context = 0;
int save_nestlevel = 0;
List *rewritten;
PlannedStmt *plan;
QueryDesc *queryDesc;
ScanDirection dir;
/*
* Create the tuple receiver object and insert info it will need
*/
dest = CreateIntoRelDestReceiver(into);
/*
* The contained Query could be a SELECT, or an EXECUTE utility command.
* If the latter, we just pass it off to ExecuteQuery.
*/
Assert(IsA(query, Query));
if (query->commandType == CMD_UTILITY &&
IsA(query->utilityStmt, ExecuteStmt))
{
ExecuteStmt *estmt = (ExecuteStmt *) query->utilityStmt;
Assert(!is_matview); /* excluded by syntax */
ExecuteQuery(estmt, into, queryString, params, dest, completionTag);
return;
}
Assert(query->commandType == CMD_SELECT);
/*
* For materialized views, lock down security-restricted operations and
* arrange to make GUC variable changes local to this command. This is
* not necessary for security, but this keeps the behavior similar to
* REFRESH MATERIALIZED VIEW. Otherwise, one could create a materialized
* view not possible to refresh.
*/
if (is_matview)
{
GetUserIdAndSecContext(&save_userid, &save_sec_context);
SetUserIdAndSecContext(save_userid,
save_sec_context | SECURITY_RESTRICTED_OPERATION);
save_nestlevel = NewGUCNestLevel();
}
/*
* Parse analysis was done already, but we still have to run the rule
* rewriter. We do not do AcquireRewriteLocks: we assume the query either
* came straight from the parser, or suitable locks were acquired by
* plancache.c.
*
* Because the rewriter and planner tend to scribble on the input, we make
* a preliminary copy of the source querytree. This prevents problems in
* the case that CTAS is in a portal or plpgsql function and is executed
* repeatedly. (See also the same hack in EXPLAIN and PREPARE.)
*/
rewritten = QueryRewrite((Query *) copyObject(query));
/* SELECT should never rewrite to more or less than one SELECT query */
if (list_length(rewritten) != 1)
elog(ERROR, "unexpected rewrite result for CREATE TABLE AS SELECT");
query = (Query *) linitial(rewritten);
Assert(query->commandType == CMD_SELECT);
/* plan the query */
plan = pg_plan_query(query, 0, params);
/*
* Use a snapshot with an updated command ID to ensure this query sees
* results of any previously executed queries. (This could only matter if
* the planner executed an allegedly-stable function that changed the
* database contents, but let's do it anyway to be parallel to the EXPLAIN
* code path.)
*/
PushCopiedSnapshot(GetActiveSnapshot());
UpdateActiveSnapshotCommandId();
/* Create a QueryDesc, redirecting output to our tuple receiver */
queryDesc = CreateQueryDesc(plan, queryString,
GetActiveSnapshot(), InvalidSnapshot,
dest, params, 0);
/* call ExecutorStart to prepare the plan for execution */
ExecutorStart(queryDesc, GetIntoRelEFlags(into));
/*
* Normally, we run the plan to completion; but if skipData is specified,
* just do tuple receiver startup and shutdown.
*/
if (into->skipData)
dir = NoMovementScanDirection;
else
dir = ForwardScanDirection;
/* run the plan */
ExecutorRun(queryDesc, dir, 0L);
/* save the rowcount if we're given a completionTag to fill */
if (completionTag)
snprintf(completionTag, COMPLETION_TAG_BUFSIZE,
"SELECT %u", queryDesc->estate->es_processed);
/* and clean up */
ExecutorFinish(queryDesc);
ExecutorEnd(queryDesc);
FreeQueryDesc(queryDesc);
PopActiveSnapshot();
if (is_matview)
{
/* Roll back any GUC changes */
AtEOXact_GUC(false, save_nestlevel);
/* Restore userid and security context */
SetUserIdAndSecContext(save_userid, save_sec_context);
}
}
/*
* ExecCreateTableAs -- execute a CREATE TABLE AS command
*/
void
ExecCreateTableAs(CreateTableAsStmt *stmt, const char *queryString,
ParamListInfo params, char *completionTag)
{
Query *query = (Query *) stmt->query;
IntoClause *into = stmt->into;
DestReceiver *dest;
List *rewritten;
PlannedStmt *plan;
QueryDesc *queryDesc;
ScanDirection dir;
/*
* Create the tuple receiver object and insert info it will need
*/
dest = CreateIntoRelDestReceiver(into);
/*
* The contained Query could be a SELECT, or an EXECUTE utility command.
* If the latter, we just pass it off to ExecuteQuery.
*/
Assert(IsA(query, Query));
if (query->commandType == CMD_UTILITY &&
IsA(query->utilityStmt, ExecuteStmt))
{
ExecuteStmt *estmt = (ExecuteStmt *) query->utilityStmt;
ExecuteQuery(estmt, into, queryString, params, dest, completionTag);
return;
}
Assert(query->commandType == CMD_SELECT);
/*
* Parse analysis was done already, but we still have to run the rule
* rewriter. We do not do AcquireRewriteLocks: we assume the query either
* came straight from the parser, or suitable locks were acquired by
* plancache.c.
*
* Because the rewriter and planner tend to scribble on the input, we make
* a preliminary copy of the source querytree. This prevents problems in
* the case that CTAS is in a portal or plpgsql function and is executed
* repeatedly. (See also the same hack in EXPLAIN and PREPARE.)
*/
rewritten = QueryRewrite((Query *) copyObject(query));
/* SELECT should never rewrite to more or less than one SELECT query */
if (list_length(rewritten) != 1)
elog(ERROR, "unexpected rewrite result for CREATE TABLE AS SELECT");
query = (Query *) linitial(rewritten);
Assert(query->commandType == CMD_SELECT);
/* plan the query */
plan = pg_plan_query(query, 0, params);
/*
* Use a snapshot with an updated command ID to ensure this query sees
* results of any previously executed queries. (This could only matter if
* the planner executed an allegedly-stable function that changed the
* database contents, but let's do it anyway to be parallel to the EXPLAIN
* code path.)
*/
PushCopiedSnapshot(GetActiveSnapshot());
UpdateActiveSnapshotCommandId();
/* Create a QueryDesc, redirecting output to our tuple receiver */
queryDesc = CreateQueryDesc(plan, queryString,
GetActiveSnapshot(), InvalidSnapshot,
dest, params, 0);
/* call ExecutorStart to prepare the plan for execution */
ExecutorStart(queryDesc, GetIntoRelEFlags(into));
/*
* Normally, we run the plan to completion; but if skipData is specified,
* just do tuple receiver startup and shutdown.
*/
if (into->skipData)
dir = NoMovementScanDirection;
else
dir = ForwardScanDirection;
/* run the plan */
ExecutorRun(queryDesc, dir, 0L);
/* save the rowcount if we're given a completionTag to fill */
if (completionTag)
snprintf(completionTag, COMPLETION_TAG_BUFSIZE,
"SELECT %u", queryDesc->estate->es_processed);
/* and clean up */
ExecutorFinish(queryDesc);
ExecutorEnd(queryDesc);
FreeQueryDesc(queryDesc);
PopActiveSnapshot();
}
bool CTextureDatabase::InvalidateCachedTexture(const CStdString &url)
{
CStdString date = (CDateTime::GetCurrentDateTime() - CDateTimeSpan(2, 0, 0, 0)).GetAsDBDateTime();
CStdString sql = PrepareSQL("UPDATE texture SET lasthashcheck='%s' WHERE url='%s'", date.c_str(), url.c_str());
return ExecuteQuery(sql);
}
bool CTextureDatabase::SetCachedTextureValid(const CStdString &url, bool updateable)
{
CStdString date = updateable ? CDateTime::GetCurrentDateTime().GetAsDBDateTime() : "";
CStdString sql = PrepareSQL("UPDATE texture SET lasthashcheck='%s' WHERE url='%s'", date.c_str(), url.c_str());
return ExecuteQuery(sql);
}
bool CTextureDatabase::IncrementUseCount(const CTextureDetails &details)
{
CStdString sql = PrepareSQL("UPDATE sizes SET usecount=usecount+1, lastusetime=CURRENT_TIMESTAMP WHERE idtexture=%u AND width=%u AND height=%u", details.id, details.width, details.height);
return ExecuteQuery(sql);
}
DataQuery::DataQuery(DataSource* data_source, const Rocket::Core::String& table, const Rocket::Core::String& _fields, int offset, int limit, const Rocket::Core::String& order)
{
ExecuteQuery(data_source, table, _fields, offset, limit, order);
}
wxArrayString TdsDatabaseLayer::GetColumns(const wxString& table)
{
wxArrayString returnArray;
// Keep these variables outside of scope so that we can clean them up
// in case of an error
/* -- Prepared statement support isn't working as well as it should so use concatenated wxStrings
PreparedStatement* pStatement = NULL;
DatabaseResultSet* pResult = NULL;
#ifndef DONT_USE_DATABASE_LAYER_EXCEPTIONS
try
{
#endif
wxString query = _("sp_columns ?;");
pStatement = PrepareStatement(query);
if (pStatement)
{
pStatement->SetParamString(1, table);
pResult = pStatement->ExecuteQuery();
if (pResult)
{
while (pResult->Next())
{
//wxPrintf(_("Adding table: '%s'\n"), pResult->GetResultString(_("TABLE_NAME")).Trim());
returnArray.Add(pResult->GetResultString(_("TABLE_NAME")).Trim());
}
}
}
#ifndef DONT_USE_DATABASE_LAYER_EXCEPTIONS
}
catch (DatabaseLayerException& e)
{
if (pResult != NULL)
{
CloseResultSet(pResult);
pResult = NULL;
}
if (pStatement != NULL)
{
CloseStatement(pStatement);
pStatement = NULL;
}
throw e;
}
#endif
if (pResult != NULL)
{
CloseResultSet(pResult);
pResult = NULL;
}
if (pStatement != NULL)
{
CloseStatement(pStatement);
pStatement = NULL;
}
*/
DatabaseResultSet* pResult = NULL;
#ifndef DONT_USE_DATABASE_LAYER_EXCEPTIONS
try
{
#endif
wxString query = wxString::Format(_("sp_columns %s;"), table.c_str());
pResult = ExecuteQuery(query);
while (pResult->Next())
{
//wxPrintf(_("Adding column: '%s'\n"), pResult->GetResultString(_("COLUMN_NAME")).Trim());
returnArray.Add(pResult->GetResultString(_("COLUMN_NAME")).Trim());
}
#ifndef DONT_USE_DATABASE_LAYER_EXCEPTIONS
}
catch (DatabaseLayerException& e)
{
if (pResult != NULL)
{
CloseResultSet(pResult);
pResult = NULL;
}
throw e;
}
#endif
if (pResult != NULL)
{
CloseResultSet(pResult);
pResult = NULL;
}
return returnArray;
}
//function to execute SELECT * query
wyInt32
TableView::ExecuteTableData()
{
wyString query;
wyInt32 ret,extraindex,j=0,no_row;
MySQLDataEx* pdata;
MYSQL_ROW fieldrow;
query.Sprintf("select * from `%s`.`%s`", m_mydata->m_db.GetString(), m_mydata->m_table.GetString());
//get filter info
GetFilterInfo(query);
//get sort info
GetSortInfo(query);
//get limits
GetLimits(query);
//execut query
m_mydata->m_datares = ExecuteQuery(query);
//is thread stopped
if(ThreadStopStatus())
{
return TE_STOPPED;
}
//any error? show error dialog
if(!m_mydata->m_datares)
{
return HandleErrors(query);
}
//allocate row array, if the thread is stopped, delete them
if((ret = AllocateRowsExArray()) != TE_SUCCESS ||
(ret = GetTableDetails()) != TE_SUCCESS)
{
pdata = ResetData(m_data);
delete pdata;
return ret;
}
extraindex = GetFieldIndex(m_wnd->m_tunnel, m_data->m_fieldres, "Extra");
no_row = m_wnd->m_tunnel->mysql_num_rows(m_data->m_fieldres);
m_data->m_colvirtual = (wyInt32*)calloc(no_row, sizeof(wyInt32));
while(fieldrow = m_wnd->m_tunnel->mysql_fetch_row(m_data->m_fieldres)){
if(!strstr(fieldrow[extraindex], "VIRTUAL") && !strstr(fieldrow[extraindex], "PERSISTENT") && !strstr(fieldrow[extraindex], "STORED"))
{
m_data->m_colvirtual[j++] = 0;
}
else
{
m_data->m_colvirtual[j++] = 1;
}
}
//add new row in the end
AddNewRow();
return TE_SUCCESS;
}
