+\normalboxd{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.}
+
+\subsection{Different ODBC Class Libraries in wxWindows}
+
+Following is 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. Since their initial inclusion with
+wxWindows v2.x, they have become the recommended wxWindows classes for database
+access.
+
+An older version of some classes ported over from wxWindows v1.68 still exist
+(see \helpref{wxDatabase}{wxdatabase} in odbc.cpp), but are now deprecated in favor of the more
+robust and comprehensive wxDb/wxDbTable classes. All current and future
+feature development, as well as active debugging, are only being done on
+the wxODBC classes. Documentation for the older classes is still provided
+in this manual. The \helpref{wxDatabase overview}{wxdatabaseoverview} of the
+older classes follows the overview of the new 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 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:
+
+\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 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 \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 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
+\helpref{wxDbTable::GetNewCursor}{wxdbtablegetnewcursor} 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.
+
+
+
+\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. 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:
+
+\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 #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-iodbc 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 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
+\helpref{wxODBC - Sample Code #1}{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 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 \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 kept track of in a linked list of established
+connections. When a program is done 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 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
+\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 cursors. 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 - queryOnly setting. If a wxDbTable instance is created as
+{\tt wxDB\_QUERY\_ONLY}, then no inserts/deletes/updates are able to 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 technically is not doing anything with the datasource
+itself. Calling \helpref{wxDbTable::Open}{wxdbtableopen} 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).
+
+\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} 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 \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 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 \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 checked 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 #1}\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{wxDatabase ODBC class overview [DEPRECATED]}\label{oldwxodbcoverview}
+
projects / wxWidgets.git / blobdiff