wxODBC - Basic Step-By-Step Guide

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

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 provided 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).

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 wxWidgets 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 'to 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.

A more advanced form of opening a connection is to use the connection caching functions that are included with the wxODBC classes. The caching mechanisms perform the same functions as 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 needing to do the coding.

To use the caching function wxDbGetConnection 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 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 is recorded in a linked list of established connections. When a program is finished with a connection, a call to wxDbFreeConnection is made, and the datasource connection will then be tagged as FREE, making it available for the next call to wxDbGetConnection that needs a connection using the same connection information (Dsn, Uid, AuthStr). The cached connections remain cached until a call to wxDbCloseConnections 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 behaviours. 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 and wxDbGetConnection. If manually creating a wxDb instance and opening it, you must pass an existing connection to the wxDb::Open function yourself to gain the performance benefit of copying existing connection settings. The wxDbGetConnection 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 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. The default behavior is determined by the setting wxODBC_FWD_ONLY_CURSORS in setup.h when you compile the wxWidgets 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 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). 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 - the queryOnly setting. If a wxDbTable instance is created as wxDB_QUERY_ONLY, then no inserts/deletes/updates can be performed using this instance of the wxDbTable. Any calls to wxDb::CommitTrans or wxDb::RollbackTrans 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_WXCHAR, sizeof(FirstName), true, true);
    table->SetColDefs(1, "LAST_NAME", DB_DATA_TYPE_VARCHAR, LastName,
                      SQL_C_WXCHAR, 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 (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 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, wxDbTable::GetPrev, 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. 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 is not technically doing anything with the datasource itself. Calling wxDbTable::Open simply does all the housekeeping 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 is likely to 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 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 which 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, 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 (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 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.

\fbox{\vbox{IMPORTANT: The result created by the call to
\htmlref{\it wxDbTable...
...tion 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 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 is called right after the successful call to wxDbTable::Query, it actually returns the first record in the result set.

When wxDbTable::GetNext is called and there are no rows remaining in the result set after the current cursor position, wxDbTable::GetNext (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 verified by calling wxDb::GetTableCount 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 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. 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;

\fbox{\vbox{Remember to never release this environment handle if there are any
connections still using the handle.}}

ymasuda 平成17年11月19日