Contents Up Previous Next

wxDb

A wxDb instance is a connection to an ODBC datasource which may be opened, closed, and re-opened an unlimited number of times. A database connection allows function to be performed directly on the datasource, as well as allowing access to any tables/views defined in the datasource to which the user has sufficient privileges.

See the database classes overview for an introduction to using the ODBC classes.

Include files

<wx/db.h>

Helper classes and data structures

The following classes and structs are defined in db.cpp/.h for use with the wxDb class.

Constants

NOTE: In a future release, all ODBC class constants will be prefaced with 'wx'.

    wxDB_PATH_MAX           Maximum path length allowed to be passed to
                            the ODBC driver to indicate where the data
                            file(s) are located.

    DB_MAX_COLUMN_NAME_LEN  Maximum supported length for the name of a
                            column

    DB_MAX_ERROR_HISTORY    Maximum number of error messages retained in
                            the queue before being overwritten by new
                            errors.

    DB_MAX_ERROR_MSG_LEN    Maximum supported length of an error message
                            returned by the ODBC classes

    DB_MAX_STATEMENT_LEN    Maximum supported length for a complete SQL
                            statement to be passed to the ODBC driver

    DB_MAX_TABLE_NAME_LEN   Maximum supported length for the name of a
                            table

    DB_MAX_WHERE_CLAUSE_LEN Maximum supported WHERE clause length that
                            can be passed to the ODBC driver

    DB_TYPE_NAME_LEN        Maximum length of the name of a column's
                            data type
Enumerated types
Associated non-class functions
wxDb::wxDb
wxDb::Catalog
wxDb::Close
wxDb::CommitTrans
wxDb::CreateView
wxDb::Dbms
wxDb::DispAllErrors
wxDb::DispNextError
wxDb::DropView
wxDb::EscapeSqlChars
wxDb::ExecSql
wxDb::FwdOnlyCursors
wxDb::GetCatalog
wxDb::GetColumnCount
wxDb::GetColumns
wxDb::GetData
wxDb::GetDatabaseName
wxDb::GetDatasourceName
wxDb::GetHDBC
wxDb::GetHENV
wxDb::GetHSTMT
wxDb::GetKeyFields
wxDb::GetNext
wxDb::GetNextError
wxDb::GetPassword
wxDb::GetTableCount
wxDb::GetUsername
wxDb::Grant
wxDb::IsFwdOnlyCursors
wxDb::IsOpen
wxDb::LogError
wxDb::ModifyColumn
wxDb::Open
wxDb::RollbackTrans
wxDb::SetDebugErrorMessages
wxDb::SetSqlLogging
wxDb::SQLColumnName
wxDb::SQLTableName
wxDb::TableExists
wxDb::TablePrivileges
wxDb::TranslateSqlState
wxDb::WriteSqlLog


Enumerated types

Enumerated types

enum wxDbSqlLogState

enum wxDBMS

These are the databases currently tested and working with the ODBC classes. A call to wxDb::Dbms will return one of these enumerated values listed below.

See the remarks in wxDb::Dbms for exceptions/issues with each of these database engines.

Public member variables

SWORD wxDb::cbErrorMsg

int wxDb::DB_STATUS

   DB_ERR_GENERAL_WARNING                     // SqlState = '01000'
   DB_ERR_DISCONNECT_ERROR                    // SqlState = '01002'
   DB_ERR_DATA_TRUNCATED                      // SqlState = '01004'
   DB_ERR_PRIV_NOT_REVOKED                    // SqlState = '01006'
   DB_ERR_INVALID_CONN_STR_ATTR               // SqlState = '01S00'
   DB_ERR_ERROR_IN_ROW                        // SqlState = '01S01'
   DB_ERR_OPTION_VALUE_CHANGED                // SqlState = '01S02'
   DB_ERR_NO_ROWS_UPD_OR_DEL                  // SqlState = '01S03'
   DB_ERR_MULTI_ROWS_UPD_OR_DEL               // SqlState = '01S04'
   DB_ERR_WRONG_NO_OF_PARAMS                  // SqlState = '07001'
   DB_ERR_DATA_TYPE_ATTR_VIOL                 // SqlState = '07006'
   DB_ERR_UNABLE_TO_CONNECT                   // SqlState = '08001'
   DB_ERR_CONNECTION_IN_USE                   // SqlState = '08002'
   DB_ERR_CONNECTION_NOT_OPEN                 // SqlState = '08003'
   DB_ERR_REJECTED_CONNECTION                 // SqlState = '08004'
   DB_ERR_CONN_FAIL_IN_TRANS                  // SqlState = '08007'
   DB_ERR_COMM_LINK_FAILURE                   // SqlState = '08S01'
   DB_ERR_INSERT_VALUE_LIST_MISMATCH          // SqlState = '21S01'
   DB_ERR_DERIVED_TABLE_MISMATCH              // SqlState = '21S02'
   DB_ERR_STRING_RIGHT_TRUNC                  // SqlState = '22001'
   DB_ERR_NUMERIC_VALUE_OUT_OF_RNG            // SqlState = '22003'
   DB_ERR_ERROR_IN_ASSIGNMENT                 // SqlState = '22005'
   DB_ERR_DATETIME_FLD_OVERFLOW               // SqlState = '22008'
   DB_ERR_DIVIDE_BY_ZERO                      // SqlState = '22012'
   DB_ERR_STR_DATA_LENGTH_MISMATCH            // SqlState = '22026'
   DB_ERR_INTEGRITY_CONSTRAINT_VIOL           // SqlState = '23000'
   DB_ERR_INVALID_CURSOR_STATE                // SqlState = '24000'
   DB_ERR_INVALID_TRANS_STATE                 // SqlState = '25000'
   DB_ERR_INVALID_AUTH_SPEC                   // SqlState = '28000'
   DB_ERR_INVALID_CURSOR_NAME                 // SqlState = '34000'
   DB_ERR_SYNTAX_ERROR_OR_ACCESS_VIOL         // SqlState = '37000'
   DB_ERR_DUPLICATE_CURSOR_NAME               // SqlState = '3C000'
   DB_ERR_SERIALIZATION_FAILURE               // SqlState = '40001'
   DB_ERR_SYNTAX_ERROR_OR_ACCESS_VIOL2        // SqlState = '42000'
   DB_ERR_OPERATION_ABORTED                   // SqlState = '70100'
   DB_ERR_UNSUPPORTED_FUNCTION                // SqlState = 'IM001'
   DB_ERR_NO_DATA_SOURCE                      // SqlState = 'IM002'
   DB_ERR_DRIVER_LOAD_ERROR                   // SqlState = 'IM003'
   DB_ERR_SQLALLOCENV_FAILED                  // SqlState = 'IM004'
   DB_ERR_SQLALLOCCONNECT_FAILED              // SqlState = 'IM005'
   DB_ERR_SQLSETCONNECTOPTION_FAILED          // SqlState = 'IM006'
   DB_ERR_NO_DATA_SOURCE_DLG_PROHIB           // SqlState = 'IM007'
   DB_ERR_DIALOG_FAILED                       // SqlState = 'IM008'
   DB_ERR_UNABLE_TO_LOAD_TRANSLATION_DLL      // SqlState = 'IM009'
   DB_ERR_DATA_SOURCE_NAME_TOO_LONG           // SqlState = 'IM010'
   DB_ERR_DRIVER_NAME_TOO_LONG                // SqlState = 'IM011'
   DB_ERR_DRIVER_KEYWORD_SYNTAX_ERROR         // SqlState = 'IM012'
   DB_ERR_TRACE_FILE_ERROR                    // SqlState = 'IM013'
   DB_ERR_TABLE_OR_VIEW_ALREADY_EXISTS        // SqlState = 'S0001'
   DB_ERR_TABLE_NOT_FOUND                     // SqlState = 'S0002'
   DB_ERR_INDEX_ALREADY_EXISTS                // SqlState = 'S0011'
   DB_ERR_INDEX_NOT_FOUND                     // SqlState = 'S0012'
   DB_ERR_COLUMN_ALREADY_EXISTS               // SqlState = 'S0021'
   DB_ERR_COLUMN_NOT_FOUND                    // SqlState = 'S0022'
   DB_ERR_NO_DEFAULT_FOR_COLUMN               // SqlState = 'S0023'
   DB_ERR_GENERAL_ERROR                       // SqlState = 'S1000'
   DB_ERR_MEMORY_ALLOCATION_FAILURE           // SqlState = 'S1001'
   DB_ERR_INVALID_COLUMN_NUMBER               // SqlState = 'S1002'
   DB_ERR_PROGRAM_TYPE_OUT_OF_RANGE           // SqlState = 'S1003'
   DB_ERR_SQL_DATA_TYPE_OUT_OF_RANGE          // SqlState = 'S1004'
   DB_ERR_OPERATION_CANCELLED                 // SqlState = 'S1008'
   DB_ERR_INVALID_ARGUMENT_VALUE              // SqlState = 'S1009'
   DB_ERR_FUNCTION_SEQUENCE_ERROR             // SqlState = 'S1010'
   DB_ERR_OPERATION_INVALID_AT_THIS_TIME      // SqlState = 'S1011'
   DB_ERR_INVALID_TRANS_OPERATION_CODE        // SqlState = 'S1012'
   DB_ERR_NO_CURSOR_NAME_AVAIL                // SqlState = 'S1015'
   DB_ERR_INVALID_STR_OR_BUF_LEN              // SqlState = 'S1090'
   DB_ERR_DESCRIPTOR_TYPE_OUT_OF_RANGE        // SqlState = 'S1091'
   DB_ERR_OPTION_TYPE_OUT_OF_RANGE            // SqlState = 'S1092'
   DB_ERR_INVALID_PARAM_NO                    // SqlState = 'S1093'
   DB_ERR_INVALID_SCALE_VALUE                 // SqlState = 'S1094'
   DB_ERR_FUNCTION_TYPE_OUT_OF_RANGE          // SqlState = 'S1095'
   DB_ERR_INF_TYPE_OUT_OF_RANGE               // SqlState = 'S1096'
   DB_ERR_COLUMN_TYPE_OUT_OF_RANGE            // SqlState = 'S1097'
   DB_ERR_SCOPE_TYPE_OUT_OF_RANGE             // SqlState = 'S1098'
   DB_ERR_NULLABLE_TYPE_OUT_OF_RANGE          // SqlState = 'S1099'
   DB_ERR_UNIQUENESS_OPTION_TYPE_OUT_OF_RANGE // SqlState = 'S1100'
   DB_ERR_ACCURACY_OPTION_TYPE_OUT_OF_RANGE   // SqlState = 'S1101'
   DB_ERR_DIRECTION_OPTION_OUT_OF_RANGE       // SqlState = 'S1103'
   DB_ERR_INVALID_PRECISION_VALUE             // SqlState = 'S1104'
   DB_ERR_INVALID_PARAM_TYPE                  // SqlState = 'S1105'
   DB_ERR_FETCH_TYPE_OUT_OF_RANGE             // SqlState = 'S1106'
   DB_ERR_ROW_VALUE_OUT_OF_RANGE              // SqlState = 'S1107'
   DB_ERR_CONCURRENCY_OPTION_OUT_OF_RANGE     // SqlState = 'S1108'
   DB_ERR_INVALID_CURSOR_POSITION             // SqlState = 'S1109'
   DB_ERR_INVALID_DRIVER_COMPLETION           // SqlState = 'S1110'
   DB_ERR_INVALID_BOOKMARK_VALUE              // SqlState = 'S1111'
   DB_ERR_DRIVER_NOT_CAPABLE                  // SqlState = 'S1C00'
   DB_ERR_TIMEOUT_EXPIRED                     // SqlState = 'S1T00'
struct wxDb::dbInf

   wxChar dbmsName[40]        - Name of the dbms product
   wxChar dbmsVer[64]         - Version # of the dbms product
   wxChar driverName[40]      - Driver name
   wxChar odbcVer[60]         - ODBC version of the driver
   wxChar drvMgrOdbcVer[60]   - ODBC version of the driver manager
   wxChar driverVer[60]       - Driver version
   wxChar serverName[80]      - Server Name, typically a connect string
   wxChar databaseName[128]   - Database filename
   wxChar outerJoins[2]       - Does datasource support outer joins
   wxChar procedureSupport[2] - Does datasource support stored
                                procedures
   UWORD  maxConnections      - Maximum # of connections datasource
                                supports
   UWORD  maxStmts            - Maximum # of HSTMTs per HDBC
   UWORD  apiConfLvl          - ODBC API conformance level
   UWORD  cliConfLvl          - Is datasource SAG compliant
   UWORD  sqlConfLvl          - SQL conformance level
   UWORD  cursorCommitBehavior   - How cursors are affected on db commit
   UWORD  cursorRollbackBehavior - How cursors are affected on db
                                   rollback
   UWORD  supportNotNullClause   - Does datasource support NOT NULL
                                   clause
   wxChar supportIEF[2]       - Integrity Enhancement Facility (Ref.
                                Integrity)
   UDWORD txnIsolation        - Transaction isolation level supported by
                                driver
   UDWORD txnIsolationOptions - Transaction isolation level options
                                available
   UDWORD fetchDirections     - Fetch directions supported
   UDWORD lockTypes           - Lock types supported in SQLSetPos
   UDWORD posOperations       - Position operations supported in
                                SQLSetPos
   UDWORD posStmts            - Position statements supported
   UDWORD scrollConcurrency   - Scrollable cursor concurrency options
                                supported
   UDWORD scrollOptions       - Scrollable cursor options supported
   UDWORD staticSensitivity   - Can additions/deletions/updates be
                                detected
   UWORD  txnCapable          - Indicates if datasource supports
                                transactions
   UDWORD loginTimeout        - Number seconds to wait for a login
                                request
wxChar wxDb::errorList[DB_MAX_ERROR_HISTORY][DB_MAX_ERROR_MSG_LEN]

wxChar wxDb::errorMsg[SQL_MAX_MESSAGE_LENGTH]

SDWORD wxDb::nativeError

wxChar wxDb::sqlState[20]

Remarks

Default cursor scrolling is defined by wxODBC_FWD_ONLY_CURSORS in setup.h when the wxWidgets library is built. This behavior can be overridden when an instance of a wxDb is created (see wxDb constructor). Default setting of this value true, as not all databases/drivers support both types of cursors.

See also

wxDbColFor, wxDbColInf, wxDbTable, wxDbTableInf, wxDbInf


Associated non-class functions

The following functions are used in conjunction with the wxDb class.

void wxDbCloseConnections()

Remarks

Closes all cached connections that have been made through use of the wxDbGetConnection function.

NOTE: These connections are closed regardless of whether they are in use or not. This function should only be called after the program has finished using the connections and all wxDbTable instances that use any of the connections have been closed.

This function performs a wxDb::CommitTrans on the connection before closing it to commit any changes that are still pending, as well as to avoid any function sequence errors upon closing each connection.

int wxDbConnectionsInUse()

Remarks

Returns a count of how many database connections are currently free ( not being used) that have been cached through use of the wxDbGetConnection function.

bool wxDbFreeConnection(wxDb *pDb)

Remarks

Searches the list of cached database connections connection for one matching the passed in wxDb instance. If found, that cached connection is freed.

Freeing a connection means that it is marked as available (free) in the cache of connections, so that a call to wxDbGetConnection is able to return a pointer to the wxDb instance for use. Freeing a connection does NOT close the connection, it only makes the connection available again.

wxDb * wxDbGetConnection(wxDbConnectInf *pDbConfig, bool FwdOnlyCursors=(bool)wxODBC_FWD_ONLY_CURSORS)

Remarks

This function is used to request a "new" wxDb instance for use by the program. The wxDb instance returned is also opened (see wxDb::Open).

This function (along with wxDbFreeConnection() and wxDbCloseConnection()) maintain a cache of wxDb instances for user/re-use by a program. When a program needs a wxDb instance, it may call this function to obtain a wxDb instance. If there is a wxDb instance in the cache that is currently unused that matches the connection requirements specified in 'pDbConfig' then that cached connection is marked as no longer being free, and a pointer to the wxDb instance is returned.

If there are no connections available in the cache that meet the requirements given in 'pDbConfig', then a new wxDb instance is created to connect to the datasource specified in 'pDbConfig' using the userID and password given in 'pDbConfig'.

NOTE: The caching routine also uses the wxDb::Open connection datatype copying code. If the call to wxDbGetConnection() requests a connection to a datasource, and there is not one available in the cache, a new connection is created. But when the connection is opened, instead of polling the datasource over again for its datatypes, if a connection to the same datasource (using the same userID/password) has already been done previously, the new connection skips querying the datasource for its datatypes, and uses the same datatypes determined previously by the other connection(s) for that same datasource. This cuts down greatly on network traffic, database load, and connection creation time.

When the program is done using a connection created through a call to wxDbGetConnection(), the program should call wxDbFreeConnection() to release the wxDb instance back to the cache. DO NOT DELETE THE wxDb INSTANCE! Deleting the wxDb instance returned can cause a crash/memory corruption later in the program when the cache is cleaned up.

When exiting the program, call wxDbCloseConnections() to close all the cached connections created by calls to wxDbGetConnection().

const wxChar * wxDbLogExtendedErrorMsg(const wxChar *userText, wxDb *pDb, wxChar *ErrFile, int ErrLine)

Writes a message to the wxLog window (stdout usually) when an internal error situation occurs.

bool wxDbSqlLog(wxDbSqlLogState state, const wxString &filename = SQL_LOG_FILENAME)

Remarks

This function sets the sql log state for all open wxDb objects

bool wxDbGetDataSource(HENV henv, wxChar *Dsn, SWORD DsnMax, wxChar *DsDesc, SWORD DsDescMax, UWORD direction = SQL_FETCH_NEXT)

Remarks

This routine queries the ODBC driver manager for a list of available datasources. Repeatedly call this function to obtain all the datasources available through the ODBC driver manager on the current workstation.

    wxArrayString strArray;

    while (wxDbGetDataSource(DbConnectInf.GetHenv(), Dsn, SQL_MAX_DSN_LENGTH+1, DsDesc, 255))
        strArray.Add(Dsn);
Members


wxDb::wxDb

wxDb()

Default constructor.

wxDb(const HENV &aHenv, bool FwdOnlyCursors=(bool)wxODBC_FWD_ONLY_CURSORS)

Constructor, used to create an ODBC connection to a datasource.

Parameters

aHenv

FwdOnlyCursors

Remarks

This is the constructor for the wxDb class. The wxDb object must be created and opened before any database activity can occur.

Example

   wxDbConnectInf ConnectInf;
   ....Set values for member variables of ConnectInf here

   wxDb sampleDB(ConnectInf.GetHenv());
   if (!sampleDB.Open(ConnectInf.GetDsn(), ConnectInf.GetUserID(),
	                   ConnectInf.GetPassword()))
   {
      // Error opening datasource
   }
See also

wxDbGetConnection


wxDb::Catalog

bool Catalog(wxChar * userID, const wxString &fileName = SQL_CATALOG_FILENAME)

Allows a data "dictionary" of the datasource to be created, dumping pertinent information about all data tables to which the user specified in userID has access.

Parameters

userID

fileName

Return value

Returns true if the catalog request was successful, or false if there was some reason that the catalog could not be generated.

Example

============== ============== ================ ========= =======
TABLE NAME     COLUMN NAME    DATA TYPE        PRECISION  LENGTH
============== ============== ================ ========= =======
EMPLOYEE       RECID          (0008)NUMBER            15       8
EMPLOYEE       USER_ID        (0012)VARCHAR2          13      13
EMPLOYEE       FULL_NAME      (0012)VARCHAR2          26      26
EMPLOYEE       PASSWORD       (0012)VARCHAR2          26      26
EMPLOYEE       START_DATE     (0011)DATE              19      16

wxDb::Close

void Close()

Closes the database connection.

Remarks

At the end of your program, when you have finished all of your database work, you must close the ODBC connection to the datasource. There are actually four steps involved in doing this as illustrated in the example.

Any wxDbTable instances which use this connection must be deleted before closing the database connection.

Example

   // Commit any open transactions on the datasource
   sampleDB.CommitTrans();

   // Delete any remaining wxDbTable objects allocated with new
   delete parts;

   // Close the wxDb connection when finished with it
   sampleDB.Close();

wxDb::CommitTrans

bool CommitTrans()

Permanently "commits" changes (insertions/deletions/updates) to the database.

Return value

Returns true if the commit was successful, or false if the commit failed.

Remarks

Transactions begin implicitly as soon as you make a change to the database with an insert/update/delete, or any other direct SQL command that performs one of these operations against the datasource. At any time thereafter, to save the changes to disk permanently, "commit" them by calling this function.

Calling this member function commits ALL open transactions on this ODBC connection. For example, if three different wxDbTable instances used the same connection to the datasource, committing changes made on one of those wxDbTable instances commits any pending transactions on all three wxDbTable instances.

Until a call to wxDb::CommitTrans() is made, no other user or cursor is able to see any changes made to the row(s) that have been inserted/modified/deleted.

Special Note : Cursors


It is important to understand that different database/ODBC driver combinations handle transactions differently. One thing in particular that you must pay attention to is cursors, in regard to transactions. Cursors are what allow you to scroll through records forward and backward and to manipulate records as you scroll through them. When you issue a query, a cursor is created behind the scenes. The cursor keeps track of the query and keeps track of the current record pointer. After you commit or rollback a transaction, the cursor may be closed automatically. This is database dependent, and with some databases this behavior can be controlled through management functions. This means you would need to requery the datasource before you can perform any additional work using this cursor. This is only necessary however if the datasource closes the cursor after a commit or rollback. Use the wxDbTable::IsCursorClosedOnCommit member function to determine the datasource's transaction behavior. Note, in many situations it is very inefficient to assume the cursor is closed and always requery. This could put a significant, unnecessary load on datasources that leave the cursors open after a transaction.


wxDb::CreateView

bool CreateView(const wxString & viewName, const wxString & colList, const wxString &pSqlStmt)

Creates a SQL VIEW of one or more tables in a single datasource. Note that this function will only work against databases which support views (currently only Oracle as of November 21 2000).

Parameters

viewName

colList

pSqlStmt

Remarks

A 'view' is a logical table that derives columns from one or more other tables or views. Once the view is created, it can be queried exactly like any other table in the database.

NOTE: Views are not available with all datasources. Oracle is one example of a datasource which does support views.

Example

   // Incomplete code sample
   db.CreateView("PARTS_SD1", "PN, PD, QTY",
                 "SELECT PART_NUM, PART_DESC, QTY_ON_HAND * 1.1 FROM PARTS \
                  WHERE STORAGE_DEVICE = 1");

   // PARTS_SD1 can now be queried just as if it were a data table.
   // e.g. SELECT PN, PD, QTY FROM PARTS_SD1

wxDb::Dbms

wxDBMS Dbms()

Remarks

The return value will be of the enumerated type wxDBMS. This enumerated type contains a list of all the currently tested and supported databases.

Additional databases may work with these classes, but the databases returned by this function have been tested and confirmed to work with these ODBC classes.

Possible values returned by this function can be viewed in the Enumerated types section of wxDb.

There are known issues with conformance to the ODBC standards with several datasources supported by the wxWidgets ODBC classes. Please see the overview for specific details on which datasource have which issues.

Return value

The return value will indicate which of the supported datasources is currently connected to by this connection. In the event that the datasource is not recognized, a value of 'dbmsUNIDENTIFIED' is returned.


wxDb::DispAllErrors

bool DispAllErrors(HENV aHenv, HDBC aHdbc = SQL_NULL_HDBC, HSTMT aHstmt = SQL_NULL_HSTMT)

Used to log all database errors that occurred as a result of an executed database command. This logging is automatic and also includes debug logging when compiled in debug mode via wxLogDebug. If logging is turned on via wxDb::SetSqlLogging, then an entry is also logged to the defined log file.

Parameters

aHenv

aHdbc

aHstmt

Remarks

This member function will log all of the ODBC error messages for the last ODBC function call that was made. This function is normally used internally within the ODBC class library, but can be used programmatically after calling ODBC functions directly (i.e. SQLFreeEnv()).

Return value

The function always returns false, so a call to this function can be made in the return statement of a code block in the event of a failure to perform an action (see the example below).

See also

wxDb::SetSqlLogging, wxDbSqlLog

Example

   if (SQLExecDirect(hstmt, (UCHAR FAR *) pSqlStmt, SQL_NTS) != SQL_SUCCESS)
      // Display all ODBC errors for this stmt
      return(db.DispAllErrors(db.henv, db.hdbc, hstmt));

wxDb::DispNextError

void DispNextError()

Remarks

This function is normally used internally within the ODBC class library. It could be used programmatically after calling ODBC functions directly. This function works in conjunction with wxDb::GetNextError when errors (or sometimes informational messages) returned from ODBC need to be analyzed rather than simply displaying them as an error. GetNextError() retrieves the next ODBC error from the ODBC error queue. The wxDb member variables "sqlState", "nativeError" and "errorMsg" could then be evaluated. To display the error retrieved, DispNextError() could then be called. The combination of GetNextError() and DispNextError() can be used to iteratively step through the errors returned from ODBC evaluating each one in context and displaying the ones you choose.

Example

   // Drop the table before attempting to create it
   sprintf(sqlStmt, "DROP TABLE %s", tableName);
   // Execute the drop table statement
   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.
      pDb->GetNextError(henv, hdbc, hstmt);
      if (wxStrcmp(pDb->sqlState, "S0002"))
      {
         pDb->DispNextError();   // Displayed error retrieved
         pDb->DispAllErrors(henv, hdbc, hstmt); // Display all other errors, if any
         pDb->RollbackTrans();   // Rollback the transaction
         CloseCursor();          // Close the cursor
         return(false);          // Return Failure
      }
   }

wxDb::DropView

bool DropView(const wxString &viewName)

Drops the data table view named in 'viewName'.

Parameters

viewName

Remarks

If the view does not exist, this function will return true. Note that views are not supported with all datasources.


wxDb::EscapeSqlChars

wxString EscapeSqlChars(const wxString& value)

This function is used internally by wxWidgets while building SQL statements. It has been provided to help users who wish to explicity construct SQL statements to be sent to the server. The function takes the value passed and returns it with any special characters escaped. Which characters are considered special depends on what type of datasource the object is connected to. For example, most database servers use a backslash as the escape character; if the value passed contains a backlash it will be replaced with a double backslash before it is passed to the server. This function can be used to avoid passing statements with syntax errors to the server as well as prevent SQL injection attacks.

Parameters

value


wxDb::ExecSql

bool ExecSql(const wxString &pSqlStmt)

bool ExecSql(const wxString &pSqlStmt, wxDbColInf **columns, short &numcols)

Allows a native SQL command to be executed directly against the datasource. In addition to being able to run any standard SQL command, use of this function allows a user to (potentially) utilize features specific to the datasource they are connected to that may not be available through ODBC. The ODBC driver will pass the specified command directly to the datasource.

To get column amount and column names or other information about returned columns, pass 'columns' and 'numcols' parameters to the function also.

Parameters

pSqlStmt

columns

numcols

Remarks

This member extends the wxDb class and allows you to build and execute ANY VALID SQL statement against the datasource. This allows you to extend the class library by being able to issue any SQL statement that the datasource is capable of processing.

See also

wxDb::GetData, wxDb::GetNext


wxDb::FwdOnlyCursors

bool IsFwdOnlyCursors()

Older form (pre-2.3/2.4 of wxWidgets) of the wxDb::IsFwdOnlyCursors. This method is provided for backward compatibility only. The method wxDb::IsFwdOnlyCursors should be used in place of this method.

wxDbInf * GetCatalog(const wxChar *userID)


wxDb::GetCatalog

wxDbInf * GetCatalog(const wxChar *userID)

Returns a wxDbInf pointer that points to the catalog (datasource) name, schema, number of tables accessible to the current user, and a wxDbTableInf pointer to all data pertaining to all tables in the users catalog.

Parameters

userID

         userID == NULL  ... UserID is ignored (DEFAULT)
         userID == ""    ... UserID set equal to 'this->uid'
         userID != ""    ... UserID set equal to 'userID'
Remarks

The returned catalog will only contain catalog entries for tables to which the user specified in 'userID' has sufficient privileges. If no user is specified (NULL passed in), a catalog pertaining to all tables in the datasource accessible to the connected user (permissions apply) via this connection will be returned.


wxDb::GetColumnCount

int GetColumnCount(const wxString &tableName, const wxChar *userID)

Parameters

tableName

userID

         userID == NULL  ... UserID is ignored (DEFAULT)
         userID == ""    ... UserID set equal to 'this->uid'
         userID != ""    ... UserID set equal to 'userID'
Return value

Returns a count of how many columns are in the specified table. If an error occurs retrieving the number of columns, this function will return a -1.


wxDb::GetColumns

wxDbColInf * GetColumns(const wxString &tableName, UWORD *numCols, const wxChar *userID=NULL)

wxDbColInf * GetColumns(wxChar *tableName[], const wxChar *userID)

Parameters

tableName

numCols tableName[] userID

         userID == NULL  ... UserID is ignored (DEFAULT)
         userID == ""    ... UserID set equal to 'this->uid'
         userID != ""    ... UserID set equal to 'userID'
Return value

This function returns a pointer to an array of wxDbColInf structures, allowing you to obtain information regarding the columns of the named table(s). If no columns were found, or an error occurred, this pointer will be NULL.

THE CALLING FUNCTION IS RESPONSIBLE FOR DELETING THE wxDbColInf MEMORY WHEN IT IS FINISHED WITH IT.


ALL column bindings associated with this wxDb instance are unbound by this function, including those used by any wxDbTable instances that use this wxDb instance. This function should use its own wxDb instance to avoid undesired unbinding of columns.

See also

wxDbColInf

Example

   wxChar *tableList[] = {"PARTS", 0};
   wxDbColInf *colInf = pDb->GetColumns(tableList);
   if (colInf)
   {
      // Use the column inf
      .......
      // Destroy the memory
      delete [] colInf;
   }

wxDb::GetData

bool GetData(UWORD colNumber, SWORD cType, PTR pData, SDWORD maxLen, SDWORD FAR * cbReturned )

Used to retrieve result set data without binding column values to memory variables (i.e. not using a wxDbTable instance to access table data).

Parameters

colNumber

cType pData maxLen cbReturned

See also

wxDb::GetNext, wxDb::ExecSql

Example

    SDWORD cb;
    ULONG reqQty;
    wxString sqlStmt;
    sqlStmt = "SELECT SUM(REQUIRED_QTY - PICKED_QTY) FROM ORDER_TABLE WHERE \
                 PART_RECID = 1450 AND REQUIRED_QTY > PICKED_QTY";

    // Perform the query
    if (!pDb->ExecSql(sqlStmt.c_str()))
    {
        // ERROR
        return(0);
    }

    // Request the first row of the result set
    if (!pDb->GetNext())
    {
        // ERROR
        return(0);
    }

    // Read column #1 of the row returned by the call to ::GetNext()
    // and return the value in 'reqQty'
    if (!pDb->GetData(1, SQL_C_ULONG, &reqQty, 0, &cb))
    {
        // ERROR
        return(0);
    }

    // Check for a NULL result
    if (cb == SQL_NULL_DATA)
        return(0);
Remarks

When requesting multiple columns to be returned from the result set (for example, the SQL query requested 3 columns be returned), the calls to this function must request the columns in ordinal sequence (1,2,3 or 1,3 or 2,3).


wxDb::GetDatabaseName

const wxChar * GetDatabaseName()

Returns the name of the database engine.


wxDb::GetDatasourceName

const wxString & GetDatasourceName()

Returns the ODBC datasource name.


wxDb::GetHDBC

HDBC GetHDBC()

Returns the ODBC handle to the database connection.


wxDb::GetHENV

HENV GetHENV()

Returns the ODBC environment handle.


wxDb::GetHSTMT

HSTMT GetHSTMT()

Returns the ODBC statement handle associated with this database connection.


wxDb::GetKeyFields

int GetKeyFields(const wxString &tableName, wxDbColInf *colInf, UWORD numColumns)

Used to determine which columns are members of primary or non-primary indexes on the specified table. If a column is a member of a foreign key for some other table, that information is detected also.

This function is primarily for use by the wxDb::GetColumns function, but may be called if desired from the client application.

Parameters

tableName

colInf numColumns

Return value

Currently always returns true.

See also

wxDbColInf, wxDb::GetColumns


wxDb::GetNext

bool GetNext()

Called after executing a query, this function requests the next row in the result set after the current position of the cursor.

See also

wxDb::ExecSql, wxDb::GetData


wxDb::GetNextError

bool GetNextError(HENV aHenv, HDBC aHdbc = SQL_NULL_HDBC, HSTMT aHstmt = SQL_NULL_HSTMT)

Parameters

aHenv

aHdbc AHstmt

Example

   if (SQLExecDirect(hstmt, (UCHAR FAR *) pSqlStmt, SQL_NTS) != SQL_SUCCESS)
   {
      return(db.GetNextError(db.henv, db.hdbc, hstmt));
   }
See also

wxDb::DispNextError, wxDb::DispAllErrors


wxDb::GetPassword

const wxString & GetPassword()

Returns the password used to establish this connection to the datasource.


wxDb::GetTableCount

int GetTableCount()

Returns the number of wxDbTable() instances currently using this datasource connection.


wxDb::GetUsername

const wxString & GetUsername()

Returns the user name (uid) used to establish this connection to the datasource.


wxDb::Grant

bool Grant(int privileges, const wxString &tableName, const wxString &userList = "PUBLIC")

Use this member function to GRANT privileges to users for accessing tables in the datasource.

Parameters

privileges

         DB_GRANT_SELECT  = 1
         DB_GRANT_INSERT  = 2
         DB_GRANT_UPDATE  = 4
         DB_GRANT_DELETE  = 8
         DB_GRANT_ALL     = DB_GRANT_SELECT | DB_GRANT_INSERT |
                            DB_GRANT_UPDATE | DB_GRANT_DELETE
tableName userList

Remarks

Some databases require user names to be specified in all capital letters (i.e. Oracle). This function does not automatically capitalize the user names passed in the comma-separated list. This is the responsibility of the calling routine.

The currently logged in user must have sufficient grantor privileges for this function to be able to successfully grant the indicated privileges.

Example

   db.Grant(DB_GRANT_SELECT | DB_GRANT_INSERT, "PARTS", "mary, sue");

wxDb::IsFwdOnlyCursors

bool IsFwdOnlyCursors()

This setting indicates whether this database connection was created as being capable of using only forward scrolling cursors.

This function does NOT indicate if the ODBC driver or datasource supports backward scrolling cursors. There is no standard way of detecting if the driver or datasource can support backward scrolling cursors.

If a wxDb instance was created as being capable of only forward scrolling cursors, then even if the datasource and ODBC driver support backward scrolling cursors, tables using this database connection would only be able to use forward scrolling cursors.

The default setting of whether a wxDb connection to a database allows forward-only or also backward scrolling cursors is defined in setup.h by the value of wxODBC_FWD_ONLY_CURSORS. This default setting can be overridden when the wxDb connection is initially created (see wxDb constructor and wxDbGetConnection).

Return value

Returns true if this datasource connection is defined as using only forward scrolling cursors, or false if the connection is defined as being allowed to use backward scrolling cursors and their associated functions (see note above).

Remarks

Added as of wxWidgets v2.4 release, this function is a renamed version of wxDb::FwdOnlyCursors() to match the normal wxWidgets naming conventions for class member functions.

This function is not available in versions prior to v2.4. You should use wxDb::FwdOnlyCursors for wxWidgets versions prior to 2.4.

See also

wxDb constructor, wxDbGetConnection


wxDb::IsOpen

bool IsOpen()

Indicates whether the database connection to the datasource is currently opened.

Remarks

This function may indicate that the database connection is open, even if the call to wxDb::Open may have failed to fully initialize the connection correctly. The connection to the database is open and can be used via the direct SQL commands, if this function returns true. Other functions which depend on the wxDb::Open to have completed correctly may not function as expected. The return result from wxDb::Open is the only way to know if complete initialization of this wxDb connection was successful or not. See wxDb::Open for more details on partial failures to open a connection instance.


wxDb::LogError

void LogError(const wxString &errMsg const wxString &SQLState="")

errMsg

SQLState

Remarks

Calling this function will enter a log message in the error list maintained for the database connection. This log message is free form and can be anything the programmer wants to enter in the error list.

If SQL logging is turned on, the call to this function will also log the text into the SQL log file.

See also

wxDb::WriteSqlLog


wxDb::ModifyColumn

void ModifyColumn(const wxString &tableName const wxString &ColumnName int dataType ULONG columnLength=0 const wxString &optionalParam="")

Used to change certain properties of a column such as the length, or whether a column allows NULLs or not.

tableName

columnName dataType columnLength optionalParam

Remarks

Cannot be used to modify the precision of a numeric column, therefore 'columnLength' is ignored unless the dataType is DB_DATA_TYPE_VARCHAR.

Some datasources do not allow certain properties of a column to be changed if any rows currently have data stored in that column. Those datasources that do allow columns to be changed with data in the rows many handle truncation and/or expansion in different ways. Please refer to the reference material for the datasource being used for behavioral descriptions.

Example

    ok = pDb->ModifyColumn("CONTACTS", "ADDRESS2",
                            DB_, colDefs[j].SzDataObj,
                            wxT("NOT NULL"));

wxDb::Open

bool Open(const wxString &Dsn, const wxString &Uid, const wxString &AuthStr, bool failOnDataTypeUnsupported)

bool Open(const wxString &inConnectStr, bool failOnDataTypeUnsupported)

bool Open(wxDbConnectInf *dbConnectInf, bool failOnDataTypeUnsupported)

bool Open(wxDb *copyDb)

Opens a connection to the datasource, sets certain behaviors of the datasource to confirm to the accepted behaviors (e.g. cursor position maintained on commits), and queries the datasource for its representations of the basic datatypes to determine the form in which the data going to/from columns in the data tables are to be handled.

The second form of this function, which accepts a "wxDb *" as a parameter, can be used to avoid the overhead (execution time, database load, network traffic) which are needed to determine the data types and representations of data that are necessary for cross-datasource support by these classes.

Normally the first form of the wxDb::Open() function will open the connection and then send a series of queries to the datasource asking it for its representation of data types, and all the features it supports. If one connection to the datasource has already been made previously, the information gathered when that connection was created can just be copied to any new connections to the same datasource by passing a pointer to the first connection in as a parameter to the wxDb::Open() function. Note that this new connection created from the first connections information will use the same Dsn/Uid/AuthStr as the first connection used.

Parameters

Dsn

Uid AuthStr failOnDataTypeUnsupporte dbConnectInf inConnectStr copyDb

Remarks

After a wxDb instance is created, it must then be opened. When opening a datasource, there must be three pieces of information passed. The data source name, user name (ID) and the password for the user. No database activity on the datasource can be performed until the connection is opened. This is normally done at program startup and the datasource remains open for the duration of the program/module run.

It is possible to have connections to multiple datasources open at the same time to support distributed database connections by having separate instances of wxDb objects that use either the same or different Dsn/Uid/AuthStr settings.

If this function returns a value of false, it does not necessarily mean that the connection to the datasource was not opened. It may mean that some portion of the initialization of the connection failed (such as a datatype not being able to be determined how the datasource represents it). To determine if the connection to the database failed, use the wxDb::IsOpen function after receiving a false result back from this function to determine if the connection was opened or not. If this function returns false, but wxDb::IsOpen returns true, then direct SQL commands may be passed to the database connection and can be successfully executed, but use of the datatypes (such as by a wxDbTable instance) that are normally determined during open will not be possible.


The Dsn, Uid, and AuthStr string pointers that are passed in are copied. NOT the strings themselves, only the pointers. The calling routine must maintain the memory for these three strings for the life of the wxDb instance.

Example

   wxDb sampleDB(DbConnectInf.GetHenv());
   if (!sampleDB.Open("Oracle 7.1 HP/UX", "gtasker", "myPassword"))
   {
      if (sampleDb.IsOpen())
      {
        // Connection is open, but the initialization of
        // datatypes and parameter settings failed
      }
      else
      {
        // Error opening datasource
      }
   }

wxDb::RollbackTrans

bool RollbackTrans()

Function to "undo" changes made to the database. After an insert/update/delete, the operation may be "undone" by issuing this command any time before a wxDb::CommitTrans is called on the database connection.

Remarks

Transactions begin implicitly as soon as you make a change to the database. The transaction continues until either a commit or rollback is executed. Calling wxDb::RollbackTrans() will result in ALL changes done using this database connection that have not already been committed to be "undone" back to the last commit/rollback that was successfully executed.


Calling this member function rolls back ALL open (uncommitted) transactions on this ODBC connection, including all wxDbTable instances that use this connection.

See also

wxDb::CommitTrans for a special note on cursors


wxDb::SetDebugErrorMessages

void SetDebugErrorMessages(bool state)

state

Remarks

Turns on/off debug error messages from the ODBC class library. When this function is passed true, errors are reported to the user/logged automatically in a text or pop-up dialog when an ODBC error occurs. When passed false, errors are silently handled.

When compiled in release mode (FINAL=1), this setting has no affect.

See also

wxDb constructor


wxDb::SetSqlLogging

bool SetSqlLogging(wxDbSqlLogState state, const wxString &filename = SQL_LOG_FILENAME, bool append = false)

Parameters

state

filename append

Remarks

When called with sqlLogON, all commands sent to the datasource engine are logged to the file specified by filename. Logging is done by embedded wxDb::WriteSqlLog calls in the database member functions, or may be manually logged by adding calls to wxDb::WriteSqlLog in your own source code.

When called with sqlLogOFF, the logging file is closed, and any calls to wxDb::WriteSqlLog are ignored.


wxDb::SQLColumnName

const wxString SQLColumnName(const wxChar * colName)

Returns the column name in a form ready for use in SQL statements. In most cases, the column name is returned verbatim. But some databases (e.g. MS Access, SQL Server, MSDE) allow for spaces in column names, which must be specially quoted. For example, if the datasource allows spaces in the column name, the returned string will have the correct enclosing marks around the name to allow it to be properly included in a SQL statement for the DBMS that is currently connected to with this connection.

Parameters

colName

See also

wxDb::SQLTableName


wxDb::SQLTableName

const wxString SQLTableName(const wxChar * tableName)

Returns the table name in a form ready for use in SQL statements. In most cases, the table name is returned verbatim. But some databases (e.g. MS Access, SQL Server, MSDE) allow for spaces in table names, which must be specially quoted. For example, if the datasource allows spaces in the table name, the returned string will have the correct enclosing marks around the name to allow it to be properly included in a SQL statement for the data source that is currently connected to with this connection.

Parameters

tableName

See also

wxDb::SQLColumnName


wxDb::TableExists

bool TableExists(const wxString &tableName, const wxChar *userID=NULL, const wxString &path="")

Checks the ODBC datasource for the existence of a table. If a userID is specified, then the table must be accessible by that user (user must have at least minimal privileges to the table).

Parameters

tableName

userID

         userID == NULL  ... UserID is ignored (DEFAULT)
         userID == ""    ... UserID set equal to 'this->uid'
         userID != ""    ... UserID set equal to 'userID'
Remarks

tableName may refer to a table, view, alias or synonym.

This function does not indicate whether or not the user has privileges to query or perform other functions on the table. Use the wxDb::TablePrivileges to determine if the user has sufficient privileges or not.

See also

wxDb::TablePrivileges


wxDb::TablePrivileges

bool TablePrivileges(const wxString &tableName, const wxString &priv, const wxChar *userID=NULL, const wxChar *schema=NULL, const wxString &path="")

Checks the ODBC datasource for the existence of a table. If a userID is specified, then the table must be accessible by that user (user must have at least minimal privileges to the table).

Parameters

tableName

priv

    SELECT      : The connected user is permitted to retrieve data for
                  one or more columns of the table.

    INSERT      : The connected user is permitted to insert new rows
                  containing data for one or more columns into the
                  table.

    UPDATE      : The connected user is permitted to update the data in
                  one or more columns of the table.

    DELETE      : The connected user is permitted to delete rows of
                  data from the table.

    REFERENCES  : Is the connected user permitted to refer to one or
                  more columns of the table within a constraint (for
                  example, a unique, referential, or table check
                  constraint).
userID

         userID == NULL  ... NOT ALLOWED!
         userID == ""    ... UserID set equal to 'this->uid'
         userID != ""    ... UserID set equal to 'userID'
schema

         schema == NULL  ... Any owner (DEFAULT)
         schema == ""    ... Owned by 'this->uid'
         schema != ""    ... Owned by userID specified in 'schema'
path

Remarks

The scope of privilege allowed to the connected user by a given table privilege is datasource dependent.

For example, the privilege UPDATE might allow the connected user to update all columns in a table on one datasource, but only those columns for which the grantor (the user that granted the connected user) has the UPDATE privilege on another datasource.

Looking up a user's privileges to a table can be time consuming depending on the datasource and ODBC driver. This time can be minimized by passing a schema as a parameter. With some datasources/drivers, the difference can be several seconds of time difference.


wxDb::TranslateSqlState

int TranslateSqlState(const wxString &SQLState)

Converts an ODBC sqlstate to an internal error code.

Parameters

SQLState

Return value

Returns the internal class DB_ERR code. See wxDb::DB_STATUS definition.


wxDb::WriteSqlLog

bool WriteSqlLog(const wxString &logMsg)

Parameters

logMsg

Remarks

Very useful debugging tool that may be turned on/off during run time (see (see wxDb::SetSqlLogging for details on turning logging on/off). The passed in string logMsg will be written to a log file if SQL logging is turned on.

Return value

If SQL logging is off when a call to WriteSqlLog() is made, or there is a failure to write the log message to the log file, the function returns false without performing the requested log, otherwise true is returned.

See also

wxDb::SetSqlLogging