\section{Database classes overview}\label{odbcoverview}
-Classes: \helpref{wxDatabase}{wxdatabase}, \helpref{wxRecordSet}{wxrecordset}, \helpref{wxQueryCol}{wxquerycol},
-\rtfsp\helpref{wxQueryField}{wxqueryfield}
-
-\normalboxd{IMPORTANT NOTE: The ODBC classes are a preliminary release and incomplete. Please
-take this into account when using them. Feedback and bug fixes are appreciated,
-as always. The classes are being developed by Olaf Klein (oklein@smallo.ruhr.de) and
-Patrick Halke (patrick@zaphod.ruhr.de).}
-
-wxWindows provides a set of classes for accessing a subset of Microsoft's ODBC (Open Database Connectivity)
-product. Currently, this wrapper is available under MS Windows only, although
-ODBC may appear on other platforms, and a generic or product-specific SQL emulator for the ODBC
-classes may be provided in wxWindows at a later date.
-
-ODBC presents a unified API (Application Programmer's Interface) to a
-wide variety of databases, by interfacing indirectly to each database or
-file via an ODBC driver. The language for most of the database
-operations is SQL, so you need to learn a small amount of SQL as well as
-the wxWindows ODBC wrapper API. Even though the databases may not be
-SQL-based, the ODBC drivers translate SQL into appropriate operations
-for the database or file: even text files have rudimentry ODBC support,
-along with dBASE, Access, Excel and other file formats.
+Following is a detailed overview of how to use the wxWidgets 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.
-The run-time files for ODBC are bundled with many existing database
-packages, including MS Office. The required header files, sql.h and
-sqlext.h, are bundled with several compilers including MS VC++ and
-Watcom C++. The only other way to obtain these header files is from the
-ODBC SDK, which is only available with the MS Developer Network CD-ROMs
--- at great expense. If you have odbc.dll, you can make the required
-import library odbc.lib using the tool `implib'. You need to have odbc.lib
-in your compiler library path.
+\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}
-The minimum you need to distribute with your application is odbc.dll, which must
-go in the Windows system directory. For the application to function correctly,
-ODBC drivers must be installed on the user's machine. If you do not use the database
-classes, odbc.dll will be loaded but not called (so ODBC does not need to be
-setup fully if no ODBC calls will be made).
+Before you are able to read data from a data table in a datasource, you must
+have a connection to the datasource. Each datasource connection may be used
+to open multiple tables all on the same connection (number of tables open are
+dependent on the driver, datasource configuration and the amount of memory on
+the client workstation). Multiple connections can be opened to the same
+datasource by the same client (number of concurrent connections is dependent
+on the driver and datasource configuration).
+
+When a query is performed, the client passes the query to the ODBC driver,
+and the driver then translates it and passes it along to the datasource. The
+database engine (in most cases - exceptions are text and dBase files) running
+on the machine hosting the database does all the work of performing the search
+for the requested data. The client simply waits for a status to come back
+through the ODBC driver from the datasource.
+
+Depending on the ODBC driver, the result set either remains "queued" on the
+database server side, or is transferred to the machine that the driver is
+queued on. The client does not receive this data. The client must request
+some or all of the result set to be returned before any data rows are
+returned to the client application.
+
+Result sets do not need to include all columns of every row matching the
+query. In fact, result sets can actually be joinings of columns from two
+or more data tables, may have derived column values, or calculated values
+returned.
+
+For each result set, a cursor is maintained (typically by the database)
+which keeps track of where in the result set the user currently is.
+Depending on the database, ODBC driver, and how you configured the
+wxWidgets ODBC settings in setup.h (see \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: wxWidgets 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:
-A sample is distributed with wxWindows in {\tt samples/odbc}. You will need to install
-the sample dbf file as a data source using the ODBC setup utility, available from
-the control panel if ODBC has been fully installed.
+\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{Procedures for writing an ODBC application}
+\subsection{wxODBC - Compiling}\label{wxodbccompiling}
-You first need to create a wxDatabase object. If you want to get information
-from the ODBC manager instead of from a particular database (for example
-using \helpref{wxRecordSet::GetDataSources}{wxrecordsetgetdatasources}), then you
-do not need to call \helpref{wxDatabase::Open}{wxdatabaseopen}.
-If you do wish to connect to a datasource, then call wxDatabase::Open.
-You can reuse your wxDatabase object, calling wxDatabase::Close and wxDatabase::Open
-multiple times.
+The wxWidgets setup.h file has several settings in it pertaining to compiling
+the wxODBC classes.
-Then, create a wxRecordSet object for retrieving or sending information.
-For ODBC manager information retrieval, you can create it as a dynaset (retrieve the
-information as needed) or a snapshot (get all the data at once).
-If you are going to call \helpref{wxRecordSet::ExecuteSQL}{wxrecordsetexecutesql}, you need to create it as a snapshot.
-Dynaset mode is not yet implemented for user data.
+\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}{wxdbctor}. 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 wxWidgets, 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}
-Having called a function such as wxRecordSet::ExecuteSQL or
-wxRecordSet::GetDataSources, you may have a number of records
-associated with the recordset, if appropriate to the operation. You can
-now retrieve information such as the number of records retrieved and the
-actual data itself. Use \helpref{wxRecordSet::GetFieldData}{wxrecordsetgetfielddata} or
-\helpref{wxRecordSet::GetFieldDataPtr}{wxrecordsetgetfielddataptr} to get the data or a pointer to it, passing
-a column index or name. The data returned will be for the current
-record. To move around the records, use \helpref{wxRecordSet::MoveNext}{wxrecordsetmovenext},
-\rtfsp\helpref{wxRecordSet::MovePrev}{wxrecordsetmoveprev} and associated functions.
+{\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 wxWidgets, this library should already be included for use with
+makefile.b32, makefile.vc, and makefile.g95.
+
+\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 wxWidgets data class wxDbConnectInf exists for holding all of these
+values, plus some others that may be desired.
+
+The 'Henv' member is the environment handle used to access memory for use by the
+ODBC driver. Use of this member is described below in the "Getting a Connection
+to the Datasource" section.
+
+The 'Dsn' must exactly match the datasource name used to configure the ODBC
+datasource (in the ODBC Administrator (MSW only) or in the .odbc.ini file).
+
+The 'Uid' is the User ID that is to be used to log in to the datasource. This
+User ID must already have been created and assigned rights within the
+datasource to which you are connecting. The user that the connection is
+establish by will determine what rights and privileges the datasource
+connection will allow the program to have when using the connection that
+this connection information was used to establish. Some datasources are
+case sensitive for User IDs, and though the wxODBC classes attempt to hide
+this from you by manipulating whatever data you pass in to match the
+datasource's needs, it is always best to pass the 'Uid' in the case that
+the datasource requires.
+
+The 'AuthStr' is the password for the User ID specified in the 'Uid' member.
+As with the 'Uid', some datasources are case sensitive (in fact most are).
+The wxODBC classes do NOT try to manage the case of the 'AuthStr' at all.
+It is passed verbatim to the datasource, so you must use the case that the
+datasource is expecting.
+
+The 'defaultDir' member is used with file based datasources (i.e. dBase,
+FoxPro, text files). It contains a full path to the location where the
+data table or file is located. When setting this value, use forward
+slashes '/' rather than backslashes '\' to avoid compatibility differences
+between ODBC drivers.
+
+The other fields are currently unused. The intent of these fields are that
+they will be used to write our own ODBC Administrator type program that will
+work on both MSW and Un*x systems, regardless of the datasource. Very little
+work has been done on this to date.
+
+{\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.
-You can use the same recordset for multiple operations, or delete
-the recordset and create a new one.
+\begin{verbatim}
+ wxDbConnectInf DbConnectInf;
+ DbConnectInf.SetDsn("MyDSN");
+ DbConnectInf.SetUserID("MyUserName");
+ DbConnectInf.SetPassword("MyPassword");
+ DbConnectInf.SetDefaultDir("");
+\end{verbatim}
-Note that when you delete a wxDatabase, any associated recordsets
-also get deleted, so beware of holding onto invalid pointers.
+To allocate an environment handle for the ODBC connection to use, the
+wxDbConnectInf class has a datasource independent method for creating
+the necessary handle:
-\subsection{wxDatabase overview}\label{wxdatabaseoverview}
+\begin{verbatim}
+ if (DbConnectInf.AllocHenv())
+ {
+ wxMessageBox("Unable to allocate an ODBC environment handle",
+ "DB CONNECTION ERROR", wxOK | wxICON_EXCLAMATION);
+ return;
+ }
+\end{verbatim}
-\overview{Database classes overview}{odbcoverview}
+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.
-Class: \helpref{wxDatabase}{wxdatabase}
+A shorter form of doing the above steps is encapsulated into the
+long form of the constructor for wxDbConnectInf.
-Every database object represents an ODBC connection. To do anything useful
-with a database object you need to bind a wxRecordSet object to it. All you
-can do with wxDatabase is opening/closing connections and getting some info
-about it (users, passwords, and so on).
+\begin{verbatim}
+ wxDbConnectInf *DbConnectInf;
-\subsection{wxQueryCol overview}\label{wxquerycoloverview}
+ DbConnectInf = new wxDbConnectInf(NULL, "MyDSN", "MyUserName",
+ "MyPassword", "");
+\end{verbatim}
-\overview{Database classes overview}{odbcoverview}
+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.
-Class: \helpref{wxQueryCol}{wxquerycol}
+Once the wxDbConnectInf instance is initialized, you are ready to
+connect to the datasource.
-Every data column is represented by an instance of this class.
-It contains the name and type of a column and a list of wxQueryFields where
-the real data is stored. The links to user-defined variables are stored
-here, as well.
+To manually create datasource connections, you must create a wxDb
+instance, and then open it.
-\subsection{wxQueryField overview}\label{wxqueryfieldoverview}
+\begin{verbatim}
+ wxDb *db = new wxDb(DbConnectInf->GetHenv());
-\overview{Database classes overview}{odbcoverview}
+ opened = db->Open(DbConnectInf);
+\end{verbatim}
-Class: \helpref{wxQueryField}{wxqueryfield}
+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}.
-As every data column is represented by an instance of the class wxQueryCol,
-every data item of a specific column is represented by an instance of
-wxQueryField. Each column contains a list of wxQueryFields. If wxRecordSet is
-of the type wxOPEN\_TYPE\_DYNASET, there will be only one field for each column,
-which will be updated every time you call functions like wxRecordSet::Move
-or wxRecordSet::GoTo. If wxRecordSet is of the type wxOPEN\_TYPE\_SNAPSHOT,
-all data returned by an ODBC function will be loaded at once and the number
-of wxQueryField instances for each column will depend on the number of records.
+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.
-\subsection{wxRecordSet overview}\label{wxrecordsetoverview}
+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:
-\overview{Database classes overview}{odbcoverview}
+\begin{verbatim}
+ db = wxDbGetConnection(DbConnectInf);
+\end{verbatim}
-Class: \helpref{wxRecordSet}{wxrecordset}
+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 wxWidgets library. The library default is to only support
+forward scrolling cursors only, though this can be overridden by parameters
+for wxDb() constructor or the \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.
-Each wxRecordSet represents a database query. You can make multiple queries
-at a time by using multiple wxRecordSets with a wxDatabase or you can make
-your queries in sequential order using the same wxRecordSet.
+\begin{verbatim}
+ table = new wxDbTable(db, tableName, numTableColumns, "",
+ !wxDB_QUERY_ONLY, "");
+\end{verbatim}
-\subsection{ODBC SQL data types}\label{sqltypes}
+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.
-\overview{Database classes overview}{odbcoverview}
+\begin{verbatim}
+ table->SetColDefs(0, "FIRST_NAME", DB_DATA_TYPE_VARCHAR, FirstName,
+ SQL_C_CHAR, sizeof(FirstName), true, true);
+ table->SetColDefs(1, "LAST_NAME", DB_DATA_TYPE_VARCHAR, LastName,
+ SQL_C_CHAR, sizeof(LastName), true, true);
+\end{verbatim}
-These are the data types supported in ODBC SQL. Note that there are other, extended level conformance
-types, not currently supported in wxWindows.
+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{twocollist}\itemsep=0pt
-\twocolitem{CHAR(n)}{A character string of fixed length {\it n}.}
-\twocolitem{VARCHAR(n)}{A varying length character string of maximum length {\it n}.}
-\twocolitem{LONG VARCHAR(n)}{A varying length character string: equivalent to VARCHAR for the purposes
-of ODBC.}
-\twocolitem{DECIMAL(p, s)}{An exact numeric of precision {\it p} and scale {\it s}.}
-\twocolitem{NUMERIC(p, s)}{Same as DECIMAL.}
-\twocolitem{SMALLINT}{A 2 byte integer.}
-\twocolitem{INTEGER}{A 4 byte integer.}
-\twocolitem{REAL}{A 4 byte floating point number.}
-\twocolitem{FLOAT}{An 8 byte floating point number.}
-\twocolitem{DOUBLE PRECISION}{Same as FLOAT.}
-\end{twocollist}
+\begin{verbatim}
+ if (!table->Open())
+ {
+ // An error occurred opening (setting up) the table
+ }
+\end{verbatim}
-These data types correspond to the following ODBC identifiers:
+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.
-\begin{twocollist}\itemsep=0pt
-\twocolitem{SQL\_CHAR}{A character string of fixed length.}
-\twocolitem{SQL\_VARCHAR}{A varying length character string.}
-\twocolitem{SQL\_DECIMAL}{An exact numeric.}
-\twocolitem{SQL\_NUMERIC}{Same as SQL\_DECIMAL.}
-\twocolitem{SQL\_SMALLINT}{A 2 byte integer.}
-\twocolitem{SQL\_INTEGER}{A 4 byte integer.}
-\twocolitem{SQL\_REAL}{A 4 byte floating point number.}
-\twocolitem{SQL\_FLOAT}{An 8 byte floating point number.}
-\twocolitem{SQL\_DOUBLE}{Same as SQL\_FLOAT.}
-\end{twocollist}
+{\bf Use the table}
-\subsection{A selection of SQL commands}\label{sqlcommands}
+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.
-\overview{Database classes overview}{odbcoverview}
+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 wxWidgets, 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 wxWidgets 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, inserting a record, setting query parameters
+(where/orderBy/from), querying the datasource, reading each row of the
+result set, deleting a record, releasing the connection, then cleaning up.
+
+NOTE: Very basic error handling is shown here, to reduce the size of the
+code and to make it more easily readable. The HandleError() function uses the wxDbLogExtendedErrorMsg() function for retrieving database error messages.
+
+\begin{verbatim}
+// ----------------------------------------------------------------------------
+// HEADERS
+// ----------------------------------------------------------------------------
+#include "wx/log.h" // #included to enable output of messages only
+#include "wx/dbtable.h"
+
+// ----------------------------------------------------------------------------
+// FUNCTION USED FOR HANDLING/DISPLAYING ERRORS
+// ----------------------------------------------------------------------------
+// Very generic error handling function.
+// If a connection to the database is passed in, then we retrieve all the
+// database errors for the connection and add them to the displayed message
+int HandleError(wxString errmsg, wxDb *pDb=NULL)
+{
+ // Retrieve all the error message for the errors that occurred
+ wxString allErrors;
+ if (!pDb == NULL)
+ // Get the database errors and append them to the error message
+ allErrors = wxDbLogExtendedErrorMsg(errmsg.c_str(), pDb, 0, 0);
+ else
+ allErrors = errmsg;
+
+ // Do whatever you wish with the error message here
+ // wxLogDebug() is called inside wxDbLogExtendedErrorMsg() so this
+ // console program will show the errors in the console window,
+ // but these lines will show the errors in RELEASE builds also
+ wxFprintf(stderr, wxT("\n%s\n"), allErrors.c_str());
+ fflush(stderr);
+
+ return 1;
+}
+
+
+// ----------------------------------------------------------------------------
+// entry point
+// ----------------------------------------------------------------------------
+int main(int argc, char **argv)
+{
+wxDbConnectInf *DbConnectInf = NULL; // DB connection information
+
+wxDb *db = NULL; // Database connection
+
+wxDbTable *table = NULL; // Data table to access
+const wxChar tableName[] = wxT("USERS"); // Name of database table
+const UWORD numTableColumns = 2; // Number table columns
+wxChar FirstName[50+1]; // column data: "FIRST_NAME"
+wxChar LastName[50+1]; // column data: "LAST_NAME"
+
+wxString msg; // Used for display messages
+
+// -----------------------------------------------------------------------
+// DEFINE THE CONNECTION HANDLE FOR THE DATABASE
+// -----------------------------------------------------------------------
+DbConnectInf = new wxDbConnectInf(NULL,
+ wxT("CONTACTS-SqlServer"),
+ wxT("sa"),
+ wxT("abk"));
+
+// Error checking....
+if (!DbConnectInf || !DbConnectInf->GetHenv())
+{
+ return HandleError(wxT("DB ENV ERROR: Cannot allocate ODBC env handle"));
+}
+
+
+// -----------------------------------------------------------------------
+// GET A DATABASE CONNECTION
+// -----------------------------------------------------------------------
+db = wxDbGetConnection(DbConnectInf);
+
+if (!db)
+{
+ return HandleError(wxT("CONNECTION ERROR - Cannot get DB connection"));
+}
+
+
+// -----------------------------------------------------------------------
+// DEFINE THE TABLE, AND THE COLUMNS THAT WILL BE ACCESSED
+// -----------------------------------------------------------------------
+table = new wxDbTable(db, tableName, numTableColumns, wxT(""),
+ !wxDB_QUERY_ONLY, wxT(""));
+//
+// Bind the columns that you wish to retrieve. Note that there must be
+// 'numTableColumns' calls to SetColDefs(), to match the wxDbTable def
+//
+// Not all columns need to be bound, only columns whose values are to be
+// returned back to the client.
+//
+table->SetColDefs(0, wxT("FIRST_NAME"), DB_DATA_TYPE_VARCHAR, FirstName,
+ SQL_C_CHAR, sizeof(FirstName), true, true);
+table->SetColDefs(1, wxT("LAST_NAME"), DB_DATA_TYPE_VARCHAR, LastName,
+ SQL_C_CHAR, sizeof(LastName), true, true);
+
+
+// -----------------------------------------------------------------------
+// CREATE (or RECREATE) THE TABLE IN THE DATABASE
+// -----------------------------------------------------------------------
+if (!table->CreateTable(true)) //NOTE: No CommitTrans is required
+{
+ return HandleError(wxT("TABLE CREATION ERROR: "), table->GetDb());
+}
+
+
+// -----------------------------------------------------------------------
+// OPEN THE TABLE FOR ACCESS
+// -----------------------------------------------------------------------
+if (!table->Open())
+{
+ return HandleError(wxT("TABLE OPEN ERROR: "), table->GetDb());
+}
+
+
+// -----------------------------------------------------------------------
+// INSERT A NEW ROW INTO THE TABLE
+// -----------------------------------------------------------------------
+wxStrcpy(FirstName, wxT("JULIAN"));
+wxStrcpy(LastName, wxT("SMART"));
+if (!table->Insert())
+{
+ return HandleError(wxT("INSERTION ERROR: "), table->GetDb());
+}
+
+// Must commit the insert to write the data to the DB
+table->GetDb()->CommitTrans();
+
+
+// -----------------------------------------------------------------------
+// RETRIEVE ROWS FROM THE TABLE BASED ON SUPPLIED CRITERIA
+// -----------------------------------------------------------------------
+// Set the WHERE clause to limit the result set to return
+// all rows that have a value of 'JULIAN' in the FIRST_NAME
+// column of the table.
+table->SetWhereClause(wxT("FIRST_NAME = 'JULIAN'"));
+
+// Result set will be sorted in ascending alphabetical
+// order on the data in the 'LAST_NAME' column of each row
+table->SetOrderByClause(wxT("LAST_NAME"));
+
+// No other tables (joins) are used for this query
+table->SetFromClause(wxT(""));
+
+// Instruct the datasource to perform a query based on the
+// criteria specified above in the where/orderBy/from clauses.
+if (!table->Query())
+{
+ return HandleError(wxT("QUERY ERROR: "), table->GetDb());
+}
+
+// Loop through all rows matching the query criteria until
+// there are no more records to read
+while (table->GetNext())
+{
+ msg.Printf(wxT("Row #%lu -- First Name : %s Last Name is %s"),
+ table->GetRowNum(), FirstName, LastName);
+
+ // Code to display 'msg' here
+ wxLogMessage(wxT("\n%s\n"), msg.c_str());
+}
+
+
+// -----------------------------------------------------------------------
+// DELETE A ROW FROM THE TABLE
+// -----------------------------------------------------------------------
+// Select the row which has FIRST_NAME of 'JULIAN' and LAST_NAME
+// of 'SMART', then delete the retrieved row
+//
+if (!table->DeleteWhere(wxT("FIRST_NAME = 'JULIAN' and LAST_NAME = 'SMART'")))
+{
+ return HandleError(wxT("DELETION ERROR: "), table->GetDb());
+}
+
+// Must commit the deletion to the database
+table->GetDb()->CommitTrans();
+
+
+// -----------------------------------------------------------------------
+// TAKE CARE OF THE ODBC CLASS INSTANCES THAT WERE BEING USED
+// -----------------------------------------------------------------------
+// If the wxDbTable instance was successfully created
+// then delete it as we are done with it now.
+wxDELETE(table);
+
+// Free the cached connection
+// (meaning release it back in to the cache of datasource
+// connections) for the next time a call to wxDbGetConnection()
+// is made.
+wxDbFreeConnection(db);
+db = NULL;
+
+
+// -----------------------------------------------------------------------
+// CLEANUP BEFORE EXITING APP
+// -----------------------------------------------------------------------
+// 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
+wxDELETE(DbConnectInf);
+
+wxUnusedVar(argc); // Here just to prevent compiler warnings
+wxUnusedVar(argv); // Here just to prevent compiler warnings
+
+return 0;
+}
+\end{verbatim}
+
+\subsection{A selection of SQL commands}\label{sqlcommands}
The following is a very brief description of some common SQL commands, with
examples.
-\subsubsection{Create}
+\wxheading{See also}
+
+\helpref{Database classes overview}{odbcoverview}
+
+\subsubsection{Create}\label{odbccreateexample}
Creates a table.
)
\end{verbatim}
-\subsubsection{Insert}
+\subsubsection{Insert}\label{odbcinsertexample}
Inserts records into a table.
VALUES(5, 'HR', 'The Lark Ascending')
\end{verbatim}
-\subsubsection{Select}
+\subsubsection{Select}\label{odbcselectexample}
The Select operation retrieves rows and columns from a table. The criteria
for selection and the columns returned may be specified.
Examples:
-\verb$SELECT * FROM Book$
+{\tt SELECT * FROM Book}
Selects all rows and columns from table Book.
-\verb$SELECT Title, RetailPriceAmount FROM Book WHERE RetailPriceAmount > 20.0$
+{\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.
-\verb$SELECT * FROM Book WHERE CatCode = 'LL' OR CatCode = 'RR'$
+{\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.
-\verb$SELECT * FROM Book WHERE CatCode IS NULL$
+{\tt SELECT * FROM Book WHERE CatCode IS NULL}
Selects all columns from table Book, returning only rows where the CatCode column
is NULL.
-\verb$SELECT * FROM Book ORDER BY Title$
+{\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.
-\verb$SELECT Title FROM Book WHERE RetailPriceAmount >= 20.0 AND RetailPriceAmount <= 35.0$
+{\tt SELECT Title FROM Book WHERE RetailPriceAmount >= 20.0 AND RetailPriceAmount <= 35.0}
Selects records where RetailPriceAmount conforms to the WHERE expression.
-\subsubsection{Update}
+\subsubsection{Update}\label{odbcupdateexample}
Updates records in a table.
Example:
-\verb$UPDATE Incident SET X = 123 WHERE ASSET = 'BD34'$
+{\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'.
-
-
projects / wxWidgets.git / blobdiff