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
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.
dbmsUNIDENTIFIED
dbmsORACLE
dbmsSYBASE_ASA // Adaptive Server Anywhere
dbmsSYBASE_ASE // Adaptive Server Enterprise
dbmsMS_SQL_SERVER
dbmsMY_SQL
dbmsPOSTGRES
dbmsACCESS
dbmsDBASE
dbmsINFORMIX
dbmsVIRTUOSO
dbmsDB2
dbmdINTERBASE
See the remarks in wxDb::Dbms for A><"ptions/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
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 cached 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.
wxStringList strList;
while (wxDbGetDataSource(DbConnectInf.GetHenv(), Dsn, SQL_MAX_DSN_LENGTH+1, DsDesc, 255))
strList.Add(Dsn);
Members
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
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
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();
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 A>ample, 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
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 A>actly 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_NO, 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
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.
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));
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"