really applied Robert's patch (and not the converse of it)

[wxWidgets.git] / docs / latex / wx / tdb.tex

\section{Database classes overview}\label{odbcoverview}

Following is a detailed overview of how to use the wxWindows ODBC classes - \helpref{wxDb}{wxdb} 
and \helpref{wxDbTable}{wxdbtable} and their associated functions. These are 
the ODBC classes donated by Remstar International, and are collectively 
referred to herein as the wxODBC classes.

\subsection{wxDb/wxDbTable wxODBC Overview}\label{wxodbcoverview}

Classes: \helpref{wxDb}{wxdb}, \helpref{wxDbTable}{wxdbtable}

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 differently. 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:

\begin{itemize}\itemsep=0pt
\item Oracle (v7, v8, v8i)
\item Sybase (ASA and ASE)
\item MS SQL Server (v7 - minimal testing)
\item MS Access (97 and 2000)
\item MySQL
\item DBase (IV, V)**
\item PostgreSQL
\item INFORMIX
\item VIRTUOSO
\item DB2
\item Interbase
\item Pervasive SQL
\end{itemize}

An up-to-date list can be obtained by looking in the comments of the function 
\helpref{wxDb::Dbms}{wxdbdbms} in db.cpp, or in the enumerated type 
\helpref{wxDBMS}{wxdbenumeratedtypes} 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 \helpref{wxODBC Known Issues}{wxodbcknownissues} section of this 
overview for details.


\subsection{wxODBC Where To Start}\label{wxodbcwheretostart}

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.

\begin{twocollist}\itemsep=0pt
\twocolitem{Datasource}{(usually a database) that contains the data that will be 
accessed by the wxODBC classes.}
\twocolitem{Data table}{The section of the datasource that contains the rows and 
columns of data.}
\twocolitem{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.}
\twocolitem{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 
\helpref{wxDb::CommitTrans}{wxdbcommittrans} and 
\helpref{wxDb::RollbackTrans}{wxdbrollbacktrans}.)}
\twocolitem{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.}
\twocolitem{Columns}{Individual fields associated with each row of a data 
table.}
\twocolitem{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.}
\twocolitem{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.}
\twocolitem{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.}
\twocolitem{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 (
\helpref{wxDbTable::GetPrev}{wxdbtablegetprev}, 
\helpref{wxDbTable::GetFirst}{wxdbtablegetfirst}, and 
\helpref{wxDbTable::GetLast}{wxdbtablegetlast}). If the datasource or the 
ODBC driver only support forward scrolling cursors, your program and logic 
must take this in to account.}
\twocolitem{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 
\helpref{wxDb::CommitTrans}{wxdbcommittrans} or 
\helpref{wxDb::RollbackTrans}{wxdbrollbacktrans} is made.}
\twocolitem{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.}
\end{twocollist}

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 \helpref{wxODBC - Compiling}{wxodbccompiling}), 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 \helpref{wxODBC - Compiling}{wxodbccompiling}). 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 
\helpref{wxDbTable::GetNewCursor}{wxdbtablegetnewcursor} 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.

\subsection{wxODBC - Configuring your system for ODBC use}\label{wxodbcconfiguringyoursystem}

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. Rumour 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 
consider 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 user who configured the DSN.

Under Unix, iODBC is used for implementation of the ODBC API. To compile the 
wxODBC classes, you must first obtain iODBC from \urlref{http://www.iodbc.org}{www.iodbc.org} and install it. 
(Note: wxWindows currently includes a version of 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:

\begin{verbatim}
        [contacts]
        Trace    = Off
        TraceFile= stderr
        Driver   = /usr/local/lib/libmyodbc.so
        DSN      = contacts
        SERVER   = 192.168.1.13
        USER     = qet
        PASSWORD = 
        PORT     = 3306
\end{verbatim}

\subsection{wxODBC - Compiling}\label{wxodbccompiling}

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

\begin{twocollist}\itemsep=0pt
\twocolitem{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.}
\twocolitem{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 \helpref{wxDbGetConnection}{wxdbfunctions} or 
\helpref{wxDb constructor}{wxdbconstr}. The default is 1.}
\twocolitem{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 {\tt\#}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.}
\end{twocollist}

{\it 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.

\normalbox{MORE TO COME}

{\it Under Unix}
--with-odbc flag for configure

\normalbox{MORE TO COME}

\subsection{wxODBC - Basic Step-By-Step Guide}\label{wxodbcstepbystep}

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

\begin{itemize}\itemsep=0pt
\item Define datasource connection information
\item Get a datasource connection
\item Create table definition
\item Open the table
\item Use the table
\item Close the table
\item Close the datasource connection
\item Release the ODBC environment handle
\end{itemize}

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 
\helpref{wxODBC - Sample Code}{wxodbcsamplecode1}).

{\bf 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 '\' 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.

{\bf 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.

\begin{verbatim}
    wxDbConnectInf DbConnectInf;
    DbConnectInf.SetDsn("MyDSN");
    DbConnectInf.SetUserID("MyUserName");
    DbConnectInf.SetPassword("MyPassword");
    DbConnectInf.SetDefaultDir("");
\end{verbatim}

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

\begin{verbatim}
    if (DbConnectInf.AllocHenv())
    {
        wxMessageBox("Unable to allocate an ODBC environment handle",
                     "DB CONNECTION ERROR", wxOK | wxICON_EXCLAMATION);
        return;
    } 
\end{verbatim}

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.

\begin{verbatim}
    wxDbConnectInf *DbConnectInf;

	 DbConnectInf = new wxDbConnectInf(NULL, "MyDSN", "MyUserName",
	                                   "MyPassword", "");
\end{verbatim}

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.

\begin{verbatim}
    wxDb *db = new wxDb(DbConnectInf->GetHenv());

    opened = db->Open(DbConnectInf);
\end{verbatim}

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 \helpref{wxDb::Open}{wxdbopen}.

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 \helpref{wxDbGetConnection}{wxdbfunctions} to get 
a connection to a datasource, simply call it with a single parameter of the 
type wxDbConnectInf:

\begin{verbatim}
    db = wxDbGetConnection(DbConnectInf);
\end{verbatim}

The wxDb pointer that is returned is both initialized and opened. If 
something failed in creating or opening the connection, the return value 
from \helpref{wxDbGetConnection}{wxdbfunctions} 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 
\helpref{wxDbGetConnection}{wxdbfunctions} is recorded in a linked list of established 
connections. When a program is finished with a connection, a call to 
\helpref{wxDbFreeConnection}{wxdbfunctions} is made, and the datasource 
connection will then be tagged as FREE, making it available for the next 
call to \helpref{wxDbGetConnection}{wxdbfunctions} that needs a connection 
using the same connection information (Dsn, Uid, AuthStr). The cached 
connections remain cached until a call to \helpref{wxDbCloseConnections}{wxdbfunctions} 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 
\helpref{wxDb::Open}{wxdbopen} and \helpref{wxDbGetConnection}{wxdbfunctions}. 
If manually creating a wxDb instance and opening it, you must pass an existing 
connection to the \helpref{wxDb::Open}{wxdbopen} function yourself to gain the performance 
benefit of copying existing connection settings. The 
\helpref{wxDbGetConnection}{wxdbfunctions} 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, \helpref{wxDbGetConnection}{wxdbfunctions} 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 {\tt 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 \helpref{wxDbGetConnection}{wxdbfunctions} 
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.

{\bf Create Table Definition}

Data can be accessed in a datasource's tables directly through various 
functions of the wxDb class (see \helpref{wxDb::GetData}{wxdbgetdata}). 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.

\begin{verbatim}
    table = new wxDbTable(db, tableName, numTableColumns, "", 
                          !wxDB_QUERY_ONLY, "");
\end{verbatim}

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 
{\tt wxDB\_QUERY\_ONLY}, then no inserts/deletes/updates can be performed 
using this instance of the wxDbTable. Any calls to \helpref{wxDb::CommitTrans}{wxdbcommittrans} 
or \helpref{wxDb::RollbackTrans}{wxdbrollbacktrans} against the datasource 
connection used by this wxDbTable instance are ignored by this instance. If 
the wxDbTable instance is created with {\tt !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. 

\begin{verbatim}
    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);
\end{verbatim}

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 \helpref{wxDbTable::GetNext}{wxdbtablegetnext} (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 
\helpref{wxDbTable::SetColDefs}{wxdbtablesetcoldefs} 
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. \helpref{wxDbTable::GetNext}{wxdbtablegetnext},
\helpref{wxDbTable::GetPrev}{wxdbtablegetprev}, etc). The variables are not 
initialized to any data by the wxODBC classes, and they still contain 
undefined data after a call to \helpref{wxDbTable::Query}{wxdbtablequery}. 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.

{\bf Open the table}

Opening the table is not technically doing anything with the datasource 
itself. Calling \helpref{wxDbTable::Open}{wxdbtableopen} 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).

\begin{verbatim}
    if (!table->Open())
    {
        // An error occurred opening (setting up) the table
    }
\end{verbatim}

The only reason that a call to \helpref{wxDbTable::Open}{wxdbtableopen} 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 \helpref{wxDbTable::Open}{wxdbtableopen} function are logged to the error log 
if SQL logging is turned on for the classes.

{\bf 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.

\begin{verbatim}
    // 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("");
\end{verbatim}

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:

\begin{verbatim}
    // 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
    }
\end{verbatim}

Typically, when an error occurs when calling \helpref{wxDbTable::Query}{wxdbtablequery}, it is a 
syntax problem in the WHERE clause that was specified. The exact SQL 
(datasource-specific) reason for what caused the failure of \helpref{wxDbTable::Query}{wxdbtablequery} 
(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 \helpref{wxDbTable::Query}{wxdbtablequery} 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.

\normalbox{IMPORTANT: The result created by the call to 
\helpref{wxDbTable::Query}{wxdbtablequery} can take 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 \helpref{wxDbTable::Refresh}{wxdbtablerefresh}. 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 \helpref{wxDbTable::Query}{wxdbtablequery} 
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.

\begin{verbatim}
    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);
    }
\end{verbatim}

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 
\helpref{wxDbTable::GetNext}{wxdbtablegetnext} is called right after the successful 
call to \helpref{wxDbTable::Query}{wxdbtablequery}, it actually returns the first record 
in the result set. 

When \helpref{wxDbTable::GetNext}{wxdbtablegetnext} is called and there are 
no rows remaining in the result set after the current cursor position, 
\helpref{wxDbTable::GetNext}{wxdbtablegetnext} (as well as all the other 
wxDbTable::GetXxxxx() functions) will return false.

{\bf 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.

\begin{verbatim}
    if (table)
    {
        delete table;
        table = NULL;
    }
\end{verbatim}

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

{\bf Close the datasource connection}

After all tables that have been using a datasource connection have been 
closed (this can be verified by calling \helpref{wxDb::GetTableCount}{wxdbgettablecount} 
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:

\begin{verbatim}
    if (db)
    {
        db->Close();
        delete db;
        db = NULL;
    }
\end{verbatim}

If the program used the \helpref{wxDbGetConnection}{wxdbfunctions} function to get a datasource 
connection, the following is the code that should be used to free the 
connection(s):

\begin{verbatim}
    if (db)
    {
        wxDbFreeConnection(db);
        db = NULL;
    }
\end{verbatim}

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

\begin{verbatim}
    wxDbCloseConnections();
\end{verbatim}

{\bf 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:

\begin{verbatim}
    DbConnectInf->FreeHenv();
\end{verbatim}

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.

\begin{verbatim}
    delete DbConnectInf;
\end{verbatim}

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

\subsection{wxODBC - Known Issues}\label{wxodbcknownissues}

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 \helpref{wxDb::Dbms}{wxdbdbms} function, and then handling any error codes 
returned by the datasource that do not match the expected values.

{\bf 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 \helpref{wxDb::Dbms}{wxdbdbms}.

{\it ORACLE}
\begin{itemize}\itemsep=0pt
\item Currently the only database supported by the wxODBC classes to support VIEWS
\end{itemize}

{\it DBASE}

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

\begin{itemize}\itemsep=0pt
\item Does not support the SQL\_TIMESTAMP structure
\item Supports only one cursor and one connect (apparently? with Microsoft driver only?)
\item 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 \helpref{wxDbTable::CreateIndex}{wxdbtablecreateindex}
\item Table names can only be 8 characters long
\item Column names can only be 10 characters long
\item Currently cannot CREATE a dBase table - bug or limitation of the drivers used??
\item Currently cannot insert rows that have integer columns - bug??
\end{itemize}

{\it SYBASE (all)}
\begin{itemize}\itemsep=0pt
\item 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
\item Ignores the keywords 'FOR UPDATE'. Use the HOLDLOCK functionality described above
\end{itemize}

{\it SYBASE (Enterprise)}
\begin{itemize}\itemsep=0pt
\item If a column is part of the Primary Key, the column cannot be NULL
\item Maximum row size is somewhere in the neighborhood of 1920 bytes
\end{itemize}

{\it mySQL}
\begin{itemize}\itemsep=0pt
\item If a column is part of the Primary Key, the column cannot be NULL.
\item Cannot support selecting for update [\helpref{wxDbTable::CanSelectForUpdate}{wxdbtablecanselectforupdate}]. Always returns false.
\item 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 \helpref{wxDbTable::CreateIndex}{wxdbtablecreateindex} to try to adjust the column definition if it is not defined correctly, but it is experimental (as of wxWindows v2.2.1)
\item Does not support sub-queries in SQL statements
\end{itemize}

{\it POSTGRES}
\begin{itemize}\itemsep=0pt
\item Does not support the keywords 'ASC' or 'DESC' as of release v6.5.0
\item Does not support sub-queries in SQL statements
\end{itemize}

{\it DB2}
\begin{itemize}\itemsep=0pt
\item Columns which are part of a primary key must be declared as NOT NULL
\end{itemize}

{\bf UNICODE with wxODBC classes}

The ODBC classes support for Unicode is yet in early experimental stage and
hasn't been tested extensively. It might work for you or it might not: please
report the bugs/problems you have encountered in the latter case.

\subsection{wxODBC - Sample Code}\label{wxodbcsamplecode1}

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.

\begin{verbatim}
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{verbatim}

\subsection{A selection of SQL commands}\label{sqlcommands}

The following is a very brief description of some common SQL commands, with
examples.

\wxheading{See also}

\helpref{Database classes overview}{odbcoverview}

\subsubsection{Create}

Creates a table.

Example:

\begin{verbatim}
CREATE TABLE Book
 (BookNumber     INTEGER     PRIMARY KEY
 , CategoryCode  CHAR(2)     DEFAULT 'RO' NOT NULL
 , Title         VARCHAR(100) UNIQUE
 , NumberOfPages SMALLINT
 , RetailPriceAmount NUMERIC(5,2)
 )
\end{verbatim}

\subsubsection{Insert}

Inserts records into a table.

Example:

\begin{verbatim}
INSERT INTO Book
  (BookNumber, CategoryCode, Title)
  VALUES(5, 'HR', 'The Lark Ascending')
\end{verbatim}

\subsubsection{Select}

The Select operation retrieves rows and columns from a table. The criteria
for selection and the columns returned may be specified.

Examples:

{\tt SELECT * FROM Book}

Selects all rows and columns from table Book.

{\tt SELECT Title, RetailPriceAmount FROM Book WHERE RetailPriceAmount > 20.0}

Selects columns Title and RetailPriceAmount from table Book, returning only
the rows that match the WHERE clause.

{\tt SELECT * FROM Book WHERE CatCode = 'LL' OR CatCode = 'RR'}

Selects all columns from table Book, returning only
the rows that match the WHERE clause.

{\tt SELECT * FROM Book WHERE CatCode IS NULL}

Selects all columns from table Book, returning only rows where the CatCode column
is NULL.

{\tt SELECT * FROM Book ORDER BY Title}

Selects all columns from table Book, ordering by Title, in ascending order. To specify
descending order, add DESC after the ORDER BY Title clause.

{\tt SELECT Title FROM Book WHERE RetailPriceAmount >= 20.0 AND RetailPriceAmount <= 35.0}

Selects records where RetailPriceAmount conforms to the WHERE expression.

\subsubsection{Update}

Updates records in a table.

Example:

{\tt UPDATE Incident SET X = 123 WHERE ASSET = 'BD34'}

This example sets a field in column `X' to the number 123, for the record
where the column ASSET has the value `BD34'.