/* * MsqlQuery object defines the needed query functions for the dbConnect mSQL driver * Copyright (C) 2003 Johnathan Ingram, jingram@rogue-order.net * * 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 "msqlQuery.h" #include #include #include "dbconnectTypes.h" // ----------------------------------------------------------------------------- // PRIVATE: // ----------------------------------------------------------------------------- // Thread mutexes. mSQL is not thread safe at all and requires to be locked by // all objects static SimpleThread_Mutex queryAndStoreResultMutex; //------------------------------------------------------------------------------ // MsqlQuery::_msqGetFieldsInformation //------------------------------------------------------------------------------ void MsqlQuery::_msqlGetFieldsInformation() { // Clear any previouse field information just incase. _freeCollection(FIELD_INFORMATION); // Internal method used to build the field information. m_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 = msqlFetchField(_result); if (field_prop != NULL) { // Field Name. string fName = field_prop->name; // Work out the field type. FieldType _type = _msqlResolveFieldType(field_prop->type); // Field Properties. long int _precision = 0; // Not supported in mSQL long int _scale = 0; // Not supported in mSQL bool _isIncrement = false; // Not supported in mSQL bool _isPriKey = false; // Not supported in mSQL bool _isUnique = IS_UNIQUE(field_prop->flags); bool _isNotNull = IS_NOT_NULL(field_prop->flags); // Add the field properties to the vector. _fieldInformation[i] = new BaseFieldDescription(fName, i, _type, _isIncrement, _isPriKey, _isUnique, _isNotNull); } } } // MsqlQuery::_msqGetFieldsInformation //------------------------------------------------------------------------------ // MsqlQuery::_msqlGetResultSetRow //------------------------------------------------------------------------------ void MsqlQuery::_msqlGetResultSetRow() { // Free the current field values just incase _freeCollection(FIELD_VALUES); // Retrieve the data from the current row. m_row rowData; unsigned long *row_lengths; // Unsigned long as this is how the function is defined try { // Fetch the row and advance the cursor with msqlFetchRow. rowData = msqlFetchRow(_result); // Allocate the value pointers _numRecordValues = _fieldCount; _recordValues = (BaseValue**)malloc(_numRecordValues * sizeof(BaseValue*)); // Assign the data to the corresponding fields. for (int i=0; i<_numRecordValues; i++) { _recordValues[i] = new BaseValue(_fieldInformation[i]->name().c_str()); // Check if the field is NULL if (rowData[i] == NULL) { _recordValues[i]->setNULL(); } else { // mSQL result is a NULL terminated string _recordValues[i]->setString((const char*)rowData[i]); } } } catch(...) { } } // MsqlQuery::_msqlGetResultSetRow //------------------------------------------------------------------------------ // MsqlQuery::_msqlResolveFieldType //------------------------------------------------------------------------------ FieldType MsqlQuery::_msqlResolveFieldType( int type) { FieldType res; switch(type) { case INT_TYPE: res = FT_LONG; break; case CHAR_TYPE: res = FT_STRING; break; case REAL_TYPE: res = FT_DOUBLE; break; case TEXT_TYPE: // Keep an eye on this mapping res = FT_BLOB; break; case DATE_TYPE: res = FT_DATETIME; break; case UINT_TYPE: res = FT_UNSIGNED_LONG; break; case MONEY_TYPE: res = FT_DOUBLE; break; case TIME_TYPE: res = FT_DATETIME; break; case IPV4_TYPE: res = FT_STRING; break; case INT64_TYPE: res = FT_LONG; break; case UINT64_TYPE: res = FT_UNSIGNED_LONG; break; case INT8_TYPE: res = FT_SHORT; break; case INT16_TYPE: res = FT_SHORT; break; case UINT8_TYPE: res = FT_UNSIGNED_SHORT; break; case UINT16_TYPE: res = FT_UNSIGNED_SHORT; break; default: res = FT_UNKNOWN; break; } return res; } // MsqlQuery::_msqlResolveFieldType //------------------------------------------------------------------------------ // MsqlQuery::_msqlParseBindParameters //------------------------------------------------------------------------------ string MsqlQuery::_msqlParseBindParameters( 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 = "_msqlParseBindParameters(): 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]->paramToMSQLString()); } // 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 = "_msqlParseBindParameters(): The bind parameter, "; err += unknownParam; err += ", in the SQL statement: "; err += originalSqlStatement; err += " has not been set."; throw BindParameterNotSet(err); } return res; } // MsqlQuery::_msqlParseBindParameters //------------------------------------------------------------------------------ // MsqlQuery::_freeCollection //------------------------------------------------------------------------------ void MsqlQuery::_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; } } // MsqlQuery::_freeCollection // ----------------------------------------------------------------------------- // PUBLIC: // ----------------------------------------------------------------------------- //------------------------------------------------------------------------------ // MsqlQuery::MsqlQuery //------------------------------------------------------------------------------ MsqlQuery::MsqlQuery( MsqlConnection* parentConnection, int index) : _parentConnection(parentConnection), _index(index), _result(NULL), _msqlNumRows(0), _msqlCurrentRow(0), _recordValues(NULL), _numRecordValues(0), _fieldInformation(NULL), _numFieldInformation(0), _parameters(NULL), _numParameters(0) { } // MsqlQuery::MsqlQuery //------------------------------------------------------------------------------ // MsqlQuery::~MsqlQuery //------------------------------------------------------------------------------ MsqlQuery::~MsqlQuery() { // Would need to rollback if transactions where supported //Make sure any stored query results are freed. if (_result != NULL) { msqlFreeResult(_result); _result = 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); } // MsqlQuery::~MsqlQuery //------------------------------------------------------------------------------ // MsqlQuery::clearBindParams //------------------------------------------------------------------------------ void MsqlQuery::clearBindParams() { // Free all the bind parameters. _freeCollection(BIND_PARAMETERS); } // MsqlQuery::clearBindParams //------------------------------------------------------------------------------ // MsqlQuery::command //------------------------------------------------------------------------------ void MsqlQuery::command( const string& sqlStatement) { // Call the base query to store a copy of the query. BaseQuery::command(sqlStatement); // Clear any bind parameters as we now have a new query clearBindParams(); } // MsqlQuery::command //------------------------------------------------------------------------------ // MsqlQuery::bindParam //------------------------------------------------------------------------------ BaseValue* MsqlQuery::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 = (MsqlBindParam**)realloc((void*)_parameters, _numParameters * sizeof(MsqlBindParam*)); _parameters[_numParameters-1] = new MsqlBindParam(paramName); return _parameters[_numParameters-1]; } // MsqlQuery::bindParam //------------------------------------------------------------------------------ // MsqlQuery::execute //------------------------------------------------------------------------------ void MsqlQuery::execute() { //Make sure any stored query results are freed. if (_result != NULL) { msqlFreeResult(_result); _result = NULL; } // Number of rows in the result set is now 0 _msqlNumRows = 0; _msqlCurrentRow = 0; // Clear any previouse field information. _freeCollection(FIELD_INFORMATION); // Free the current field values _freeCollection(FIELD_VALUES); // Resolve the bind parameters string resolvedSqlStatement = _msqlParseBindParameters(_sqlStatement); // Ping the connection to make sure it is still valid. _parentConnection->_msqlPing(_index); // Execute the query // Lock the static mutex. Need to query and store results in a single thread at a time // Damn the mSQL API is really really not thread safe at all. SimpleThread_Synchronize sync(queryAndStoreResultMutex); _parentConnection->_msqlQuery(_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. _result = msqlStoreResult(); if (( _msqlNumRows = msqlNumRows(_result)) == 0) { // Nope, its a DML statement. _fieldCount = 0; _eof = true; } else { // We have data _fieldCount = msqlNumFields(_result); // Get the field information for this query. _msqlGetFieldsInformation(); _eof = false; } } // MsqlQuery::execute //------------------------------------------------------------------------------ // MsqlQuery::next //------------------------------------------------------------------------------ void MsqlQuery::fetchNext() { // Do nothing if the we have reached the end of file. if (_eof) return; _msqlGetResultSetRow(); _msqlCurrentRow++; // Check if we are at the end of the result set. if (_msqlCurrentRow >= _msqlNumRows) _eof = true; } // MsqlQuery::next //------------------------------------------------------------------------------ // MsqlQuery::transBegin //------------------------------------------------------------------------------ void MsqlQuery::transBegin() { throw NotImplemented("transBegin(): The transBegin function is not implemented in the mSQL driver."); } // MsqlQuery::transBegin //------------------------------------------------------------------------------ // MsqlQuery::commit //------------------------------------------------------------------------------ void MsqlQuery::commit() { throw NotImplemented("commit(): The commit function is not implemented in the mSQL driver."); } // MsqlQuery::commit //------------------------------------------------------------------------------ // MsqlQuery::rollback //------------------------------------------------------------------------------ void MsqlQuery::rollback() { throw NotImplemented("rollback(): The rollback function is not implemented in the mSQL driver."); } // MsqlQuery::rollback //------------------------------------------------------------------------------ // MsqlQuery::getFieldInfoByColumn //------------------------------------------------------------------------------ BaseFieldDescription* MsqlQuery::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]; } // MsqlQuery::getFieldInfoByColumn //------------------------------------------------------------------------------ // MsqlQuery::getFieldInfoByName //------------------------------------------------------------------------------ BaseFieldDescription* MsqlQuery::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."); } // MsqlQuery::getFieldInfoByName //------------------------------------------------------------------------------ // MsqlQuery::getFieldByColumn //------------------------------------------------------------------------------ BaseValue* MsqlQuery::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]; } // MsqlQuery::getFieldByColumn //------------------------------------------------------------------------------ // MsqlQuery::getFieldByName //------------------------------------------------------------------------------ BaseValue* MsqlQuery::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."); } // MsqlQuery::getFieldByName