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.

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.

· wxDbColFor (p. 32)

· wxDbColInf (p. 31)

· wxDbTableInf (p. 76)

· wxDbInf (p. 39)

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

sqlLogOFF, sqlLogON

enum wxDBMS

These are the databases currently tested and working with the ODBC classes. A call to wxDb::Dbms (p. 11) 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 (p. 11) for exceptions/issues with each of these database engines.

Public member variables

SWORD wxDb::cbErrorMsg

This member variable is populated as a result of calling wxDb::GetNextError (p. 20). Contains the count of bytes in the wxDb::errorMsg string.

int wxDb::DB_STATUS

The last ODBC error/status that occurred on this data connection. Possible codes are:

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

This structure is internal to the wxDb class and contains details of the ODBC datasource that the current instance of the wxDb is connected to in its members. When the datasource is opened, all of the information contained in the dbInf structure is queried from the datasource. This information is used almost exclusively within the ODBC class library. Where there may be a need for particular portions of this information outside of the class library, member functions (e.g. wxDbTable::IsCursorClosedOnCommit (p. 60)) have been added for ease of use.

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]

The last n ODBC errors that have occurred on this database connection.

wxChar wxDb::errorMsg[SQL_MAX_MESSAGE_LENGTH]

This member variable is populated as a result of calling wxDb::GetNextError (p. 20). It contains the ODBC error message text.

SDWORD wxDb::nativeError

Set by wxDb::DispAllErrors, wxDb::GetNextError, and wxDb::DispNextError. It contains the datasource-specific error code returned by the datasource to the ODBC driver. Used for reporting ODBC errors.

wxChar wxDb::sqlState[20]

Set by wxDb::TranslateSqlState(). Indicates the error state after a failed ODBC operation. Used for reporting ODBC errors.

Remarks

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

See also

wxDbColFor (p. 32), wxDbColInf (p. 31), wxDbTable (p. 40), wxDbTableInf (p. 76), wxDbInf (p. 39)

Associated non-class functions

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

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 (p. 24)).

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 (p. 24) 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().

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 (p. 5) 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.

void wxDbCloseConnections()

Remarks

Closes all cached connections that have been made through use of the wxDbGetConnection (p. 5) 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 (p. 10) 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 (p. 5) function.

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);

}

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

Environment handle used for this connection. See wxDConnectInf::AllocHenv (p. 35)

FwdOnlyCursors

Will cursors created for use with this datasource connection only allow forward scrolling cursors.

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 (p. 5),

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

Database user name to use in accessing the database. All tables to which this user has rights will be evaluated in the catalog.

fileName

OPTIONAL. Name of the text file to create and write the DB catalog to. Default is SQL_CATALOG_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 (p. 60) 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

The name of the view. e.g. PARTS_V

colList

OPTIONAL Pass in a comma delimited list of column names if you wish to explicitly name each column in the result set. If not desired, pass in an empty string and the column names from the associated table(s) will be used.

pSqlStmt

Pointer to the select statement portion of the CREATE VIEW statement. Must be a complete, valid SQL SELECT statement.

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_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

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 (p. 2) section of wxDb.

There are known issues with conformance to the ODBC standards with several datasources supported by the wxWindows 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 (p. Error! Bookmark not defined.). If logging is turned on via wxDb::SetSqlLogging (p. 26), then an entry is also logged to the defined log file.

Parameters

aHenv

Handle to the ODBC environment.

aHdbc

Handle to the ODBC connection. Pass this in if the ODBC function call that erred required a hdbc or hstmt argument.

aHstmt

Handle to the ODBC statement being executed against. Pass this in if the ODBC function call that erred out required a hstmt argument.

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 (p. 26), 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 (p. 20) 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

Name of the view to be dropped.

Remarks

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

wxDb::ExecSql

bool ExecSql(const wxString&pSqlStmt)

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.

Parameters

pSqlStmt

Pointer to the SQL statement to be executed.

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 (p. 17), wxDb::GetNext (p. 19)

wxDb::FwdOnlyCursors

bool IsFwdOnlyCursors()

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

wxDbInf * GetCatalog(const wxChar *userID)

wxDb::GetCatalog

wxDbInf * GetCatalog(const wxChar *userID)

Returns a wxDbInf (p. 39) 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

Owner/Schema of the table. Specify a userID when the datasource you are connected to allows multiple unique tables with the same name to be owned by different users. userID is evaluated as follows:

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

The table name you wish to obtain column information about.

userID

Name of the user that owns the table(s) (also referred to as schema). Required for some datasources for situations where there may be multiple tables with the same name in the datasource, but owned by different users. userID is evaluated in the following manner:

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

The table name you wish to obtain column information about.

numCols

Pointer to a UWORD which will hold a count of the number of columns returned by this function

tableName[]

An array of pointers to table names you wish to obtain column information about. The last element of this array must be a NULL string.

userID

Name of the user that owns the table(s) (also referred to as schema). Required for some datasources for situations where there may be multiple tables with the same name in the datasource, but owned by different users. userID is evaluated in the following manner:

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 (p. 31) 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 (p. 31)

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 colNo, 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

colNo

Ordinal number of the desired column in the result set to be returned.

cType

The C data type that is to be returned. See a partial list in wxDbTable::SetColDefs (p. 68)

pData

Memory buffer which will hold the data returned by the call to this function.

maxLen

Maximum size of the buffer 'pData' in characters. NOTE: Not UNICODE safe. If this is a numeric field, a value of 0 may be passed for this parameter, as the API knows the size of the expected return value.

cbReturned

Pointer to the buffer containing the length of the actual data returned. If this value comes back as SQL_NULL_DATA, then the wxDb::GetData (p. 17) call has failed.

See also

wxDb::GetNext (p. 19), wxDb::ExecSql (p. 14)

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 nocols)

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 (p. 16) function, but may be called if desired from the client application.

Parameters

tableName

Name of the table for which the columns will be evaluated as to their inclusion in any indexes.

colInf

Data structure containing the column definitions (obtained with wxDb::GetColumns (p. 16)). This function populates the PkCol, PkTableName, and FkTableName members of the colInf structure.

nocols

Number of columns defined in the instance of colInf.

Return value

Currently always returns TRUE.

See also

wxDbColInf (p. 31), wxDb::GetColumns (p. 16)

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 (p. 14), wxDb::GetData (p. 17)

wxDb::GetNextError

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

Parameters

aHenv

A handle to the ODBC environment.

aHdbc

OPTIONAL. A handle to the ODBC connection. Pass this in if the ODBC function call that erred out required a hdbc or hstmt argument.

AHstmt

OPTIONAL.A handle to the ODBC statement being executed against. Pass this in if the ODBC function call that erred out requires a hstmt argument.

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));

}

See also

wxDb::DispNextError (p. 13), wxDb::DispAllErrors (p. 12)

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

Use this argument to select which privileges you want to grant. Pass DB_GRANT_ALL to grant all privileges. To grant individual privileges pass one or more of the following OR'd together:

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

The name of the table you wish to grant privileges on.

userList

OPTIONAL. A comma delimited list of users to grant the privileges to. If this argument is not passed in, the privileges will be given to the general PUBLIC.

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 (p. 8) and wxDbGetConnection (p. 5)).

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 wxWindows v2.4 release, this function is a renamed version of wxDb::FwdOnlyCursors() to match the normal wxWindows naming conventions for class member functions.

This function is not available in versions prior to v2.4. You should use wxDb::FwdOnlyCursors (p. 14) for wxWindows versions prior to 2.4.

See also

wxDb constructor (p. 8), wxDbGetConnection (p. 5)

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 (p. 24) 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 (p. 24) to have completed correctly may not function as expected. The return result from wxDb::Open (p. 24) is the only way to know if complete initialization of this wxDb connection was successful or not. See wxDb::Open (p. 24) for more details on partial failures to open a connection instance.

wxDb::LogError

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

errMsg

Free-form text to display describing the error/text to be logged.

SQLState

OPTIONAL. Native SQL state error. Default is 0.

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 (p. 29)

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

Name of the table that the column to be modified is in.

columnName

Name of the column to be modified. NOTE: Name of column cannot be changed with this function.

dataType

Any one of DB_DATA_TYPE_VARCHAR, DB_DATA_TYPE_INTEGER, DB_DATA_TYPE_FLOAT, DB_DATA_TYPE_DATE.

columnLength

New size of the column. Valid only for DB_DATA_TYPE_VARCHAR dataType fields. Default is 0.

optionalParam

Default is "".

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 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

datasource name. The name of the ODBC datasource as assigned when the datasource is initially set up through the ODBC data source manager.

Uid

User ID. The name (ID) of the user you wish to connect as to the datasource. The user name (ID) determines what objects you have access to in the datasource and what datasource privileges you have. Privileges include being able to create new objects, update objects, delete objects and so on. Users and privileges are normally administered by the database administrator.

AuthStr

The password associated with the Uid.

copyDb

Already completely configured and opened datasource connection from which all Dsn, Uid, AuthStr, and data typing information is to be copied from for use by this datasource connection.

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 (p. 22) 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 (p. 22) 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 (p. 10) 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 (p. 10) for a special note on cursors

wxDb::SetDebugErrorMessages

void SetDebugErrorMessages(bool state)

state

Either TRUE (debug messages are logged) or FALSE (debug messages are not logged).

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 (p. 8)

wxDb::SetSqlLogging

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

Parameters

state

Either sqlLogOFF or sqlLogON (see enum wxDbSqlLogState (p. 32)). Turns logging of SQL commands sent to the datasource OFF or ON.

filename

OPTIONAL. Name of the file to which the log text is to be written. Default is SQL_LOG_FILENAME.

append

OPTIONAL. Whether the file is appended to or overwritten. Default is FALSE.

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 (p. 29) calls in the database member functions, or may be manually logged by adding calls to wxDb::WriteSqlLog (p. 29) in your own source code.

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

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

Name of the table to check for the existence of.

userID

Owner of the table (also referred to as schema). Specify a userID when the datasource you are connected to allows multiple unique tables with the same name to be owned by different users. userIDis evaluated as follows:

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 (p. 27) to determine if the user has sufficient privileges or not.

See also

wxDb::TablePrivileges (p. 27)

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

Name of the table on which to check privileges. tableName may refer to a table, view, alias or synonym.

priv

The table privilege being evaluated. May be one of the following (or a datasource specific privilege):

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

OPTIONAL. User for which to determine if the privilege specified to be checked is granted or not. Default is "". userID is evaluated as follows:

userID == NULL ... NOT ALLOWED!

userID == "" ... UserID set equal to 'this->uid'

userID != "" ... UserID set equal to 'userID'

schema

OPTIONAL. Owner of the table. Specify a userID when the datasource you are connected to allows multiple unique tables with the same name to be owned by different users. Specifying the table owner makes determination of the users privileges MUCH faster. Default is NULL. userID is evaluated as follows:

schema == NULL ... Any owner (DEFAULT)

schema == "" ... Owned by 'this->uid'

schema != "" ... Owned by userID specified in 'schema'

path

OPTIONAL. Path to the table. Default is "". Currently unused.

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

State to be converted.

Return value

Returns the internal class DB_ERR code. See wxDb::DB_STATUS (p. 1) definition.

wxDb::WriteSqlLog

bool WriteSqlLog(const wxString&logMsg)

Parameters

logMsg

Free form string to be written to the log file.

Remarks

Very useful debugging tool that may be turned on/off during run time (see (see wxDb::SetSqlLogging (p. 26) 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 (p. 26)

wxDbColDataPtr

Pointer to dynamic column definitions for use with a wxDbTable instance. Currently there are no member functions for this class.

void *PtrDataObj;

int SzDataObj;

SWORD SqlCtype;

wxDbColDef

This class is used to hold information about the columns bound to an instance of a wxDbTable object.

Each instance of this class describes one column in the wxDbTable object. When calling the wxDb constructor (p. 8), a parameter passed in indicates the number of columns that will be defined for the wxDbTable object. The constructor uses this information to allocate adequate memory for all of the column descriptions in your wxDbTable object. Private member wxDbTable::colDefs is a pointer to this chunk of memory maintained by the wxDbTable class (and can be retrieved using the wxDbTable::GetColDefs (p. 54) function). To access the nth column definition of your wxDbTable object, just reference wxDbColDefs element [n - 1].

Typically, wxDbTable::SetColDefs (p. 68) is used to populate an array of these data structures for the wxDbTable instance.

Currently there are no accessor functions for this class, so all members are public.

wxChar ColName[DB_MAX_COLUMN_NAME_LEN+1]; // Column Name

int DbDataType; - Logical Data Type;

e.g. DB_DATA_TYPE_INTEGER

SWORD SqlCtype; - C data type; e.g. SQL_C_LONG

void *PtrDataObj; - Address of the data object

int SzDataObj; - Size, in bytes, of the data object

bool KeyField; - Is column part of the PRIMARY KEY for the

table? -- Date fields should NOT be

KeyFields

bool Updateable; - Column is updateable?

bool InsertAllowed; - Column included in INSERT statements?

bool DerivedCol; - Column is a derived value?

SDWORD CbValue; - !!!Internal use only!!!

bool Null; - NOT FULLY IMPLEMENTED

Allows NULL values in Inserts and Updates

See also

wxDbTable::GetColDefs (p. 54), wxDb constructor (p. 8)

wxDbColDef::Initialize

Simply initializes all member variables to a cleared state. Called by the constructor automatically.

wxDbColInf

Used with the wxDb::GetColumns (p. 16) functions for obtaining all retrievable information about a column's definition.

wxChar catalog[128+1];

wxChar schema[128+1];

wxChar tableName[DB_MAX_TABLE_NAME_LEN+1];

wxChar colName[DB_MAX_COLUMN_NAME_LEN+1];

SWORD sqlDataType;

wxChar typeName[128+1];

SWORD columnSize;

SWORD bufferLength;

short decimalDigits;

short numPrecRadix;

short nullable;

wxChar remarks[254+1];

int dbDataType; // conversion of the 'sqlDataType'

// to the generic data type used by

// these classes

int PkCol; // Primary key column

0 = No

1 = First Key

2 = Second Key, etc...

wxChar PkTableName[DB_MAX_TABLE_NAME_LEN+1];

// Tables that use this PKey as a FKey

int FkCol; // Foreign key column

0 = No

1 = First Key

2 = Second Key, etc...

wxChar FkTableName[DB_MAX_TABLE_NAME_LEN+1];

// Foreign key table name

wxDbColFor *pColFor; // How should this column be formatted

The constructor for this class initializes all the values to zero, "", or NULL.

The destructor for this class takes care of deleting the pColFor member if it is non-NULL.

wxDbColInf::Initialize

Simply initializes all member variables to a cleared state. Called by the constructor automatically.

wxDbColFor

Beginning support for handling international formatting specifically on dates and floats.

wxString s_Field; // Formated String for Output

wxString s_Format[7]; // Formated Objects - TIMESTAMP has

the biggest (7)

wxString s_Amount[7]; // Formated Objects - amount of

things that can be formatted

int i_Amount[7]; // Formated Objects -

TT MM YYYY HH MM SS m

int i_Nation; // 0 = timestamp

1 = EU

2 = UK

3 = International

4 = US

int i_dbDataType; // conversion of the 'sqlDataType'

to the generic data type used by

these classes

SWORD i_sqlDataType;

The constructor for this class initializes all the values to zero or NULL.

The destructor does nothing at this time.

Only one function is provided with this class currently:

wxDbColFor::Format

int Format(int Nation, int dbDataType, SWORD sqlDataType, short columnSize, short decimalDigits)

Work in progress, and should be inter-related with wxLocale eventually.

wxDbColFor::Initialize

Simply initializes all member variables to a cleared state. Called by the constructor automatically.

wxDbConnectInf

This class is used for holding the data necessary for connecting to the ODBC datasource. That information includes: SQL environment handle, datasource name, user ID, password and default directory path (used with dBase). Other optional fields held in this class are and file type, both for future functions planned to be added for creating/manipulating datasource definitions.

wxDbConnectInf()

Default constructor.

wxDb(HENV henv, const wxString&dsn, const wxString&userID="", const wxString&password, const wxString&defaultDir="", const wxString&description="", const wxString&fileType="")

Constructor which allows initial settings of all the classes member variables.

See the special not below on the henv parameter for forcing this constructor to create a SQL environment handle automatically, rather than needing to pass one in to the function.

Parameters

henv

Environment handle used for this connection. See wxDConnectInf::AllocHenv (p. 35) for how to create an SQL environment handle. NOTE: Passing in a NULL for this parameter will inform the constructor that it should create its own SQL environment handle. If NULL is passed for this parameter, the constructor will call wxDConnectInf::AllocHenv (p. 35) internally. A flag is set internally also to indicate that the HENV was created by the constructor so that when the default class destructor is called, the destructor will call wxDConnectInf::FreeHenv (p. 35) to free the environment handle automatically.

dsn

Name of the datasource to be used in creating wxDb instances for creating connection(s) to a datasource.

userID

OPTIONAL Many datasources allow (or even require) use of a username to determine privileges that connecting user is allowed to have when accessing the datasource or the data tables. Default is "".

password

OPTIONAL Password to be associated with the user ID specified in 'userID'. Default is "".

defaultDir

OPTIONAL Used for datasources which require the path to where the data file is stored to be specified. dBase is one example of the type of datasource which requires this information. Default is "".

description

OPTIONAL FUTURE USE Default is "".

fileType

OPTIONAL FUTURE USE Default is "".

Remarks

It is strongly recommended that programs use the longer form of the constructor and allow the constructor to create the SQL environment handle automatically, and manage the destruction of the handle.

Example

wxDbConnectInf *DbConnectInf;

DbConnectInf = new wxDbConnectInf(0,"MY_DSN", "MY_USER",

"MY_PASSWORD");

....the rest of the program

delete DbConnectInf;

See also

wxDConnectInf::AllocHenv (p. 35), wxDConnectInf::FreeHenv (p. 35)

wxDbConnectInf::~wxDbConnectInf

~wxDbConnectInf()

Handles the default destruction of the instance of the class. If the long form of the wxDConnectInf (p. 33) was used, then this destructor also takes care of calling wxDConnectInf::FreeHenv (p. 35) to free the SQL environment handle.

wxDbConnectInf::AllocHenv

bool AllocHenv()

Allocates a SQL environment handle that will be used to interface with an ODBC datasource.

Remarks

This function can be automatically called by the long from of the wxDbConnectInf (p. 33) constructor.

wxDbConnectInf::FreeHenv

void FreeHenv()

Frees the SQL environment handle being managed by the instance of this class.

Remarks

If the SQL environment handle was created using the long form of the wxDbConnectInf (p. 33) constructor, then the flag indicating that the HENV should be destroyed when the classes destructor is called is reset to be FALSE, so that any future handles created using the wxDbConnectInf::AllocHenv (p. 35) function must be manually released with a call to this function.

wxDbConnectInf::Initialize

Simply initializes all member variables to a cleared state. Called by the constructor automatically.

wxDbConnectInf::GetAuthStr

const wxChar * GetAuthStr()

Accessor function to return the password assigned for this class instance that will be used with the user ID.

Synonymous with wxDbConnectInf::GetPassword (p. 37)

wxDbConnectInf::GetDefaultDir

const wxChar * GetDefaultDir()

Accessor function to return the default directory in which the datasource's data table is stored. This directory is only used for file based datasources like dBase. MS-Access does not require this to be set, as the path is set in the ODBC Administrator for MS-Access.

wxDbConnectInf::GetDescription

const wxChar * GetDescription()

Accessor function to return the description assigned for this class instance.

NOTE: Description is a FUTURE USE item and is unused currently.

wxDbConnectInf::GetDsn

const wxChar * GetDsn()

Accessor function to return the datasource name assigned for this class instance.

wxDbConnectInf::GetFileType

const wxChar * GetFileType()

Accessor function to return the filetype of the ODBC datasource assigned for this class instance.

NOTE: FileType is a FUTURE USE item and is unused currently.

wxDbConnectInf::GetHenv

const HENV GetHenv()

Accessor function to return the SQL environment handle being managed by this class instance.

wxDbConnectInf::GetPassword

const wxChar * GetPassword()

Accessor function to return the password assigned for this class instance that will be used with the user ID.

Synonymous with wxDbConnectInf::GetAuthStr (p. 36)

wxDbConnectInf::GetUid

const wxChar * GetUid()

Accessor function to return the user ID assigned for this class instance.

wxDbConnectInf::GetUserID

const wxChar * GetUserID()

Accessor function to return the user ID assigned for this class instance.

wxDbConnectInf::SetAuthStr

SetAuthStr(const wxString&authstr)

Accessor function to assign the password for this class instance that will be used with the user ID.

Synonymous with wxDbConnectInf::SetPassword (p. 38)

wxDbConnectInf::SetDefaultDir

SetDefaultDir(const wxString&defDir)

Accessor function to assign the default directory in which the datasource's data table is stored. This directory is only used for file based datasources like dBase. MS-Access does not require this to be set, as the path is set in the ODBC Administrator for MS-Access.

wxDbConnectInf::SetDescription

SetDescription(const wxString&desc)

Accessor function to assign the description assigned for this class instance.

NOTE: Description is a FUTURE USE item and is unused currently.

wxDbConnectInf::SetDsn

SetDsn(const wxString&dsn)

Accessor function to assign the datasource name for this class instance.

wxDbConnectInf::SetFileType

SetFileType(const wxString&)

Accessor function to return the filetype of the ODBC datasource assigned for this class instance.

NOTE: FileType is a FUTURE USE item and is unused currently.

wxDbConnectInf::SetHenv

void SetHenv(const HENV henv)

Accessor function to set the SQL environment handle for this class instance.

wxDbConnectInf::SetPassword

SetPassword(const wxString&password)

Accessor function to assign the password for this class instance that will be used with the user ID.

Synonymous with wxDbConnectInf::SetAuthStr (p. 37)

wxDbConnectInf::SetUid

SetUid(const wxString&uid)

Accessor function to set the user ID for this class instance.

wxDbConnectInf::SetUserID

SetUserID(const wxString&userID)

Accessor function to assign the user ID for this class instance.

wxDbIdxDef

Used in creation of non-primary indexes. Currently there are no member functions for this class.

wxChar ColName[DB_MAX_COLUMN_NAME_LEN+1] // Name of column

bool Ascending // Is index maintained in ASCENDING sequence?

There are no constructors/destructors as of this time, and no member functions.

wxDbInf

Contains information regarding the database connection (datasource name, number of tables, etc). A pointer to a wxDbTableInf is included in this class so a program can create a wxDbTableInf array instance to maintain all information about all tables in the datasource to have all the datasource's information in one memory structure.

Primarily, this class is used internally by the wxWindows ODBC classes.

wxChar catalog[128+1];

wxChar schema[128+1]; // typically means owner of table(s)

int numTables; // How many tables does this

datasource have

wxDbTableInf *pTableInf; // Equals a new

wxDbTableInf[numTables];

The constructor for this class initializes all the values to zero, "", or NULL.

The destructor for this class takes care of deleting the pTableInf member if it is non-NULL.

wxDbInf::Initialize

Simply initializes all member variables to a cleared state. Called by the constructor automatically.

wxDbTable

A wxDbTable instance provides re-usable access to rows of data in a table contained within the associated ODBC datasource

Include files

<wx/dbtable.h>
<wx/db.h>

Helper classes and data structures

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

· wxDbColDef (p. 30)

· wxDbColDataPtr (p. 30)

· wxDbIdxDef (p. 39)

Constants

wxDB_DEFAULT_CURSOR Primary cursor normally used for cursor based

operations.

wxDB_QUERY_ONLY Used to indicate whether a table that is opened

is for query only, or if insert/update/deletes

will be performed on the table. Less overhead

(cursors and memory) are allocated for query

only tables, plus read access times are faster

with some datasources.

wxDB_ROWID_LEN [Oracle only] - Used when CanUpdateByRowID()

is true. Optimizes updates so they are faster

by updating on the Oracle-specific ROWID column

rather than some other index.

wxDB_DISABLE_VIEW Use to indicate when a database view should not

be if a table is normally set up to use a view.

[Currently unsupported.]

wxDbTable::wxDbTable

wxDbTable(wxDb *pwxDb, const wxString&tblName, const UWORD numColumns, const wxString&qryTblName = "", bool qryOnly = !wxDB_QUERY_ONLY, const wxString&tblPath = "")

Default constructor.

Parameters

pwxDb

Pointer to the wxDb instance to be used by this wxDbTable instance.

tblName

The name of the table in the RDBMS.

numColumns

The number of columns in the table. (Do NOT include the ROWID column in the count if using Oracle).

qryTblName

OPTIONAL. The name of the table or view to base your queries on. This argument allows you to specify a table/view other than the base table for this object to base your queries on. This allows you to query on a view for example, but all of the INSERT, UPDATE and DELETES will still be performed on the base table for this wxDbTable object. Basing your queries on a view can provide a substantial performance increase in cases where your queries involve many tables with multiple joins. Default is "".

qryOnly

OPTIONAL. Indicates whether the table will be accessible for query purposes only, or should the table create the necessary cursors to be able to insert, update, and delete data from the table. Default is !wxDB_QUERY_ONLY.

tblPath

OPTIONAL. Some datasources (such as dBase) require a path to where the table is stored on the system. Default is "".

wxDbTable::wxDbTable

virtual ~wxDbTable()

Virtual default destructor.

wxDbTable::BuildDeleteStmt

void BuildDeleteStmt(wxString&pSqlStmt, int typeOfDel, const wxString&pWhereClause="")

Constructs the full SQL statement that can be used to delete all rows matching the criteria in the pWhereClause.

Parameters

pSqlStmt

Pointer to buffer for the SQL statement retrieved. To be sure you have adequate space allocated for the SQL statement, allocate DB_MAX_STATEMENT_LEN bytes.

typeOfDel

The type of delete statement being performed. Can be one of three values: DB_DEL_KEYFIELDS, DB_DEL_WHERE or DB_DEL_MATCHING

pWhereClause

OPTIONAL. If the typeOfDel is DB_DEL_WHERE, then you must also pass in a SQL WHERE clause in this argument. Default is "".

Remarks

This member function constructs a SQL DELETE statement. This can be used for debugging purposes if you are having problems executing your SQL statement.

WHERE and FROM clauses specified using wxDbTable::SetWhereClause (p. 73) and wxDbTable::SetFromClause (p. 70) are ignored by this function.

wxDbTable::BuildSelectStmt

void BuildSelectStmt(wxString&pSqlStmt, int typeOfSelect, bool distinct)

Constructs the full SQL statement that can be used to select all rows matching the criteria in the pWhereClause. This function is called internally in the wxDbTable class whenever the function wxDbTable::Query (p. 62) is called.

NOTE: Only the columns specified in wxDbTable::SetColDefs (p. 68) statements are included in the list of columns returned by the SQL statement created by a call to this function.

Parameters

pSqlStmt

Pointer to storage for the SQL statement retrieved. To be sure you have adequate space allocated for the SQL statement, allocate DB_MAX_STATEMENT_LEN bytes.

typeOfSelect

The type of select statement being performed. Can be one of four values: DB_SELECT_KEYFIELDS, DB_SELECT_WHERE, DB_SELECT_MATCHING or DB_SELECT_STATEMENT.

distinct

Whether to select distinct records only.

Remarks

This member function constructs a SQL SELECT statement. This can be used for debugging purposes if you are having problems executing your SQL statement.

WHERE and FROM clauses specified using wxDbTable::SetWhereClause (p. 73) and wxDbTable::SetFromClause (p. 70) are ignored by this function.

wxDbTable::BuildUpdateStmt

void BuildUpdateStmt(wxString&pSqlStmt, int typeOfUpd, const wxString&pWhereClause="")

Constructs the full SQL statement that can be used to update all rows matching the criteria in the pWhereClause.

If typeOfUpd is DB_UPD_KEYFIELDS, then the current values in the bound columns are used to determine which row(s) in the table are to be updated. The exception to this is when a datasource supports ROW IDs (Oracle). The ROW ID column is used for efficiency purposes when available.

NOTE: Only the columns specified in wxDbTable::SetColDefs (p. 68) statements are included in the list of columns updated by the SQL statement created by a call to this function. Any column definitions that were defined as being non-updateable will be excluded from the SQL UPDATE statement created by this function.

Parameters

pSqlStmt

Pointer to storage for the SQL statement retrieved. To be sure you have adequate space allocated for the SQL statement, allocate DB_MAX_STATEMENT_LEN bytes.

typeOfUpd

The type of update statement being performed. Can be one of two values: DB_UPD_KEYFIELDS or DB_UPD_WHERE.

pWhereClause

OPTIONAL. If the typeOfUpd is DB_UPD_WHERE, then you must also pass in a SQL WHERE clause in this argument. Default is "".

Remarks

This member function allows you to see what the SQL UPDATE statement looks like that the ODBC class library builds. This can be used for debugging purposes if you are having problems executing your SQL statement.

WHERE and FROM clauses specified using wxDbTable::SetWhereClause (p. 73) and wxDbTable::SetFromClause (p. 70) are ignored by this function.

wxDbTable::BuildWhereStmt

void BuildSelectStmt(wxString&pWhereClause, int typeOfWhere, const wxString&qualTableName="", bool useLikeComparison=FALSE)

Constructs the portion of a SQL statement which would follow the word 'WHERE' in a SQL statement to be passed to the datasource. The returned string does NOT include the word 'WHERE'.

Parameters

pWhereClause

Pointer to storage for the SQL statement retrieved. To be sure you have adequate space allocated for the SQL statement, allocate DB_MAX_STATEMENT_LEN bytes.

typeOfWhere

The type of where clause to generate. Can be one of two values: DB_WHERE_KEYFIELDS or DB_WHERE_MATCHING.

qualTableName

OPTIONAL. Prepended to all base table column names. For use when a FROM clause has been specified with the wxDbTable::SetFromClause (p. 70), to clarify which table a column name reference belongs to. Default is "".

useLikeComparison

OPTIONAL. Should the constructed WHERE clause utilize the LIKE comparison operator. If FALSE, then the '=' operator is used. Default is FALSE.

Remarks

This member function allows you to see what the SQL WHERE clause looks like that the ODBC class library builds. This can be used for debugging purposes if you are having problems executing your own SQL statements.

If using 'typeOfWhere' set to DB_WHERE_MATCHING, any bound columns currently containing a NULL value are not included in the WHERE clause's list of columns to use in the comparison.

wxDbTable::CanSelectForUpdate

bool CanSelectForUpdate()

Use this function to determine if the datasource supports SELECT ... FOR UPDATE. When the keywords "FOR UPDATE" are included as part of your SQL SELECT statement, all records retrieved (not just queried, but actually retrieved using wxDbTable::GetNext (p. 57), etc) from the result set are locked.

Remarks

Not all datasources support the "FOR UPDATE" clause, so you must use this member function to determine if the datasource currently connected to supports this behavior or not before trying to select using "FOR UPDATE".

If the wxDbTable instance was created with the parameter wxDB_QUERY_ONLY, then this function will return FALSE. For all known databases which do not support the FOR UPDATE clause, this function will return FALSE also.

wxDbTable::CanUpdateByROWID

bool CanUpdateByROWID()

CURRENTLY ONLY POSSIBLE IF USING ORACLE.

--- CURRENTLY DISABLED FOR *ALL* DATASOURCES --- NOV 1 2000 - gt

Every Oracle table has a hidden column named ROWID. This is a pointer to the physical location of the record in the datasource and allows for very fast updates and deletes. The key is to retrieve this ROWID during your query so it is available during an update or delete operation.

Use of the ROWID feature is always handled by the class library except in the case of wxDbTable::QueryBySqlStmt (p. 64). Since you are passing in the SQL SELECT statement, it is up to you to include the ROWID column in your query. If you do not, the application will still work, but may not be as optimized. The ROWID is always the last column in the column list in your SQL SELECT statement. The ROWID is not a column in the normal sense and should not be considered part of the column definitions for the wxDbTable object.

Remarks

The decision to include the ROWID in your SQL SELECT statement must be deferred until runtime since it depends on whether you are connected to an Oracle datasource or not.

Example

// Incomplete code sample

wxDbTable parts;

.....

if (parts.CanUpdByROWID())

{

// Note the ROWID column must always be the last column selected

sqlStmt = "SELECT PART_NO, PART_DESC, ROWID" FROM PARTS";

}

else

sqlStmt = "SELECT PART_NO, PART_DESC FROM PARTS";

wxDbTable::ClearMemberVar

void ClearMemberVar(UWORD colNo, bool setToNull=FALSE)

Same as wxDbTable::ClearMemberVars (p. 46) except that this function clears only the specified column of its values, and optionally sets the column to be a NULL column.

colNo

Column number that is to be cleared. This number (between 0 and (noCols-1)) is the index of the column definition created using the wxDbTable::SetColDefs (p. 68) function.

setToNull

OPTIONAL. Indicates whether the column should be flagged as being a NULL value stored in the bound memory variable. If TRUE, then any value stored in the bound member variable is cleared. Default is FALSE.

wxDbTable::ClearMemberVars

void ClearMemberVars(bool setToNull=FALSE)

Initializes all bound columns of the wxDbTable instance to zero. In the case of a string, zero is copied to the first byte of the string.

setToNull

OPTIONAL. Indicates whether all columns should be flagged as having a NULL value stored in the bound memory variable. If TRUE, then any value stored in the bound member variable is cleared. Default is FALSE.

Remarks

This is useful before calling functions such as wxDbTable::QueryMatching (p. 65) or wxDbTable::DeleteMatching (p. 51) since these functions build their WHERE clauses from non-zero columns. To call either wxDbTable::QueryMatching (p. 65) or wxDbTable::DeleteMatching (p. 51) use this sequence:

1) ClearMemberVars()

2) Assign columns values you wish to match on

3) Call wxDbTable::QueryMatching() or wxDbTable::DeleteMatching()

wxDbTable::CloseCursor

bool CloseCursor(HSTMTcursor)

Closes the specified cursor associated with the wxDbTable object.

Parameters

cursor

The cursor to be closed.

Remarks

Typically handled internally by the ODBC class library, but may be used by the programmer if desired.

DO NOT CLOSE THE wxDB_DEFAULT_CURSOR!

wxDbTable::Count

ULONG Count(const wxString&args="*")

Returns the number of records which would be in the result set using the current query parameters specified in the WHERE and FROM clauses.

Parameters

args

OPTIONAL. This argument allows the use of the DISTINCT keyword against a column name to cause the returned count to only indicate the number of rows in the result set that have a unique value in the specified column. An example is shown below. Default is "*", meaning a count of the total number of rows matching is returned, regardless of uniqueness.

Remarks

This function can be called before or after an actual query to obtain the count of records in the result set. Count() uses its own cursor, so result set cursor positioning is not affected by calls to Count().

WHERE and FROM clauses specified using wxDbTable::SetWhereClause (p. 73) and wxDbTable::SetFromClause (p. 70) ARE used by this function.

Example

USERS TABLE

FIRST_NAME LAST_NAME

----------- ----------

John Doe

Richard Smith

Michael Jones

John Carpenter

// Incomplete code sample

wxDbTable users;

.....

users.SetWhereClause("");

// This Count() will return 4, as there are four users listed above

// that match the query parameters

totalNumberOfUsers = users.Count();

// This Count() will return 3, as there are 3 unique first names

// in the table above - John, Richard, Michael.

totalNumberOfUniqueFirstNames = users.Count("DISTINCT FIRST_NAME");

wxDbTable::CreateIndex

bool CreateIndex(const wxString&idxName, bool unique, UWORD noIdxCols, wxDbIdxDef *pIdxDefs, bool attemptDrop=TRUE)

This member function allows you to create secondary (non primary) indexes on your tables. You first create your table, normally specifying a primary index, and then create any secondary indexes on the table. Indexes in relational model are not required. You do not need indexes to look up records in a table or to join two tables together. In the relational model, indexes, if available, provide a quicker means to look up data in a table. To enjoy the performance benefits of indexes, the indexes must be defined on the appropriate columns and your SQL code must be written in such a way as to take advantage of those indexes.

Parameters

idxName

Name of the Index. Name must be unique within the table space of the datasource.

unique

Indicates if this index is unique.

noIdxCols

Number of columns in the index.

pIdxDefs

A pointer to an array wxDbIdxDef structures.

attemptDrop

OPTIONAL. Indicates if the function should try to execute a wxDbTable::DropIndex (p. 53) on the index name provided before trying to create the index name. Default is TRUE.

Remarks

The first parameter, index name, must be unique and should be given a meaningful name. Common practice is to include the table name as a prefix in the index name (e.g. For table PARTS, you might want to call your index PARTS_IDX1). This will allow you to easily view all of the indexes defined for a given table grouped together alphabetically.

The second parameter indicates if the index is unique or not. Uniqueness is enforced at the RDBMS level preventing rows which would have duplicate indexes from being inserted into the table when violating a unique index's uniqueness.

In the third parameter, specify how many columns are in your index. This number must match the number of columns defined in the 'pIdxDefs' parameter.

The fourth parameter specifies which columns make up the index using the wxDbIdxDef structure. For each column in the index, you must specify two things, the column name and the sort order (ascending / descending). See the example below to see how to build and pass in the wxDbIdxDef structure.

The fifth parameter is provided to handle the differences in datasources as to whether they will automatically overwrite existing indexes with the same name or not. Some datasources require that the existing index must be dropped first, so this is the default behavior.

Some datasources (MySQL, and possibly others) require columns which are to be part of an index to be defined as NOT NULL. When this function is called, if a column is not defined to be NOT NULL, a call to this function will modify the column definition to change any columns included in the index to be NOT NULL. In this situation, if a NULL value already exists in one of the columns that is being modified, creation of the index will fail.

Postgres is unable to handle index definitions which specify whether the index is ascending or descending, and defaults to the system default when the index is created.

It is not necessary to call wxDb::CommitTrans (p. 10) after executing this function.

Example

// Create a secondary index on the PARTS table

wxDbIdxDef idxDef[2]; // 2 columns make up the index

wxStrcpy(idxDef[0].ColName, "PART_DESC"); // Column 1

idxDef[0].Ascending = TRUE;

wxStrcpy(idxDef[1].ColName, "SERIAL_NO"); // Column 2

idxDef[1].Ascending = FALSE;

// Create a name for the index based on the table's name

wxString indexName;

indexName.Printf("%s_IDX1",parts->GetTableName());

parts->CreateIndex(indexName, TRUE, 2, idxDef);

wxDbTable::CreateTable

bool CreateTable(bool attemptDrop=TRUE)

Creates a table based on the definitions previously defined for this wxDbTable instance.

Parameters

attemptDrop

OPTIONAL. Indicates whether the driver should attempt to drop the table before trying to create it. Some datasources will not allow creation of a table if the table already exists in the table space being used. Default is TRUE.

Remarks

This function creates the table and primary index (if any) in the table space associated with the connected datasource. The owner of these objects will be the user id that was given when wxDb::Open (p. 24) was called. The objects will be created in the default schema/table space for that user.

In your derived wxDbTable object constructor, the columns and primary index of the table are described through the wxDbColDef (p. 30) structure. wxDbTable::CreateTable (p. 50) uses this information to create the table and to add the primary index. See wxDbTable (p. 40) ctor and wxDbColDef description for additional information on describing the columns of the table.

It is not necessary to call wxDb::CommitTrans (p. 10) after executing this function.

wxDbTable::DB_STATUS

bool DB_STATUS()

Accessor function that returns the wxDb private member variable DB_STATUS for the database connection used by this instance of wxDbTable.

wxDbTable::Delete

bool Delete()

Deletes the row from the table indicated by the current cursor.

Remarks

Use wxDbTable::GetFirst (p. 55), wxDbTable::GetLast (p. 56), wxDbTable::GetNext (p. 57) orwxDbTable::GetPrev (p. 57) to position the cursor to a valid record. Once positioned on a record, call this function to delete the row from the table.

A wxDb::CommitTrans (p. 10) or wxDb::RollbackTrans (p. 25) must be called after use of this function to commit or rollback the deletion.

NOTE: Most datasources have a limited size "rollback" segment. This means that it is only possible to insert/update/delete a finite number of rows without performing a wxDb::CommitTrans (p. 10) or wxDb::RollbackTrans (p. 25). Size of the rollback segment varies from database to database, and is user configurable in most databases. Therefore it is usually best to try to perform a commit or rollback at relatively small intervals when processing a larger number of actions that insert/update/delete rows in a table.

wxDbTable::DeleteCursor

bool DeleteCursor(HSTMT *hstmtDel)

Allows a program to delete a cursor.

Parameters

hstmtDel

Handle of the cursor to delete.

Remarks

For default cursors associated with the instance of wxDbTable, it is not necessary to specifically delete the cursors. This is automatically done in the wxDbTable destructor.

NOTE: If the cursor could not be deleted for some reason, an error is logged indicating the reason. Even if the cursor could not be deleted, the HSTMT that is passed in is deleted, and the pointer is set to NULL.

DO NOT DELETE THE wxDB_DEFAULT_CURSOR!

wxDbTable::DeleteMatching

bool DeleteMatching()

This member function allows you to delete records from your wxDbTable object by specifying the data in the columns to match on.

Remarks

To delete all users with a first name of "JOHN", do the following:

1) Clear all "columns" using wxDbTable::ClearMemberVars().

2) Set the FIRST_NAME column equal to "JOHN".

3) Call wxDbTable::DeleteMatching().

The WHERE clause is built by the ODBC class library based on all non-NULL columns. This allows deletion of records by matching on any column(s) in your wxDbTable instance, without having to write the SQL WHERE clause.

A wxDb::CommitTrans (p. 10) or wxDb::RollbackTrans (p. 25) must be called after use of this function to commit or rollback the deletion.

NOTE: Row(s) should be locked before deleting them to make sure they are not already in use. This can be achieved by calling wxDbTable::QueryMatching (p. 65), and then retrieving the records, locking each as you go (assuming FOR UPDATE is allowed on the datasource). After the row(s) have been successfully locked, call this function.

NOTE: Most datasources have a limited "rollback" segment. This means that it is only possible to insert/update/delete a finite number of rows without performing a wxDb::CommitTrans (p. 10) or wxDb::RollbackTrans (p. 25). Size of the rollback segment varies from database to database, and is user configurable in most databases. Therefore it is usually best to try to perform a commit or rollback at relatively small intervals when processing a larger number of actions that insert/update/delete rows in a table.

Example

// Incomplete code sample to delete all users with a first name

// of "JOHN"

users.ClearMemberVars();

wxStrcpy(users.FirstName,"JOHN");

users.DeleteMatching();

wxDbTable::DeleteWhere

bool DeleteWhere(const wxString&pWhereClause)

Deletes all rows from the table which match the criteria specified in the WHERE clause that is passed in.

Parameters

pWhereClause

SQL WHERE clause. This WHERE clause determines which records will be deleted from the table interfaced through the wxDbTable instance. The WHERE clause passed in must be compliant with the SQL 92 grammar. Do not include the keyword 'WHERE'

Remarks

This is the most powerful form of the wxDbTable delete functions. This function gives access to the full power of SQL. This function can be used to delete records by passing a valid SQL WHERE clause. Sophisticated deletions can be performed based on multiple criteria using the full functionality of the SQL language.

A wxDb::CommitTrans (p. 10) must be called after use of this function to commit the deletions.

Note: This function is limited to deleting records from the table associated with this wxDbTable object only. Deletions on joined tables is not possible.

NOTE: Most datasources have a limited size "rollback" segment. This means that it is only possible to insert/update/delete a finite number of rows without performing a wxDb::CommitTrans (p. 10) or wxDb::RollbackTrans (p. 25). Size of the rollback segment varies from database to database, and is user configurable in most databases. Therefore it is usually best to try to perform a commit or rollback at relatively small intervals when processing a larger number of actions that insert/update/delete rows in a table.

WHERE and FROM clauses specified using wxDbTable::SetWhereClause (p. 73) and wxDbTable::SetFromClause (p. 70) are ignored by this function.

Example

// Delete parts 1 thru 10 from containers 'X', 'Y' and 'Z' that

// are magenta in color

parts.DeleteWhere("(PART_NUMBER BETWEEN 1 AND 10) AND \

CONTAINER IN ('X', 'Y', 'Z') AND \

UPPER(COLOR) = 'MAGENTA'");

wxDbTable::DropIndex

bool DropIndex(const wxString&idxName)

Allows an index on the associated table to be dropped (deleted) if the user login has sufficient privileges to do so.

Parameters

idxName

Name of the index to be dropped.

Remarks

If the index specified in the 'idxName' parameter does not exist, an error will be logged, and the function will return a result of FALSE.

It is not necessary to call wxDb::CommitTrans (p. 10) after executing this function.

wxDbTable::DropTable

bool DropTable()

Deletes the associated table if the user has sufficient privileges to do so.

Remarks

This function returns TRUE if the table does not exist, but only for supported databases (see wxDb::Dbms (p. 11)). If a datasource is not specifically supported, and this function is called, the function will return FALSE.

Most datasources/ODBC drivers will delete any indexes associated with the table automatically, and others may not. Check the documentation for your database to determine the behavior.

It is not necessary to call wxDb::CommitTrans (p. 10) after executing this function.

wxDbTable::From

const wxString& From()

void From(const wxString&From)

Accessor function for the private class member wxDbTable::from. Can be used as a synonym for wxDbTable::GetFromClause (p. 55) (the first form of this function) or wxDbTable::SetFromClause (p. 70) (the second form of this function).

Parameters

From

A comma separated list of table names that are to be outer joined with the base table's columns so that the joined table's columns may be returned in the result set or used as a portion of a comparison with the base table's columns. NOTE that the base tables name must NOT be included in the FROM clause, as it is automatically included by the wxDbTable class in constructing query statements.

Return value

The first form of this function returns the current value of the wxDbTable member variable ::from.

The second form of the function has no return value, as it will always set the from clause successfully.

See also

wxDbTable::GetFromClause (p. 55), wxDbTable::SetFromClause (p. 70)

wxDbTable::GetColDefs

wxDbColDef * GetColDefs()

Accessor function that returns a pointer to the array of column definitions that are bound to the columns that this wxDbTable instance is associated with.

To determine the number of elements pointed to by the returned wxDbColDef (p. 30) pointer, use the wxDbTable::GetNumberOfColumns (p. 57) function.

Remarks

These column definitions must not be manually redefined after they have been set.

wxDbTable::GetCursor

HSTMT GetCursor()

Returns the HSTMT value of the current cursor for this wxDbTable object.

Remarks

This function is typically used just before changing to use a different cursor so that after the program is finished using the other cursor, the current cursor can be set back to being the cursor in use.

See also

wxDbTable::SetCursor (p. 70), wxDbTable::GetNewCursor (p. 56)

wxDbTable::GetDb

wxDb * GetDb()

Accessor function for the private member variable pDb which is a pointer to the datasource connection that this wxDbTable instance uses.

wxDbTable::GetFirst

bool GetFirst()

Retrieves the FIRST row in the record set as defined by the current query. Before retrieving records, a query must be performed using wxDbTable::Query (p. 62), wxDbTable::QueryOnKeyFields (p. 67), wxDbTable::QueryMatching (p. 65) or wxDbTable::QueryBySqlStmt (p. 64).

Remarks

This function can only be used if the datasource connection used by the wxDbTable instance was created with FwdOnlyCursors set to FALSE. If the connection does not allow backward scrolling cursors, this function will return FALSE, and the data contained in the bound columns will be undefined.

See also

wxDb::IsFwdOnlyCursors (p. 21)

wxDbTable::GetFromClause

const wxString& GetFromClause()

Accessor function that returns the current FROM setting assigned with the wxDbTable::SetFromClause (p. 70).

See also

wxDbTable::From (p. 54)

wxDbTable::GetLast

bool GetLast()

Retrieves the LAST row in the record set as defined by the current query. Before retrieving records, a query must be performed using wxDbTable::Query (p. 62), wxDbTable::QueryOnKeyFields (p. 67), wxDbTable::QueryMatching (p. 65) or wxDbTable::QueryBySqlStmt (p. 64).

Remarks

This function can only be used if the datasource connection used by the wxDbTable instance was created with FwdOnlyCursors set to FALSE. If the connection does not allow backward scrolling cursors, this function will return FALSE, and the data contained in the bound columns will be undefined.

See also

wxDb::IsFwdOnlyCursors (p. 21)

wxDbTable::GetNewCursor

HSTMT * GetNewCursor(bool setCursor=FALSE, bool bindColumns=TRUE)

This function will create a new cursor that can be used to access the table being referenced by this wxDbTable instance, or to execute direct SQL commands on without affecting the cursors that are already defined and possibly positioned.

Parameters

setCursor

OPTIONAL. Should this new cursor be set to be the current cursor after successfully creating the new cursor. Default is FALSE.

bindColumns

OPTIONAL. Should this new cursor be bound to all the memory variables that the default cursor is bound to. Default is TRUE.

Remarks

This new cursor must be closed using wxDbTable::DeleteCursor (p. 51) by the calling program before the wxDbTable instance is deleted, or both memory and resource leaks will occur.

wxDbTable::GetNext

bool GetNext()

Retrieves the NEXT row in the record set after the current cursor position as defined by the current query. Before retrieving records, a query must be performed using wxDbTable::Query (p. 62), wxDbTable::QueryOnKeyFields (p. 67), wxDbTable::QueryMatching (p. 65) or wxDbTable::QueryBySqlStmt (p. 64).

Return value

This function returns FALSE when the current cursor has reached the end of the result set. When FALSE is returned, data in the bound columns is undefined.

Remarks

This function works with both forward and backward scrolling cursors.

See alsowxDbTable::++ (p. 75)

wxDbTable::GetNumberOfColumns

UWORD GetNumberOfColumns()

Accessor function that returns the number of columns that are statically bound for access by the wxDbTable instance.

wxDbTable::GetOrderByClause

const wxString& GetOrderByClause()

Accessor function that returns the current ORDER BY setting assigned with the wxDbTable::SetOrderByClause (p. 72).

See also

wxDbTable::OrderBy (p. 62)

wxDbTable::GetPrev

bool GetPrev()

Retrieves the PREVIOUS row in the record set before the current cursor position as defined by the current query. Before retrieving records, a query must be performed using wxDbTable::Query (p. 62), wxDbTable::QueryOnKeyFields (p. 67), wxDbTable::QueryMatching (p. 65) or wxDbTable::QueryBySqlStmt (p. 64).

Return value

This function returns FALSE when the current cursor has reached the beginning of the result set and there are now other rows prior to the cursors current position. When FALSE is returned, data in the bound columns is undefined.

Remarks

This function can only be used if the datasource connection used by the wxDbTable instance was created with FwdOnlyCursors set to FALSE. If the connection does not allow backward scrolling cursors, this function will return FALSE, and the data contained in the bound columns will be undefined.

See also

wxDb::IsFwdOnlyCursors (p. 21), wxDbTable::-- (p. 75)

wxDbTable::GetQueryTableName

const wxString& GetQueryTableName()

Accessor function that returns the name of the table/view that was indicated as being the table/view to query against when this wxDbTable instance was created.

See also

wxDbTable constructor (p. 41)

wxDbTable::GetRowNum

UWORD GetRowNum()

Returns the ODBC row number for performing positioned updates and deletes.

Remarks

This function is not being used within the ODBC class library and may be a candidate for removal if no use is found for it.

Row number with some datasources/ODBC drivers is the position in the result set, while in others it may be a physical position in the database. Check your database documentation to find out which behavior is supported.

wxDbTable::GetTableName

const wxString& GetTableName()

Accessor function that returns the name of the table that was indicated as being the table that this wxDbTable instance was associated with.

wxDbTable::GetTablePath

const wxString& GetTablePath()

Accessor function that returns the path to the data table that was indicated during creation of this wxDbTable instance.

Remarks

Currently only applicable to dBase and MS-Access datasources.

wxDbTable::GetWhereClause

const wxString& GetWhereClause()

Accessor function that returns the current WHERE setting assigned with the wxDbTable::SetWhereClause (p. 73)

See also

wxDbTable::Where (p. 75)

wxDbTable::Insert

int Insert()

Inserts a new record into the table being referenced by this wxDbTable instance. The values in the member variables of the wxDbTable instance are inserted into the columns of the new row in the database. Return value

DB_SUCCESS Record inserted successfully (value = 1)

DB_FAILURE Insert failed (value = 0)

DB_ERR_INTEGRITY_CONSTRAINT_VIOL

The insert failed due to an integrity

constraint violation (duplicate non-unique

index entry) is attempted.

Remarks

A wxDb::CommitTrans (p. 10) or wxDb::RollbackTrans (p. 25) must be called after use of this function to commit or rollback the insertion.

Example

// Incomplete code snippet

wxStrcpy(parts->PartName, "10");

wxStrcpy(parts->PartDesc, "Part #10");

parts->Qty = 1000;

RETCODE retcode = parts.Insert();

switch(retcode)

{

case DB_SUCCESS:

parts->GetDb()->CommitTrans();

return(TRUE);

case DB_ERR_INTEGRITY_CONSTRAINT_VIOL:

// Current data would result in a duplicate key

// on one or more indexes that do not allow duplicates

parts->GetDb()->RollbackTrans();

return(FALSE);

default:

// Insert failed for some unexpected reason

parts->GetDb()->RollbackTrans();

return(FALSE);

}

wxDbTable::IsColNull

bool IsColNull(UWORD colNo)

Used primarily in the ODBC class library to determine if a column value is set to "NULL". Works for all data types supported by the ODBC class library.

Parameters

colNo

The column number of the bound column as defined by the wxDbTable::SetColDefs (p. 68) calls which defined the columns accessible to this wxDbTable instance.

Remarks

NULL column support is currently not fully implemented as of wxWindows 2.4

wxDbTable::IsCursorClosedOnCommit

bool IsCursorClosedOnCommit()

Accessor function to return information collected during the opening of the datasource connection that is used by this wxDbTable instance. The result returned by this function indicates whether an implicit closing of the cursor is done after a commit on the database connection.

Return value

Returns TRUE if the cursor associated with this wxDbTable object is closed after a commit or rollback operation. Returns FALSE otherwise.

Remarks

If more than one wxDbTable instance used the same database connection, all cursors which use the database connection are closed on the commit if this function indicates TRUE.

wxDbTable::IsQueryOnly

bool IsQueryOnly()

Accessor function that returns a value indicating if this wxDbTable instance was created to allow only queries to be performed on the bound columns. If this function returns TRUE, then no actions may be performed using this wxDbTable instance that would modify (insert/delete/update) the table's data.

wxDbTable::Open

bool Open(bool checkPrivileges=FALSE, bool checkTableExists=TRUE)

Every wxDbTable instance must be opened before it can be used. This function checks for the existence of the requested table, binds columns, creates required cursors, (insert/select and update if connection is not wxDB_QUERY_ONLY) and constructs the insert statement that is to be used for inserting data as a new row in the datasource.

Parameters

checkPrivileges

Indicates whether the Open() function should check whether the current connected user has at least SELECT privileges to access the table to which they are trying to open. Default is FALSE.

checkTableExists

Indicates whether the Open() function should check whether the table exists in the database or not before opening it. Default is TRUE.

Remarks

If the function returns a FALSE value due to the table not existing, a log entry is recorded for the datasource connection indicating the problem that was detected when checking for table existence. Note that it is usually best for the calling routine to check for the existence of the table and for sufficent user privileges to access the table in the mode (wxDB_QUERY_ONLY or !wxDB_QUERY_ONLY) before trying to open the table for the best possible explanation as to why a table cannot be opened.

Checking the user's privileges on a table can be quite time consuming during the open phase. With most applications, the programmer already knows that the user has sufficient privileges to access the table, so this check is normally not required.

For best performance, open the table, and then use the wxDb::TablePrivileges (p. 27) function to check the users privileges. Passing a schema to the TablePrivileges() function can significantly speed up the privileges checks.

See also

wxDb::TableExists (p. 27), wxDb::TablePrivileges (p. 27)

wxDbTable::OrderBy

const wxString& OrderBy()

void OrderBy(const wxString&OrderBy)

Accessor function for the private class member wxDbTable::orderBy. Can be used as a synonym for wxDbTable::GetOrderByClause (p. 57) (the first form of this function) or wxDbTable::SetOrderByClause (p. 72) (the second form of this function).

Parameters

OrderBy

A comma separated list of column names that indicate the alphabetized/numeric sorting sequence that the result set is to be returned in. If a FROM clause has also been specified, each column name specified in the ORDER BY clause should be prefaced with the table name to which the column belongs using DOT notation (TABLE_NAME.COLUMN_NAME).

Return value

The first form of this function returns the current value of the wxDbTable member variable ::orderBy.

The second form of the function has no return value.

See also

wxDbTable::GetOrderByClause (p. 57), wxDbTable::SetFromClause (p. 70)

wxDbTable::Query

virtual bool Query(bool forUpdate=FALSE, bool distinct=FALSE)

Parameters

forUpdate

OPTIONAL. Gives you the option of locking records as they are retrieved. If the RDBMS is not capable of the FOR UPDATE clause, this argument is ignored. See wxDbTable::CanSelectForUpdate (p. 45) for additional information regarding this argument. Default is FALSE.

distinct

OPTIONAL. Allows selection of only distinct values from the query (SELECT DISTINCT ... FROM ...). The notion of DISTINCT applies to all columns returned in the result set, not individual columns. Default is FALSE.

Remarks

This function queries records from the datasource based on the three wxDbTable members: "where", "orderBy", and "from". Use wxDbTable::SetWhereClause (p. 73) to filter on records to be retrieved (e.g. All users with a first name of "JOHN"). Use wxDbTable::SetOrderByClause (p. 72) to change the sequence in which records are returned in the result set from the datasource (e.g. Ordered by LAST_NAME). Use wxDbTable::SetFromClause (p. 70) to allow outer joining of the base table (the one being associated with this instance of wxDbTable) with other tables which share a related field.

After each of these clauses are set/cleared, call wxDbTable::Query() to fetch the result set from the datasource.

This scheme has an advantage if you have to requery your record set frequently in that you only have to set your WHERE, ORDER BY, and FROM clauses once. Then to refresh the record set, simply call wxDbTable::Query() as frequently as needed.

Note that repeated calls to wxDbTable::Query() may tax the database server and make your application sluggish if done too frequently or unnecessarily.

The base table name is automatically prepended to the base column names in the event that the FROM clause has been set (is non-null) using wxDbTable::SetFromClause (p. 70).

The cursor for the result set is positioned before the first record in the result set after the query. To retrieve the first record, call either wxDbTable::GetFirst (p. 55) (only if backward scrolling cursors are available) or wxDbTable::GetNext (p. 57). Typically, no data from the result set is returned to the client driver until a request such as wxDbTable::GetNext (p. 57) is performed, so network traffic and database load are not overwhelmed transmitting data until the data is actually requested by the client. This behavior is solely dependent on the ODBC driver though, so refer to the ODBC driver's reference material for information on its behaviors.

Values in the bound columns' memory variables are undefined after executing a call to this function and remain that way until a row in the result set is requested to be returned.

The wxDbTable::Query() function is defined as "virtual" so that it may be overridden for application specific purposes.

Be sure to set the wxDbTable's "where", "orderBy", and "from" member variables to "" if they are not to be used in the query. Otherwise, the results returned may have unexpected results (or no results) due to improper or incorrect query parameters constructed from the uninitialized clauses.

Example

// Incomplete code sample

parts->SetWhereClause("DESCRIPTION = 'FOOD'");

parts->SetOrderByClause("EXPIRATION_DATE");

parts->SetFromClause("");

// Query the records based on the where, orderBy and from clauses

// specified above

parts->Query();

// Display all records queried

while(parts->GetNext())

dispPart(parts); // user defined function

wxDbTable::QueryBySqlStmt

bool QueryBySqlStmt(const wxString&pSqlStmt)

Performs a query against the datasource by accepting and passing verbatim the SQL SELECT statement passed to the function.

Parameters

pSqlStmt

Pointer to the SQL SELECT statement to be executed.

Remarks

This is the most powerful form of the query functions available. This member function allows a programmer to write their own custom SQL SELECT statement for requesting data from the datasource. This gives the programmer access to the full power of SQL for performing operations such as scalar functions, aggregate functions, table joins, and sub-queries, as well as datasource specific function calls.

The requirements of the SELECT statement are the following:

1. Must return the correct number of columns. In the derived

wxDbTable constructor, it is specified how many columns are in

the wxDbTable object. The SELECT statement must return exactly

that many columns.

2. The columns must be returned in the same sequence as specified

when defining the bounds columns using wxDbTable::SetColDefs(),

and the columns returned must be of the proper data type. For

example, if column 3 is defined in the wxDbTable bound column

definitions to be a float, the SELECT statement must return a

float for column 3 (e.g. PRICE * 1.10 to increase the price by

10%).

3. The ROWID can be included in your SELECT statement as the last

column selected, if the datasource supports it. Use

wxDbTable::CanUpdByROWID() to determine if the ROWID can be

selected from the datasource. If it can, much better

performance can be achieved on updates and deletes by including

the ROWID in the SELECT statement.

Even though data can be selected from multiple tables (joins) in your select statement, only the base table associated with this wxDbTable object is automatically updated through the ODBC class library. Data from multiple tables can be selected for display purposes however. Include columns in the wxDbTable object and mark them as non-updateable (See wxDbColDef (p. 30) for details). This way columns can be selected and displayed from other tables, but only the base table will be updated automatically when performed through the wxDbTable::Update (p. 74) function after using this type of query. To update tables other than the base table, use the wxDbTable::Update (p. 74) function passing a SQL statement.

After this function has been called, the cursor is positioned before the first record in the record set. To retrieve the first record, call either wxDbTable::GetFirst (p. 55) or wxDbTable::GetNext (p. 57).

Example

// Incomplete code samples

wxString sqlStmt;

sqlStmt = "SELECT * FROM PARTS WHERE STORAGE_DEVICE = 'SD98' \

AND CONTAINER = 12";

// Query the records using the SQL SELECT statement above

parts->QueryBySqlStmt(sqlStmt);

// Display all records queried

while(parts->GetNext())

dispPart(&parts);

Example SQL statements

----------------------

// Table Join returning 3 columns

SELECT part_no, part_desc, sd_name

from parts, storage_devices

where parts.storage_device_id =

storage_devices.storage_device_id

// Aggregate function returning total number of

// parts in container 99

SELECT count(*) from PARTS where container = 99

// Order by clause; ROWID, scalar function

SELECT part_no, substring(part_desc, 1, 10), qty_on_hand + 1, ROWID

from parts

where warehouse = 10

order by part_no desc // descending order

// Subquery

SELECT * from parts

where container in (select container

from storage_devices

where device_id = 12)

wxDbTable::QueryMatching

virtual bool QueryMatching(bool forUpdate=FALSE, bool distinct=FALSE)

QueryMatching allows querying of records from the table associated with the wxDbTable object by matching "columns" to values.

For example: To query the datasource for the row with a PART_NUMBER column value of "32", clear all column variables of the wxDbTable object, set the PartNumber variable that is bound to the PART_NUMBER column in the wxDbTable object to "32", and then call wxDbTable::QueryMatching().

Parameters

forUpdate

OPTIONAL. Gives you the option of locking records as they are queried (SELECT ... FOR UPDATE). If the RDBMS is not capable of the FOR UPDATE clause, this argument is ignored. See wxDbTable::CanSelectForUpdate (p. 45) for additional information regarding this argument. Default is FALSE.

distinct

OPTIONAL. Allows selection of only distinct values from the query (SELECT DISTINCT ... FROM ...). The notion of DISTINCT applies to all columns returned in the result set, not individual columns. Default is FALSE.

Remarks

The SQL WHERE clause is built by the ODBC class library based on all non-zero/non-NULL columns in your wxDbTable object. Matches can be on one, many or all of the wxDbTable's columns. The base table name is prepended to the column names in the event that the wxDbTable's FROM clause is non-null.

This function cannot be used to perform queries which will check for columns that are 0 or NULL, as the automatically constructed WHERE clause only will contain comparisons on column member variables that are non-zero/non-NULL.

The primary difference between this function and wxDbTable::QueryOnKeyFields (p. 67) is that this function can query on any column(s) in the wxDbTable object. Note however that this may not always be very efficient. Searching on non-indexed columns will always require a full table scan.

The cursor is positioned before the first record in the record set after the query is performed. To retrieve the first record, the program must call either wxDbTable::GetFirst (p. 55) or wxDbTable::GetNext (p. 57).

WHERE and FROM clauses specified using wxDbTable::SetWhereClause (p. 73) and wxDbTable::SetFromClause (p. 70) are ignored by this function.

Example

// Incomplete code sample

parts->ClearMemberVars(); // Set all columns to zero

wxStrcpy(parts->PartNumber,"32"); // Set columns to query on

parts->OnHold = TRUE;

parts->QueryMatching(); // Query

// Display all records queried

while(parts->GetNext())

dispPart(parts); // Some application defined function

wxDbTable::QueryOnKeyFields

bool QueryOnKeyFields(bool forUpdate=FALSE, bool distinct=FALSE)

QueryOnKeyFields provides an easy mechanism to query records in the table associated with the wxDbTable object by the primary index column(s). Simply assign the primary index column(s) values and then call this member function to retrieve the record.

Note that since primary indexes are always unique, this function implicitly always returns a single record from the database. The base table name is prepended to the column names in the event that the wxDbTable's FROM clause is non-null.

Parameters

forUpdate

OPTIONAL. Gives you the option of locking records as they are queried (SELECT ... FOR UPDATE). If the RDBMS is not capable of the FOR UPDATE clause, this argument is ignored. See wxDbTable::CanSelectForUpdate (p. 45) for additional information regarding this argument. Default is FALSE.

distinct

OPTIONAL. Allows selection of only distinct values from the query (SELECT DISTINCT ... FROM ...). The notion of DISTINCT applies to all columns returned in the result set, not individual columns. Default is FALSE.

Remarks

The cursor is positioned before the first record in the record set after the query is performed. To retrieve the first record, the program must call either wxDbTable::GetFirst (p. 55) or wxDbTable::GetNext (p. 57).

WHERE and FROM clauses specified using wxDbTable::SetWhereClause (p. 73) and wxDbTable::SetFromClause (p. 70) are ignored by this function.

Example

// Incomplete code sample

wxStrcpy(parts->PartNumber, "32");

parts->QueryOnKeyFields();

// Display all records queried

while(parts->GetNext())

dispPart(parts); // Some application defined function

wxDbTable::Refresh

bool Refresh()

This function re-reads the bound columns into the memory variables, setting them to the current values stored on the disk.

The cursor position and result set are unaffected by calls to this function. (The one exception is in the case where the record to be refreshed has been deleted by some other user or transaction since it was originally retrieved as part of the result set. For most datasources, the default behavior in this situation is to return the value that was originally queried for the result set, even though it has been deleted from the database. But this is datasource dependent, and should be tested before relying on this behavior.)

Remarks

This routine is only guaranteed to work if the table has a unique primary index defined for it. Otherwise, more than one record may be fetched and there is no guarantee that the correct record will be refreshed. The table's columns are refreshed to reflect the current data in the database.

wxDbTable::SetColDefs

void SetColDefs(UWORD index, const wxString&fieldName, int dataType, void *pData, SWORD cType, int size, bool keyField = FALSE, bool upd = TRUE, bool insAllow = TRUE, bool derivedCol = FALSE)

wxDbColDataPtr * SetColDefs(wxDbColInf *colInfs, UWORD numCols)

Parameters

index

Column number (0 to n-1, where n is the number of columns specified as being defined for this wxDbTable instance when the wxDbTable constructor was called.

fieldName

Column name from the associated data table.

dataType

Logical data type. Valid logical types include:

DB_DATA_TYPE_VARCHAR : strings

DB_DATA_TYPE_INTEGER : non-floating point numbers

DB_DATA_TYPE_FLOAT : floating point numbers

DB_DATA_TYPE_DATE : dates

pData

Pointer to the data object that will hold the column's value when a row of data is returned from the datasource.

cType

SQL C Type. This defines the data type that the SQL representation of the data is converted to to be stored in pData. Other valid types are available also, but these are the most common ones:

SQL_C_CHAR // strings

SQL_C_LONG

SQL_C_ULONG

SQL_C_SHORT

SQL_C_USHORT

SQL_C_FLOAT

SQL_C_DOUBLE

SQL_C_NUMERIC

SQL_C_TIMESTAMP

SQL_C_BOOLEAN // defined in db.h

SQL_C_ENUM // defined in db.h

size

Maximum size in bytes of the pData object.

keyField

OPTIONAL. Indicates if this column is part of the primary index. Default is FALSE.

upd

OPTIONAL. Are updates allowed on this column? Default is TRUE.

insAllow

OPTIONAL. Inserts allowed on this column? Default is TRUE.

derivedCol

OPTIONAL. Is this a derived column (non-base table column for query only)? Default is FALSE.

colInfs

Pointer to an array of wxDbColInf instances which contains all the information necessary to create numCols column definitions.

numCols

Number of elements of wxDbColInf type that are pointed to by colInfs, which are to have column definitions created from them.

Remarks

If pData is to hold a string of characters, be sure to include enough space for the NULL terminator in pData and in the byte count of size.

Both forms of this function provide a shortcut for defining the columns in your wxDbTable object. Use this function in any derived wxDbTable constructor when describing the column/columns in the wxDbTable object.

The second form of this function is primarily used when the wxDb::GetColumns (p. 16) function was used to query the datasource for the column definitions, so that the column definitions are already stored in wxDbColInf form. One example use of using wxDb::GetColumns (p. 16) then using this function is if a data table existed in one datasource, and the table's column definitions were to be copied over to another datasource or table.

Example

// Long way not using this function

wxStrcpy(colDefs[0].ColName, "PART_NO");

colDefs[0].DbDataType = DB_DATA_TYPE_VARCHAR;

colDefs[0].PtrDataObj = PartNumber;

colDefs[0].SqlCtype = SQL_C_CHAR;

colDefs[0].SzDataObj = PART_NUMBER_LEN;

colDefs[0].KeyField = TRUE;

colDefs[0].Updateable = FALSE;

colDefs[0].InsertAllowed= TRUE;

colDefs[0].DerivedCol = FALSE;

// Shortcut using this function

SetColDefs(0, "PART_NUMBER", DB_DATA_TYPE_VARCHAR, PartNumber,

SQL_C_CHAR, PART_NUMBER_LEN, TRUE, FALSE,TRUE,FALSE);

wxDbTable::SetCursor

bool SetCursor(HSTMT *hstmtActivate = (void **) wxDB_DEFAULT_CURSOR)

Parameters

hstmtActivate

OPTIONAL. Pointer to the cursor that is to become the current cursor. Passing no cursor handle will reset the cursor back to the wxDbTable's default (original) cursor that was created when the wxDbTable instance was first created. Default is wxDB_DEFAULT_CURSOR.

Remarks

When swapping between cursors, the member variables of the wxDbTable object are automatically refreshed with the column values of the row that the current cursor is positioned at (if any). If the cursor is not positioned, then the data in member variables is undefined.

The only way to return back to the cursor that was in use before this function was called is to programmatically determine the current cursor's HSTMT BEFORE calling this function using wxDbTable::GetCursor (p. 55) and saving a pointer to that cursor.

See also

wxDbTable::GetNewCursor (p. 56), wxDbTable::GetCursor (p. 55), wxDbTable::SetCursor (p. 70)

wxDbTable::SetFromClause

void SetFromClause(const wxString&From)

Accessor function for setting the private class member wxDbTable::from that indicates what other tables should be outer joined with the wxDbTable's base table for access to the columns in those other tables.

Synonym to this function is one form of wxDbTable::From (p. 54)

Parameters

From

A comma separated list of table names that are to be outer joined with the base table's columns so that the joined table's columns may be returned in the result set or used as a portion of a comparison with the base table's columns. NOTE that the base tables name must NOT be included in the FROM clause, as it is automatically included by the wxDbTable class in constructing query statements.

Remarks

Used by the wxDbTable::Query (p. 62) and wxDbTable::Count (p. 47) member functions to allow outer joining of records from multiple tables.

Do not include the keyword "FROM" when setting the FROM clause.

If using the FROM clause when performing a query, be certain to include in the corresponding WHERE clause a comparison of a column from either the base table or one of the other joined tables to each other joined table to ensure the datasource knows on which column values the tables should be joined on.

Example

...

// Base table is the "LOCATION" table, and it is being

// outer joined to the "PART" table via the the field "PART_NUMBER"

// that can be related between the two tables.

location->SetWhereClause("LOCATION.PART_NUMBER = PART.PART_NUMBER")

location->SetFromClause("PART");

...

See also

wxDbTable::From (p. 54), wxDbTable::GetFromClause (p. 55)

wxDbTable::SetColNull

bool SetColNull(UWORD colNo, bool set=TRUE)

bool SetColNull(const wxString&colName, bool set=TRUE)

Both forms of this function allow a member variable representing a column in the table associated with this wxDbTable object to be set to NULL.

The first form allows the column to be set by the index into the column definitions used to create the wxDbTable instance, while the second allows the actual column name to be specified.

Parameters

colNo

Index into the column definitions used when first defining this wxDbTable object.

colName

Actual data table column name that is to be set to NULL.

set

Whether the column is set to NULL or not. Passing TRUE sets the column to NULL, passing FALSE sets the column to be non-NULL. Default is TRUE.

Remarks

No database updates are done by this function. It only operates on the member variables in memory. Use and insert or update function to store this value to disk.

wxDbTable::SetOrderByClause

void SetOrderByClause(const wxString&OrderBy)

Accessor function for setting the private class member wxDbTable::orderBy which determines sequence/ordering of the rows returned in the result set of a query.

A synonym to this function is one form of the function wxDbTable::OrderBy (p. 62)

Parameters

OrderBy

A comma separated list of column names that indicate the alphabetized sorting sequence that the result set is to be returned in. If a FROM clause has also been specified, each column name specified in the ORDER BY clause should be prefaced with the table name to which the column belongs using DOT notation (TABLE_NAME.COLUMN_NAME).

Remarks

Do not include the keywords "ORDER BY" when setting the ORDER BY clause.

Example

...

parts->SetOrderByClause("PART_DESCRIP, QUANTITY");

...

...

location->SetOrderByClause("LOCATION.POSITION, PART.PART_NUMBER);

...

See also

wxDbTable::OrderBy (p. 62), wxDbTable::GetOrderByClause (p. 57)

wxDbTable::SetQueryTimeout

bool SetQueryTimeout(UDWORD nSeconds)

Allows a time period to be set as the timeout period for queries.

Parameters

nSeconds

The number of seconds to wait for the query to complete before timing out.

Remarks

Neither Oracle or Access support this function as of yet. Other databases should be evaluated for support before depending on this function working correctly.

wxDbTable::SetWhereClause

void SetWhereClause(const wxString&Where)

Accessor function for setting the private class member wxDbTable::where that determines which rows are returned in the result set by the datasource.

A synonym to this function is one form of the function wxDbTable::Where (p. 75)

Parameters

Where

SQL "where" clause. This clause can contain any SQL language that is legal in standard where clauses. If a FROM clause has also been specified, each column name specified in the ORDER BY clause should be prefaced with the table name to which the column belongs using DOT notation (TABLE_NAME.COLUMN_NAME).

Remarks

Do not include the keywords "WHERE" when setting the WHERE clause.

Example

...

// Simple where clause

parts->SetWhereClause("PART_NUMBER = '32'");

...

// Any comparison operators

parts->SetWhereClause("PART_DESCRIP LIKE 'HAMMER%'");

...

// Multiple comparisons, including a function call

parts->Where("QTY > 0 AND {fn UCASE(PART_DESCRIP)} LIKE '%DRILL%'");

...

// Using parameters and multiple logical combinations

parts->Where("((QTY > 10) OR (ON_ORDER > 0)) AND ON_HOLD = 0");

...

// This query uses an outer join (requiring a FROM clause also)

// that joins the PART and LOCATION table on he common field

// PART_NUMBER.

parts->Where("PART.ON_HOLD = 0 AND \

PART.PART_NUMBER = LOCATION.PART_NUMBER AND \

LOCATION.PART_NUMBER > 0");

See also

wxDbTable::Where (p. 75), wxDbTable::GetWhereClause (p. 59)

wxDbTable::Update

bool Update()

bool Update(const wxString&pSqlStmt)

The first form of this function will update the row that the current cursor is currently positioned at with the values in the memory variables that are bound to the columns. The actual SQL statement to perform the update is automatically created by the ODBC class, and then executed.

The second form of the function allows full access through SQL statements for updating records in the database. Write any valid SQL UPDATE statement and submit it to this function for execution. Sophisticated updates can be performed using the full power of the SQL dialect. The full SQL statement must have the exact syntax required by the driver/datasource for performing the update. This usually is in the form of:

UPDATE tablename SET col1=X, col2=Y, ... where ...

Parameters

pSqlStmt

Pointer to SQL UPDATE statement to be executed.

Remarks

A wxDb::CommitTrans (p. 10) or wxDb::RollbackTrans (p. 25) must be called after use of this function to commit or rollback the update.

Example

wxString sqlStmt;

sqlStmt = "update PART set QTY = 0 where PART_NUMBER = '32'";

wxDbTable::UpdateWhere

bool UpdateWhere(const wxString&pWhereClause)

Performs updates to the base table of the wxDbTable object, updating only the rows which match the criteria specified in the pWhereClause.

All columns that are bound to member variables for this wxDbTable instance that were defined with the "updateable" parameter set to TRUE will be updated with the information currently held in the memory variable.

Parameters

pWhereClause

Pointer to a valid SQL WHERE clause. Do not include the keyword 'WHERE'.

Remarks

Care should be used when updating columns that are part of indexes with this function so as not to violate an unique key constraints.

A wxDb::CommitTrans (p. 10) or wxDb::RollbackTrans (p. 25) must be called after use of this function to commit or rollback the update(s).

wxDbTable::Where

const wxString& Where()

void Where(const wxString&Where)

Accessor function for the private class member wxDbTable::where. Can be used as a synonym for wxDbTable::GetWhereClause (p. 59) (the first form of this function) to return the current where clause or wxDbTable::SetWhereClause (p. 73) (the second form of this function) to set the where clause for this table instance.

Parameters

Where

A valid SQL WHERE clause. Do not include the keyword 'WHERE'.

Return value

The first form of this function returns the current value of the wxDbTable member variable ::where.

The second form of the function has no return value, as it will always set the where clause successfully.

See also

wxDbTable::GetWhereClause (p. 59), wxDbTable::SetWhereClause (p. 73)

wxDbTable::operator ++

bool operator ++()

Synonym for wxDbTable::GetNext (p. 57)

See also

wxDbTable::GetNext (p. 57)

wxDbTable::operator --

bool operator --()

Synonym for wxDbTable::GetPrev (p. 57)

See also

wxDbTable::GetPrev (p. 57)

wxDbTableInf

tableName[0] = 0;

tableType[0] = 0;

tableRemarks[0] = 0;

numCols = 0;

pColInf = NULL;

Currently only used by wxDb::GetCatalog (p. 15) internally and wxDbInf (p. 39) class, but may be used in future releases for user functions. Contains information describing the table (Name, type, etc). A pointer to a wxDbColInf array instance is included so a program can create a wxDbColInf (p. 31) array instance (using wxDb::GetColumns (p. 16)) to maintain all information about the columns of a table in one memory structure.

Eventually, accessor functions will be added for this class

wxDbTableInf::Initialize

Simply initializes all member variables to a cleared state. Called by the constructor automatically.

Database classes overview

The more sophisticated wxODBC classes (wxDb/wxDbTable) are the recommended classes for doing database/ODBC work with wxWindows. These new classes replace the wxWindows v1.6x classes wxDatabase. Documentation for the old wxDatabase class and its associated classes is still included in the class documentation and in this overview section, but support for these old classes has been phased out, and all future development work is being done solely on the new wxDb/wxDbTable classes.

Different ODBC Class Libraries in wxWindows

Following is detailed overview of how to use the wxWindows ODBC classes - wxDb (p. 1) and wxDbTable (p. 40) and their associated functions. These are the ODBC classes donated by Remstar International, and are collectively referred to herein as the wxODBC classes. Since their initial inclusion with wxWindows v2.x, they have become the recommended wxWindows classes for database access.

wxDb/wxDbTable wxODBC Overview

Classes: wxDb (p. 1), wxDbTable (p. 40)

The wxODBC classes were designed for database independence. Although SQL and ODBC both have standards which define the minimum requirements they must support to be in compliance with specifications, different database vendors may implement things slightly different. One example of this is that Oracle requires all user names for the datasources to be supplied in uppercase characters. In situations like this, the wxODBC classes have been written to make this transparent to the programmer when using functions that require database specific syntax.

Currently several major databases, along with other widely used databases, have been tested and supported through the wxODBC classes. The list of supported databases is certain to grow as more users start implementing software with these classes, but at the time of the writing of this document, users have successfully used the classes with the following datasources:

· Oracle (v7, v8, v8i)

· Sybase (ASA and ASE)

· MS SQL Server (v7 - minimal testing)

· MS Access (97 and 2000)

· MySQL

· DBase (IV, V)**

· PostgreSQL

· INFORMIX

· VIRTUOSO

· DB2

An up-to-date list can be obtained by looking in the comments of the function wxDb::Dbms (p. 11) in db.cpp, or in the enumerated type wxDBMS (p. 2) in db.h.

**dBase is not truly an ODBC datasource, but there are drivers which can emulate much of the functionality of an ODBC connection to a dBase table. See the wxODBC Known Issues (p. 91) section of this overview for details.

wxODBC Where To Start

First, if you are not familiar with SQL and ODBC, go to your local bookstore and pick up a good book on each. This documentation is not meant to teach you many details about SQL or ODBC, though you may learn some just from immersion in the subject.

If you have worked with non-SQL/ODBC datasources before, there are some things you will need to un-learn. First some terminology as these phrases will be used heavily in this section of the manual.

Datasource (usually a database) that contains the data that will be accessed by the wxODBC classes.

Data table The section of the datasource that contains the rows and columns of data.

ODBC driver The middle-ware software that interprets the ODBC commands sent by your application and converts them to the SQL format expected by the target datasource.

Datasource connection An open pipe between your application and the ODBC driver which in turn has a connection to the target datasource. Datasource connections can have a virtually unlimited number of wxDbTable instances using the same connect (dependent on the ODBC driver). A separate connection is not needed for each table (the exception is for isolating commits/rollbacks on different tables from affecting more than the desired table. See the class documentation on wxDb::CommitTrans (p. 10) and wxDb::RollbackTrans (p. 25).

Rows Similar to records in old relational databases, a row is a collection of one instance of each column of the data table that are all associated with each other.

Columns Individual fields associated with each row of a data table.

Query Request from the client to the datasource asking for the data that matches the requirements specified in the users request. When a query is performed, the datasource performs the lookup of the rows with satisfy the query, and creates a result set.

Result set The data which matches the requirements specified in a query sent to the datasource. Dependent on drivers, a result set typically remains at the datasource (no data is transmitted to the ODBC driver) until the client actually instructs the ODBC driver to retrieve it.

Cursor a logical pointer into the result set that a query generates, indicating the next record that will be returned to the client when a request for the next record is made.

Scrolling cursors Scrolling refers to the movement of cursors through the result set. Cursors can always scroll forward sequentially in the result set (FORWARD ONLY scrolling cursors). With Forward only scrolling cursors, once a row in the result set has been returned to the ODBC driver and on to the client, there is no way to have the cursor move backward in the result set to look at the row that is previous to the current row in the result set. If BACKWARD scrolling cursors are supported by both the ODBC driver and the datasource that are being used, then backward scrolling cursor functions may be used (wxDbTable::GetPrev (p. 57), wxDbTable::GetFirst (p. 55), and wxDbTable::GetLast (p. 56)). If the datasource or the ODBC driver only support forward scrolling cursors, your program and logic must take this in to account.

Commit/Rollback Commit will physically save insertions/deletions/updates, while rollback basically does an undo of everything done against the datasource connection that has not been previously committed. Note that Commit and Rollbacks are done on a connection, not on individual tables. All tables which use a shared connection to the datasource are all committed/rolled back at the same time when a call to wxDb::CommitTrans (p. 10) or wxDb::RollbackTrans (p. 25) is made.

Index Indexes are datasource maintained lookup structures that allow the datasource to quickly locate data rows based on the values of certain columns. Without indexes, the datasource would need to do a sequential search of a table every time a query request is made. Proper unique key index construction can make datasource queries nearly instantaneous.

Before you are able to read data from a data table in a datasource, you must have a connection to the datasource. Each datasource connection may be used to open multiple tables all on the same connection (number of tables open are dependent on the driver, datasource configuration and the amount of memory on the client workstation). Multiple connections can be opened to the same datasource by the same client (number of concurrent connections is dependent on the driver and datasource configuration).

When a query is performed, the client passes the query to the ODBC driver, and the driver then translates it and passes it along to the datasource. The database engine (in most cases - exceptions are text and dBase files) running on the machine hosting the database does all the work of performing the search for the requested data. The client simply waits for a status to come back through the ODBC driver from the datasource.

Depending on the ODBC driver, the result set either remains "queued" on the database server side, or is transferred to the machine that the driver is queued on. The client does not receive this data. The client must request some or all of the result set to be returned before any data rows are returned to the client application.

Result sets do not need to include all columns of every row matching the query. In fact, result sets can actually be joinings of columns from two or more data tables, may have derived column values, or calculated values returned.

For each result set, a cursor is maintained (typically by the database) which keeps track of where in the result set the user currently is. Depending on the database, ODBC driver, and how you configured the wxWindows ODBC settings in setup.h (see wxODBC - Compiling (p. 82)), cursors can be either forward or backward scrolling. At a minim, cursors must scroll forward. For example, if a query resulted in a result set with 100 rows, as the data is read by the client application, it will read row 1, then 2, then 3, etc, etc. With forward only cursors, once the cursor has moved to the next row, the previous row cannot be accessed again without re-querying the datasource for the result set over again. Backward scrolling cursors allow you to request the previous row from the result set, actually scrolling the cursor backward.

Backward scrolling cursors are not supported on all database/driver combinations. For this reason, forward-only cursors are the default in the wxODBC classes. If your datasource does support backward scrolling cursors and you wish to use them, make the appropriate changes in setup.h to enable them (see wxODBC - Compiling (p. 82)). For greatest portability between datasources, writing your program in such a way that it only requires forward scrolling cursors is your best bet. On the other hand, if you are focusing on using only datasources that support backward scrolling cursors, potentially large performance benefits can be gained from using them.

There is a limit to the number of cursors that can be open on each connection to the datasource, and usually a maximum number of cursors for the datasource itself. This is all dependent on the database. Each connection that is opened (each instance of a wxDb) opens a minimum of 5 cursors for on creation that are required for things such as updates/deletions/rollbacks/queries. Cursors are a limited resource, so use care in creating large numbers of cursors.

Additional cursors can be created if necessary with the wxDbTable::GetNewCursor (p. 56) function. One example use for additional cursors are to track multiple scroll points in result sets. By creating a new cursor, a program could request a second result set from the datasource while still maintaining the original cursor position in the first result set.

Different than non-SQL/ODBC datasources, when a program performs an insertion, deletion, or update (or other SQL functions like altering tables, etc) through ODBC, the program must issue a "commit" to the datasource to tell the datasource that the action(s) it has been told to perform are to be recorded as permanent. Until a commit is performed, any other programs that query the datasource will not see the changes that have been made (although there are databases that can be configured to auto-commit). NOTE: With most all datasources, until the commit is performed, any cursor that is open on that same datasource connection will be able to see the changes that are uncommitted. Check your database's documentation/configuration to verify this before counting on it though.

A rollback is basically an UNDO command on the datasource connection. When a rollback is issued, the datasource will flush all commands it has been told to do since the last commit that was performed.

NOTE: Commits/Rollbacks are done on datasource connections (wxDb instances) not on the wxDbTable instances. This means that if more than one table shares the same connection, and a commit or rollback is done on that connection, all pending changes for ALL tables using that connection are committed/rolled back.

wxODBC - Configuring your system for ODBC use

Before you are able to access a datasource, you must have installed and configured an ODBC driver. Doing this is system specific, so it will not be covered in detail here. But here are a few details to get you started.

Most database vendors provide at least a minimal ODBC driver with their database product. In practice, many of these drivers have proven to be slow and/or incomplete. Rumor has it that this is because the vendors do not want you using the ODBC interface to their products, they want you to use their applications to access the data.

Whatever the reason, for database intensive applications, you may want to think of using a third-party ODBC driver for your needs. One example of a third party set of ODBC drivers that has been heavily tested and used is Rogue Wave's drivers. Rogue Wave has drivers available for many different platforms and databases. Under Microsoft Windows, install the ODBC driver you are planning to use. You will then use the ODBC Administrator in the Control Panel to configure an instance of the driver for your intended datasource. Note that with all flavors of NT, this configuration can be set up as a System or User DSN (datasource name). Configuring it as a system resource will make it available to all users (if you are logged in as 'administrator'), otherwise the datasource will only be available to the who configured the DSN.

Under Unix, iODBC is used for implementation of the ODBC API. To compile the wxODBC classes, you must first obtain (http://www.iodbc.org) and install iODBC. Then you must create the file " /.odbc.ini" (or optionally create "/etc/odbc.ini" for access for all users on the system). This file contains the settings for your system/datasource. Below is an example section of a odbc.ini file for use with the "samples/db" sample program using MySQL:

[contacts]

Trace = Off

TraceFile= stderr

Driver = /usr/local/lib/libmyodbc.so

DSN = contacts

SERVER = 192.168.1.13

USER = qet

PASSWORD =

PORT = 3306

wxODBC - Compiling

The wxWindows setup.h file has several settings in it pertaining to compiling the wxODBC classes.

wxUSE_ODBC This must be set to 1 in order for the compiler to compile the wxODBC classes. Without setting this to 1, there will be no access to any of the wxODBC classes. The default is 0.

wxODBC_FWD_ONLY_CURSORS When a new database connection is requested, this setting controls the default of whether the connection allows only forward scrolling cursors, or forward and backward scrolling cursors (see the section in "WHERE TO START" on cursors for more information on cursors). This default can be overridden by passing a second parameter to either the wxDbGetConnection (p. 5) or wxDb constructor (p. 8). The default is 1.

wxODBC_BACKWARD_COMPATABILITY Between v2.0 and 2.2, massive renaming efforts were done to the ODBC classes to get naming conventions similar to those used throughout wxWindows, as well as to preface all wxODBC classes names and functions with a wxDb preface. Because this renaming would affect applications written using the v2.0 names, this compile-time directive was added to allow those programs written for v2.0 to still compile using the old naming conventions. These deprecated names are all define'd to their corresponding new function names at the end of the db.cpp/dbtable.cpp source files. These deprecated class/function names should not be used in future development, as at some point in the future they will be removed. The default is 0.

Under MS Windows

You are required to include the "odbc32.lib" provided by your compiler vendor in the list of external libraries to be linked in. If using the makefiles supplied with wxWindows, this library should already be included for use with makefile.b32, makefile.vc, and makefile.g95.

You cannot compile the wxODBC classes under Win16 - sorry.

MORE TO COME

Under Unix--with-iodbc flag for configure

MORE TO COME

wxODBC - Basic Step-By-Step Guide

To use the classes in an application, there are eight basic steps:

· Define datasource connection information

· Get a datasource connection

· Create table definition

· Open the table

· Use the table

· Close the table

· Close the datasource connection

· Release the ODBC environment handle

Following each of these steps is detailed to explain the step, and to hopefully mention as many of the pitfalls that beginning users fall in to when first starting to use the classes. Throughout the steps, small snippets of code are shown to show the syntax of performing the step. A complete code snippet is provided at the end of this overview that shows a complete working flow of all these steps (see wxODBC - Sample Code 1 (p. 93)).

Define datasource connection information

To be able to connect to a datasource through the ODBC driver, a program must supply a minimum of three pieces of information: Datasource name, User ID, and Authorization string (password). A fourth piece of information, a default directory indicating where the data file is stored, is required for Text and dBase drivers for ODBC.

The wxWindows data class wxDbConnectInf exists for holding all of these values, plus some others that may be desired.

The 'Henv' member is the environment handle used to access memory for use by the ODBC driver. Use of this member is described below in the "Getting a Connection to the Datasource" section.

The 'Dsn' must exactly match the datasource name used to configure the ODBC datasource (in the ODBC Administrator (MSW only) or in the .odbc.ini file).

The 'Uid' is the User ID that is to be used to log in to the datasource. This User ID must already have been created and assigned rights within the datasource to which you are connecting. The user that the connection is establish by will determine what rights and privileges the datasource connection will allow the program to have when using the connection that this connection information was used to establish. Some datasources are case sensitive for User IDs, and though the wxODBC classes attempt to hide this from you by manipulating whatever data you pass in to match the datasource's needs, it is always best to pass the 'Uid' in the case that the datasource requires.

The 'AuthStr' is the password for the User ID specified in the 'Uid' member. As with the 'Uid', some datasources are case sensitive (in fact most are). The wxODBC classes do NOT try to manage the case of the 'AuthStr' at all. It is passed verbatim to the datasource, so you must use the case that the datasource is expecting.

The 'defaultDir' member is used with file based datasources (i.e. dBase, FoxPro, text files). It contains a full path to the location where the data table or file is located. When setting this value, use forward slashes '/' rather than backslashes ' avoid compatibility differences between ODBC drivers.

The other fields are currently unused. The intent of these fields are that they will be used to write our own ODBC Administrator type program that will work on both MSW and Un*x systems, regardless of the datasource. Very little work has been done on this to date.

Get a Datasource Connection

There are two methods of establishing a connection to a datasource. You may either manually create your own wxDb instance and open the connection, or you may use the caching functions provided with the wxODBC classes to create/maintain/delete the connections.

Regardless of which method you use, you must first have a fully populated wxDbConnectInf object. In the wxDbConnectInf instance, provide a valid Dns, Uid, and AuthStr (along with a 'defaultDir' if necessary). Before using this though, you must allocate an environment handle to the 'Henv' member.

wxDbConnectInf DbConnectInf;

DbConnectInf.SetDsn,"MyDSN");

DbConnectInf.SetUserID,"MyUserName");

DbConnectInf.SetPassword("MyPassword");

DbConnectInf.SetDefaultDir("");

To allocate an environment handle for the ODBC connection to use, the wxDbConnectInf class has a datasource independent method for creating the necessary handle:

if (DbConnectInf.AllocHenv())

{

wxMessageBox("Unable to allocate an ODBC environment handle",

"DB CONNECTION ERROR", wxOK | wxICON_EXCLAMATION);

return;

}

When the wxDbConnectInf::AllocHenv() function is called successfully, a value of TRUE will be returned. A value of FALSE means allocation failed, and the handle will be undefined.

A shorter form of doing the above steps is encapsulated into the long form of the constructor for wxDbConnectInf.

wxDbConnectInf *DbConnectInf;

DbConnectInf = new wxDbConnectInf(NULL, "MyDSN", "MyUserName",

"MyPassword", "");

This shorthand form of initializing the constructor passes a NULL for the SQL environment handle, telling the constructor to allocate a handle during construction. This handle is also managed for the life of wxDbConnectInf instance, and is freed automatically upon destruction of the instance.

Once the wxDbConnectInf instance is initialized, you are ready to connect to the datasource.

To manually create datasource connections, you must create a wxDb instance, and then open it.

wxDb *db = new wxDb(DbConnectInf->GetHenv());

opened = db->Open(DbConnectInf);

The first line does the house keeping needed to initialize all the members of the wxDb class. The second line actually sends the request to the ODBC driver to open a connection to its associated datasource using the parameters supplied in the call to wxDb::Open (p. 24).

A more advanced form of opening a connection is to used the connection caching functions that are included with the wxODBC classes. The caching mechanisms do the same functions are the manual approach to opening a connection, but they also manage each connection they have created, re-using them and cleaning them up when they are closed, without you programmatically needing to do the coding.

To use the caching function wxDbGetConnection (p. 5) to get a connection to a datasource, simply call it with a single parameter of the type wxDbConnectInf:

db = wxDbGetConnection(DbConnectInf);

The wxDb pointer that is returned is both initialized and opened. If something failed in creating or opening the connection, the return value from wxDbGetConnection (p. 5) will be NULL.

The connection that is returned is either a new connection, or it is a "free" connection from the cache of connections that the class maintains that was no longer in use. Any wxDb instance created with a call to wxDbGetConnection (p. 5) is kept track of in a linked list of established connections. When a program is done with a connection, a call to wxDbFreeConnection (p. 5) is made, and the datasource connection will then be tagged as FREE, making it available for the next call to wxDbGetConnection (p. 5) that needs a connection using the same connection information (Dsn, Uid, AuthStr). The cached connections remain cached until a call to wxDbCloseConnections (p. 5) is made, at which time all cached connections are closed and deleted.

Besides the obvious advantage of using the single command caching routine to obtain a datasource connection, using cached connections can be quite a performance boost as well. Each time that a new connection is created (not retrieved from the cache of free connections), the wxODBC classes perform many queries against the datasource to determine the datasource's datatypes and other fundamental behaviors. Depending on the hardware, network bandwidth, and datasource speed, this can in some cases take a few seconds to establish the new connection (with well balanced systems, it should only be a fraction of a second). Re-using already established datasource connections rather than creating/deleting, creating/deleting connections can be quite a time saver.

Another time saver is the "copy connection" features of both wxDb::Open (p. 24) and wxDbGetConnection (p. 5). If manually creating a wxDb instance and opening it, you must pass an existing connection to the wxDb::Open (p. 24) function yourself to gain the performance benefit of copying existing connection settings. The wxDbGetConnection (p. 5) function automatically does this for you, checking the Dsn, Uid, and AuthStr parameters when you request a connection for any existing connections that use those same settings. If one is found, wxDbGetConnection (p. 5) copies the datasource settings for datatypes and other datasource specific information that was previously queried, rather than re-querying the datasource for all those same settings.

One final note on creating a connection. When a connection is created, it will default to only allowing cursor scrolling to be either forward only, or both backward and forward scrolling cursors. The default behavior is determined by the setting "wxODBC_FWD_ONLY_CURSORS" in setup.h when you compile the wxWindows library. The library default is to only support forward scrolling cursors only, though this can be overridden by parameters for wxDb() constructor or the wxDbGetConnection (p. 5) function. All datasources and ODBC drivers must support forward scrolling cursors. Many datasources support backward scrolling cursors, and many ODBC drivers support backward scrolling cursors. Before planning on using backward scrolling cursors, you must be certain that both your datasource and ODBC driver fully support backward scrolling cursors. See the small blurb about "Scrolling cursors" in the definitions at the beginning of this overview, or other details of setting the cursor behavior in the wxDb class documentation.

Create Table Definition

Data can be accessed in a datasource's tables directly through various functions of the wxDb class (see wxDb::GetData (p. 17)). But to make life much simpler, the wxDbTable class encapsulates all of the SQL specific API calls that would be necessary to do this, wrapping it in an intuitive class of APIs.

The first step in accessing data in a datasource's tables via the wxDbTable class is to create a wxDbTable instance.

table = new wxDbTable(db, tableName, numTableColumns, "",

!wxDB_QUERY_ONLY, "");

When you create the instance, you indicate the previously established datasource connection to be used to access the table, the name of the primary table that is to be accessed with the datasource's tables, how many columns of each row are going to be returned, the name of the view of the table that will actually be used to query against (works with Oracle only at this time), whether the data returned is for query purposes only, and finally the path to the table, if different than the path specified when connecting to the datasource.

Each of the above parameters are described in detail in the wxDbTable class' description, but one special note here about the fifth parameter - queryOnly setting. If a wxDbTable instance is created as wxDB_QUERY_ONLY, then no inserts/deletes/updates are able to be performed using this instance of the wxDbTable. Any calls to wxDb::CommitTrans (p. 10) or wxDb::RollbackTrans (p. 25) against the datasource connection used by this wxDbTable instance are ignored by this instance. If the wxDbTable instance is created with "!wxDB_QUERY_ONLY" as shown above, then all the cursors and other overhead associated with being able to insert/update/delete data in the table are created, and thereby those operations can then be performed against the associated table with this wxDbTable instance.

If a table is to be accessed via a wxDbTable instance, and the table will only be read from, not written to, there is a performance benefit (not as many cursors need to be maintained/updated, hence speeding up access times), as well as a resource savings due to fewer cursors being created for the wxDbTable instance. Also, with some datasources, the number of simultaneous cursors is limited.

When defining the columns to be retrievable by the wxDbTable instance, you can specify anywhere from one column up to all columns in the table.

table->SetColDefs(0, "FIRST_NAME", DB_DATA_TYPE_VARCHAR, FirstName,

SQL_C_CHAR, sizeof(name), TRUE, TRUE);

table->SetColDefs(1, "LAST_NAME", DB_DATA_TYPE_VARCHAR, LastName,

SQL_C_CHAR, sizeof(LastName), TRUE, TRUE);

Notice that column definitions start at index 0 and go up to one less than the number of columns specified when the wxDbTable instance was created (in this example, two columns - one with index 0, one with index 1).

The above lines of code "bind" the datasource columns specified to the memory variables in the client application. So when the application makes a call to wxDbTable::GetNext (p. 57) (or any other function that retrieves data from the result set), the variables that are bound to the columns will have the column value stored into them. See the wxDbTable::SetColDefs (p. 68) class documentation for more details on all the parameters for this function.

The bound memory variables have undefined data in them until a call to a function that retrieves data from a result set is made (e.g. wxDbTable::GetNext (p. 57),wxDbTable::GetPrev (p. 57), etc). The variables are not initialized to any data by the wxODBC classes, and they still contain undefined data after a call to wxDbTable::Query (p. 62). Only after a successful call to one of the ::GetXxxx() functions is made do the variables contain valid data.

It is not necessary to define column definitions for columns whose data is not going to be returned to the client. For example, if you want to query the datasource for all users with a first name of 'GEORGE', but you only want the list of last names associated with those rows (why return the FIRST_NAME column every time when you already know it is 'GEORGE'), you would only have needed to define one column above.

You may have as many wxDbTable instances accessing the same table using the same wxDb instance as you desire. There is no limit imposed by the classes on this. All datasources supported (so far) also have no limitations on this.

Open the table

Opening the table technically is not doing anything with the datasource itself. Calling wxDbTable::Open (p. 61) simply does all the house keeping of checking that the specified table exists, that the current connected user has at least SELECT privileges for accessing the table, setting up the requisite cursors, binding columns and cursors, and constructing the default INSERT statement that is used when a new row is inserted into the table (non-wxDB_QUERY_ONLY tables only).

if (!table->Open())

{

// An error occurred opening (setting up) the table

}

The only reason that a call to wxDbTable::Open (p. 61) will likely fail is if the user has insufficient privileges to even SELECT the table. Other problems could occur, such as being unable to bind columns, but these other reason point to some lack of resource (like memory). Any errors generated internally in the wxDbTable::Open (p. 61) function are logged to the error log if SQL logging is turned on for the classes.

Use the table

To use the table and the definitions that are now set up, we must first define what data we want the datasource to collect in to a result set, tell it where to get the data from, and in what sequence we want the data returned.

// the WHERE clause limits/specifies which rows in the table

// are to be returned in the result set

table->SetWhereClause("FIRST_NAME = 'GEORGE'");

// Result set will be sorted in ascending alphabetical

// order on the data in the 'LAST_NAME' column of each row

// If the same last name is in the table for two rows,

// sub-sort on the 'AGE' column

table->SetOrderByClause("LAST_NAME, AGE");

// No other tables (joins) are used for this query

table->SetFromClause("");

The above lines will be used to tell the datasource to return in the result all the rows in the table whose column "FIRST_NAME" contains the name 'GEORGE' (note the required use of the single quote around the string literal) and that the result set will return the rows sorted by ascending last names (ascending is the default, and can be overridden with the "DESC" keyword for datasources that support it - "LAST_NAME DESC").

Specifying a blank WHERE clause will result in the result set containing all rows in the datasource.

Specifying a blank ORDERBY clause means that the datasource will return the result set in whatever sequence it encounters rows which match the selection criteria. What this sequence is can be hard to determine. Typically it depends on the index that the datasource used to find the rows which match the WHERE criteria. BEWARE - relying on the datasource to return data in a certain sequence when you have not provided an ORDERBY clause will eventually cause a problem for your program. Databases can be tuned to be COST-based, SPEED-based, or some other basis for how it gets your result set. In short, if you need your result set returned in a specific sequence, ask for it that way by providing an ORDERBY clause.

Using an ORDERBY clause can be a performance hit, as the database must sort the items before making the result set available to the client. Creating efficient indexes that cause the data to be "found" in the correct ORDERBY sequence can be a big performance benefit. Also, in the large majority of cases, the database will be able to sort the records faster than your application can read all the records in (unsorted) and then sort them. Let the database do the work for you!

Notice in the example above, a column that is not included in the bound data columns ('AGE') will be used to sub-sort the result set.

The FROM clause in this example is blanked, as we are not going to be performing any table joins with this simple query. When the FROM clause is blank, it is assumed that all columns referenced are coming from the default table for the wxDbTable instance.

After the selection criteria have been specified, the program can now ask the datasource to perform the search and create a result set that can be retrieved:

// Instruct the datasource to perform a query based on the

// criteria specified above in the where/orderBy/from clauses.

if (!table->Query())

{

// An error occurred performing the query

}

Typically, when an error occurs when calling wxDbTable::Query (p. 62), it is a syntax problem in the WHERE clause that was specified. The exact SQL (datasource specific) reason for what caused the failure of wxDbTable::Query (p. 62) (and all other operations against the datasource can be found by parsing the table's database connection's "errorList[]" array member for the stored text of the error.

When the wxDbTable::Query (p. 62) returns TRUE, the database was able to successfully complete the requested query using the provided criteria. This does not mean that there are any rows in the result set, it just mean that the query was successful.

IMPORTANT: The result created by the call to wxDbTable::Query (p. 62) can be one of two forms. It is either a snapshot of the data at the exact moment that the database determined the record matched the search criteria, or it is a pointer to the row that matched the selection criteria. Which form of behavior is datasource dependent. If it is a snapshot, the data may have changed since the result set was constructed, so beware if your datasource uses snapshots and call wxDbTable::Refresh (p. 67). Most larger brand databases do not use snapshots, but it is important to mention so that your application can handle it properly if your datasource does.

To retrieve the data, one of the data fetching routines must be used to request a row from the result set, and to store the data from the result set into the bound memory variables. After wxDbTable::Query (p. 62) has completed successfully, the default/current cursor is placed so it is pointing just before the first record in the result set. If the result set is empty (no rows matched the criteria), then any calls to retrieve data from the result set will return FALSE.

wxString msg;

while (table->GetNext())

{

msg.Printf("Row #%lu -- First Name : %s Last Name is %s",

table->GetRowNum(), FirstName, LastName);

wxMessageBox(msg, "Data", wxOK | wxICON_INFORMATION, NULL);

}

The sample code above will read the next record in the result set repeatedly until the end of the result set has been reached. The first time that wxDbTable::GetNext (p. 57) is called right after the successful call to wxDbTable::Query (p. 62), it actually returns the first record in the result set.

When wxDbTable::GetNext (p. 57) is called and there are no rows remaining in the result set after the current cursor position, wxDbTable::GetNext (p. 57) (as well as all the other wxDbTable::GetXxxxx() functions) will return FALSE.

Close the table

When the program is done using a wxDbTable instance, it is as simple as deleting the table pointer (or if declared statically, letting the variable go out of scope). Typically the default destructor will take care of all that is required for cleaning up the wxDbTable instance.

if (table)

{

delete table;

table = NULL;

}

Deleting a wxDbTable instance releases all of its cursors, deletes the column definitions and frees the SQL environment handles used by the table (but not the environment handle used by the datasource connection that the wxDbTable instance was using).

Close the datasource connection

After all tables that have been using a datasource connection have been closed (this can be checked by calling wxDb::GetTableCount (p. 20) and checking that it returns 0), then you may close the datasource connection. The method of doing this is dependent on whether the non-caching or caching method was used to obtain the datasource connection.

If the datasource connection was created manually (non-cached), closing the connection is done like this:

if (db)

{

db->Close();

delete db;

db = NULL;

}

If the program used the wxDbGetConnection (p. 5) function to get a datasource connection, the following is the code that should be used to free the connection(s):

if (db)

{

wxDbFreeConnection(db);

db = NULL;

}

Note that the above code just frees the connection so that it can be re-used on the next call the wxDbGetConnection (p. 5). To actually dispose of the connection, releasing all of its resources (other than the environment handle), do the following:

wxDbCloseConnections();

Release the ODBC environment handle

Once all of the connections that used the ODBC environment handle (in this example it was stored in "DbConnectInf.Henv") have been closed, then it is safe to release the environment handle:

DbConnectInf->FreeHenv());

Or, if the long form of the constructor was used and the constructor was allowed to allocate its own SQL environment handle, leaving scope or destruction of the wxDbConnectInf will free the handle automatically.

delete DbConnectInf;

Remember to never release this environment handle if there are any connections still using the handle.

wxODBC - Known Issues

As with creating wxWindows, writing the wxODBC classes was not the simple task of writing an application to run on a single type of computer system. The classes need to be cross-platform for different operating systems, and they also needed to take in to account different database manufacturers and different ODBC driver manufacturers. Because of all the possible combinations of OS/database/drivers, it is impossible to say that these classes will work perfectly with datasource ABC, ODBC driver XYZ, on platform LMN. You may run in to some incompatibilities or unsupported features when moving your application from one environment to another. But that is what makes cross-platform programming fun. It is also pinpoints one of the great things about open source software. It can evolve!

The most common difference between different database/ODBC driver manufacturers in regards to these wxODBC classes is the lack of standard error codes being returned to the calling program. Sometimes manufacturers have even changed the error codes between versions of their databases/drivers.

In all the tested databases, every effort has been made to determine the correct error codes and handle them in the class members that need to check for specific error codes (such as TABLE DOES NOT EXIST when you try to open a table that has not been created yet). Adding support for additional databases in the future requires adding an entry for the database in the wxDb::Dbms (p. 11) function, and then handling any error codes returned by the datasource that do not match the expected values.

Databases

Following is a list of known issues and incompatibilities that the wxODBC classes have between different datasources. An up to date listing of known issues can be seen in the comments of the source for wxDb::Dbms (p. 11).

ORACLE

· Currently the only database supported by the wxODBC classes to support VIEWS

DBASE

NOTE: dBase is not a true ODBC datasource. You only have access to as much functionality as the driver can emulate.

· Does not support the SQL_TIMESTAMP structure

· Supports only one cursor and one connect (apparently? with Microsoft driver only?)

· Does not automatically create the primary index if the 'keyField' param of SetColDef is TRUE. The user must create ALL indexes from their program with calls to wxDbTable::CreateIndex (p. 48)

· Table names can only be 8 characters long

· Column names can only be 10 characters long

· Currently cannot CREATE a dBase table - bug or limitation of the drivers used??

· Currently cannot insert rows that have integer columns - bug??

SYBASE (all)

· To lock a record during QUERY functions, the reserved word 'HOLDLOCK' must be added after every table name involved in the query/join if that table's matching record(s) are to be locked

· Ignores the keywords 'FOR UPDATE'. Use the HOLDLOCK functionality described above

SYBASE (Enterprise)

· If a column is part of the Primary Key, the column cannot be NULL

· 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 [wxDbTable::CanSelectForUpdate (p. 45)]. 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 wxDbTable::CreateIndex (p. 48) to try to adjust the column definition if it is not defined correctly, but it is experimental (as of wxWindows v2.2.1)

· 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

· Columns which are part of a primary key must be declared as NOT NULL

UNICODE with wxODBC classes

Currently there is no support for Unicode with the wxODBC classes. In fact, Unicode builds must be disabled if wxWindows is compiled with wxUSE_ODBC set to 1 in setup.h

wxODBC - Sample Code 1

Simplest example of establishing/opening a connection to an ODBC datasource, binding variables to the columns for read/write usage, opening an existing table in the datasource, setting the query parameters (where/orderBy/from), querying the datasource, reading each row of the result set, then cleaning up.

NOTE: Not all error trapping is shown here, to reduce the size of the code and to make it more easily readable.

wxDbConnectInf *DbConnectInf = NULL;

wxDb *db = NULL; // The database connection

wxDbTable *table = NULL; // The data table to access

wxChar FirstName[50+1]; // buffer for data from column "FIRST_NAME"

wxChar LastName[50+1]; // buffer for data from column "LAST_NAME"

bool errorOccured = FALSE;

const wxChar tableName[] = "CONTACTS";

const UWORD numTableColumns = 2; // Number of bound columns

FirstName[0] = 0;

LastName[0] = 0;

DbConnectInf = new wxDbConnectInf(NULL,"MyDSN","MyUserName", "MyPassword");

if (!DbConnectInf || !DbConnectInf->GetHenv())

{

wxMessageBox("Unable to allocate an ODBC environment handle",

"DB CONNECTION ERROR", wxOK | wxICON_EXCLAMATION);

return;

}

// Get a database connection from the cached connections

db = wxDbGetConnection(DbConnectInf);

// Create the table connection

table = new wxDbTable(db, tableName, numTableColumns, "",

!wxDB_QUERY_ONLY, "");

//

// Bind the columns that you wish to retrieve. Note that there must be

// 'numTableColumns' calls to SetColDefs(), to match the wxDbTable

// definition

//

// Not all columns need to be bound, only columns whose values are to be

// returned back to the client.

//

table->SetColDefs(0, "FIRST_NAME", DB_DATA_TYPE_VARCHAR, FirstName,

SQL_C_CHAR, sizeof(name), TRUE, TRUE);

table->SetColDefs(1, "LAST_NAME", DB_DATA_TYPE_VARCHAR, LastName,

SQL_C_CHAR, sizeof(LastName), TRUE, TRUE);

// Open the table for access

table->Open();

// Set the WHERE clause to limit the result set to only

// return all rows that have a value of 'GEORGE' in the

// FIRST_NAME column of the table.

table->SetWhereClause("FIRST_NAME = 'GEORGE'");

// Result set will be sorted in ascending alphabetical

// order on the data in the 'LAST_NAME' column of each row

table->SetOrderByClause("LAST_NAME");

// No other tables (joins) are used for this query

table->SetFromClause("");

// Instruct the datasource to perform a query based on the

// criteria specified above in the where/orderBy/from clauses.

if (!table->Query())

{

wxMessageBox("Error on Query()","ERROR!",

wxOK | wxICON_EXCLAMATION);

errorOccured = TRUE;

}

wxString msg;

// Start and continue reading every record in the table

// displaying info about each record read.

while (table->GetNext())

{

msg.Printf("Row #%lu -- First Name : %s Last Name is %s",

table->GetRowNum(), FirstName, LastName);

wxMessageBox(msg, "Data", wxOK | wxICON_INFORMATION, NULL);

}

// If the wxDbTable instance was successfully created

// then delete it as I am done with it now.

if (table)

{

delete table;

table = NULL;

}

// If we have a valid wxDb instance, then free the connection

// (meaning release it back in to the cache of datasource

// connections) for the next time a call to wxDbGetConnection()

// is made.

if (db)

{

wxDbFreeConnection(db);

db = NULL;

}

// The program is now ending, so we need to close

// any cached connections that are still being

// maintained.

wxDbCloseConnections();

// Release the environment handle that was created

// for use with the ODBC datasource connections

delete DbConnectInf;

END OF SAMPLE CODE 1

Index

—~—

~wxDbConnectInf, 35

~wxDbTable, 42

—A—

AllocHenv, 35

Associated non-class functions, 5

—B—

BuildDeleteStmt, 42

BuildSelectStmt, 43, 44

BuildUpdateStmt, 43

—C—

CanSelectForUpdate, 45

CanUpdateByROWID, 45

Catalog, 8

ClearMemberVar, 46

ClearMemberVars, 46

Close, 9

CloseCursor, 47

CommitTrans, 10

Count, 47

CreateIndex, 48

CreateTable, 50

CreateView, 10

—D—

DB_STATUS, 50

Dbms, 11

Delete, 50

DeleteCursor, 51

DeleteMatching, 51

DeleteWhere, 52

Different ODBC Class Libraries in wxWindows, 78

DispAllErrors, 12

DispNextError, 13

DropIndex, 53

DropTable, 53

DropView, 13

—E—

Enumerated types, 2

ExecSql, 14

—F—

Format, 33

FreeHenv, 35

From, 54

—G—

GetAuthStr, 36

GetCatalog, 14, 15

GetColDefs, 54

GetColumnCount, 15

GetColumns, 16

GetCursor, 55

GetData, 17

GetDatabaseName, 18

GetDatasourceName, 18

GetDb, 55

GetDefaultDir, 36

GetDescription, 36

GetDsn, 36

GetFileType, 36

GetFirst, 55

GetFromClause, 55

GetHDBC, 18

GetHenv, 37

GetHENV, 18

GetHSTMT, 19

GetKeyFields, 19

GetLast, 56

GetNewCursor, 56

GetNext, 19, 57

GetNextError, 20

GetNumberOfColumns, 57

GetOrderByClause, 57

GetPassword, 20, 37

GetPrev, 57

GetQueryTableName, 58

GetRowNum, 58

GetTableCount, 20

GetTableName, 59

GetTablePath, 59

GetUid, 37

GetUserID, 37

GetUsername, 20

GetWhereClause, 59

Grant, 21

—I—

Insert, 59

IsColNull, 60

IsCursorClosedOnCommit, 60

IsFwdOnlyCursors, 14, 21

IsOpen, 22

IsQueryOnly, 61

—L—

LogError, 23

—M—

ModifyColumn, 23

—O—

Open, 24, 61

operator --, 76

operator ++, 75

OrderBy, 62

—Q—

Query, 62

QueryBySqlStmt, 64

QueryMatching, 65

QueryOnKeyFields, 67

—R—

Refresh, 67

RollbackTrans, 25

—S—

SetAuthStr, 37

SetColDefs, 68

SetColNull, 71

SetCursor, 70

SetDebugErrorMessages, 26

SetDefaultDir, 37

SetDescription, 38

SetDsn, 38

SetFileType, 38

SetFromClause, 70

SetHenv, 38

SetOrderByClause, 72

SetPassword, 38

SetQueryTimeout, 72

SetSqlLogging, 26

SetUid, 38

SetUserID, 39

SetWhereClause, 73

—T—

TableExists, 27

TablePrivileges, 27

TranslateSqlState, 29

—U—

Update, 74

UpdateWhere, 74

—W—

Where, 75

WriteSqlLog, 29

wxDb, 8, 34

wxDb/wxDbTable wxODBC Overview, 78

wxDb::Catalog, 8

wxDb::Close, 9

wxDb::CommitTrans, 10

wxDb::CreateView, 10

wxDb::Dbms, 11

wxDb::DispAllErrors, 12

wxDb::DispNextError, 13

wxDb::DropView, 13

wxDb::ExecSql, 14

wxDb::FwdOnlyCursors, 14

wxDb::GetCatalog, 15

wxDb::GetColumnCount, 15

wxDb::GetColumns, 16

wxDb::GetData, 17

wxDb::GetDatabaseName, 18

wxDb::GetDatasourceName, 18

wxDb::GetHDBC, 18

wxDb::GetHENV, 18

wxDb::GetHSTMT, 19

wxDb::GetKeyFields, 19

wxDb::GetNext, 19

wxDb::GetNextError, 20

wxDb::GetPassword, 20

wxDb::GetTableCount, 20

wxDb::GetUsername, 20

wxDb::Grant, 21

wxDb::IsFwdOnlyCursors, 21

wxDb::IsOpen, 22

wxDb::LogError, 23

wxDb::ModifyColumn, 23

wxDb::Open, 24

wxDb::RollbackTrans, 25

wxDb::SetDebugErrorMessages, 26

wxDb::SetSqlLogging, 26

wxDb::TableExists, 27

wxDb::TablePrivileges, 27

wxDb::TranslateSqlState, 29

wxDb::WriteSqlLog, 29

wxDb::wxDb, 8

wxDbCloseConnections, 6

wxDbColDef::Initialize, 31

wxDbColFor::Format, 33

wxDbColFor::Initialize, 33

wxDbColInf::Initialize, 32

wxDbConnectInf, 34

wxDbConnectInf::~wxDbConnectInf, 35

wxDbConnectInf::AllocHenv, 35

wxDbConnectInf::FreeHenv, 35

wxDbConnectInf::GetAuthStr, 36

wxDbConnectInf::GetDefaultDir, 36

wxDbConnectInf::GetDescription, 36

wxDbConnectInf::GetDsn, 36

wxDbConnectInf::GetFileType, 36

wxDbConnectInf::GetHenv, 37

wxDbConnectInf::GetPassword, 37

wxDbConnectInf::GetUid, 37

wxDbConnectInf::GetUserID, 37

wxDbConnectInf::Initialize, 36

wxDbConnectInf::SetAuthStr, 37

wxDbConnectInf::SetDefaultDir, 37

wxDbConnectInf::SetDescription, 38

wxDbConnectInf::SetDsn, 38

wxDbConnectInf::SetFileType, 38

wxDbConnectInf::SetHenv, 38

wxDbConnectInf::SetPassword, 38

wxDbConnectInf::SetUid, 38

wxDbConnectInf::SetUserID, 38

wxDbConnectionsInUse, 7

wxDbFreeConnection, 6

wxDbGetConnection, 5

wxDbGetDataSource, 7

wxDbInf::Initialize, 40

wxDbSqlLog, 7

wxDbTable, 41

wxDbTable::BuildDeleteStmt, 42

wxDbTable::BuildSelectStmt, 43

wxDbTable::BuildUpdateStmt, 43

wxDbTable::BuildWhereStmt, 44

wxDbTable::CanSelectForUpdate, 45

wxDbTable::CanUpdateByROWID, 45

wxDbTable::ClearMemberVar, 46

wxDbTable::ClearMemberVars, 46

wxDbTable::CloseCursor, 47

wxDbTable::Count, 47

wxDbTable::CreateIndex, 48

wxDbTable::CreateTable, 50

wxDbTable::DB_STATUS, 50

wxDbTable::Delete, 50

wxDbTable::DeleteCursor, 51

wxDbTable::DeleteMatching, 51

wxDbTable::DeleteWhere, 52

wxDbTable::DropIndex, 53

wxDbTable::DropTable, 53

wxDbTable::From, 54

wxDbTable::GetColDefs, 54

wxDbTable::GetCursor, 55

wxDbTable::GetDb, 55

wxDbTable::GetFirst, 55

wxDbTable::GetFromClause, 55

wxDbTable::GetLast, 56

wxDbTable::GetNewCursor, 56

wxDbTable::GetNext, 57

wxDbTable::GetNumberOfColumns, 57

wxDbTable::GetOrderByClause, 57

wxDbTable::GetPrev, 57

wxDbTable::GetQueryTableName, 58

wxDbTable::GetRowNum, 58

wxDbTable::GetTableName, 58

wxDbTable::GetTablePath, 59

wxDbTable::GetWhereClause, 59

wxDbTable::Insert, 59

wxDbTable::IsColNull, 60

wxDbTable::IsCursorClosedOnCommit, 60

wxDbTable::IsQueryOnly, 61

wxDbTable::Open, 61

wxDbTable::operator --, 75

wxDbTable::operator ++, 75

wxDbTable::OrderBy, 62

wxDbTable::Query, 62

wxDbTable::QueryBySqlStmt, 64

wxDbTable::QueryMatching, 65

wxDbTable::QueryOnKeyFields, 67

wxDbTable::Refresh, 67

wxDbTable::SetColDefs, 68

wxDbTable::SetColNull, 71

wxDbTable::SetCursor, 70

wxDbTable::SetFromClause, 70

wxDbTable::SetOrderByClause, 72

wxDbTable::SetQueryTimeout, 72

wxDbTable::SetWhereClause, 73

wxDbTable::Update, 74

wxDbTable::UpdateWhere, 74

wxDbTable::Where, 75

wxDbTable::wxDbTable, 41, 42

wxDbTableInf::Initialize, 77

wxODBC - Basic Step-By-Step Guide, 83

wxODBC - Compiling, 82

wxODBC - Configuring your system for ODBC use, 81

wxODBC - Known Issues, 91

wxODBC - Sample Code 1, 93

wxODBC Where To Start, 79