X-Git-Url: https://git.saurik.com/wxWidgets.git/blobdiff_plain/f6bcfd974ef26faf6f91a62cac09827e09463fd1..cb73d83f6328202f2b2b25d41e4f27b032f550c4:/src/common/db.cpp diff --git a/src/common/db.cpp b/src/common/db.cpp index cd146b9004..71f0176226 100644 --- a/src/common/db.cpp +++ b/src/common/db.cpp @@ -5,6 +5,8 @@ // source such as opening and closing the data source. // Author: Doug Card // Modified by: George Tasker +// Bart Jourquin +// Mark Johnson, wxWindows@mj10777.de // Mods: Dec, 1998: // -Added support for SQL statement logging and database cataloging // Mods: April, 1999 @@ -43,7 +45,7 @@ #if wxMAJOR_VERSION == 2 #ifdef __GNUG__ - #pragma implementation "db.h" + #pragma implementation "db.h" #endif #endif @@ -108,7 +110,8 @@ char const *SQL_CATALOG_FILENAME = "catalog.txt"; wxDbSqlLogState SQLLOGstate = sqlLogOFF; //char SQLLOGfn[wxDB_PATH_MAX+1] = SQL_LOG_FILENAME; -wxChar *SQLLOGfn = (wxChar*) SQL_LOG_FILENAME; +//wxChar *SQLLOGfn = (wxChar*) SQL_LOG_FILENAME; +static wxString SQLLOGfn = SQL_LOG_FILENAME; // The wxDb::errorList is copied to this variable when the wxDb object // is closed. This way, the error list is still available after the @@ -116,23 +119,39 @@ wxChar *SQLLOGfn = (wxChar*) SQL_LOG_FILENAME; // 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. +// this variable. NOTE: This occurs during a CLOSE, not a FREEing of the +// connection char DBerrorList[DB_MAX_ERROR_HISTORY][DB_MAX_ERROR_MSG_LEN]; +// This type defines the return row-struct form +// SQLTablePrivileges, and is used by wxDB::TablePrivileges. +typedef struct +{ + wxChar tableQual[128+1]; + wxChar tableOwner[128+1]; + wxChar tableName[128+1]; + wxChar grantor[128+1]; + wxChar grantee[128+1]; + wxChar privilege[128+1]; + wxChar grantable[3+1]; +} wxDbTablePrivilegeInfo; + /********** wxDbColFor Constructor **********/ wxDbColFor::wxDbColFor() { - i_Nation = 0; // 0=EU, 1=UK, 2=International, 3=US - s_Field = ""; + s_Field = ""; int i; for (i=0;i<7;i++) { s_Format[i] = ""; - s_Menge[i] = ""; - i_Menge[i] = 0; + s_Amount[i] = ""; + i_Amount[i] = 0; } - Format(1,DB_DATA_TYPE_VARCHAR,0,0,0); // the Function that does the work + i_Nation = 0; // 0=EU, 1=UK, 2=International, 3=US + i_dbDataType = 0; + i_sqlDataType = 0; + Format(1,DB_DATA_TYPE_VARCHAR,0,0,0); // the Function that does the work } // wxDbColFor::wxDbColFor() @@ -141,29 +160,104 @@ wxDbColFor::~wxDbColFor() } // wxDbColFor::~wxDbColFor() +/********** wxDbColInf Con / Destructor **********/ +wxDbColInf::wxDbColInf() +{ + catalog[0] = 0; + schema[0] = 0; + tableName[0] = 0; + colName[0] = 0; + sqlDataType = 0; + typeName[0] = 0; + columnSize = 0; + bufferLength = 0; + decimalDigits = 0; + numPrecRadix = 0; + nullable = 0; + remarks[0] = 0; + dbDataType = 0; + PkCol = 0; + PkTableName[0] = 0; + FkCol = 0; + FkTableName[0] = 0; + pColFor = NULL; +} // wxDbColInf::wxDbColFor() + + +wxDbColInf::~wxDbColInf() +{ + if (pColFor) + delete pColFor; + pColFor = NULL; +} // wxDbColInf::~wxDbColInf() + + +/********** wxDbTableInf Constructor ********/ +wxDbTableInf::wxDbTableInf() +{ + tableName[0] = 0; + tableType[0] = 0; + tableRemarks[0] = 0; + numCols = 0; + pColInf = NULL; +} // wxDbTableInf::wxDbTableFor() + + +/********** wxDbTableInf Constructor ********/ +wxDbTableInf::~wxDbTableInf() +{ + if (pColInf) + delete [] pColInf; + pColInf = NULL; +} // wxDbTableInf::~wxDbTableInf() + + +/********** wxDbInf Constructor *************/ +wxDbInf::wxDbInf() +{ + catalog[0] = 0; + schema[0] = 0; + numTables = 0; + pTableInf = NULL; +} // wxDbInf::wxDbFor() + + +/********** wxDbInf Destructor *************/ +wxDbInf::~wxDbInf() +{ + if (pTableInf) + delete [] pTableInf; + pTableInf = NULL; +} // wxDbInf::~wxDbInf() + + +/*************************************************/ + + int wxDbColFor::Format(int Nation,int dbDataType,SWORD sqlDataType,short columnSize,short decimalDigits) { // ---------------------------------------------------------------------------------------- - // -- 19991224 : mj10777@gmx.net : Create + // -- 19991224 : mj10777 : Create // There is still a lot of work to do here, but it is a start // It handles all the basic data-types that I have run into up to now - // The main work will have be with Dates and float Formatting (US 1,000.00 ; EU 1.000,00) - // There are wxWindow plans for locale support and the new wxDateTime. - // - if they define some constants (wxEUROPEAN) that can be gloably used, + // The main work will have be with Dates and float Formatting + // (US 1,000.00 ; EU 1.000,00) + // There are wxWindow plans for locale support and the new wxDateTime. If + // they define some constants (wxEUROPEAN) that can be gloably used, // they should be used here. // ---------------------------------------------------------------------------------------- - // There should also be a Function to scan in a string to fill the variable + // There should also be a function to scan in a string to fill the variable // ---------------------------------------------------------------------------------------- wxString Temp0; - i_Nation = Nation; // 0 = timestamp , 1=EU, 2=UK, 3=International, 4=US + i_Nation = Nation; // 0 = timestamp , 1=EU, 2=UK, 3=International, 4=US i_dbDataType = dbDataType; i_sqlDataType = sqlDataType; - s_Field.Printf(wxT("%s%d"),s_Menge[1].c_str(),i_Menge[1]); // OK for VARCHAR, INTEGER and FLOAT - if (i_dbDataType == 0) // Filter unsupported dbDataTypes + s_Field.Printf(wxT("%s%d"),s_Amount[1].c_str(),i_Amount[1]); // OK for VARCHAR, INTEGER and FLOAT + if (i_dbDataType == 0) // Filter unsupported dbDataTypes { if ((i_sqlDataType == SQL_VARCHAR) || (i_sqlDataType == SQL_LONGVARCHAR)) i_dbDataType = DB_DATA_TYPE_VARCHAR; - if (i_sqlDataType == SQL_C_DATE) + if ((i_sqlDataType == SQL_C_DATE) || (i_sqlDataType == SQL_C_TIMESTAMP)) i_dbDataType = DB_DATA_TYPE_DATE; if (i_sqlDataType == SQL_C_BIT) i_dbDataType = DB_DATA_TYPE_INTEGER; @@ -179,17 +273,17 @@ int wxDbColFor::Format(int Nation,int dbDataType,SWORD sqlDataType,short columnS switch(i_dbDataType) // -A-> Still a lot of proper formatting to do { case DB_DATA_TYPE_VARCHAR: - s_Field = "%s"; // + s_Field = "%s"; break; case DB_DATA_TYPE_INTEGER: - s_Field = "%d"; // + s_Field = "%d"; break; case DB_DATA_TYPE_FLOAT: if (decimalDigits == 0) decimalDigits = 2; Temp0 = "%"; Temp0.Printf(wxT("%s%d.%d"),Temp0.c_str(),columnSize,decimalDigits); - s_Field.Printf(wxT("%sf"),Temp0.c_str()); // + s_Field.Printf(wxT("%sf"),Temp0.c_str()); break; case DB_DATA_TYPE_DATE: if (i_Nation == 0) // timestamp YYYY-MM-DD HH:MM:SS.SSS (tested for SYBASE) @@ -214,21 +308,35 @@ int wxDbColFor::Format(int Nation,int dbDataType,SWORD sqlDataType,short columnS } break; default: - s_Field.Printf(wxT("-E-> unknown Format(%d)-sql(%d)"),dbDataType,sqlDataType); // + s_Field.Printf(wxT("Unknown Format(%d)-SQL(%d)"),dbDataType,sqlDataType); // break; }; return TRUE; } // wxDbColFor::Format() -/********** wxDb Constructor **********/ +/********** wxDb Constructors **********/ wxDb::wxDb(HENV &aHenv, bool FwdOnlyCursors) +{ + // Copy the HENV into the db class + henv = aHenv; + fwdOnlyCursors = FwdOnlyCursors; + initialize(); +} // wxDb::wxDb() + + +void wxDb::initialize() +/* + * Private member function that sets all wxDb member variables to + * known values at creation of the wxDb + */ { int i; fpSqlLog = 0; // Sql Log file pointer sqlLogState = sqlLogOFF; // By default, logging is turned off nTables = 0; + dbmsType = dbmsUNIDENTIFIED; wxStrcpy(sqlState,wxT("")); wxStrcpy(errorMsg,wxT("")); @@ -264,10 +372,6 @@ wxDb::wxDb(HENV &aHenv, bool FwdOnlyCursors) // Error reporting is turned OFF by default silent = TRUE; - // Copy the HENV into the db class - henv = aHenv; - fwdOnlyCursors = FwdOnlyCursors; - // Allocate a data source connection handle if (SQLAllocConnect(henv, &hdbc) != SQL_SUCCESS) DispAllErrors(henv); @@ -277,8 +381,7 @@ wxDb::wxDb(HENV &aHenv, bool FwdOnlyCursors) // Mark database as not open as of yet dbIsOpen = FALSE; - -} // wxDb::wxDb() +} // wxDb::initialize() /********** wxDb::Open() **********/ @@ -306,15 +409,23 @@ bool wxDb::Open(char *Dsn, char *Uid, char *AuthStr) } // Connect to the data source - retcode = SQLConnect(hdbc, (UCHAR FAR *) Dsn, SQL_NTS, - (UCHAR FAR *) Uid, SQL_NTS, - (UCHAR FAR *) AuthStr,SQL_NTS); + 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 (retcode == SQL_ERROR) + return(DispAllErrors(henv, hdbc)); +*/ + if ((retcode != SQL_SUCCESS) && + (retcode != SQL_SUCCESS_WITH_INFO)) + return(DispAllErrors(henv, hdbc)); + /* If using Intersolv branded ODBC drivers, this is the place where you would substitute your branded driver license information @@ -331,11 +442,11 @@ bool wxDb::Open(char *Dsn, char *Uid, char *AuthStr) return(DispAllErrors(henv, hdbc)); // Set Connection Options - if (! setConnectionOptions()) + if (!setConnectionOptions()) return(FALSE); // Query the data source for inf. about itself - if (! getDbInfo()) + if (!getDbInfo()) return(FALSE); // Query the data source regarding data type information @@ -380,8 +491,8 @@ bool wxDb::Open(char *Dsn, char *Uid, char *AuthStr) // SQL_INTEGER type name = 'LONG', Precision = 10 // VARCHAR = Variable length character string - if (! getDataTypeInfo(SQL_VARCHAR, typeInfVarchar)) - if (! getDataTypeInfo(SQL_CHAR, typeInfVarchar)) + if (!getDataTypeInfo(SQL_VARCHAR, typeInfVarchar)) + if (!getDataTypeInfo(SQL_CHAR, typeInfVarchar)) return(FALSE); else typeInfVarchar.FsqlType = SQL_CHAR; @@ -389,12 +500,12 @@ bool wxDb::Open(char *Dsn, char *Uid, char *AuthStr) typeInfVarchar.FsqlType = SQL_VARCHAR; // Float - if (! getDataTypeInfo(SQL_DOUBLE, typeInfFloat)) + if (!getDataTypeInfo(SQL_DOUBLE,typeInfFloat)) - if (! getDataTypeInfo(SQL_REAL, typeInfFloat)) - if (! getDataTypeInfo(SQL_FLOAT, typeInfFloat)) - if (! getDataTypeInfo(SQL_DECIMAL, typeInfFloat)) - if (! getDataTypeInfo(SQL_NUMERIC, typeInfFloat)) + if (!getDataTypeInfo(SQL_REAL,typeInfFloat)) + if (!getDataTypeInfo(SQL_FLOAT,typeInfFloat)) + if (!getDataTypeInfo(SQL_DECIMAL,typeInfFloat)) + if (!getDataTypeInfo(SQL_NUMERIC,typeInfFloat)) return(FALSE); else typeInfFloat.FsqlType = SQL_NUMERIC; @@ -407,29 +518,30 @@ bool wxDb::Open(char *Dsn, char *Uid, char *AuthStr) else typeInfFloat.FsqlType = SQL_DOUBLE; - // Integer - if (! getDataTypeInfo(SQL_INTEGER, typeInfInteger)) - // If SQL_INTEGER is not supported, use the floating point - // data type to store integers as well as floats - if (! getDataTypeInfo(typeInfFloat.FsqlType, typeInfInteger)) + if (!getDataTypeInfo(SQL_INTEGER, typeInfInteger)) + { + // If SQL_INTEGER is not supported, use the floating point + // data type to store integers as well as floats + if (!getDataTypeInfo(typeInfFloat.FsqlType, typeInfInteger)) return(FALSE); else typeInfInteger.FsqlType = typeInfFloat.FsqlType; + } else typeInfInteger.FsqlType = SQL_INTEGER; // Date/Time if (Dbms() != dbmsDBASE) - { - if (! getDataTypeInfo(SQL_TIMESTAMP, typeInfDate)) + { + if (! getDataTypeInfo(SQL_TIMESTAMP,typeInfDate)) return(FALSE); else typeInfDate.FsqlType = SQL_TIMESTAMP; } else { - if (! getDataTypeInfo(SQL_DATE, typeInfDate)) + if (!getDataTypeInfo(SQL_DATE,typeInfDate)) return(FALSE); else typeInfDate.FsqlType = SQL_DATE; @@ -449,14 +561,158 @@ bool wxDb::Open(char *Dsn, char *Uid, char *AuthStr) } // wxDb::Open() +bool wxDb::Open(wxDb *copyDb) +{ + dsn = (char *)copyDb->GetDatasourceName(); + uid = (char *)copyDb->GetUsername(); + authStr = (char *)copyDb->GetPassword(); + + RETCODE retcode; + + if (!FwdOnlyCursors()) + { + // 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); + +#ifdef DBDEBUG_CONSOLE + if (retcode == SQL_SUCCESS) + cout << "SQLSetConnectOption(CURSOR_LIB) successful" << endl; + else + cout << "SQLSetConnectOption(CURSOR_LIB) failed" << endl; +#endif + } + + // Connect to the data source + retcode = SQLConnect(hdbc, (UCHAR FAR *) dsn, SQL_NTS, + (UCHAR FAR *) uid, SQL_NTS, + (UCHAR FAR *) authStr, SQL_NTS); + + if (retcode == SQL_ERROR) + 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; + + // Allocate a statement handle for the database connection + if (SQLAllocStmt(hdbc, &hstmt) != SQL_SUCCESS) + return(DispAllErrors(henv, hdbc)); + + // Set Connection Options + if (!setConnectionOptions()) + return(FALSE); + + // Instead of Querying the data source for info about itself, it can just be copied + // from the wxDb instance that was passed in (copyDb). + wxStrcpy(dbInf.serverName,copyDb->dbInf.serverName); + wxStrcpy(dbInf.databaseName,copyDb->dbInf.databaseName); + wxStrcpy(dbInf.dbmsName,copyDb->dbInf.dbmsName); + wxStrcpy(dbInf.dbmsVer,copyDb->dbInf.dbmsVer); + dbInf.maxConnections = copyDb->dbInf.maxConnections; + dbInf.maxStmts = copyDb->dbInf.maxStmts; + wxStrcpy(dbInf.driverName,copyDb->dbInf.driverName); + wxStrcpy(dbInf.odbcVer,copyDb->dbInf.odbcVer); + wxStrcpy(dbInf.drvMgrOdbcVer,copyDb->dbInf.drvMgrOdbcVer); + wxStrcpy(dbInf.driverVer,copyDb->dbInf.driverVer); + dbInf.apiConfLvl = copyDb->dbInf.apiConfLvl; + dbInf.cliConfLvl = copyDb->dbInf.cliConfLvl; + dbInf.sqlConfLvl = copyDb->dbInf.sqlConfLvl; + wxStrcpy(dbInf.outerJoins,copyDb->dbInf.outerJoins); + wxStrcpy(dbInf.procedureSupport,copyDb->dbInf.procedureSupport); + wxStrcpy(dbInf.accessibleTables,copyDb->dbInf.accessibleTables); + dbInf.cursorCommitBehavior = copyDb->dbInf.cursorCommitBehavior; + dbInf.cursorRollbackBehavior = copyDb->dbInf.cursorRollbackBehavior; + dbInf.supportNotNullClause = copyDb->dbInf.supportNotNullClause; + wxStrcpy(dbInf.supportIEF,copyDb->dbInf.supportIEF); + dbInf.txnIsolation = copyDb->dbInf.txnIsolation; + dbInf.txnIsolationOptions = copyDb->dbInf.txnIsolationOptions; + dbInf.fetchDirections = copyDb->dbInf.fetchDirections; + dbInf.lockTypes = copyDb->dbInf.lockTypes; + dbInf.posOperations = copyDb->dbInf.posOperations; + dbInf.posStmts = copyDb->dbInf.posStmts; + dbInf.scrollConcurrency = copyDb->dbInf.scrollConcurrency; + dbInf.scrollOptions = copyDb->dbInf.scrollOptions; + dbInf.staticSensitivity = copyDb->dbInf.staticSensitivity; + dbInf.txnCapable = copyDb->dbInf.txnCapable; + dbInf.loginTimeout = copyDb->dbInf.loginTimeout; + + // VARCHAR = Variable length character string + typeInfVarchar.FsqlType = copyDb->typeInfVarchar.FsqlType; + wxStrcpy(typeInfVarchar.TypeName, copyDb->typeInfVarchar.TypeName); + typeInfVarchar.Precision = copyDb->typeInfVarchar.Precision; + typeInfVarchar.CaseSensitive = copyDb->typeInfVarchar.CaseSensitive; + typeInfVarchar.MaximumScale = copyDb->typeInfVarchar.MaximumScale; + + // Float + typeInfFloat.FsqlType = copyDb->typeInfFloat.FsqlType; + wxStrcpy(typeInfFloat.TypeName, copyDb->typeInfFloat.TypeName); + typeInfFloat.Precision = copyDb->typeInfFloat.Precision; + typeInfFloat.CaseSensitive = copyDb->typeInfFloat.CaseSensitive; + typeInfFloat.MaximumScale = copyDb->typeInfFloat.MaximumScale; + + // Integer + typeInfInteger.FsqlType = copyDb->typeInfInteger.FsqlType; + wxStrcpy(typeInfInteger.TypeName, copyDb->typeInfInteger.TypeName); + typeInfInteger.Precision = copyDb->typeInfInteger.Precision; + typeInfInteger.CaseSensitive = copyDb->typeInfInteger.CaseSensitive; + typeInfInteger.MaximumScale = copyDb->typeInfInteger.MaximumScale; + + // Date/Time + typeInfDate.FsqlType = copyDb->typeInfDate.FsqlType; + wxStrcpy(typeInfDate.TypeName, copyDb->typeInfDate.TypeName); + typeInfDate.Precision = copyDb->typeInfDate.Precision; + typeInfDate.CaseSensitive = copyDb->typeInfDate.CaseSensitive; + typeInfDate.MaximumScale = copyDb->typeInfDate.MaximumScale; + +#ifdef DBDEBUG_CONSOLE + cout << "VARCHAR DATA TYPE: " << typeInfVarchar.TypeName << endl; + cout << "INTEGER DATA TYPE: " << typeInfInteger.TypeName << endl; + cout << "FLOAT DATA TYPE: " << typeInfFloat.TypeName << endl; + cout << "DATE DATA TYPE: " << typeInfDate.TypeName << endl; + cout << endl; +#endif + + // Completed Successfully + return(TRUE); +} // wxDb::Open() 2 + + /********** wxDb::setConnectionOptions() **********/ bool wxDb::setConnectionOptions(void) /* * NOTE: The Intersolv/Oracle 7 driver was "Not Capable" of setting the login timeout. */ { + SWORD cb; + + // I need to get the DBMS name here, because some of the connection options + // are database specific and need to call the Dbms() function. + if (SQLGetInfo(hdbc, SQL_DBMS_NAME, (UCHAR*) dbInf.dbmsName, 40, &cb) != SQL_SUCCESS) + return(DispAllErrors(henv, hdbc)); + SQLSetConnectOption(hdbc, SQL_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF); SQLSetConnectOption(hdbc, SQL_OPT_TRACE, SQL_OPT_TRACE_OFF); +// SQLSetConnectOption(hdbc, SQL_TXN_ISOLATION, SQL_TXN_READ_COMMITTED); // No dirty reads + + // By default, MS Sql Server closes cursors on commit and rollback. The following + // call to SQLSetConnectOption() is needed to force SQL Server to preserve cursors + // after a transaction. This is a driver specific option and is not part of the + // ODBC standard. Note: this behavior is specific to the ODBC interface to SQL Server. + // The database settings don't have any effect one way or the other. + if (Dbms() == dbmsMS_SQL_SERVER) + { + const long SQL_PRESERVE_CURSORS = 1204L; + const long SQL_PC_ON = 1L; + SQLSetConnectOption(hdbc, SQL_PRESERVE_CURSORS, SQL_PC_ON); + } // Display the connection options to verify them #ifdef DBDEBUG_CONSOLE @@ -543,7 +799,11 @@ bool wxDb::getDbInfo(void) return(DispAllErrors(henv, hdbc)); if (SQLGetInfo(hdbc, SQL_ODBC_SAG_CLI_CONFORMANCE, (UCHAR*) &dbInf.cliConfLvl, sizeof(dbInf.cliConfLvl), &cb) != SQL_SUCCESS) - return(DispAllErrors(henv, hdbc)); +// return(DispAllErrors(henv, hdbc)); + { + // Not all drivers support this call - Nick Gorham(unixODBC) + dbInf.cliConfLvl = 0; + } if (SQLGetInfo(hdbc, SQL_ODBC_SQL_CONFORMANCE, (UCHAR*) &dbInf.sqlConfLvl, sizeof(dbInf.sqlConfLvl), &cb) != SQL_SUCCESS) return(DispAllErrors(henv, hdbc)); @@ -554,6 +814,9 @@ bool wxDb::getDbInfo(void) if (SQLGetInfo(hdbc, SQL_PROCEDURES, (UCHAR*) dbInf.procedureSupport, 2, &cb) != SQL_SUCCESS) return(DispAllErrors(henv, hdbc)); + if (SQLGetInfo(hdbc, SQL_ACCESSIBLE_TABLES, (UCHAR*) dbInf.accessibleTables, 2, &cb) != SQL_SUCCESS) + return(DispAllErrors(henv, hdbc)); + if (SQLGetInfo(hdbc, SQL_CURSOR_COMMIT_BEHAVIOR, (UCHAR*) &dbInf.cursorCommitBehavior, sizeof(dbInf.cursorCommitBehavior), &cb) != SQL_SUCCESS) return(DispAllErrors(henv, hdbc)); @@ -625,16 +888,16 @@ bool wxDb::getDbInfo(void) cout << "SQL Conf. Level: "; switch(dbInf.sqlConfLvl) { - case SQL_OSC_MINIMUM: cout << "Minimum Grammer"; break; - case SQL_OSC_CORE: cout << "Core Grammer"; break; - case SQL_OSC_EXTENDED: cout << "Extended Grammer"; break; + case SQL_OSC_MINIMUM: cout << "Minimum Grammar"; break; + case SQL_OSC_CORE: cout << "Core Grammar"; break; + case SQL_OSC_EXTENDED: cout << "Extended Grammar"; break; } cout << endl; cout << "Max. Connections: " << dbInf.maxConnections << endl; cout << "Outer Joins: " << dbInf.outerJoins << endl; cout << "Support for Procedures: " << dbInf.procedureSupport << endl; - + cout << "All tables accessible : " << dbInf.accessibleTables << endl; cout << "Cursor COMMIT Behavior: "; switch(dbInf.cursorCommitBehavior) { @@ -811,43 +1074,45 @@ bool wxDb::getDataTypeInfo(SWORD fSqlType, wxDbSqlTypeInfo &structSQLTypeInfo) */ RETCODE retcode; SDWORD cbRet; - + // Get information about the data type specified if (SQLGetTypeInfo(hstmt, fSqlType) != SQL_SUCCESS) return(DispAllErrors(henv, hdbc, hstmt)); // Fetch the record if ((retcode = SQLFetch(hstmt)) != SQL_SUCCESS) - { + { #ifdef DBDEBUG_CONSOLE - if (retcode == SQL_NO_DATA_FOUND) - cout << "SQL_NO_DATA_FOUND fetching inf. about data type." << endl; + if (retcode == SQL_NO_DATA_FOUND) + cout << "SQL_NO_DATA_FOUND fetching inf. about data type." << endl; #endif - DispAllErrors(henv, hdbc, hstmt); - SQLFreeStmt(hstmt, SQL_CLOSE); - return(FALSE); - } + DispAllErrors(henv, hdbc, hstmt); + SQLFreeStmt(hstmt, SQL_CLOSE); + return(FALSE); + } // Obtain columns from the record if (SQLGetData(hstmt, 1, SQL_C_CHAR, (UCHAR*) structSQLTypeInfo.TypeName, DB_TYPE_NAME_LEN, &cbRet) != SQL_SUCCESS) return(DispAllErrors(henv, hdbc, hstmt)); - // BJO 20000503: no more needed with new GetColumns... #if OLD_GETCOLUMNS // BJO 991209 if (Dbms() == dbmsMY_SQL) - { + { if (!wxStrcmp(structSQLTypeInfo.TypeName, "middleint")) wxStrcpy(structSQLTypeInfo.TypeName, "mediumint"); if (!wxStrcmp(structSQLTypeInfo.TypeName, "middleint unsigned")) wxStrcpy(structSQLTypeInfo.TypeName, "mediumint unsigned"); if (!wxStrcmp(structSQLTypeInfo.TypeName, "integer")) wxStrcpy(structSQLTypeInfo.TypeName, "int"); if (!wxStrcmp(structSQLTypeInfo.TypeName, "integer unsigned")) wxStrcpy(structSQLTypeInfo.TypeName, "int unsigned"); if (!wxStrcmp(structSQLTypeInfo.TypeName, "middleint")) wxStrcpy(structSQLTypeInfo.TypeName, "mediumint"); if (!wxStrcmp(structSQLTypeInfo.TypeName, "varchar")) wxStrcpy(structSQLTypeInfo.TypeName, "char"); - } - - // BJO 20000427 : OpenLink driver + } + + // BJO 20000427 : OpenLink driver if (!wxStrncmp(dbInf.driverName, "oplodbc", 7) || - !wxStrncmp(dbInf.driverName, "OLOD", 4)) - if (!wxStrcmp(structSQLTypeInfo.TypeName, "double precision")) wxStrcpy(structSQLTypeInfo.TypeName, "real"); + !wxStrncmp(dbInf.driverName, "OLOD", 4)) + { + if (!wxStrcmp(structSQLTypeInfo.TypeName, "double precision")) + wxStrcpy(structSQLTypeInfo.TypeName, "real"); + } #endif if (SQLGetData(hstmt, 3, SQL_C_LONG, (UCHAR*) &structSQLTypeInfo.Precision, 0, &cbRet) != SQL_SUCCESS) @@ -924,6 +1189,9 @@ void wxDb::Close(void) for (i = 0; i < DB_MAX_ERROR_HISTORY; i++) wxStrcpy(DBerrorList[i],errorList[i]); + dbmsType = dbmsUNIDENTIFIED; + dbIsOpen = FALSE; + } // wxDb::Close() @@ -958,8 +1226,21 @@ bool wxDb::RollbackTrans(void) /********** wxDb::DispAllErrors() **********/ bool wxDb::DispAllErrors(HENV aHenv, HDBC aHdbc, HSTMT aHstmt) +/* + * This function is called internally whenever an error condition prevents the user's + * request from being executed. This function will query the datasource as to the + * actual error(s) that just occured on the previous request of the datasource. + * + * The function will retrieve each error condition from the datasource and + * sprintf the codes/text values into a string which it then logs via logError(). + * If in DBDEBUG_CONSOLE mode, the constructed string will be displayed in the console + * window and program execution will be paused until the user presses a key. + * + * This function always returns a FALSE, so that functions which call this function + * can have a line like "return (DispAllErrors(henv, hdbc));" to indicate the failure + * of the users request, so that the calling code can then process the error msg log + */ { -// char odbcErrMsg[DB_MAX_ERROR_MSG_LEN]; wxString odbcErrMsg; while (SQLError(aHenv, aHdbc, aHstmt, (UCHAR FAR *) sqlState, &nativeError, (UCHAR FAR *) errorMsg, SQL_MAX_MESSAGE_LENGTH - 1, &cbErrorMsg) == SQL_SUCCESS) @@ -1000,7 +1281,6 @@ bool wxDb::GetNextError(HENV aHenv, HDBC aHdbc, HSTMT aHstmt) /********** wxDb::DispNextError() **********/ void wxDb::DispNextError(void) { -// char odbcErrMsg[DB_MAX_ERROR_MSG_LEN]; wxString odbcErrMsg; odbcErrMsg.sprintf("SQL State = %s\nNative Error Code = %li\nError Message = %s\n", sqlState, nativeError, errorMsg); @@ -1020,7 +1300,6 @@ void wxDb::DispNextError(void) wxLogDebug(odbcErrMsg,wxT("ODBC DEBUG MESSAGE")); #endif // __WXDEBUG__ - } // wxDb::DispNextError() @@ -1243,7 +1522,6 @@ int wxDb::TranslateSqlState(const wxChar *SQLState) /********** wxDb::Grant() **********/ bool wxDb::Grant(int privileges, const char *tableName, const char *userList) { -// char sqlStmt[DB_MAX_STATEMENT_LEN]; wxString sqlStmt; // Build the grant statement @@ -1297,7 +1575,6 @@ bool wxDb::Grant(int privileges, const char *tableName, const char *userList) /********** wxDb::CreateView() **********/ bool wxDb::CreateView(const char *viewName, const char *colList, const char *pSqlStmt, bool attemptDrop) { -// char sqlStmt[DB_MAX_STATEMENT_LEN]; wxString sqlStmt; // Drop the view first @@ -1436,7 +1713,7 @@ int wxDb::GetKeyFields(char *tableName, wxDbColInf* colInf, int noCols) wxString Temp0; /* * --------------------------------------------------------------------- - * -- 19991224 : mj10777@gmx.net : Create ------ + * -- 19991224 : mj10777 : Create ------ * -- : Three things are done and stored here : ------ * -- : 1) which Column(s) is/are Primary Key(s) ------ * -- : 2) which tables use this Key as a Foreign Key ------ @@ -1470,8 +1747,8 @@ int wxDb::GetKeyFields(char *tableName, wxDbColInf* colInf, int noCols) for (i=0;iuid' - // userID != "" ... UserID set equal to 'userID' - // - // NOTE: ALL column bindings associated with this wxDb instance are unbound - // by this function. This function should use its own wxDb instance - // to avoid undesired unbinding of columns. - +// +// Same as the above GetColumns() function except this one gets columns +// only for a single table, and if 'numCols' is not NULL, the number of +// columns stored in the returned wxDbColInf is set in '*numCols' +// +// userID is evaluated in the following manner: +// userID == NULL ... UserID is ignored +// userID == "" ... UserID set equal to 'this->uid' +// userID != "" ... UserID set equal to 'userID' +// +// NOTE: ALL column bindings associated with this wxDb instance are unbound +// by this function. This function should use its own wxDb instance +// to avoid undesired unbinding of columns. { - SWORD noCols = 0; - int colNo = 0; - wxDbColInf *colInf = 0; - - RETCODE retcode; - SDWORD cb; - - wxString UserID; - wxString TableName; - - if (userID) - { - if (!wxStrlen(userID)) - UserID = uid; - else - UserID = userID; - } - else - UserID = ""; - - // 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) - UserID = UserID.Upper(); - - // Pass 1 - Determine how many columns there are. - // Pass 2 - Allocate the wxDbColInf array and fill in - // the array with the column information. - int pass; - for (pass = 1; pass <= 2; pass++) - { - if (pass == 2) + SWORD noCols = 0; + int colNo = 0; + wxDbColInf *colInf = 0; + + RETCODE retcode; + SDWORD cb; + + wxString UserID; + wxString TableName; + + if (userID) + { + if (!wxStrlen(userID)) + UserID = uid; + else + UserID = userID; + } + else + UserID = ""; + + // 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) + UserID = UserID.Upper(); + + // Pass 1 - Determine how many columns there are. + // Pass 2 - Allocate the wxDbColInf array and fill in + // the array with the column information. + int pass; + for (pass = 1; pass <= 2; pass++) + { + if (pass == 2) { - if (noCols == 0) // Probably a bogus table name(s) - break; - // Allocate n wxDbColInf objects to hold the column information - colInf = new wxDbColInf[noCols+1]; - if (!colInf) - break; - // Mark the end of the array - wxStrcpy(colInf[noCols].tableName, wxT("")); - wxStrcpy(colInf[noCols].colName, wxT("")); - colInf[noCols].sqlDataType = 0; + if (noCols == 0) // Probably a bogus table name(s) + break; + // Allocate n wxDbColInf objects to hold the column information + colInf = new wxDbColInf[noCols+1]; + if (!colInf) + break; + // Mark the end of the array + wxStrcpy(colInf[noCols].tableName, wxT("")); + wxStrcpy(colInf[noCols].colName, wxT("")); + colInf[noCols].sqlDataType = 0; } - - TableName = tableName; - // Oracle table names are uppercase only, so force - // the name to uppercase just in case programmer forgot to do this - if (Dbms() == dbmsORACLE) - TableName = TableName.Upper(); - - SQLFreeStmt(hstmt, SQL_CLOSE); - - // 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 (wxStrcmp(UserID.c_str(),wxT("")) && - Dbms() != dbmsMY_SQL && - Dbms() != dbmsACCESS) + + TableName = tableName; + // Oracle and Interbase table names are uppercase only, so force + // the name to uppercase just in case programmer forgot to do this + if ((Dbms() == dbmsORACLE) || + (Dbms() == dbmsINTERBASE)) + TableName = TableName.Upper(); + + SQLFreeStmt(hstmt, SQL_CLOSE); + + // MySQL, SQLServer, 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 (wxStrcmp(UserID.c_str(),wxT("")) && + Dbms() != dbmsMY_SQL && + Dbms() != dbmsACCESS && + Dbms() != dbmsMS_SQL_SERVER) { - retcode = SQLColumns(hstmt, - NULL, 0, // All qualifiers - (UCHAR *) UserID.c_str(), SQL_NTS, // Owner - (UCHAR *) TableName.c_str(), SQL_NTS, - NULL, 0); // All columns + retcode = SQLColumns(hstmt, + NULL, 0, // All qualifiers + (UCHAR *) UserID.c_str(), SQL_NTS, // Owner + (UCHAR *) TableName.c_str(), SQL_NTS, + NULL, 0); // All columns } - else + else { - retcode = SQLColumns(hstmt, - NULL, 0, // All qualifiers - NULL, 0, // Owner - (UCHAR *) TableName.c_str(), SQL_NTS, - NULL, 0); // All columns + retcode = SQLColumns(hstmt, + NULL, 0, // All qualifiers + NULL, 0, // Owner + (UCHAR *) TableName.c_str(), SQL_NTS, + NULL, 0); // All columns } - if (retcode != SQL_SUCCESS) + if (retcode != SQL_SUCCESS) { // Error occured, abort - DispAllErrors(henv, hdbc, hstmt); - if (colInf) - delete [] colInf; - SQLFreeStmt(hstmt, SQL_CLOSE); - if (numCols) - *numCols = 0; - return(0); + DispAllErrors(henv, hdbc, hstmt); + if (colInf) + delete [] colInf; + SQLFreeStmt(hstmt, SQL_CLOSE); + if (numCols) + *numCols = 0; + return(0); } while ((retcode = SQLFetch(hstmt)) == SQL_SUCCESS) @@ -2118,7 +2414,7 @@ wxDbColInf *wxDb::GetColumns(char *tableName, int *numCols, const char *userID) GetData( 4, SQL_C_CHAR, (UCHAR*) colInf[colNo].colName, DB_MAX_COLUMN_NAME_LEN+1, &cb); GetData( 5, SQL_C_SSHORT, (UCHAR*) &colInf[colNo].sqlDataType, 0, &cb); GetData( 6, SQL_C_CHAR, (UCHAR*) colInf[colNo].typeName, 128+1, &cb); - GetData( 7, SQL_C_SLONG, (UCHAR*) &colInf[colNo].columnSize, 0, &cb); + GetData( 7, SQL_C_SLONG, (UCHAR*) &colInf[colNo].columnSize, 0, &cb); GetData( 8, SQL_C_SSHORT, (UCHAR*) &colInf[colNo].bufferLength, 0, &cb); GetData( 9, SQL_C_SSHORT, (UCHAR*) &colInf[colNo].decimalDigits,0, &cb); GetData(10, SQL_C_SSHORT, (UCHAR*) &colInf[colNo].numPrecRadix, 0, &cb); @@ -2129,151 +2425,146 @@ wxDbColInf *wxDb::GetColumns(char *tableName, int *numCols, const char *userID) colInf[colNo].PkTableName[0] = 0; // Tablenames where Primary Key is used as a Foreign Key colInf[colNo].FkCol = 0; // Foreign key column 0=No; 1= First Key, 2 = Second Key etc. colInf[colNo].FkTableName[0] = 0; // Foreign key table name - -#ifdef _IODBC_ - // IODBC returns the columnSize in bufferLength.. (bug) - colInf[colNo].columnSize = colInf[colNo].bufferLength; + +#ifdef _IODBC_ + // IODBC does not return a correct columnSize, so we set + // columnSize = bufferLength if no column size was returned + // IODBC returns the columnSize in bufferLength.. (bug) + if (colInf[colNo].columnSize < 1) + { + colInf[colNo].columnSize = colInf[colNo].bufferLength; + } #endif - - // Determine the wxDb data type that is used to represent the native data type of this data source - colInf[colNo].dbDataType = 0; - // Get the intern datatype - switch (colInf[colNo].sqlDataType) - { - case SQL_VARCHAR: - case SQL_CHAR: - colInf[colNo].dbDataType = DB_DATA_TYPE_VARCHAR; - break; - - case SQL_TINYINT: - case SQL_SMALLINT: - case SQL_INTEGER: - colInf[colNo].dbDataType = DB_DATA_TYPE_INTEGER; - break; - case SQL_DOUBLE: - case SQL_DECIMAL: - case SQL_NUMERIC: - case SQL_FLOAT: - case SQL_REAL: - colInf[colNo].dbDataType = DB_DATA_TYPE_FLOAT; - break; - case SQL_DATE: - colInf[colNo].dbDataType = DB_DATA_TYPE_DATE; - break; - - + + // Determine the wxDb data type that is used to represent the native data type of this data source + colInf[colNo].dbDataType = 0; + // Get the intern datatype + switch (colInf[colNo].sqlDataType) + { + case SQL_VARCHAR: + case SQL_CHAR: + colInf[colNo].dbDataType = DB_DATA_TYPE_VARCHAR; + break; + + case SQL_TINYINT: + case SQL_SMALLINT: + case SQL_INTEGER: + colInf[colNo].dbDataType = DB_DATA_TYPE_INTEGER; + break; + case SQL_DOUBLE: + case SQL_DECIMAL: + case SQL_NUMERIC: + case SQL_FLOAT: + case SQL_REAL: + colInf[colNo].dbDataType = DB_DATA_TYPE_FLOAT; + break; + case SQL_DATE: + colInf[colNo].dbDataType = DB_DATA_TYPE_DATE; + break; #ifdef __WXDEBUG__ - default: - wxString errMsg; - errMsg.sprintf("SQL Data type %d currently not supported by wxWindows", colInf[colNo].sqlDataType); - wxLogDebug(errMsg,wxT("ODBC DEBUG MESSAGE")); -#endif - } - - - + default: + wxString errMsg; + errMsg.sprintf("SQL Data type %d currently not supported by wxWindows", colInf[colNo].sqlDataType); + wxLogDebug(errMsg,wxT("ODBC DEBUG MESSAGE")); +#endif + } colNo++; } } } if (retcode != SQL_NO_DATA_FOUND) - { // Error occured, abort + { // Error occured, abort DispAllErrors(henv, hdbc, hstmt); if (colInf) - delete [] colInf; + delete [] colInf; SQLFreeStmt(hstmt, SQL_CLOSE); if (numCols) - *numCols = 0; + *numCols = 0; return(0); - } - } - - SQLFreeStmt(hstmt, SQL_CLOSE); - - // Store Primary and Foreign Keys - GetKeyFields(tableName,colInf,noCols); - - - - /////////////////////////////////////////////////////////////////////////// - // Now sort the the columns in order to make them appear in the right order - /////////////////////////////////////////////////////////////////////////// - - // Build a generic SELECT statement which returns 0 rows - wxString Stmt; - - Stmt.sprintf("select * from %s where 0=1", tableName); - - // Execute query - if (SQLExecDirect(hstmt, (UCHAR FAR *) Stmt.c_str(), SQL_NTS) != SQL_SUCCESS) - { - DispAllErrors(henv, hdbc, hstmt); - return NULL; - } - - // Get the number of result columns - if (SQLNumResultCols (hstmt, &noCols) != SQL_SUCCESS) - { - DispAllErrors(henv, hdbc, hstmt); - return NULL; - } - - if (noCols == 0) // Probably a bogus table name - return NULL; - - // Get the name - int i; - short colNum; - UCHAR name[100]; - SWORD Sword; - SDWORD Sdword; - for (colNum = 0; colNum < noCols; colNum++) - { - if (SQLColAttributes(hstmt,colNum+1, SQL_COLUMN_NAME, - name, sizeof(name), - &Sword, &Sdword) != SQL_SUCCESS) - { - DispAllErrors(henv, hdbc, hstmt); - return NULL; - } - - wxString Name1 = name; - Name1 = Name1.Upper(); - - // Where is this name in the array ? - for (i = colNum ; i < noCols ; i++) - { - wxString Name2 = colInf[i].colName; - Name2 = Name2.Upper(); - if (Name2 == Name1) - { - if (colNum != i) // swap to sort - { - wxDbColInf tmpColInf = colInf[colNum]; - colInf[colNum] = colInf[i]; - colInf[i] = tmpColInf; - } - break; - } - } - } - SQLFreeStmt(hstmt, SQL_CLOSE); - - /////////////////////////////////////////////////////////////////////////// - // End sorting - /////////////////////////////////////////////////////////////////////////// - - - - - if (numCols) - *numCols = noCols; - return colInf; - + } + } + + SQLFreeStmt(hstmt, SQL_CLOSE); + + // Store Primary and Foreign Keys + GetKeyFields(tableName,colInf,noCols); + + /////////////////////////////////////////////////////////////////////////// + // Now sort the the columns in order to make them appear in the right order + /////////////////////////////////////////////////////////////////////////// + + // Build a generic SELECT statement which returns 0 rows + wxString Stmt; + + Stmt.sprintf("select * from %s where 0=1", tableName); + + // Execute query + if (SQLExecDirect(hstmt, (UCHAR FAR *) Stmt.c_str(), SQL_NTS) != SQL_SUCCESS) + { + DispAllErrors(henv, hdbc, hstmt); + return NULL; + } + + // Get the number of result columns + if (SQLNumResultCols (hstmt, &noCols) != SQL_SUCCESS) + { + DispAllErrors(henv, hdbc, hstmt); + return NULL; + } + + if (noCols == 0) // Probably a bogus table name + return NULL; + + // Get the name + int i; + short colNum; + UCHAR name[100]; + SWORD Sword; + SDWORD Sdword; + for (colNum = 0; colNum < noCols; colNum++) + { + if (SQLColAttributes(hstmt,colNum+1, SQL_COLUMN_NAME, + name, sizeof(name), + &Sword, &Sdword) != SQL_SUCCESS) + { + DispAllErrors(henv, hdbc, hstmt); + return NULL; + } + + wxString Name1 = name; + Name1 = Name1.Upper(); + + // Where is this name in the array ? + for (i = colNum ; i < noCols ; i++) + { + wxString Name2 = colInf[i].colName; + Name2 = Name2.Upper(); + if (Name2 == Name1) + { + if (colNum != i) // swap to sort + { + wxDbColInf tmpColInf = colInf[colNum]; + colInf[colNum] = colInf[i]; + colInf[i] = tmpColInf; + } + break; + } + } + } + SQLFreeStmt(hstmt, SQL_CLOSE); + + /////////////////////////////////////////////////////////////////////////// + // End sorting + /////////////////////////////////////////////////////////////////////////// + + if (numCols) + *numCols = noCols; + return colInf; + } // wxDb::GetColumns() -#endif +#endif // #else OLD_GETCOLUMNS /********** wxDb::GetColumnCount() **********/ @@ -2323,22 +2614,24 @@ int wxDb::GetColumnCount(char *tableName, const char *userID) // Loop through each table name { TableName = tableName; - // Oracle table names are uppercase only, so force + // Oracle and Interbase table names are uppercase only, so force // the name to uppercase just in case programmer forgot to do this - if (Dbms() == dbmsORACLE) + if ((Dbms() == dbmsORACLE) || + (Dbms() == dbmsINTERBASE)) TableName = TableName.Upper(); SQLFreeStmt(hstmt, SQL_CLOSE); - // MySQL and Access cannot accept a user name when looking up column names, so we + // MySQL, SQLServer, 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 (wxStrcmp(UserID.c_str(),wxT("")) && Dbms() != dbmsMY_SQL && - Dbms() != dbmsACCESS) + Dbms() != dbmsACCESS && + Dbms() != dbmsMS_SQL_SERVER) { retcode = SQLColumns(hstmt, NULL, 0, // All qualifiers - (UCHAR *) UserID.c_str(), SQL_NTS, // Owner + (UCHAR *) UserID.c_str(), SQL_NTS, // Owner (UCHAR *) TableName.c_str(), SQL_NTS, NULL, 0); // All columns } @@ -2380,7 +2673,7 @@ int wxDb::GetColumnCount(char *tableName, const char *userID) wxDbInf *wxDb::GetCatalog(char *userID) /* * --------------------------------------------------------------------- - * -- 19991203 : mj10777@gmx.net : Create ------ + * -- 19991203 : mj10777 : Create ------ * -- : Creates a wxDbInf with Tables / Cols Array ------ * -- : uses SQLTables and fills pTableInf; ------ * -- : pColInf is set to NULL and numCols to 0; ------ @@ -2405,7 +2698,6 @@ wxDbInf *wxDb::GetCatalog(char *userID) int pass; RETCODE retcode; SDWORD cb; -// char tblNameSave[DB_MAX_TABLE_NAME_LEN+1]; wxString tblNameSave; wxString UserID; @@ -2431,10 +2723,6 @@ wxDbInf *wxDb::GetCatalog(char *userID) //------------------------------------------------------------- pDbInf = new wxDbInf; // Create the Database Arrray - pDbInf->catalog[0] = 0; - pDbInf->schema[0] = 0; - pDbInf->numTables = 0; // Counter for Tables - pDbInf->pTableInf = NULL; // Array of Tables //------------------------------------------------------------- // Table Information // Pass 1 - Determine how many Tables there are. @@ -2442,26 +2730,23 @@ wxDbInf *wxDb::GetCatalog(char *userID) // - Create the Cols array = NULL //------------------------------------------------------------- - for (pass = 1; pass <= 2; pass++) { SQLFreeStmt(hstmt, SQL_CLOSE); // Close if Open tblNameSave = wxT(""); if (wxStrcmp(UserID.c_str(),wxT("")) && - Dbms() != dbmsMY_SQL && - Dbms() != dbmsACCESS) + Dbms() != dbmsMY_SQL && + Dbms() != dbmsACCESS) { - retcode = SQLTables(hstmt, NULL, 0, // All qualifiers - (UCHAR *) UserID.c_str(), SQL_NTS, // User specified + (UCHAR *) UserID.c_str(), SQL_NTS, // User specified NULL, 0, // All tables NULL, 0); // All columns } else { - retcode = SQLTables(hstmt, NULL, 0, // All qualifiers NULL, 0, // User specified @@ -2471,16 +2756,14 @@ wxDbInf *wxDb::GetCatalog(char *userID) if (retcode != SQL_SUCCESS) { - DispAllErrors(henv, hdbc, hstmt); pDbInf = NULL; SQLFreeStmt(hstmt, SQL_CLOSE); return pDbInf; } - + while ((retcode = SQLFetch(hstmt)) == SQL_SUCCESS) // Table Information { - if (pass == 1) // First pass, just count the Tables { if (pDbInf->numTables == 0) @@ -2495,25 +2778,17 @@ wxDbInf *wxDb::GetCatalog(char *userID) if (pDbInf->pTableInf == NULL) // Has the Table Array been created { // no, then create the Array pDbInf->pTableInf = new wxDbTableInf[pDbInf->numTables]; - for (noTab=0;noTabnumTables;noTab++) - { - (pDbInf->pTableInf+noTab)->tableName[0] = 0; - (pDbInf->pTableInf+noTab)->tableType[0] = 0; - (pDbInf->pTableInf+noTab)->tableRemarks[0] = 0; - (pDbInf->pTableInf+noTab)->numCols = 0; - (pDbInf->pTableInf+noTab)->pColInf = NULL; - } noTab = 0; - } // if (pDbInf->pTableInf == NULL) // Has the Table Array been created + } // if (pDbInf->pTableInf == NULL) // Has the Table Array been created GetData( 3, SQL_C_CHAR, (UCHAR*) (pDbInf->pTableInf+noTab)->tableName, DB_MAX_TABLE_NAME_LEN+1, &cb); GetData( 4, SQL_C_CHAR, (UCHAR*) (pDbInf->pTableInf+noTab)->tableType, 30+1, &cb); GetData( 5, SQL_C_CHAR, (UCHAR*) (pDbInf->pTableInf+noTab)->tableRemarks, 254+1, &cb); - + noTab++; - } // if (pass == 2) We now know the amount of Tables - } // while ((retcode = SQLFetch(hstmt)) == SQL_SUCCESS) - } // for (pass = 1; pass <= 2; pass++) + } // if + } // while + } // for SQLFreeStmt(hstmt, SQL_CLOSE); // Query how many columns are in each table @@ -2521,7 +2796,9 @@ wxDbInf *wxDb::GetCatalog(char *userID) { (pDbInf->pTableInf+noTab)->numCols = GetColumnCount((pDbInf->pTableInf+noTab)->tableName,UserID); } + return pDbInf; + } // wxDb::GetCatalog() @@ -2586,7 +2863,7 @@ bool wxDb::Catalog(const char *userID, const char *fileName) { retcode = SQLColumns(hstmt, NULL, 0, // All qualifiers - (UCHAR *) UserID.c_str(), SQL_NTS, // User specified + (UCHAR *) UserID.c_str(), SQL_NTS, // User specified NULL, 0, // All tables NULL, 0); // All columns } @@ -2678,18 +2955,17 @@ bool wxDb::TableExists(const char *tableName, const char *userID, const char *ta assert(tableName && wxStrlen(tableName)); - if (Dbms() == dbmsDBASE) + if (Dbms() == dbmsDBASE) { - - wxString dbName; - if (tablePath && wxStrlen(tablePath)) - dbName.sprintf("%s\\%s.dbf",tablePath,tableName); - else - dbName.sprintf("%s.dbf",tableName); - - bool exists; - exists = wxFileExists(dbName.c_str()); - return exists; + wxString dbName; + if (tablePath && wxStrlen(tablePath)) + dbName.sprintf("%s\\%s.dbf",tablePath,tableName); + else + dbName.sprintf("%s.dbf",tableName); + + bool exists; + exists = wxFileExists(dbName.c_str()); + return exists; } if (userID) @@ -2708,50 +2984,177 @@ bool wxDb::TableExists(const char *tableName, const char *userID, const char *ta UserID = UserID.Upper(); TableName = tableName; - // Oracle table names are uppercase only, so force + // Oracle and Interbase table names are uppercase only, so force // the name to uppercase just in case programmer forgot to do this - if (Dbms() == dbmsORACLE) + if ((Dbms() == dbmsORACLE) || + (Dbms() == dbmsINTERBASE)) TableName = TableName.Upper(); SQLFreeStmt(hstmt, SQL_CLOSE); 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 + // Some databases cannot accept a user name when looking up table names, + // so we use the call below that leaves out the user name if (wxStrcmp(UserID,"") && - Dbms() != dbmsMY_SQL && - Dbms() != dbmsACCESS) + Dbms() != dbmsMY_SQL && + Dbms() != dbmsACCESS && + Dbms() != dbmsMS_SQL_SERVER) { retcode = SQLTables(hstmt, - NULL, 0, // All qualifiers - (UCHAR *) UserID.c_str(), SQL_NTS, // All owners + NULL, 0, // All qualifiers + (UCHAR *) UserID.c_str(), SQL_NTS, // Only tables owned by this user (UCHAR FAR *)TableName.c_str(), SQL_NTS, - NULL, 0); // All table types + NULL, 0); // All table types } else { retcode = SQLTables(hstmt, - NULL, 0, // All qualifiers - NULL, 0, // All owners + NULL, 0, // All qualifiers + NULL, 0, // All owners (UCHAR FAR *)TableName.c_str(), SQL_NTS, - NULL, 0); // All table types + NULL, 0); // All table types } if (retcode != SQL_SUCCESS) return(DispAllErrors(henv, hdbc, hstmt)); retcode = SQLFetch(hstmt); - if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO) + if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO) { SQLFreeStmt(hstmt, SQL_CLOSE); return(DispAllErrors(henv, hdbc, hstmt)); } SQLFreeStmt(hstmt, SQL_CLOSE); + return(TRUE); } // wxDb::TableExists() +/********** wxDb::TablePrivileges() **********/ +bool wxDb::TablePrivileges(const char *tableName, const char* priv, const char *userID, + const char *schema, const char *tablePath) +{ + wxDbTablePrivilegeInfo result; + SDWORD cbRetVal; + RETCODE retcode; + + //We probably need to be able to dynamically set this based on + //the driver type, and state. + char curRole[]="public"; + + //Prologue here similar to db::TableExists() + wxString UserID; + wxString TableName; + + assert(userID); + assert(tableName && wxStrlen(tableName)); + + if (userID) + { + if (!wxStrlen(userID)) + UserID = uid; + else + UserID = userID; + } + else + UserID = ""; + + // Oracle user names may only be in uppercase, so force + // the name to uppercase + if (Dbms() == dbmsORACLE) + UserID = UserID.Upper(); + + TableName = tableName; + // Oracle and Interbase table names are uppercase only, so force + // the name to uppercase just in case programmer forgot to do this + if ((Dbms() == dbmsORACLE) || + (Dbms() == dbmsINTERBASE)) + TableName = TableName.Upper(); + + SQLFreeStmt(hstmt, SQL_CLOSE); + + if (!schema) + { + retcode = SQLTablePrivileges(hstmt, + NULL, 0, // Catalog + NULL, 0, // Schema + (UCHAR FAR *)TableName.c_str(), SQL_NTS); + } + else + { + retcode = SQLTablePrivileges(hstmt, + NULL, 0, // Catalog + (UCHAR FAR *)schema, SQL_NTS, // Schema + (UCHAR FAR *)TableName.c_str(), SQL_NTS); + } + +#ifdef DBDEBUG_CONSOLE + fprintf(stderr ,"SQLTablePrivileges() returned %i \n",retcode); +#endif + + if ((retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO)) + return(DispAllErrors(henv, hdbc, hstmt)); + + retcode = SQLFetch(hstmt); + while (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) + { + if (SQLGetData(hstmt, 1, SQL_C_CHAR, (UCHAR*) result.tableQual, sizeof(result.tableQual), &cbRetVal) != SQL_SUCCESS) + return(DispAllErrors(henv, hdbc, hstmt)); + + if (SQLGetData(hstmt, 2, SQL_C_CHAR, (UCHAR*) result.tableOwner, sizeof(result.tableOwner), &cbRetVal) != SQL_SUCCESS) + return(DispAllErrors(henv, hdbc, hstmt)); + + if (SQLGetData(hstmt, 3, SQL_C_CHAR, (UCHAR*) result.tableName, sizeof(result.tableName), &cbRetVal) != SQL_SUCCESS) + return(DispAllErrors(henv, hdbc, hstmt)); + + if (SQLGetData(hstmt, 4, SQL_C_CHAR, (UCHAR*) result.grantor, sizeof(result.grantor), &cbRetVal) != SQL_SUCCESS) + return(DispAllErrors(henv, hdbc, hstmt)); + + if (SQLGetData(hstmt, 5, SQL_C_CHAR, (UCHAR*) result.grantee, sizeof(result.grantee), &cbRetVal) != SQL_SUCCESS) + return(DispAllErrors(henv, hdbc, hstmt)); + + if (SQLGetData(hstmt, 6, SQL_C_CHAR, (UCHAR*) result.privilege, sizeof(result.privilege), &cbRetVal) != SQL_SUCCESS) + return(DispAllErrors(henv, hdbc, hstmt)); + + if (SQLGetData(hstmt, 7, SQL_C_CHAR, (UCHAR*) result.grantable, sizeof(result.grantable), &cbRetVal) != SQL_SUCCESS) + return(DispAllErrors(henv, hdbc, hstmt)); + +#ifdef DBDEBUG_CONSOLE + fprintf(stderr,"Scanning %s privilege on table %s.%s granted by %s to %s\n", + result.privilege,result.tableOwner,result.tableName, + result.grantor, result.grantee); +#endif + + if (UserID.IsSameAs(result.tableOwner,FALSE)) + { + SQLFreeStmt(hstmt, SQL_CLOSE); + return TRUE; + } + + if (UserID.IsSameAs(result.grantee,FALSE) && + !wxStrcmp(result.privilege,priv)) + { + SQLFreeStmt(hstmt, SQL_CLOSE); + return TRUE; + } + + if (!wxStrcmp(result.grantee,curRole) && + !wxStrcmp(result.privilege,priv)) + { + SQLFreeStmt(hstmt, SQL_CLOSE); + return TRUE; + } + + retcode = SQLFetch(hstmt); + } + + SQLFreeStmt(hstmt, SQL_CLOSE); + return FALSE; + +} // wxDb::TablePrivileges + + /********** wxDb::SetSqlLogging() **********/ bool wxDb::SetSqlLogging(wxDbSqlLogState state, const char *filename, bool append) { @@ -2835,65 +3238,91 @@ wxDBMS wxDb::Dbms(void) * * SYBASE (Enterprise) * - If a column is part of the Primary Key, the column cannot be NULL + * - Maximum row size is somewhere in the neighborhood of 1920 bytes * * 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 + * - Columns that are part of primary or secondary keys must be defined as being NOT NULL + * when they are created. Some code is added in ::CreateIndex to try to adjust the + * column definition if it is not defined correctly, but it is experimental + * - Does not support sub-queries in SQL statements * * POSTGRES * - Does not support the keywords 'ASC' or 'DESC' as of release v6.5.0 + * - Does not support sub-queries in SQL statements * + * DB2 + * - Primary keys must be declared as NOT NULL * */ { + // Should only need to do this once for each new database connection + // so return the value we already determined it to be to save time + // and lots of string comparisons + if (dbmsType != dbmsUNIDENTIFIED) + return(dbmsType); + wxChar baseName[25+1]; wxStrncpy(baseName,dbInf.dbmsName,25); + baseName[25] = 0; + + // RGG 20001025 : add support for Interbase + // GT : Integrated to base classes on 20001121 + if (!wxStricmp(dbInf.dbmsName,"Interbase")) + return((wxDBMS)(dbmsType = dbmsINTERBASE)); // BJO 20000428 : add support for Virtuoso if (!wxStricmp(dbInf.dbmsName,"OpenLink Virtuoso VDBMS")) - return(dbmsVIRTUOSO); - + return((wxDBMS)(dbmsType = dbmsVIRTUOSO)); if (!wxStricmp(dbInf.dbmsName,"Adaptive Server Anywhere")) - return(dbmsSYBASE_ASA); - + return((wxDBMS)(dbmsType = dbmsSYBASE_ASA)); + // BJO 20000427 : The "SQL Server" string is also returned by SQLServer when // connected through an OpenLink driver. - // Is it also returned by Sybase Adapatitve server? - // OpenLink driver name is OLOD3032.DLL for msw and oplodbc.so for unix - if (!wxStricmp(dbInf.dbmsName,"SQL Server")) + // Is it also returned by Sybase Adapatitve server? + // OpenLink driver name is OLOD3032.DLL for msw and oplodbc.so for unix + if (!wxStricmp(dbInf.dbmsName,"SQL Server")) + { if (!wxStrncmp(dbInf.driverName, "oplodbc", 7) || !wxStrncmp(dbInf.driverName, "OLOD", 4)) - return dbmsMS_SQL_SERVER; else return dbmsSYBASE_ASE; - + return ((wxDBMS)(dbmsMS_SQL_SERVER)); + else + return ((wxDBMS)(dbmsType = dbmsSYBASE_ASE)); + } if (!wxStricmp(dbInf.dbmsName,"Microsoft SQL Server")) - return(dbmsMS_SQL_SERVER); + return((wxDBMS)(dbmsType = dbmsMS_SQL_SERVER)); if (!wxStricmp(dbInf.dbmsName,"MySQL")) - return(dbmsMY_SQL); + return((wxDBMS)(dbmsType = dbmsMY_SQL)); if (!wxStricmp(dbInf.dbmsName,"PostgreSQL")) // v6.5.0 - return(dbmsPOSTGRES); + return((wxDBMS)(dbmsType = dbmsPOSTGRES)); baseName[8] = 0; if (!wxStricmp(baseName,"Informix")) - return(dbmsINFORMIX); - + return((wxDBMS)(dbmsType = dbmsINFORMIX)); baseName[6] = 0; if (!wxStricmp(baseName,"Oracle")) - return(dbmsORACLE); + return((wxDBMS)(dbmsType = dbmsORACLE)); if (!wxStricmp(dbInf.dbmsName,"ACCESS")) - return(dbmsACCESS); + return((wxDBMS)(dbmsType = dbmsACCESS)); if (!wxStricmp(dbInf.dbmsName,"MySQL")) - return(dbmsMY_SQL); - if (!wxStricmp(baseName,"Sybase")) - return(dbmsSYBASE_ASE); + return((wxDBMS)(dbmsType = dbmsMY_SQL)); + if (!wxStricmp(baseName,"Sybase")) + return((wxDBMS)(dbmsType = dbmsSYBASE_ASE)); baseName[5] = 0; if (!wxStricmp(baseName,"DBASE")) - return(dbmsDBASE); + return((wxDBMS)(dbmsType = dbmsDBASE)); + + baseName[3] = 0; + if (!wxStricmp(baseName,"DB2")) + return((wxDBMS)(dbmsType = dbmsDBASE)); + + return((wxDBMS)(dbmsType = dbmsUNIDENTIFIED)); - return(dbmsUNIDENTIFIED); } // wxDb::Dbms() @@ -2902,17 +3331,31 @@ wxDb WXDLLEXPORT *wxDbGetConnection(wxDbConnectInf *pDbConfig, bool FwdOnlyCurso { wxDbList *pList; + // Used to keep a pointer to a DB connection that matches the requested + // DSN and FwdOnlyCursors settings, even if it is not FREE, so that the + // data types can be copied from it (using the wxDb::Open(wxDb *) function) + // rather than having to re-query the datasource to get all the values + // using the wxDb::Open(Dsn,Uid,AuthStr) function + wxDb *matchingDbConnection = NULL; + // Scan the linked list searching for an available database connection // that's already been opened but is currently not in use. for (pList = PtrBegDbList; pList; pList = pList->PtrNext) { // The database connection must be for the same datasource // name and must currently not be in use. - if (pList->Free && (! wxStrcmp(pDbConfig->Dsn, pList->Dsn))) // Found a free connection + if (pList->Free && + (pList->PtrDb->FwdOnlyCursors() == FwdOnlyCursors) && + (!wxStrcmp(pDbConfig->Dsn, pList->Dsn))) // Found a free connection { pList->Free = FALSE; return(pList->PtrDb); } + + if (!wxStrcmp(pDbConfig->Dsn, pList->Dsn) && + !wxStrcmp(pDbConfig->Uid, pList->Uid) && + !wxStrcmp(pDbConfig->AuthStr, pList->AuthStr)) + matchingDbConnection = pList->PtrDb; } // No available connections. A new connection must be made and @@ -2926,7 +3369,7 @@ wxDb WXDLLEXPORT *wxDbGetConnection(wxDbConnectInf *pDbConfig, bool FwdOnlyCurso pList->PtrNext->PtrPrev = pList; pList = pList->PtrNext; } - else // Empty list + else // Empty list { // Create the first node on the list pList = PtrBegDbList = new wxDbList; @@ -2937,10 +3380,20 @@ wxDb WXDLLEXPORT *wxDbGetConnection(wxDbConnectInf *pDbConfig, bool FwdOnlyCurso pList->PtrNext = 0; pList->Free = FALSE; wxStrcpy(pList->Dsn, pDbConfig->Dsn); + wxStrcpy(pList->Uid, pDbConfig->Uid); + wxStrcpy(pList->AuthStr, pDbConfig->AuthStr); + pList->PtrDb = new wxDb(pDbConfig->Henv,FwdOnlyCursors); + bool opened = FALSE; + + if (!matchingDbConnection) + opened = pList->PtrDb->Open(pDbConfig->Dsn, pDbConfig->Uid, pDbConfig->AuthStr); + else + opened = pList->PtrDb->Open(matchingDbConnection); + // Connect to the datasource - if (pList->PtrDb->Open(pDbConfig->Dsn, pDbConfig->Uid, pDbConfig->AuthStr)) + if (opened) { pList->PtrDb->SetSqlLogging(SQLLOGstate,SQLLOGfn,TRUE); return(pList->PtrDb); @@ -2969,8 +3422,8 @@ bool WXDLLEXPORT wxDbFreeConnection(wxDb *pDb) // Scan the linked list searching for the database connection for (pList = PtrBegDbList; pList; pList = pList->PtrNext) { - if (pList->PtrDb == pDb) // Found it!!! - return(pList->Free = TRUE); + if (pList->PtrDb == pDb) // Found it, now free it!!! + return (pList->Free = TRUE); } // Never found the database object, return failure @@ -3000,7 +3453,7 @@ void WXDLLEXPORT wxDbCloseConnections(void) } // wxDbCloseConnections() -/********** wxDbNumberConnectionsInUse() **********/ +/********** wxDbConnectionsInUse() **********/ int WXDLLEXPORT wxDbConnectionsInUse(void) { wxDbList *pList; @@ -3032,7 +3485,7 @@ bool wxDbSqlLog(wxDbSqlLogState state, const wxChar *filename) } SQLLOGstate = state; - wxStrcpy(SQLLOGfn,filename); + SQLLOGfn = filename; return(TRUE); @@ -3045,12 +3498,15 @@ int wxDbCreateDataSource(const char *driverName, const char *dsn, const char *de bool sysDSN, const char *defDir, wxWindow *parent) /* * !!!! ONLY FUNCTIONAL UNDER MSW with VC6 !!!! - * Very rudimentary creation of an ODBC data source. + * Very rudimentary creation of an ODBC data source. + * + * ODBC driver must be ODBC 3.0 compliant to use this function */ { int result = FALSE; -#ifdef __WXMSW__ +//!!!! ONLY FUNCTIONAL UNDER MSW with VC6 !!!! +#ifdef __VISUALC__ int dsnLocation; wxString setupStr; @@ -3060,7 +3516,7 @@ int wxDbCreateDataSource(const char *driverName, const char *dsn, const char *de dsnLocation = ODBC_ADD_DSN; // NOTE: The decimal 2 is an invalid character in all keyword pairs - // so that is why I used it, as wxString does not deal well with + // so that is why I used it, as wxString does not deal well with // embedded nulls in strings setupStr.sprintf("DSN=%s%cDescription=%s%cDefaultDir=%s%c",dsn,2,description,2,defDir,2); @@ -3078,40 +3534,40 @@ int wxDbCreateDataSource(const char *driverName, const char *dsn, const char *de result = SQLConfigDataSource((HWND)parent->GetHWND(), dsnLocation, driverName, setupStr.c_str()); - if (!result) + if ((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO)) { // check for errors caused by ConfigDSN based functions DWORD retcode = 0; WORD cb; - wxChar errMsg[500+1]; + wxChar errMsg[SQL_MAX_MESSAGE_LENGTH]; errMsg[0] = '\0'; - SQLInstallerError(1,&retcode,errMsg,500,&cb); + // This function is only supported in ODBC drivers v3.0 compliant and above + SQLInstallerError(1,&retcode,errMsg,SQL_MAX_MESSAGE_LENGTH-1,&cb); if (retcode) { - if (!silent) - { #ifdef DBDEBUG_CONSOLE // When run in console mode, use standard out to display errors. cout << errMsg << endl; - cout << "Press any key to continue..." << endl; + cout << wxT("Press any key to continue...") << endl; getchar(); #endif // DBDEBUG_CONSOLE #ifdef __WXDEBUG__ wxLogDebug(errMsg,wxT("ODBC DEBUG MESSAGE")); #endif // __WXDEBUG__ - } } } else result = TRUE; - -#else // __WXMSW__ +#else + // Using iODBC/unixODBC or some other compiler which does not support the APIs + // necessary to use this function, so this function is not supported #ifdef __WXDEBUG__ - wxLogDebug("wxDbCreateDataSource() not available except under MSW","DEBUG MESSAGE"); + wxLogDebug("wxDbCreateDataSource() not available except under VC++/MSW",wxT("ODBC DEBUG MESSAGE")); #endif -#endif // __WXMSW__ + result = FALSE; +#endif // __VISUALC__ return result;