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 and wxDb::RollbackTrans.)|
|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, wxDbTable::GetFirst, and wxDbTable::GetLast). 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 or wxDb::RollbackTrans 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 wxWidgets ODBC settings in setup.h (see wxODBC - Compiling), cursors can be either forward or backward scrolling. At a minimum, 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. 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). 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 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 function. One example use for additional cursors is 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 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 relying 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.