/**
* Execute a SQL query.
*/
const RecordSet &MySqlDataProvider::execSql(const std::string& sql,
const bool refresh)
{
if (!mIsConnected)
throw std::runtime_error("not connected to database");
LOG_DEBUG("MySqlDataProvider::execSql Performing SQL query: " << sql);
// do something only if the query is different from the previous
// or if the cache must be refreshed
// otherwise just return the recordset from cache.
if (refresh || (sql != mSql))
{
mRecordSet.clear();
// actually execute the query.
if (mysql_query(mDb, sql.c_str()) != 0)
throw DbSqlQueryExecFailure(mysql_error(mDb));
if (mysql_field_count(mDb) > 0)
{
MYSQL_RES* res;
// get the result of the query.
if (!(res = mysql_store_result(mDb)))
throw DbSqlQueryExecFailure(mysql_error(mDb));
// set the field names.
unsigned nFields = mysql_num_fields(res);
MYSQL_FIELD* fields = mysql_fetch_fields(res);
Row fieldNames;
for (unsigned i = 0; i < nFields; ++i)
fieldNames.push_back(fields[i].name);
mRecordSet.setColumnHeaders(fieldNames);
// populate the RecordSet.
MYSQL_ROW row;
while ((row = mysql_fetch_row(res)))
{
Row r;
for (unsigned i = 0; i < nFields; ++i)
r.push_back(static_cast<char *>(row[i]));
mRecordSet.add(r);
}
// free memory
mysql_free_result(res);
}
}
return mRecordSet;
}
void MySqlDataProvider::rollbackTransaction()
throw (std::runtime_error)
{
if (!mIsConnected)
{
const std::string error = "Trying to rollback a transaction while not "
"connected to the database!";
LOG_ERROR(error);
throw std::runtime_error(error);
}
if (!inTransaction())
{
const std::string error = "Trying to rollback a transaction while no "
"one is open!";
LOG_ERROR(error);
throw std::runtime_error(error);
}
if (mysql_rollback(mDb) != 0)
{
LOG_ERROR("MySqlDataProvider::rollbackTransaction: "
<< mysql_error(mDb));
throw DbSqlQueryExecFailure(mysql_error(mDb));
}
mysql_autocommit(mDb, AUTOCOMMIT_ON);
mInTransaction = false;
LOG_DEBUG("SQL: transaction rolled back");
}
unsigned MySqlDataProvider::getModifiedRows() const
{
if (!mIsConnected)
{
const std::string error = "Trying to getModifiedRows while not "
"connected to the database!";
LOG_ERROR(error);
throw std::runtime_error(error);
}
const my_ulonglong affected = mysql_affected_rows(mDb);
if (affected > INT_MAX)
throw std::runtime_error(
"MySqlDataProvider::getLastId exceeded INT_MAX");
if (affected == (my_ulonglong)-1)
{
LOG_ERROR("MySqlDataProvider::getModifiedRows: " << mysql_error(mDb));
throw DbSqlQueryExecFailure(mysql_error(mDb));
}
return (unsigned) affected;
}
/**
* Execute a SQL query.
*/
const RecordSet &SqLiteDataProvider::execSql(const std::string& sql,
const bool refresh)
{
if (!mIsConnected)
throw std::runtime_error("not connected to database");
LOG_DEBUG("Performing SQL query: " << sql);
// do something only if the query is different from the previous
// or if the cache must be refreshed
// otherwise just return the recordset from cache.
if (refresh || (sql != mSql))
{
char** result;
int nRows;
int nCols;
char* errMsg;
mRecordSet.clear();
int errCode = sqlite3_get_table(
mDb, // an open database
sql.c_str(), // SQL to be executed
&result, // result of the query
&nRows, // number of result rows
&nCols, // number of result columns
&errMsg // error msg
);
if (errCode != SQLITE_OK)
{
std::string msg(sqlite3_errmsg(mDb));
LOG_ERROR("Error in SQL: " << sql << "\n" << msg);
// free memory
sqlite3_free_table(result);
sqlite3_free(errMsg);
throw DbSqlQueryExecFailure(msg);
}
// the first row of result[] contains the field names.
Row fieldNames;
for (int col = 0; col < nCols; ++col)
fieldNames.push_back(result[col]);
mRecordSet.setColumnHeaders(fieldNames);
// populate the RecordSet
for (int row = 0; row < nRows; ++row)
{
Row r;
for (int col = 0; col < nCols; ++col)
r.push_back(result[nCols + (row * nCols) + col]);
mRecordSet.add(r);
}
// free memory
sqlite3_free_table(result);
sqlite3_free(errMsg);
}
return mRecordSet;
}
const RecordSet &MySqlDataProvider::processSql()
{
if (!mIsConnected)
throw std::runtime_error("not connected to database");
// Since we'll have to return something in all cases,
// we clear the result member first.
mRecordSet.clear();
if (!mBind)
{
LOG_ERROR("MySqlDataProvider::processSql: "
"No bind done before processing.");
return mRecordSet;
}
if (mysql_stmt_bind_param(mStmt, mBind))
{
LOG_ERROR("MySqlDataProvider::processSql Bind params failed: "
<< mysql_stmt_error(mStmt));
return mRecordSet;
}
if (mysql_stmt_field_count(mStmt) > 0)
{
MYSQL_BIND* resultBind;
MYSQL_RES* res;
if (mysql_stmt_execute(mStmt))
{
LOG_ERROR("MySqlDataProvider::processSql Execute failed: "
<< mysql_stmt_error(mStmt));
}
res = mysql_stmt_result_metadata(mStmt);
// set the field names.
unsigned nFields = mysql_num_fields(res);
MYSQL_FIELD* fields = mysql_fetch_fields(res);
Row fieldNames;
resultBind = new MYSQL_BIND[mysql_num_fields(res)];
unsigned i = 0;
for (i = 0; i < mysql_num_fields(res); ++i)
{
resultBind[i].buffer_type = MYSQL_TYPE_STRING;
resultBind[i].buffer = (void*) new char[255];
resultBind[i].buffer_length = 255;
resultBind[i].is_null = new my_bool;
resultBind[i].length = new unsigned long;
resultBind[i].error = new my_bool;
}
if (mysql_stmt_bind_result(mStmt, resultBind))
{
LOG_ERROR("MySqlDataProvider::processSql Bind result failed: "
<< mysql_stmt_error(mStmt));
}
for (i = 0; i < nFields; ++i)
fieldNames.push_back(fields[i].name);
mRecordSet.setColumnHeaders(fieldNames);
// store the result of the query.
if (mysql_stmt_store_result(mStmt))
throw DbSqlQueryExecFailure(mysql_stmt_error(mStmt));
// populate the RecordSet.
while (!mysql_stmt_fetch(mStmt))
{
Row r;
for (i = 0; i < nFields; ++i)
r.push_back(static_cast<char *>(resultBind[i].buffer));
mRecordSet.add(r);
}
delete[] resultBind;
}
else
{
if (mysql_stmt_execute(mStmt))
{
LOG_ERROR("MySqlDataProvider::processSql Execute failed: "
<< mysql_stmt_error(mStmt));
}
}
// Free memory
mysql_stmt_free_result(mStmt);
return mRecordSet;
}
