/* * PostgresqQuery object defines the needed query functions for the dbConnect PostgreSQL driver * Copyright (C) 2003 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 "pgsqlQuery.h" #include "pgsqlOID.h" // ----------------------------------------------------------------------------- // PRIVATE: // ----------------------------------------------------------------------------- //------------------------------------------------------------------------------ // PostgresqlQuery::_pgsqGetFieldsInformation //------------------------------------------------------------------------------ void PostgresqlQuery::_pgsqlGetFieldsInformation() { // Clear any previouse field information just incase. _freeCollection(FIELD_INFORMATION); // Internal method used to build the field information. // 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 Name. string fName = PQfname(__pgsql_res__, i); // Work out the field type. FieldType _type = _pgsqlResolveFieldType(PQftype(__pgsql_res__, i)); // Field Properties. long int _precision = 0; long int _scale = 0; bool _isIncrement = false; // Not supported, PostgreSQL uses sequences bool _isPriKey = false; // Not supported bool _isUnique = false; // Not supported bool _isNotNull = false; // Not supported int modData = PQfmod(__pgsql_res__, i); // TODO: Use modData for prescision etc settings // Add the field properties to the vector. _fieldInformation[i] = new BaseFieldDescription(fName, i, _type, _isIncrement, _isPriKey, _isUnique, _isNotNull); } } // PostgresqlQuery::_pgsqGetFieldsInformation //------------------------------------------------------------------------------ // PostgresqlQuery::_pgsqlGetResultSetRow //------------------------------------------------------------------------------ void PostgresqlQuery::_pgsqlGetResultSetRow() { // Free the current field values just incase _freeCollection(FIELD_VALUES); // Retrieve the data from the current row. char* value = NULL; try { // Allocate the value pointers _numRecordValues = _fieldCount; _recordValues = (PostgresqlValue**)malloc(_numRecordValues * sizeof(PostgresqlValue*)); // Assign the data to the corresponding fields. for (int i=0; i<_numRecordValues; i++) { Oid type = PQftype(__pgsql_res__, i); _recordValues[i] = new PostgresqlValue(_fieldInformation[i]->name().c_str(), type); // Check if the field is NULL if (PQgetisnull(__pgsql_res__, _pgsqlCurrentRow, i)) { _recordValues[i]->setNULL(); } else { // Get the actual PostgreSQL value. The value is already a NULL terminated string and is managed by the result value = PQgetvalue(__pgsql_res__, _pgsqlCurrentRow, i); // Check if this is a BYTEA type field and convert if necessary if (type == PG_TYPE_BYTEA) { // Convert the value to the proper binary representation // **NB: The memory allocated is required to be freed by the PostgresqlValue object size_t binValSize = 0; unsigned char *binVal = PQunescapeBytea((unsigned char*)value, &binValSize); // The destructor of PostgresqlValue will free binVal _recordValues[i]->setBinary((void*)binVal, binValSize); } else { _recordValues[i]->setString((const char*)value); } value = NULL; } } } catch(...) { } } // PostgresqlQuery::_pgsqlGetResultSetRow //------------------------------------------------------------------------------ // PostgresqlQuery::_pgsqlResolveFieldType //------------------------------------------------------------------------------ FieldType PostgresqlQuery::_pgsqlResolveFieldType( Oid type) { FieldType res; switch(type) { case PG_TYPE_BOOL: res = FT_BOOLEAN; break; case PG_TYPE_CHAR: res = FT_STRING; break; case PG_TYPE_NAME: res = FT_STRING; break; case PG_TYPE_INT8: res = FT_LONG; break; case PG_TYPE_INT2: res = FT_SHORT; break; case PG_TYPE_INT4: res = FT_LONG; break; case PG_TYPE_TEXT: res = FT_STRING; break; case PG_TYPE_OID: res = FT_LONG; break; case PG_TYPE_FLOAT4: res = FT_DOUBLE; break; case PG_TYPE_FLOAT8: res = FT_DOUBLE; break; case PG_TYPE_ABSTIME: res = FT_DATETIME; break; case PG_TYPE_RELTIME: res = FT_DATETIME; break; case PG_TYPE_UNKNOWN: res = FT_UNKNOWN; break; case PG_TYPE_MONEY: res = FT_DOUBLE; break; case PG_TYPE_MACADDR: res = FT_STRING; break; case PG_TYPE_INET: res = FT_STRING; break; case PG_TYPE_CIDR: res = FT_STRING; break; case PG_TYPE_BPCHAR: res = FT_STRING; break; case PG_TYPE_VARCHAR: res = FT_STRING; break; case PG_TYPE_DATE: res = FT_DATETIME; break; case PG_TYPE_TIME: res = FT_DATETIME; break; case PG_TYPE_TIMESTAMP: res = FT_DATETIME; break; case PG_TYPE_TIMESTAMPTZ: res = FT_DATETIME; break; case PG_TYPE_TIMETZ: res = FT_DATETIME; break; case PG_TYPE_NUMERIC: res = FT_DOUBLE; break; case PG_TYPE_PATH: res = FT_STRING; break; case PG_TYPE_BYTEA: res = FT_BLOB; break; case PG_TYPE_BIT: res = FT_STRING; break; case PG_TYPE_VARBIT: res = FT_STRING; break; default: res = FT_UNKNOWN; break; } return res; } // PostgresqlQuery::_pgsqlResolveFieldType //------------------------------------------------------------------------------ // PostgresqlQuery::_pgsqlParseBindParameters //------------------------------------------------------------------------------ string PostgresqlQuery::_pgsqlParseBindParameters( 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 = "_pgsqlParseBindParameters(): 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]->paramToPostgreSQLString()); } // 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 = "_pgsqlParseBindParameters(): The bind parameter, "; err += unknownParam; err += " in the SQL statement: "; err += originalSqlStatement; err += " has not been set."; throw BindParameterNotSet(err); } return res; } // PostgresqlQuery::_pgsqlParseBindParameters //------------------------------------------------------------------------------ // PostgresqlQuery::_pgsqlParseFunctionCall //------------------------------------------------------------------------------ string PostgresqlQuery::_pgsqlParseFunctionCall( const string& originalSqlStatement) { // Check if this is a function call. Only a single token is present in the SQL if it is // Check for only one token by looking for any spaces if (originalSqlStatement.find(" ") == string::npos) { // Is a function, build the correct SQL with any bind parameters // Lowercase the function as all Posgresql creates functions lower cased string lFunc = originalSqlStatement; for (int i=0; i_pgsqlPing(_index); // Determine the number of function parameters using the Postgresql catalogue string sqlQuery = "SELECT pg_proc.pronargs FROM pg_proc WHERE pg_proc.proname = '" + lFunc + "'"; PGresult *r = PQexec(_parentConnection->_handles[_index]->__conn, sqlQuery.c_str()); if (!r || PQresultStatus(r) == PGRES_BAD_RESPONSE || PQresultStatus(r) == PGRES_FATAL_ERROR) { string err = "_pgsqlParseFunctionCall(): Unable to determine number of function parameters"; if (r) { err += ", "; err += PQresultErrorMessage(r); PQclear(r); } throw CommandError(err); } // Make sure the function exists if (PQresultStatus(r) == PGRES_COMMAND_OK || PQntuples(r) == 0) { string err = "_pgsqlParseFunctionCall(): The function '"; err += originalSqlStatement; err += "' does not exist in the server catalogue."; if (r) PQclear(r); throw CommandError(err); } // Get the result and convert it to an integer long numFuncParameters = atol(PQgetvalue(r, 0, 0)); PQclear(r); // Build in form "SELECT function(:param1, :paramX) AS _fnc_reslt_" string functionCallSQL = "SELECT "; functionCallSQL += lFunc; functionCallSQL += "("; // Add any parameters given if required // NOTE: The first param, ie param 0 is reserved for any results if (numFuncParameters > 1) { // Always add the first param. functionCallSQL += ":param1"; // Iterate throught the other parameters and add with a leading , char buf[6]; for (int j=2; j<=numFuncParameters; j++) { functionCallSQL += ",:param"; memset(buf, 0, sizeof(buf)); sprintf(buf, "%d", j); functionCallSQL += buf; } } functionCallSQL += ") AS result"; // The result will always be in the field _fnc_reslt_; _isFunctionQuery = true; return functionCallSQL; } else { // Nope, not a function, return the original query _isFunctionQuery = false; return originalSqlStatement; } } // PostgresqlQuery::_pgsqlParseFunctionCall //------------------------------------------------------------------------------ // PostgresqlQuery::_freeCollection //------------------------------------------------------------------------------ void PostgresqlQuery::_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; } } // PostgresqlQuery::_freeCollection // ----------------------------------------------------------------------------- // PUBLIC: // ----------------------------------------------------------------------------- //------------------------------------------------------------------------------ // PostgresqlQuery::PostgresqlQuery //------------------------------------------------------------------------------ PostgresqlQuery::PostgresqlQuery( PostgresqlConnection* parentConnection, int index) : _parentConnection(parentConnection), _index(index), _isTransaction(false), __pgsql_res__(NULL), _pgsqlNumRows(0), _pgsqlCurrentRow(0), _recordValues(NULL), _numRecordValues(0), _fieldInformation(NULL), _numFieldInformation(0), _parameters(NULL), _numParameters(0) { } // PostgresqlQuery::PostgresqlQuery //------------------------------------------------------------------------------ // PostgresqlQuery::~PostgresqlQuery //------------------------------------------------------------------------------ PostgresqlQuery::~PostgresqlQuery() { // If there is still a transaction, rollback if (_isTransaction) rollback(); //Make sure any stored query results are freed. if (__pgsql_res__ != NULL) { PQclear(__pgsql_res__); __pgsql_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); } // PostgresqlQuery::~PostgresqlQuery //------------------------------------------------------------------------------ // PostgresqlQuery::clearBindParams //------------------------------------------------------------------------------ void PostgresqlQuery::clearBindParams() { // Free all the bind parameters. _freeCollection(BIND_PARAMETERS); } // PostgresqlQuery::clearBindParams //------------------------------------------------------------------------------ // PostgresqlQuery::command //------------------------------------------------------------------------------ void PostgresqlQuery::command( const string& sqlStatement) { // Clear any bind parameters as we now have a new query clearBindParams(); // Check if this is a function been called and build the correct sql if it is string newSqlStatement = _pgsqlParseFunctionCall(sqlStatement); // Call the base query to store a copy of the query. BaseQuery::command(newSqlStatement); } // PostgresqlQuery::command //------------------------------------------------------------------------------ // PostgresqlQuery::bindParam //------------------------------------------------------------------------------ BaseValue* PostgresqlQuery::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 bind param is not reserved if (strcasecmp(paramName.c_str(), "result") == 0) { string err = "bindParam(): The specified bind parameter, "; err += paramName; err += ", is a reserved name"; throw BindParameterError(err); } // 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 = (PostgresqlBindParam**)realloc((void*)_parameters, _numParameters * sizeof(PostgresqlBindParam*)); _parameters[_numParameters-1] = new PostgresqlBindParam(paramName); return _parameters[_numParameters-1]; } // PostgresqlQuery::bindParam //------------------------------------------------------------------------------ // PostgresqlQuery::execute //------------------------------------------------------------------------------ void PostgresqlQuery::execute() { // Make sure any stored query results are freed. if (__pgsql_res__ != NULL) { PQclear(__pgsql_res__); __pgsql_res__ = NULL; } // Number of rows in the result set is now 0 _pgsqlNumRows = 0; _pgsqlCurrentRow = 0; // Clear any previouse field information. _freeCollection(FIELD_INFORMATION); // Free the current field values _freeCollection(FIELD_VALUES); // Always make sure there is a transaction that the query is occuring in if (!_isTransaction) transBegin(); // Resolve the bind parameters string resolvedSqlStatement = _pgsqlParseBindParameters(_sqlStatement); // Ping the connection to make sure it is still valid. _parentConnection->_pgsqlPing(_index); // Execute the query __pgsql_res__ = _parentConnection->_pgsqlQuery(_index, resolvedSqlStatement); // Setup the result with the number of records and field names if any if (PQresultStatus(__pgsql_res__) == PGRES_COMMAND_OK) { // Executed query, but no possible data can be returned (Ie DDL statement) _fieldCount = 0; _eof = true; } else { // We have data _fieldCount = PQnfields(__pgsql_res__); // Get the field information for this query. _pgsqlGetFieldsInformation(); // Store the number of rows in the result set. _pgsqlNumRows = PQntuples(__pgsql_res__); if (!_pgsqlNumRows) _eof = true; // No data in result set else _eof = false; } // If this was a function call, get the result and populate the result param if (_isFunctionQuery) { // The function result is in the special field _fnc_reslt_, // which is the first and only field in the single row result set fetchNext(); // Add the value to the parameters array. _numParameters++; _parameters = (PostgresqlBindParam**)realloc((void*)_parameters, _numParameters * sizeof(PostgresqlBindParam*)); _parameters[_numParameters-1] = (PostgresqlBindParam*)_recordValues[0]; _recordValues[0] = NULL; _eof = true; // Clear the field information _freeCollection(FIELD_INFORMATION); // Free the field values _freeCollection(FIELD_VALUES); } } // PostgresqlQuery::execute //------------------------------------------------------------------------------ // PostgresqlQuery::next //------------------------------------------------------------------------------ void PostgresqlQuery::fetchNext() { // Do nothing if the we have reached the end of file. if (_eof) return; // Get the data for the row _pgsqlGetResultSetRow(); _pgsqlCurrentRow++; // Check if we are at the end of the result set. if (_pgsqlCurrentRow >= _pgsqlNumRows) _eof = true; } // PostgresqlQuery::next //------------------------------------------------------------------------------ // PostgresqlQuery::transBegin //------------------------------------------------------------------------------ void PostgresqlQuery::transBegin() { // 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->_pgsqlPing(_index); // Start the transaction PGresult *r = PQexec(_parentConnection->_handles[_index]->__conn, "BEGIN"); if (!r || PQresultStatus(r) == PGRES_BAD_RESPONSE || PQresultStatus(r) == PGRES_FATAL_ERROR) { string err = "transBegin(): Unable to start transaction"; if (r) { err += ", "; err += PQresultErrorMessage(r); PQclear(r); } throw TransactionError(err); } // The transaction has active PQclear(r); _isTransaction = true; } // PostgresqlQuery::transBegin //------------------------------------------------------------------------------ // PostgresqlQuery::commit //------------------------------------------------------------------------------ void PostgresqlQuery::commit() { // 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->_pgsqlPing(_index); // Commit the transaction PGresult *r = PQexec(_parentConnection->_handles[_index]->__conn, "COMMIT"); if (!r || PQresultStatus(r) == PGRES_BAD_RESPONSE || PQresultStatus(r) == PGRES_FATAL_ERROR) { string err = "commit(): Unable to commit the transaction"; if (r) { err += ", "; err += PQresultErrorMessage(r); PQclear(r); } throw TransactionError(err); } // The transaction has been commited PQclear(r); _isTransaction = false; } // PostgresqlQuery::commit //------------------------------------------------------------------------------ // PostgresqlQuery::rollback //------------------------------------------------------------------------------ void PostgresqlQuery::rollback() { // 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->_pgsqlPing(_index); // Commit the transaction PGresult *r = PQexec(_parentConnection->_handles[_index]->__conn, "ROLLBACK"); if (!r || PQresultStatus(r) == PGRES_BAD_RESPONSE || PQresultStatus(r) == PGRES_FATAL_ERROR) { string err = "rollback(): Unable to rollback the transaction"; if (r) { err += ", "; err += PQresultErrorMessage(r); PQclear(r); } throw TransactionError(err); } // The transaction has been rolled back PQclear(r); _isTransaction = false; } // PostgresqlQuery::rollback //------------------------------------------------------------------------------ // PostgresqlQuery::getFieldInfoByColumn //------------------------------------------------------------------------------ BaseFieldDescription* PostgresqlQuery::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]; } // PostgresqlQuery::getFieldInfoByColumn //------------------------------------------------------------------------------ // PostgresqlQuery::getFieldInfoByName //------------------------------------------------------------------------------ BaseFieldDescription* PostgresqlQuery::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."); } // PostgresqlQuery::getFieldInfoByName //------------------------------------------------------------------------------ // PostgresqlQuery::getFieldByColumn //------------------------------------------------------------------------------ BaseValue* PostgresqlQuery::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]; } // PostgresqlQuery::getFieldByColumn //------------------------------------------------------------------------------ // PostgresqlQuery::getFieldByName //------------------------------------------------------------------------------ BaseValue* PostgresqlQuery::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."); } // PostgresqlQuery::getFieldByName