From: George Tasker Date: Thu, 7 Oct 1999 14:19:57 +0000 (+0000) Subject: Added support for dBase, PostGres, MySQL, Access, MS SQL Server. X-Git-Url: https://git.saurik.com/wxWidgets.git/commitdiff_plain/a2115c88f381b95cf6d423a5d3128ccbe20c4fa1 Added support for dBase, PostGres, MySQL, Access, MS SQL Server. Added QUERY_ONLY data connection types. SQL logging code addded. Cleaned up handling compilation under both 1.6x and 2.x. Added debug code to warn when connections were not released on program termination. MS-VC6 corrections due to larger memory buffers required. Parameter added to not require CreateView() and CreateTable() to drop the view or table first. DropView() function added. Database UserIDs can now be passed to functions that need them. SQLUnbind() called where needed now to prevent resource leaks. TableExists() function now handles dBase files. Dbms() function added to determine which database the program is currently running against. Comments have been added to the Dbms() function to indicate issues specific to different data sources. Dynamic cursor support added (no longer creates 5-7 cursors for every wxTable instance). wxTable dtor is now virtual. Parameter added to not require CreateIndex() and CreateTable() to drop the view or table first. DropIndex() and DropTable() functions added. git-svn-id: https://svn.wxwidgets.org/svn/wx/wxWidgets/trunk@3868 c3d73ce0-8a6f-49c7-b76d-6d57e0e08775 --- diff --git a/include/wx/db.h b/include/wx/db.h index 466000ddde..979f566ae7 100644 --- a/include/wx/db.h +++ b/include/wx/db.h @@ -5,13 +5,20 @@ // source such as opening and closing the data source. // Author: Doug Card // Modified by: -// Mods: Dec, 1998: Added support for SQL statement logging and database -// cataloging +// Mods: Dec, 1998: +// -Added support for SQL statement logging and database cataloging +// April, 1999 +// -Added QUERY_ONLY mode support to reduce default number of cursors +// -Added additional SQL logging code +// -Added DEBUG-ONLY tracking of Ctable objects to detect orphaned DB connections +// -Set ODBC option to only read committed writes to the DB so all +// databases operate the same in that respect +// // 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 @@ -30,54 +37,85 @@ #ifndef DB_DOT_H #define DB_DOT_H - -#ifdef __GNUG__ -#pragma interface "db.h" + +// Use this line for wxWindows v1.x +//#include "wx_ver.h" +// Use this line for wxWindows v2.x +#include "wx/version.h" + +#if wxMAJOR_VERSION == 2 + #ifdef __GNUG__ + #pragma interface "db.h" + #endif #endif -#if defined(__WXMSW__) || defined(WIN32) +#if defined(wx_msw) || defined(__WXMSW__) || defined(WIN32) #include #endif +#ifdef _IODBC_ +#if wxMAJOR_VERSION == 2 + extern "C" { + #include "../../src/iodbc/isql.h" + #include "../../src/iodbc/isqlext.h" + } +#else // version == 1 + extern "C" { + #include "iodbc.h" + #include "isqlext.h" + } +#endif + typedef float SFLOAT; + typedef double SDOUBLE; + typedef unsigned int UINT; + #define ULONG UDWORD +#else // msw + #define ODBCVER 0x0250 + #include + #include +#endif -#ifdef __WXGTK__ - -extern "C" { -#include "../../src/iodbc/isql.h" -#include "../../src/iodbc/isqlext.h" -typedef float SFLOAT; -typedef double SDOUBLE; -typedef unsigned int UINT; -#define ULONG UDWORD +#ifdef __UNIX__ +# ifndef strnicmp +# define strnicmp strncasecmp +# endif +# ifndef stricmp +# define stricmp strcasecmp +# endif +#else +# include +#endif -} +enum enumDummy {enumDum1}; -#else +#define SQL_C_BOOLEAN(datatype) (sizeof(datatype) == 1 ? SQL_C_UTINYINT : (sizeof(datatype) == 2 ? SQL_C_USHORT : SQL_C_ULONG)) +// #define SQL_C_BOOLEAN (sizeof(Bool) == 2 ? SQL_C_USHORT : SQL_C_ULONG) -#define ODBCVER 0x0250 -#include -#include +#define SQL_C_ENUM (sizeof(enumDummy) == 2 ? SQL_C_USHORT : SQL_C_ULONG) +#ifndef TRUE +#define TRUE 1 #endif -enum enumDummy {enumDum1}; +#ifndef FALSE +#define FALSE 0 +#endif -#define SQL_C_BOOLEAN (sizeof(int) == 2 ? SQL_C_USHORT : SQL_C_ULONG) -#define SQL_C_ENUM (sizeof(enumDummy) == 2 ? SQL_C_USHORT : SQL_C_ULONG) //glt 2-21-97 +const int DB_PATH_MAX = 254; // Database Globals const int DB_TYPE_NAME_LEN = 40; -const int DB_MAX_STATEMENT_LEN = 2048; -const int DB_MAX_WHERE_CLAUSE_LEN = 1024; -const int DB_MAX_ERROR_MSG_LEN = 512; -const int DB_MAX_ERROR_HISTORY = 5; +const int DB_MAX_STATEMENT_LEN = 2048; +const int DB_MAX_WHERE_CLAUSE_LEN = 1024; +const int DB_MAX_ERROR_MSG_LEN = 512; +const int DB_MAX_ERROR_HISTORY = 5; const int DB_MAX_TABLE_NAME_LEN = 128; const int DB_MAX_COLUMN_NAME_LEN = 128; -const int DB_DATA_TYPE_VARCHAR = 1; -const int DB_DATA_TYPE_INTEGER = 2; +const int DB_DATA_TYPE_VARCHAR = 1; +const int DB_DATA_TYPE_INTEGER = 2; const int DB_DATA_TYPE_FLOAT = 3; -const int DB_DATA_TYPE_DATE = 4; +const int DB_DATA_TYPE_DATE = 4; const int DB_SELECT_KEYFIELDS = 1; const int DB_SELECT_WHERE = 2; @@ -92,14 +130,7 @@ const int DB_DEL_WHERE = 2; const int DB_DEL_MATCHING = 3; const int DB_WHERE_KEYFIELDS = 1; -const int DB_WHERE_MATCHING = 2; - -const int DB_CURSOR0 = 0; -const int DB_CURSOR1 = 1; -const int DB_CURSOR2 = 2; -//const int DB_CURSOR3 = 3; -//const int DB_CURSOR4 = 4; -//const int DB_CURSOR5 = 5; +const int DB_WHERE_MATCHING = 2; const int DB_GRANT_SELECT = 1; const int DB_GRANT_INSERT = 2; @@ -110,8 +141,8 @@ const int DB_GRANT_ALL = DB_GRANT_SELECT | DB_GRANT_INSERT | DB_GRANT_UPDA // ODBC Error codes (derived from ODBC SqlState codes) enum ODBC_ERRORS { - DB_FAILURE = 0, - DB_SUCCESS = 1, + DB_FAILURE = 0, + DB_SUCCESS = 1, DB_ERR_NOT_IN_USE, DB_ERR_GENERAL_WARNING, // SqlState = '01000' DB_ERR_DISCONNECT_ERROR, // SqlState = '01002' @@ -207,9 +238,15 @@ enum ODBC_ERRORS struct DbStuff { HENV Henv; - char Dsn[SQL_MAX_DSN_LENGTH+1]; // Data Source Name - char Uid[20]; // User ID - char AuthStr[20]; // Authorization string (password) + char Dsn[SQL_MAX_DSN_LENGTH+1]; // Data Source Name + char Uid[20+1]; // User ID + char AuthStr[20+1]; // Authorization string (password) + + char description[SQL_MAX_DSN_LENGTH+1]; // Not sure what the max length is + char fileType[SQL_MAX_DSN_LENGTH+1]; // Not sure what the max length is + + // Optionals needed for some databases like dBase + char defaultDir[DB_PATH_MAX]; // Directory that db file resides in }; typedef struct @@ -236,6 +273,30 @@ enum sqlLog sqlLogON }; +enum dbms +{ + dbmsUNIDENTIFIED, + dbmsORACLE, + dbmsSYBASE_ASA, // Adaptive Server Anywhere + dbmsSYBASE_ASE, // Adaptive Server Enterprise + dbmsMS_SQL_SERVER, + dbmsMY_SQL, + dbmsPOSTGRES, + dbmsACCESS, + dbmsDBASE +}; + +typedef enum dbms DBMS; + +// The wxDB::errorList is copied to this variable when the wxDB object +// is closed. This way, the error list is still available after the +// database object is closed. This is necessary if the database +// connection fails so the calling application can show the operator +// why the connection failed. Note: as each wxDB object is closed, it +// will overwrite the errors of the previously destroyed wxDB object in +// this variable. +extern char DBerrorList[DB_MAX_ERROR_HISTORY][DB_MAX_ERROR_MSG_LEN]; + class WXDLLEXPORT wxDB { private: @@ -261,7 +322,7 @@ public: struct { char dbmsName[40]; // Name of the dbms product - char dbmsVer[40]; // Version # of the dbms product + char dbmsVer[64]; // Version # of the dbms product char driverName[40]; // Driver name char odbcVer[60]; // ODBC version of the driver char drvMgrOdbcVer[60]; // ODBC version of the driver manager @@ -308,6 +369,9 @@ public: //Error reporting mode bool silent; + // Number of Ctable objects connected to this db object + unsigned int nTables; + // Inf. about logical data types VARCHAR, INTEGER, FLOAT and DATE. // This inf. is obtained from the ODBC driver by use of the // SQLGetTypeInfo() function. The key piece of inf. is the @@ -324,24 +388,28 @@ public: bool DispAllErrors(HENV aHenv, HDBC aHdbc = SQL_NULL_HDBC, HSTMT aHstmt = SQL_NULL_HSTMT); bool GetNextError(HENV aHenv, HDBC aHdbc = SQL_NULL_HDBC, HSTMT aHstmt = SQL_NULL_HSTMT); void DispNextError(void); - bool CreateView(char *viewName, char *colList, char *pSqlStmt); + bool CreateView(char *viewName, char *colList, char *pSqlStmt, bool attemptDrop=TRUE); + bool DropView(char *viewName); bool ExecSql(char *pSqlStmt); + bool GetNext(void); + bool GetData(UWORD colNo, SWORD cType, PTR pData, SDWORD maxLen, SDWORD FAR *cbReturned); bool Grant(int privileges, char *tableName, char *userList = "PUBLIC"); int TranslateSqlState(char *SQLState); bool Catalog(char *userID, char *fileName = "Catalog.txt"); - CcolInf *GetColumns(char *tableName[]); + CcolInf *GetColumns(char *tableName[], char *userID=NULL); char *GetDatabaseName(void) {return dbInf.dbmsName;} char *GetDataSource(void) {return dsn;} - char *GetUsername(void) {return uid;} - char *GetPassword(void) {return authStr;} + char *GetUsername(void) {return uid;} + char *GetPassword(void) {return authStr;} bool IsOpen(void) {return dbIsOpen;} HENV GetHENV(void) {return henv;} HDBC GetHDBC(void) {return hdbc;} - HSTMT GetHSTMT(void) {return hstmt;} - bool TableExists(char *tableName); // Table name can refer to a table, view, alias or synonym + HSTMT GetHSTMT(void) {return hstmt;} + bool TableExists(char *tableName, char *userID=NULL, char *path=NULL); // Table name can refer to a table, view, alias or synonym void LogError(char *errMsg, char *SQLState = 0) {logError(errMsg, SQLState);} bool SqlLog(enum sqlLog state, char *filename = "sqllog.txt", bool append = FALSE); bool WriteSqlLog(char *logMsg); + DBMS Dbms(void); }; // wxDB @@ -359,6 +427,17 @@ struct DbList DbList *PtrNext; // Pointer to next item in the list }; + +#if __WXDEBUG__ > 0 +class CstructTablesInUse : public wxObject +{ + public: + const char *tableName; + ULONG tableID; + class wxDB *pDb; +}; // CstructTablesInUse +#endif + // The following routines allow a user to get new database connections, free them // for other code segments to use, or close all of them when the application has // completed. @@ -368,6 +447,9 @@ bool WXDLLEXPORT FreeDbConnection(wxDB *pDb); void WXDLLEXPORT CloseDbConnections(void); int WXDLLEXPORT NumberDbConnectionsInUse(void); +// This function sets the sql log state for all open wxDB objects +bool SqlLog(enum sqlLog state, char *filename = "sqllog.txt"); + // This routine allows you to query a driver manager // for a list of available datasources. Call this routine // the first time using SQL_FETCH_FIRST. Continue to call it diff --git a/include/wx/dbtable.h b/include/wx/dbtable.h index 004f02a299..71ef8d2e7d 100644 --- a/include/wx/dbtable.h +++ b/include/wx/dbtable.h @@ -1,5 +1,5 @@ /////////////////////////////////////////////////////////////////////////////// -// Name: table.h +// Name: dbtable.h // Purpose: Declaration of the wxTable class. // Author: Doug Card // Modified by: @@ -23,16 +23,30 @@ // SYNOPSIS STOP */ -#ifndef TABLE_DOT_H -#define TABLE_DOT_H +#ifndef DBTABLE_DOT_H +#define DBTABLE_DOT_H -#ifdef __GNUG__ -#pragma interface "dbtable.h" +// Use this line for wxWindows v1.x +//#include "wx_ver.h" +// Use this line for wxWindows v2.x +#include "wx/version.h" + +#if wxMAJOR_VERSION == 2 + #ifdef __GNUG__ + #pragma interface "dbtable.h" + #endif #endif -#include "wx/db.h" +#if wxMAJOR_VERSION == 2 + #include "wx/db.h" +#else + #include "db.h" +#endif -const int ROWID_LEN = 24; // 18 is the max, 24 is in case it gets larger +const int ROWID_LEN = 24; // 18 is the max, 24 is in case it gets larger +const int DEFAULT_CURSOR = 0; +const bool QUERY_ONLY = TRUE; +const bool DISABLE_VIEW = TRUE; // The following class is used to define a column of a table. // The wxTable constructor will dynamically allocate as many of @@ -46,7 +60,7 @@ const int ROWID_LEN = 24; // 18 is the max, 24 is in case it gets larger class WXDLLEXPORT CcolDef { public: - char ColName[DB_MAX_COLUMN_NAME_LEN+1]; // Column Name glt 4/19/97 added one for the null terminator + char ColName[DB_MAX_COLUMN_NAME_LEN+1]; // Column Name int DbDataType; // Logical Data Type; e.g. DB_DATA_TYPE_INTEGER int SqlCtype; // C data type; e.g. SQL_C_LONG void *PtrDataObj; // Address of the data object @@ -56,13 +70,14 @@ public: bool InsertAllowed; // Specifies whether this column should be included in an INSERT statement bool DerivedCol; // Specifies whether this column is a derived value SDWORD CbValue; // Internal use only!!! + bool Null; // NOT FULLY IMPLEMENTED - Allows NULL values in Inserts and Updates }; // CcolDef // This structure is used when creating secondary indexes. class WXDLLEXPORT CidxDef { public: - char ColName[DB_MAX_COLUMN_NAME_LEN+1]; // Column Name glt 4/19/97 added one for the null terminator + char ColName[DB_MAX_COLUMN_NAME_LEN+1]; bool Ascending; }; // CidxDef @@ -70,8 +85,10 @@ class WXDLLEXPORT wxTable { private: + ULONG tableID; // Used for debugging. This can help to match up mismatched constructors/destructors + // Private member variables - int currCursorNo; + UDWORD cursorType; // Private member functions bool bindInsertParams(void); @@ -91,17 +108,20 @@ public: HENV henv; // ODBC Environment handle HDBC hdbc; // ODBC DB Connection handle HSTMT hstmt; // ODBC Statement handle -// HSTMT c0, c1, c2, c3, c4, c5; // Cursors 0 through 5 - HSTMT c0, c1, c2; // Limited to Cursors 0 through 2 for now + HSTMT *hstmtDefault; // Default cursor HSTMT hstmtInsert; // ODBC Statement handle used specifically for inserts HSTMT hstmtDelete; // ODBC Statement handle used specifically for deletes HSTMT hstmtUpdate; // ODBC Statement handle used specifically for updates - HSTMT hstmtCount; // ODBC Statement handle used specifically for COUNT(*) + HSTMT hstmtInternal; // ODBC Statement handle used internally only + HSTMT *hstmtCount; // ODBC Statement handle used by Count() function (No binding of columns) // Table Inf. char tableName[DB_MAX_TABLE_NAME_LEN+1]; // Table name char queryTableName[DB_MAX_TABLE_NAME_LEN+1]; // Query Table Name int noCols; // # of columns in the table + bool queryOnly; // Query Only, no inserts, updates or deletes + + char tablePath[DB_PATH_MAX]; // needed for dBase tables // Column Definitions CcolDef *colDefs; // Array of CcolDef structures @@ -109,17 +129,20 @@ public: // Where, Order By and From clauses char *where; // Standard SQL where clause, minus the word WHERE char *orderBy; // Standard SQL order by clause, minus the ORDER BY - char *from; // Allows for joins in a Ctable::Query(). Format: ",tbl,tbl..." + char *from; // Allows for joins in a wxTable::Query(). Format: ",tbl,tbl..." // Flags bool selectForUpdate; // Public member functions - wxTable(wxDB *pwxDB, const char *tblName, const int nCols, const char *qryTblName = 0); + wxTable(wxDB *pwxDB, const char *tblName, const int nCols, + const char *qryTblName = 0, bool qryOnly = !QUERY_ONLY, char *tblPath=NULL); virtual ~wxTable(); bool Open(void); - bool CreateTable(void); - bool CreateIndex(char * idxName, bool unique, int noIdxCols, CidxDef *pIdxDefs); + bool CreateTable(bool attemptDrop=TRUE); + bool DropTable(void); + bool CreateIndex(char * idxName, bool unique, int noIdxCols, CidxDef *pIdxDefs, bool attemptDrop=TRUE); + bool DropIndex(char * idxName); bool CloseCursor(HSTMT cursor); int Insert(void); bool Update(void); @@ -154,11 +177,19 @@ public: void SetColDefs (int index, char *fieldName, int dataType, void *pData, int cType, int size, bool keyField = FALSE, bool upd = TRUE, bool insAllow = TRUE, bool derivedCol = FALSE); - bool SetCursor(int cursorNo = DB_CURSOR0); - int GetCursor(void) { return(currCursorNo); } + HSTMT *NewCursor(bool setCursor = FALSE, bool bindColumns = TRUE); + bool DeleteCursor(HSTMT *hstmtDel); + void SetCursor(HSTMT *hstmtActivate = (void **) DEFAULT_CURSOR); + HSTMT GetCursor(void) { return(hstmt); } ULONG Count(void); int DB_STATUS(void) { return(pDb->DB_STATUS); } bool Refresh(void); + bool SetNull(int colNo); + bool SetNull(char *colName); + +#if __WXDEBUG__ > 0 + ULONG GetTableID() { return tableID; }; +#endif }; // wxTable diff --git a/src/common/db.cpp b/src/common/db.cpp index bdac571002..6f9c399bff 100644 --- a/src/common/db.cpp +++ b/src/common/db.cpp @@ -5,13 +5,19 @@ // source such as opening and closing the data source. // Author: Doug Card // Modified by: -// Mods: Dec, 1998: Added support for SQL statement logging and database -// cataloging +// Mods: Dec, 1998: +// -Added support for SQL statement logging and database cataloging +// Mods: April, 1999 +// -Added QUERY_ONLY mode support to reduce default number of cursors +// -Added additional SQL logging code +// -Added DEBUG-ONLY tracking of wxTable objects to detect orphaned DB connections +// -Set ODBC option to only read committed writes to the DB so all +// databases operate the same in that respect // 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 @@ -22,30 +28,47 @@ // the wxWindows GUI development toolkit. /////////////////////////////////////////////////////////////////////////////// -#ifdef __GNUG__ -#pragma implementation "db.h" -#endif - /* // SYNOPSIS START // SYNOPSIS STOP */ -/* +// 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 "db.h" + #endif +#endif + #ifdef DBDEBUG_CONSOLE #include #endif -*/ - -#include "wx/wxprec.h" #ifdef __BORLANDC__ - #pragma hdrstop + #pragma hdrstop #endif //__BORLANDC__ -#ifndef WX_PRECOMP - #include "wx/string.h" -#endif //WX_PRECOMP +#if wxMAJOR_VERSION == 2 + #ifndef WX_PRECOMP + #include "wx/string.h" + #endif //WX_PRECOMP +#endif + +#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 #if wxUSE_ODBC @@ -54,10 +77,32 @@ #include #include #include -#include "wx/db.h" +#if wxMAJOR_VERSION == 1 + #include "db.h" +#elif wxMAJOR_VERSION == 2 + #include "wx/db.h" +#endif DbList* WXDLLEXPORT PtrBegDbList = 0; +#if __WXDEBUG__ > 0 + extern wxList TablesInUse; +#endif + +// SQL Log defaults to be used by GetDbConnection +enum sqlLog SQLLOGstate = sqlLogOFF; + +char SQLLOGfn[DB_PATH_MAX+1] = "sqllog.txt"; + +// The wxDB::errorList is copied to this variable when the wxDB object +// is closed. This way, the error list is still available after the +// database object is closed. This is necessary if the database +// connection fails so the calling application can show the operator +// why the connection failed. Note: as each wxDB object is closed, it +// will overwrite the errors of the previously destroyed wxDB object in +// this variable. +char DBerrorList[DB_MAX_ERROR_HISTORY][DB_MAX_ERROR_MSG_LEN]; + /********** wxDB Constructor **********/ wxDB::wxDB(HENV &aHenv) { @@ -65,6 +110,7 @@ wxDB::wxDB(HENV &aHenv) fpSqlLog = 0; // Sql Log file pointer sqlLogState = sqlLogOFF; // By default, logging is turned off + nTables = 0; strcpy(sqlState,""); strcpy(errorMsg,""); @@ -123,10 +169,10 @@ bool wxDB::Open(char *Dsn, char *Uid, char *AuthStr) uid = Uid; authStr = AuthStr; -#ifndef FWD_ONLY_CURSORS - RETCODE retcode; +#ifndef FWD_ONLY_CURSORS + // Specify that the ODBC cursor library be used, if needed. This must be // specified before the connection is made. retcode = SQLSetConnectOption(hdbc, SQL_ODBC_CURSORS, SQL_CUR_USE_IF_NEEDED); @@ -141,11 +187,21 @@ bool wxDB::Open(char *Dsn, char *Uid, char *AuthStr) #endif // Connect to the data source - if (SQLConnect(hdbc, (UCHAR FAR *) Dsn, SQL_NTS, - (UCHAR FAR *) Uid, SQL_NTS, - (UCHAR FAR *) AuthStr, SQL_NTS) != SQL_SUCCESS) + retcode = SQLConnect(hdbc, (UCHAR FAR *) Dsn, SQL_NTS, + (UCHAR FAR *) Uid, SQL_NTS, + (UCHAR FAR *) AuthStr,SQL_NTS); + if (retcode == SQL_SUCCESS_WITH_INFO) + DispAllErrors(henv, hdbc); + else if (retcode != SQL_SUCCESS) return(DispAllErrors(henv, hdbc)); +/* + If using Intersolv branded ODBC drivers, this is the place where you would substitute + your branded driver license information + + SQLSetConnectOption(hdbc, 1041, (UDWORD) ""); + SQLSetConnectOption(hdbc, 1042, (UDWORD) ""); +*/ // Mark database as open dbIsOpen = TRUE; @@ -193,7 +249,7 @@ bool wxDB::Open(char *Dsn, char *Uid, char *AuthStr) // ===================================================================== // Results from a Microsoft Access 7.0 db, using a driver from Microsoft // - // SQL_VARCHAR type name = 'TEXT(', Precision = 255 + // SQL_VARCHAR type name = 'TEXT', Precision = 255 // SQL_TIMESTAMP type name = 'DATETIME' // SQL_DECIMAL SQL_NO_DATA_FOUND // SQL_NUMERIC type name = 'CURRENCY', Precision = 19 @@ -241,10 +297,20 @@ bool wxDB::Open(char *Dsn, char *Uid, char *AuthStr) typeInfInteger.FsqlType = SQL_INTEGER; // Date/Time - if (! getDataTypeInfo(SQL_TIMESTAMP, typeInfDate)) - return(FALSE); + if (Dbms() != dbmsDBASE) + { + if (! getDataTypeInfo(SQL_TIMESTAMP, typeInfDate)) + return(FALSE); + else + typeInfDate.FsqlType = SQL_TIMESTAMP; + } else - typeInfDate.FsqlType = SQL_TIMESTAMP; + { + if (! getDataTypeInfo(SQL_DATE, typeInfDate)) + return(FALSE); + else + typeInfDate.FsqlType = SQL_DATE; + } #ifdef DBDEBUG_CONSOLE cout << "VARCHAR DATA TYPE: " << typeInfVarchar.TypeName << endl; @@ -309,8 +375,9 @@ bool wxDB::setConnectionOptions(void) bool wxDB::getDbInfo(void) { SWORD cb; + RETCODE retcode; - if (SQLGetInfo(hdbc, SQL_SERVER_NAME, (UCHAR*) dbInf.serverName, 40, &cb) != SQL_SUCCESS) + if (SQLGetInfo(hdbc, SQL_SERVER_NAME, (UCHAR*) dbInf.serverName, 80, &cb) != SQL_SUCCESS) return(DispAllErrors(henv, hdbc)); if (SQLGetInfo(hdbc, SQL_DATABASE_NAME, (UCHAR*) dbInf.databaseName, 128, &cb) != SQL_SUCCESS) @@ -319,7 +386,11 @@ bool wxDB::getDbInfo(void) if (SQLGetInfo(hdbc, SQL_DBMS_NAME, (UCHAR*) dbInf.dbmsName, 40, &cb) != SQL_SUCCESS) return(DispAllErrors(henv, hdbc)); - if (SQLGetInfo(hdbc, SQL_DBMS_VER, (UCHAR*) dbInf.dbmsVer, 40, &cb) != SQL_SUCCESS) + // 16-Mar-1999 + // After upgrading to MSVC6, the original 20 char buffer below was insufficient, + // causing database connectivity to fail in some cases. + retcode = SQLGetInfo(hdbc, SQL_DBMS_VER, (UCHAR*) dbInf.dbmsVer, 64, &cb); + if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO ) return(DispAllErrors(henv, hdbc)); if (SQLGetInfo(hdbc, SQL_ACTIVE_CONNECTIONS, (UCHAR*) &dbInf.maxConnections, sizeof(dbInf.maxConnections), &cb) != SQL_SUCCESS) @@ -334,7 +405,8 @@ bool wxDB::getDbInfo(void) if (SQLGetInfo(hdbc, SQL_DRIVER_ODBC_VER, (UCHAR*) dbInf.odbcVer, 60, &cb) == SQL_ERROR) return(DispAllErrors(henv, hdbc)); - if (SQLGetInfo(hdbc, SQL_ODBC_VER, (UCHAR*) dbInf.drvMgrOdbcVer, 60, &cb) == SQL_ERROR) + retcode = SQLGetInfo(hdbc, SQL_ODBC_VER, (UCHAR*) dbInf.drvMgrOdbcVer, 60, &cb); + if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO) return(DispAllErrors(henv, hdbc)); if (SQLGetInfo(hdbc, SQL_DRIVER_VER, (UCHAR*) dbInf.driverVer, 60, &cb) == SQL_ERROR) @@ -635,8 +707,14 @@ bool wxDB::getDataTypeInfo(SWORD fSqlType, SqlTypeInfo &structSQLTypeInfo) return(DispAllErrors(henv, hdbc, hstmt)); // if (SQLGetData(hstmt, 14, SQL_C_SHORT, (UCHAR*) &structSQLTypeInfo.MinimumScale, 0, &cbRet) != SQL_SUCCESS) // return(DispAllErrors(henv, hdbc, hstmt)); - if (SQLGetData(hstmt, 15, SQL_C_SHORT, (UCHAR*) &structSQLTypeInfo.MaximumScale, 0, &cbRet) != SQL_SUCCESS) + +//#ifdef __UNIX__ // BJO : IODBC knows about 5, not 15... +// if (SQLGetData(hstmt, 5, SQL_C_SHORT,(UCHAR*) &structSQLTypeInfo.MaximumScale, 0, &cbRet) != SQL_SUCCESS) +// return(DispAllErrors(henv, hdbc, hstmt)); +//#else + if (SQLGetData(hstmt, 15, SQL_C_SHORT,(UCHAR*) &structSQLTypeInfo.MaximumScale, 0, &cbRet) != SQL_SUCCESS) return(DispAllErrors(henv, hdbc, hstmt)); +//#endif if (structSQLTypeInfo.MaximumScale < 0) structSQLTypeInfo.MaximumScale = 0; @@ -657,7 +735,7 @@ void wxDB::Close(void) if (fpSqlLog) { fclose(fpSqlLog); - fpSqlLog = 0; //glt + fpSqlLog = 0; } // Free statement handle @@ -675,14 +753,43 @@ void wxDB::Close(void) if (SQLFreeConnect(hdbc) != SQL_SUCCESS) DispAllErrors(henv, hdbc); + // There should be zero Ctable objects still connected to this db object + assert(nTables == 0); + +#if __WXDEBUG__ > 0 + CstructTablesInUse *tiu; + wxNode *pNode; + pNode = TablesInUse.First(); + char s[80]; + char s2[80]; + while (pNode) + { + tiu = (CstructTablesInUse *)pNode->Data(); + if (tiu->pDb == this) + { + sprintf(s, "(%-20s) tableID:[%6lu] pDb:[%lu]", tiu->tableName,tiu->tableID,tiu->pDb); + sprintf(s2,"Orphaned found using pDb:[%lu]",this); + wxMessageBox (s,s2); + } + pNode = pNode->Next(); + } +#endif + + // Copy the error messages to a global variable + for (int i = 0; i < DB_MAX_ERROR_HISTORY; i++) + strcpy(DBerrorList[i],errorList[i]); + } // wxDB::Close() /********** wxDB::CommitTrans() **********/ bool wxDB::CommitTrans(void) { - // Commit the transaction - if (SQLTransact(henv, hdbc, SQL_COMMIT) != SQL_SUCCESS) - return(DispAllErrors(henv, hdbc)); + if (this) + { + // Commit the transaction + if (SQLTransact(henv, hdbc, SQL_COMMIT) != SQL_SUCCESS) + return(DispAllErrors(henv, hdbc)); + } // Completed successfully return(TRUE); @@ -719,9 +826,13 @@ bool wxDB::DispAllErrors(HENV aHenv, HDBC aHdbc, HSTMT aHstmt) getchar(); #endif } + +#ifdef __WXDEBUG__ + wxMessageBox(odbcErrMsg); +#endif } - return(FALSE); // This function alway's returns false. + return(FALSE); // This function always returns false. } // wxDB::DispAllErrors() @@ -765,7 +876,8 @@ void wxDB::logError(char *errMsg, char *SQLState) if (++pLast == DB_MAX_ERROR_HISTORY) { - for (int i = 0; i < DB_MAX_ERROR_HISTORY; i++) + int i; + for (i = 0; i < DB_MAX_ERROR_HISTORY; i++) strcpy(errorList[i], errorList[i+1]); pLast--; } @@ -776,6 +888,9 @@ void wxDB::logError(char *errMsg, char *SQLState) if ((dbStatus = TranslateSqlState(SQLState)) != DB_ERR_FUNCTION_SEQUENCE_ERROR) DB_STATUS = dbStatus; + // Add the errmsg to the sql log + WriteSqlLog(errMsg); + } // wxDB::logError() /**********wxDB::TranslateSqlState() **********/ @@ -979,14 +1094,14 @@ bool wxDB::Grant(int privileges, char *tableName, char *userList) int c = 0; if (privileges & DB_GRANT_SELECT) { - strcat(sqlStmt, "SELECT("); + strcat(sqlStmt, "SELECT"); c++; } if (privileges & DB_GRANT_INSERT) { if (c++) strcat(sqlStmt, ", "); - strcat(sqlStmt, "INSERT("); + strcat(sqlStmt, "INSERT"); } if (privileges & DB_GRANT_UPDATE) { @@ -1018,33 +1133,13 @@ bool wxDB::Grant(int privileges, char *tableName, char *userList) } // wxDB::Grant() /********** wxDB::CreateView() **********/ -bool wxDB::CreateView(char *viewName, char *colList, char *pSqlStmt) +bool wxDB::CreateView(char *viewName, char *colList, char *pSqlStmt, bool attemptDrop) { char sqlStmt[DB_MAX_STATEMENT_LEN]; // Drop the view first - sprintf(sqlStmt, "DROP VIEW %s", viewName); - 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. - GetNextError(henv, hdbc, hstmt); - if (strcmp(sqlState, "S0002") && strcmp(sqlState, "42000")) - { - DispNextError(); - DispAllErrors(henv, hdbc, hstmt); - RollbackTrans(); - return(FALSE); - } - } - - WriteSqlLog(sqlStmt); - -#ifdef DBDEBUG_CONSOLE - cout << endl << sqlStmt << endl; -#endif + if (attemptDrop && !DropView(viewName)) + return FALSE; // Build the create view statement strcpy(sqlStmt, "CREATE VIEW "); @@ -1070,9 +1165,54 @@ bool wxDB::CreateView(char *viewName, char *colList, char *pSqlStmt) } // wxDB::CreateView() +/********** wxDB::DropView() **********/ +bool wxDB::DropView(char *viewName) +{ + // NOTE: This function returns TRUE if the View 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 VIEW %s", viewName); + + 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 + GetNextError(henv, hdbc, hstmt); + if (strcmp(sqlState,"S0002")) // "Base table not found" + { + // Check for product specific error codes + if (!((Dbms() == dbmsSYBASE_ASA && !strcmp(sqlState,"42000")))) // 5.x (and lower?) + { + DispNextError(); + DispAllErrors(henv, hdbc, hstmt); + RollbackTrans(); + return(FALSE); + } + } + } + + // Commit the transaction + if (! CommitTrans()) + return(FALSE); + + return TRUE; + +} // wxDB::DropView() + + /********** wxDB::ExecSql() **********/ bool wxDB::ExecSql(char *pSqlStmt) { + SQLFreeStmt(hstmt, SQL_CLOSE); if (SQLExecDirect(hstmt, (UCHAR FAR *) pSqlStmt, SQL_NTS) == SQL_SUCCESS) return(TRUE); else @@ -1083,6 +1223,35 @@ bool wxDB::ExecSql(char *pSqlStmt) } // wxDB::ExecSql() +/********** wxDB::GetNext() **********/ +bool wxDB::GetNext(void) +{ + if (SQLFetch(hstmt) == SQL_SUCCESS) + return(TRUE); + else + { + DispAllErrors(henv, hdbc, hstmt); + return(FALSE); + } + +} // wxDB::GetNext() + +/********** wxDB::GetData() **********/ +bool wxDB::GetData(UWORD colNo, SWORD cType, PTR pData, SDWORD maxLen, SDWORD FAR *cbReturned) +{ + assert(pData); + assert(cbReturned); + + if (SQLGetData(hstmt, colNo, cType, pData, maxLen, cbReturned) == SQL_SUCCESS) + return(TRUE); + else + { + DispAllErrors(henv, hdbc, hstmt); + return(FALSE); + } + +} // wxDB::GetData() + /********** wxDB::GetColumns() **********/ /* * 1) The last array element of the tableName[] argument must be zero (null). @@ -1092,7 +1261,7 @@ bool wxDB::ExecSql(char *pSqlStmt) * CALLING FUNCTION IS RESPONSIBLE FOR DELETING THE MEMORY RETURNED WHEN IT * IS FINISHED WITH IT. i.e. * - * CcolInf *colInf = pDb->GetColumns(tableList); + * CcolInf *colInf = pDb->GetColumns(tableList, userID); * if (colInf) * { * // Use the column inf @@ -1101,7 +1270,7 @@ bool wxDB::ExecSql(char *pSqlStmt) * delete [] colInf; * } */ -CcolInf *wxDB::GetColumns(char *tableName[]) +CcolInf *wxDB::GetColumns(char *tableName[], char *userID) { UINT noCols = 0; UINT colNo = 0; @@ -1111,11 +1280,32 @@ CcolInf *wxDB::GetColumns(char *tableName[]) char tblName[DB_MAX_TABLE_NAME_LEN+1]; char colName[DB_MAX_COLUMN_NAME_LEN+1]; SWORD sqlDataType; + char userIdUC[80+1]; + char tableNameUC[DB_MAX_TABLE_NAME_LEN+1]; + + if (!userID || !strlen(userID)) + userID = uid; + + // dBase does not use user names, and some drivers fail if you try to pass one + if (Dbms() == dbmsDBASE) + userID = ""; + + // Oracle user names may only be in uppercase, so force + // the name to uppercase + if (Dbms() == dbmsORACLE) + { + int i = 0; + for (char *p = userID; *p; p++) + userIdUC[i++] = toupper(*p); + userIdUC[i] = 0; + userID = userIdUC; + } // Pass 1 - Determine how many columns there are. // Pass 2 - Allocate the CcolInf array and fill in // the array with the column information. - for (int pass = 1; pass <= 2; pass++) + int pass; + for (pass = 1; pass <= 2; pass++) { if (pass == 2) { @@ -1131,19 +1321,50 @@ CcolInf *wxDB::GetColumns(char *tableName[]) colInf[noCols].sqlDataType = 0; } // Loop through each table name - for (int tbl = 0; tableName[tbl]; tbl++) + int tbl; + for (tbl = 0; tableName[tbl]; tbl++) { + // Oracle table names are uppercase only, so force + // the name to uppercase just in case programmer forgot to do this + if (Dbms() == dbmsORACLE) + { + int i = 0; + for (char *p = tableName[tbl]; *p; p++) + tableNameUC[i++] = toupper(*p); + tableNameUC[i] = 0; + } + else + sprintf(tableNameUC,tableName[tbl]); + SQLFreeStmt(hstmt, SQL_CLOSE); - retcode = SQLColumns(hstmt, - NULL, 0, // All qualifiers - NULL, 0, // All owners - (UCHAR *) tableName[tbl], SQL_NTS, - NULL, 0); // All columns + + // MySQL and Access cannot accept a user name when looking up column names, so we + // use the call below that leaves out the user name + if (strcmp(userID,"") && + Dbms() != dbmsMY_SQL && + Dbms() != dbmsACCESS) + { + retcode = SQLColumns(hstmt, + NULL, 0, // All qualifiers + (UCHAR *) userID, SQL_NTS, // Owner + (UCHAR *) tableNameUC, SQL_NTS, + NULL, 0); // All columns + } + else + { + retcode = SQLColumns(hstmt, + NULL, 0, // All qualifiers + NULL, 0, // Owner + (UCHAR *) tableNameUC, SQL_NTS, + NULL, 0); // All columns + } if (retcode != SQL_SUCCESS) { // Error occured, abort DispAllErrors(henv, hdbc, hstmt); if (colInf) delete [] colInf; + SQLFreeStmt(hstmt, SQL_UNBIND); + SQLFreeStmt(hstmt, SQL_CLOSE); return(0); } SQLBindCol(hstmt, 3, SQL_C_CHAR, (UCHAR*) tblName, DB_MAX_TABLE_NAME_LEN+1, &cb); @@ -1169,11 +1390,14 @@ CcolInf *wxDB::GetColumns(char *tableName[]) DispAllErrors(henv, hdbc, hstmt); if (colInf) delete [] colInf; + SQLFreeStmt(hstmt, SQL_UNBIND); + SQLFreeStmt(hstmt, SQL_CLOSE); return(0); } } } + SQLFreeStmt(hstmt, SQL_UNBIND); SQLFreeStmt(hstmt, SQL_CLOSE); return colInf; @@ -1183,7 +1407,6 @@ CcolInf *wxDB::GetColumns(char *tableName[]) /********** wxDB::Catalog() **********/ bool wxDB::Catalog(char *userID, char *fileName) { - assert(userID && strlen(userID)); assert(fileName && strlen(fileName)); RETCODE retcode; @@ -1192,7 +1415,7 @@ bool wxDB::Catalog(char *userID, char *fileName) char tblNameSave[DB_MAX_TABLE_NAME_LEN+1]; char colName[DB_MAX_COLUMN_NAME_LEN+1]; SWORD sqlDataType; - char typeName[16]; + char typeName[30+1]; SWORD precision, length; FILE *fp = fopen(fileName,"wt"); @@ -1201,17 +1424,37 @@ bool wxDB::Catalog(char *userID, char *fileName) SQLFreeStmt(hstmt, SQL_CLOSE); - int i = 0; - char userIdUC[81]; - for (char *p = userID; *p; p++) - userIdUC[i++] = toupper(*p); - userIdUC[i] = 0; - - retcode = SQLColumns(hstmt, - NULL, 0, // All qualifiers - (UCHAR *) userIdUC, SQL_NTS, // User specified - NULL, 0, // All tables - NULL, 0); // All columns + if (!userID || !strlen(userID)) + userID = uid; + + char userIdUC[80+1]; + // Oracle user names may only be in uppercase, so force + // the name to uppercase + if (Dbms() == dbmsORACLE) + { + int i = 0; + for (char *p = userID; *p; p++) + userIdUC[i++] = toupper(*p); + userIdUC[i] = 0; + userID = userIdUC; + } + + if (strcmp(userID,"")) + { + retcode = SQLColumns(hstmt, + NULL, 0, // All qualifiers + (UCHAR *) userID, SQL_NTS, // User specified + NULL, 0, // All tables + NULL, 0); // All columns + } + else + { + retcode = SQLColumns(hstmt, + NULL, 0, // All qualifiers + NULL, 0, // User specified + NULL, 0, // All tables + NULL, 0); // All columns + } if (retcode != SQL_SUCCESS) { DispAllErrors(henv, hdbc, hstmt); @@ -1219,10 +1462,10 @@ bool wxDB::Catalog(char *userID, char *fileName) return(FALSE); } - SQLBindCol(hstmt, 3, SQL_C_CHAR, (UCHAR*) tblName, DB_MAX_TABLE_NAME_LEN+1, &cb); - SQLBindCol(hstmt, 4, SQL_C_CHAR, (UCHAR*) colName, DB_MAX_COLUMN_NAME_LEN+1, &cb); + SQLBindCol(hstmt, 3, SQL_C_CHAR, (UCHAR*) tblName, DB_MAX_TABLE_NAME_LEN+1, &cb); + SQLBindCol(hstmt, 4, SQL_C_CHAR, (UCHAR*) colName, DB_MAX_COLUMN_NAME_LEN+1, &cb); SQLBindCol(hstmt, 5, SQL_C_SSHORT, (UCHAR*) &sqlDataType, 0, &cb); - SQLBindCol(hstmt, 6, SQL_C_CHAR, (UCHAR*) typeName, 16, &cb); + SQLBindCol(hstmt, 6, SQL_C_CHAR, (UCHAR*) typeName, sizeof(typeName), &cb); SQLBindCol(hstmt, 7, SQL_C_SSHORT, (UCHAR*) &precision, 0, &cb); SQLBindCol(hstmt, 8, SQL_C_SSHORT, (UCHAR*) &length, 0, &cb); @@ -1255,6 +1498,8 @@ bool wxDB::Catalog(char *userID, char *fileName) tblName, colName, sqlDataType, typeName, precision, length); if (fputs(outStr, fp) == EOF) { + SQLFreeStmt(hstmt, SQL_UNBIND); + SQLFreeStmt(hstmt, SQL_CLOSE); fclose(fp); return(FALSE); } @@ -1262,15 +1507,13 @@ bool wxDB::Catalog(char *userID, char *fileName) } if (retcode != SQL_NO_DATA_FOUND) - { DispAllErrors(henv, hdbc, hstmt); - fclose(fp); - return(FALSE); - } + SQLFreeStmt(hstmt, SQL_UNBIND); SQLFreeStmt(hstmt, SQL_CLOSE); + fclose(fp); - return(TRUE); + return(retcode == SQL_NO_DATA_FOUND); } // wxDB::Catalog() @@ -1279,20 +1522,78 @@ bool wxDB::Catalog(char *userID, char *fileName) // if the object exists in the database. This function does not indicate // whether or not the user has privleges to query or perform other functions // on the table. -bool wxDB::TableExists(char *tableName) +bool wxDB::TableExists(char *tableName, char *userID, char *tablePath) { assert(tableName && strlen(tableName)); + if (Dbms() == dbmsDBASE) + { + wxString dbName; + if (tablePath && strlen(tablePath)) + dbName.sprintf("%s/%s.dbf",tablePath,tableName); + else + dbName.sprintf("%s.dbf",tableName); + bool glt; + glt = wxFileExists(dbName.GetData()); + return glt; + } + + if (!userID || !strlen(userID)) + userID = uid; + + char userIdUC[80+1]; + // Oracle user names may only be in uppercase, so force + // the name to uppercase + if (Dbms() == dbmsORACLE) + { + int i = 0; + for (char *p = userID; *p; p++) + userIdUC[i++] = toupper(*p); + userIdUC[i] = 0; + userID = userIdUC; + } + + char tableNameUC[DB_MAX_TABLE_NAME_LEN+1]; + // Oracle table names are uppercase only, so force + // the name to uppercase just in case programmer forgot to do this + if (Dbms() == dbmsORACLE) + { + int i = 0; + for (char *p = tableName; *p; p++) + tableNameUC[i++] = toupper(*p); + tableNameUC[i] = 0; + } + else + sprintf(tableNameUC,tableName); + SQLFreeStmt(hstmt, SQL_CLOSE); - RETCODE retcode = SQLTables(hstmt, - NULL, 0, // All qualifiers - NULL, 0, // All owners - (UCHAR FAR *)tableName, SQL_NTS, - NULL, 0); // All table types + RETCODE retcode; + + // MySQL and Access cannot accept a user name when looking up table names, so we + // use the call below that leaves out the user name + if (strcmp(userID,"") && + Dbms() != dbmsMY_SQL && + Dbms() != dbmsACCESS) + { + retcode = SQLTables(hstmt, + NULL, 0, // All qualifiers + (UCHAR *) userID, SQL_NTS, // All owners + (UCHAR FAR *)tableNameUC, SQL_NTS, + NULL, 0); // All table types + } + else + { + retcode = SQLTables(hstmt, + NULL, 0, // All qualifiers + NULL, 0, // All owners + (UCHAR FAR *)tableNameUC, SQL_NTS, + NULL, 0); // All table types + } if (retcode != SQL_SUCCESS) return(DispAllErrors(henv, hdbc, hstmt)); - if (SQLFetch(hstmt) != SQL_SUCCESS) + retcode = SQLFetch(hstmt); + if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO) { SQLFreeStmt(hstmt, SQL_CLOSE); return(DispAllErrors(henv, hdbc, hstmt)); @@ -1352,6 +1653,73 @@ bool wxDB::WriteSqlLog(char *logMsg) } // wxDB::WriteSqlLog() +/********** wxDB::Dbms() **********/ +/* + * Be aware that not all database engines use the exact same syntax, and not + * every ODBC compliant database is compliant to the same level of compliancy. + * Some manufacturers support the minimum Level 1 compliancy, and others up + * through Level 3. Others support subsets of features for levels above 1. + * + * If you find an inconsistency between the wxDB class and a specific database + * engine, and an identifier to this section, and special handle the database in + * the area where behavior is non-conforming with the other databases. + * + * + * NOTES ABOUT ISSUES SPECIFIC TO EACH DATABASE ENGINE + * --------------------------------------------------- + * + * ORACLE + * - Currently the only database supported by the class to support VIEWS + * + * DBASE + * - Does not support the SQL_TIMESTAMP structure + * - Supports only one cursor and one connect (apparently? with Microsoft driver only?) + * - Does not automatically create the primary index if the 'keyField' param of SetColDef + * is TRUE. The user must create ALL indexes from their program. + * - Table names can only be 8 characters long + * - Column names can only be 10 characters long + * + * SYBASE (all) + * - To lock a record during QUERY functions, the reserved word 'HOLDLOCK' must be added + * after every table name involved in the query/join if that tables matching record(s) + * are to be locked + * - Ignores the keywords 'FOR UPDATE'. Use the HOLDLOCK functionality described above + * + * SYBASE (Enterprise) + * - If a column is part of the Primary Key, the column cannot be NULL + * + * MY_SQL + * - If a column is part of the Primary Key, the column cannot be NULL + * - Cannot support selecting for update [::CanSelectForUpdate()]. Always returns FALSE + * + * POSTGRES + * - Does not support the keywords 'ASC' or 'DESC' as of release v6.5.0 + * + * + */ +DBMS wxDB::Dbms(void) +{ + if (!strnicmp(dbInf.dbmsName,"Oracle",6)) + return(dbmsORACLE); + if (!stricmp(dbInf.dbmsName,"Adaptive Server Anywhere")) + return(dbmsSYBASE_ASA); + if (!stricmp(dbInf.dbmsName,"SQL Server")) // Sybase Adaptive Server Enterprise + return(dbmsSYBASE_ASE); + if (!stricmp(dbInf.dbmsName,"Microsoft SQL Server")) + return(dbmsMS_SQL_SERVER); + if (!stricmp(dbInf.dbmsName,"MySQL")) + return(dbmsMY_SQL); + if (!stricmp(dbInf.dbmsName,"PostgresSQL")) // v6.5.0 + return(dbmsPOSTGRES); + if (!stricmp(dbInf.dbmsName,"ACCESS")) + return(dbmsACCESS); + if (!strnicmp(dbInf.dbmsName,"DBASE",5)) + return(dbmsDBASE); + return(dbmsUNIDENTIFIED); + +} // wxDB::Dbms() + + /********** GetDbConnection() **********/ wxDB* WXDLLEXPORT GetDbConnection(DbStuff *pDbStuff) { @@ -1396,7 +1764,10 @@ wxDB* WXDLLEXPORT GetDbConnection(DbStuff *pDbStuff) // Connect to the datasource if (pList->PtrDb->Open(pDbStuff->Dsn, pDbStuff->Uid, pDbStuff->AuthStr)) + { + pList->PtrDb->SqlLog(SQLLOGstate,SQLLOGfn,TRUE); return(pList->PtrDb); + } else // Unable to connect, destroy list item { if (pList->PtrPrev) @@ -1466,6 +1837,26 @@ int WXDLLEXPORT NumberDbConnectionsInUse(void) } // NumberDbConnectionsInUse() +/********** SqlLog() **********/ +bool SqlLog(enum sqlLog state, char *filename) +{ + bool append = FALSE; + DbList *pList; + + for (pList = PtrBegDbList; pList; pList = pList->PtrNext) + { + if (!pList->PtrDb->SqlLog(state,filename,append)) + return(FALSE); + append = TRUE; + } + + SQLLOGstate = state; + strcpy(SQLLOGfn,filename); + + return(TRUE); + +} // SqlLog() + /********** GetDataSource() **********/ bool GetDataSource(HENV henv, char *Dsn, SWORD DsnMax, char *DsDesc, SWORD DsDescMax, UWORD direction) diff --git a/src/common/dbtable.cpp b/src/common/dbtable.cpp index d1166229c4..3fab3c4813 100644 --- a/src/common/dbtable.cpp +++ b/src/common/dbtable.cpp @@ -2,12 +2,16 @@ // 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 @@ -18,39 +22,59 @@ // the wxWindows GUI development toolkit. /////////////////////////////////////////////////////////////////////////////// -#ifdef __GNUG__ -#pragma implementation "dbtable.h" -#endif - /* // SYNOPSIS START // 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 #endif //__BORLANDC__ -#ifndef WX_PRECOMP - #include -#endif //WX_PRECOMP +#if wxMAJOR_VERSION == 2 +# ifndef WX_PRECOMP +# include +# endif //WX_PRECOMP +#endif -#if wxUSE_ODBC +#if wxMAJOR_VERSION == 1 +# if defined(wx_msw) || defined(wx_x) +# ifdef WX_PRECOMP +# include "wx_prec.h" +# else +# include "wx.h" +# endif +# endif +# define wxUSE_ODBC 1 +#endif -#include +#if wxUSE_ODBC #include #include #include #include +#if wxMAJOR_VERSION == 1 + #include "table.h" +#elif wxMAJOR_VERSION == 2 + #include "wx/dbtable.h" +#endif #ifdef __UNIX__ // The HPUX preprocessor lines below were commented out on 8/20/97 @@ -63,27 +87,68 @@ # endif #endif +ULONG lastTableID = 0; + + +#if __WXDEBUG__ > 0 + 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, char *tblPath) { - // Assign member variables - pDb = pwxDB; // Pointer to the wxDB object + 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); strcpy(tableName, tblName); // Table Name + if (tblPath) + strcpy(tablePath, tblPath); // Table Path - used for dBase files + if (qryTblName) // Name of the table/view to query strcpy(queryTableName, qryTblName); else strcpy(queryTableName, tblName); - assert(pDb); // Assert is placed after table name is assigned for error reporting reasons +// assert(pDb); // Assert is placed after table name is assigned for error reporting reasons if (!pDb) return; - 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 + pDb->nTables++; + + char s[200]; + tableID = ++lastTableID; + sprintf(s, "wxTable constructor (%-20s) tableID:[%6lu] pDb:[%lu]", tblName,tableID,pDb); + +#if __WXDEBUG__ > 0 + CstructTablesInUse *tableInUse; + tableInUse = new CstructTablesInUse(); + tableInUse->tableName = tblName; + tableInUse->tableID = tableID; + tableInUse->pDb = pDb; + TablesInUse.Append(tableInUse); +#endif + + pDb->WriteSqlLog(s); // Grab the HENV and HDBC from the wxDB object henv = pDb->henv; @@ -92,49 +157,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); + pDb->GetNextError(henv, hdbc, hstmtInternal); if (! strcmp(pDb->sqlState, "01S02")) // Option Value Changed { // Datasource does not support static cursors. Driver // will substitute a cursor type. Call SQLGetStmtOption() // to determine which cursor type was selected. - if (SQLGetStmtOption(c1, SQL_CURSOR_TYPE, (UCHAR*) &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) { @@ -153,72 +207,101 @@ 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:[%lu]", tableName,tableID,pDb); + pDb->WriteSqlLog(s); + } + +#ifndef PROGRAM_FP4UPG +#if __WXDEBUG__ > 0 + 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 +#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() @@ -232,7 +315,7 @@ bool wxTable::Open(void) 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); @@ -242,25 +325,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); @@ -286,7 +367,7 @@ bool wxTable::Open(void) } strcat(sqlStmt, ")"); - pDb->WriteSqlLog(sqlStmt); +// pDb->WriteSqlLog(sqlStmt); // Prepare the insert statement for execution if (SQLPrepare(hstmtInsert, (UCHAR FAR *) sqlStmt, SQL_NTS) != SQL_SUCCESS) @@ -353,10 +434,12 @@ bool wxTable::query(int queryType, bool forUpdate, bool distinct, char *pSqlStmt // 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) + 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 @@ -385,7 +468,8 @@ void wxTable::GetSelectStmt(char *pSqlStmt, int typeOfSelect, bool distinct) appendFromClause = TRUE; // Add the column list - for (int i = 0; i < noCols; i++) + int i; + for (i = 0; i < noCols; i++) { // If joining tables, the base table column names must be qualified to avoid ambiguity if (appendFromClause) @@ -416,6 +500,15 @@ void wxTable::GetSelectStmt(char *pSqlStmt, int typeOfSelect, bool distinct) // Append the FROM tablename portion strcat(pSqlStmt, " FROM "); strcat(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)) + strcat(pSqlStmt, " HOLDLOCK"); + if (appendFromClause) strcat(pSqlStmt, from); @@ -455,7 +548,9 @@ void wxTable::GetSelectStmt(char *pSqlStmt, int typeOfSelect, bool distinct) strcat(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"); @@ -477,11 +572,15 @@ bool wxTable::getRec(UWORD fetchType) return(pDb->DispAllErrors(henv, hdbc, hstmt)); #else // Fetch the next record from the record set - if ((retcode = SQLFetch(hstmt)) != SQL_SUCCESS) + + 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)); + } #endif // Completed successfully @@ -508,12 +607,17 @@ UWORD wxTable::GetRowNum(void) /********** wxTable::bindInsertParams() **********/ bool wxTable::bindInsertParams(void) { - SWORD fSqlType = 0; - UDWORD precision = 0; - SWORD scale = 0; + 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 - for (int i = 0; i < noCols; i++) + int i; + for (i = 0; i < noCols; i++) { if (! colDefs[i].InsertAllowed) continue; @@ -549,6 +653,12 @@ bool wxTable::bindInsertParams(void) 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) @@ -563,12 +673,17 @@ bool wxTable::bindInsertParams(void) /********** wxTable::bindUpdateParams() **********/ bool wxTable::bindUpdateParams(void) { - SWORD fSqlType = 0; - UDWORD precision = 0; - SWORD scale = 0; + 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 - for (int i = 0, colNo = 1; i < noCols; i++) + int i,colNo; + for (i = 0, colNo = 1; i < noCols; i++) { if (! colDefs[i].Updateable) continue; @@ -621,7 +736,8 @@ bool wxTable::bindCols(HSTMT cursor) static SDWORD cb; // Bind each column of the table to a memory address for fetching data - for (int i = 0; i < noCols; i++) + 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) @@ -645,7 +761,7 @@ bool wxTable::CloseCursor(HSTMT cursor) } // wxTable::CloseCursor() /********** wxTable::CreateTable() **********/ -bool wxTable::CreateTable(void) +bool wxTable::CreateTable(bool attemptDrop) { if (!pDb) return FALSE; @@ -653,51 +769,16 @@ bool wxTable::CreateTable(void) 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. */ - - /* PostgreSQL 6.4.0 returns "08S01" or in written form - "ERROR: Relation ... Does Not Exist", Robert Roebling */ - - /* MySQL 3.23.33b returns "S1000" or in written form - "ERROR: Unknown table ...", Robert Roebling */ - - /* This routine is bullshit, Robert Roebling */ - - pDb->GetNextError(henv, hdbc, hstmt); - if (strcmp(pDb->sqlState, "S0002") && - strcmp(pDb->sqlState, "S1000") && - strcmp(pDb->sqlState, "42000") && - strcmp(pDb->sqlState, "08S01")) - { - pDb->DispNextError(); - pDb->DispAllErrors(henv, hdbc, hstmt); - pDb->RollbackTrans(); - CloseCursor(hstmt); - return(FALSE); - } - } - - pDb->WriteSqlLog(sqlStmt); - - // 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 @@ -759,13 +840,14 @@ bool wxTable::CreateTable(void) sprintf(s, "(%d)", colDefs[i].SzDataObj); strcat(sqlStmt, s); } - -#ifdef __WXGTK__ - if (colDefs[i].KeyField) + + if (pDb->Dbms() == dbmsSYBASE_ASE || pDb->Dbms() == dbmsMY_SQL) { - strcat(sqlStmt, " NOT NULL"); + if (colDefs[i].KeyField) + { + strcat(sqlStmt, " NOT NULL"); + } } -#endif needComma = TRUE; } @@ -778,16 +860,19 @@ bool wxTable::CreateTable(void) break; } } - if (j) // Found a keyfield + if (j && pDb->Dbms() != dbmsDBASE) // Found a keyfield { -#ifndef __WXGTK__ - /* MySQL goes out on this one. We also declare the relevant key NON NULL above */ - strcat(sqlStmt, ",CONSTRAINT "); - strcat(sqlStmt, tableName); - strcat(sqlStmt, "_PIDX PRIMARY KEY ("); -#else - strcat(sqlStmt, ", PRIMARY KEY ("); -#endif + if (pDb->Dbms() != dbmsMY_SQL) + { + strcat(sqlStmt, ",CONSTRAINT "); + strcat(sqlStmt, tableName); + strcat(sqlStmt, "_PIDX PRIMARY KEY ("); + } + else + { + /* MySQL goes out on this one. We also declare the relevant key NON NULL above */ + strcat(sqlStmt, ", PRIMARY KEY ("); + } // List column name(s) of column(s) comprising the primary key for (i = j = 0; i < noCols; i++) @@ -802,11 +887,11 @@ bool wxTable::CreateTable(void) strcat(sqlStmt, ")"); } // Append the closing parentheses for the create table statement - strcat(sqlStmt, ")"); - + strcat(sqlStmt, ")"); + pDb->WriteSqlLog(sqlStmt); -#ifdef _CONSOLE +#ifdef DBDEBUG_CONSOLE cout << endl << sqlStmt << endl; #endif @@ -830,11 +915,62 @@ 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 (strcmp(pDb->sqlState,"S0002")) // "Base table not found" + { + // Check for product specific error codes + if (!((pDb->Dbms() == dbmsSYBASE_ASA && !strcmp(pDb->sqlState,"42000")) || // 5.x (and lower?) + (pDb->Dbms() == dbmsMY_SQL && !strcmp(pDb->sqlState,"S1000")) || // untested + (pDb->Dbms() == dbmsPOSTGRES && !strcmp(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(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 "); if (unique) @@ -847,20 +983,21 @@ bool wxTable::CreateIndex(char * idxName, bool unique, int noIdxCols, CidxDef *p strcat(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); - - /* Postgres doesnt cope with ASC */ -#ifndef __WXGTK__ - if (pIdxDefs[i].Ascending) - strcat(sqlStmt, " ASC"); - else - strcat(sqlStmt, " DESC"); -#endif + /* Postgres doesn't cope with ASC */ + if (pDb->Dbms() != dbmsPOSTGRES) + { + if (pIdxDefs[i].Ascending) + strcat(sqlStmt, " ASC"); + else + strcat(sqlStmt, " DESC"); + } if ((i + 1) < noIdxCols) - strcat(sqlStmt, ", "); + strcat(sqlStmt, ","); } // Append closing parentheses @@ -868,7 +1005,7 @@ bool wxTable::CreateIndex(char * idxName, bool unique, int noIdxCols, CidxDef *p pDb->WriteSqlLog(sqlStmt); -#ifdef _CONSOLE +#ifdef DBDEBUG_CONSOLE cout << endl << sqlStmt << endl << endl; #endif @@ -892,11 +1029,72 @@ bool wxTable::CreateIndex(char * idxName, bool unique, int noIdxCols, CidxDef *p } // wxTable::CreateIndex() +/********** wxTable::DropIndex() **********/ +bool wxTable::DropIndex(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 (strcmp(pDb->sqlState,"S0012")) // "Index not found" + { + // Check for product specific error codes + if (!((pDb->Dbms() == dbmsSYBASE_ASA && !strcmp(pDb->sqlState,"42000")) || // v5.x (and lower?) + (pDb->Dbms() == dbmsSYBASE_ASE && !strcmp(pDb->sqlState,"S0002")) || // Base table not found + (pDb->Dbms() == dbmsMY_SQL && !strcmp(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); @@ -918,6 +1116,10 @@ int wxTable::Insert(void) /********** wxTable::Update(pSqlStmt) **********/ bool wxTable::Update(char *pSqlStmt) { + assert(!queryOnly); + if (queryOnly) + return(FALSE); + pDb->WriteSqlLog(pSqlStmt); return(execUpdate(pSqlStmt)); @@ -927,6 +1129,10 @@ bool wxTable::Update(char *pSqlStmt) /********** wxTable::Update() **********/ bool wxTable::Update(void) { + assert(!queryOnly); + if (queryOnly) + return(FALSE); + char sqlStmt[DB_MAX_STATEMENT_LEN]; // Build the SQL UPDATE statement @@ -934,7 +1140,7 @@ bool wxTable::Update(void) pDb->WriteSqlLog(sqlStmt); -#ifdef _CONSOLE +#ifdef DBDEBUG_CONSOLE cout << endl << sqlStmt << endl << endl; #endif @@ -946,6 +1152,10 @@ bool wxTable::Update(void) /********** wxTable::UpdateWhere() **********/ bool wxTable::UpdateWhere(char *pWhereClause) { + assert(!queryOnly); + if (queryOnly) + return(FALSE); + char sqlStmt[DB_MAX_STATEMENT_LEN]; // Build the SQL UPDATE statement @@ -953,7 +1163,7 @@ bool wxTable::UpdateWhere(char *pWhereClause) pDb->WriteSqlLog(sqlStmt); -#ifdef _CONSOLE +#ifdef DBDEBUG_CONSOLE cout << endl << sqlStmt << endl << endl; #endif @@ -965,6 +1175,10 @@ 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 @@ -980,6 +1194,10 @@ bool wxTable::Delete(void) /********** wxTable::DeleteWhere() **********/ bool wxTable::DeleteWhere(char *pWhereClause) { + assert(!queryOnly); + if (queryOnly) + return(FALSE); + char sqlStmt[DB_MAX_STATEMENT_LEN]; // Build the SQL DELETE statement @@ -995,6 +1213,10 @@ 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 @@ -1034,6 +1256,10 @@ bool wxTable::execUpdate(char *pSqlStmt) /********** wxTable::GetUpdateStmt() **********/ void wxTable::GetUpdateStmt(char *pSqlStmt, int typeOfUpd, char *pWhereClause) { + assert(!queryOnly); + if (queryOnly) + return; + char whereClause[DB_MAX_WHERE_CLAUSE_LEN]; bool firstColumn = TRUE; @@ -1041,7 +1267,8 @@ 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) @@ -1062,7 +1289,7 @@ void wxTable::GetUpdateStmt(char *pSqlStmt, int typeOfUpd, char *pWhereClause) 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; @@ -1094,6 +1321,10 @@ void wxTable::GetUpdateStmt(char *pSqlStmt, int typeOfUpd, char *pWhereClause) /********** wxTable::GetDeleteStmt() **********/ void wxTable::GetDeleteStmt(char *pSqlStmt, int typeOfDel, char *pWhereClause) { + assert(!queryOnly); + if (queryOnly) + return; + char whereClause[DB_MAX_WHERE_CLAUSE_LEN]; whereClause[0] = 0; @@ -1159,7 +1390,8 @@ void wxTable::GetWhereClause(char *pWhereClause, int typeOfWhere, char *qualTabl 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) || @@ -1244,14 +1476,14 @@ bool wxTable::IsColNull(int colNo) } // wxTable::IsColNull() /********** wxTable::CanSelectForUpdate() **********/ - bool wxTable::CanSelectForUpdate(void) { -#ifndef __WXGTK__ + if (pDb->Dbms() == dbmsMY_SQL) + return FALSE; + if (pDb->dbInf.posStmts & SQL_PS_SELECT_FOR_UPDATE) return(TRUE); else -#endif return(FALSE); } // wxTable::CanSelectForUpdate() @@ -1264,7 +1496,7 @@ bool wxTable::CanUpdByROWID(void) // 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); @@ -1285,7 +1517,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) { @@ -1329,26 +1562,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); @@ -1360,11 +1581,13 @@ void wxTable::SetColDefs (int index, char *fieldName, int dataType, void *pData, int cType, int size, bool keyField, bool upd, bool insAllow, bool derivedCol) { - // Please, no uint, it doesn't exist for VC++ - if (strlen(fieldName) > (unsigned int) DB_MAX_COLUMN_NAME_LEN) // glt 4/21/97 + if (!colDefs) // May happen if the database connection fails + return; + + if (strlen(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 + colDefs[index].ColName[DB_MAX_COLUMN_NAME_LEN] = 0; } else strcpy(colDefs[index].ColName, fieldName); @@ -1386,52 +1609,18 @@ 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() @@ -1458,30 +1647,39 @@ ULONG wxTable::Count(void) pDb->WriteSqlLog(sqlStmt); + // Initialize the Count cursor if it's not already initialized + if (!hstmtCount) + { + 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, (UCHAR*) &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); @@ -1493,10 +1691,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; @@ -1537,8 +1734,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; @@ -1548,6 +1748,90 @@ bool wxTable::Refresh(void) } // wxTable::Refresh() -#endif - // wxUSE_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(char *colName) +{ + int i; + for (i = 0; i < noCols; i++) + { + if (!stricmp(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