X-Git-Url: https://git.saurik.com/wxWidgets.git/blobdiff_plain/2f74ed285965eaf401a4060507b5c80166dfc0f5..937afade13326376cf1d3071057d069041172daa:/src/common/dbtable.cpp diff --git a/src/common/dbtable.cpp b/src/common/dbtable.cpp index f39279fef8..de3b913141 100644 --- a/src/common/dbtable.cpp +++ b/src/common/dbtable.cpp @@ -1,13 +1,10 @@ /////////////////////////////////////////////////////////////////////////////// // Name: dbtable.cpp -// Purpose: Implementation of the wxTable class. +// Purpose: Implementation of the wxDbTable class. // Author: Doug Card // Modified by: George Tasker -// 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 +// Bart Jourquin +// Mark Johnson // Created: 9.96 // RCS-ID: $Id$ // Copyright: (c) 1996 Remstar International, Inc. @@ -31,19 +28,20 @@ // 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" +#include "wx/version.h" #if wxMAJOR_VERSION == 2 -# ifdef __GNUG__ -# pragma implementation "dbtable.h" -# endif + #ifdef __GNUG__ + #pragma implementation "dbtable.h" + #endif #endif #ifdef DBDEBUG_CONSOLE - #include + #include "wx/ioswrap.h" #endif + #ifdef __BORLANDC__ #pragma hdrstop #endif //__BORLANDC__ @@ -55,6 +53,7 @@ #include "wx/list.h" #include "wx/utils.h" #include "wx/msgdlg.h" + #include "wx/log.h" #endif #include "wx/filefn.h" #endif @@ -70,6 +69,7 @@ # define wxUSE_ODBC 1 #endif + #if wxUSE_ODBC #include @@ -102,11 +102,11 @@ ULONG lastTableID = 0; #endif -/********** wxTable::wxTable() **********/ -wxTable::wxTable(wxDB *pwxDB, const char *tblName, const int nCols, +/********** wxDbTable::wxDbTable() **********/ +wxDbTable::wxDbTable(wxDb *pwxDb, const char *tblName, const int nCols, const char *qryTblName, bool qryOnly, const char *tblPath) { - pDb = pwxDB; // Pointer to the wxDB object + pDb = pwxDb; // Pointer to the wxDb object henv = 0; hdbc = 0; hstmt = 0; @@ -119,11 +119,15 @@ wxTable::wxTable(wxDB *pwxDB, const char *tblName, const int nCols, 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 + 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); @@ -131,41 +135,41 @@ wxTable::wxTable(wxDB *pwxDB, const char *tblName, const int nCols, if (tblPath) wxStrcpy(tablePath, tblPath); // Table Path - used for dBase files else - tablePath[0]=0; - + tablePath[0] = 0; + if (qryTblName) // Name of the table/view to query wxStrcpy(queryTableName, qryTblName); else wxStrcpy(queryTableName, tblName); - + if (!pDb) return; - - pDb->nTables++; - + + pDb->incrementTableCount(); + wxString s; tableID = ++lastTableID; - s.sprintf("wxTable constructor (%-20s) tableID:[%6lu] pDb:[%p]", tblName,tableID,pDb); - + s.sprintf("wxDbTable constructor (%-20s) tableID:[%6lu] pDb:[%p]", tblName,tableID,pDb); + #ifdef __WXDEBUG__ - CstructTablesInUse *tableInUse; - tableInUse = new CstructTablesInUse(); + wxTablesInUse *tableInUse; + tableInUse = new wxTablesInUse(); tableInUse->tableName = tblName; tableInUse->tableID = tableID; tableInUse->pDb = pDb; TablesInUse.Append(tableInUse); #endif - - pDb->WriteSqlLog(s.GetData()); - - // Grab the HENV and HDBC from the wxDB object - henv = pDb->henv; - hdbc = pDb->hdbc; - + + 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 wxColDef[noCols]; // Points to the first column defintion - + colDefs = new wxDbColDef[noCols]; // Points to the first column defintion + // Allocate statement handles for the table if (!queryOnly) { @@ -182,11 +186,12 @@ wxTable::wxTable(wxDB *pwxDB, const char *tblName, const int nCols, // 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 @@ -201,16 +206,32 @@ wxTable::wxTable(wxDB *pwxDB, const char *tblName, const int nCols, switch(cursorType) { case SQL_CURSOR_FORWARD_ONLY: - cout << "Forward Only"; break; + cout << "Forward Only"; + break; case SQL_CURSOR_STATIC: - cout << "Static"; break; + cout << "Static"; + break; case SQL_CURSOR_KEYSET_DRIVEN: - cout << "Keyset Driven"; break; + cout << "Keyset Driven"; + break; case SQL_CURSOR_DYNAMIC: - cout << "Dynamic"; break; + 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 { @@ -222,7 +243,7 @@ wxTable::wxTable(wxDB *pwxDB, const char *tblName, const int nCols, else cout << "Cursor Type set to STATIC" << endl << endl; #endif - + if (!queryOnly) { // Set the cursor type for the INSERT statement handle @@ -235,23 +256,23 @@ wxTable::wxTable(wxDB *pwxDB, const char *tblName, const int nCols, 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); + hstmtDefault = GetNewCursor(FALSE,FALSE); assert(hstmtDefault); hstmt = *hstmtDefault; - -} // wxTable::wxTable() + +} // wxDbTable::wxDbTable() -/********** wxTable::~wxTable() **********/ -wxTable::~wxTable() +/********** wxDbTable::~wxDbTable() **********/ +wxDbTable::~wxDbTable() { wxString s; if (pDb) { - s.sprintf("wxTable destructor (%-20s) tableID:[%6lu] pDb:[%p]", tableName,tableID,pDb); - pDb->WriteSqlLog(s.GetData()); + s.sprintf("wxDbTable destructor (%-20s) tableID:[%6lu] pDb:[%p]", tableName,tableID,pDb); + pDb->WriteSqlLog(s.c_str()); } #ifdef __WXDEBUG__ @@ -264,11 +285,11 @@ wxTable::~wxTable() pNode = TablesInUse.First(); while (pNode && !found) { - if (((CstructTablesInUse *)pNode->Data())->tableID == tableID) + if (((wxTablesInUse *)pNode->Data())->tableID == tableID) { found = TRUE; if (!TablesInUse.DeleteNode(pNode)) - wxMessageBox (s.GetData(),"Unable to delete node!"); + wxLogDebug (s.c_str(),wxT("Unable to delete node!")); } else pNode = pNode->Next(); @@ -276,15 +297,15 @@ wxTable::~wxTable() if (!found) { wxString msg; - msg.sprintf("Unable to find the tableID in the linked\nlist of tables in use.\n\n%s",s.GetData()); - wxMessageBox (msg.GetData(),"NOTICE..."); + 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 + // Decrement the wxDb table count if (pDb) - pDb->nTables--; + pDb->decrementTableCount(); // Delete memory allocated for column definitions if (colDefs) @@ -296,13 +317,15 @@ wxTable::~wxTable() 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); @@ -310,10 +333,11 @@ wxTable::~wxTable() // Delete dynamically allocated cursors if (hstmtDefault) DeleteCursor(hstmtDefault); + if (hstmtCount) DeleteCursor(hstmtCount); -} // wxTable::~wxTable() +} // wxDbTable::~wxDbTable() @@ -321,8 +345,8 @@ wxTable::~wxTable() -/********** wxTable::bindInsertParams() **********/ -bool wxTable::bindInsertParams(void) +/********** wxDbTable::bindInsertParams() **********/ +bool wxDbTable::bindInsertParams(void) { assert(!queryOnly); if (queryOnly) @@ -340,61 +364,77 @@ bool wxTable::bindInsertParams(void) 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; + 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 - if (colDefs[i].Null) - { - colDefs[i].CbValue = SQL_NULL_DATA; - colDefs[i].Null = FALSE; - } +//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) + 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() +} // wxDbTable::bindInsertParams() -/********** wxTable::bindUpdateParams() **********/ -bool wxTable::bindUpdateParams(void) +/********** wxDbTable::bindUpdateParams() **********/ +bool wxDbTable::bindUpdateParams(void) { assert(!queryOnly); if (queryOnly) return(FALSE); - + SWORD fSqlType = 0; UDWORD precision = 0; SWORD scale = 0; @@ -407,70 +447,88 @@ bool wxTable::bindUpdateParams(void) 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; + 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); -} // wxTable::bindUpdateParams() +} // wxDbTable::bindUpdateParams() -/********** wxTable::bindCols() **********/ -bool wxTable::bindCols(HSTMT cursor) +/********** wxDbTable::bindCols() **********/ +bool wxDbTable::bindCols(HSTMT cursor) { - static SDWORD cb; +//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, - colDefs[i].SzDataObj, &cb) != SQL_SUCCESS) - return(pDb->DispAllErrors(henv, hdbc, cursor)); +//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); -} // wxTable::bindCols() +} // wxDbTable::bindCols() -/********** wxTable::getRec() **********/ -bool wxTable::getRec(UWORD fetchType) +/********** wxDbTable::getRec() **********/ +bool wxDbTable::getRec(UWORD fetchType) { RETCODE retcode; @@ -482,10 +540,20 @@ bool wxTable::getRec(UWORD fetchType) 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 { @@ -498,16 +566,24 @@ bool wxTable::getRec(UWORD fetchType) 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); -} // wxTable::getRec() +} // wxDbTable::getRec() -/********** wxTable::execDelete() **********/ -bool wxTable::execDelete(const char *pSqlStmt) +/********** wxDbTable::execDelete() **********/ +bool wxDbTable::execDelete(const char *pSqlStmt) { // Execute the DELETE statement if (SQLExecDirect(hstmtDelete, (UCHAR FAR *) pSqlStmt, SQL_NTS) != SQL_SUCCESS) @@ -516,11 +592,11 @@ bool wxTable::execDelete(const char *pSqlStmt) // Record deleted successfully return(TRUE); -} // wxTable::execDelete() +} // wxDbTable::execDelete() -/********** wxTable::execUpdate() **********/ -bool wxTable::execUpdate(const char *pSqlStmt) +/********** wxDbTable::execUpdate() **********/ +bool wxDbTable::execUpdate(const char *pSqlStmt) { // Execute the UPDATE statement if (SQLExecDirect(hstmtUpdate, (UCHAR FAR *) pSqlStmt, SQL_NTS) != SQL_SUCCESS) @@ -529,15 +605,14 @@ bool wxTable::execUpdate(const char *pSqlStmt) // Record deleted successfully return(TRUE); -} // wxTable::execUpdate() +} // wxDbTable::execUpdate() -/********** wxTable::query() **********/ -bool wxTable::query(int queryType, bool forUpdate, bool distinct, char *pSqlStmt) +/********** wxDbTable::query() **********/ +bool wxDbTable::query(int queryType, bool forUpdate, bool distinct, const 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(); @@ -546,94 +621,111 @@ bool wxTable::query(int queryType, bool forUpdate, bool distinct, char *pSqlStmt // 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); + { // 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)) + 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); + 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() +} // wxDbTable::query() /***************************** PUBLIC FUNCTIONS *****************************/ -/********** wxTable::Open() **********/ -bool wxTable::Open(void) +/********** wxDbTable::Open() **********/ +bool wxDbTable::Open(bool checkPrivileges) { if (!pDb) - return FALSE; + return FALSE; int i; -// char sqlStmt[DB_MAX_STATEMENT_LEN]; wxString sqlStmt; - wxString *s = NULL; + wxString s; + s = ""; // Verify that the table exists in the database -// if (!pDb->TableExists(tableName,pDb->GetUsername(),tablePath)) - if (!pDb->TableExists(tableName,NULL,tablePath)) + if (!pDb->TableExists(tableName,/*pDb->GetUsername()*/NULL,tablePath)) { - s =new wxString("Table/view does not exist in the database"); - if (*(pDb->dbInf.accessibleTables) == 'Y') - { - (*s)+=", or you have insufficient permissions.\n"; - } - else - { - (*s)+=".\n"; - } + 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 + 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()) { - // Verify the user has rights to access the table. - // Shortcut boolean evaluation to optimize out call to TablePrivs - // Unfortunely this optimization doesn't seem to be reliable! - if (/* *(pDb->dbInf.accessibleTables) == 'N' && */ - !pDb->TablePrivileges(tableName,"SELECT",NULL,tablePath)) - s = new wxString("Current logged in user has insufficient privileges to access this table.\n"); - } - - if (s) - { wxString p; + if (wxStrcmp(tablePath,"")) p.sprintf("Error opening '%s/%s'.\n",tablePath,tableName); - else + else p.sprintf("Error opening '%s'.\n", tableName); - p += (*s); + p += s; pDb->LogError(p.GetData()); return(FALSE); } // Bind the member variables for field exchange between - // the wxTable object and the ODBC record. + // 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!!! */ @@ -653,6 +745,9 @@ bool wxTable::Open(void) } needComma = FALSE; sqlStmt += ") VALUES ("; + + int insertableCount = 0; + for (i = 0; i < noCols; i++) { if (! colDefs[i].InsertAllowed) @@ -661,113 +756,121 @@ bool wxTable::Open(void) sqlStmt += ","; sqlStmt += "?"; needComma = TRUE; + insertableCount++; } sqlStmt += ")"; - -// pDb->WriteSqlLog(sqlStmt); - + // Prepare the insert statement for execution - if (SQLPrepare(hstmtInsert, (UCHAR FAR *) sqlStmt.GetData(), SQL_NTS) != SQL_SUCCESS) - return(pDb->DispAllErrors(henv, hdbc, hstmtInsert)); + 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); -} // wxTable::Open() +} // wxDbTable::Open() -/********** wxTable::Query() **********/ -bool wxTable::Query(bool forUpdate, bool distinct) +/********** wxDbTable::Query() **********/ +bool wxDbTable::Query(bool forUpdate, bool distinct) { return(query(DB_SELECT_WHERE, forUpdate, distinct)); -} // wxTable::Query() +} // wxDbTable::Query() -/********** wxTable::QueryBySqlStmt() **********/ -bool wxTable::QueryBySqlStmt(char *pSqlStmt) +/********** wxDbTable::QueryBySqlStmt() **********/ +bool wxDbTable::QueryBySqlStmt(const char *pSqlStmt) { pDb->WriteSqlLog(pSqlStmt); return(query(DB_SELECT_STATEMENT, FALSE, FALSE, pSqlStmt)); -} // wxTable::QueryBySqlStmt() +} // wxDbTable::QueryBySqlStmt() -/********** wxTable::QueryMatching() **********/ -bool wxTable::QueryMatching(bool forUpdate, bool distinct) +/********** wxDbTable::QueryMatching() **********/ +bool wxDbTable::QueryMatching(bool forUpdate, bool distinct) { return(query(DB_SELECT_MATCHING, forUpdate, distinct)); -} // wxTable::QueryMatching() +} // wxDbTable::QueryMatching() -/********** wxTable::QueryOnKeyFields() **********/ -bool wxTable::QueryOnKeyFields(bool forUpdate, bool distinct) +/********** wxDbTable::QueryOnKeyFields() **********/ +bool wxDbTable::QueryOnKeyFields(bool forUpdate, bool distinct) { return(query(DB_SELECT_KEYFIELDS, forUpdate, distinct)); -} // wxTable::QueryOnKeyFields() +} // wxDbTable::QueryOnKeyFields() -/********** wxTable::GetPrev() **********/ -bool wxTable::GetPrev(void) +/********** wxDbTable::GetPrev() **********/ +bool wxDbTable::GetPrev(void) { if (pDb->FwdOnlyCursors()) { - wxFAIL_MSG(wxT("GetPrev()::Backward scrolling cursors are not enabled for this instance of wxTable")); + wxFAIL_MSG(wxT("GetPrev()::Backward scrolling cursors are not enabled for this instance of wxDbTable")); return FALSE; } else return(getRec(SQL_FETCH_PRIOR)); -} // wxTable::GetPrev() +} // wxDbTable::GetPrev() -/********** wxTable::operator-- **********/ -bool wxTable::operator--(int) + +/********** wxDbTable::operator-- **********/ +bool wxDbTable::operator--(int) { if (pDb->FwdOnlyCursors()) { - wxFAIL_MSG(wxT("operator--:Backward scrolling cursors are not enabled for this instance of wxTable")); + wxFAIL_MSG(wxT("operator--:Backward scrolling cursors are not enabled for this instance of wxDbTable")); return FALSE; } else return(getRec(SQL_FETCH_PRIOR)); -} // wxTable::operator-- + +} // wxDbTable::operator-- -/********** wxTable::GetFirst() **********/ -bool wxTable::GetFirst(void) +/********** wxDbTable::GetFirst() **********/ +bool wxDbTable::GetFirst(void) { if (pDb->FwdOnlyCursors()) { - wxFAIL_MSG(wxT("GetFirst():Backward scrolling cursors are not enabled for this instance of wxTable")); + wxFAIL_MSG(wxT("GetFirst():Backward scrolling cursors are not enabled for this instance of wxDbTable")); return FALSE; } else return(getRec(SQL_FETCH_FIRST)); -} // wxTable::GetFirst() +} // wxDbTable::GetFirst() -/********** wxTable::GetLast() **********/ -bool wxTable::GetLast(void) + +/********** wxDbTable::GetLast() **********/ +bool wxDbTable::GetLast(void) { if (pDb->FwdOnlyCursors()) { - wxFAIL_MSG(wxT("GetLast()::Backward scrolling cursors are not enabled for this instance of wxTable")); + wxFAIL_MSG(wxT("GetLast()::Backward scrolling cursors are not enabled for this instance of wxDbTable")); return FALSE; } else return(getRec(SQL_FETCH_LAST)); -} // wxTable::GetLast() + +} // wxDbTable::GetLast() -/********** wxTable::GetSelectStmt() **********/ -void wxTable::GetSelectStmt(char *pSqlStmt, int typeOfSelect, bool distinct) +/********** wxDbTable::BuildSelectStmt() **********/ +void wxDbTable::BuildSelectStmt(char *pSqlStmt, int typeOfSelect, bool distinct) { char whereClause[DB_MAX_WHERE_CLAUSE_LEN]; @@ -783,8 +886,13 @@ void wxTable::GetSelectStmt(char *pSqlStmt, int typeOfSelect, bool 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; @@ -835,33 +943,41 @@ void wxTable::GetSelectStmt(char *pSqlStmt, int typeOfSelect, bool distinct) // or build a where clause. The typeOfSelect determines this. switch(typeOfSelect) { - case DB_SELECT_WHERE: - if (where && wxStrlen(where)) // May not want a where clause!!! - { - wxStrcat(pSqlStmt, " WHERE "); - wxStrcat(pSqlStmt, where); - } - break; - case DB_SELECT_KEYFIELDS: - GetWhereClause(whereClause, DB_WHERE_KEYFIELDS); - if (wxStrlen(whereClause)) - { - wxStrcat(pSqlStmt, " WHERE "); - wxStrcat(pSqlStmt, whereClause); - } - break; - case DB_SELECT_MATCHING: - GetWhereClause(whereClause, DB_WHERE_MATCHING); - if (wxStrlen(whereClause)) - { - wxStrcat(pSqlStmt, " WHERE "); - wxStrcat(pSqlStmt, whereClause); - } - break; + 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); @@ -873,11 +989,11 @@ void wxTable::GetSelectStmt(char *pSqlStmt, int typeOfSelect, bool distinct) if (selectForUpdate && CanSelectForUpdate()) wxStrcat(pSqlStmt, " FOR UPDATE"); -} // wxTable::GetSelectStmt() +} // wxDbTable::BuildSelectStmt() -/********** wxTable::GetRowNum() **********/ -UWORD wxTable::GetRowNum(void) +/********** wxDbTable::GetRowNum() **********/ +UWORD wxDbTable::GetRowNum(void) { UDWORD rowNum; @@ -890,11 +1006,11 @@ UWORD wxTable::GetRowNum(void) // Completed successfully return((UWORD) rowNum); -} // wxTable::GetRowNum() +} // wxDbTable::GetRowNum() -/********** wxTable::CloseCursor() **********/ -bool wxTable::CloseCursor(HSTMT cursor) +/********** wxDbTable::CloseCursor() **********/ +bool wxDbTable::CloseCursor(HSTMT cursor) { if (SQLFreeStmt(cursor, SQL_CLOSE) != SQL_SUCCESS) return(pDb->DispAllErrors(henv, hdbc, cursor)); @@ -902,17 +1018,16 @@ bool wxTable::CloseCursor(HSTMT cursor) // Completed successfully return(TRUE); -} // wxTable::CloseCursor() +} // wxDbTable::CloseCursor() -/********** wxTable::CreateTable() **********/ -bool wxTable::CreateTable(bool attemptDrop) +/********** wxDbTable::CreateTable() **********/ +bool wxDbTable::CreateTable(bool attemptDrop) { if (!pDb) return FALSE; int i, j; -// char sqlStmt[DB_MAX_STATEMENT_LEN]; wxString sqlStmt; #ifdef DBDEBUG_CONSOLE @@ -961,7 +1076,7 @@ bool wxTable::CreateTable(bool attemptDrop) continue; // Comma Delimiter if (needComma) - sqlStmt += ","; + sqlStmt += ","; // Column Name sqlStmt += colDefs[i].ColName; sqlStmt += " "; @@ -969,13 +1084,17 @@ bool wxTable::CreateTable(bool attemptDrop) switch(colDefs[i].DbDataType) { case DB_DATA_TYPE_VARCHAR: - sqlStmt += pDb->typeInfVarchar.TypeName; break; + sqlStmt += pDb->GetTypeInfVarchar().TypeName; + break; case DB_DATA_TYPE_INTEGER: - sqlStmt += pDb->typeInfInteger.TypeName; break; + sqlStmt += pDb->GetTypeInfInteger().TypeName; + break; case DB_DATA_TYPE_FLOAT: - sqlStmt += pDb->typeInfFloat.TypeName; break; + sqlStmt += pDb->GetTypeInfFloat().TypeName; + break; case DB_DATA_TYPE_DATE: - sqlStmt += pDb->typeInfDate.TypeName; break; + sqlStmt += pDb->GetTypeInfDate().TypeName; + break; } // For varchars, append the size of the string if (colDefs[i].DbDataType == DB_DATA_TYPE_VARCHAR) @@ -985,10 +1104,13 @@ bool wxTable::CreateTable(bool attemptDrop) // wxStrcat(sqlStmt, itoa(colDefs[i].SzDataObj, s, 10)); // wxStrcat(sqlStmt, ")"); s.sprintf("(%d)", colDefs[i].SzDataObj); - sqlStmt += s.GetData(); + sqlStmt += s.c_str(); } - if (pDb->Dbms() == dbmsSYBASE_ASE || pDb->Dbms() == dbmsMY_SQL) + if (pDb->Dbms() == dbmsDB2 || + pDb->Dbms() == dbmsMY_SQL || + pDb->Dbms() == dbmsSYBASE_ASE || + pDb->Dbms() == dbmsMS_SQL_SERVER) { if (colDefs[i].KeyField) { @@ -1036,14 +1158,14 @@ bool wxTable::CreateTable(bool attemptDrop) // Append the closing parentheses for the create table statement sqlStmt += ")"; - pDb->WriteSqlLog(sqlStmt.GetData()); + pDb->WriteSqlLog(sqlStmt.c_str()); #ifdef DBDEBUG_CONSOLE - cout << endl << sqlStmt.GetData() << endl; + cout << endl << sqlStmt.c_str() << endl; #endif // Execute the CREATE TABLE statement - RETCODE retcode = SQLExecDirect(hstmt, (UCHAR FAR *) sqlStmt.GetData(), SQL_NTS); + 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); @@ -1053,46 +1175,45 @@ bool wxTable::CreateTable(bool attemptDrop) } // Commit the transaction and close the cursor - if (! pDb->CommitTrans()) + if (!pDb->CommitTrans()) return(FALSE); - if (! CloseCursor(hstmt)) + if (!CloseCursor(hstmt)) return(FALSE); // Database table created successfully return(TRUE); -} // wxTable::CreateTable() +} // wxDbTable::CreateTable() -/********** wxTable::DropTable() **********/ -bool wxTable::DropTable() +/********** wxDbTable::DropTable() **********/ +bool wxDbTable::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 + // below for any other databases when those databases are defined // to handle this situation consistently -// char sqlStmt[DB_MAX_STATEMENT_LEN]; wxString sqlStmt; sqlStmt.sprintf("DROP TABLE %s", tableName); - pDb->WriteSqlLog(sqlStmt.GetData()); + pDb->WriteSqlLog(sqlStmt.c_str()); #ifdef DBDEBUG_CONSOLE - cout << endl << sqlStmt.GetData() << endl; + cout << endl << sqlStmt.c_str() << endl; #endif - if (SQLExecDirect(hstmt, (UCHAR FAR *) sqlStmt.GetData(), SQL_NTS) != SQL_SUCCESS) + 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")) // "Base table not found" - { + 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() == dbmsMY_SQL && !wxStrcmp(pDb->sqlState,"S1000")) || // untested - (pDb->Dbms() == dbmsPOSTGRES && !wxStrcmp(pDb->sqlState,"08S01")))) // untested + 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); @@ -1110,30 +1231,110 @@ bool wxTable::DropTable() return(FALSE); return(TRUE); -} // wxTable::DropTable() +} // wxDbTable::DropTable() -/********** wxTable::CreateIndex() **********/ -bool wxTable::CreateIndex(const char * idxName, bool unique, int noIdxCols, CidxDef *pIdxDefs, bool attemptDrop) +/********** wxDbTable::CreateIndex() **********/ +bool wxDbTable::CreateIndex(const char * idxName, bool unique, int noIdxCols, wxDbIdxDef *pIdxDefs, bool attemptDrop) { -// char sqlStmt[DB_MAX_STATEMENT_LEN]; 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++) @@ -1155,14 +1356,14 @@ bool wxTable::CreateIndex(const char * idxName, bool unique, int noIdxCols, Cidx // Append closing parentheses sqlStmt += ")"; - pDb->WriteSqlLog(sqlStmt.GetData()); + pDb->WriteSqlLog(sqlStmt.c_str()); #ifdef DBDEBUG_CONSOLE - cout << endl << sqlStmt.GetData() << endl << endl; + cout << endl << sqlStmt.c_str() << endl << endl; #endif // Execute the CREATE INDEX statement - if (SQLExecDirect(hstmt, (UCHAR FAR *) sqlStmt.GetData(), SQL_NTS) != SQL_SUCCESS) + if (SQLExecDirect(hstmt, (UCHAR FAR *) sqlStmt.c_str(), SQL_NTS) != SQL_SUCCESS) { pDb->DispAllErrors(henv, hdbc, hstmt); pDb->RollbackTrans(); @@ -1179,44 +1380,47 @@ bool wxTable::CreateIndex(const char * idxName, bool unique, int noIdxCols, Cidx // Index Created Successfully return(TRUE); -} // wxTable::CreateIndex() +} // wxDbTable::CreateIndex() -/********** wxTable::DropIndex() **********/ -bool wxTable::DropIndex(const char * idxName) +/********** wxDbTable::DropIndex() **********/ +bool wxDbTable::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]; wxString sqlStmt; - if (pDb->Dbms() == dbmsACCESS) + if (pDb->Dbms() == dbmsACCESS || pDb->Dbms() == dbmsMY_SQL) sqlStmt.sprintf("DROP INDEX %s ON %s",idxName,tableName); - else if (pDb->Dbms() == dbmsSYBASE_ASE) + 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.GetData()); + pDb->WriteSqlLog(sqlStmt.c_str()); #ifdef DBDEBUG_CONSOLE - cout << endl << sqlStmt.GetData() << endl; + cout << endl << sqlStmt.c_str() << endl; #endif - if (SQLExecDirect(hstmt, (UCHAR FAR *) sqlStmt.GetData(), SQL_NTS) != SQL_SUCCESS) + 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,"S0002")) || // Base table not found - (pDb->Dbms() == dbmsMY_SQL && !wxStrcmp(pDb->sqlState,"42S02")) // untested - )) + 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); @@ -1234,14 +1438,50 @@ bool wxTable::DropIndex(const char * idxName) return(FALSE); return(TRUE); -} // wxTable::DropIndex() +} // 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; + } + + if (colNo != first) + tempStr += ","; + tempStr += colDefs[colNo].ColName; + colNo = va_arg (argptr, int); + } + va_end (argptr); /* Reset variable arguments. */ + + SetOrderByClause(tempStr.c_str()); + + return (!abort); +} // wxDbTable::SetOrderByColNums() -/********** wxTable::Insert() **********/ -int wxTable::Insert(void) + +/********** wxDbTable::Insert() **********/ +int wxDbTable::Insert(void) { assert(!queryOnly); - if (queryOnly) + if (queryOnly || !insertable) return(DB_FAILURE); bindInsertParams(); @@ -1266,11 +1506,11 @@ int wxTable::Insert(void) // Record inserted into the datasource successfully return(DB_SUCCESS); -} // wxTable::Insert() +} // wxDbTable::Insert() -/********** wxTable::Update() **********/ -bool wxTable::Update(void) +/********** wxDbTable::Update() **********/ +bool wxDbTable::Update(void) { assert(!queryOnly); if (queryOnly) @@ -1279,22 +1519,22 @@ bool wxTable::Update(void) char sqlStmt[DB_MAX_STATEMENT_LEN]; // Build the SQL UPDATE statement - GetUpdateStmt(sqlStmt, DB_UPD_KEYFIELDS); + BuildUpdateStmt(sqlStmt, DB_UPD_KEYFIELDS); pDb->WriteSqlLog(sqlStmt); #ifdef DBDEBUG_CONSOLE - cout << endl << sqlStmt.GetData() << endl << endl; + cout << endl << sqlStmt << endl << endl; #endif // Execute the SQL UPDATE statement return(execUpdate(sqlStmt)); -} // wxTable::Update() +} // wxDbTable::Update() -/********** wxTable::Update(pSqlStmt) **********/ -bool wxTable::Update(const char *pSqlStmt) +/********** wxDbTable::Update(pSqlStmt) **********/ +bool wxDbTable::Update(const char *pSqlStmt) { assert(!queryOnly); if (queryOnly) @@ -1304,11 +1544,11 @@ bool wxTable::Update(const char *pSqlStmt) return(execUpdate(pSqlStmt)); -} // wxTable::Update(pSqlStmt) +} // wxDbTable::Update(pSqlStmt) -/********** wxTable::UpdateWhere() **********/ -bool wxTable::UpdateWhere(const char *pWhereClause) +/********** wxDbTable::UpdateWhere() **********/ +bool wxDbTable::UpdateWhere(const char *pWhereClause) { assert(!queryOnly); if (queryOnly) @@ -1317,22 +1557,22 @@ bool wxTable::UpdateWhere(const char *pWhereClause) char sqlStmt[DB_MAX_STATEMENT_LEN]; // Build the SQL UPDATE statement - GetUpdateStmt(sqlStmt, DB_UPD_WHERE, pWhereClause); + BuildUpdateStmt(sqlStmt, DB_UPD_WHERE, pWhereClause); pDb->WriteSqlLog(sqlStmt); #ifdef DBDEBUG_CONSOLE - cout << endl << sqlStmt.GetData() << endl << endl; + cout << endl << sqlStmt << endl << endl; #endif // Execute the SQL UPDATE statement return(execUpdate(sqlStmt)); -} // wxTable::UpdateWhere() +} // wxDbTable::UpdateWhere() -/********** wxTable::Delete() **********/ -bool wxTable::Delete(void) +/********** wxDbTable::Delete() **********/ +bool wxDbTable::Delete(void) { assert(!queryOnly); if (queryOnly) @@ -1341,18 +1581,18 @@ bool wxTable::Delete(void) char sqlStmt[DB_MAX_STATEMENT_LEN]; // Build the SQL DELETE statement - GetDeleteStmt(sqlStmt, DB_DEL_KEYFIELDS); + BuildDeleteStmt(sqlStmt, DB_DEL_KEYFIELDS); pDb->WriteSqlLog(sqlStmt); // Execute the SQL DELETE statement return(execDelete(sqlStmt)); -} // wxTable::Delete() +} // wxDbTable::Delete() -/********** wxTable::DeleteWhere() **********/ -bool wxTable::DeleteWhere(const char *pWhereClause) +/********** wxDbTable::DeleteWhere() **********/ +bool wxDbTable::DeleteWhere(const char *pWhereClause) { assert(!queryOnly); if (queryOnly) @@ -1361,18 +1601,18 @@ bool wxTable::DeleteWhere(const char *pWhereClause) char sqlStmt[DB_MAX_STATEMENT_LEN]; // Build the SQL DELETE statement - GetDeleteStmt(sqlStmt, DB_DEL_WHERE, pWhereClause); + BuildDeleteStmt(sqlStmt, DB_DEL_WHERE, pWhereClause); pDb->WriteSqlLog(sqlStmt); // Execute the SQL DELETE statement return(execDelete(sqlStmt)); -} // wxTable::DeleteWhere() +} // wxDbTable::DeleteWhere() -/********** wxTable::DeleteMatching() **********/ -bool wxTable::DeleteMatching(void) +/********** wxDbTable::DeleteMatching() **********/ +bool wxDbTable::DeleteMatching(void) { assert(!queryOnly); if (queryOnly) @@ -1381,18 +1621,18 @@ bool wxTable::DeleteMatching(void) char sqlStmt[DB_MAX_STATEMENT_LEN]; // Build the SQL DELETE statement - GetDeleteStmt(sqlStmt, DB_DEL_MATCHING); + BuildDeleteStmt(sqlStmt, DB_DEL_MATCHING); pDb->WriteSqlLog(sqlStmt); // Execute the SQL DELETE statement return(execDelete(sqlStmt)); -} // wxTable::DeleteMatching() +} // wxDbTable::DeleteMatching() -/********** wxTable::GetUpdateStmt() **********/ -void wxTable::GetUpdateStmt(char *pSqlStmt, int typeOfUpd, const char *pWhereClause) +/********** wxDbTable::BuildUpdateStmt() **********/ +void wxDbTable::BuildUpdateStmt(char *pSqlStmt, int typeOfUpd, const char *pWhereClause) { assert(!queryOnly); if (queryOnly) @@ -1424,40 +1664,40 @@ void wxTable::GetUpdateStmt(char *pSqlStmt, int typeOfUpd, const char *pWhereCla 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[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, ROWID_LEN, &cb) == SQL_SUCCESS) + 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()) { - wxStrcat(pSqlStmt, "ROWID = '"); - wxStrcat(pSqlStmt, rowid); - wxStrcat(pSqlStmt, "'"); - break; + 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. - GetWhereClause(whereClause, DB_WHERE_KEYFIELDS); - wxStrcat(pSqlStmt, whereClause); - break; - case DB_UPD_WHERE: - wxStrcat(pSqlStmt, pWhereClause); - 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; } -} // GetUpdateStmt() +} // BuildUpdateStmt() -/********** wxTable::GetDeleteStmt() **********/ -void wxTable::GetDeleteStmt(char *pSqlStmt, int typeOfDel, const char *pWhereClause) +/********** wxDbTable::BuildDeleteStmt() **********/ +void wxDbTable::BuildDeleteStmt(char *pSqlStmt, int typeOfDel, const char *pWhereClause) { assert(!queryOnly); if (queryOnly) @@ -1480,47 +1720,48 @@ void wxTable::GetDeleteStmt(char *pSqlStmt, int typeOfDel, const char *pWhereCla // 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, (UCHAR*) rowid, ROWID_LEN, &cb) == SQL_SUCCESS) + 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()) { - wxStrcat(pSqlStmt, "ROWID = '"); - wxStrcat(pSqlStmt, rowid); - wxStrcat(pSqlStmt, "'"); - break; + 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. - GetWhereClause(whereClause, DB_WHERE_KEYFIELDS); - wxStrcat(pSqlStmt, whereClause); - break; - case DB_DEL_WHERE: - wxStrcat(pSqlStmt, pWhereClause); - break; - case DB_DEL_MATCHING: - GetWhereClause(whereClause, DB_WHERE_MATCHING); - wxStrcat(pSqlStmt, whereClause); - 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; } -} // GetDeleteStmt() +} // BuildDeleteStmt() -/********** wxTable::GetWhereClause() **********/ -void wxTable::GetWhereClause(char *pWhereClause, int typeOfWhere, const char *qualTableName) +/********** 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. */ { @@ -1533,7 +1774,7 @@ void wxTable::GetWhereClause(char *pWhereClause, int typeOfWhere, const char *qu { // 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)))) + (typeOfWhere == DB_WHERE_MATCHING && (!IsColNull(i)))) { // Skip over timestamp columns if (colDefs[i].SqlCtype == SQL_C_TIMESTAMP) @@ -1550,85 +1791,98 @@ void wxTable::GetWhereClause(char *pWhereClause, int typeOfWhere, const char *qu wxStrcat(pWhereClause, "."); } wxStrcat(pWhereClause, colDefs[i].ColName); - wxStrcat(pWhereClause, " = "); + 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; + 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); } } -} // wxTable::GetWhereClause() +} // wxDbTable::BuildWhereClause() -/********** wxTable::IsColNull() **********/ -bool wxTable::IsColNull(int colNo) +/********** 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) + 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); - else - return(FALSE); - default: - return(TRUE); } -} // wxTable::IsColNull() +*/ + return (colDefs[colNo].Null); +} // wxDbTable::IsColNull() -/********** wxTable::CanSelectForUpdate() **********/ -bool wxTable::CanSelectForUpdate(void) +/********** wxDbTable::CanSelectForUpdate() **********/ +bool wxDbTable::CanSelectForUpdate(void) { + if (queryOnly) + return FALSE; + if (pDb->Dbms() == dbmsMY_SQL) return FALSE; - if (pDb->dbInf.posStmts & SQL_PS_SELECT_FOR_UPDATE) + if ((pDb->Dbms() == dbmsORACLE) || + (pDb->dbInf.posStmts & SQL_PS_SELECT_FOR_UPDATE)) return(TRUE); else return(FALSE); -} // wxTable::CanSelectForUpdate() +} // wxDbTable::CanSelectForUpdate() -/********** wxTable::CanUpdByROWID() **********/ -bool wxTable::CanUpdByROWID(void) +/********** wxDbTable::CanUpdByROWID() **********/ +bool wxDbTable::CanUpdByROWID(void) { /* * NOTE: Returning FALSE for now until this can be debugged, @@ -1641,53 +1895,52 @@ bool wxTable::CanUpdByROWID(void) else return(FALSE); -} // wxTable::CanUpdByROWID() +} // wxDbTable::CanUpdByROWID() -/********** wxTable::IsCursorClosedOnCommit() **********/ -bool wxTable::IsCursorClosedOnCommit(void) +/********** wxDbTable::IsCursorClosedOnCommit() **********/ +bool wxDbTable::IsCursorClosedOnCommit(void) { if (pDb->dbInf.cursorCommitBehavior == SQL_CB_PRESERVE) return(FALSE); else return(TRUE); -} // wxTable::IsCursorClosedOnCommit() +} // wxDbTable::IsCursorClosedOnCommit() -/********** wxTable::ClearMemberVars() **********/ -void wxTable::ClearMemberVars(void) + +/********** wxDbTable::ClearMemberVar() **********/ +void wxDbTable::ClearMemberVar(int colNo, bool setToNull) { - // Loop through the columns setting each member variable to zero - int i; - for (i = 0; i < noCols; i++) + assert(colNo < noCols); + + switch(colDefs[colNo].SqlCtype) { - switch(colDefs[i].SqlCtype) - { case SQL_C_CHAR: - ((UCHAR FAR *) colDefs[i].PtrDataObj)[0] = 0; + ((UCHAR FAR *) colDefs[colNo].PtrDataObj)[0] = 0; break; case SQL_C_SSHORT: - *((SWORD *) colDefs[i].PtrDataObj) = 0; + *((SWORD *) colDefs[colNo].PtrDataObj) = 0; break; case SQL_C_USHORT: - *((UWORD*) colDefs[i].PtrDataObj) = 0; + *((UWORD*) colDefs[colNo].PtrDataObj) = 0; break; case SQL_C_SLONG: - *((SDWORD *) colDefs[i].PtrDataObj) = 0; + *((SDWORD *) colDefs[colNo].PtrDataObj) = 0; break; case SQL_C_ULONG: - *((UDWORD *) colDefs[i].PtrDataObj) = 0; + *((UDWORD *) colDefs[colNo].PtrDataObj) = 0; break; case SQL_C_FLOAT: - *((SFLOAT *) colDefs[i].PtrDataObj) = 0.0f; + *((SFLOAT *) colDefs[colNo].PtrDataObj) = 0.0f; break; case SQL_C_DOUBLE: - *((SDOUBLE *) colDefs[i].PtrDataObj) = 0.0f; + *((SDOUBLE *) colDefs[colNo].PtrDataObj) = 0.0f; break; case SQL_C_TIMESTAMP: TIMESTAMP_STRUCT *pDt; - pDt = (TIMESTAMP_STRUCT *) colDefs[i].PtrDataObj; + pDt = (TIMESTAMP_STRUCT *) colDefs[colNo].PtrDataObj; pDt->year = 0; pDt->month = 0; pDt->day = 0; @@ -1696,14 +1949,27 @@ void wxTable::ClearMemberVars(void) pDt->second = 0; pDt->fraction = 0; break; - } } -} // wxTable::ClearMemberVars() + if (setToNull) + SetColNull(colNo); +} // wxDbTable::ClearMemberVar() + +/********** wxDbTable::ClearMemberVars() **********/ +void wxDbTable::ClearMemberVars(bool setToNull) +{ + int i; + + // Loop through the columns setting each member variable to zero + for (i=0; i < noCols; i++) + ClearMemberVar(i,setToNull); + +} // wxDbTable::ClearMemberVars() -/********** wxTable::SetQueryTimeout() **********/ -bool wxTable::SetQueryTimeout(UDWORD nSeconds) + +/********** wxDbTable::SetQueryTimeout() **********/ +bool wxDbTable::SetQueryTimeout(UDWORD nSeconds) { if (SQLSetStmtOption(hstmtInsert, SQL_QUERY_TIMEOUT, nSeconds) != SQL_SUCCESS) return(pDb->DispAllErrors(henv, hdbc, hstmtInsert)); @@ -1717,13 +1983,13 @@ bool wxTable::SetQueryTimeout(UDWORD nSeconds) // Completed Successfully return(TRUE); -} // wxTable::SetQueryTimeout() +} // wxDbTable::SetQueryTimeout() -/********** wxTable::SetColDefs() **********/ -void wxTable::SetColDefs (int index, const char *fieldName, int dataType, void *pData, - int cType, int size, bool keyField, bool upd, - bool insAllow, bool derivedCol) +/********** 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; @@ -1756,57 +2022,32 @@ void wxTable::SetColDefs (int index, const char *fieldName, int dataType, void * colDefs[index].Null = FALSE; -} // wxTable::SetColDefs() +} // wxDbTable::SetColDefs() -/********** wxTable::SetColDef() **********/ -// BJO20000121 : changed prototype in order to return proper pointer on wxColDataPtr's array -//bool wxTable::SetColDefs(wxColInf *pColInfs, ULONG numCols, wxColDataPtr *pColDataPtrs) -wxColDataPtr* wxTable::SetColDefs (wxColInf *pColInfs, ULONG numCols) +/********** wxDbTable::SetColDefs() **********/ +wxDbColDataPtr* wxDbTable::SetColDefs(wxDbColInf *pColInfs, ULONG numCols) { assert(pColInfs); - wxColDataPtr *pColDataPtrs = NULL; + wxDbColDataPtr *pColDataPtrs = NULL; if (pColInfs) { ULONG index; - - pColDataPtrs = new wxColDataPtr[numCols+1]; + pColDataPtrs = new wxDbColDataPtr[numCols+1]; for (index = 0; index < numCols; index++) { -/* - wxString title,msg; - title.sprintf("Catalog: %s, Schema: %s, Table name: %s",pColInfs[index].catalog,pColInfs[index].schema,pColInfs[index].tableName); - msg.sprintf("Column name: %s\nData type: %04d\nType name: %s\nColumn size: %d\nBuffer len: %d\nDecimals:%d\nRadix: %d\nNullable: %d\nRemarks: %s", - pColInfs[index].colName,pColInfs[index].sqlDataType,pColInfs[index].typeName,pColInfs[index].columnSize,pColInfs[index].bufferLength,pColInfs[index].decimalDigits,pColInfs[index].numPrecRadix,pColInfs[index].nullable,pColInfs[index].remarks); - msg += " \nDB_DATA_TYPE: "; - switch(pColInfs[index].dbDataType) - { - case DB_DATA_TYPE_VARCHAR: - msg += pDb->typeInfVarchar.TypeName; break; - case DB_DATA_TYPE_INTEGER: - msg += pDb->typeInfInteger.TypeName; break; - case DB_DATA_TYPE_FLOAT: - msg += pDb->typeInfFloat.TypeName; break; - case DB_DATA_TYPE_DATE: - msg += pDb->typeInfDate.TypeName; break; - } - wxMessageBox(msg.GetData(),title.GetData()); -*/ // 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].bufferLength; - pColDataPtrs[index].SqlCtype = SQL_C_CHAR; - break; - } + 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)) { @@ -1821,9 +2062,7 @@ wxColDataPtr* wxTable::SetColDefs (wxColInf *pColInfs, ULONG numCols) pColDataPtrs[index].SqlCtype = SQL_C_SSHORT; } break; - } case DB_DATA_TYPE_FLOAT: - { // Can be float or double if (pColInfs[index].bufferLength == sizeof(float)) { @@ -1838,39 +2077,36 @@ wxColDataPtr* wxTable::SetColDefs (wxColInf *pColInfs, ULONG numCols) 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); + SetColDefs (index,pColInfs[index].colName,pColInfs[index].dbDataType, pColDataPtrs[index].PtrDataObj, pColDataPtrs[index].SqlCtype, pColDataPtrs[index].SzDataObj); } } + return (pColDataPtrs); -} // wxTable::SetColDef() + +} // wxDbTable::SetColDefs() -/********** wxTable::SetCursor() **********/ -void wxTable::SetCursor(HSTMT *hstmtActivate) +/********** wxDbTable::SetCursor() **********/ +void wxDbTable::SetCursor(HSTMT *hstmtActivate) { - if (hstmtActivate == DEFAULT_CURSOR) + if (hstmtActivate == wxDB_DEFAULT_CURSOR) hstmt = *hstmtDefault; else hstmt = *hstmtActivate; -} // wxTable::SetCursor() +} // wxDbTable::SetCursor() -/********** wxTable::Count(const char *) **********/ -ULONG wxTable::Count(const char *args) +/********** wxDbTable::Count(const char *) **********/ +ULONG wxDbTable::Count(const char *args) { - ULONG l; -// char sqlStmt[DB_MAX_STATEMENT_LEN]; + ULONG count; wxString sqlStmt; SDWORD cb; @@ -1879,30 +2115,37 @@ ULONG wxTable::Count(const char *args) 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.GetData()); + pDb->WriteSqlLog(sqlStmt.c_str()); // Initialize the Count cursor if it's not already initialized if (!hstmtCount) { - hstmtCount = NewCursor(FALSE,FALSE); + hstmtCount = GetNewCursor(FALSE,FALSE); assert(hstmtCount); if (!hstmtCount) return(0); } // Execute the SQL statement - if (SQLExecDirect(*hstmtCount, (UCHAR FAR *) sqlStmt.GetData(), SQL_NTS) != SQL_SUCCESS) + if (SQLExecDirect(*hstmtCount, (UCHAR FAR *) sqlStmt.c_str(), SQL_NTS) != SQL_SUCCESS) { pDb->DispAllErrors(henv, hdbc, *hstmtCount); return(0); @@ -1916,7 +2159,7 @@ ULONG wxTable::Count(const char *args) } // Obtain the result - if (SQLGetData(*hstmtCount, 1, SQL_C_ULONG, &l, sizeof(l), &cb) != SQL_SUCCESS) + if (SQLGetData(*hstmtCount, 1, SQL_C_ULONG, &count, sizeof(count), &cb) != SQL_SUCCESS) { pDb->DispAllErrors(henv, hdbc, *hstmtCount); return(0); @@ -1927,24 +2170,27 @@ ULONG wxTable::Count(const char *args) pDb->DispAllErrors(henv, hdbc, *hstmtCount); // Return the record count - return(l); + return(count); -} // wxTable::Count() +} // wxDbTable::Count() -/********** wxTable::Refresh() **********/ -bool wxTable::Refresh(void) +/********** wxDbTable::Refresh() **********/ +bool wxDbTable::Refresh(void) { 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]; @@ -1952,12 +2198,12 @@ bool wxTable::Refresh(void) if (CanUpdByROWID()) { SDWORD cb; - char rowid[ROWID_LEN+1]; + 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, ROWID_LEN, &cb) == SQL_SUCCESS) + if (SQLGetData(hstmt, noCols+1, SQL_C_CHAR, (UCHAR*) rowid, wxDB_ROWID_LEN, &cb) == SQL_SUCCESS) { wxStrcat(whereClause, queryTableName); wxStrcat(whereClause, ".ROWID = '"); @@ -1968,11 +2214,11 @@ bool wxTable::Refresh(void) // If unable to use the ROWID, build a where clause from the keyfields if (wxStrlen(whereClause) == 0) - GetWhereClause(whereClause, DB_WHERE_KEYFIELDS, queryTableName); + BuildWhereClause(whereClause, DB_WHERE_KEYFIELDS, queryTableName); // Requery the record where = whereClause; - orderBy = 0; + orderBy = ""; if (!Query()) result = FALSE; @@ -1992,22 +2238,27 @@ bool wxTable::Refresh(void) return(result); -} // wxTable::Refresh() +} // wxDbTable::Refresh() -/********** wxTable::SetNull(int colNo) **********/ -bool wxTable::SetNull(int colNo) +/********** wxDbTable::SetColNull(int colNo, bool set) **********/ +bool wxDbTable::SetColNull(int colNo, bool set) { if (colNo < noCols) - return(colDefs[colNo].Null = TRUE); + { + 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); -} // wxTable::SetNull(int colNo) +} // wxDbTable::SetColNull(int colNo) -/********** wxTable::SetNull(char *colName) **********/ -bool wxTable::SetNull(const char *colName) +/********** wxDbTable::SetColNull(char *colName, bool set) **********/ +bool wxDbTable::SetColNull(const char *colName, bool set) { int i; for (i = 0; i < noCols; i++) @@ -2017,15 +2268,20 @@ bool wxTable::SetNull(const char *colName) } if (i < noCols) - return(colDefs[i].Null = TRUE); + { + 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); -} // wxTable::SetNull(char *colName) +} // wxDbTable::SetColNull(char *colName) -/********** wxTable::NewCursor() **********/ -HSTMT *wxTable::NewCursor(bool setCursor, bool bindColumns) +/********** wxDbTable::GetNewCursor() **********/ +HSTMT *wxDbTable::GetNewCursor(bool setCursor, bool bindColumns) { HSTMT *newHSTMT = new HSTMT; assert(newHSTMT); @@ -2060,11 +2316,11 @@ HSTMT *wxTable::NewCursor(bool setCursor, bool bindColumns) return(newHSTMT); -} // wxTable::NewCursor() +} // wxDbTable::GetNewCursor() -/********** wxTable::DeleteCursor() **********/ -bool wxTable::DeleteCursor(HSTMT *hstmtDel) +/********** wxDbTable::DeleteCursor() **********/ +bool wxDbTable::DeleteCursor(HSTMT *hstmtDel) { bool result = TRUE; @@ -2081,7 +2337,7 @@ bool wxTable::DeleteCursor(HSTMT *hstmtDel) return(result); -} // wxTable::DeleteCursor() +} // wxDbTable::DeleteCursor() #endif // wxUSE_ODBC