X-Git-Url: https://git.saurik.com/wxWidgets.git/blobdiff_plain/9a05fd8d137bbe26acd4f86adf6d62c55424822c..43e995b6e39d41fd8a26f8bb9e6a6ef54bbcd324:/docs/latex/wx/tdb.tex diff --git a/docs/latex/wx/tdb.tex b/docs/latex/wx/tdb.tex index 2682a6647d..71a470d202 100644 --- a/docs/latex/wx/tdb.tex +++ b/docs/latex/wx/tdb.tex @@ -1,166 +1,1159 @@ \section{Database classes overview}\label{odbcoverview} -Classes: \helpref{wxDatabase}{wxdatabase}, \helpref{wxRecordSet}{wxrecordset}, \helpref{wxQueryCol}{wxquerycol}, -\rtfsp\helpref{wxQueryField}{wxqueryfield} - -\normalboxd{Note that more sophisticated ODBC classes are provided by the Remstar -database classes: please see the separate HTML and Word documentation.} - -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 DB2 +\item DBase (IV, V)** +\item Firebird +\item INFORMIX +\item Interbase +\item MS SQL Server (v7 - minimal testing) +\item MS Access (97, 2000, 2002, and 2003) +\item MySQL (2.x and 3.5 - use the 2.5x drivers though) +\item Oracle (v7, v8, v8i) +\item Pervasive SQL +\item PostgreSQL +\item Sybase (ASA and ASE) +\item XBase Sequiter +\item VIRTUOSO +\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_WXCHAR, sizeof(FirstName), true, true); + table->SetColDefs(1, "LAST_NAME", DB_DATA_TYPE_VARCHAR, LastName, + SQL_C_WXCHAR, 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 which 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 +into some incompatibilities or unsupported features when moving your +application from one environment to another. But that is what makes +cross-platform programming fun. It 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} + +As of v2.6 of wxWidgets, the wxODBC classes now fully support the compilation +and use of the classes in a Unicode build of wxWidgets, assuming the compiler +and OS on which the program will be compiled/run is Unicode capable. + +The one major difference in writing code that can be compiled in either +unicode or non-unicode builds that is specific to the wxODBC classes is to +use the SQL\_C\_WXCHAR datatype for string columns rather than SQL\_C\_CHAR or +SQL\_C\_WCHAR. + +\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_WXCHAR, sizeof(FirstName), true, true); +table->SetColDefs(1, wxT("LAST_NAME"), DB_DATA_TYPE_VARCHAR, LastName, + SQL_C_WXCHAR, 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. @@ -176,7 +1169,7 @@ CREATE TABLE Book ) \end{verbatim} -\subsubsection{Insert} +\subsubsection{Insert}\label{odbcinsertexample} Inserts records into a table. @@ -188,51 +1181,49 @@ INSERT INTO Book 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'. - -