X-Git-Url: https://git.saurik.com/wxWidgets.git/blobdiff_plain/9a05fd8d137bbe26acd4f86adf6d62c55424822c..dd0fba48accaa064eba35a54a65a1f0b42b5c862:/docs/latex/wx/tdb.tex?ds=sidebyside diff --git a/docs/latex/wx/tdb.tex b/docs/latex/wx/tdb.tex index 2682a6647d..a3c33edee1 100644 --- a/docs/latex/wx/tdb.tex +++ b/docs/latex/wx/tdb.tex @@ -1,10 +1,1094 @@ \section{Database classes overview}\label{odbcoverview} +\normalboxd{The more sophisticated wxODBC classes (wxDb/wxDbTable) are the +recommended classes for doing database/ODBC work with wxWindows. These new +classes replace the wxWindows v1.6x classes wxDatabase. Documentation for the +old wxDatabase class and its associated classes is still +included in the class documentation and in this overview section, but support +for these old classes has been phased out, and all future development work +is being done solely on the new wxDb/wxDbTable classes.} + +\subsection{Different ODBC Class Libraries in wxWindows} + +Following is detailed overview of how to use the wxWindows ODBC classes - \helpref{wxDb}{wxdb} +and \helpref{wxDbTable}{wxdbtable} and their associated functions. These are +the ODBC classes donated by Remstar International, and are collectively +referred to herein as the wxODBC classes. Since their initial inclusion with +wxWindows v2.x, they have become the recommended wxWindows classes for database +access. + +An older version of some classes ported over from wxWindows v1.68 still exist +(see \helpref{wxDatabase}{wxdatabase} in odbc.cpp), but are now deprecated in favor of the more +robust and comprehensive wxDb/wxDbTable classes. All current and future +feature development, as well as active debugging, are only being done on +the wxODBC classes. Documentation for the older classes is still provided +in this manual. The \helpref{wxDatabase overview}{wxdatabaseoverview} of the +older classes follows the overview of the new classes. + +\subsection{wxDb/wxDbTable wxODBC Overview}\label{wxodbcoverview} + +Classes: \helpref{wxDb}{wxdb}, \helpref{wxDbTable}{wxdbtable} + +The wxODBC classes were designed for database independence. Although SQL and +ODBC both have standards which define the minimum requirements they must +support to be in compliance with specifications, different database vendors +may implement things slightly different. One example of this is that Oracle +requires all user names for the datasources to be supplied in uppercase +characters. In situations like this, the wxODBC classes have been written +to make this transparent to the programmer when using functions that require +database specific syntax. + +Currently several major databases, along with other widely used databases, +have been tested and supported through the wxODBC classes. The list of +supported databases is certain to grow as more users start implementing +software with these classes, but at the time of the writing of this document, +users have successfully used the classes with the following datasources: + +\begin{itemize}\itemsep=0pt +\item Oracle (v7, v8, v8i) +\item Sybase (ASA and ASE) +\item MS SQL Server (v7 - minimal testing) +\item MS Access (97 and 2000) +\item MySQL +\item DBase (IV, V)** +\item PostgreSQL +\item INFORMIX +\item VIRTUOSO +\item DB2 +\item Interbase +\item Pervasive SQL +\end{itemize} + +An up-to-date list can be obtained by looking in the comments of the function +\helpref{wxDb::Dbms}{wxdbdbms} in db.cpp, or in the enumerated type +\helpref{wxDBMS}{wxdbenumeratedtypes} in db.h. + +**dBase is not truly an ODBC datasource, but there are drivers which can +emulate much of the functionality of an ODBC connection to a dBase table. +See the \helpref{wxODBC Known Issues}{wxodbcknownissues} section of this +overview for details. + + +\subsection{wxODBC Where To Start}\label{wxodbcwheretostart} + +First, if you are not familiar with SQL and ODBC, go to your local bookstore +and pick up a good book on each. This documentation is not meant to teach +you many details about SQL or ODBC, though you may learn some just from +immersion in the subject. + +If you have worked with non-SQL/ODBC datasources before, there are some +things you will need to un-learn. First some terminology as these phrases will +be used heavily in this section of the manual. + +\begin{twocollist}\itemsep=0pt +\twocolitem{Datasource}{(usually a database) that contains the data that will be +accessed by the wxODBC classes.} +\twocolitem{Data table}{The section of the datasource that contains the rows and +columns of data.} +\twocolitem{ODBC driver}{The middle-ware software that interprets the ODBC +commands sent by your application and converts them to the SQL format expected +by the target datasource.} +\twocolitem{Datasource connection}{An open pipe between your application and +the ODBC driver which in turn has a connection to the target datasource. +Datasource connections can have a virtually unlimited number of wxDbTable +instances using the same connect (dependent on the ODBC driver). A separate +connection is not needed for each table (the exception is for isolating +commits/rollbacks on different tables from affecting more than the desired +table. See the class documentation on +\helpref{wxDb::CommitTrans}{wxdbcommittrans} and +\helpref{wxDb::RollbackTrans}{wxdbrollbacktrans}.} +\twocolitem{Rows}{Similar to records in old relational databases, a row is a +collection of one instance of each column of the data table that are all +associated with each other.} +\twocolitem{Columns}{Individual fields associated with each row of a data +table.} +\twocolitem{Query}{Request from the client to the datasource asking for +the data that matches the requirements specified in the users request. When +a query is performed, the datasource performs the lookup of the rows with +satisfy the query, and creates a result set.} +\twocolitem{Result set}{The data which matches the requirements specified +in a query sent to the datasource. Dependent on drivers, a result set +typically remains at the datasource (no data is transmitted to the ODBC driver) +until the client actually instructs the ODBC driver to retrieve it.} +\twocolitem{Cursor}{a logical pointer into the result set that a query +generates, indicating the next record that will be returned to the client +when a request for the next record is made.} +\twocolitem{Scrolling cursors}{Scrolling refers to the movement of cursors +through the result set. Cursors can always scroll forward sequentially in +the result set (FORWARD ONLY scrolling cursors). With Forward only scrolling +cursors, once a row in the result set has been returned to the ODBC driver +and on to the client, there is no way to have the cursor move backward in +the result set to look at the row that is previous to the current row in +the result set. If BACKWARD scrolling cursors are supported by both the +ODBC driver and the datasource that are being used, then backward +scrolling cursor functions may be used ( +\helpref{wxDbTable::GetPrev}{wxdbtablegetprev}, +\helpref{wxDbTable::GetFirst}{wxdbtablegetfirst}, and +\helpref{wxDbTable::GetLast}{wxdbtablegetlast}). If the datasource or the +ODBC driver only support forward scrolling cursors, your program and logic +must take this in to account.} +\twocolitem{Commit/Rollback}{Commit will physically save +insertions/deletions/updates, while rollback basically does an undo of +everything done against the datasource connection that has not been +previously committed. Note that Commit and Rollbacks are done on a +connection, not on individual tables. All tables which use a shared +connection to the datasource are all committed/rolled back at the same +time when a call to +\helpref{wxDb::CommitTrans}{wxdbcommittrans} or +\helpref{wxDb::RollbackTrans}{wxdbrollbacktrans} is made.} +\twocolitem{Index}{Indexes are datasource maintained lookup structures +that allow the datasource to quickly locate data rows based on the values +of certain columns. Without indexes, the datasource would need to do a +sequential search of a table every time a query request is made. Proper +unique key index construction can make datasource queries nearly instantaneous.} +\end{twocollist} + +Before you are able to read data from a data table in a datasource, you must +have a connection to the datasource. Each datasource connection may be used +to open multiple tables all on the same connection (number of tables open are +dependent on the driver, datasource configuration and the amount of memory on +the client workstation). Multiple connections can be opened to the same +datasource by the same client (number of concurrent connections is dependent +on the driver and datasource configuration). + +When a query is performed, the client passes the query to the ODBC driver, +and the driver then translates it and passes it along to the datasource. The +database engine (in most cases - exceptions are text and dBase files) running +on the machine hosting the database does all the work of performing the search +for the requested data. The client simply waits for a status to come back +through the ODBC driver from the datasource. + +Depending on the ODBC driver, the result set either remains "queued" on the +database server side, or is transferred to the machine that the driver is +queued on. The client does not receive this data. The client must request +some or all of the result set to be returned before any data rows are +returned to the client application. + +Result sets do not need to include all columns of every row matching the +query. In fact, result sets can actually be joinings of columns from two +or more data tables, may have derived column values, or calculated values +returned. + +For each result set, a cursor is maintained (typically by the database) +which keeps track of where in the result set the user currently is. +Depending on the database, ODBC driver, and how you configured the +wxWindows ODBC settings in setup.h (see \helpref{wxODBC - Compiling}{wxodbccompiling}), cursors can be +either forward or backward scrolling. At a minim, cursors must scroll +forward. For example, if a query resulted in a result set with 100 rows, +as the data is read by the client application, it will read row 1, then 2, +then 3, etc, etc. With forward only cursors, once the cursor has moved to +the next row, the previous row cannot be accessed again without re-querying +the datasource for the result set over again. Backward scrolling cursors +allow you to request the previous row from the result set, actually +scrolling the cursor backward. + +Backward scrolling cursors are not supported on all database/driver +combinations. For this reason, forward-only cursors are the default in +the wxODBC classes. If your datasource does support backward scrolling +cursors and you wish to use them, make the appropriate changes in setup.h +to enable them (see \helpref{wxODBC - Compiling}{wxodbccompiling}). For greatest portability between +datasources, writing your program in such a way that it only requires +forward scrolling cursors is your best bet. On the other hand, if you are +focusing on using only datasources that support backward scrolling cursors, +potentially large performance benefits can be gained from using them. + +There is a limit to the number of cursors that can be open on each connection +to the datasource, and usually a maximum number of cursors for the datasource +itself. This is all dependent on the database. Each connection that is +opened (each instance of a wxDb) opens a minimum of 5 cursors for on creation +that are required for things such as updates/deletions/rollbacks/queries. +Cursors are a limited resource, so use care in creating large numbers of +cursors. + +Additional cursors can be created if necessary with the +\helpref{wxDbTable::GetNewCursor}{wxdbtablegetnewcursor} function. One example +use for additional cursors are to track multiple scroll points in result +sets. By creating a new cursor, a program could request a second result set +from the datasource while still maintaining the original cursor position in +the first result set. + +Different than non-SQL/ODBC datasources, when a program performs an +insertion, deletion, or update (or other SQL functions like altering +tables, etc) through ODBC, the program must issue a "commit" to the +datasource to tell the datasource that the action(s) it has been told to +perform are to be recorded as permanent. Until a commit is performed, +any other programs that query the datasource will not see the changes that +have been made (although there are databases that can be configured to +auto-commit). NOTE: With most all datasources, until the commit is +performed, any cursor that is open on that same datasource connection +will be able to see the changes that are uncommitted. Check your +database's documentation/configuration to verify this before counting on it +though. + +A rollback is basically an UNDO command on the datasource connection. When +a rollback is issued, the datasource will flush all commands it has been told +to do since the last commit that was performed. + +NOTE: Commits/Rollbacks are done on datasource connections (wxDb instances) +not on the wxDbTable instances. This means that if more than one table +shares the same connection, and a commit or rollback is done on that +connection, all pending changes for ALL tables using that connection are +committed/rolled back. + + + +\subsection{wxODBC - Configuring your system for ODBC use}\label{wxodbcconfiguringyoursystem} + +Before you are able to access a datasource, you must have installed and +configured an ODBC driver. Doing this is system specific, so it will not be +covered in detail here. But here are a few details to get you started. + +Most database vendors provide at least a minimal ODBC driver with their +database product. In practice, many of these drivers have proven to be slow +and/or incomplete. Rumor has it that this is because the vendors do not want +you using the ODBC interface to their products, they want you to use their +applications to access the data. + +Whatever the reason, for database intensive applications, you may want to +think of using a third-party ODBC driver for your needs. One example of a +third party set of ODBC drivers that has been heavily tested and used is +Rogue Wave's drivers. Rogue Wave has drivers available for many different +platforms and databases. + +Under Microsoft Windows, install the ODBC driver you are planning to use. You +will then use the ODBC Administrator in the Control Panel to configure an +instance of the driver for your intended datasource. Note that with all +flavors of NT, this configuration can be set up as a System or User DSN +(datasource name). Configuring it as a system resource will make it +available to all users (if you are logged in as 'administrator'), otherwise +the datasource will only be available to the who configured the DSN. + +Under Unix, iODBC is used for implementation of the ODBC API. To compile the +wxODBC classes, you must first obtain (http://www.iodbc.org) and install iODBC. +Then you must create the file "~/.odbc.ini" (or optionally create +"/etc/odbc.ini" for access for all users on the system). This file contains +the settings for your system/datasource. Below is an example section of a +odbc.ini file for use with the "samples/db" sample program using MySQL: + +\begin{verbatim} + [contacts] + Trace = Off + TraceFile= stderr + Driver = /usr/local/lib/libmyodbc.so + DSN = contacts + SERVER = 192.168.1.13 + USER = qet + PASSWORD = + PORT = 3306 +\end{verbatim} + + + +\subsection{wxODBC - Compiling}\label{wxodbccompiling} + +The wxWindows setup.h file has several settings in it pertaining to compiling +the wxODBC classes. + +\begin{twocollist}\itemsep=0pt +\twocolitem{wxUSE\_ODBC}{This must be set to 1 in order for the compiler to +compile the wxODBC classes. Without setting this to 1, there will be no +access to any of the wxODBC classes. The default is 0.} +\twocolitem{wxODBC\_FWD\_ONLY\_CURSORS}{When a new database connection is +requested, this setting controls the default of whether the connection allows +only forward scrolling cursors, or forward and backward scrolling cursors +(see the section in "WHERE TO START" on cursors for more information on +cursors). This default can be overridden by passing a second parameter to +either the \helpref{wxDbGetConnection}{wxdbfunctions} or +\helpref{wxDb constructor}{wxdbconstr}. The default is 1.} +\twocolitem{wxODBC\_BACKWARD\_COMPATABILITY}{Between v2.0 and 2.2, massive +renaming efforts were done to the ODBC classes to get naming conventions +similar to those used throughout wxWindows, as well as to preface all wxODBC +classes names and functions with a wxDb preface. Because this renaming would +affect applications written using the v2.0 names, this compile-time directive +was added to allow those programs written for v2.0 to still compile using the +old naming conventions. These deprecated names are all #define'd to their +corresponding new function names at the end of the db.cpp/dbtable.cpp source +files. These deprecated class/function names should not be used in future +development, as at some point in the future they will be removed. The default +is 0.} +\end{twocollist} + +{\it Under MS Windows} + +You are required to include the "odbc32.lib" provided by your compiler vendor +in the list of external libraries to be linked in. If using the makefiles +supplied with wxWindows, this library should already be included for use with +makefile.b32, makefile.vc, and makefile.g95. + +You cannot compile the wxODBC classes under Win16 - sorry. + +\normalbox{MORE TO COME} + +{\it Under Unix} +--with-iodbc flag for configure + +\normalbox{MORE TO COME} + + + +\subsection{wxODBC - Basic Step-By-Step Guide}\label{wxodbcstepbystep} + +To use the classes in an application, there are eight basic steps: + +\begin{itemize}\itemsep=0pt +\item Define datasource connection information +\item Get a datasource connection +\item Create table definition +\item Open the table +\item Use the table +\item Close the table +\item Close the datasource connection +\item Release the ODBC environment handle +\end{itemize} + +Following each of these steps is detailed to explain the step, and to +hopefully mention as many of the pitfalls that beginning users fall in +to when first starting to use the classes. Throughout the steps, small +snippets of code are shown to show the syntax of performing the step. A +complete code snippet is provided at the end of this overview that shows a +complete working flow of all these steps (see +\helpref{wxODBC - Sample Code #1}{wxodbcsamplecode1}). + + +{\bf Define datasource connection information} + +To be able to connect to a datasource through the ODBC driver, a program must +supply a minimum of three pieces of information: Datasource name, User ID, and +Authorization string (password). A fourth piece of information, a default +directory indicating where the data file is stored, is required for Text and +dBase drivers for ODBC. + +The wxWindows data class wxDbConnectInf exists for holding all of these +values, plus some others that may be desired. + +The 'Henv' member is the environment handle used to access memory for use by the +ODBC driver. Use of this member is described below in the "Getting a Connection +to the Datasource" section. + +The 'Dsn' must exactly match the datasource name used to configure the ODBC +datasource (in the ODBC Administrator (MSW only) or in the .odbc.ini file). + +The 'Uid' is the User ID that is to be used to log in to the datasource. This +User ID must already have been created and assigned rights within the +datasource to which you are connecting. The user that the connection is +establish by will determine what rights and privileges the datasource +connection will allow the program to have when using the connection that +this connection information was used to establish. Some datasources are +case sensitive for User IDs, and though the wxODBC classes attempt to hide +this from you by manipulating whatever data you pass in to match the +datasource's needs, it is always best to pass the 'Uid' in the case that +the datasource requires. + +The 'AuthStr' is the password for the User ID specified in the 'Uid' member. +As with the 'Uid', some datasources are case sensitive (in fact most are). +The wxODBC classes do NOT try to manage the case of the 'AuthStr' at all. +It is passed verbatim to the datasource, so you must use the case that the +datasource is expecting. + +The 'defaultDir' member is used with file based datasources (i.e. dBase, +FoxPro, text files). It contains a full path to the location where the +data table or file is located. When setting this value, use forward +slashes '/' rather than backslashes '\' to avoid compatibility differences +between ODBC drivers. + +The other fields are currently unused. The intent of these fields are that +they will be used to write our own ODBC Administrator type program that will +work on both MSW and Un*x systems, regardless of the datasource. Very little +work has been done on this to date. + + +{\bf Get a Datasource Connection} + +There are two methods of establishing a connection to a datasource. You +may either manually create your own wxDb instance and open the connection, +or you may use the caching functions provided with the wxODBC classes to +create/maintain/delete the connections. + +Regardless of which method you use, you must first have a fully populated +wxDbConnectInf object. In the wxDbConnectInf instance, provide a valid +Dns, Uid, and AuthStr (along with a 'defaultDir' if necessary). Before +using this though, you must allocate an environment handle to the 'Henv' +member. + +\begin{verbatim} + wxDbConnectInf DbConnectInf; + DbConnectInf.SetDsn,"MyDSN"); + DbConnectInf.SetUserID,"MyUserName"); + DbConnectInf.SetPassword("MyPassword"); + DbConnectInf.SetDefaultDir(""); +\end{verbatim} + +To allocate an environment handle for the ODBC connection to use, the +wxDbConnectInf class has a datasource independent method for creating +the necessary handle: + +\begin{verbatim} + if (DbConnectInf.AllocHenv()) + { + wxMessageBox("Unable to allocate an ODBC environment handle", + "DB CONNECTION ERROR", wxOK | wxICON_EXCLAMATION); + return; + } +\end{verbatim} + +When the wxDbConnectInf::AllocHenv() function is called successfully, a +value of TRUE will be returned. A value of FALSE means allocation failed, +and the handle will be undefined. + +A shorter form of doing the above steps is encapsulated into the +long form of the constructor for wxDbConnectInf. + +\begin{verbatim} + wxDbConnectInf *DbConnectInf; + + DbConnectInf = new wxDbConnectInf(NULL, "MyDSN", "MyUserName", + "MyPassword", ""); +\end{verbatim} + +This shorthand form of initializing the constructor passes a NULL for the SQL +environment handle, telling the constructor to allocate a handle during +construction. This handle is also managed for the life of wxDbConnectInf +instance, and is freed automatically upon destruction of the instance. + +Once the wxDbConnectInf instance is initialized, you are ready to +connect to the datasource. + +To manually create datasource connections, you must create a wxDb +instance, and then open it. + +\begin{verbatim} + wxDb *db = new wxDb(DbConnectInf->GetHenv()); + + opened = db->Open(DbConnectInf); +\end{verbatim} + +The first line does the house keeping needed to initialize all +the members of the wxDb class. The second line actually sends the request +to the ODBC driver to open a connection to its associated datasource using +the parameters supplied in the call to \helpref{wxDb::Open}{wxdbopen}. + +A more advanced form of opening a connection is to used the connection +caching functions that are included with the wxODBC classes. The caching +mechanisms do the same functions are the manual approach to opening a +connection, but they also manage each connection they have created, +re-using them and cleaning them up when they are closed, without you +programmatically needing to do the coding. + +To use the caching function \helpref{wxDbGetConnection}{wxdbfunctions} to get +a connection to a datasource, simply call it with a single parameter of the +type wxDbConnectInf: + +\begin{verbatim} + db = wxDbGetConnection(DbConnectInf); +\end{verbatim} + +The wxDb pointer that is returned is both initialized and opened. If +something failed in creating or opening the connection, the return value +from \helpref{wxDbGetConnection}{wxdbfunctions} will be NULL. + +The connection that is returned is either a new connection, or it is a +"free" connection from the cache of connections that the class maintains +that was no longer in use. Any wxDb instance created with a call to +\helpref{wxDbGetConnection}{wxdbfunctions} is kept track of in a linked list of established +connections. When a program is done with a connection, a call to +\helpref{wxDbFreeConnection}{wxdbfunctions} is made, and the datasource +connection will then be tagged as FREE, making it available for the next +call to \helpref{wxDbGetConnection}{wxdbfunctions} that needs a connection +using the same connection information (Dsn, Uid, AuthStr). The cached +connections remain cached until a call to \helpref{wxDbCloseConnections}{wxdbfunctions} is made, +at which time all cached connections are closed and deleted. + +Besides the obvious advantage of using the single command caching routine to +obtain a datasource connection, using cached connections can be quite a +performance boost as well. Each time that a new connection is created +(not retrieved from the cache of free connections), the wxODBC classes +perform many queries against the datasource to determine the datasource's +datatypes and other fundamental behaviors. Depending on the hardware, +network bandwidth, and datasource speed, this can in some cases take a +few seconds to establish the new connection (with well balanced systems, +it should only be a fraction of a second). Re-using already established +datasource connections rather than creating/deleting, creating/deleting +connections can be quite a time saver. + +Another time saver is the "copy connection" features of both +\helpref{wxDb::Open}{wxdbopen} and \helpref{wxDbGetConnection}{wxdbfunctions}. +If manually creating a wxDb instance and opening it, you must pass an existing +connection to the \helpref{wxDb::Open}{wxdbopen} function yourself to gain the performance +benefit of copying existing connection settings. The +\helpref{wxDbGetConnection}{wxdbfunctions} function automatically does this +for you, checking the Dsn, Uid, and AuthStr parameters when you request +a connection for any existing connections that use those same settings. +If one is found, \helpref{wxDbGetConnection}{wxdbfunctions} copies the datasource settings for +datatypes and other datasource specific information that was previously +queried, rather than re-querying the datasource for all those same settings. + +One final note on creating a connection. When a connection is created, it +will default to only allowing cursor scrolling to be either forward only, +or both backward and forward scrolling cursors. The default behavior is +determined by the setting {\tt wxODBC\_FWD\_ONLY\_CURSORS} in setup.h when you +compile the wxWindows library. The library default is to only support +forward scrolling cursors only, though this can be overridden by parameters +for wxDb() constructor or the \helpref{wxDbGetConnection}{wxdbfunctions} +function. All datasources and ODBC drivers must support forward scrolling +cursors. Many datasources support backward scrolling cursors, and many +ODBC drivers support backward scrolling cursors. Before planning on using +backward scrolling cursors, you must be certain that both your datasource +and ODBC driver fully support backward scrolling cursors. See the small +blurb about "Scrolling cursors" in the definitions at the beginning of +this overview, or other details of setting the cursor behavior in the wxDb +class documentation. + + +{\bf Create Table Definition} + +Data can be accessed in a datasource's tables directly through various +functions of the wxDb class (see \helpref{wxDb::GetData}{wxdbgetdata}). But to make life much +simpler, the wxDbTable class encapsulates all of the SQL specific API calls +that would be necessary to do this, wrapping it in an intuitive class of APIs. + +The first step in accessing data in a datasource's tables via the wxDbTable +class is to create a wxDbTable instance. + +\begin{verbatim} + table = new wxDbTable(db, tableName, numTableColumns, "", + !wxDB_QUERY_ONLY, ""); +\end{verbatim} + +When you create the instance, you indicate the previously established +datasource connection to be used to access the table, the name of the +primary table that is to be accessed with the datasource's tables, how many +columns of each row are going to be returned, the name of the view of the +table that will actually be used to query against (works with Oracle only +at this time), whether the data returned is for query purposes only, and +finally the path to the table, if different than the path specified when +connecting to the datasource. + +Each of the above parameters are described in detail in the wxDbTable +class' description, but one special note here about the fifth +parameter - queryOnly setting. If a wxDbTable instance is created as +{\tt wxDB\_QUERY\_ONLY}, then no inserts/deletes/updates are able to be performed +using this instance of the wxDbTable. Any calls to \helpref{wxDb::CommitTrans}{wxdbcommittrans} +or \helpref{wxDb::RollbackTrans}{wxdbrollbacktrans} against the datasource +connection used by this wxDbTable instance are ignored by this instance. If +the wxDbTable instance is created with {\tt !wxDB\_QUERY\_ONLY} as shown above, +then all the cursors and other overhead associated with being able to +insert/update/delete data in the table are created, and thereby those +operations can then be performed against the associated table with this +wxDbTable instance. + +If a table is to be accessed via a wxDbTable instance, and the table will +only be read from, not written to, there is a performance benefit (not as +many cursors need to be maintained/updated, hence speeding up access times), +as well as a resource savings due to fewer cursors being created for the +wxDbTable instance. Also, with some datasources, the number of +simultaneous cursors is limited. + +When defining the columns to be retrievable by the wxDbTable instance, you +can specify anywhere from one column up to all columns in the table. + +\begin{verbatim} + table->SetColDefs(0, "FIRST_NAME", DB_DATA_TYPE_VARCHAR, FirstName, + SQL_C_CHAR, sizeof(name), TRUE, TRUE); + table->SetColDefs(1, "LAST_NAME", DB_DATA_TYPE_VARCHAR, LastName, + SQL_C_CHAR, sizeof(LastName), TRUE, TRUE); +\end{verbatim} + +Notice that column definitions start at index 0 and go up to one less than +the number of columns specified when the wxDbTable instance was created +(in this example, two columns - one with index 0, one with index 1). + +The above lines of code "bind" the datasource columns specified to the +memory variables in the client application. So when the application +makes a call to \helpref{wxDbTable::GetNext}{wxdbtablegetnext} (or any other function that retrieves +data from the result set), the variables that are bound to the columns will +have the column value stored into them. See the +\helpref{wxDbTable::SetColDefs}{wxdbtablesetcoldefs} +class documentation for more details on all the parameters for this function. + +The bound memory variables have undefined data in them until a call to a +function that retrieves data from a result set is made +(e.g. \helpref{wxDbTable::GetNext}{wxdbtablegetnext}, +\helpref{wxDbTable::GetPrev}{wxdbtablegetprev}, etc). The variables are not +initialized to any data by the wxODBC classes, and they still contain +undefined data after a call to \helpref{wxDbTable::Query}{wxdbtablequery}. Only +after a successful call to one of the ::GetXxxx() functions is made do the +variables contain valid data. + +It is not necessary to define column definitions for columns whose data is +not going to be returned to the client. For example, if you want to query +the datasource for all users with a first name of 'GEORGE', but you only want +the list of last names associated with those rows (why return the FIRST\_NAME +column every time when you already know it is 'GEORGE'), you would only have +needed to define one column above. + +You may have as many wxDbTable instances accessing the same table using the +same wxDb instance as you desire. There is no limit imposed by the classes +on this. All datasources supported (so far) also have no limitations on this. + + +{\bf Open the table} + +Opening the table technically is not doing anything with the datasource +itself. Calling \helpref{wxDbTable::Open}{wxdbtableopen} simply does all the +house keeping of checking that the specified table exists, that the current +connected user has at least SELECT privileges for accessing the table, +setting up the requisite cursors, binding columns and cursors, and +constructing the default INSERT statement that is used when a new row is +inserted into the table (non-wxDB\_QUERY\_ONLY tables only). + +\begin{verbatim} + if (!table->Open()) + { + // An error occurred opening (setting up) the table + } +\end{verbatim} + +The only reason that a call to \helpref{wxDbTable::Open}{wxdbtableopen} will likely fail is if the +user has insufficient privileges to even SELECT the table. Other problems +could occur, such as being unable to bind columns, but these other reason +point to some lack of resource (like memory). Any errors generated +internally in the \helpref{wxDbTable::Open}{wxdbtableopen} function are logged to the error log +if SQL logging is turned on for the classes. + + +{\bf Use the table} + +To use the table and the definitions that are now set up, we must first +define what data we want the datasource to collect in to a result set, tell +it where to get the data from, and in what sequence we want the data returned. + +\begin{verbatim} + // the WHERE clause limits/specifies which rows in the table + // are to be returned in the result set + table->SetWhereClause("FIRST_NAME = 'GEORGE'"); + + // Result set will be sorted in ascending alphabetical + // order on the data in the 'LAST_NAME' column of each row + // If the same last name is in the table for two rows, + // sub-sort on the 'AGE' column + table->SetOrderByClause("LAST_NAME, AGE"); + + // No other tables (joins) are used for this query + table->SetFromClause(""); +\end{verbatim} + +The above lines will be used to tell the datasource to return in the result +all the rows in the table whose column "FIRST\_NAME" contains the name +'GEORGE' (note the required use of the single quote around the string +literal) and that the result set will return the rows sorted by ascending +last names (ascending is the default, and can be overridden with the +"DESC" keyword for datasources that support it - "LAST\_NAME DESC"). + +Specifying a blank WHERE clause will result in the result set containing +all rows in the datasource. + +Specifying a blank ORDERBY clause means that the datasource will return +the result set in whatever sequence it encounters rows which match the +selection criteria. What this sequence is can be hard to determine. +Typically it depends on the index that the datasource used to find the +rows which match the WHERE criteria. BEWARE - relying on the datasource +to return data in a certain sequence when you have not provided an ORDERBY +clause will eventually cause a problem for your program. Databases can be +tuned to be COST-based, SPEED-based, or some other basis for how it gets +your result set. In short, if you need your result set returned in a +specific sequence, ask for it that way by providing an ORDERBY clause. + +Using an ORDERBY clause can be a performance hit, as the database must +sort the items before making the result set available to the client. +Creating efficient indexes that cause the data to be "found" in the correct +ORDERBY sequence can be a big performance benefit. Also, in the large +majority of cases, the database will be able to sort the records faster +than your application can read all the records in (unsorted) and then sort +them. Let the database do the work for you! + +Notice in the example above, a column that is not included in the bound +data columns ('AGE') will be used to sub-sort the result set. + +The FROM clause in this example is blanked, as we are not going to be +performing any table joins with this simple query. When the FROM clause +is blank, it is assumed that all columns referenced are coming from +the default table for the wxDbTable instance. + +After the selection criteria have been specified, the program can now +ask the datasource to perform the search and create a result set that +can be retrieved: + +\begin{verbatim} + // Instruct the datasource to perform a query based on the + // criteria specified above in the where/orderBy/from clauses. + if (!table->Query()) + { + // An error occurred performing the query + } +\end{verbatim} + +Typically, when an error occurs when calling \helpref{wxDbTable::Query}{wxdbtablequery}, it is a +syntax problem in the WHERE clause that was specified. The exact SQL +(datasource specific) reason for what caused the failure of \helpref{wxDbTable::Query}{wxdbtablequery} +(and all other operations against the datasource can be found by +parsing the table's database connection's "errorList[]" array member for +the stored text of the error. + +When the \helpref{wxDbTable::Query}{wxdbtablequery} returns TRUE, the +database was able to successfully complete the requested query using the +provided criteria. This does not mean that there are any rows in the +result set, it just mean that the query was successful. + +\normalbox{IMPORTANT: The result created by the call to +\helpref{wxDbTable::Query}{wxdbtablequery} can be one of two forms. It is +either a snapshot of the data at the exact moment that the database +determined the record matched the search criteria, or it is a pointer to +the row that matched the selection criteria. Which form of behavior is +datasource dependent. If it is a snapshot, the data may have changed +since the result set was constructed, so beware if your datasource +uses snapshots and call \helpref{wxDbTable::Refresh}{wxdbtablerefresh}. Most larger brand databases +do not use snapshots, but it is important to mention so that your application +can handle it properly if your datasource does. } + +To retrieve the data, one of the data fetching routines must be used to +request a row from the result set, and to store the data from the result +set into the bound memory variables. After \helpref{wxDbTable::Query}{wxdbtablequery} +has completed successfully, the default/current cursor is placed so it +is pointing just before the first record in the result set. If the +result set is empty (no rows matched the criteria), then any calls to +retrieve data from the result set will return FALSE. + +\begin{verbatim} + wxString msg; + + while (table->GetNext()) + { + msg.Printf("Row #%lu -- First Name : %s Last Name is %s", + table->GetRowNum(), FirstName, LastName); + wxMessageBox(msg, "Data", wxOK | wxICON_INFORMATION, NULL); + } +\end{verbatim} + +The sample code above will read the next record in the result set repeatedly +until the end of the result set has been reached. The first time that +\helpref{wxDbTable::GetNext}{wxdbtablegetnext} is called right after the successful +call to \helpref{wxDbTable::Query}{wxdbtablequery}, it actually returns the first record +in the result set. + +When \helpref{wxDbTable::GetNext}{wxdbtablegetnext} is called and there are +no rows remaining in the result set after the current cursor position, +\helpref{wxDbTable::GetNext}{wxdbtablegetnext} (as well as all the other +wxDbTable::GetXxxxx() functions) will return FALSE. + + +{\bf Close the table} + +When the program is done using a wxDbTable instance, it is as simple as +deleting the table pointer (or if declared statically, letting the +variable go out of scope). Typically the default destructor will take +care of all that is required for cleaning up the wxDbTable instance. + +\begin{verbatim} + if (table) + { + delete table; + table = NULL; + } +\end{verbatim} + +Deleting a wxDbTable instance releases all of its cursors, deletes the +column definitions and frees the SQL environment handles used by the +table (but not the environment handle used by the datasource connection +that the wxDbTable instance was using). + + + +{\bf Close the datasource connection} + +After all tables that have been using a datasource connection have been +closed (this can be checked by calling \helpref{wxDb::GetTableCount}{wxdbgettablecount} +and checking that it returns 0), then you may close the datasource +connection. The method of doing this is dependent on whether the +non-caching or caching method was used to obtain the datasource connection. + +If the datasource connection was created manually (non-cached), closing the +connection is done like this: + +\begin{verbatim} + if (db) + { + db->Close(); + delete db; + db = NULL; + } +\end{verbatim} + +If the program used the \helpref{wxDbGetConnection}{wxdbfunctions} function to get a datasource +connection, the following is the code that should be used to free the +connection(s): + +\begin{verbatim} + if (db) + { + wxDbFreeConnection(db); + db = NULL; + } +\end{verbatim} + +Note that the above code just frees the connection so that it can be +re-used on the next call the \helpref{wxDbGetConnection}{wxdbfunctions}. To actually dispose +of the connection, releasing all of its resources (other than the +environment handle), do the following: + +\begin{verbatim} + wxDbCloseConnections(); +\end{verbatim} + + +{\bf Release the ODBC environment handle} + +Once all of the connections that used the ODBC environment handle (in +this example it was stored in "DbConnectInf.Henv") have been closed, then +it is safe to release the environment handle: + +\begin{verbatim} + DbConnectInf->FreeHenv()); +\end{verbatim} + +Or, if the long form of the constructor was used and the constructor was allowed +to allocate its own SQL environment handle, leaving scope or destruction of the +wxDbConnectInf will free the handle automatically. + +\begin{verbatim} + delete DbConnectInf; +\end{verbatim} + +\normalbox{Remember to never release this environment handle if there are any +connections still using the handle.} + + +\subsection{wxODBC - Known Issues}\label{wxodbcknownissues} + +As with creating wxWindows, writing the wxODBC classes was not the simple +task of writing an application to run on a single type of computer system. +The classes need to be cross-platform for different operating systems, and +they also needed to take in to account different database manufacturers and +different ODBC driver manufacturers. Because of all the possible combinations +of OS/database/drivers, it is impossible to say that these classes will work +perfectly with datasource ABC, ODBC driver XYZ, on platform LMN. You may run +in to some incompatibilities or unsupported features when moving your +application from one environment to another. But that is what makes +cross-platform programming fun. It is also pinpoints one of the great +things about open source software. It can evolve! + +The most common difference between different database/ODBC driver +manufacturers in regards to these wxODBC classes is the lack of +standard error codes being returned to the calling program. Sometimes +manufacturers have even changed the error codes between versions of +their databases/drivers. + +In all the tested databases, every effort has been made to determine +the correct error codes and handle them in the class members that need +to check for specific error codes (such as TABLE DOES NOT EXIST when +you try to open a table that has not been created yet). Adding support +for additional databases in the future requires adding an entry for the +database in the \helpref{wxDb::Dbms}{wxdbdbms} function, and then handling any error codes +returned by the datasource that do not match the expected values. + + +{\bf Databases} + +Following is a list of known issues and incompatibilities that the +wxODBC classes have between different datasources. An up to date +listing of known issues can be seen in the comments of the source +for \helpref{wxDb::Dbms}{wxdbdbms}. + +{\it ORACLE} +\begin{itemize}\itemsep=0pt +\item Currently the only database supported by the wxODBC classes to support VIEWS +\end{itemize} + +{\it DBASE} + +NOTE: dBase is not a true ODBC datasource. You only have access to as much +functionality as the driver can emulate. + +\begin{itemize}\itemsep=0pt +\item Does not support the SQL\_TIMESTAMP structure +\item Supports only one cursor and one connect (apparently? with Microsoft driver only?) +\item Does not automatically create the primary index if the 'keyField' param of SetColDef is TRUE. The user must create ALL indexes from their program with calls to \helpref{wxDbTable::CreateIndex}{wxdbtablecreateindex} +\item Table names can only be 8 characters long +\item Column names can only be 10 characters long +\item Currently cannot CREATE a dBase table - bug or limitation of the drivers used?? +\item Currently cannot insert rows that have integer columns - bug?? +\end{itemize} + +{\it SYBASE (all)} +\begin{itemize}\itemsep=0pt +\item To lock a record during QUERY functions, the reserved word 'HOLDLOCK' must be added after every table name involved in the query/join if that table's matching record(s) are to be locked +\item Ignores the keywords 'FOR UPDATE'. Use the HOLDLOCK functionality described above +\end{itemize} + +{\it SYBASE (Enterprise)} +\begin{itemize}\itemsep=0pt +\item If a column is part of the Primary Key, the column cannot be NULL +\item Maximum row size is somewhere in the neighborhood of 1920 bytes +\end{itemize} + +{\it mySQL} +\begin{itemize}\itemsep=0pt +\item If a column is part of the Primary Key, the column cannot be NULL. +\item Cannot support selecting for update [\helpref{wxDbTable::CanSelectForUpdate}{wxdbtablecanselectforupdate}]. Always returns FALSE. +\item Columns that are part of primary or secondary keys must be defined as being NOT NULL when they are created. Some code is added in \helpref{wxDbTable::CreateIndex}{wxdbtablecreateindex} to try to adjust the column definition if it is not defined correctly, but it is experimental (as of wxWindows v2.2.1) +\item Does not support sub-queries in SQL statements +\end{itemize} + +{\it POSTGRES} +\begin{itemize}\itemsep=0pt +\item Does not support the keywords 'ASC' or 'DESC' as of release v6.5.0 +\item Does not support sub-queries in SQL statements +\end{itemize} + +{\it DB2} +\begin{itemize}\itemsep=0pt +\item Columns which are part of a primary key must be declared as NOT NULL +\end{itemize} + + +{\bf UNICODE with wxODBC classes} + +The ODBC classes support for Unicode is yet in early experimental stage and +hasn't been tested extensively. It might work for you or it might not: please +report the bugs/problems you have encountered in the latter case. + +\subsection{wxODBC - Sample Code #1}\label{wxodbcsamplecode1} + +Simplest example of establishing/opening a connection to an ODBC datasource, +binding variables to the columns for read/write usage, opening an +existing table in the datasource, setting the query parameters +(where/orderBy/from), querying the datasource, reading each row of the +result set, then cleaning up. + +NOTE: Not all error trapping is shown here, to reduce the size of the +code and to make it more easily readable. + +\begin{verbatim} +wxDbConnectInf *DbConnectInf = NULL; + +wxDb *db = NULL; // The database connection +wxDbTable *table = NULL; // The data table to access + +wxChar FirstName[50+1]; // buffer for data from column "FIRST_NAME" +wxChar LastName[50+1]; // buffer for data from column "LAST_NAME" + +bool errorOccured = FALSE; + +const wxChar tableName[] = "CONTACTS"; +const UWORD numTableColumns = 2; // Number of bound columns + +FirstName[0] = 0; +LastName[0] = 0; + +DbConnectInf = new wxDbConnectInf(NULL,"MyDSN","MyUserName", "MyPassword"); + +if (!DbConnectInf || !DbConnectInf->GetHenv()) +{ + wxMessageBox("Unable to allocate an ODBC environment handle", + "DB CONNECTION ERROR", wxOK | wxICON_EXCLAMATION); + return; +} + +// Get a database connection from the cached connections +db = wxDbGetConnection(DbConnectInf); + +// Create the table connection +table = new wxDbTable(db, tableName, numTableColumns, "", + !wxDB_QUERY_ONLY, ""); + +// +// Bind the columns that you wish to retrieve. Note that there must be +// 'numTableColumns' calls to SetColDefs(), to match the wxDbTable definition +// +// Not all columns need to be bound, only columns whose values are to be +// returned back to the client. +// +table->SetColDefs(0, "FIRST_NAME", DB_DATA_TYPE_VARCHAR, FirstName, + SQL_C_CHAR, sizeof(name), TRUE, TRUE); +table->SetColDefs(1, "LAST_NAME", DB_DATA_TYPE_VARCHAR, LastName, + SQL_C_CHAR, sizeof(LastName), TRUE, TRUE); + +// Open the table for access +table->Open(); + +// Set the WHERE clause to limit the result set to only +// return all rows that have a value of 'GEORGE' in the +// FIRST_NAME column of the table. +table->SetWhereClause("FIRST_NAME = 'GEORGE'"); + +// Result set will be sorted in ascending alphabetical +// order on the data in the 'LAST_NAME' column of each row +table->SetOrderByClause("LAST_NAME"); + +// No other tables (joins) are used for this query +table->SetFromClause(""); + +// Instruct the datasource to perform a query based on the +// criteria specified above in the where/orderBy/from clauses. +if (!table->Query()) +{ + wxMessageBox("Error on Query()","ERROR!", + wxOK | wxICON_EXCLAMATION); + errorOccured = TRUE; +} + +wxString msg; + +// Start and continue reading every record in the table +// displaying info about each record read. +while (table->GetNext()) +{ + msg.Printf("Row #%lu -- First Name : %s Last Name is %s", + table->GetRowNum(), FirstName, LastName); + wxMessageBox(msg, "Data", wxOK | wxICON_INFORMATION, NULL); +} + +// If the wxDbTable instance was successfully created +// then delete it as I am done with it now. +if (table) +{ + delete table; + table = NULL; +} + +// If we have a valid wxDb instance, then free the connection +// (meaning release it back in to the cache of datasource +// connections) for the next time a call to wxDbGetConnection() +// is made. +if (db) +{ + wxDbFreeConnection(db); + db = NULL; +} + +// The program is now ending, so we need to close +// any cached connections that are still being +// maintained. +wxDbCloseConnections(); + +// Release the environment handle that was created +// for use with the ODBC datasource connections +delete DbConnectInf; + +\end{verbatim} + +\subsection{wxDatabase ODBC class overview [DEPRECATED]}\label{oldwxodbcoverview} + 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.} +\normalboxd{The more sophisticated wxODBC classes (wxDb/wxDbTable) are the +recommended classes for doing database/ODBC work with wxWindows. These new +classes replace the wxWindows v1.6x classes wxDatabase. + +Documentation for the old wxDatabase class and its associated classes is still +included in the class documentation and in this overview section, but support +for these old classes has been phased out, and all future development work +is being done solely on the new wxDb/wxDbTable classes.} 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 @@ -17,7 +1101,7 @@ 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, +for the database or file: even text files have rudimentary ODBC support, along with dBASE, Access, Excel and other file formats. The run-time files for ODBC are bundled with many existing database @@ -39,7 +1123,7 @@ A sample is distributed with wxWindows in {\tt samples/odbc}. You will need to i the sample dbf file as a data source using the ODBC setup utility, available from the control panel if ODBC has been fully installed. -\subsection{Procedures for writing an ODBC application} +\subsection{Procedures for writing an ODBC application using wxDatabase [DEPRECATED]} 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 @@ -71,34 +1155,48 @@ the recordset and create a new one. Note that when you delete a wxDatabase, any associated recordsets also get deleted, so beware of holding onto invalid pointers. -\subsection{wxDatabase overview}\label{wxdatabaseoverview} +\subsection{wxDatabase class overview [DEPRECATED]}\label{wxdatabaseoverview} -\overview{Database classes overview}{odbcoverview} +Class: \helpref{wxDatabase}{wxdatabase} -Class: \helpref{wxDatabase}{wxdatabase} +\wxheading{DEPRECATED} + +Use \helpref{wxDb}{wxdb} and \helpref{wxDbTable}{wxdbtable} instead. 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). -\subsection{wxQueryCol overview}\label{wxquerycoloverview} +\wxheading{See also} + +\helpref{Database classes overview}{odbcoverview} -\overview{Database classes overview}{odbcoverview} +\subsection{wxQueryCol class overview [DEPRECATED]}\label{wxquerycoloverview} Class: \helpref{wxQueryCol}{wxquerycol} +\wxheading{DEPRECATED} + +Use \helpref{wxDb}{wxdb} and \helpref{wxDbTable}{wxdbtable} instead. + 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. -\subsection{wxQueryField overview}\label{wxqueryfieldoverview} +\wxheading{See also} -\overview{Database classes overview}{odbcoverview} +\helpref{Database classes overview}{odbcoverview} + +\subsection{wxQueryField class overview [DEPRECATED]}\label{wxqueryfieldoverview} Class: \helpref{wxQueryField}{wxqueryfield} +\wxheading{DEPRECATED} + +Use \helpref{wxDb}{wxdb} and \helpref{wxDbTable}{wxdbtable} instead. + 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 @@ -108,19 +1206,28 @@ 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. -\subsection{wxRecordSet overview}\label{wxrecordsetoverview} +\wxheading{See also} + +\helpref{Database classes overview}{odbcoverview} -\overview{Database classes overview}{odbcoverview} +\subsection{wxRecordSet overview [DEPRECATED]}\label{wxrecordsetoverview} Class: \helpref{wxRecordSet}{wxrecordset} +\wxheading{DEPRECATED} + +Use \helpref{wxDb}{wxdb} and \helpref{wxDbTable}{wxdbtable} instead. + + 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. -\subsection{ODBC SQL data types}\label{sqltypes} +\wxheading{See also} -\overview{Database classes overview}{odbcoverview} +\helpref{Database classes overview}{odbcoverview} + +\subsection{ODBC SQL data types [DEPRECATED]}\label{sqltypes} These are the data types supported in ODBC SQL. Note that there are other, extended level conformance types, not currently supported in wxWindows. @@ -153,13 +1260,19 @@ These data types correspond to the following ODBC identifiers: \twocolitem{SQL\_DOUBLE}{Same as SQL\_FLOAT.} \end{twocollist} -\subsection{A selection of SQL commands}\label{sqlcommands} +\wxheading{See also} + +\helpref{Database classes overview}{odbcoverview} -\overview{Database classes overview}{odbcoverview} +\subsection{A selection of SQL commands [DEPRECATED]}\label{sqlcommands} The following is a very brief description of some common SQL commands, with examples. +\wxheading{See also} + +\helpref{Database classes overview}{odbcoverview} + \subsubsection{Create} Creates a table. @@ -195,31 +1308,31 @@ 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. @@ -229,7 +1342,7 @@ 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'.