X-Git-Url: https://git.saurik.com/wxWidgets.git/blobdiff_plain/a1ee9ea29c3a00779d97be65b9f99e8f644b9a1a..42b1f941b010bee7e5e82b12c1096dce094553d4:/src/common/dbtable.cpp diff --git a/src/common/dbtable.cpp b/src/common/dbtable.cpp index 5a888dec03..de3b913141 100644 --- a/src/common/dbtable.cpp +++ b/src/common/dbtable.cpp @@ -1,13 +1,15 @@ /////////////////////////////////////////////////////////////////////////////// -// Name: table.cpp -// Purpose: Implementation of the wxTable class. +// Name: dbtable.cpp +// Purpose: Implementation of the wxDbTable class. // Author: Doug Card -// Modified by: +// Modified by: George Tasker +// Bart Jourquin +// Mark Johnson // Created: 9.96 // RCS-ID: $Id$ // Copyright: (c) 1996 Remstar International, Inc. // Licence: wxWindows licence, plus: -// Notice: This class library and its intellectual design are free of charge for use, +// Notice: This class library and its intellectual design are free of charge for use, // modification, enhancement, debugging under the following conditions: // 1) These classes may only be used as part of the implementation of a // wxWindows-based application @@ -23,1423 +25,2319 @@ // SYNOPSIS STOP */ -/* -#ifdef _CONSOLE - #include +// Use this line for wxWindows v1.x +//#include "wx_ver.h" +// Use this line for wxWindows v2.x +#include "wx/wxprec.h" +#include "wx/version.h" + +#if wxMAJOR_VERSION == 2 + #ifdef __GNUG__ + #pragma implementation "dbtable.h" + #endif +#endif + +#ifdef DBDEBUG_CONSOLE + #include "wx/ioswrap.h" #endif -*/ -#include "wx/wxprec.h" #ifdef __BORLANDC__ - #pragma hdrstop + #pragma hdrstop #endif //__BORLANDC__ -#ifndef WX_PRECOMP - #include -#endif //WX_PRECOMP +#if wxMAJOR_VERSION == 2 + #ifndef WX_PRECOMP + #include "wx/string.h" + #include "wx/object.h" + #include "wx/list.h" + #include "wx/utils.h" + #include "wx/msgdlg.h" + #include "wx/log.h" + #endif + #include "wx/filefn.h" +#endif -#if wxUSE_ODBC +#if wxMAJOR_VERSION == 1 +# if defined(wx_msw) || defined(wx_x) +# ifdef WX_PRECOMP +# include "wx_prec.h" +# else +# include "wx.h" +# endif +# endif +# define wxUSE_ODBC 1 +#endif -#include + +#if wxUSE_ODBC #include #include #include +#include + +#if wxMAJOR_VERSION == 1 + #include "table.h" +#elif wxMAJOR_VERSION == 2 + #include "wx/dbtable.h" +#endif -#ifdef __WXUNIX__ +#ifdef __UNIX__ // The HPUX preprocessor lines below were commented out on 8/20/97 // because macros.h currently redefines DEBUG and is unneeded. // # ifdef HPUX // # include // # endif -# ifdef __WXLINUX__ +# ifdef LINUX # include # endif #endif -/********** wxTable::wxTable() **********/ -wxTable::wxTable(wxDB *pwxDB, const char *tblName, const int nCols, const char *qryTblName) +ULONG lastTableID = 0; + + +#ifdef __WXDEBUG__ + wxList TablesInUse; +#endif + + +/********** wxDbTable::wxDbTable() **********/ +wxDbTable::wxDbTable(wxDb *pwxDb, const char *tblName, const int nCols, + const char *qryTblName, bool qryOnly, const char *tblPath) { - // Assign member variables - pDb = pwxDB; // Pointer to the wxDB object - strcpy(tableName, tblName); // Table Name - if (qryTblName) // Name of the table/view to query - strcpy(queryTableName, qryTblName); - else - strcpy(queryTableName, tblName); - - noCols = nCols; // No. of cols in the table - where = 0; // Where clause - orderBy = 0; // Order By clause - selectForUpdate = FALSE; // SELECT ... FOR UPDATE; Indicates whether to include the FOR UPDATE phrase - - // Grab the HENV and HDBC from the wxDB object - henv = pDb->henv; - hdbc = pDb->hdbc; - - // Allocate space for column definitions - if (noCols) - colDefs = new CcolDef[noCols]; // Points to the first column defintion - else - colDefs = 0; - - // Allocate statement handles for the table - if (SQLAllocStmt(hdbc, &c0) != SQL_SUCCESS) - pDb->DispAllErrors(henv, hdbc); - if (SQLAllocStmt(hdbc, &c1) != SQL_SUCCESS) - pDb->DispAllErrors(henv, hdbc); - if (SQLAllocStmt(hdbc, &c2) != SQL_SUCCESS) - pDb->DispAllErrors(henv, hdbc); -// if (SQLAllocStmt(hdbc, &c3) != SQL_SUCCESS) -// pDb->DispAllErrors(henv, hdbc); -// if (SQLAllocStmt(hdbc, &c4) != SQL_SUCCESS) -// pDb->DispAllErrors(henv, hdbc); -// if (SQLAllocStmt(hdbc, &c5) != SQL_SUCCESS) -// pDb->DispAllErrors(henv, hdbc); - // Allocate a separate statement handle for performing inserts - if (SQLAllocStmt(hdbc, &hstmtInsert) != SQL_SUCCESS) - pDb->DispAllErrors(henv, hdbc); - // Allocate a separate statement handle for performing deletes - if (SQLAllocStmt(hdbc, &hstmtDelete) != SQL_SUCCESS) - pDb->DispAllErrors(henv, hdbc); - // Allocate a separate statement handle for performing updates - if (SQLAllocStmt(hdbc, &hstmtUpdate) != SQL_SUCCESS) - pDb->DispAllErrors(henv, hdbc); - // Allocate a separate statement handle for performing count(*) function - if (SQLAllocStmt(hdbc, &hstmtCount) != SQL_SUCCESS) - pDb->DispAllErrors(henv, hdbc); - - // Set the cursor type for the statement handles - UDWORD cursorType = SQL_CURSOR_STATIC; - if (SQLSetStmtOption(c1, SQL_CURSOR_TYPE, cursorType) != SQL_SUCCESS) - { - // Check to see if cursor type is supported - pDb->GetNextError(henv, hdbc, c1); - if (! strcmp(pDb->sqlState, "01S02")) // Option Value Changed - { - // Datasource does not support static cursors. Driver - // will substitute a cursor type. Call SQLGetStmtOption() - // to determine which cursor type was selected. - if (SQLGetStmtOption(c1, SQL_CURSOR_TYPE, &cursorType) != SQL_SUCCESS) - pDb->DispAllErrors(henv, hdbc, c1); -#ifdef _CONSOLE - cout << "Static cursor changed to: "; - switch(cursorType) - { - case SQL_CURSOR_FORWARD_ONLY: - cout << "Forward Only"; break; - case SQL_CURSOR_STATIC: - cout << "Static"; break; - case SQL_CURSOR_KEYSET_DRIVEN: - cout << "Keyset Driven"; break; - case SQL_CURSOR_DYNAMIC: - cout << "Dynamic"; break; - } - cout << endl << endl; + pDb = pwxDb; // Pointer to the wxDb object + henv = 0; + hdbc = 0; + hstmt = 0; + hstmtDefault = 0; // Initialized below + hstmtCount = 0; // Initialized first time it is needed + hstmtInsert = 0; + hstmtDelete = 0; + hstmtUpdate = 0; + hstmtInternal = 0; + colDefs = 0; + tableID = 0; + noCols = nCols; // No. of cols in the table + where = ""; // Where clause + orderBy = ""; // Order By clause + from = ""; // From clause + selectForUpdate = FALSE; // SELECT ... FOR UPDATE; Indicates whether to include the FOR UPDATE phrase + queryOnly = qryOnly; + insertable = TRUE; + wxStrcpy(tablePath,""); + wxStrcpy(tableName,""); + wxStrcpy(queryTableName,""); + + assert (tblName); + + wxStrcpy(tableName, tblName); // Table Name + if (tblPath) + wxStrcpy(tablePath, tblPath); // Table Path - used for dBase files + else + tablePath[0] = 0; + + if (qryTblName) // Name of the table/view to query + wxStrcpy(queryTableName, qryTblName); + else + wxStrcpy(queryTableName, tblName); + + if (!pDb) + return; + + pDb->incrementTableCount(); + + wxString s; + tableID = ++lastTableID; + s.sprintf("wxDbTable constructor (%-20s) tableID:[%6lu] pDb:[%p]", tblName,tableID,pDb); + +#ifdef __WXDEBUG__ + wxTablesInUse *tableInUse; + tableInUse = new wxTablesInUse(); + tableInUse->tableName = tblName; + tableInUse->tableID = tableID; + tableInUse->pDb = pDb; + TablesInUse.Append(tableInUse); #endif - } - else - { - pDb->DispNextError(); - pDb->DispAllErrors(henv, hdbc, c1); - } - } -#ifdef _CONSOLE - else - cout << "Cursor Type set to STATIC" << endl << endl; + + pDb->WriteSqlLog(s.c_str()); + + // Grab the HENV and HDBC from the wxDb object + henv = pDb->GetHENV(); + hdbc = pDb->GetHDBC(); + + // Allocate space for column definitions + if (noCols) + colDefs = new wxDbColDef[noCols]; // Points to the first column defintion + + // Allocate statement handles for the table + if (!queryOnly) + { + // Allocate a separate statement handle for performing inserts + if (SQLAllocStmt(hdbc, &hstmtInsert) != SQL_SUCCESS) + pDb->DispAllErrors(henv, hdbc); + // Allocate a separate statement handle for performing deletes + if (SQLAllocStmt(hdbc, &hstmtDelete) != SQL_SUCCESS) + pDb->DispAllErrors(henv, hdbc); + // Allocate a separate statement handle for performing updates + if (SQLAllocStmt(hdbc, &hstmtUpdate) != SQL_SUCCESS) + pDb->DispAllErrors(henv, hdbc); + } + // Allocate a separate statement handle for internal use + if (SQLAllocStmt(hdbc, &hstmtInternal) != SQL_SUCCESS) + pDb->DispAllErrors(henv, hdbc); + + // Set the cursor type for the statement handles + cursorType = SQL_CURSOR_STATIC; + + if (SQLSetStmtOption(hstmtInternal, SQL_CURSOR_TYPE, cursorType) != SQL_SUCCESS) + { + // Check to see if cursor type is supported + pDb->GetNextError(henv, hdbc, hstmtInternal); + if (! wxStrcmp(pDb->sqlState, "01S02")) // Option Value Changed + { + // Datasource does not support static cursors. Driver + // will substitute a cursor type. Call SQLGetStmtOption() + // to determine which cursor type was selected. + if (SQLGetStmtOption(hstmtInternal, SQL_CURSOR_TYPE, &cursorType) != SQL_SUCCESS) + pDb->DispAllErrors(henv, hdbc, hstmtInternal); +#ifdef DBDEBUG_CONSOLE + cout << "Static cursor changed to: "; + switch(cursorType) + { + case SQL_CURSOR_FORWARD_ONLY: + cout << "Forward Only"; + break; + case SQL_CURSOR_STATIC: + cout << "Static"; + break; + case SQL_CURSOR_KEYSET_DRIVEN: + cout << "Keyset Driven"; + break; + case SQL_CURSOR_DYNAMIC: + cout << "Dynamic"; + break; + } + cout << endl << endl; #endif + // BJO20000425 + if (pDb->FwdOnlyCursors() && cursorType != SQL_CURSOR_FORWARD_ONLY) + { + // Force the use of a forward only cursor... + cursorType = SQL_CURSOR_FORWARD_ONLY; + if (SQLSetStmtOption(hstmtInternal, SQL_CURSOR_TYPE, cursorType) != SQL_SUCCESS) + { + // Should never happen + pDb->GetNextError(henv, hdbc, hstmtInternal); + return; + } + } + } + else + { + pDb->DispNextError(); + pDb->DispAllErrors(henv, hdbc, hstmtInternal); + } + } +#ifdef DBDEBUG_CONSOLE + else + cout << "Cursor Type set to STATIC" << endl << endl; +#endif + + if (!queryOnly) + { + // Set the cursor type for the INSERT statement handle + if (SQLSetStmtOption(hstmtInsert, SQL_CURSOR_TYPE, SQL_CURSOR_FORWARD_ONLY) != SQL_SUCCESS) + pDb->DispAllErrors(henv, hdbc, hstmtInsert); + // Set the cursor type for the DELETE statement handle + if (SQLSetStmtOption(hstmtDelete, SQL_CURSOR_TYPE, SQL_CURSOR_FORWARD_ONLY) != SQL_SUCCESS) + pDb->DispAllErrors(henv, hdbc, hstmtDelete); + // Set the cursor type for the UPDATE statement handle + if (SQLSetStmtOption(hstmtUpdate, SQL_CURSOR_TYPE, SQL_CURSOR_FORWARD_ONLY) != SQL_SUCCESS) + pDb->DispAllErrors(henv, hdbc, hstmtUpdate); + } + + // Make the default cursor the active cursor + hstmtDefault = GetNewCursor(FALSE,FALSE); + assert(hstmtDefault); + hstmt = *hstmtDefault; + +} // wxDbTable::wxDbTable() + + +/********** wxDbTable::~wxDbTable() **********/ +wxDbTable::~wxDbTable() +{ + wxString s; + if (pDb) + { + s.sprintf("wxDbTable destructor (%-20s) tableID:[%6lu] pDb:[%p]", tableName,tableID,pDb); + pDb->WriteSqlLog(s.c_str()); + } + +#ifdef __WXDEBUG__ + if (tableID) + { + TablesInUse.DeleteContents(TRUE); + bool found = FALSE; + + wxNode *pNode; + pNode = TablesInUse.First(); + while (pNode && !found) + { + if (((wxTablesInUse *)pNode->Data())->tableID == tableID) + { + found = TRUE; + if (!TablesInUse.DeleteNode(pNode)) + wxLogDebug (s.c_str(),wxT("Unable to delete node!")); + } + else + pNode = pNode->Next(); + } + if (!found) + { + wxString msg; + msg.sprintf(wxT("Unable to find the tableID in the linked\nlist of tables in use.\n\n%s"),s.c_str()); + wxLogDebug (msg.c_str(),wxT("NOTICE...")); + } + } +#endif + + // Decrement the wxDb table count + if (pDb) + pDb->decrementTableCount(); + + // Delete memory allocated for column definitions + if (colDefs) + delete [] colDefs; + + // Free statement handles + if (!queryOnly) + { + if (hstmtInsert) + if (SQLFreeStmt(hstmtInsert, SQL_DROP) != SQL_SUCCESS) + pDb->DispAllErrors(henv, hdbc); + + if (hstmtDelete) + if (SQLFreeStmt(hstmtDelete, SQL_DROP) != SQL_SUCCESS) + + if (hstmtUpdate) + if (SQLFreeStmt(hstmtUpdate, SQL_DROP) != SQL_SUCCESS) + pDb->DispAllErrors(henv, hdbc); + } + + if (hstmtInternal) + if (SQLFreeStmt(hstmtInternal, SQL_DROP) != SQL_SUCCESS) + pDb->DispAllErrors(henv, hdbc); + + // Delete dynamically allocated cursors + if (hstmtDefault) + DeleteCursor(hstmtDefault); + + if (hstmtCount) + DeleteCursor(hstmtCount); + +} // wxDbTable::~wxDbTable() - if (SQLSetStmtOption(c0, SQL_CURSOR_TYPE, cursorType) != SQL_SUCCESS) - pDb->DispAllErrors(henv, hdbc, c0); - if (SQLSetStmtOption(c2, SQL_CURSOR_TYPE, cursorType) != SQL_SUCCESS) - pDb->DispAllErrors(henv, hdbc, c2); -// if (SQLSetStmtOption(c3, SQL_CURSOR_TYPE, cursorType) != SQL_SUCCESS) -// pDb->DispAllErrors(henv, hdbc, c3); -// if (SQLSetStmtOption(c4, SQL_CURSOR_TYPE, cursorType) != SQL_SUCCESS) -// pDb->DispAllErrors(henv, hdbc, c4); -// if (SQLSetStmtOption(c5, SQL_CURSOR_TYPE, cursorType) != SQL_SUCCESS) -// pDb->DispAllErrors(henv, hdbc, c5); - - // Set the cursor type for the INSERT statement handle - if (SQLSetStmtOption(hstmtInsert, SQL_CURSOR_TYPE, SQL_CURSOR_FORWARD_ONLY) != SQL_SUCCESS) - pDb->DispAllErrors(henv, hdbc, hstmtInsert); - // Set the cursor type for the DELETE statement handle - if (SQLSetStmtOption(hstmtDelete, SQL_CURSOR_TYPE, SQL_CURSOR_FORWARD_ONLY) != SQL_SUCCESS) - pDb->DispAllErrors(henv, hdbc, hstmtDelete); - // Set the cursor type for the UPDATE statement handle - if (SQLSetStmtOption(hstmtUpdate, SQL_CURSOR_TYPE, SQL_CURSOR_FORWARD_ONLY) != SQL_SUCCESS) - pDb->DispAllErrors(henv, hdbc, hstmtUpdate); - // Set the cursor type for the COUNT(*) statement handle - if (SQLSetStmtOption(hstmtCount, SQL_CURSOR_TYPE, SQL_CURSOR_FORWARD_ONLY) != SQL_SUCCESS) - pDb->DispAllErrors(henv, hdbc, hstmtCount); - - // Copy cursor 1 to the default cursor - hstmt = c1; - currCursorNo = DB_CURSOR1; - -} // wxTable::wxTable() - -/********** wxTable::~wxTable() **********/ -wxTable::~wxTable() + + +/***************************** PRIVATE FUNCTIONS *****************************/ + + + +/********** wxDbTable::bindInsertParams() **********/ +bool wxDbTable::bindInsertParams(void) { - // Delete memory allocated for column definitions - if (colDefs) - delete [] colDefs; - - // Free statement handles - if (SQLFreeStmt(c0, SQL_DROP) != SQL_SUCCESS) - pDb->DispAllErrors(henv, hdbc); - if (SQLFreeStmt(c1, SQL_DROP) != SQL_SUCCESS) - pDb->DispAllErrors(henv, hdbc); - if (SQLFreeStmt(c2, SQL_DROP) != SQL_SUCCESS) - pDb->DispAllErrors(henv, hdbc); -// if (SQLFreeStmt(c3, SQL_DROP) != SQL_SUCCESS) -// pDb->DispAllErrors(henv, hdbc); -// if (SQLFreeStmt(c4, SQL_DROP) != SQL_SUCCESS) -// pDb->DispAllErrors(henv, hdbc); -// if (SQLFreeStmt(c5, SQL_DROP) != SQL_SUCCESS) -// pDb->DispAllErrors(henv, hdbc); - if (SQLFreeStmt(hstmtInsert, SQL_DROP) != SQL_SUCCESS) - pDb->DispAllErrors(henv, hdbc); - if (SQLFreeStmt(hstmtDelete, SQL_DROP) != SQL_SUCCESS) - pDb->DispAllErrors(henv, hdbc); - if (SQLFreeStmt(hstmtUpdate, SQL_DROP) != SQL_SUCCESS) - pDb->DispAllErrors(henv, hdbc); - if (SQLFreeStmt(hstmtCount, SQL_DROP) != SQL_SUCCESS) - pDb->DispAllErrors(henv, hdbc); - -} // wxTable::~wxTable() - -/********** wxTable::Open() **********/ -bool wxTable::Open(void) + assert(!queryOnly); + if (queryOnly) + return(FALSE); + + SWORD fSqlType = 0; + UDWORD precision = 0; + SWORD scale = 0; + + // Bind each column (that can be inserted) of the table to a parameter marker + int i,colNo; + for (i = 0, colNo = 1; i < noCols; i++) + { + if (! colDefs[i].InsertAllowed) + continue; + switch(colDefs[i].DbDataType) + { + case DB_DATA_TYPE_VARCHAR: + fSqlType = pDb->GetTypeInfVarchar().FsqlType; + precision = colDefs[i].SzDataObj; + scale = 0; + if (colDefs[i].Null) + colDefs[i].CbValue = SQL_NULL_DATA; + else + colDefs[i].CbValue = SQL_NTS; + break; + case DB_DATA_TYPE_INTEGER: + fSqlType = pDb->GetTypeInfInteger().FsqlType; + precision = pDb->GetTypeInfInteger().Precision; + scale = 0; + if (colDefs[i].Null) + colDefs[i].CbValue = SQL_NULL_DATA; + else + colDefs[i].CbValue = 0; + break; + case DB_DATA_TYPE_FLOAT: + fSqlType = pDb->GetTypeInfFloat().FsqlType; + precision = pDb->GetTypeInfFloat().Precision; + scale = pDb->GetTypeInfFloat().MaximumScale; + // SQL Sybase Anywhere v5.5 returned a negative number for the + // MaxScale. This caused ODBC to kick out an error on ibscale. + // I check for this here and set the scale = precision. + //if (scale < 0) + // scale = (short) precision; + if (colDefs[i].Null) + colDefs[i].CbValue = SQL_NULL_DATA; + else + colDefs[i].CbValue = 0; + break; + case DB_DATA_TYPE_DATE: + fSqlType = pDb->GetTypeInfDate().FsqlType; + precision = pDb->GetTypeInfDate().Precision; + scale = 0; + if (colDefs[i].Null) + colDefs[i].CbValue = SQL_NULL_DATA; + else + colDefs[i].CbValue = 0; + break; + } + // Null values +//RG-NULL +//RG-NULL if (colDefs[i].Null) +//RG-NULL { +//RG-NULL colDefs[i].CbValue = SQL_NULL_DATA; +//RG-NULL colDefs[i].Null = FALSE; +//RG-NULL } + + if (SQLBindParameter(hstmtInsert, colNo++, SQL_PARAM_INPUT, colDefs[i].SqlCtype, + fSqlType, precision, scale, (UCHAR*) colDefs[i].PtrDataObj, + precision+1,&colDefs[i].CbValue) != SQL_SUCCESS) + { + return(pDb->DispAllErrors(henv, hdbc, hstmtInsert)); + } + } + + // Completed successfully + return(TRUE); + +} // wxDbTable::bindInsertParams() + + +/********** wxDbTable::bindUpdateParams() **********/ +bool wxDbTable::bindUpdateParams(void) { - int i; - char sqlStmt[DB_MAX_STATEMENT_LEN]; - - // Verify that the table exists in the database - if (!pDb->TableExists(tableName)) - { - wxString s; - s.Printf("Error opening '%s', table/view does not exist in the database.", tableName); - pDb->LogError(WXSTRINGCAST(s)); - return(FALSE); - } - - // Bind the member variables for field exchange between - // the wxTable object and the ODBC record. - if(! bindInsertParams()) // Inserts - return(FALSE); - if(! bindUpdateParams()) // Updates - return(FALSE); - if(! bindCols(c0)) // Selects - return(FALSE); - if(! bindCols(c1)) - return(FALSE); - if(! bindCols(c2)) - return(FALSE); -// if(! bindCols(c3)) -// return(FALSE); -// if(! bindCols(c4)) -// return(FALSE); -// if(! bindCols(c5)) -// return(FALSE); - - // Build an insert statement using parameter markers - if (noCols > 0) - { - bool needComma = FALSE; - sprintf(sqlStmt, "INSERT INTO %s (", tableName); - for (i = 0; i < noCols; i++) - { - if (! colDefs[i].InsertAllowed) - continue; - if (needComma) - strcat(sqlStmt, ","); - strcat(sqlStmt, colDefs[i].ColName); - needComma = TRUE; - } - needComma = FALSE; - strcat(sqlStmt, ") VALUES ("); - for (i = 0; i < noCols; i++) - { - if (! colDefs[i].InsertAllowed) - continue; - if (needComma) - strcat(sqlStmt, ","); - strcat(sqlStmt, "?"); - needComma = TRUE; - } - strcat(sqlStmt, ")"); - - // Prepare the insert statement for execution - if (SQLPrepare(hstmtInsert, (UCHAR FAR *) sqlStmt, SQL_NTS) != SQL_SUCCESS) - return(pDb->DispAllErrors(henv, hdbc, hstmtInsert)); - } - - // Completed successfully - return(TRUE); - -} // wxTable::Open() - -/********** wxTable::Query() **********/ -bool wxTable::Query(bool forUpdate, bool distinct) + assert(!queryOnly); + if (queryOnly) + return(FALSE); + + SWORD fSqlType = 0; + UDWORD precision = 0; + SWORD scale = 0; + + // Bind each UPDATEABLE column of the table to a parameter marker + int i,colNo; + for (i = 0, colNo = 1; i < noCols; i++) + { + if (! colDefs[i].Updateable) + continue; + switch(colDefs[i].DbDataType) + { + case DB_DATA_TYPE_VARCHAR: + fSqlType = pDb->GetTypeInfVarchar().FsqlType; + precision = colDefs[i].SzDataObj; + scale = 0; + if (colDefs[i].Null) + colDefs[i].CbValue = SQL_NULL_DATA; + else + colDefs[i].CbValue = SQL_NTS; + break; + case DB_DATA_TYPE_INTEGER: + fSqlType = pDb->GetTypeInfInteger().FsqlType; + precision = pDb->GetTypeInfInteger().Precision; + scale = 0; + if (colDefs[i].Null) + colDefs[i].CbValue = SQL_NULL_DATA; + else + colDefs[i].CbValue = 0; + break; + case DB_DATA_TYPE_FLOAT: + fSqlType = pDb->GetTypeInfFloat().FsqlType; + precision = pDb->GetTypeInfFloat().Precision; + scale = pDb->GetTypeInfFloat().MaximumScale; + // SQL Sybase Anywhere v5.5 returned a negative number for the + // MaxScale. This caused ODBC to kick out an error on ibscale. + // I check for this here and set the scale = precision. + //if (scale < 0) + // scale = (short) precision; + if (colDefs[i].Null) + colDefs[i].CbValue = SQL_NULL_DATA; + else + colDefs[i].CbValue = 0; + break; + case DB_DATA_TYPE_DATE: + fSqlType = pDb->GetTypeInfDate().FsqlType; + precision = pDb->GetTypeInfDate().Precision; + scale = 0; + if (colDefs[i].Null) + colDefs[i].CbValue = SQL_NULL_DATA; + else + colDefs[i].CbValue = 0; + break; + } + + if (SQLBindParameter(hstmtUpdate, colNo++, SQL_PARAM_INPUT, colDefs[i].SqlCtype, + fSqlType, precision, scale, (UCHAR*) colDefs[i].PtrDataObj, + precision+1, &colDefs[i].CbValue) != SQL_SUCCESS) + { + return(pDb->DispAllErrors(henv, hdbc, hstmtUpdate)); + } + } + + // Completed successfully + return(TRUE); + +} // wxDbTable::bindUpdateParams() + + +/********** wxDbTable::bindCols() **********/ +bool wxDbTable::bindCols(HSTMT cursor) { +//RG-NULL static SDWORD cb; + + // Bind each column of the table to a memory address for fetching data + int i; + for (i = 0; i < noCols; i++) + { + if (SQLBindCol(cursor, i+1, colDefs[i].SqlCtype, (UCHAR*) colDefs[i].PtrDataObj, +//RG-NULL colDefs[i].SzDataObj, &cb) != SQL_SUCCESS) + colDefs[i].SzDataObj, &colDefs[i].CbValue ) != SQL_SUCCESS) + { + return (pDb->DispAllErrors(henv, hdbc, cursor)); + } + } + + // Completed successfully + return(TRUE); + +} // wxDbTable::bindCols() + + +/********** wxDbTable::getRec() **********/ +bool wxDbTable::getRec(UWORD fetchType) +{ + RETCODE retcode; + + if (!pDb->FwdOnlyCursors()) + { + // Fetch the NEXT, PREV, FIRST or LAST record, depending on fetchType + UDWORD cRowsFetched; + UWORD rowStatus; + + retcode = SQLExtendedFetch(hstmt, fetchType, 0, &cRowsFetched, &rowStatus); + if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO) + { + if (retcode == SQL_NO_DATA_FOUND) + return(FALSE); + else + return(pDb->DispAllErrors(henv, hdbc, hstmt)); + } + else + { + // Set the Null member variable to indicate the Null state + // of each column just read in. + int i; + for (i = 0; i < noCols; i++) + colDefs[i].Null = (colDefs[i].CbValue == SQL_NULL_DATA); + } + } + else + { + // Fetch the next record from the record set + retcode = SQLFetch(hstmt); + if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO) + { + if (retcode == SQL_NO_DATA_FOUND) + return(FALSE); + else + return(pDb->DispAllErrors(henv, hdbc, hstmt)); + } + else + { + // Set the Null member variable to indicate the Null state + // of each column just read in. + int i; + for (i = 0; i < noCols; i++) + colDefs[i].Null = (colDefs[i].CbValue == SQL_NULL_DATA); + } + } + + // Completed successfully + return(TRUE); + +} // wxDbTable::getRec() + + +/********** wxDbTable::execDelete() **********/ +bool wxDbTable::execDelete(const char *pSqlStmt) +{ + // Execute the DELETE statement + if (SQLExecDirect(hstmtDelete, (UCHAR FAR *) pSqlStmt, SQL_NTS) != SQL_SUCCESS) + return(pDb->DispAllErrors(henv, hdbc, hstmtDelete)); + + // Record deleted successfully + return(TRUE); - return(query(DB_SELECT_WHERE, forUpdate, distinct)); +} // wxDbTable::execDelete() -} // wxTable::Query() -/********** wxTable::QueryBySqlStmt() **********/ -bool wxTable::QueryBySqlStmt(char *pSqlStmt) +/********** wxDbTable::execUpdate() **********/ +bool wxDbTable::execUpdate(const char *pSqlStmt) { + // Execute the UPDATE statement + if (SQLExecDirect(hstmtUpdate, (UCHAR FAR *) pSqlStmt, SQL_NTS) != SQL_SUCCESS) + return(pDb->DispAllErrors(henv, hdbc, hstmtUpdate)); - return(query(DB_SELECT_STATEMENT, FALSE, FALSE, pSqlStmt)); + // Record deleted successfully + return(TRUE); -} // wxTable::QueryBySqlStmt() +} // wxDbTable::execUpdate() -/********** wxTable::QueryMatching() **********/ -bool wxTable::QueryMatching(bool forUpdate, bool distinct) + +/********** wxDbTable::query() **********/ +bool wxDbTable::query(int queryType, bool forUpdate, bool distinct, const char *pSqlStmt) { + char sqlStmt[DB_MAX_STATEMENT_LEN]; + + if (forUpdate) + // The user may wish to select for update, but the DBMS may not be capable + selectForUpdate = CanSelectForUpdate(); + else + selectForUpdate = FALSE; + + // Set the SQL SELECT string + if (queryType != DB_SELECT_STATEMENT) // A select statement was not passed in, + { // so generate a select statement. + BuildSelectStmt(sqlStmt, queryType, distinct); + pDb->WriteSqlLog(sqlStmt); + } +/* + This is the block of code that got added during the 2.2.1 merge with + the 2.2 main branch that somehow got added here when it should not have. - gt + + else + wxStrcpy(sqlStmt, pSqlStmt); + + SQLFreeStmt(hstmt, SQL_CLOSE); + if (SQLExecDirect(hstmt, (UCHAR FAR *) sqlStmt, SQL_NTS) == SQL_SUCCESS) + return(TRUE); + else + { + pDb->DispAllErrors(henv, hdbc, hstmt); + return(FALSE); + } +*/ + // Make sure the cursor is closed first + if (!CloseCursor(hstmt)) + return(FALSE); + + // Execute the SQL SELECT statement + int retcode; + retcode = SQLExecDirect(hstmt, (UCHAR FAR *) (queryType == DB_SELECT_STATEMENT ? pSqlStmt : sqlStmt), SQL_NTS); + if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO) + return(pDb->DispAllErrors(henv, hdbc, hstmt)); + + // Completed successfully + return(TRUE); + +} // wxDbTable::query() + - return(query(DB_SELECT_MATCHING, forUpdate, distinct)); +/***************************** PUBLIC FUNCTIONS *****************************/ -} // wxTable::QueryMatching() -/********** wxTable::QueryOnKeyFields() **********/ -bool wxTable::QueryOnKeyFields(bool forUpdate, bool distinct) +/********** wxDbTable::Open() **********/ +bool wxDbTable::Open(bool checkPrivileges) { + if (!pDb) + return FALSE; + + int i; + wxString sqlStmt; + wxString s; + + s = ""; + // Verify that the table exists in the database + if (!pDb->TableExists(tableName,/*pDb->GetUsername()*/NULL,tablePath)) + { + s = "Table/view does not exist in the database"; + if ( *(pDb->dbInf.accessibleTables) == 'Y') + s += ", or you have no permissions.\n"; + else + s += ".\n"; + } + else if (checkPrivileges) + { + // Verify the user has rights to access the table. + // Shortcut boolean evaluation to optimize out call to + // TablePrivileges + // + // Unfortunately this optimization doesn't seem to be + // reliable! + if (// *(pDb->dbInf.accessibleTables) == 'N' && + !pDb->TablePrivileges(tableName,"SELECT",NULL,pDb->GetUsername(),tablePath)) + s = "Current logged in user does not have sufficient privileges to access this table.\n"; + } + + if (!s.IsEmpty()) + { + wxString p; + + if (wxStrcmp(tablePath,"")) + p.sprintf("Error opening '%s/%s'.\n",tablePath,tableName); + else + p.sprintf("Error opening '%s'.\n", tableName); + + p += s; + pDb->LogError(p.GetData()); + + return(FALSE); + } + + // Bind the member variables for field exchange between + // the wxDbTable object and the ODBC record. + if (!queryOnly) + { + if (!bindInsertParams()) // Inserts + return(FALSE); + + if (!bindUpdateParams()) // Updates + return(FALSE); + } + + if (!bindCols(*hstmtDefault)) // Selects + return(FALSE); + + if (!bindCols(hstmtInternal)) // Internal use only + return(FALSE); + + /* + * Do NOT bind the hstmtCount cursor!!! + */ + + // Build an insert statement using parameter markers + if (!queryOnly && noCols > 0) + { + bool needComma = FALSE; + sqlStmt.sprintf("INSERT INTO %s (", tableName); + for (i = 0; i < noCols; i++) + { + if (! colDefs[i].InsertAllowed) + continue; + if (needComma) + sqlStmt += ","; + sqlStmt += colDefs[i].ColName; + needComma = TRUE; + } + needComma = FALSE; + sqlStmt += ") VALUES ("; + + int insertableCount = 0; + + for (i = 0; i < noCols; i++) + { + if (! colDefs[i].InsertAllowed) + continue; + if (needComma) + sqlStmt += ","; + sqlStmt += "?"; + needComma = TRUE; + insertableCount++; + } + sqlStmt += ")"; + + // Prepare the insert statement for execution + if (insertableCount) + { + if (SQLPrepare(hstmtInsert, (UCHAR FAR *) sqlStmt.c_str(), SQL_NTS) != SQL_SUCCESS) + return(pDb->DispAllErrors(henv, hdbc, hstmtInsert)); + } + else + insertable= FALSE; + } + + // Completed successfully + return(TRUE); + +} // wxDbTable::Open() + + +/********** wxDbTable::Query() **********/ +bool wxDbTable::Query(bool forUpdate, bool distinct) +{ + + return(query(DB_SELECT_WHERE, forUpdate, distinct)); - return(query(DB_SELECT_KEYFIELDS, forUpdate, distinct)); +} // wxDbTable::Query() -} // wxTable::QueryOnKeyFields() -/********** wxTable::query() **********/ -bool wxTable::query(int queryType, bool forUpdate, bool distinct, char *pSqlStmt) +/********** wxDbTable::QueryBySqlStmt() **********/ +bool wxDbTable::QueryBySqlStmt(const char *pSqlStmt) { - char sqlStmt[DB_MAX_STATEMENT_LEN]; + pDb->WriteSqlLog(pSqlStmt); - // Set the selectForUpdate member variable - if (forUpdate) - // The user may wish to select for update, but the DBMS may not be capable - selectForUpdate = CanSelectForUpdate(); - else - selectForUpdate = FALSE; + return(query(DB_SELECT_STATEMENT, FALSE, FALSE, pSqlStmt)); - // Set the SQL SELECT string - if (queryType != DB_SELECT_STATEMENT) // A select statement was not passed in, - GetSelectStmt(sqlStmt, queryType, distinct); // so generate a select statement. +} // wxDbTable::QueryBySqlStmt() - // Make sure the cursor is closed first - if (! CloseCursor(hstmt)) - return(FALSE); - // Execute the SQL SELECT statement - if (SQLExecDirect(hstmt, (UCHAR FAR *) (queryType == DB_SELECT_STATEMENT ? pSqlStmt : sqlStmt), - SQL_NTS) != SQL_SUCCESS) - return(pDb->DispAllErrors(henv, hdbc, hstmt)); +/********** wxDbTable::QueryMatching() **********/ +bool wxDbTable::QueryMatching(bool forUpdate, bool distinct) +{ - // Completed successfully - return(TRUE); + return(query(DB_SELECT_MATCHING, forUpdate, distinct)); -} // wxTable::query() +} // wxDbTable::QueryMatching() -/********** wxTable::GetSelectStmt() **********/ -void wxTable::GetSelectStmt(char *pSqlStmt, int typeOfSelect, bool distinct) + +/********** wxDbTable::QueryOnKeyFields() **********/ +bool wxDbTable::QueryOnKeyFields(bool forUpdate, bool distinct) { - char whereClause[DB_MAX_WHERE_CLAUSE_LEN]; - - whereClause[0] = 0; - - // Build a select statement to query the database - strcpy(pSqlStmt, "SELECT "); - - // SELECT DISTINCT values only? - if (distinct) - strcat(pSqlStmt, "DISTINCT "); - - // Add the column list - for (int i = 0; i < noCols; i++) - { - strcat(pSqlStmt, colDefs[i].ColName); - if (i + 1 < noCols) - strcat(pSqlStmt, ","); - } - - // If the datasource supports ROWID, get this column as well. Exception: Don't retrieve - // the ROWID if querying distinct records. The rowid will always be unique. - if (!distinct && CanUpdByROWID()) - strcat(pSqlStmt, ",ROWID"); - - // Append the FROM tablename portion - strcat(pSqlStmt, " FROM "); - strcat(pSqlStmt, queryTableName); - - // Append the WHERE clause. Either append the where clause for the class - // or build a where clause. The typeOfSelect determines this. - switch(typeOfSelect) - { - case DB_SELECT_WHERE: - if (where && strlen(where)) // May not want a where clause!!! - { - strcat(pSqlStmt, " WHERE "); - strcat(pSqlStmt, where); - } - break; - case DB_SELECT_KEYFIELDS: - GetWhereClause(whereClause, DB_WHERE_KEYFIELDS); - if (strlen(whereClause)) - { - strcat(pSqlStmt, " WHERE "); - strcat(pSqlStmt, whereClause); - } - break; - case DB_SELECT_MATCHING: - GetWhereClause(whereClause, DB_WHERE_MATCHING); - if (strlen(whereClause)) - { - strcat(pSqlStmt, " WHERE "); - strcat(pSqlStmt, whereClause); - } - break; - } - - // Append the ORDER BY clause - if (orderBy && strlen(orderBy)) - { - strcat(pSqlStmt, " ORDER BY "); - strcat(pSqlStmt, orderBy); - } - - // SELECT FOR UPDATE if told to do so and the datasource is capable - if (selectForUpdate && CanSelectForUpdate()) - strcat(pSqlStmt, " FOR UPDATE"); - -} // wxTable::GetSelectStmt() - -/********** wxTable::getRec() **********/ -bool wxTable::getRec(UWORD fetchType) + + return(query(DB_SELECT_KEYFIELDS, forUpdate, distinct)); + +} // wxDbTable::QueryOnKeyFields() + + +/********** wxDbTable::GetPrev() **********/ +bool wxDbTable::GetPrev(void) { - RETCODE retcode; - -#ifndef FWD_ONLY_CURSORS - // Fetch the NEXT, PREV, FIRST or LAST record, depending on fetchType - UDWORD cRowsFetched; - UWORD rowStatus; - if ((retcode = SQLExtendedFetch(hstmt, fetchType, 0, &cRowsFetched, &rowStatus)) != SQL_SUCCESS) - if (retcode == SQL_NO_DATA_FOUND) - return(FALSE); - else - return(pDb->DispAllErrors(henv, hdbc, hstmt)); -#else - // Fetch the next record from the record set - if ((retcode = SQLFetch(hstmt)) != SQL_SUCCESS) - if (retcode == SQL_NO_DATA_FOUND) - return(FALSE); - else - return(pDb->DispAllErrors(henv, hdbc, hstmt)); -#endif + if (pDb->FwdOnlyCursors()) + { + wxFAIL_MSG(wxT("GetPrev()::Backward scrolling cursors are not enabled for this instance of wxDbTable")); + return FALSE; + } + else + return(getRec(SQL_FETCH_PRIOR)); - // Completed successfully - return(TRUE); +} // wxDbTable::GetPrev() -} // wxTable::getRec() -/********** wxTable::GetRowNum() **********/ -UWORD wxTable::GetRowNum(void) +/********** wxDbTable::operator-- **********/ +bool wxDbTable::operator--(int) { - UDWORD rowNum; + if (pDb->FwdOnlyCursors()) + { + wxFAIL_MSG(wxT("operator--:Backward scrolling cursors are not enabled for this instance of wxDbTable")); + return FALSE; + } + else + return(getRec(SQL_FETCH_PRIOR)); - if (SQLGetStmtOption(hstmt, SQL_ROW_NUMBER, &rowNum) != SQL_SUCCESS) - { - pDb->DispAllErrors(henv, hdbc, hstmt); - return(0); - } +} // wxDbTable::operator-- - // Completed successfully - return((UWORD) rowNum); -} // wxTable::GetRowNum() +/********** wxDbTable::GetFirst() **********/ +bool wxDbTable::GetFirst(void) +{ + if (pDb->FwdOnlyCursors()) + { + wxFAIL_MSG(wxT("GetFirst():Backward scrolling cursors are not enabled for this instance of wxDbTable")); + return FALSE; + } + else + return(getRec(SQL_FETCH_FIRST)); + +} // wxDbTable::GetFirst() + -/********** wxTable::bindInsertParams() **********/ -bool wxTable::bindInsertParams(void) +/********** wxDbTable::GetLast() **********/ +bool wxDbTable::GetLast(void) { - SWORD fSqlType; - UDWORD precision; - SWORD scale; - -//glt CcolDef *tColDef; - - // Bind each column (that can be inserted) of the table to a parameter marker - for (int i = 0; i < noCols; i++) - { -//glt tColDef = &colDefs[i]; - if (! colDefs[i].InsertAllowed) - continue; - switch(colDefs[i].DbDataType) - { - case DB_DATA_TYPE_VARCHAR: - fSqlType = pDb->typeInfVarchar.FsqlType; - precision = colDefs[i].SzDataObj; - scale = 0; - colDefs[i].CbValue = SQL_NTS; - break; - case DB_DATA_TYPE_INTEGER: - fSqlType = pDb->typeInfInteger.FsqlType; - precision = pDb->typeInfInteger.Precision; - scale = 0; - colDefs[i].CbValue = 0; - break; - case DB_DATA_TYPE_FLOAT: - fSqlType = pDb->typeInfFloat.FsqlType; - precision = pDb->typeInfFloat.Precision; - scale = pDb->typeInfFloat.MaximumScale; - // SQL Sybase Anywhere v5.5 returned a negative number for the - // MaxScale. This caused ODBC to kick out an error on ibscale. - // I check for this here and set the scale = precision. - //if (scale < 0) - // scale = (short) precision; - colDefs[i].CbValue = 0; - break; - case DB_DATA_TYPE_DATE: - fSqlType = pDb->typeInfDate.FsqlType; - precision = pDb->typeInfDate.Precision; - scale = 0; - colDefs[i].CbValue = 0; - break; - } - if (SQLBindParameter(hstmtInsert, i+1, SQL_PARAM_INPUT, colDefs[i].SqlCtype, - fSqlType, precision, scale, colDefs[i].PtrDataObj, - precision+1,&colDefs[i].CbValue) != SQL_SUCCESS) - return(pDb->DispAllErrors(henv, hdbc, hstmtInsert)); - } - - // Completed successfully - return(TRUE); - -} // wxTable::bindInsertParams() - -/********** wxTable::bindUpdateParams() **********/ -bool wxTable::bindUpdateParams(void) + if (pDb->FwdOnlyCursors()) + { + wxFAIL_MSG(wxT("GetLast()::Backward scrolling cursors are not enabled for this instance of wxDbTable")); + return FALSE; + } + else + return(getRec(SQL_FETCH_LAST)); + +} // wxDbTable::GetLast() + + +/********** wxDbTable::BuildSelectStmt() **********/ +void wxDbTable::BuildSelectStmt(char *pSqlStmt, int typeOfSelect, bool distinct) { - SWORD fSqlType; - UDWORD precision; - SWORD scale; - - // Bind each UPDATEABLE column of the table to a parameter marker - for (int i = 0, colNo = 1; i < noCols; i++) - { - if (! colDefs[i].Updateable) - continue; - switch(colDefs[i].DbDataType) - { - case DB_DATA_TYPE_VARCHAR: - fSqlType = pDb->typeInfVarchar.FsqlType; - precision = colDefs[i].SzDataObj; - scale = 0; - colDefs[i].CbValue = SQL_NTS; - break; - case DB_DATA_TYPE_INTEGER: - fSqlType = pDb->typeInfInteger.FsqlType; - precision = pDb->typeInfInteger.Precision; - scale = 0; - colDefs[i].CbValue = 0; - break; - case DB_DATA_TYPE_FLOAT: - fSqlType = pDb->typeInfFloat.FsqlType; - precision = pDb->typeInfFloat.Precision; - scale = pDb->typeInfFloat.MaximumScale; - // SQL Sybase Anywhere v5.5 returned a negative number for the - // MaxScale. This caused ODBC to kick out an error on ibscale. - // I check for this here and set the scale = precision. - //if (scale < 0) - // scale = (short) precision; - colDefs[i].CbValue = 0; - break; - case DB_DATA_TYPE_DATE: - fSqlType = pDb->typeInfDate.FsqlType; - precision = pDb->typeInfDate.Precision; - scale = 0; - colDefs[i].CbValue = 0; - break; - } - if (SQLBindParameter(hstmtUpdate, colNo++, SQL_PARAM_INPUT, colDefs[i].SqlCtype, - fSqlType, precision, scale, colDefs[i].PtrDataObj, - precision+1, &colDefs[i].CbValue) != SQL_SUCCESS) - return(pDb->DispAllErrors(henv, hdbc, hstmtUpdate)); - } - - // Completed successfully - return(TRUE); - -} // wxTable::bindUpdateParams() - -/********** wxTable::bindCols() **********/ -bool wxTable::bindCols(HSTMT cursor) + char whereClause[DB_MAX_WHERE_CLAUSE_LEN]; + + whereClause[0] = 0; + + // Build a select statement to query the database + wxStrcpy(pSqlStmt, "SELECT "); + + // SELECT DISTINCT values only? + if (distinct) + wxStrcat(pSqlStmt, "DISTINCT "); + + // Was a FROM clause specified to join tables to the base table? + // Available for ::Query() only!!! + bool appendFromClause = FALSE; +#if wxODBC_BACKWARD_COMPATABILITY + if (typeOfSelect == DB_SELECT_WHERE && from && wxStrlen(from)) + appendFromClause = TRUE; +#else + if (typeOfSelect == DB_SELECT_WHERE && from.Length()) + appendFromClause = TRUE; +#endif + + // Add the column list + int i; + for (i = 0; i < noCols; i++) + { + // If joining tables, the base table column names must be qualified to avoid ambiguity + if (appendFromClause) + { + wxStrcat(pSqlStmt, queryTableName); + wxStrcat(pSqlStmt, "."); + } + wxStrcat(pSqlStmt, colDefs[i].ColName); + if (i + 1 < noCols) + wxStrcat(pSqlStmt, ","); + } + + // If the datasource supports ROWID, get this column as well. Exception: Don't retrieve + // the ROWID if querying distinct records. The rowid will always be unique. + if (!distinct && CanUpdByROWID()) + { + // If joining tables, the base table column names must be qualified to avoid ambiguity + if (appendFromClause) + { + wxStrcat(pSqlStmt, ","); + wxStrcat(pSqlStmt, queryTableName); + wxStrcat(pSqlStmt, ".ROWID"); + } + else + wxStrcat(pSqlStmt, ",ROWID"); + } + + // Append the FROM tablename portion + wxStrcat(pSqlStmt, " FROM "); + wxStrcat(pSqlStmt, queryTableName); + + // Sybase uses the HOLDLOCK keyword to lock a record during query. + // The HOLDLOCK keyword follows the table name in the from clause. + // Each table in the from clause must specify HOLDLOCK or + // NOHOLDLOCK (the default). Note: The "FOR UPDATE" clause + // is parsed but ignored in SYBASE Transact-SQL. + if (selectForUpdate && (pDb->Dbms() == dbmsSYBASE_ASA || pDb->Dbms() == dbmsSYBASE_ASE)) + wxStrcat(pSqlStmt, " HOLDLOCK"); + + if (appendFromClause) + wxStrcat(pSqlStmt, from); + + // Append the WHERE clause. Either append the where clause for the class + // or build a where clause. The typeOfSelect determines this. + switch(typeOfSelect) + { + case DB_SELECT_WHERE: +#if wxODBC_BACKWARD_COMPATABILITY + if (where && wxStrlen(where)) // May not want a where clause!!! +#else + if (where.Length()) // May not want a where clause!!! +#endif + { + wxStrcat(pSqlStmt, " WHERE "); + wxStrcat(pSqlStmt, where); + } + break; + case DB_SELECT_KEYFIELDS: + BuildWhereClause(whereClause, DB_WHERE_KEYFIELDS); + if (wxStrlen(whereClause)) + { + wxStrcat(pSqlStmt, " WHERE "); + wxStrcat(pSqlStmt, whereClause); + } + break; + case DB_SELECT_MATCHING: + BuildWhereClause(whereClause, DB_WHERE_MATCHING); + if (wxStrlen(whereClause)) + { + wxStrcat(pSqlStmt, " WHERE "); + wxStrcat(pSqlStmt, whereClause); + } + break; + } + + // Append the ORDER BY clause +#if wxODBC_BACKWARD_COMPATABILITY + if (orderBy && wxStrlen(orderBy)) +#else + if (orderBy.Length()) +#endif + { + wxStrcat(pSqlStmt, " ORDER BY "); + wxStrcat(pSqlStmt, orderBy); + } + + // SELECT FOR UPDATE if told to do so and the datasource is capable. Sybase + // parses the FOR UPDATE clause but ignores it. See the comment above on the + // HOLDLOCK for Sybase. + if (selectForUpdate && CanSelectForUpdate()) + wxStrcat(pSqlStmt, " FOR UPDATE"); + +} // wxDbTable::BuildSelectStmt() + + +/********** wxDbTable::GetRowNum() **********/ +UWORD wxDbTable::GetRowNum(void) { - static SDWORD cb; - - // Bind each column of the table to a memory address for fetching data - for (int i = 0; i < noCols; i++) - { - if (SQLBindCol(cursor, i+1, colDefs[i].SqlCtype, colDefs[i].PtrDataObj, - colDefs[i].SzDataObj, &cb) != SQL_SUCCESS) - return(pDb->DispAllErrors(henv, hdbc, cursor)); - } - - // Completed successfully - return(TRUE); - -} // wxTable::bindCols() - -/********** wxTable::CloseCursor() **********/ -bool wxTable::CloseCursor(HSTMT cursor) + UDWORD rowNum; + + if (SQLGetStmtOption(hstmt, SQL_ROW_NUMBER, (UCHAR*) &rowNum) != SQL_SUCCESS) + { + pDb->DispAllErrors(henv, hdbc, hstmt); + return(0); + } + + // Completed successfully + return((UWORD) rowNum); + +} // wxDbTable::GetRowNum() + + +/********** wxDbTable::CloseCursor() **********/ +bool wxDbTable::CloseCursor(HSTMT cursor) { - if (SQLFreeStmt(cursor, SQL_CLOSE) != SQL_SUCCESS) - return(pDb->DispAllErrors(henv, hdbc, cursor)); + if (SQLFreeStmt(cursor, SQL_CLOSE) != SQL_SUCCESS) + return(pDb->DispAllErrors(henv, hdbc, cursor)); + + // Completed successfully + return(TRUE); - // Completed successfully - return(TRUE); +} // wxDbTable::CloseCursor() -} // wxTable::CloseCursor() -/********** wxTable::CreateTable() **********/ -bool wxTable::CreateTable(void) +/********** wxDbTable::CreateTable() **********/ +bool wxDbTable::CreateTable(bool attemptDrop) { - int i, j; - char sqlStmt[DB_MAX_STATEMENT_LEN]; + if (!pDb) + return FALSE; + + int i, j; + wxString sqlStmt; -#ifdef _CONSOLE - cout << "Creating Table " << tableName << "..." << endl; +#ifdef DBDEBUG_CONSOLE + cout << "Creating Table " << tableName << "..." << endl; #endif - // Drop the table first - sprintf(sqlStmt, "DROP TABLE %s", tableName); - if (SQLExecDirect(hstmt, (UCHAR FAR *) sqlStmt, SQL_NTS) != SQL_SUCCESS) - { - // Check for sqlState = S0002, "Table or view not found". - // Ignore this error, bomb out on any other error. - // SQL Sybase Anwhere v5.5 returns an access violation error here - // (sqlstate = 42000) rather than an S0002. - pDb->GetNextError(henv, hdbc, hstmt); - if (strcmp(pDb->sqlState, "S0002") && strcmp(pDb->sqlState, "42000")) - { - pDb->DispNextError(); - pDb->DispAllErrors(henv, hdbc, hstmt); - pDb->RollbackTrans(); - CloseCursor(hstmt); - return(FALSE); - } - } - - // Commit the transaction and close the cursor - if (! pDb->CommitTrans()) - return(FALSE); - if (! CloseCursor(hstmt)) - return(FALSE); - - // Create the table -#ifdef _CONSOLE - for (i = 0; i < noCols; i++) - { - // Exclude derived columns since they are NOT part of the base table - if (colDefs[i].DerivedCol) - continue; - cout << i + 1 << ": " << colDefs[i].ColName << "; "; - switch(colDefs[i].DbDataType) - { - case DB_DATA_TYPE_VARCHAR: - cout << pDb->typeInfVarchar.TypeName << "(" << colDefs[i].SzDataObj << ")"; - break; - case DB_DATA_TYPE_INTEGER: - cout << pDb->typeInfInteger.TypeName; - break; - case DB_DATA_TYPE_FLOAT: - cout << pDb->typeInfFloat.TypeName; - break; - case DB_DATA_TYPE_DATE: - cout << pDb->typeInfDate.TypeName; - break; - } - cout << endl; - } + // Drop table first + if (attemptDrop && !DropTable()) + return FALSE; + + // Create the table +#ifdef DBDEBUG_CONSOLE + for (i = 0; i < noCols; i++) + { + // Exclude derived columns since they are NOT part of the base table + if (colDefs[i].DerivedCol) + continue; + cout << i + 1 << ": " << colDefs[i].ColName << "; "; + switch(colDefs[i].DbDataType) + { + case DB_DATA_TYPE_VARCHAR: + cout << pDb->typeInfVarchar.TypeName << "(" << colDefs[i].SzDataObj << ")"; + break; + case DB_DATA_TYPE_INTEGER: + cout << pDb->typeInfInteger.TypeName; + break; + case DB_DATA_TYPE_FLOAT: + cout << pDb->typeInfFloat.TypeName; + break; + case DB_DATA_TYPE_DATE: + cout << pDb->typeInfDate.TypeName; + break; + } + cout << endl; + } #endif - // Build a CREATE TABLE string from the colDefs structure. - bool needComma = FALSE; - sprintf(sqlStmt, "CREATE TABLE %s (", tableName); - for (i = 0; i < noCols; i++) - { - // Exclude derived columns since they are NOT part of the base table - if (colDefs[i].DerivedCol) - continue; - // Comma Delimiter - if (needComma) - strcat(sqlStmt, ","); - // Column Name - strcat(sqlStmt, colDefs[i].ColName); - strcat(sqlStmt, " "); - // Column Type - switch(colDefs[i].DbDataType) - { - case DB_DATA_TYPE_VARCHAR: - strcat(sqlStmt, pDb->typeInfVarchar.TypeName); break; - case DB_DATA_TYPE_INTEGER: - strcat(sqlStmt, pDb->typeInfInteger.TypeName); break; - case DB_DATA_TYPE_FLOAT: - strcat(sqlStmt, pDb->typeInfFloat.TypeName); break; - case DB_DATA_TYPE_DATE: - strcat(sqlStmt, pDb->typeInfDate.TypeName); break; - } - // For varchars, append the size of the string - if (colDefs[i].DbDataType == DB_DATA_TYPE_VARCHAR) - { - // strcat(sqlStmt, "("); - // strcat(sqlStmt, itoa(colDefs[i].SzDataObj, s, 10)); - // strcat(sqlStmt, ")"); + // Build a CREATE TABLE string from the colDefs structure. + bool needComma = FALSE; + sqlStmt.sprintf("CREATE TABLE %s (", tableName); + + for (i = 0; i < noCols; i++) + { + // Exclude derived columns since they are NOT part of the base table + if (colDefs[i].DerivedCol) + continue; + // Comma Delimiter + if (needComma) + sqlStmt += ","; + // Column Name + sqlStmt += colDefs[i].ColName; + sqlStmt += " "; + // Column Type + switch(colDefs[i].DbDataType) + { + case DB_DATA_TYPE_VARCHAR: + sqlStmt += pDb->GetTypeInfVarchar().TypeName; + break; + case DB_DATA_TYPE_INTEGER: + sqlStmt += pDb->GetTypeInfInteger().TypeName; + break; + case DB_DATA_TYPE_FLOAT: + sqlStmt += pDb->GetTypeInfFloat().TypeName; + break; + case DB_DATA_TYPE_DATE: + sqlStmt += pDb->GetTypeInfDate().TypeName; + break; + } + // For varchars, append the size of the string + if (colDefs[i].DbDataType == DB_DATA_TYPE_VARCHAR) + { wxString s; - s.Printf("(%d)", colDefs[i].SzDataObj); - strcat(sqlStmt, s); - } - needComma = TRUE; - } - // If there is a primary key defined, include it in the create statement - for (i = j = 0; i < noCols; i++) - { - if (colDefs[i].KeyField) - { - j++; - break; - } - } - if (j) // Found a keyfield - { - strcat(sqlStmt, ",CONSTRAINT "); - strcat(sqlStmt, tableName); - strcat(sqlStmt, "_PIDX PRIMARY KEY ("); - // List column name(s) of column(s) comprising the primary key - for (i = j = 0; i < noCols; i++) - { - if (colDefs[i].KeyField) - { - if (j++) // Multi part key, comma separate names - strcat(sqlStmt, ","); - strcat(sqlStmt, colDefs[i].ColName); - } - } - strcat(sqlStmt, ")"); - } - // Append the closing parentheses for the create table statement - strcat(sqlStmt, ")"); - -#ifdef _CONSOLE - cout << endl << sqlStmt << endl; + // wxStrcat(sqlStmt, "("); + // wxStrcat(sqlStmt, itoa(colDefs[i].SzDataObj, s, 10)); + // wxStrcat(sqlStmt, ")"); + s.sprintf("(%d)", colDefs[i].SzDataObj); + sqlStmt += s.c_str(); + } + + if (pDb->Dbms() == dbmsDB2 || + pDb->Dbms() == dbmsMY_SQL || + pDb->Dbms() == dbmsSYBASE_ASE || + pDb->Dbms() == dbmsMS_SQL_SERVER) + { + if (colDefs[i].KeyField) + { + sqlStmt += " NOT NULL"; + } + } + + needComma = TRUE; + } + // If there is a primary key defined, include it in the create statement + for (i = j = 0; i < noCols; i++) + { + if (colDefs[i].KeyField) + { + j++; + break; + } + } + if (j && pDb->Dbms() != dbmsDBASE) // Found a keyfield + { + if (pDb->Dbms() != dbmsMY_SQL) + { + sqlStmt += ",CONSTRAINT "; + sqlStmt += tableName; + sqlStmt += "_PIDX PRIMARY KEY ("; + } + else + { + /* MySQL goes out on this one. We also declare the relevant key NON NULL above */ + sqlStmt += ", PRIMARY KEY ("; + } + + // List column name(s) of column(s) comprising the primary key + for (i = j = 0; i < noCols; i++) + { + if (colDefs[i].KeyField) + { + if (j++) // Multi part key, comma separate names + sqlStmt += ","; + sqlStmt += colDefs[i].ColName; + } + } + sqlStmt += ")"; + } + // Append the closing parentheses for the create table statement + sqlStmt += ")"; + + pDb->WriteSqlLog(sqlStmt.c_str()); + +#ifdef DBDEBUG_CONSOLE + cout << endl << sqlStmt.c_str() << endl; #endif - // Execute the CREATE TABLE statement - if (SQLExecDirect(hstmt, (UCHAR FAR *) sqlStmt, SQL_NTS) != SQL_SUCCESS) - { - pDb->DispAllErrors(henv, hdbc, hstmt); - pDb->RollbackTrans(); - CloseCursor(hstmt); - return(FALSE); - } + // Execute the CREATE TABLE statement + RETCODE retcode = SQLExecDirect(hstmt, (UCHAR FAR *) sqlStmt.c_str(), SQL_NTS); + if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO) + { + pDb->DispAllErrors(henv, hdbc, hstmt); + pDb->RollbackTrans(); + CloseCursor(hstmt); + return(FALSE); + } - // Commit the transaction and close the cursor - if (! pDb->CommitTrans()) - return(FALSE); - if (! CloseCursor(hstmt)) - return(FALSE); + // Commit the transaction and close the cursor + if (!pDb->CommitTrans()) + return(FALSE); + if (!CloseCursor(hstmt)) + return(FALSE); - // Database table created successfully - return(TRUE); + // Database table created successfully + return(TRUE); -} // wxTable::CreateTable() +} // wxDbTable::CreateTable() -/********** wxTable::CreateIndex() **********/ -bool wxTable::CreateIndex(char * idxName, bool unique, int noIdxCols, CidxDef *pIdxDefs) + +/********** wxDbTable::DropTable() **********/ +bool wxDbTable::DropTable() { - char sqlStmt[DB_MAX_STATEMENT_LEN]; - - // Build a CREATE INDEX statement - strcpy(sqlStmt, "CREATE "); - if (unique) - strcat(sqlStmt, "UNIQUE "); - - strcat(sqlStmt, "INDEX "); - strcat(sqlStmt, idxName); - strcat(sqlStmt, " ON "); - strcat(sqlStmt, tableName); - strcat(sqlStmt, " ("); - - // Append list of columns making up index - for (int i = 0; i < noIdxCols; i++) - { - strcat(sqlStmt, pIdxDefs[i].ColName); - if (pIdxDefs[i].Ascending) - strcat(sqlStmt, " ASC"); - else - strcat(sqlStmt, " DESC"); - if ((i + 1) < noIdxCols) - strcat(sqlStmt, ","); - } - - // Append closing parentheses - strcat(sqlStmt, ")"); - -#ifdef _CONSOLE - cout << endl << sqlStmt << endl << endl; -#endif + // NOTE: This function returns TRUE if the Table does not exist, but + // only for identified databases. Code will need to be added + // below for any other databases when those databases are defined + // to handle this situation consistently - // Execute the CREATE INDEX statement - if (SQLExecDirect(hstmt, (UCHAR FAR *) sqlStmt, SQL_NTS) != SQL_SUCCESS) - { - pDb->DispAllErrors(henv, hdbc, hstmt); - pDb->RollbackTrans(); - CloseCursor(hstmt); - return(FALSE); - } + wxString sqlStmt; - // Commit the transaction and close the cursor - if (! pDb->CommitTrans()) - return(FALSE); - if (! CloseCursor(hstmt)) - return(FALSE); + sqlStmt.sprintf("DROP TABLE %s", tableName); - // Index Created Successfully - return(TRUE); + pDb->WriteSqlLog(sqlStmt.c_str()); -} // wxTable::CreateIndex() +#ifdef DBDEBUG_CONSOLE + cout << endl << sqlStmt.c_str() << endl; +#endif -/********** wxTable::Insert() **********/ -int wxTable::Insert(void) -{ - // Insert the record by executing the already prepared insert statement - if (SQLExecute(hstmtInsert) != SQL_SUCCESS) - { - // Check to see if integrity constraint was violated - pDb->GetNextError(henv, hdbc, hstmtInsert); - if (! strcmp(pDb->sqlState, "23000")) // Integrity constraint violated - return(DB_ERR_INTEGRITY_CONSTRAINT_VIOL); - else - { - pDb->DispNextError(); - pDb->DispAllErrors(henv, hdbc, hstmtInsert); - return(DB_FAILURE); - } - } - - // Record inserted into the datasource successfully - return(DB_SUCCESS); - -} // wxTable::Insert() - -/********** wxTable::Update(pSqlStmt) **********/ -bool wxTable::Update(char *pSqlStmt) + if (SQLExecDirect(hstmt, (UCHAR FAR *) sqlStmt.c_str(), SQL_NTS) != SQL_SUCCESS) + { + // Check for "Base table not found" error and ignore + pDb->GetNextError(henv, hdbc, hstmt); + if (wxStrcmp(pDb->sqlState,"S0002") && wxStrcmp(pDb->sqlState, "S1000")) // "Base table not found" + { + // Check for product specific error codes + if (!((pDb->Dbms() == dbmsSYBASE_ASA && !wxStrcmp(pDb->sqlState,"42000")) || // 5.x (and lower?) + (pDb->Dbms() == dbmsSYBASE_ASE && !wxStrcmp(pDb->sqlState,"37000")) || + (pDb->Dbms() == dbmsPOSTGRES && !wxStrcmp(pDb->sqlState,"08S01")))) + { + pDb->DispNextError(); + pDb->DispAllErrors(henv, hdbc, hstmt); + pDb->RollbackTrans(); + CloseCursor(hstmt); + return(FALSE); + } + } + } + + // Commit the transaction and close the cursor + if (! pDb->CommitTrans()) + return(FALSE); + if (! CloseCursor(hstmt)) + return(FALSE); + + return(TRUE); +} // wxDbTable::DropTable() + + +/********** wxDbTable::CreateIndex() **********/ +bool wxDbTable::CreateIndex(const char * idxName, bool unique, int noIdxCols, wxDbIdxDef *pIdxDefs, bool attemptDrop) { + wxString sqlStmt; + + // Drop the index first + if (attemptDrop && !DropIndex(idxName)) + return (FALSE); + + // MySQL (and possibly Sybase ASE?? - gt) require that any columns which are used as portions + // of an index have the columns defined as "NOT NULL". During initial table creation though, + // it may not be known which columns are necessarily going to be part of an index (e.g. the + // table was created, then months later you determine that an additional index while + // give better performance, so you want to add an index). + // + // The following block of code will modify the column definition to make the column be + // defined with the "NOT NULL" qualifier. + if (pDb->Dbms() == dbmsMY_SQL) + { + wxString sqlStmt; + int i; + bool ok = TRUE; + for (i = 0; i < noIdxCols && ok; i++) + { + int j = 0; + bool found = FALSE; + // Find the column definition that has the ColName that matches the + // index column name. We need to do this to get the DB_DATA_TYPE of + // the index column, as MySQL's syntax for the ALTER column requires + // this information + while (!found && (j < this->noCols)) + { + if (wxStrcmp(colDefs[j].ColName,pIdxDefs[i].ColName) == 0) + found = TRUE; + if (!found) + j++; + } + + if (found) + { + wxString typeNameAndSize; + + switch(colDefs[j].DbDataType) + { + case DB_DATA_TYPE_VARCHAR: + typeNameAndSize = pDb->GetTypeInfVarchar().TypeName; + break; + case DB_DATA_TYPE_INTEGER: + typeNameAndSize = pDb->GetTypeInfInteger().TypeName; + break; + case DB_DATA_TYPE_FLOAT: + typeNameAndSize = pDb->GetTypeInfFloat().TypeName; + break; + case DB_DATA_TYPE_DATE: + typeNameAndSize = pDb->GetTypeInfDate().TypeName; + break; + } + + // For varchars, append the size of the string + if (colDefs[j].DbDataType == DB_DATA_TYPE_VARCHAR) + { + wxString s; + s.sprintf("(%d)", colDefs[i].SzDataObj); + typeNameAndSize += s.c_str(); + } + + sqlStmt.sprintf("ALTER TABLE %s MODIFY %s %s NOT NULL",tableName,pIdxDefs[i].ColName,typeNameAndSize.c_str()); + ok = pDb->ExecSql(sqlStmt.c_str()); + + if (!ok) + { + wxODBC_ERRORS retcode; + // Oracle returns a DB_ERR_GENERAL_ERROR if the column is already + // defined to be NOT NULL, but reportedly MySQL doesn't mind. + // This line is just here for debug checking of the value + retcode = (wxODBC_ERRORS)pDb->DB_STATUS; + } + } + else + ok = FALSE; + } + if (ok) + pDb->CommitTrans(); + else + { + pDb->RollbackTrans(); + return(FALSE); + } + } + + // Build a CREATE INDEX statement + sqlStmt = "CREATE "; + if (unique) + sqlStmt += "UNIQUE "; + + sqlStmt += "INDEX "; + sqlStmt += idxName; + sqlStmt += " ON "; + sqlStmt += tableName; + sqlStmt += " ("; + + // Append list of columns making up index + int i; + for (i = 0; i < noIdxCols; i++) + { + sqlStmt += pIdxDefs[i].ColName; + /* Postgres doesn't cope with ASC */ + if (pDb->Dbms() != dbmsPOSTGRES) + { + if (pIdxDefs[i].Ascending) + sqlStmt += " ASC"; + else + sqlStmt += " DESC"; + } + + if ((i + 1) < noIdxCols) + sqlStmt += ","; + } + + // Append closing parentheses + sqlStmt += ")"; + + pDb->WriteSqlLog(sqlStmt.c_str()); + +#ifdef DBDEBUG_CONSOLE + cout << endl << sqlStmt.c_str() << endl << endl; +#endif + + // Execute the CREATE INDEX statement + if (SQLExecDirect(hstmt, (UCHAR FAR *) sqlStmt.c_str(), SQL_NTS) != SQL_SUCCESS) + { + pDb->DispAllErrors(henv, hdbc, hstmt); + pDb->RollbackTrans(); + CloseCursor(hstmt); + return(FALSE); + } + + // Commit the transaction and close the cursor + if (! pDb->CommitTrans()) + return(FALSE); + if (! CloseCursor(hstmt)) + return(FALSE); + + // Index Created Successfully + return(TRUE); - return(execUpdate(pSqlStmt)); +} // wxDbTable::CreateIndex() -} // wxTable::Update(pSqlStmt) -/********** wxTable::Update() **********/ -bool wxTable::Update(void) +/********** wxDbTable::DropIndex() **********/ +bool wxDbTable::DropIndex(const char * idxName) { - char sqlStmt[DB_MAX_STATEMENT_LEN]; + // NOTE: This function returns TRUE if the Index does not exist, but + // only for identified databases. Code will need to be added + // below for any other databases when those databases are defined + // to handle this situation consistently - // Build the SQL UPDATE statement - GetUpdateStmt(sqlStmt, DB_UPD_KEYFIELDS); + wxString sqlStmt; -#ifdef _CONSOLE - cout << endl << sqlStmt << endl << endl; + if (pDb->Dbms() == dbmsACCESS || pDb->Dbms() == dbmsMY_SQL) + sqlStmt.sprintf("DROP INDEX %s ON %s",idxName,tableName); + else if ((pDb->Dbms() == dbmsMS_SQL_SERVER) || + (pDb->Dbms() == dbmsSYBASE_ASE)) + sqlStmt.sprintf("DROP INDEX %s.%s",tableName,idxName); + else + sqlStmt.sprintf("DROP INDEX %s",idxName); + + pDb->WriteSqlLog(sqlStmt.c_str()); + +#ifdef DBDEBUG_CONSOLE + cout << endl << sqlStmt.c_str() << endl; #endif - // Execute the SQL UPDATE statement - return(execUpdate(sqlStmt)); + if (SQLExecDirect(hstmt, (UCHAR FAR *) sqlStmt.c_str(), SQL_NTS) != SQL_SUCCESS) + { + // Check for "Index not found" error and ignore + pDb->GetNextError(henv, hdbc, hstmt); + if (wxStrcmp(pDb->sqlState,"S0012")) // "Index not found" + { + // Check for product specific error codes + if (!((pDb->Dbms() == dbmsSYBASE_ASA && !wxStrcmp(pDb->sqlState,"42000")) || // v5.x (and lower?) + (pDb->Dbms() == dbmsSYBASE_ASE && !wxStrcmp(pDb->sqlState,"37000")) || + (pDb->Dbms() == dbmsMS_SQL_SERVER && !wxStrcmp(pDb->sqlState,"S1000")) || + (pDb->Dbms() == dbmsSYBASE_ASE && !wxStrcmp(pDb->sqlState,"S0002")) || // Base table not found + (pDb->Dbms() == dbmsMY_SQL && !wxStrcmp(pDb->sqlState,"42S12")) || // tested by Christopher Ludwik Marino-Cebulski using v3.23.21beta + (pDb->Dbms() == dbmsPOSTGRES && !wxStrcmp(pDb->sqlState,"08S01")) + )) + { + pDb->DispNextError(); + pDb->DispAllErrors(henv, hdbc, hstmt); + pDb->RollbackTrans(); + CloseCursor(hstmt); + return(FALSE); + } + } + } + + // Commit the transaction and close the cursor + if (! pDb->CommitTrans()) + return(FALSE); + if (! CloseCursor(hstmt)) + return(FALSE); + + return(TRUE); +} // wxDbTable::DropIndex() + + +/********** wxDbTable::SetOrderByColNums() **********/ +bool wxDbTable::SetOrderByColNums(int first, ... ) +{ + int colNo = first; + va_list argptr; + + bool abort = FALSE; + wxString tempStr; + + va_start(argptr, first); /* Initialize variable arguments. */ + while (!abort && (colNo != wxDB_NO_MORE_COLUMN_NUMBERS)) + { + // Make sure the passed in column number + // is within the valid range of columns + // + // Valid columns are 0 thru noCols-1 + if (colNo >= noCols || colNo < 0) + { + abort = TRUE; + continue; + } -} // wxTable::Update() + if (colNo != first) + tempStr += ","; -/********** wxTable::UpdateWhere() **********/ -bool wxTable::UpdateWhere(char *pWhereClause) + tempStr += colDefs[colNo].ColName; + colNo = va_arg (argptr, int); + } + va_end (argptr); /* Reset variable arguments. */ + + SetOrderByClause(tempStr.c_str()); + + return (!abort); +} // wxDbTable::SetOrderByColNums() + + +/********** wxDbTable::Insert() **********/ +int wxDbTable::Insert(void) +{ + assert(!queryOnly); + if (queryOnly || !insertable) + return(DB_FAILURE); + + bindInsertParams(); + + // Insert the record by executing the already prepared insert statement + RETCODE retcode; + retcode=SQLExecute(hstmtInsert); + if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO) + { + // Check to see if integrity constraint was violated + pDb->GetNextError(henv, hdbc, hstmtInsert); + if (! wxStrcmp(pDb->sqlState, "23000")) // Integrity constraint violated + return(DB_ERR_INTEGRITY_CONSTRAINT_VIOL); + else + { + pDb->DispNextError(); + pDb->DispAllErrors(henv, hdbc, hstmtInsert); + return(DB_FAILURE); + } + } + + // Record inserted into the datasource successfully + return(DB_SUCCESS); + +} // wxDbTable::Insert() + + +/********** wxDbTable::Update() **********/ +bool wxDbTable::Update(void) { - char sqlStmt[DB_MAX_STATEMENT_LEN]; + assert(!queryOnly); + if (queryOnly) + return(FALSE); - // Build the SQL UPDATE statement - GetUpdateStmt(sqlStmt, DB_UPD_WHERE, pWhereClause); + char sqlStmt[DB_MAX_STATEMENT_LEN]; -#ifdef _CONSOLE - cout << endl << sqlStmt << endl << endl; + // Build the SQL UPDATE statement + BuildUpdateStmt(sqlStmt, DB_UPD_KEYFIELDS); + + pDb->WriteSqlLog(sqlStmt); + +#ifdef DBDEBUG_CONSOLE + cout << endl << sqlStmt << endl << endl; #endif - // Execute the SQL UPDATE statement - return(execUpdate(sqlStmt)); + // Execute the SQL UPDATE statement + return(execUpdate(sqlStmt)); + +} // wxDbTable::Update() -} // wxTable::UpdateWhere() -/********** wxTable::Delete() **********/ -bool wxTable::Delete(void) +/********** wxDbTable::Update(pSqlStmt) **********/ +bool wxDbTable::Update(const char *pSqlStmt) { - char sqlStmt[DB_MAX_STATEMENT_LEN]; + assert(!queryOnly); + if (queryOnly) + return(FALSE); - // Build the SQL DELETE statement - GetDeleteStmt(sqlStmt, DB_DEL_KEYFIELDS); + pDb->WriteSqlLog(pSqlStmt); - // Execute the SQL DELETE statement - return(execDelete(sqlStmt)); + return(execUpdate(pSqlStmt)); -} // wxTable::Delete() +} // wxDbTable::Update(pSqlStmt) -/********** wxTable::DeleteWhere() **********/ -bool wxTable::DeleteWhere(char *pWhereClause) + +/********** wxDbTable::UpdateWhere() **********/ +bool wxDbTable::UpdateWhere(const char *pWhereClause) { - char sqlStmt[DB_MAX_STATEMENT_LEN]; + assert(!queryOnly); + if (queryOnly) + return(FALSE); + + char sqlStmt[DB_MAX_STATEMENT_LEN]; - // Build the SQL DELETE statement - GetDeleteStmt(sqlStmt, DB_DEL_WHERE, pWhereClause); + // Build the SQL UPDATE statement + BuildUpdateStmt(sqlStmt, DB_UPD_WHERE, pWhereClause); - // Execute the SQL DELETE statement - return(execDelete(sqlStmt)); + pDb->WriteSqlLog(sqlStmt); -} // wxTable::DeleteWhere() +#ifdef DBDEBUG_CONSOLE + cout << endl << sqlStmt << endl << endl; +#endif + + // Execute the SQL UPDATE statement + return(execUpdate(sqlStmt)); + +} // wxDbTable::UpdateWhere() -/********** wxTable::DeleteMatching() **********/ -bool wxTable::DeleteMatching(void) + +/********** wxDbTable::Delete() **********/ +bool wxDbTable::Delete(void) { - char sqlStmt[DB_MAX_STATEMENT_LEN]; + assert(!queryOnly); + if (queryOnly) + return(FALSE); + + char sqlStmt[DB_MAX_STATEMENT_LEN]; - // Build the SQL DELETE statement - GetDeleteStmt(sqlStmt, DB_DEL_MATCHING); + // Build the SQL DELETE statement + BuildDeleteStmt(sqlStmt, DB_DEL_KEYFIELDS); - // Execute the SQL DELETE statement - return(execDelete(sqlStmt)); + pDb->WriteSqlLog(sqlStmt); -} // wxTable::DeleteMatching() + // Execute the SQL DELETE statement + return(execDelete(sqlStmt)); -/********** wxTable::execDelete() **********/ -bool wxTable::execDelete(char *pSqlStmt) +} // wxDbTable::Delete() + + +/********** wxDbTable::DeleteWhere() **********/ +bool wxDbTable::DeleteWhere(const char *pWhereClause) { - // Execute the DELETE statement - if (SQLExecDirect(hstmtDelete, (UCHAR FAR *) pSqlStmt, SQL_NTS) != SQL_SUCCESS) - return(pDb->DispAllErrors(henv, hdbc, hstmtDelete)); + assert(!queryOnly); + if (queryOnly) + return(FALSE); + + char sqlStmt[DB_MAX_STATEMENT_LEN]; + + // Build the SQL DELETE statement + BuildDeleteStmt(sqlStmt, DB_DEL_WHERE, pWhereClause); + + pDb->WriteSqlLog(sqlStmt); - // Record deleted successfully - return(TRUE); + // Execute the SQL DELETE statement + return(execDelete(sqlStmt)); -} // wxTable::execDelete() +} // wxDbTable::DeleteWhere() -/********** wxTable::execUpdate() **********/ -bool wxTable::execUpdate(char *pSqlStmt) + +/********** wxDbTable::DeleteMatching() **********/ +bool wxDbTable::DeleteMatching(void) { - // Execute the UPDATE statement - if (SQLExecDirect(hstmtUpdate, (UCHAR FAR *) pSqlStmt, SQL_NTS) != SQL_SUCCESS) - return(pDb->DispAllErrors(henv, hdbc, hstmtUpdate)); + assert(!queryOnly); + if (queryOnly) + return(FALSE); + + char sqlStmt[DB_MAX_STATEMENT_LEN]; + + // Build the SQL DELETE statement + BuildDeleteStmt(sqlStmt, DB_DEL_MATCHING); + + pDb->WriteSqlLog(sqlStmt); + + // Execute the SQL DELETE statement + return(execDelete(sqlStmt)); - // Record deleted successfully - return(TRUE); +} // wxDbTable::DeleteMatching() -} // wxTable::execUpdate() -/********** wxTable::GetUpdateStmt() **********/ -void wxTable::GetUpdateStmt(char *pSqlStmt, int typeOfUpd, char *pWhereClause) +/********** wxDbTable::BuildUpdateStmt() **********/ +void wxDbTable::BuildUpdateStmt(char *pSqlStmt, int typeOfUpd, const char *pWhereClause) { - char whereClause[DB_MAX_WHERE_CLAUSE_LEN]; - bool firstColumn = TRUE; - - whereClause[0] = 0; - sprintf(pSqlStmt, "UPDATE %s SET ", tableName); - - // Append a list of columns to be updated - for (int i = 0; i < noCols; i++) - { - // Only append Updateable columns - if (colDefs[i].Updateable) - { - if (! firstColumn) - strcat(pSqlStmt, ","); - else - firstColumn = FALSE; - strcat(pSqlStmt, colDefs[i].ColName); - strcat(pSqlStmt, " = ?"); - } - } - - // Append the WHERE clause to the SQL UPDATE statement - strcat(pSqlStmt, " WHERE "); - switch(typeOfUpd) - { - case DB_UPD_KEYFIELDS: - // If the datasource supports the ROWID column, build - // the where on ROWID for efficiency purposes. - // e.g. UPDATE PARTS SET C1 = ?, C2 = ? WHERE ROWID = '111.222.333' - if (CanUpdByROWID()) - { - SDWORD cb; - char rowid[ROWID_LEN]; - - // Get the ROWID value. If not successful retreiving the ROWID, - // simply fall down through the code and build the WHERE clause - // based on the key fields. - if (SQLGetData(hstmt, noCols+1, SQL_C_CHAR, rowid, ROWID_LEN, &cb) == SQL_SUCCESS) - { - strcat(pSqlStmt, "ROWID = '"); - strcat(pSqlStmt, rowid); - strcat(pSqlStmt, "'"); - break; - } - } - // Unable to delete by ROWID, so build a WHERE - // clause based on the keyfields. - GetWhereClause(whereClause, DB_WHERE_KEYFIELDS); - strcat(pSqlStmt, whereClause); - break; - case DB_UPD_WHERE: - strcat(pSqlStmt, pWhereClause); - break; - } - -} // GetUpdateStmt() - -/********** wxTable::GetDeleteStmt() **********/ -void wxTable::GetDeleteStmt(char *pSqlStmt, int typeOfDel, char *pWhereClause) + assert(!queryOnly); + if (queryOnly) + return; + + char whereClause[DB_MAX_WHERE_CLAUSE_LEN]; + bool firstColumn = TRUE; + + whereClause[0] = 0; + sprintf(pSqlStmt, "UPDATE %s SET ", tableName); + + // Append a list of columns to be updated + int i; + for (i = 0; i < noCols; i++) + { + // Only append Updateable columns + if (colDefs[i].Updateable) + { + if (! firstColumn) + wxStrcat(pSqlStmt, ","); + else + firstColumn = FALSE; + wxStrcat(pSqlStmt, colDefs[i].ColName); + wxStrcat(pSqlStmt, " = ?"); + } + } + + // Append the WHERE clause to the SQL UPDATE statement + wxStrcat(pSqlStmt, " WHERE "); + switch(typeOfUpd) + { + case DB_UPD_KEYFIELDS: + // If the datasource supports the ROWID column, build + // the where on ROWID for efficiency purposes. + // e.g. UPDATE PARTS SET Col1 = ?, Col2 = ? WHERE ROWID = '111.222.333' + if (CanUpdByROWID()) + { + SDWORD cb; + char rowid[wxDB_ROWID_LEN]; + + // Get the ROWID value. If not successful retreiving the ROWID, + // simply fall down through the code and build the WHERE clause + // based on the key fields. + if (SQLGetData(hstmt, noCols+1, SQL_C_CHAR, (UCHAR*) rowid, wxDB_ROWID_LEN, &cb) == SQL_SUCCESS) + { + wxStrcat(pSqlStmt, "ROWID = '"); + wxStrcat(pSqlStmt, rowid); + wxStrcat(pSqlStmt, "'"); + break; + } + } + // Unable to delete by ROWID, so build a WHERE + // clause based on the keyfields. + BuildWhereClause(whereClause, DB_WHERE_KEYFIELDS); + wxStrcat(pSqlStmt, whereClause); + break; + case DB_UPD_WHERE: + wxStrcat(pSqlStmt, pWhereClause); + break; + } +} // BuildUpdateStmt() + + +/********** wxDbTable::BuildDeleteStmt() **********/ +void wxDbTable::BuildDeleteStmt(char *pSqlStmt, int typeOfDel, const char *pWhereClause) { - char whereClause[DB_MAX_WHERE_CLAUSE_LEN]; - - whereClause[0] = 0; - - // Handle the case of DeleteWhere() and the where clause is blank. It should - // delete all records from the database in this case. - if (typeOfDel == DB_DEL_WHERE && (pWhereClause == 0 || strlen(pWhereClause) == 0)) - { - sprintf(pSqlStmt, "DELETE FROM %s", tableName); - return; - } - - sprintf(pSqlStmt, "DELETE FROM %s WHERE ", tableName); - - // Append the WHERE clause to the SQL DELETE statement - switch(typeOfDel) - { - case DB_DEL_KEYFIELDS: - // If the datasource supports the ROWID column, build - // the where on ROWID for efficiency purposes. - // e.g. DELETE FROM PARTS WHERE ROWID = '111.222.333' - if (CanUpdByROWID()) - { - SDWORD cb; - char rowid[ROWID_LEN]; - - // Get the ROWID value. If not successful retreiving the ROWID, - // simply fall down through the code and build the WHERE clause - // based on the key fields. - if (SQLGetData(hstmt, noCols+1, SQL_C_CHAR, rowid, ROWID_LEN, &cb) == SQL_SUCCESS) - { - strcat(pSqlStmt, "ROWID = '"); - strcat(pSqlStmt, rowid); - strcat(pSqlStmt, "'"); - break; - } - } - // Unable to delete by ROWID, so build a WHERE - // clause based on the keyfields. - GetWhereClause(whereClause, DB_WHERE_KEYFIELDS); - strcat(pSqlStmt, whereClause); - break; - case DB_DEL_WHERE: - strcat(pSqlStmt, pWhereClause); - break; - case DB_DEL_MATCHING: - GetWhereClause(whereClause, DB_WHERE_MATCHING); - strcat(pSqlStmt, whereClause); - break; - } - -} // GetDeleteStmt() - -/********** wxTable::GetWhereClause() **********/ + assert(!queryOnly); + if (queryOnly) + return; + + char whereClause[DB_MAX_WHERE_CLAUSE_LEN]; + + whereClause[0] = 0; + + // Handle the case of DeleteWhere() and the where clause is blank. It should + // delete all records from the database in this case. + if (typeOfDel == DB_DEL_WHERE && (pWhereClause == 0 || wxStrlen(pWhereClause) == 0)) + { + sprintf(pSqlStmt, "DELETE FROM %s", tableName); + return; + } + + sprintf(pSqlStmt, "DELETE FROM %s WHERE ", tableName); + + // Append the WHERE clause to the SQL DELETE statement + switch(typeOfDel) + { + case DB_DEL_KEYFIELDS: + // If the datasource supports the ROWID column, build + // the where on ROWID for efficiency purposes. + // e.g. DELETE FROM PARTS WHERE ROWID = '111.222.333' + if (CanUpdByROWID()) + { + SDWORD cb; + char rowid[wxDB_ROWID_LEN]; + + // Get the ROWID value. If not successful retreiving the ROWID, + // simply fall down through the code and build the WHERE clause + // based on the key fields. + if (SQLGetData(hstmt, noCols+1, SQL_C_CHAR, (UCHAR*) rowid, wxDB_ROWID_LEN, &cb) == SQL_SUCCESS) + { + wxStrcat(pSqlStmt, "ROWID = '"); + wxStrcat(pSqlStmt, rowid); + wxStrcat(pSqlStmt, "'"); + break; + } + } + // Unable to delete by ROWID, so build a WHERE + // clause based on the keyfields. + BuildWhereClause(whereClause, DB_WHERE_KEYFIELDS); + wxStrcat(pSqlStmt, whereClause); + break; + case DB_DEL_WHERE: + wxStrcat(pSqlStmt, pWhereClause); + break; + case DB_DEL_MATCHING: + BuildWhereClause(whereClause, DB_WHERE_MATCHING); + wxStrcat(pSqlStmt, whereClause); + break; + } + +} // BuildDeleteStmt() + + +/********** wxDbTable::BuildWhereClause() **********/ +void wxDbTable::BuildWhereClause(char *pWhereClause, int typeOfWhere, + const char *qualTableName, bool useLikeComparison) /* - * Note: GetWhereClause() currently ignores timestamp columns. + * Note: BuildWhereClause() currently ignores timestamp columns. * They are not included as part of the where clause. */ +{ + bool moreThanOneColumn = FALSE; + char colValue[255]; + + // Loop through the columns building a where clause as you go + int i; + for (i = 0; i < noCols; i++) + { + // Determine if this column should be included in the WHERE clause + if ((typeOfWhere == DB_WHERE_KEYFIELDS && colDefs[i].KeyField) || + (typeOfWhere == DB_WHERE_MATCHING && (!IsColNull(i)))) + { + // Skip over timestamp columns + if (colDefs[i].SqlCtype == SQL_C_TIMESTAMP) + continue; + // If there is more than 1 column, join them with the keyword "AND" + if (moreThanOneColumn) + wxStrcat(pWhereClause, " AND "); + else + moreThanOneColumn = TRUE; + // Concatenate where phrase for the column + if (qualTableName && wxStrlen(qualTableName)) + { + wxStrcat(pWhereClause, qualTableName); + wxStrcat(pWhereClause, "."); + } + wxStrcat(pWhereClause, colDefs[i].ColName); + if (useLikeComparison && (colDefs[i].SqlCtype == SQL_C_CHAR)) + wxStrcat(pWhereClause, " LIKE "); + else + wxStrcat(pWhereClause, " = "); + switch(colDefs[i].SqlCtype) + { + case SQL_C_CHAR: + sprintf(colValue, "'%s'", (UCHAR FAR *) colDefs[i].PtrDataObj); + break; + case SQL_C_SSHORT: + sprintf(colValue, "%hi", *((SWORD *) colDefs[i].PtrDataObj)); + break; + case SQL_C_USHORT: + sprintf(colValue, "%hu", *((UWORD *) colDefs[i].PtrDataObj)); + break; + case SQL_C_SLONG: + sprintf(colValue, "%li", *((SDWORD *) colDefs[i].PtrDataObj)); + break; + case SQL_C_ULONG: + sprintf(colValue, "%lu", *((UDWORD *) colDefs[i].PtrDataObj)); + break; + case SQL_C_FLOAT: + sprintf(colValue, "%.6f", *((SFLOAT *) colDefs[i].PtrDataObj)); + break; + case SQL_C_DOUBLE: + sprintf(colValue, "%.6f", *((SDOUBLE *) colDefs[i].PtrDataObj)); + break; + } + wxStrcat(pWhereClause, colValue); + } + } +} // wxDbTable::BuildWhereClause() + + +/********** wxDbTable::IsColNull() **********/ +bool wxDbTable::IsColNull(int colNo) +{ +/* + This logic is just not right. It would indicate TRUE + if a numeric field were set to a value of 0. + + switch(colDefs[colNo].SqlCtype) + { + case SQL_C_CHAR: + return(((UCHAR FAR *) colDefs[colNo].PtrDataObj)[0] == 0); + case SQL_C_SSHORT: + return(( *((SWORD *) colDefs[colNo].PtrDataObj)) == 0); + case SQL_C_USHORT: + return(( *((UWORD*) colDefs[colNo].PtrDataObj)) == 0); + case SQL_C_SLONG: + return(( *((SDWORD *) colDefs[colNo].PtrDataObj)) == 0); + case SQL_C_ULONG: + return(( *((UDWORD *) colDefs[colNo].PtrDataObj)) == 0); + case SQL_C_FLOAT: + return(( *((SFLOAT *) colDefs[colNo].PtrDataObj)) == 0); + case SQL_C_DOUBLE: + return((*((SDOUBLE *) colDefs[colNo].PtrDataObj)) == 0); + case SQL_C_TIMESTAMP: + TIMESTAMP_STRUCT *pDt; + pDt = (TIMESTAMP_STRUCT *) colDefs[colNo].PtrDataObj; + if (pDt->year == 0 && pDt->month == 0 && pDt->day == 0) + return(TRUE); + else + return(FALSE); + default: + return(TRUE); + } +*/ + return (colDefs[colNo].Null); +} // wxDbTable::IsColNull() + -void wxTable::GetWhereClause(char *pWhereClause, int typeOfWhere) +/********** wxDbTable::CanSelectForUpdate() **********/ +bool wxDbTable::CanSelectForUpdate(void) { - bool moreThanOneColumn = FALSE; - wxString colValue; - - // Loop through the columns building a where clause as you go - for (int i = 0; i < noCols; i++) - { - // Determine if this column should be included in the WHERE clause - if ((typeOfWhere == DB_WHERE_KEYFIELDS && colDefs[i].KeyField) || - (typeOfWhere == DB_WHERE_MATCHING && (! IsColNull(i)))) - { - // Skip over timestamp columns - if (colDefs[i].SqlCtype == SQL_C_TIMESTAMP) - continue; - // If there is more than 1 column, join them with the keyword "AND" - if (moreThanOneColumn) - strcat(pWhereClause, " AND "); - else - moreThanOneColumn = TRUE; - // Concatenate where phrase for the column - strcat(pWhereClause, colDefs[i].ColName); - strcat(pWhereClause, " = "); - switch(colDefs[i].SqlCtype) - { - case SQL_C_CHAR: - colValue.Printf("'%s'", (UCHAR FAR *) colDefs[i].PtrDataObj); - break; - case SQL_C_SSHORT: - colValue.Printf("%hi", *((SWORD *) colDefs[i].PtrDataObj)); - break; - case SQL_C_USHORT: - colValue.Printf("%hu", *((UWORD *) colDefs[i].PtrDataObj)); - break; - case SQL_C_SLONG: - colValue.Printf("%li", *((SDWORD *) colDefs[i].PtrDataObj)); - break; - case SQL_C_ULONG: - colValue.Printf("%lu", *((UDWORD *) colDefs[i].PtrDataObj)); - break; - case SQL_C_FLOAT: - colValue.Printf("%.6f", *((SFLOAT *) colDefs[i].PtrDataObj)); - break; - case SQL_C_DOUBLE: - colValue.Printf("%.6f", *((SDOUBLE *) colDefs[i].PtrDataObj)); - break; - } - strcat(pWhereClause, colValue); - } - } - -} // wxTable::GetWhereClause() - -/********** wxTable::IsColNull() **********/ -bool wxTable::IsColNull(int colNo) + if (queryOnly) + return FALSE; + + if (pDb->Dbms() == dbmsMY_SQL) + return FALSE; + + if ((pDb->Dbms() == dbmsORACLE) || + (pDb->dbInf.posStmts & SQL_PS_SELECT_FOR_UPDATE)) + return(TRUE); + else + return(FALSE); + +} // wxDbTable::CanSelectForUpdate() + + +/********** wxDbTable::CanUpdByROWID() **********/ +bool wxDbTable::CanUpdByROWID(void) { - switch(colDefs[colNo].SqlCtype) - { - case SQL_C_CHAR: - return(((UCHAR FAR *) colDefs[colNo].PtrDataObj)[0] == 0); - case SQL_C_SSHORT: - return(( *((SWORD *) colDefs[colNo].PtrDataObj)) == 0); - case SQL_C_USHORT: - return(( *((UWORD*) colDefs[colNo].PtrDataObj)) == 0); - case SQL_C_SLONG: - return(( *((SDWORD *) colDefs[colNo].PtrDataObj)) == 0); - case SQL_C_ULONG: - return(( *((UDWORD *) colDefs[colNo].PtrDataObj)) == 0); - case SQL_C_FLOAT: - return(( *((SFLOAT *) colDefs[colNo].PtrDataObj)) == 0); - case SQL_C_DOUBLE: - return((*((SDOUBLE *) colDefs[colNo].PtrDataObj)) == 0); - case SQL_C_TIMESTAMP: - TIMESTAMP_STRUCT *pDt; - pDt = (TIMESTAMP_STRUCT *) colDefs[colNo].PtrDataObj; - if (pDt->year == 0 && pDt->month == 0 && pDt->day == 0) - return(TRUE); - else - return(FALSE); - default: - return(TRUE); - } - -} // wxTable::IsColNull() - -/********** wxTable::CanSelectForUpdate() **********/ - -bool wxTable::CanSelectForUpdate(void) +/* + * NOTE: Returning FALSE for now until this can be debugged, + * as the ROWID is not getting updated correctly + */ + return FALSE; + + if (pDb->Dbms() == dbmsORACLE) + return(TRUE); + else + return(FALSE); + +} // wxDbTable::CanUpdByROWID() + + +/********** wxDbTable::IsCursorClosedOnCommit() **********/ +bool wxDbTable::IsCursorClosedOnCommit(void) { - if (pDb->dbInf.posStmts & SQL_PS_SELECT_FOR_UPDATE) - return(TRUE); - else - return(FALSE); + if (pDb->dbInf.cursorCommitBehavior == SQL_CB_PRESERVE) + return(FALSE); + else + return(TRUE); + +} // wxDbTable::IsCursorClosedOnCommit() + -} // wxTable::CanSelectForUpdate() -/********** wxTable::CanUpdByROWID() **********/ -bool wxTable::CanUpdByROWID(void) +/********** wxDbTable::ClearMemberVar() **********/ +void wxDbTable::ClearMemberVar(int colNo, bool setToNull) { + assert(colNo < noCols); + + switch(colDefs[colNo].SqlCtype) + { + case SQL_C_CHAR: + ((UCHAR FAR *) colDefs[colNo].PtrDataObj)[0] = 0; + break; + case SQL_C_SSHORT: + *((SWORD *) colDefs[colNo].PtrDataObj) = 0; + break; + case SQL_C_USHORT: + *((UWORD*) colDefs[colNo].PtrDataObj) = 0; + break; + case SQL_C_SLONG: + *((SDWORD *) colDefs[colNo].PtrDataObj) = 0; + break; + case SQL_C_ULONG: + *((UDWORD *) colDefs[colNo].PtrDataObj) = 0; + break; + case SQL_C_FLOAT: + *((SFLOAT *) colDefs[colNo].PtrDataObj) = 0.0f; + break; + case SQL_C_DOUBLE: + *((SDOUBLE *) colDefs[colNo].PtrDataObj) = 0.0f; + break; + case SQL_C_TIMESTAMP: + TIMESTAMP_STRUCT *pDt; + pDt = (TIMESTAMP_STRUCT *) colDefs[colNo].PtrDataObj; + pDt->year = 0; + pDt->month = 0; + pDt->day = 0; + pDt->hour = 0; + pDt->minute = 0; + pDt->second = 0; + pDt->fraction = 0; + break; + } + + if (setToNull) + SetColNull(colNo); +} // wxDbTable::ClearMemberVar() + + +/********** wxDbTable::ClearMemberVars() **********/ +void wxDbTable::ClearMemberVars(bool setToNull) +{ + int i; -//@@@@@@glt - returning FALSE for testing purposes, as the ROWID is not getting updated correctly - return FALSE; + // Loop through the columns setting each member variable to zero + for (i=0; i < noCols; i++) + ClearMemberVar(i,setToNull); - if ((! strcmp(pDb->dbInf.dbmsName, "Oracle")) || (! strcmp(pDb->dbInf.dbmsName, "ORACLE"))) - return(TRUE); - else - return(FALSE); +} // wxDbTable::ClearMemberVars() -} // wxTable::CanUpdByROWID() -/********** wxTable::IsCursorClosedOnCommit() **********/ -bool wxTable::IsCursorClosedOnCommit(void) +/********** wxDbTable::SetQueryTimeout() **********/ +bool wxDbTable::SetQueryTimeout(UDWORD nSeconds) { - if (pDb->dbInf.cursorCommitBehavior == SQL_CB_PRESERVE) - return(FALSE); - else - return(TRUE); + if (SQLSetStmtOption(hstmtInsert, SQL_QUERY_TIMEOUT, nSeconds) != SQL_SUCCESS) + return(pDb->DispAllErrors(henv, hdbc, hstmtInsert)); + if (SQLSetStmtOption(hstmtUpdate, SQL_QUERY_TIMEOUT, nSeconds) != SQL_SUCCESS) + return(pDb->DispAllErrors(henv, hdbc, hstmtUpdate)); + if (SQLSetStmtOption(hstmtDelete, SQL_QUERY_TIMEOUT, nSeconds) != SQL_SUCCESS) + return(pDb->DispAllErrors(henv, hdbc, hstmtDelete)); + if (SQLSetStmtOption(hstmtInternal, SQL_QUERY_TIMEOUT, nSeconds) != SQL_SUCCESS) + return(pDb->DispAllErrors(henv, hdbc, hstmtInternal)); + + // Completed Successfully + return(TRUE); -} // wxTable::IsCursorClosedOnCommit() +} // wxDbTable::SetQueryTimeout() -/********** wxTable::ClearMemberVars() **********/ -void wxTable::ClearMemberVars(void) + +/********** wxDbTable::SetColDefs() **********/ +void wxDbTable::SetColDefs(int index, const char *fieldName, int dataType, void *pData, + int cType, int size, bool keyField, bool upd, + bool insAllow, bool derivedCol) +{ + if (!colDefs) // May happen if the database connection fails + return; + + if (wxStrlen(fieldName) > (unsigned int) DB_MAX_COLUMN_NAME_LEN) + { + wxStrncpy (colDefs[index].ColName, fieldName, DB_MAX_COLUMN_NAME_LEN); + colDefs[index].ColName[DB_MAX_COLUMN_NAME_LEN] = 0; + } + else + wxStrcpy(colDefs[index].ColName, fieldName); + + colDefs[index].DbDataType = dataType; + colDefs[index].PtrDataObj = pData; + colDefs[index].SqlCtype = cType; + colDefs[index].SzDataObj = size; + colDefs[index].KeyField = keyField; + colDefs[index].DerivedCol = derivedCol; + // Derived columns by definition would NOT be "Insertable" or "Updateable" + if (derivedCol) + { + colDefs[index].Updateable = FALSE; + colDefs[index].InsertAllowed = FALSE; + } + else + { + colDefs[index].Updateable = upd; + colDefs[index].InsertAllowed = insAllow; + } + + colDefs[index].Null = FALSE; + +} // wxDbTable::SetColDefs() + + +/********** wxDbTable::SetColDefs() **********/ +wxDbColDataPtr* wxDbTable::SetColDefs(wxDbColInf *pColInfs, ULONG numCols) { - // Loop through the columns setting each member variable to zero - for (int i = 0; i < noCols; i++) - { - switch(colDefs[i].SqlCtype) - { - case SQL_C_CHAR: - ((UCHAR FAR *) colDefs[i].PtrDataObj)[0] = 0; - break; - case SQL_C_SSHORT: - *((SWORD *) colDefs[i].PtrDataObj) = 0; - break; - case SQL_C_USHORT: - *((UWORD*) colDefs[i].PtrDataObj) = 0; - break; - case SQL_C_SLONG: - *((SDWORD *) colDefs[i].PtrDataObj) = 0; - break; - case SQL_C_ULONG: - *((UDWORD *) colDefs[i].PtrDataObj) = 0; - break; - case SQL_C_FLOAT: - *((SFLOAT *) colDefs[i].PtrDataObj) = 0.0f; - break; - case SQL_C_DOUBLE: - *((SDOUBLE *) colDefs[i].PtrDataObj) = 0.0f; - break; - case SQL_C_TIMESTAMP: - TIMESTAMP_STRUCT *pDt; - pDt = (TIMESTAMP_STRUCT *) colDefs[i].PtrDataObj; - pDt->year = 0; - pDt->month = 0; - pDt->day = 0; - pDt->hour = 0; - pDt->minute = 0; - pDt->second = 0; - pDt->fraction = 0; - break; - } - } - -} // wxTable::ClearMemberVars() - -/********** wxTable::SetQueryTimeout() **********/ -bool wxTable::SetQueryTimeout(UDWORD nSeconds) + assert(pColInfs); + wxDbColDataPtr *pColDataPtrs = NULL; + + if (pColInfs) + { + ULONG index; + + pColDataPtrs = new wxDbColDataPtr[numCols+1]; + + for (index = 0; index < numCols; index++) + { + // Process the fields + switch (pColInfs[index].dbDataType) + { + case DB_DATA_TYPE_VARCHAR: + pColDataPtrs[index].PtrDataObj = new char[pColInfs[index].bufferLength+1]; + pColDataPtrs[index].SzDataObj = pColInfs[index].columnSize; + pColDataPtrs[index].SqlCtype = SQL_C_CHAR; + break; + case DB_DATA_TYPE_INTEGER: + // Can be long or short + if (pColInfs[index].bufferLength == sizeof(long)) + { + pColDataPtrs[index].PtrDataObj = new long; + pColDataPtrs[index].SzDataObj = sizeof(long); + pColDataPtrs[index].SqlCtype = SQL_C_SLONG; + } + else + { + pColDataPtrs[index].PtrDataObj = new short; + pColDataPtrs[index].SzDataObj = sizeof(short); + pColDataPtrs[index].SqlCtype = SQL_C_SSHORT; + } + break; + case DB_DATA_TYPE_FLOAT: + // Can be float or double + if (pColInfs[index].bufferLength == sizeof(float)) + { + pColDataPtrs[index].PtrDataObj = new float; + pColDataPtrs[index].SzDataObj = sizeof(float); + pColDataPtrs[index].SqlCtype = SQL_C_FLOAT; + } + else + { + pColDataPtrs[index].PtrDataObj = new double; + pColDataPtrs[index].SzDataObj = sizeof(double); + pColDataPtrs[index].SqlCtype = SQL_C_DOUBLE; + } + break; + case DB_DATA_TYPE_DATE: + pColDataPtrs[index].PtrDataObj = new TIMESTAMP_STRUCT; + pColDataPtrs[index].SzDataObj = sizeof(TIMESTAMP_STRUCT); + pColDataPtrs[index].SqlCtype = SQL_C_TIMESTAMP; + break; + } + SetColDefs (index,pColInfs[index].colName,pColInfs[index].dbDataType, pColDataPtrs[index].PtrDataObj, pColDataPtrs[index].SqlCtype, pColDataPtrs[index].SzDataObj); + } + } + + return (pColDataPtrs); + +} // wxDbTable::SetColDefs() + + +/********** wxDbTable::SetCursor() **********/ +void wxDbTable::SetCursor(HSTMT *hstmtActivate) { - if (SQLSetStmtOption(c0, SQL_QUERY_TIMEOUT, nSeconds) != SQL_SUCCESS) - return(pDb->DispAllErrors(henv, hdbc, c0)); - if (SQLSetStmtOption(c1, SQL_QUERY_TIMEOUT, nSeconds) != SQL_SUCCESS) - return(pDb->DispAllErrors(henv, hdbc, c1)); - if (SQLSetStmtOption(c2, SQL_QUERY_TIMEOUT, nSeconds) != SQL_SUCCESS) - return(pDb->DispAllErrors(henv, hdbc, c2)); -// if (SQLSetStmtOption(c3, SQL_QUERY_TIMEOUT, nSeconds) != SQL_SUCCESS) -// return(pDb->DispAllErrors(henv, hdbc, c3)); -// if (SQLSetStmtOption(c4, SQL_QUERY_TIMEOUT, nSeconds) != SQL_SUCCESS) -// return(pDb->DispAllErrors(henv, hdbc, c4)); -// if (SQLSetStmtOption(c5, SQL_QUERY_TIMEOUT, nSeconds) != SQL_SUCCESS) -// return(pDb->DispAllErrors(henv, hdbc, c5)); - if (SQLSetStmtOption(hstmtInsert, SQL_QUERY_TIMEOUT, nSeconds) != SQL_SUCCESS) - return(pDb->DispAllErrors(henv, hdbc, hstmtInsert)); - if (SQLSetStmtOption(hstmtUpdate, SQL_QUERY_TIMEOUT, nSeconds) != SQL_SUCCESS) - return(pDb->DispAllErrors(henv, hdbc, hstmtUpdate)); - if (SQLSetStmtOption(hstmtDelete, SQL_QUERY_TIMEOUT, nSeconds) != SQL_SUCCESS) - return(pDb->DispAllErrors(henv, hdbc, hstmtDelete)); - if (SQLSetStmtOption(hstmtCount, SQL_QUERY_TIMEOUT, nSeconds) != SQL_SUCCESS) - return(pDb->DispAllErrors(henv, hdbc, hstmtCount)); - - // Completed Successfully - return(TRUE); - -} // wxTable::SetQueryTimeout() - -/********** wxTable::SetColDefs() **********/ -void wxTable::SetColDefs (int index, char *fieldName, int dataType, void *pData, - int cType, int size, bool keyField, bool upd, - bool insAllow, bool derivedCol) + if (hstmtActivate == wxDB_DEFAULT_CURSOR) + hstmt = *hstmtDefault; + else + hstmt = *hstmtActivate; + +} // wxDbTable::SetCursor() + + +/********** wxDbTable::Count(const char *) **********/ +ULONG wxDbTable::Count(const char *args) { - if (strlen(fieldName) > DB_MAX_COLUMN_NAME_LEN) // glt 4/21/97 - { - strncpy (colDefs[index].ColName, fieldName, DB_MAX_COLUMN_NAME_LEN); - colDefs[index].ColName[DB_MAX_COLUMN_NAME_LEN] = 0; // glt 10/23/97 - } - else - strcpy(colDefs[index].ColName, fieldName); - - colDefs[index].DbDataType = dataType; - colDefs[index].PtrDataObj = pData; - colDefs[index].SqlCtype = cType; - colDefs[index].SzDataObj = size; - colDefs[index].KeyField = keyField; - colDefs[index].DerivedCol = derivedCol; - // Derived columns by definition would NOT be "Insertable" or "Updateable" - if (derivedCol) - { - colDefs[index].Updateable = FALSE; - colDefs[index].InsertAllowed = FALSE; - } - else - { - colDefs[index].Updateable = upd; - colDefs[index].InsertAllowed = insAllow; - } - -} // wxTable::SetColDefs() - -/********** wxTable::SetCursor() **********/ -bool wxTable::SetCursor(int cursorNo) + ULONG count; + wxString sqlStmt; + SDWORD cb; + + // Build a "SELECT COUNT(*) FROM queryTableName [WHERE whereClause]" SQL Statement + sqlStmt = "SELECT COUNT("; + sqlStmt += args; + sqlStmt += ") FROM "; + sqlStmt += queryTableName; +#if wxODBC_BACKWARD_COMPATABILITY + if (from && wxStrlen(from)) +#else + if (from.Length()) +#endif + sqlStmt += from; + + // Add the where clause if one is provided +#if wxODBC_BACKWARD_COMPATABILITY + if (where && wxStrlen(where)) +#else + if (where.Length()) +#endif + { + sqlStmt += " WHERE "; + sqlStmt += where; + } + + pDb->WriteSqlLog(sqlStmt.c_str()); + + // Initialize the Count cursor if it's not already initialized + if (!hstmtCount) + { + hstmtCount = GetNewCursor(FALSE,FALSE); + assert(hstmtCount); + if (!hstmtCount) + return(0); + } + + // Execute the SQL statement + if (SQLExecDirect(*hstmtCount, (UCHAR FAR *) sqlStmt.c_str(), SQL_NTS) != SQL_SUCCESS) + { + pDb->DispAllErrors(henv, hdbc, *hstmtCount); + return(0); + } + + // Fetch the record + if (SQLFetch(*hstmtCount) != SQL_SUCCESS) + { + pDb->DispAllErrors(henv, hdbc, *hstmtCount); + return(0); + } + + // Obtain the result + if (SQLGetData(*hstmtCount, 1, SQL_C_ULONG, &count, sizeof(count), &cb) != SQL_SUCCESS) + { + pDb->DispAllErrors(henv, hdbc, *hstmtCount); + return(0); + } + + // Free the cursor + if (SQLFreeStmt(*hstmtCount, SQL_CLOSE) != SQL_SUCCESS) + pDb->DispAllErrors(henv, hdbc, *hstmtCount); + + // Return the record count + return(count); + +} // wxDbTable::Count() + + +/********** wxDbTable::Refresh() **********/ +bool wxDbTable::Refresh(void) { - switch(cursorNo) - { - case DB_CURSOR0: - hstmt = c0; - // currCursorNo doesn't change since Cursor0 is a temp cursor - break; - case DB_CURSOR1: - hstmt = c1; - currCursorNo = DB_CURSOR1; - break; - case DB_CURSOR2: - hstmt = c2; - currCursorNo = DB_CURSOR2; - break; -// case DB_CURSOR3: -// hstmt = c3; -// currCursorNo = DB_CURSOR3; -// break; -// case DB_CURSOR4: -// hstmt = c4; -// currCursorNo = DB_CURSOR4; -// break; -// case DB_CURSOR5: -// hstmt = c5; -// currCursorNo = DB_CURSOR5; -// break; - default: - return(FALSE); - } - - // Refresh the current record -#ifndef FWD_ONLY_CURSORS - UDWORD cRowsFetched; - UWORD rowStatus; - SQLExtendedFetch(hstmt, SQL_FETCH_NEXT, 0, &cRowsFetched, &rowStatus); - SQLExtendedFetch(hstmt, SQL_FETCH_PRIOR, 0, &cRowsFetched, &rowStatus); + bool result = TRUE; + + // Switch to the internal cursor so any active cursors are not corrupted + HSTMT currCursor = GetCursor(); + hstmt = hstmtInternal; +#if wxODBC_BACKWARD_COMPATABILITY + // Save the where and order by clauses + char *saveWhere = where; + char *saveOrderBy = orderBy; +#else + wxString saveWhere = where; + wxString saveOrderBy = orderBy; #endif + // Build a where clause to refetch the record with. Try and use the + // ROWID if it's available, ow use the key fields. + char whereClause[DB_MAX_WHERE_CLAUSE_LEN+1]; + wxStrcpy(whereClause, ""); + if (CanUpdByROWID()) + { + SDWORD cb; + char rowid[wxDB_ROWID_LEN+1]; + + // Get the ROWID value. If not successful retreiving the ROWID, + // simply fall down through the code and build the WHERE clause + // based on the key fields. + if (SQLGetData(hstmt, noCols+1, SQL_C_CHAR, (UCHAR*) rowid, wxDB_ROWID_LEN, &cb) == SQL_SUCCESS) + { + wxStrcat(whereClause, queryTableName); + wxStrcat(whereClause, ".ROWID = '"); + wxStrcat(whereClause, rowid); + wxStrcat(whereClause, "'"); + } + } + + // If unable to use the ROWID, build a where clause from the keyfields + if (wxStrlen(whereClause) == 0) + BuildWhereClause(whereClause, DB_WHERE_KEYFIELDS, queryTableName); + + // Requery the record + where = whereClause; + orderBy = ""; + if (!Query()) + result = FALSE; + + if (result && !GetNext()) + result = FALSE; + + // Switch back to original cursor + SetCursor(&currCursor); + + // Free the internal cursor + if (SQLFreeStmt(hstmtInternal, SQL_CLOSE) != SQL_SUCCESS) + pDb->DispAllErrors(henv, hdbc, hstmtInternal); + + // Restore the original where and order by clauses + where = saveWhere; + orderBy = saveOrderBy; + + return(result); + +} // wxDbTable::Refresh() + + +/********** wxDbTable::SetColNull(int colNo, bool set) **********/ +bool wxDbTable::SetColNull(int colNo, bool set) +{ + if (colNo < noCols) + { + colDefs[colNo].Null = set; + if (set) // Blank out the values in the member variable + ClearMemberVar(colNo,FALSE); // Must call with FALSE, or infinite recursion will happen + return(TRUE); + } + else + return(FALSE); - // Completed successfully - return(TRUE); +} // wxDbTable::SetColNull(int colNo) -} // wxTable::SetCursor() -/********** wxTable::Count() **********/ -ULONG wxTable::Count(void) +/********** wxDbTable::SetColNull(char *colName, bool set) **********/ +bool wxDbTable::SetColNull(const char *colName, bool set) +{ + int i; + for (i = 0; i < noCols; i++) + { + if (!wxStricmp(colName, colDefs[i].ColName)) + break; + } + + if (i < noCols) + { + colDefs[i].Null = set; + if (set) // Blank out the values in the member variable + ClearMemberVar(i,FALSE); // Must call with FALSE, or infinite recursion will happen + return(TRUE); + } + else + return(FALSE); + +} // wxDbTable::SetColNull(char *colName) + + +/********** wxDbTable::GetNewCursor() **********/ +HSTMT *wxDbTable::GetNewCursor(bool setCursor, bool bindColumns) { - ULONG l; - char sqlStmt[DB_MAX_STATEMENT_LEN]; - SDWORD cb; - - // Build a "SELECT COUNT(*) FROM queryTableName [WHERE whereClause]" SQL Statement - strcpy(sqlStmt, "SELECT COUNT(*) FROM "); - strcat(sqlStmt, queryTableName); - - // Add the where clause if one is provided - if (where && strlen(where)) - { - strcat(sqlStmt, " WHERE "); - strcat(sqlStmt, where); - } - - // Execute the SQL statement - if (SQLExecDirect(hstmtCount, (UCHAR FAR *) sqlStmt, SQL_NTS) != SQL_SUCCESS) - { - pDb->DispAllErrors(henv, hdbc, hstmtCount); - return(0); - } - - // Fetch the record - if (SQLFetch(hstmtCount) != SQL_SUCCESS) - { - pDb->DispAllErrors(henv, hdbc, hstmtCount); - return(0); - } - - // Obtain the result - if (SQLGetData(hstmtCount, 1, SQL_C_ULONG, &l, sizeof(l), &cb) != SQL_SUCCESS) - { - pDb->DispAllErrors(henv, hdbc, hstmtCount); - return(0); - } - - // Free the cursor - if (SQLFreeStmt(hstmtCount, SQL_CLOSE) != SQL_SUCCESS) - pDb->DispAllErrors(henv, hdbc, hstmtCount); - - // Return the record count - return(l); - -} // wxTable::Count() - -/********** wxTable::Refresh() **********/ -bool wxTable::Refresh(void) + HSTMT *newHSTMT = new HSTMT; + assert(newHSTMT); + if (!newHSTMT) + return(0); + + if (SQLAllocStmt(hdbc, newHSTMT) != SQL_SUCCESS) + { + pDb->DispAllErrors(henv, hdbc); + delete newHSTMT; + return(0); + } + + if (SQLSetStmtOption(*newHSTMT, SQL_CURSOR_TYPE, cursorType) != SQL_SUCCESS) + { + pDb->DispAllErrors(henv, hdbc, *newHSTMT); + delete newHSTMT; + return(0); + } + + if (bindColumns) + { + if(!bindCols(*newHSTMT)) + { + delete newHSTMT; + return(0); + } + } + + if (setCursor) + SetCursor(newHSTMT); + + return(newHSTMT); + +} // wxDbTable::GetNewCursor() + + +/********** wxDbTable::DeleteCursor() **********/ +bool wxDbTable::DeleteCursor(HSTMT *hstmtDel) { - bool result = TRUE; - - // Switch to cursor 0 - int cursorNo = GetCursor(); - if (!SetCursor()) - return(FALSE); - - // Save the where and order by clauses - char *saveWhere = where; - char *saveOrderBy = orderBy; - - // Build a where clause to refetch the record with. Try and use the - // ROWID if it's available, ow use the key fields. - char whereClause[DB_MAX_WHERE_CLAUSE_LEN+1]; - strcpy(whereClause, ""); - if (CanUpdByROWID()) - { - SDWORD cb; - char rowid[ROWID_LEN+1]; - - // Get the ROWID value. If not successful retreiving the ROWID, - // simply fall down through the code and build the WHERE clause - // based on the key fields. - if (SQLGetData(hstmt, noCols+1, SQL_C_CHAR, rowid, ROWID_LEN, &cb) == SQL_SUCCESS) - { - strcat(whereClause, "ROWID = '"); - strcat(whereClause, rowid); - strcat(whereClause, "'"); - } - } - - // If unable to use the ROWID, build a where clause from the keyfields - if (strlen(whereClause) == 0) - GetWhereClause(whereClause, DB_WHERE_KEYFIELDS); - - // Requery the record - where = whereClause; - orderBy = 0; - if (!Query()) - result = FALSE; - - if (result && !GetNext()) - result = FALSE; - - // Switch back to original cursor - if (!SetCursor(cursorNo)) - result = FALSE; - - // Restore the original where and order by clauses - where = saveWhere; - orderBy = saveOrderBy; - - return(result); - -} // wxTable::Refresh() + bool result = TRUE; + + if (!hstmtDel) // Cursor already deleted + return(result); + + if (SQLFreeStmt(*hstmtDel, SQL_DROP) != SQL_SUCCESS) + { + pDb->DispAllErrors(henv, hdbc); + result = FALSE; + } + + delete hstmtDel; + + return(result); + +} // wxDbTable::DeleteCursor() + +#endif // wxUSE_ODBC -#endif - // wxUSE_ODBC