/* * MysqQuery object defines the needed query functions for the dbConnect MySQL driver * Copyright (C) 2002 Johnathan Ingram, jingram@rogueware.org * * This library is free software; you can redistribute it and/or * modify it under the terms of the GNU Lesser General Public * License as published by the Free Software Foundation; either * version 2.1 of the License, or (at your option) any later version. * * This library is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU * Lesser General Public License for more details. * * You should have received a copy of the GNU Lesser General Public * License along with this library; if not, write to the Free Software * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 US * */ #include "mysqlQuery.h" #include #include #include "mysqlConnection.h" #include "dbconnectTypes.h" // ----------------------------------------------------------------------------- // PRIVATE: // ----------------------------------------------------------------------------- //------------------------------------------------------------------------------ // MysqlQuery::_mysqGetFieldsInformation //------------------------------------------------------------------------------ void MysqlQuery::_mysqlGetFieldsInformation() { // Clear any previouse field information just incase. _freeCollection(FIELD_INFORMATION); // Internal method used to build the field information. MYSQL_FIELD *field_prop = NULL; // Allocate the information pointers _numFieldInformation = _fieldCount; _fieldInformation = (BaseFieldDescription**)malloc(_numFieldInformation * sizeof(BaseFieldDescription*)); // Assign the data to the corresponding fields. for (int i=0; i<_numFieldInformation; i++) { field_prop = mysql_fetch_field(__mysql_res__); if (field_prop != NULL) { // Field Name. string fName = field_prop->name; // Work out the field type. FieldType _type = _mysqlResolveFieldType(field_prop->type); // Field Properties. long int _precision = 0; long int _scale = 0; bool _isIncrement = field_prop->flags & AUTO_INCREMENT_FLAG; bool _isPriKey = field_prop->flags & PRI_KEY_FLAG; bool _isUnique = field_prop->flags & UNIQUE_KEY_FLAG; bool _isNotNull = field_prop->flags & NOT_NULL_FLAG; if (_type == FT_DOUBLE) { _precision = field_prop->max_length; //Seems to work for the precision. _scale = field_prop->decimals; } // Add the field properties to the vector. _fieldInformation[i] = new BaseFieldDescription(fName, i, _type, _isIncrement, _isPriKey, _isUnique, _isNotNull); } } } // MysqlQuery::_mysqGetFieldsInformation //------------------------------------------------------------------------------ // MysqlQuery::_mysqlGetResultSetRow //------------------------------------------------------------------------------ void MysqlQuery::_mysqlGetResultSetRow() { // Free the current field values just incase _freeCollection(FIELD_VALUES); // Retrieve the data from the current row. MYSQL_ROW row_data; unsigned long *row_lengths; // Unsigned long as this is how the function is defined try { // Fetch the row and data sizes and advance the cursor with mysql_fetch_row. row_data = mysql_fetch_row(__mysql_res__); row_lengths = mysql_fetch_lengths(__mysql_res__); // Allocate the value pointers _numRecordValues = _fieldCount; _recordValues = (MysqlValue**)malloc(_numRecordValues * sizeof(MysqlValue*)); // Assign the data to the corresponding fields. for (int i=0; i<_numRecordValues; i++) { // Allocate the field _recordValues[i] = new MysqlValue(_fieldInformation[i]->name().c_str()); // Check if the field is NULL if (row_data[i] == NULL) { _recordValues[i]->setNULL(); } else { char* data = (char*)malloc(row_lengths[i] + 1); strncpy(data, row_data[i], row_lengths[i]); if (_fieldInformation[i]->type() == FT_BLOB) { // BLOB type, set it as a Binary field and do not free the memory as the MysqlValue Object will _recordValues[i]->setBinary((void*)data, row_lengths[i]); } else { // Need to first convert the MySQL result to a NULL terminating string data[row_lengths[i]] = '\0'; _recordValues[i]->setString((const char*)data); free((void*)data); } } } } catch(...) { } } // MysqlQuery::_mysqlGetResultSetRow //------------------------------------------------------------------------------ // MysqlQuery::_mysqlResolveFieldType //------------------------------------------------------------------------------ FieldType MysqlQuery::_mysqlResolveFieldType( enum_field_types type) { FieldType res; switch(type) { case FIELD_TYPE_TINY: res = FT_SHORT; break; case FIELD_TYPE_SHORT: res = FT_SHORT; break; case FIELD_TYPE_LONG: res = FT_LONG; break; case FIELD_TYPE_INT24: res = FT_LONG; break; case FIELD_TYPE_LONGLONG: res = FT_LONG; break; case FIELD_TYPE_DECIMAL: res = FT_DOUBLE; break; case FIELD_TYPE_FLOAT: res = FT_DOUBLE; break; case FIELD_TYPE_DOUBLE: res = FT_DOUBLE; break; case FIELD_TYPE_TIMESTAMP: res = FT_DATETIME; break; case FIELD_TYPE_DATE: res = FT_DATETIME; break; case FIELD_TYPE_TIME: res = FT_DATETIME; break; case FIELD_TYPE_DATETIME: res = FT_DATETIME; break; case FIELD_TYPE_YEAR: res = FT_DATETIME; break; case FIELD_TYPE_STRING: res = FT_STRING; break; case FIELD_TYPE_BLOB: res = FT_BLOB; break; case FIELD_TYPE_TINY_BLOB: res = FT_BLOB; break; case FIELD_TYPE_MEDIUM_BLOB: res = FT_BLOB; break; case FIELD_TYPE_LONG_BLOB: res = FT_BLOB; break; case FIELD_TYPE_SET: res = FT_STRING; break; case FIELD_TYPE_ENUM: res = FT_STRING; break; case FIELD_TYPE_NULL: res = FT_NULL; break; case FIELD_TYPE_VAR_STRING: res = FT_STRING; break; default: res = FT_UNKNOWN; break; } return res; } // MysqlQuery::_mysqlResolveFieldType //------------------------------------------------------------------------------ // MysqlQuery::_mysqlParseBindParameters //------------------------------------------------------------------------------ string MysqlQuery::_mysqlParseBindParameters( const string& originalSqlStatement) { // Try and substitute the parameters checking to make sure they exist. string res = originalSqlStatement; int pos; for (int i =0; i<_numParameters; i++) { string paramName = ":" + _parameters[i]->name(); if ((pos = res.find(paramName)) == string::npos) { // We have a parameter that is not present in our sql statement string err = "_mysqlParseBindParameters(): The specified bind parameter, "; err += paramName; err += ", is not present in the SQL statement: "; err += originalSqlStatement; throw BindParameterNotPresent(err); } // Substitute the parameter with the correct value according to the parameter type. res.replace(pos, paramName.length(), _parameters[i]->paramToMySQLString()); } // Make sure there are no parameters left if (((pos = res.find(" :")) != string::npos) || ((pos = res.find("(:")) != string::npos) || ((pos = res.find(",:")) != string::npos) ) { // We have a parameter that has not been set. pos += 1; int pos2 = res.find(" ", pos); int pos3 = res.find(")", pos); int pos4 = res.find(",", pos); if (pos2 == string::npos) pos2 = 65535; if (pos3 == string::npos) pos3 = 65535; if (pos4 == string::npos) pos4 = 65535; int endPos; if (pos2 < pos3 && pos2 < pos4) endPos = pos2; if (pos3 < pos2 && pos3 < pos4) endPos = pos3; if (pos4 < pos2 && pos4 < pos3) endPos = pos4; string unknownParam = res.substr(pos, endPos-pos); string err = "_mysqlParseBindParameters(): The bind parameter, "; err += unknownParam; err += ", in the SQL statement: "; err += originalSqlStatement; err += " has not been set."; throw BindParameterNotSet(err); } return res; } // MysqlQuery::_mysqlParseBindParameters //------------------------------------------------------------------------------ // MysqlQuery::_freeCollection //------------------------------------------------------------------------------ void MysqlQuery::_freeCollection( CollectionType type) { int i; switch (type) { case FIELD_INFORMATION: if (_fieldInformation) { for (i=0; i<_numFieldInformation; i++) { if (_fieldInformation[i]) { delete _fieldInformation[i]; _fieldInformation[i] = NULL; } } free(_fieldInformation); _fieldInformation = NULL; _numFieldInformation = 0; _fieldCount = 0; } break; case FIELD_VALUES: if (_recordValues) { for (i=0; i<_numRecordValues; i++) { if (_recordValues[i]) { delete _recordValues[i]; _recordValues[i] = NULL; } } free(_recordValues); _recordValues = NULL; _numRecordValues = 0; } break; case BIND_PARAMETERS: if (_parameters) { for (i=0; i<_numParameters; i++) { if (_parameters[i]) { delete _parameters[i]; _parameters[i] = NULL; } } free(_parameters); _parameters = NULL; _numParameters = 0; } break; } } // MysqlQuery::_freeCollection // ----------------------------------------------------------------------------- // PUBLIC: // ----------------------------------------------------------------------------- //------------------------------------------------------------------------------ // MysqlQuery::MysqlQuery //------------------------------------------------------------------------------ MysqlQuery::MysqlQuery( MysqlConnection* parentConnection, int index) : _parentConnection(parentConnection), _index(index), _isTransaction(false), __mysql_res__(NULL), _mysqlNumRows(0), _mysqlCurrentRow(0), _recordValues(NULL), _numRecordValues(0), _fieldInformation(NULL), _numFieldInformation(0), _parameters(NULL), _numParameters(0) { } // MysqlQuery::MysqlQuery //------------------------------------------------------------------------------ // MysqlQuery::~MysqlQuery //------------------------------------------------------------------------------ MysqlQuery::~MysqlQuery() { // If there is still a transaction, rollback if (_isTransaction) rollback(); //Make sure any stored query results are freed. if (__mysql_res__ != NULL) { mysql_free_result(__mysql_res__); __mysql_res__ = NULL; } // Free any parameters _freeCollection(BIND_PARAMETERS); // Free the current field values _freeCollection(FIELD_VALUES); // Clear any previouse field information. _freeCollection(FIELD_INFORMATION); // Release this connection from the parent connection object. _parentConnection->releaseQueryConnection(this); } // MysqlQuery::~MysqlQuery //------------------------------------------------------------------------------ // MysqlQuery::clearBindParams //------------------------------------------------------------------------------ void MysqlQuery::clearBindParams() { // Free all the bind parameters. _freeCollection(BIND_PARAMETERS); } // MysqlQuery::clearBindParams //------------------------------------------------------------------------------ // MysqlQuery::command //------------------------------------------------------------------------------ void MysqlQuery::command( const string& sqlStatement) { // Clear any bind parameters as we now have a new query clearBindParams(); // Call the base query to store a copy of the query. BaseQuery::command(sqlStatement); } // MysqlQuery::command //------------------------------------------------------------------------------ // MysqlQuery::bindParam //------------------------------------------------------------------------------ BaseValue* MysqlQuery::bindParam( const string& paramName) { // Make sure the name has not already been added. If it has, return the instance to it for (int i=0; i<_numParameters; i++) if (strcasecmp(_parameters[i]->bindName.c_str(), paramName.c_str()) == 0) return _parameters[i]; // Make sure the parameter is present in the quey if (!_isBindParameterPresent(paramName)) { string err = "bindParam(): The specified bind parameter, "; err += paramName; err += ", is not present in the SQL statement: "; err += _sqlStatement; throw BindParameterNotPresent(err); } // Add the value to the parameters array. _numParameters++; _parameters = (MysqlBindParam**)realloc((void*)_parameters, _numParameters * sizeof(MysqlBindParam*)); _parameters[_numParameters-1] = new MysqlBindParam(paramName); return _parameters[_numParameters-1]; } // MysqlQuery::bindParam //------------------------------------------------------------------------------ // MysqlQuery::execute //------------------------------------------------------------------------------ void MysqlQuery::execute() { //Make sure any stored query results are freed. if (__mysql_res__ != NULL) { mysql_free_result(__mysql_res__); __mysql_res__ = NULL; } // Number of rows in the result set is now 0 _mysqlNumRows = 0; _mysqlCurrentRow = 0; // Clear any previouse field information. _freeCollection(FIELD_INFORMATION); // Free the current field values _freeCollection(FIELD_VALUES); // IIF there is transaction support enabled then // always make sure there is a transaction that the query is occuring in if (_parentConnection->_mysqlOptions.mysqlOptTransaction) if (!_isTransaction) transBegin(); // Resolve the bind parameters string resolvedSqlStatement = _mysqlParseBindParameters(_sqlStatement); // Ping the connection to make sure it is still valid. _parentConnection->_mysqlPing(_index); // Execute the query _parentConnection->_mysqlQuery(_index, resolvedSqlStatement); // Determine if we have any data or not and get the field structures if we do. // Using store result. This should change to be configurable from the config file. __mysql_res__ = mysql_store_result(&_parentConnection->_handles[_index]->__mysql__); if (__mysql_res__ == NULL) { // MySQL returned nothing. Is it an error or a DDL or DML statement. if (mysql_errno(&_parentConnection->_handles[_index]->__mysql__)) { string err = "execute(): "; err += "SQL statement: "; err += _sqlStatement; err += ", "; err += mysql_error(&_parentConnection->_handles[_index]->__mysql__); throw ErrorQuerying(err); } // Nope, its a DDL or DML statement. _fieldCount = 0; _eof = true; } else { // We have data _fieldCount = mysql_num_fields(__mysql_res__); // Get the field information for this query. _mysqlGetFieldsInformation(); // Store the number of rows in the result set. _mysqlNumRows = mysql_num_rows(__mysql_res__); if (!_mysqlNumRows) _eof = true; // No data in result set else _eof = false; } } // MysqlQuery::execute //------------------------------------------------------------------------------ // MysqlQuery::next //------------------------------------------------------------------------------ void MysqlQuery::fetchNext() { // Do nothing if the we have reached the end of file. if (_eof) return; // Get the data. mysql_fetch_row() will advance the cursor and don't need to use mysql_data_seek // mysql_data_seek slows performance on large result sets :( (Thanks to David Wojtowicz for this) _mysqlGetResultSetRow(); _mysqlCurrentRow++; // Check if we are at the end of the result set. if (_mysqlCurrentRow >= _mysqlNumRows) _eof = true; } // MysqlQuery::next //------------------------------------------------------------------------------ // MysqlQuery::transBegin //------------------------------------------------------------------------------ void MysqlQuery::transBegin() { // Only allow transactions if they have been enabled if (_parentConnection->_mysqlOptions.mysqlOptTransaction) { // Transaction support enabled // Make sure a transaction is not already active if (_isTransaction) throw TransactionError("transBegin(): " "A transaction is already active. Commit or rollback the transaction before creating a new transaction."); // Ping the connection to make sure it is still valid. _parentConnection->_mysqlPing(_index); string query = "BEGIN"; if (mysql_real_query(&_parentConnection->_handles[_index]->__mysql__, query.c_str(), query.length()) != 0) { // Something went wrong while trying to execute the sql statement, so throw an exception. string err = "transBegin(): Unable to start transaction"; err += ", "; err += mysql_error(&_parentConnection->_handles[_index]->__mysql__); throw TransactionError(err); } _isTransaction = true; } else { // Transaction support not enabled throw NotImplemented("transBegin(): Transaction support is not enabled. Please enable in .ini file with 'mysql_opt_transaction = yes' entry."); } } // MysqlQuery::transBegin //------------------------------------------------------------------------------ // MysqlQuery::commit //------------------------------------------------------------------------------ void MysqlQuery::commit() { // Only allow transactions if they have been enabled if (_parentConnection->_mysqlOptions.mysqlOptTransaction) { // Transaction support enabled // Make sure a transaction is active if (!_isTransaction) throw TransactionError("commit(): " "A transaction is not active. Create a transaction before calling commit."); // Ping the connection to make sure it is still valid. _parentConnection->_mysqlPing(_index); string query = "COMMIT"; if (mysql_real_query(&_parentConnection->_handles[_index]->__mysql__, query.c_str(), query.length()) != 0) { // Something went wrong while trying to execute the sql statement, so throw an exception. string err = "commit(): Unable to commit the transaction"; err += ", "; err += mysql_error(&_parentConnection->_handles[_index]->__mysql__); throw TransactionError(err); } _isTransaction = false; } else { // Transaction support not enabled throw NotImplemented("commit(): Transaction support is not enabled. Please enable in .ini file with 'mysql_opt_transaction = yes' entry."); } } // MysqlQuery::commit //------------------------------------------------------------------------------ // MysqlQuery::rollback //------------------------------------------------------------------------------ void MysqlQuery::rollback() { // Only allow transactions if they have been enabled if (_parentConnection->_mysqlOptions.mysqlOptTransaction) { // Transaction support enabled // Make sure a transaction is active if (!_isTransaction) throw TransactionError("rollback(): " "A transaction is not active. Create a transaction before calling commit."); // Ping the connection to make sure it is still valid. _parentConnection->_mysqlPing(_index); string query = "ROLLBACK"; if (mysql_real_query(&_parentConnection->_handles[_index]->__mysql__, query.c_str(), query.length()) != 0) { // Something went wrong while trying to execute the sql statement, so throw an exception. string err = "commit(): Unable to rollback the transaction"; err += ", "; err += mysql_error(&_parentConnection->_handles[_index]->__mysql__); throw TransactionError(err); } _isTransaction = false; } else { // Transaction support not enabled throw NotImplemented("rollback(): Transaction support is not enabled. Please enable in .ini file with 'mysql_opt_transaction = yes' entry."); } } // MysqlQuery::rollback //------------------------------------------------------------------------------ // MysqlQuery::getFieldInfoByColumn //------------------------------------------------------------------------------ BaseFieldDescription* MysqlQuery::getFieldInfoByColumn( int index) { // Make sure the index is in range. if (index < 0 || index >= _numFieldInformation) throw IndexOutOfRange("getFieldInfoByColumn(): The field index is out of range for the current result set"); return _fieldInformation[index]; } // MysqlQuery::getFieldInfoByColumn //------------------------------------------------------------------------------ // MysqlQuery::getFieldInfoByName //------------------------------------------------------------------------------ BaseFieldDescription* MysqlQuery::getFieldInfoByName( const string& fieldName) { // Try and find the field name for (int i=0; i<_numFieldInformation; i++) if (strcasecmp(_fieldInformation[i]->name().c_str(), fieldName.c_str()) == 0) return _fieldInformation[i]; throw NameNotFound("getFieldInfoByName(): The field name was not found for the current result set."); } // MysqlQuery::getFieldInfoByName //------------------------------------------------------------------------------ // MysqlQuery::getFieldByColumn //------------------------------------------------------------------------------ BaseValue* MysqlQuery::getFieldByColumn( int index) { // Make sure the index is in range. if (index < 0 || index >= _numRecordValues) { throw IndexOutOfRange("getFieldByColumn(): The field index is out of range for the current result set"); } return _recordValues[index]; } // MysqlQuery::getFieldByColumn //------------------------------------------------------------------------------ // MysqlQuery::getFieldByName //------------------------------------------------------------------------------ BaseValue* MysqlQuery::getFieldByName( const string& fieldName) { // Try and find the field name for (int i=0; i<_numRecordValues; i++) if (strcasecmp(_recordValues[i]->name().c_str(), fieldName.c_str()) == 0) return _recordValues[i]; throw NameNotFound("getFieldByName(): The field name was not found for the current result set."); } // MysqlQuery::getFieldByName