X-Git-Url: https://git.saurik.com/wxWidgets.git/blobdiff_plain/108106cfe7af7b582fcaae8f5fe0ac05c545259d..dc5c1114e12c45b61f7243057ba522a1c51da602:/src/common/dbtable.cpp diff --git a/src/common/dbtable.cpp b/src/common/dbtable.cpp index 32c7895396..edf1a0ce1c 100644 --- a/src/common/dbtable.cpp +++ b/src/common/dbtable.cpp @@ -1,13 +1,17 @@ /////////////////////////////////////////////////////////////////////////////// -// Name: table.cpp +// Name: dbtable.cpp // Purpose: Implementation of the wxTable class. // Author: Doug Card -// Modified by: +// Mods: April 1999 +// -Dynamic cursor support - Only one predefined cursor, as many others as +// you need may be created on demand +// -Reduced number of active cursors significantly +// -Query-Only wxTable objects // 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,56 +27,133 @@ // 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/version.h" +#include "wx/wxprec.h" + +#if wxMAJOR_VERSION == 2 +# ifdef __GNUG__ +# pragma implementation "dbtable.h" +# endif #endif -*/ -#include "wx/wxprec.h" +#ifdef DBDEBUG_CONSOLE + #include +#endif #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" + #endif + #include "wx/filefn.h" +#endif -#if USE_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 +ULONG lastTableID = 0; + + +#ifdef __WXDEBUG__ + wxList TablesInUse; +#endif + + /********** wxTable::wxTable() **********/ -wxTable::wxTable(wxDB *pwxDB, const char *tblName, const int nCols, const char *qryTblName) +wxTable::wxTable(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 + 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 = 0; // Where clause + orderBy = 0; // Order By clause + from = 0; // From clause + selectForUpdate = FALSE; // SELECT ... FOR UPDATE; Indicates whether to include the FOR UPDATE phrase + queryOnly = qryOnly; + + assert (tblName); + + wxStrcpy(tableName, tblName); // Table Name + if (tblPath) + wxStrcpy(tablePath, tblPath); // Table Path - used for dBase files + if (qryTblName) // Name of the table/view to query - strcpy(queryTableName, qryTblName); + wxStrcpy(queryTableName, qryTblName); else - strcpy(queryTableName, tblName); + wxStrcpy(queryTableName, tblName); + +// assert(pDb); // Assert is placed after table name is assigned for error reporting reasons + if (!pDb) + return; + + pDb->nTables++; + + char s[200]; + tableID = ++lastTableID; + sprintf(s, "wxTable constructor (%-20s) tableID:[%6lu] pDb:[%p]", tblName,tableID,pDb); + +#ifdef __WXDEBUG__ + CstructTablesInUse *tableInUse; + tableInUse = new CstructTablesInUse(); + tableInUse->tableName = tblName; + tableInUse->tableID = tableID; + tableInUse->pDb = pDb; + TablesInUse.Append(tableInUse); +#endif - 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 + pDb->WriteSqlLog(s); // Grab the HENV and HDBC from the wxDB object henv = pDb->henv; @@ -81,49 +162,38 @@ wxTable::wxTable(wxDB *pwxDB, const char *tblName, const int nCols, const char * // 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) + 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 - UDWORD cursorType = SQL_CURSOR_STATIC; - if (SQLSetStmtOption(c1, SQL_CURSOR_TYPE, cursorType) != SQL_SUCCESS) + 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, c1); - if (! strcmp(pDb->sqlState, "01S02")) // Option Value Changed + 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(c1, SQL_CURSOR_TYPE, &cursorType) != SQL_SUCCESS) - pDb->DispAllErrors(henv, hdbc, c1); -#ifdef _CONSOLE + if (SQLGetStmtOption(hstmtInternal, SQL_CURSOR_TYPE, &cursorType) != SQL_SUCCESS) + pDb->DispAllErrors(henv, hdbc, hstmtInternal); +#ifdef DBDEBUG_CONSOLE cout << "Static cursor changed to: "; switch(cursorType) { @@ -142,83 +212,358 @@ wxTable::wxTable(wxDB *pwxDB, const char *tblName, const int nCols, const char * else { pDb->DispNextError(); - pDb->DispAllErrors(henv, hdbc, c1); + pDb->DispAllErrors(henv, hdbc, hstmtInternal); } } -#ifdef _CONSOLE +#ifdef DBDEBUG_CONSOLE else cout << "Cursor Type set to STATIC" << endl << endl; #endif - 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; + 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 = NewCursor(FALSE,FALSE); + assert(hstmtDefault); + hstmt = *hstmtDefault; } // wxTable::wxTable() /********** wxTable::~wxTable() **********/ wxTable::~wxTable() { + char s[80]; + if (pDb) + { + sprintf(s, "wxTable destructor (%-20s) tableID:[%6lu] pDb:[%p]", tableName,tableID,pDb); + pDb->WriteSqlLog(s); + } + +#ifdef __WXDEBUG__ + if (tableID) + { + bool found = FALSE; + wxNode *pNode; + pNode = TablesInUse.First(); + while (pNode && !found) + { + if (((CstructTablesInUse *)pNode->Data())->tableID == tableID) + { + found = TRUE; + if (!TablesInUse.DeleteNode(pNode)) + wxMessageBox (s,"Unable to delete node!"); + } + else + pNode = pNode->Next(); + } + if (!found) + { + char msg[250]; + sprintf(msg,"Unable to find the tableID in the linked\nlist of tables in use.\n\n%s",s); + wxMessageBox (msg,"NOTICE..."); + } + } +#endif + + // Decrement the wxDB table count + if (pDb) + pDb->nTables--; + // 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); + if (!queryOnly) + { + if (hstmtInsert) + if (SQLFreeStmt(hstmtInsert, SQL_DROP) != SQL_SUCCESS) + pDb->DispAllErrors(henv, hdbc); + if (hstmtDelete) + if (SQLFreeStmt(hstmtDelete, SQL_DROP) != SQL_SUCCESS) + pDb->DispAllErrors(henv, hdbc); + 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); } // wxTable::~wxTable() +/***************************** PRIVATE FUNCTIONS *****************************/ + +/********** wxTable::bindInsertParams() **********/ +bool wxTable::bindInsertParams(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; + for (i = 0; i < noCols; 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; + } + // Null values + if (colDefs[i].Null) + { + colDefs[i].CbValue = SQL_NULL_DATA; + colDefs[i].Null = FALSE; + } + if (SQLBindParameter(hstmtInsert, i+1, 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); + +} // wxTable::bindInsertParams() + +/********** wxTable::bindUpdateParams() **********/ +bool wxTable::bindUpdateParams(void) +{ + 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->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, (UCHAR*) 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) +{ + 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, + colDefs[i].SzDataObj, &cb) != SQL_SUCCESS) + return(pDb->DispAllErrors(henv, hdbc, cursor)); + } + + // Completed successfully + return(TRUE); + +} // wxTable::bindCols() + +/********** wxTable::getRec() **********/ +bool wxTable::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 + { + // 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)); + } + } + + // Completed successfully + return(TRUE); + +} // wxTable::getRec() + +/********** wxTable::execDelete() **********/ +bool wxTable::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); + +} // wxTable::execDelete() + +/********** wxTable::execUpdate() **********/ +bool wxTable::execUpdate(const char *pSqlStmt) +{ + // Execute the UPDATE statement + if (SQLExecDirect(hstmtUpdate, (UCHAR FAR *) pSqlStmt, SQL_NTS) != SQL_SUCCESS) + return(pDb->DispAllErrors(henv, hdbc, hstmtUpdate)); + + // Record deleted successfully + return(TRUE); + +} // wxTable::execUpdate() + +/********** wxTable::query() **********/ +bool wxTable::query(int queryType, bool forUpdate, bool distinct, char *pSqlStmt) +{ + char sqlStmt[DB_MAX_STATEMENT_LEN]; + + // 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; + + // Set the SQL SELECT string + if (queryType != DB_SELECT_STATEMENT) // A select statement was not passed in, + { // so generate a select statement. + GetSelectStmt(sqlStmt, queryType, distinct); + pDb->WriteSqlLog(sqlStmt); + } + + // 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); + +} // wxTable::query() + + + /********** wxTable::Open() **********/ bool wxTable::Open(void) { + if (!pDb) + return FALSE; + int i; char sqlStmt[DB_MAX_STATEMENT_LEN]; // Verify that the table exists in the database - if (!pDb->TableExists(tableName)) + if (!pDb->TableExists(tableName,NULL,tablePath)) { char s[128]; sprintf(s, "Error opening '%s', table/view does not exist in the database.", tableName); @@ -228,25 +573,23 @@ bool wxTable::Open(void) // 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)) + if (!queryOnly) + { + if (!bindInsertParams()) // Inserts + return(FALSE); + if (!bindUpdateParams()) // Updates + return(FALSE); + } + if (!bindCols(*hstmtDefault)) // Selects return(FALSE); - if(! bindCols(c2)) + if (!bindCols(hstmtInternal)) // Internal use only return(FALSE); -// if(! bindCols(c3)) -// return(FALSE); -// if(! bindCols(c4)) -// return(FALSE); -// if(! bindCols(c5)) -// return(FALSE); + /* + * Do NOT bind the hstmtCount cursor!!! + */ // Build an insert statement using parameter markers - if (noCols > 0) + if (!queryOnly && noCols > 0) { bool needComma = FALSE; sprintf(sqlStmt, "INSERT INTO %s (", tableName); @@ -255,22 +598,24 @@ bool wxTable::Open(void) if (! colDefs[i].InsertAllowed) continue; if (needComma) - strcat(sqlStmt, ","); - strcat(sqlStmt, colDefs[i].ColName); + wxStrcat(sqlStmt, ","); + wxStrcat(sqlStmt, colDefs[i].ColName); needComma = TRUE; } needComma = FALSE; - strcat(sqlStmt, ") VALUES ("); + wxStrcat(sqlStmt, ") VALUES ("); for (i = 0; i < noCols; i++) { if (! colDefs[i].InsertAllowed) continue; if (needComma) - strcat(sqlStmt, ","); - strcat(sqlStmt, "?"); + wxStrcat(sqlStmt, ","); + wxStrcat(sqlStmt, "?"); needComma = TRUE; } - strcat(sqlStmt, ")"); + wxStrcat(sqlStmt, ")"); + +// pDb->WriteSqlLog(sqlStmt); // Prepare the insert statement for execution if (SQLPrepare(hstmtInsert, (UCHAR FAR *) sqlStmt, SQL_NTS) != SQL_SUCCESS) @@ -293,6 +638,7 @@ bool wxTable::Query(bool forUpdate, bool distinct) /********** wxTable::QueryBySqlStmt() **********/ bool wxTable::QueryBySqlStmt(char *pSqlStmt) { + pDb->WriteSqlLog(pSqlStmt); return(query(DB_SELECT_STATEMENT, FALSE, FALSE, pSqlStmt)); @@ -314,35 +660,53 @@ bool wxTable::QueryOnKeyFields(bool forUpdate, bool distinct) } // wxTable::QueryOnKeyFields() -/********** wxTable::query() **********/ -bool wxTable::query(int queryType, bool forUpdate, bool distinct, char *pSqlStmt) +/********** wxTable::GetPrev() **********/ +bool wxTable::GetPrev(void) { - char sqlStmt[DB_MAX_STATEMENT_LEN]; - - // Set the selectForUpdate member variable - if (forUpdate) - // The user may wish to select for update, but the DBMS may not be capable - selectForUpdate = CanSelectForUpdate(); + if (pDb->FwdOnlyCursors()) + { + wxFAIL_MSG(wxT("GetPrev()::Backward scrolling cursors are not enabled for this instance of wxTable")); + return FALSE; + } else - selectForUpdate = FALSE; - - // 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. + return(getRec(SQL_FETCH_PRIOR)); +} // wxTable::GetPrev() - // 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)); +/********** wxTable::operator-- **********/ +bool wxTable::operator--(int) +{ + if (pDb->FwdOnlyCursors()) + { + wxFAIL_MSG(wxT("operator--:Backward scrolling cursors are not enabled for this instance of wxTable")); + return FALSE; + } + else + return(getRec(SQL_FETCH_PRIOR)); +} // wxTable::operator-- - // Completed successfully - return(TRUE); +/********** wxTable::GetFirst() **********/ +bool wxTable::GetFirst(void) +{ + if (pDb->FwdOnlyCursors()) + { + wxFAIL_MSG(wxT("GetFirst():Backward scrolling cursors are not enabled for this instance of wxTable")); + return FALSE; + } + else + return(getRec(SQL_FETCH_FIRST)); +} // wxTable::GetFirst() -} // wxTable::query() +/********** wxTable::GetLast() **********/ +bool wxTable::GetLast(void) +{ + if (pDb->FwdOnlyCursors()) + { + wxFAIL_MSG(wxT("GetLast()::Backward scrolling cursors are not enabled for this instance of wxTable")); + return FALSE; + } + else + return(getRec(SQL_FETCH_LAST)); +} // wxTable::GetLast() /********** wxTable::GetSelectStmt() **********/ void wxTable::GetSelectStmt(char *pSqlStmt, int typeOfSelect, bool distinct) @@ -352,245 +716,122 @@ void wxTable::GetSelectStmt(char *pSqlStmt, int typeOfSelect, bool distinct) whereClause[0] = 0; // Build a select statement to query the database - strcpy(pSqlStmt, "SELECT "); + wxStrcpy(pSqlStmt, "SELECT "); // SELECT DISTINCT values only? if (distinct) - strcat(pSqlStmt, "DISTINCT "); + wxStrcat(pSqlStmt, "DISTINCT "); + + // Was a FROM clause specified to join tables to the base table? + // Available for ::Query() only!!! + bool appendFromClause = FALSE; + if (typeOfSelect == DB_SELECT_WHERE && from && wxStrlen(from)) + appendFromClause = TRUE; // Add the column list - for (int i = 0; i < noCols; i++) + int i; + for (i = 0; i < noCols; i++) { - strcat(pSqlStmt, colDefs[i].ColName); + // 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) - strcat(pSqlStmt, ","); + 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()) - strcat(pSqlStmt, ",ROWID"); + { + // 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 - strcat(pSqlStmt, " FROM "); - strcat(pSqlStmt, queryTableName); + 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 (where && strlen(where)) // May not want a where clause!!! + if (where && wxStrlen(where)) // May not want a where clause!!! { - strcat(pSqlStmt, " WHERE "); - strcat(pSqlStmt, where); + wxStrcat(pSqlStmt, " WHERE "); + wxStrcat(pSqlStmt, where); } break; case DB_SELECT_KEYFIELDS: GetWhereClause(whereClause, DB_WHERE_KEYFIELDS); - if (strlen(whereClause)) + if (wxStrlen(whereClause)) { - strcat(pSqlStmt, " WHERE "); - strcat(pSqlStmt, whereClause); + wxStrcat(pSqlStmt, " WHERE "); + wxStrcat(pSqlStmt, whereClause); } break; case DB_SELECT_MATCHING: GetWhereClause(whereClause, DB_WHERE_MATCHING); - if (strlen(whereClause)) + if (wxStrlen(whereClause)) { - strcat(pSqlStmt, " WHERE "); - strcat(pSqlStmt, whereClause); + wxStrcat(pSqlStmt, " WHERE "); + wxStrcat(pSqlStmt, whereClause); } break; } // Append the ORDER BY clause - if (orderBy && strlen(orderBy)) + if (orderBy && wxStrlen(orderBy)) { - strcat(pSqlStmt, " ORDER BY "); - strcat(pSqlStmt, orderBy); + wxStrcat(pSqlStmt, " ORDER BY "); + wxStrcat(pSqlStmt, orderBy); } - // SELECT FOR UPDATE if told to do so and the datasource is capable + // 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()) - strcat(pSqlStmt, " FOR UPDATE"); - -} // wxTable::GetSelectStmt() - -/********** wxTable::getRec() **********/ -bool wxTable::getRec(UWORD fetchType) -{ - 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 - - // Completed successfully - return(TRUE); - -} // wxTable::getRec() + wxStrcat(pSqlStmt, " FOR UPDATE"); -/********** wxTable::GetRowNum() **********/ -UWORD wxTable::GetRowNum(void) -{ - UDWORD rowNum; - - if (SQLGetStmtOption(hstmt, SQL_ROW_NUMBER, &rowNum) != SQL_SUCCESS) - { - pDb->DispAllErrors(henv, hdbc, hstmt); - return(0); - } - - // Completed successfully - return((UWORD) rowNum); - -} // wxTable::GetRowNum() - -/********** wxTable::bindInsertParams() **********/ -bool wxTable::bindInsertParams(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) -{ - 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::GetSelectStmt() -/********** wxTable::bindCols() **********/ -bool wxTable::bindCols(HSTMT cursor) +/********** wxTable::GetRowNum() **********/ +UWORD wxTable::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++) + UDWORD rowNum; + + if (SQLGetStmtOption(hstmt, SQL_ROW_NUMBER, (UCHAR*) &rowNum) != SQL_SUCCESS) { - if (SQLBindCol(cursor, i+1, colDefs[i].SqlCtype, colDefs[i].PtrDataObj, - colDefs[i].SzDataObj, &cb) != SQL_SUCCESS) - return(pDb->DispAllErrors(henv, hdbc, cursor)); + pDb->DispAllErrors(henv, hdbc, hstmt); + return(0); } // Completed successfully - return(TRUE); + return((UWORD) rowNum); -} // wxTable::bindCols() +} // wxTable::GetRowNum() /********** wxTable::CloseCursor() **********/ bool wxTable::CloseCursor(HSTMT cursor) @@ -604,42 +845,24 @@ bool wxTable::CloseCursor(HSTMT cursor) } // wxTable::CloseCursor() /********** wxTable::CreateTable() **********/ -bool wxTable::CreateTable(void) +bool wxTable::CreateTable(bool attemptDrop) { + if (!pDb) + return FALSE; + int i, j; char sqlStmt[DB_MAX_STATEMENT_LEN]; -#ifdef _CONSOLE +#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); + // Drop table first + if (attemptDrop && !DropTable()) + return FALSE; // Create the table -#ifdef _CONSOLE +#ifdef DBDEBUG_CONSOLE for (i = 0; i < noCols; i++) { // Exclude derived columns since they are NOT part of the base table @@ -675,32 +898,41 @@ bool wxTable::CreateTable(void) continue; // Comma Delimiter if (needComma) - strcat(sqlStmt, ","); + wxStrcat(sqlStmt, ","); // Column Name - strcat(sqlStmt, colDefs[i].ColName); - strcat(sqlStmt, " "); + wxStrcat(sqlStmt, colDefs[i].ColName); + wxStrcat(sqlStmt, " "); // Column Type switch(colDefs[i].DbDataType) { case DB_DATA_TYPE_VARCHAR: - strcat(sqlStmt, pDb->typeInfVarchar.TypeName); break; + wxStrcat(sqlStmt, pDb->typeInfVarchar.TypeName); break; case DB_DATA_TYPE_INTEGER: - strcat(sqlStmt, pDb->typeInfInteger.TypeName); break; + wxStrcat(sqlStmt, pDb->typeInfInteger.TypeName); break; case DB_DATA_TYPE_FLOAT: - strcat(sqlStmt, pDb->typeInfFloat.TypeName); break; + wxStrcat(sqlStmt, pDb->typeInfFloat.TypeName); break; case DB_DATA_TYPE_DATE: - strcat(sqlStmt, pDb->typeInfDate.TypeName); break; + wxStrcat(sqlStmt, pDb->typeInfDate.TypeName); break; } // For varchars, append the size of the string if (colDefs[i].DbDataType == DB_DATA_TYPE_VARCHAR) { char s[10]; - // strcat(sqlStmt, "("); - // strcat(sqlStmt, itoa(colDefs[i].SzDataObj, s, 10)); - // strcat(sqlStmt, ")"); + // wxStrcat(sqlStmt, "("); + // wxStrcat(sqlStmt, itoa(colDefs[i].SzDataObj, s, 10)); + // wxStrcat(sqlStmt, ")"); sprintf(s, "(%d)", colDefs[i].SzDataObj); - strcat(sqlStmt, s); + wxStrcat(sqlStmt, s); + } + + if (pDb->Dbms() == dbmsSYBASE_ASE || pDb->Dbms() == dbmsMY_SQL) + { + if (colDefs[i].KeyField) + { + wxStrcat(sqlStmt, " NOT NULL"); + } } + needComma = TRUE; } // If there is a primary key defined, include it in the create statement @@ -712,27 +944,38 @@ bool wxTable::CreateTable(void) break; } } - if (j) // Found a keyfield + if (j && pDb->Dbms() != dbmsDBASE) // Found a keyfield { - strcat(sqlStmt, ",CONSTRAINT "); - strcat(sqlStmt, tableName); - strcat(sqlStmt, "_PIDX PRIMARY KEY ("); + if (pDb->Dbms() != dbmsMY_SQL) + { + wxStrcat(sqlStmt, ",CONSTRAINT "); + wxStrcat(sqlStmt, tableName); + wxStrcat(sqlStmt, "_PIDX PRIMARY KEY ("); + } + else + { + /* MySQL goes out on this one. We also declare the relevant key NON NULL above */ + wxStrcat(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 - strcat(sqlStmt, ","); - strcat(sqlStmt, colDefs[i].ColName); + wxStrcat(sqlStmt, ","); + wxStrcat(sqlStmt, colDefs[i].ColName); } } - strcat(sqlStmt, ")"); + wxStrcat(sqlStmt, ")"); } // Append the closing parentheses for the create table statement - strcat(sqlStmt, ")"); + wxStrcat(sqlStmt, ")"); -#ifdef _CONSOLE + pDb->WriteSqlLog(sqlStmt); + +#ifdef DBDEBUG_CONSOLE cout << endl << sqlStmt << endl; #endif @@ -756,38 +999,97 @@ bool wxTable::CreateTable(void) } // wxTable::CreateTable() +/********** wxTable::DropTable() **********/ +bool wxTable::DropTable() +{ + // 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 + + char sqlStmt[DB_MAX_STATEMENT_LEN]; + + sprintf(sqlStmt, "DROP TABLE %s", tableName); + + pDb->WriteSqlLog(sqlStmt); + +#ifdef DBDEBUG_CONSOLE + cout << endl << sqlStmt << endl; +#endif + + if (SQLExecDirect(hstmt, (UCHAR FAR *) sqlStmt, SQL_NTS) != SQL_SUCCESS) + { + // Check for "Base table not found" error and ignore + pDb->GetNextError(henv, hdbc, hstmt); + if (wxStrcmp(pDb->sqlState,"S0002")) // "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() == dbmsMY_SQL && !wxStrcmp(pDb->sqlState,"S1000")) || // untested + (pDb->Dbms() == dbmsPOSTGRES && !wxStrcmp(pDb->sqlState,"08S01")))) // untested + { + 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); +} // wxTable::DropTable() + /********** wxTable::CreateIndex() **********/ -bool wxTable::CreateIndex(char * idxName, bool unique, int noIdxCols, CidxDef *pIdxDefs) +bool wxTable::CreateIndex(const char * idxName, bool unique, int noIdxCols, CidxDef *pIdxDefs, bool attemptDrop) { char sqlStmt[DB_MAX_STATEMENT_LEN]; + // Drop the index first + if (attemptDrop && !DropIndex(idxName)) + return (FALSE); + // Build a CREATE INDEX statement - strcpy(sqlStmt, "CREATE "); + wxStrcpy(sqlStmt, "CREATE "); if (unique) - strcat(sqlStmt, "UNIQUE "); + wxStrcat(sqlStmt, "UNIQUE "); - strcat(sqlStmt, "INDEX "); - strcat(sqlStmt, idxName); - strcat(sqlStmt, " ON "); - strcat(sqlStmt, tableName); - strcat(sqlStmt, " ("); + wxStrcat(sqlStmt, "INDEX "); + wxStrcat(sqlStmt, idxName); + wxStrcat(sqlStmt, " ON "); + wxStrcat(sqlStmt, tableName); + wxStrcat(sqlStmt, " ("); // Append list of columns making up index - for (int i = 0; i < noIdxCols; i++) + int i; + for (i = 0; i < noIdxCols; i++) { - strcat(sqlStmt, pIdxDefs[i].ColName); - if (pIdxDefs[i].Ascending) - strcat(sqlStmt, " ASC"); - else - strcat(sqlStmt, " DESC"); + wxStrcat(sqlStmt, pIdxDefs[i].ColName); + /* Postgres doesn't cope with ASC */ + if (pDb->Dbms() != dbmsPOSTGRES) + { + if (pIdxDefs[i].Ascending) + wxStrcat(sqlStmt, " ASC"); + else + wxStrcat(sqlStmt, " DESC"); + } + if ((i + 1) < noIdxCols) - strcat(sqlStmt, ","); + wxStrcat(sqlStmt, ","); } // Append closing parentheses - strcat(sqlStmt, ")"); + wxStrcat(sqlStmt, ")"); + + pDb->WriteSqlLog(sqlStmt); -#ifdef _CONSOLE +#ifdef DBDEBUG_CONSOLE cout << endl << sqlStmt << endl << endl; #endif @@ -811,15 +1113,76 @@ bool wxTable::CreateIndex(char * idxName, bool unique, int noIdxCols, CidxDef *p } // wxTable::CreateIndex() +/********** wxTable::DropIndex() **********/ +bool wxTable::DropIndex(const char * idxName) +{ + // 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 + + char sqlStmt[DB_MAX_STATEMENT_LEN]; + + if (pDb->Dbms() == dbmsACCESS) + sprintf(sqlStmt, "DROP INDEX %s ON %s",idxName,tableName); + else if (pDb->Dbms() == dbmsSYBASE_ASE) + sprintf(sqlStmt, "DROP INDEX %s.%s",tableName,idxName); + else + sprintf(sqlStmt, "DROP INDEX %s",idxName); + + pDb->WriteSqlLog(sqlStmt); + +#ifdef DBDEBUG_CONSOLE + cout << endl << sqlStmt << endl; +#endif + + if (SQLExecDirect(hstmt, (UCHAR FAR *) sqlStmt, 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,"S0002")) || // Base table not found + (pDb->Dbms() == dbmsMY_SQL && !wxStrcmp(pDb->sqlState,"42S02")) // untested + )) + { + 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); +} // wxTable::DropIndex() + /********** wxTable::Insert() **********/ int wxTable::Insert(void) { + assert(!queryOnly); + if (queryOnly) + return(DB_FAILURE); + + bindInsertParams(); + // Insert the record by executing the already prepared insert statement - if (SQLExecute(hstmtInsert) != SQL_SUCCESS) + 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 (! strcmp(pDb->sqlState, "23000")) // Integrity constraint violated + if (! wxStrcmp(pDb->sqlState, "23000")) // Integrity constraint violated return(DB_ERR_INTEGRITY_CONSTRAINT_VIOL); else { @@ -834,23 +1197,21 @@ int wxTable::Insert(void) } // wxTable::Insert() -/********** wxTable::Update(pSqlStmt) **********/ -bool wxTable::Update(char *pSqlStmt) -{ - - return(execUpdate(pSqlStmt)); - -} // wxTable::Update(pSqlStmt) - /********** wxTable::Update() **********/ bool wxTable::Update(void) { + assert(!queryOnly); + if (queryOnly) + return(FALSE); + char sqlStmt[DB_MAX_STATEMENT_LEN]; // Build the SQL UPDATE statement GetUpdateStmt(sqlStmt, DB_UPD_KEYFIELDS); -#ifdef _CONSOLE + pDb->WriteSqlLog(sqlStmt); + +#ifdef DBDEBUG_CONSOLE cout << endl << sqlStmt << endl << endl; #endif @@ -859,15 +1220,34 @@ bool wxTable::Update(void) } // wxTable::Update() +/********** wxTable::Update(pSqlStmt) **********/ +bool wxTable::Update(const char *pSqlStmt) +{ + assert(!queryOnly); + if (queryOnly) + return(FALSE); + + pDb->WriteSqlLog(pSqlStmt); + + return(execUpdate(pSqlStmt)); + +} // wxTable::Update(pSqlStmt) + /********** wxTable::UpdateWhere() **********/ -bool wxTable::UpdateWhere(char *pWhereClause) +bool wxTable::UpdateWhere(const char *pWhereClause) { + assert(!queryOnly); + if (queryOnly) + return(FALSE); + char sqlStmt[DB_MAX_STATEMENT_LEN]; // Build the SQL UPDATE statement GetUpdateStmt(sqlStmt, DB_UPD_WHERE, pWhereClause); -#ifdef _CONSOLE + pDb->WriteSqlLog(sqlStmt); + +#ifdef DBDEBUG_CONSOLE cout << endl << sqlStmt << endl << endl; #endif @@ -879,24 +1259,36 @@ bool wxTable::UpdateWhere(char *pWhereClause) /********** wxTable::Delete() **********/ bool wxTable::Delete(void) { + assert(!queryOnly); + if (queryOnly) + return(FALSE); + char sqlStmt[DB_MAX_STATEMENT_LEN]; // Build the SQL DELETE statement GetDeleteStmt(sqlStmt, DB_DEL_KEYFIELDS); + pDb->WriteSqlLog(sqlStmt); + // Execute the SQL DELETE statement return(execDelete(sqlStmt)); } // wxTable::Delete() /********** wxTable::DeleteWhere() **********/ -bool wxTable::DeleteWhere(char *pWhereClause) +bool wxTable::DeleteWhere(const char *pWhereClause) { + assert(!queryOnly); + if (queryOnly) + return(FALSE); + char sqlStmt[DB_MAX_STATEMENT_LEN]; // Build the SQL DELETE statement GetDeleteStmt(sqlStmt, DB_DEL_WHERE, pWhereClause); + pDb->WriteSqlLog(sqlStmt); + // Execute the SQL DELETE statement return(execDelete(sqlStmt)); @@ -905,43 +1297,29 @@ bool wxTable::DeleteWhere(char *pWhereClause) /********** wxTable::DeleteMatching() **********/ bool wxTable::DeleteMatching(void) { + assert(!queryOnly); + if (queryOnly) + return(FALSE); + char sqlStmt[DB_MAX_STATEMENT_LEN]; // Build the SQL DELETE statement GetDeleteStmt(sqlStmt, DB_DEL_MATCHING); + pDb->WriteSqlLog(sqlStmt); + // Execute the SQL DELETE statement return(execDelete(sqlStmt)); } // wxTable::DeleteMatching() -/********** wxTable::execDelete() **********/ -bool wxTable::execDelete(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); - -} // wxTable::execDelete() - -/********** wxTable::execUpdate() **********/ -bool wxTable::execUpdate(char *pSqlStmt) -{ - // Execute the UPDATE statement - if (SQLExecDirect(hstmtUpdate, (UCHAR FAR *) pSqlStmt, SQL_NTS) != SQL_SUCCESS) - return(pDb->DispAllErrors(henv, hdbc, hstmtUpdate)); - - // Record deleted successfully - return(TRUE); - -} // wxTable::execUpdate() - /********** wxTable::GetUpdateStmt() **********/ -void wxTable::GetUpdateStmt(char *pSqlStmt, int typeOfUpd, char *pWhereClause) +void wxTable::GetUpdateStmt(char *pSqlStmt, int typeOfUpd, const char *pWhereClause) { + assert(!queryOnly); + if (queryOnly) + return; + char whereClause[DB_MAX_WHERE_CLAUSE_LEN]; bool firstColumn = TRUE; @@ -949,28 +1327,29 @@ void wxTable::GetUpdateStmt(char *pSqlStmt, int typeOfUpd, char *pWhereClause) sprintf(pSqlStmt, "UPDATE %s SET ", tableName); // Append a list of columns to be updated - for (int i = 0; i < noCols; i++) + int i; + for (i = 0; i < noCols; i++) { // Only append Updateable columns if (colDefs[i].Updateable) { if (! firstColumn) - strcat(pSqlStmt, ","); + wxStrcat(pSqlStmt, ","); else firstColumn = FALSE; - strcat(pSqlStmt, colDefs[i].ColName); - strcat(pSqlStmt, " = ?"); + wxStrcat(pSqlStmt, colDefs[i].ColName); + wxStrcat(pSqlStmt, " = ?"); } } // Append the WHERE clause to the SQL UPDATE statement - strcat(pSqlStmt, " WHERE "); + 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 C1 = ?, C2 = ? WHERE ROWID = '111.222.333' + // e.g. UPDATE PARTS SET Col1 = ?, Col2 = ? WHERE ROWID = '111.222.333' if (CanUpdByROWID()) { SDWORD cb; @@ -979,36 +1358,39 @@ void wxTable::GetUpdateStmt(char *pSqlStmt, int typeOfUpd, char *pWhereClause) // 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) + if (SQLGetData(hstmt, noCols+1, SQL_C_CHAR, (UCHAR*) rowid, ROWID_LEN, &cb) == SQL_SUCCESS) { - strcat(pSqlStmt, "ROWID = '"); - strcat(pSqlStmt, rowid); - strcat(pSqlStmt, "'"); + wxStrcat(pSqlStmt, "ROWID = '"); + wxStrcat(pSqlStmt, rowid); + wxStrcat(pSqlStmt, "'"); break; } } // Unable to delete by ROWID, so build a WHERE // clause based on the keyfields. GetWhereClause(whereClause, DB_WHERE_KEYFIELDS); - strcat(pSqlStmt, whereClause); + wxStrcat(pSqlStmt, whereClause); break; case DB_UPD_WHERE: - strcat(pSqlStmt, pWhereClause); + wxStrcat(pSqlStmt, pWhereClause); break; } - } // GetUpdateStmt() /********** wxTable::GetDeleteStmt() **********/ -void wxTable::GetDeleteStmt(char *pSqlStmt, int typeOfDel, char *pWhereClause) +void wxTable::GetDeleteStmt(char *pSqlStmt, int typeOfDel, const char *pWhereClause) { + 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 || strlen(pWhereClause) == 0)) + if (typeOfDel == DB_DEL_WHERE && (pWhereClause == 0 || wxStrlen(pWhereClause) == 0)) { sprintf(pSqlStmt, "DELETE FROM %s", tableName); return; @@ -1031,25 +1413,25 @@ void wxTable::GetDeleteStmt(char *pSqlStmt, int typeOfDel, char *pWhereClause) // 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) + if (SQLGetData(hstmt, noCols+1, SQL_C_CHAR, (UCHAR*) rowid, ROWID_LEN, &cb) == SQL_SUCCESS) { - strcat(pSqlStmt, "ROWID = '"); - strcat(pSqlStmt, rowid); - strcat(pSqlStmt, "'"); + wxStrcat(pSqlStmt, "ROWID = '"); + wxStrcat(pSqlStmt, rowid); + wxStrcat(pSqlStmt, "'"); break; } } // Unable to delete by ROWID, so build a WHERE // clause based on the keyfields. GetWhereClause(whereClause, DB_WHERE_KEYFIELDS); - strcat(pSqlStmt, whereClause); + wxStrcat(pSqlStmt, whereClause); break; case DB_DEL_WHERE: - strcat(pSqlStmt, pWhereClause); + wxStrcat(pSqlStmt, pWhereClause); break; case DB_DEL_MATCHING: GetWhereClause(whereClause, DB_WHERE_MATCHING); - strcat(pSqlStmt, whereClause); + wxStrcat(pSqlStmt, whereClause); break; } @@ -1061,13 +1443,14 @@ void wxTable::GetDeleteStmt(char *pSqlStmt, int typeOfDel, char *pWhereClause) * They are not included as part of the where clause. */ -void wxTable::GetWhereClause(char *pWhereClause, int typeOfWhere) +void wxTable::GetWhereClause(char *pWhereClause, int typeOfWhere, const char *qualTableName) { bool moreThanOneColumn = FALSE; char colValue[255]; // Loop through the columns building a where clause as you go - for (int i = 0; i < noCols; i++) + 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) || @@ -1078,12 +1461,17 @@ void wxTable::GetWhereClause(char *pWhereClause, int typeOfWhere) continue; // If there is more than 1 column, join them with the keyword "AND" if (moreThanOneColumn) - strcat(pWhereClause, " AND "); + wxStrcat(pWhereClause, " AND "); else moreThanOneColumn = TRUE; // Concatenate where phrase for the column - strcat(pWhereClause, colDefs[i].ColName); - strcat(pWhereClause, " = "); + if (qualTableName && wxStrlen(qualTableName)) + { + wxStrcat(pWhereClause, qualTableName); + wxStrcat(pWhereClause, "."); + } + wxStrcat(pWhereClause, colDefs[i].ColName); + wxStrcat(pWhereClause, " = "); switch(colDefs[i].SqlCtype) { case SQL_C_CHAR: @@ -1108,7 +1496,7 @@ void wxTable::GetWhereClause(char *pWhereClause, int typeOfWhere) sprintf(colValue, "%.6f", *((SDOUBLE *) colDefs[i].PtrDataObj)); break; } - strcat(pWhereClause, colValue); + wxStrcat(pWhereClause, colValue); } } @@ -1147,9 +1535,11 @@ bool wxTable::IsColNull(int colNo) } // wxTable::IsColNull() /********** wxTable::CanSelectForUpdate() **********/ - bool wxTable::CanSelectForUpdate(void) { + if (pDb->Dbms() == dbmsMY_SQL) + return FALSE; + if (pDb->dbInf.posStmts & SQL_PS_SELECT_FOR_UPDATE) return(TRUE); else @@ -1161,10 +1551,11 @@ bool wxTable::CanSelectForUpdate(void) bool wxTable::CanUpdByROWID(void) { -//@@@@@@glt - returning FALSE for testing purposes, as the ROWID is not getting updated correctly +//NOTE: Returning FALSE for now until this can be debugged, +// as the ROWID is not getting updated correctly return FALSE; - if ((! strcmp(pDb->dbInf.dbmsName, "Oracle")) || (! strcmp(pDb->dbInf.dbmsName, "ORACLE"))) + if (pDb->Dbms() == dbmsORACLE) return(TRUE); else return(FALSE); @@ -1185,7 +1576,8 @@ bool wxTable::IsCursorClosedOnCommit(void) void wxTable::ClearMemberVars(void) { // Loop through the columns setting each member variable to zero - for (int i = 0; i < noCols; i++) + int i; + for (i = 0; i < noCols; i++) { switch(colDefs[i].SqlCtype) { @@ -1229,26 +1621,14 @@ void wxTable::ClearMemberVars(void) /********** wxTable::SetQueryTimeout() **********/ bool wxTable::SetQueryTimeout(UDWORD nSeconds) { - 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)); + if (SQLSetStmtOption(hstmtInternal, SQL_QUERY_TIMEOUT, nSeconds) != SQL_SUCCESS) + return(pDb->DispAllErrors(henv, hdbc, hstmtInternal)); // Completed Successfully return(TRUE); @@ -1256,17 +1636,20 @@ bool wxTable::SetQueryTimeout(UDWORD nSeconds) } // wxTable::SetQueryTimeout() /********** wxTable::SetColDefs() **********/ -void wxTable::SetColDefs (int index, char *fieldName, int dataType, void *pData, +void wxTable::SetColDefs (int index, const char *fieldName, int dataType, void *pData, int cType, int size, bool keyField, bool upd, bool insAllow, bool derivedCol) { - if (strlen(fieldName) > DB_MAX_COLUMN_NAME_LEN) // glt 4/21/97 + if (!colDefs) // May happen if the database connection fails + return; + + if (wxStrlen(fieldName) > (unsigned int) DB_MAX_COLUMN_NAME_LEN) { - strncpy (colDefs[index].ColName, fieldName, DB_MAX_COLUMN_NAME_LEN); - colDefs[index].ColName[DB_MAX_COLUMN_NAME_LEN] = 0; // glt 10/23/97 + wxStrncpy (colDefs[index].ColName, fieldName, DB_MAX_COLUMN_NAME_LEN); + colDefs[index].ColName[DB_MAX_COLUMN_NAME_LEN] = 0; } else - strcpy(colDefs[index].ColName, fieldName); + wxStrcpy(colDefs[index].ColName, fieldName); colDefs[index].DbDataType = dataType; colDefs[index].PtrDataObj = pData; @@ -1285,97 +1668,79 @@ void wxTable::SetColDefs (int index, char *fieldName, int dataType, void *pData, colDefs[index].Updateable = upd; colDefs[index].InsertAllowed = insAllow; } + + colDefs[index].Null = FALSE; } // wxTable::SetColDefs() /********** wxTable::SetCursor() **********/ -bool wxTable::SetCursor(int cursorNo) +void wxTable::SetCursor(HSTMT *hstmtActivate) { - 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); -#endif - - // Completed successfully - return(TRUE); + if (hstmtActivate == DEFAULT_CURSOR) + hstmt = *hstmtDefault; + else + hstmt = *hstmtActivate; } // wxTable::SetCursor() -/********** wxTable::Count() **********/ -ULONG wxTable::Count(void) +/********** wxTable::Count(const char *) **********/ +ULONG wxTable::Count(const char *args) { 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); + wxStrcpy(sqlStmt, "SELECT COUNT("); + wxStrcat(sqlStmt, args); + wxStrcat(sqlStmt, ") FROM "); + wxStrcat(sqlStmt, queryTableName); + + if (from && wxStrlen(from)) + wxStrcat(sqlStmt, from); // Add the where clause if one is provided - if (where && strlen(where)) + if (where && wxStrlen(where)) + { + wxStrcat(sqlStmt, " WHERE "); + wxStrcat(sqlStmt, where); + } + + pDb->WriteSqlLog(sqlStmt); + + // Initialize the Count cursor if it's not already initialized + if (!hstmtCount) { - strcat(sqlStmt, " WHERE "); - strcat(sqlStmt, where); + hstmtCount = NewCursor(FALSE,FALSE); + assert(hstmtCount); + if (!hstmtCount) + return(0); } // Execute the SQL statement - if (SQLExecDirect(hstmtCount, (UCHAR FAR *) sqlStmt, SQL_NTS) != SQL_SUCCESS) + if (SQLExecDirect(*hstmtCount, (UCHAR FAR *) sqlStmt, SQL_NTS) != SQL_SUCCESS) { - pDb->DispAllErrors(henv, hdbc, hstmtCount); + pDb->DispAllErrors(henv, hdbc, *hstmtCount); return(0); } // Fetch the record - if (SQLFetch(hstmtCount) != SQL_SUCCESS) + if (SQLFetch(*hstmtCount) != SQL_SUCCESS) { - pDb->DispAllErrors(henv, hdbc, hstmtCount); + pDb->DispAllErrors(henv, hdbc, *hstmtCount); return(0); } // Obtain the result - if (SQLGetData(hstmtCount, 1, SQL_C_ULONG, &l, sizeof(l), &cb) != SQL_SUCCESS) + if (SQLGetData(*hstmtCount, 1, SQL_C_ULONG, &l, sizeof(l), &cb) != SQL_SUCCESS) { - pDb->DispAllErrors(henv, hdbc, hstmtCount); + pDb->DispAllErrors(henv, hdbc, *hstmtCount); return(0); } // Free the cursor - if (SQLFreeStmt(hstmtCount, SQL_CLOSE) != SQL_SUCCESS) - pDb->DispAllErrors(henv, hdbc, hstmtCount); + if (SQLFreeStmt(*hstmtCount, SQL_CLOSE) != SQL_SUCCESS) + pDb->DispAllErrors(henv, hdbc, *hstmtCount); // Return the record count return(l); @@ -1387,10 +1752,9 @@ bool wxTable::Refresh(void) { bool result = TRUE; - // Switch to cursor 0 - int cursorNo = GetCursor(); - if (!SetCursor()) - return(FALSE); + // Switch to the internal cursor so any active cursors are not corrupted + HSTMT currCursor = GetCursor(); + hstmt = hstmtInternal; // Save the where and order by clauses char *saveWhere = where; @@ -1399,7 +1763,7 @@ bool wxTable::Refresh(void) // 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, ""); + wxStrcpy(whereClause, ""); if (CanUpdByROWID()) { SDWORD cb; @@ -1408,17 +1772,18 @@ bool wxTable::Refresh(void) // 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) + if (SQLGetData(hstmt, noCols+1, SQL_C_CHAR, (UCHAR*) rowid, ROWID_LEN, &cb) == SQL_SUCCESS) { - strcat(whereClause, "ROWID = '"); - strcat(whereClause, rowid); - strcat(whereClause, "'"); + 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 (strlen(whereClause) == 0) - GetWhereClause(whereClause, DB_WHERE_KEYFIELDS); + if (wxStrlen(whereClause) == 0) + GetWhereClause(whereClause, DB_WHERE_KEYFIELDS, queryTableName); // Requery the record where = whereClause; @@ -1430,8 +1795,11 @@ bool wxTable::Refresh(void) result = FALSE; // Switch back to original cursor - if (!SetCursor(cursorNo)) - result = FALSE; + 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; @@ -1441,5 +1809,90 @@ bool wxTable::Refresh(void) } // wxTable::Refresh() -#endif - // USE_ODBC +/********** wxTable::SetNull(UINT colNo) **********/ +bool wxTable::SetNull(int colNo) +{ + if (colNo < noCols) + return(colDefs[colNo].Null = TRUE); + else + return(FALSE); + +} // wxTable::SetNull(UINT colNo) + +/********** wxTable::SetNull(char *colName) **********/ +bool wxTable::SetNull(const char *colName) +{ + int i; + for (i = 0; i < noCols; i++) + { + if (!wxStricmp(colName, colDefs[i].ColName)) + break; + } + + if (i < noCols) + return(colDefs[i].Null = TRUE); + else + return(FALSE); + +} // wxTable::SetNull(char *colName) + +/********** wxTable::NewCursor() **********/ +HSTMT *wxTable::NewCursor(bool setCursor, bool bindColumns) +{ + 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); + +} // wxTable::NewCursor() + +/********** wxTable::DeleteCursor() **********/ +bool wxTable::DeleteCursor(HSTMT *hstmtDel) +{ + 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); + +} // wxTable::DeleteCursor() + +#endif // wxUSE_ODBC +