]> git.saurik.com Git - wxWidgets.git/blame - docs/latex/wx/tdb.tex
Fixed a couple spelling typos
[wxWidgets.git] / docs / latex / wx / tdb.tex
CommitLineData
a660d684
KB
1\section{Database classes overview}\label{odbcoverview}
2
b25ba5b7
GT
3\normalboxd{The more sophisticated wxODBC classes (wxDb/wxDbTable) are the
4recommended classes for doing database/ODBC work with wxWindows. These new
5classes replace the wxWindows v1.6x classes wxDatabase. Documentation for the
6old wxDatabase class and its associated classes is still
7included in the class documentation and in this overview section, but support
8for these old classes has been phased out, and all future development work
9is being done solely on the new wxDb/wxDbTable classes.}
10
11\subsection{Different ODBC Class Libraries in wxWindows}
12
13Following is detailed overview of how to use the wxWindows ODBC classes - \helpref{wxDb}{wxdb}
14and \helpref{wxDbTable}{wxdbtable} and their associated functions. These are
15the ODBC classes donated by Remstar International, and are collectively
16referred to herein as the wxODBC classes. Since their initial inclusion with
17wxWindows v2.x, they have become the standard wxWindows classes for database
18access.
19
20An older version of some classes ported over from wxWindows v1.68 still exist
21(see \helpref{wxDatabase}{wxdatabase} in odbc.cpp), but are now deprecated in favor of the more
22robust and comprehensive wxDb/wxDbTable classes. All current and future
23feature development, as well as active debugging, are only being done on
24the wxODBC classes. Documentation for the older classes is still provided
25in this manual. The \helpref{wxDatabase overview}{wxdatabaseoverview} of the
26older classes follows the overview of the new classes.
27
28\subsection{wxDb/wxDbTable wxODBC Overview}\label{wxodbcoverview}
29
30Classes: \helpref{wxDb}{wxdb}, \helpref{wxDbTable}{wxdbtable}
31
32The wxODBC classes were designed for database independence. Although SQL and
33ODBC both have standards which define the minimum requirements they must
34support to be in compliance with specifications, different database vendors
35may implement things slightly different. One example of this is that Oracle
36requires all user names for the datasources to be supplied in uppercase
37characters. In situations like this, the wxODBC classes have been written
38to make this transparent to the programmer when using functions that require
39database specific syntax.
40
41Currently several major databases, along with other widely used databases,
42have been tested and supported through the wxODBC classes. The list of
43supported databases is certain to grow as more users start implementing
44software with these classes, but at the time of the writing of this document,
45users have successfully used the classes with the following datasources:
46
47\begin{itemize}\itemsep=0pt
48\item Oracle (v7, v8, v8i)
49\item Sybase (ASA and ASE)
50\item MS SQL Server (v7 - minimal testing)
51\item MS Access (97 and 2000)
52\item MySQL
53\item DBase (IV, V)**
54\item PostgreSQL
55\item INFORMIX
56\item VIRTUOSO
57\item DB2
58\end{itemize}
59
60An up-to-date list can be obtained by looking in the comments of the function
61\helpref{wxDb::Dbms}{wxdbdbms} in db.cpp, or in the enumerated type
62\helpref{wxDBMS}{wxdbenumeratedtypes} in db.h.
63
64**dBase is not truly an ODBC datasource, but there are drivers which can
65emulate much of the functionality of an ODBC connection to a dBase table.
66See the \helpref{wxODBC Known Issues}{wxodbcknownissues} section of this
67overview for details.
68
69
70\subsection{wxODBC Where To Start}\label{wxodbcwheretostart}
71
72First, if you are not familiar with SQL and ODBC, go to your local bookstore
73and pick up a good book on each. This documentation is not meant to teach
74you many details about SQL or ODBC, though you may learn some just from
75immersion in the subject.
76
77If you have worked with non-SQL/ODBC datasources before, there are some
78things you will need to un-learn. First some terminology as these phrases will
79be used heavily in this section of the manual.
80
81\begin{twocollist}\itemsep=0pt
82\twocolitem{Datasource}{(usually a database) that contains the data that will be
83accessed by the wxODBC classes.}
84\twocolitem{Data table}{The section of the datasource that contains the rows and
85columns of data.}
86\twocolitem{ODBC driver}{The middle-ware software that interprets the ODBC
87commands sent by your application and converts them to the SQL format expected
88by the target datasource.}
89\twocolitem{Datasource connection}{An open pipe between your application and
90the ODBC driver which in turn has a connection to the target datasource.
91Datasource connections can have a virtually unlimited number of wxDbTable
92instances using the same connect (dependent on the ODBC driver). A separate
93connection is not needed for each table (the exception is for isolating
94commits/rollbacks on different tables from affecting more than the desired
95table. See the class documentation on
96\helpref{wxDb::CommitTrans}{wxdbcommittrans} and
97\helpref{wxDb::RollbackTrans}{wxdbrollbacktrans}.}
98\twocolitem{Rows}{Similar to records in old relational databases, a row is a
99collection of one instance of each column of the data table that are all
100associated with each other.}
101\twocolitem{Columns}{Individual fields associated with each row of a data
102table.}
103\twocolitem{Query}{Request from the client to the datasource asking for
104the data that matches the requirements specified in the users request. When
105a query is performed, the datasource performs the lookup of the rows with
106satisfy the query, and creates a result set.}
107\twocolitem{Result set}{The data which matches the requirements specified
108in a query sent to the datasource. Dependent on drivers, a result set
109typically remains at the datasource (no data is transmitted to the ODBC driver)
110until the client actually instructs the ODBC driver to retrieve it.}
111\twocolitem{Cursor}{a logical pointer into the result set that a query
112generates, indicating the next record that will be returned to the client
113when a request for the next record is made.}
114\twocolitem{Scrolling cursors}{Scrolling refers to the movement of cursors
115through the result set. Cursors can always scroll forward sequentially in
116the result set (FORWARD ONLY scrolling cursors). With Forward only scrolling
117cursors, once a row in the result set has been returned to the ODBC driver
118and on to the client, there is no way to have the cursor move backward in
119the result set to look at the row that is previous to the current row in
120the result set. If BACKWARD scrolling cursors are supported by both the
121ODBC driver and the datasource that are being used, then backward
122scrolling cursor functions may be used (
123\helpref{wxDbTable::GetPrev}{wxdbtablegetprev},
124\helpref{wxDbTable::GetFirst}{wxdbtablegetfirst}, and
125\helpref{wxDbTable::GetLast}{wxdbtablegetlast}). If the datasource or the
126ODBC driver only support forward scrolling cursors, your program and logic
127must take this in to account.}
128\twocolitem{Commit/Rollback}{Commit will physically save
129insertions/deletions/updates, while rollback basically does an undo of
130everything done against the datasource connection that has not been
131previously committed. Note that Commit and Rollbacks are done on a
132connection, not on individual tables. All tables which use a shared
133connection to the datasource are all committed/rolled back at the same
134time when a call to
135\helpref{wxDb::CommitTrans}{wxdbcommittrans} or
136\helpref{wxDb::RollbackTrans}{wxdbrollbacktrans} is made.}
137\twocolitem{Index}{Indexes are datasource maintained lookup structures
138that allow the datasource to quickly locate data rows based on the values
139of certain columns. Without indexes, the datasource would need to do a
140sequential search of a table every time a query request is made. Proper
141unique key index construction can make datasource queries nearly instantaneous.}
142\end{twocollist}
143
144Before you are able to read data from a data table in a datasource, you must
145have a connection to the datasource. Each datasource connection may be used
146to open multiple tables all on the same connection (number of tables open are
147dependent on the driver, datasource configuration and the amount of memory on
148the client workstation). Multiple connections can be opened to the same
149datasource by the same client (number of concurrent connections is dependent
150on the driver and datasource configuration).
151
152When a query is performed, the client passes the query to the ODBC driver,
153and the driver then translates it and passes it along to the datasource. The
154database engine (in most cases - exceptions are text and dBase files) running
155on the machine hosting the database does all the work of performing the search
156for the requested data. The client simply waits for a status to come back
157through the ODBC driver from the datasource.
158
159Depending on the ODBC driver, the result set either remains "queued" on the
160database server side, or is transferred to the machine that the driver is
161queued on. The client does not receive this data. The client must request
162some or all of the result set to be returned before any data rows are
163returned to the client application.
164
165Result sets do not need to include all columns of every row matching the
166query. In fact, result sets can actually be joinings of columns from two
167or more data tables, may have derived column values, or calculated values
168returned.
169
170For each result set, a cursor is maintained (typically by the database)
171which keeps track of where in the result set the user currently is.
172Depending on the database, ODBC driver, and how you configured the
173wxWindows ODBC settings in setup.h (see \helpref{wxODBC - Compiling}{wxodbccompiling}), cursors can be
174either forward or backward scrolling. At a minim, cursors must scroll
175forward. For example, if a query resulted in a result set with 100 rows,
176as the data is read by the client application, it will read row 1, then 2,
177then 3, etc, etc. With forward only cursors, once the cursor has moved to
178the next row, the previous row cannot be accessed again without re-querying
179the datasource for the result set over again. Backward scrolling cursors
180allow you to request the previous row from the result set, actually
181scrolling the cursor backward.
182
183Backward scrolling cursors are not supported on all database/driver
184combinations. For this reason, forward-only cursors are the default in
185the wxODBC classes. If your datasource does support backward scrolling
186cursors and you wish to use them, make the appropriate changes in setup.h
187to enable them (see \helpref{wxODBC - Compiling}{wxodbccompiling}). For greatest portability between
188datasources, writing your program in such a way that it only requires
189forward scrolling cursors is your best bet. On the other hand, if you are
190focusing on using only datasources that support backward scrolling cursors,
191potentially large performance benefits can be gained from using them.
192
193There is a limit to the number of cursors that can be open on each connection
194to the datasource, and usually a maximum number of cursors for the datasource
195itself. This is all dependent on the database. Each connection that is
196opened (each instance of a wxDb) opens a minimum of 5 cursors for on creation
197that are required for things such as updates/deletions/rollbacks/queries.
198Cursors are a limited resource, so use care in creating large numbers of
199cursors.
200
201Additional cursors can be created if necessary with the
202\helpref{wxDbTable::GetNewCursor}{wxdbtablegetnewcursor} function. One example
203use for additional cursors are to track multiple scroll points in result
204sets. By creating a new cursor, a program could request a second result set
205from the datasource while still maintaining the original cursor position in
206the first result set.
207
208Different than non-SQL/ODBC datasources, when a program performs an
209insertion, deletion, or update (or other SQL functions like altering
210tables, etc) through ODBC, the program must issue a "commit" to the
211datasource to tell the datasource that the action(s) it has been told to
212perform are to be recorded as permanent. Until a commit is performed,
213any other programs that query the datasource will not see the changes that
214have been made (although there are databases that can be configured to
215auto-commit). NOTE: With most all datasources, until the commit is
216performed, any cursor that is open on that same datasource connection
217will be able to see the changes that are uncommitted. Check your
218database's documentation/configuration to verify this before counting on it
219though.
220
221A rollback is basically an UNDO command on the datasource connection. When
222a rollback is issued, the datasource will flush all commands it has been told
223to do since the last commit that was performed.
224
225NOTE: Commits/Rollbacks are done on datasource connections (wxDb instances)
226not on the wxDbTable instances. This means that if more than one table
227shares the same connection, and a commit or rollback is done on that
228connection, all pending changes for ALL tables using that connection are
229committed/rolled back.
230
231
232
233\subsection{wxODBC - Configuring your system for ODBC use}\label{wxodbcconfiguringyoursystem}
234
235Before you are able to access a datasource, you must have installed and
236configured an ODBC driver. Doing this is system specific, so it will not be
237covered in detail here. But here are a few details to get you started.
238
239Most database vendors provide at least a minimal ODBC driver with their
240database product. In practice, many of these drivers have proven to be slow
241and/or incomplete. Rumor has it that this is because the vendors do not want
242you using the ODBC interface to their products, they want you to use their
243applications to access the data.
244
245Whatever the reason, for database intensive applications, you may want to
246think of using a third-party ODBC driver for your needs. One example of a
247third party set of ODBC drivers that has been heavily tested and used is
248Rogue Wave's drivers. Rogue Wave has drivers available for many different
249platforms and databases.
250
251Under Microsoft Windows, install the ODBC driver you are planning to use. You
252will then use the ODBC Administrator in the Control Panel to configure an
253instance of the driver for your intended datasource. Note that with all
254flavors of NT, this configuration can be set up as a System or User DSN
255(datasource name). Configuring it as a system resource will make it
256available to all users (if you are logged in as 'administrator'), otherwise
257the datasource will only be available to the who configured the DSN.
258
259Under Unix, iODBC is used for implementation of the ODBC API. To compile the
260wxODBC classes, you must first obtain (http://www.iodbc.org) and install iODBC.
261Then you must create the file "~/.odbc.ini" (or optionally create
262"/etc/odbc.ini" for access for all users on the system). This file contains
263the settings for your system/datasource. Below is an example section of a
264odbc.ini file for use with the "samples/db" sample program using MySQL:
265
266\begin{verbatim}
267 [contacts]
268 Trace = Off
269 TraceFile= stderr
270 Driver = /usr/local/lib/libmyodbc.so
271 DSN = contacts
272 SERVER = 192.168.1.13
273 USER = qet
274 PASSWORD =
275 PORT = 3306
276\end{verbatim}
277
278
279
280\subsection{wxODBC - Compiling}\label{wxodbccompiling}
281
282The wxWindows setup.h file has several settings in it pertaining to compiling
283the wxODBC classes.
284
285\begin{twocollist}\itemsep=0pt
286\twocolitem{wxUSE_ODBC}{This must be set to 1 in order for the compiler to
287compile the wxODBC classes. Without setting this to 1, there will be no
288access to any of the wxODBC classes. The default is 0.}
289\twocolitem{wxODBC_FWD_ONLY_CURSORS}{When a new database connection is
290requested, this setting controls the default of whether the connection allows
291only forward scrolling cursors, or forward and backward scrolling cursors
292(see the section in "WHERE TO START" on cursors for more information on
293cursors). This default can be overridden by passing a second parameter to
294either the \helpref{wxDbGetConnection}{wxdbfunctions} or
295\helpref{wxDb constructor}{wxdbconstr}. The default is 1.}
296\twocolitem{wxODBC_BACKWARD_COMPATABILITY}{Between v2.0 and 2.2, massive
297renaming efforts were done to the ODBC classes to get naming conventions
298similar to those used throughout wxWindows, as well as to preface all wxODBC
299classes names and functions with a wxDb preface. Because this renaming would
300affect applications written using the v2.0 names, this compile-time directive
301was added to allow those programs written for v2.0 to still compile using the
302old naming conventions. These deprecated names are all #define'd to their
303corresponding new function names at the end of the db.cpp/dbtable.cpp source
304files. These deprecated class/function names should not be used in future
305development, as at some point in the future they will be removed. The default
306is 0.}
307\end{twocollist}
308
309{\it Under MS Windows}
310
311You are required to include the "odbc32.lib" provided by your compiler vendor
312in the list of external libraries to be linked in. If using the makefiles
313supplied with wxWindows, this library should already be included for use with
314makefile.b32, makefile.vc, and makefile.g95.
315
316You cannot compile the wxODBC classes under Win16 - sorry.
317
318\normalbox{MORE TO COME}
319
320{\it Under Unix}
321--with-iodbc flag for configure
322
323\normalbox{MORE TO COME}
324
325
326
327\subsection{wxODBC - Basic Step-By-Step Guide}\label{wxodbcstepbystep}
328
329To use the classes in an application, there are eight basic steps:
330
331\begin{itemize}\itemsep=0pt
332\item Define datasource connection information
333\item Get a datasource connection
334\item Create table definition
335\item Open the table
336\item Use the table
337\item Close the table
338\item Close the datasource connection
339\item Release the ODBC environment handle
340\end{itemize}
341
342Following each of these steps is detailed to explain the step, and to
343hopefully mention as many of the pitfalls that beginning users fall in
344to when first starting to use the classes. Throughout the steps, small
345snippets of code are shown to show the syntax of performing the step. A
346complete code snippet is provided at the end of this overview that shows a
347complete working flow of all these steps (see
348\helpref{wxODBC - Sample Code #1}{wxodbcsamplecode1}).
349
350
351{\bf Define datasource connection information}
352
353To be able to connect to a datasource through the ODBC driver, a program must
354supply a minimum of three pieces of information: Datasource name, User ID, and
355Authorization string (password). A fourth piece of information, a default
356directory indicating where the data file is stored, is required for Text and
357dBase drivers for ODBC.
358
359The wxWindows data structure "wxDbConnectInf" exists for holding all of these
360values, plus some others that may be desired.
361
362\begin{verbatim}
363 wxDbConnectInf
364 HENV Henv;
365 char Dsn[SQL_MAX_DSN_LENGTH+1] // DataSource Name
366 char Uid[20+1] // User ID
367 char AuthStr[20+1] // Authorization (password)
368
369 // Optional needed for some databases like dBase, FoxPro and TEXT files
370 char defaultDir[wxDB_PATH_MAX] // Directory that db file resides in
371
372 char description[SQL_MAX_DSN_LENGTH+1];
373 char fileType[SQL_MAX_DSN_LENGTH+1];
374\end{verbatim}
375
376The 'Henv' member is the environment handle used to access memory for use by the
377ODBC driver. Use of this member is described below in the "Getting a Connection
378to the Datasource" section.
379
380The 'Dsn' must exactly match the datasource name used to configure the ODBC
381datasource (in the ODBC Administrator (MSW only) or in the .odbc.ini file).
382
383The 'Uid' is the User ID that is to be used to log in to the datasource. This
384User ID must already have been created and assigned rights within the
385datasource to which you are connecting. The user that the connection is
386establish by will determine what rights and privileges the datasource
387connection will allow the program to have when using the connection that
388this connection information was used to establish. Some datasources are
389case sensitive for User IDs, and though the wxODBC classes attempt to hide
390this from you by manipulating whatever data you pass in to match the
391datasource's needs, it is always best to pass the 'Uid' in the case that
392the datasource requires.
393
394The 'AuthStr' is the password for the User ID specified in the 'Uid' member.
395As with the 'Uid', some datasources are case sensitive (in fact most are).
396The wxODBC classes do NOT try to manage the case of the 'AuthStr' at all.
397It is passed verbatim to the datasource, so you must use the case that the
398datasource is expecting.
399
400The 'defaultDir' member is used with file based datasources (i.e. dBase,
401FoxPro, text files). It contains a full path to the location where the
402data table or file is located. When setting this value, use forward
403slashes '/' rather than backslashes '\' to avoid compatibility differences
404between ODBC drivers.
405
406The other fields are currently unused. The intent of these fields are that
407they will be used to write our own ODBC Administrator type program that will
408work on both MSW and Un*x systems, regardless of the datasource. Very little
409work has been done on this to date.
410
411
412{\bf Get a Datasource Connection}
413
414There are two methods of establishing a connection to a datasource. You
415may either manually create your own wxDb instance and open the connection,
416or you may use the caching functions provided with the wxODBC classes to
417create/maintain/delete the connections.
418
419Regardless of which method you use, you must first have a fully populated
420wxDbConnectInf object. In the wxDbConnectInf instance, provide a valid
421Dns, Uid, and AuthStr (along with a 'defaultDir' if necessary). Before
422using this though, you must allocate an environment handle to the 'Henv'
423member.
424
425\begin{verbatim}
426 wxDbConnectInf DbConnectInf;
427 wxStrcpy(DbConnectInf.Dsn, "Your DSN");
428 wxStrcpy(DbConnectInf.Uid, "YourUserName");
429 wxStrcpy(DbConnectInf.AuthStr, "YourUserPassword");
430 wxStrcpy(DbConnectInf.defaultDir, "");
431\end{verbatim}
432
433To allocate an environment handle for the ODBC connection to use, the
434standard SQLAllocEnv() function is used.
435
436\begin{verbatim}
437 if (SQLAllocEnv(&DbConnectInf.Henv) != SQL_SUCCESS)
438 {
439 wxMessageBox("Unable to allocate an ODBC environment handle",
440 "DB CONNECTION ERROR", wxOK | wxICON_EXCLAMATION);
441 return;
442 }
443\end{verbatim}
444
445When the SQLAllocEnv() function is called successfully, a value of
446SQL_SUCCESS will be returned. Anything other return value is a failure
447to create the handle, and the handle will be undefined.
448
449Once the members of the wxDbConnectInf structure are initialized, you are
450now ready to connect to the datasource.
451
452To manually create datasource connections, you must create a wxDb
453instance, and then open it.
454
455\begin{verbatim}
456 wxDb *db = new wxDb(DbConnectInf.Henv);
457 opened = db->Open(DbConnectInf.Dsn, DbConnectInf.Uid,
458 DbConnectInf.AuthStr);
459\end{verbatim}
460
461The first line does the house keeping needed to initialize everything all
462the members of the wxDb class. The second line actually sends the request
463to the ODBC driver to open a connection to its associated datasource using
464the parameters supplied in the call to \helpref{wxDb::Open}{wxdbopen}.
465
466A more advanced form of opening a connection is to used the connection
467caching functions that are included with the wxODBC classes. The caching
468mechanisms do the same functions are the manual approach to opening a
469connection, but they also manage each connection they have created,
470re-using them and cleaning them up when they are closed, without you
471programmatically needing to do the coding.
472
473To use the caching function \helpref{wxDbGetConnection}{wxdbfunctions} to get
474a connection to a datasource, simply call it with a single parameter of the
475type wxDbConnectInf:
476
477\begin{verbatim}
478 db = wxDbGetConnection(&DbConnectInf);
479\end{verbatim}
480
481The wxDb pointer that is returned is both initialized and opened. If
482something failed in creating or opening the connection, the return value
483from \helpref{wxDbGetConnection}{wxdbfunctions} will be NULL.
484
485The connection that is returned is either a new connection, or it is a
486"free" connection from the cache of connections that the class maintains
487that was no longer in use. Any wxDb instance created with a call to
488\helpref{wxDbGetConnection}{wxdbfunctions} is kept track of in a linked list of established
489connections. When a program is done with a connection, a call to
490\helpref{wxDbFreeConnection}{wxdbfunctions} is made, and the datasource
491connection will then be tagged as FREE, making it available for the next
492call to \helpref{wxDbGetConnection}{wxdbfunctions} that needs a connection
493using the same connection information (Dsn, Uid, AuthStr). The cached
494connections remain cached until a call to \helpref{wxDbCloseConnections}{wxdbfunctions} is made,
495at which time all cached connections are closed and deleted.
496
497Besides the obvious advantage of using the single command caching routine to
498obtain a datasource connection, using cached connections can be quite a
499performance boost as well. Each time that a new connection is created
500(not retrieved from the cache of free connections), the wxODBC classes
501perform many queries against the datasource to determine the datasource's
502datatypes and other fundamental behaviors. Depending on the hardware,
503network bandwidth, and datasource speed, this can in some cases take a
504few seconds to establish the new connection (with well balanced systems,
505it should only be a fraction of a second). Re-using already established
506datasource connections rather than creating/deleting, creating/deleting
507connections can be quite a time saver.
508
509Another time saver is the "copy connection" features of both
510\helpref{wxDb::Open}{wxdbopen} and \helpref{wxDbGetConnection}{wxdbfunctions}.
511If manually creating a wxDb instance and opening it, you must pass an existing
512connection to the \helpref{wxDb::Open}{wxdbopen} function yourself to gain the performance
513benefit of copying existing connection settings. The
514\helpref{wxDbGetConnection}{wxdbfunctions} function automatically does this
515for you, checking the Dsn, Uid, and AuthStr parameters when you request
516a connection for any existing connections that use those same settings.
517If one is found, \helpref{wxDbGetConnection}{wxdbfunctions} copies the datasource settings for
518datatypes and other datasource specific information that was previously
519queried, rather than re-querying the datasource for all those same settings.
520
521One final note on creating a connection. When a connection is created, it
522will default to only allowing cursor scrolling to be either forward only,
523or both backward and forward scrolling cursors. The default behavior is
524determined by the setting "wxODBC_FWD_ONLY_CURSORS" in setup.h when you
525compile the wxWindows library. The library default is to only support
526forward scrolling cursors only, though this can be overridden by parameters
527for wxDb() constructor or the \helpref{wxDbGetConnection}{wxdbfunctions}
528function. All datasources and ODBC drivers must support forward scrolling
529cursors. Many datasources support backward scrolling cursors, and many
530ODBC drivers support backward scrolling cursors. Before planning on using
531backward scrolling cursors, you must be certain that both your datasource
532and ODBC driver fully support backward scrolling cursors. See the small
533blurb about "Scrolling cursors" in the definitions at the beginning of
534this overview, or other details of setting the cursor behavior in the wxDb
535class documentation.
536
537
538{\bf Create Table Definition}
539
540Data can be accessed in a datasource's tables directly through various
541functions of the wxDb class (see \helpref{wxDb::GetData}{wxdbgetdata}). But to make life much
542simpler, the wxDbTable class encapsulates all of the SQL specific API calls
543that would be necessary to do this, wrapping it in an intuitive class of APIs.
544
545The first step in accessing data in a datasource's tables via the wxDbTable
546class is to create a wxDbTable instance.
547
548\begin{verbatim}
549 table = new wxDbTable(db, tableName, numTableColumns, 0,
550 !wxDB_QUERY_ONLY, NULL);
551\end{verbatim}
552
553When you create the instance, you indicate the previously established
554datasource connection to be used to access the table, the name of the
555primary table that is to be accessed with the datasource's tables, how many
556columns of each row are going to be returned, the name of the view of the
557table that will actually be used to query against (works with Oracle only
558at this time), whether the data returned is for query purposes only, and
559finally the path to the table, if different than the path specified when
560connecting to the datasource.
561
562Each of the above parameters are described in detail in the wxDbTable
563class' description, but one special note here about the fifth
564parameter - queryOnly setting. If a wxDbTable instance is created as
565wxDB_QUERY_ONLY, then no inserts/deletes/updates are able to be performed
566using this instance of the wxDbTable. Any calls to \helpref{wxDb::CommitTrans}{wxdbcommittrans}
567or \helpref{wxDb::RollbackTrans}{wxdbrollbacktrans} against the datasource
568connection used by this wxDbTable instance are ignored by this instance. If
569the wxDbTable instance is created with "!wxDB_QUERY_ONLY" as shown above,
570then all the cursors and other overhead associated with being able to
571insert/update/delete data in the table are created, and thereby those
572operations can then be performed against the associated table with this
573wxDbTable instance.
574
575If a table is to be accessed via a wxDbTable instance, and the table will
576only be read from, not written to, there is a performance benefit (not as
577many cursors need to be maintained/updated, hence speeding up access times),
578as well as a resource savings due to fewer cursors being created for the
579wxDbTable instance. Also, with some datasources, the number of
580simultaneous cursors is limited.
581
582When defining the columns to be retrievable by the wxDbTable instance, you
583can specify anywhere from one column up to all columns in the table.
584
585\begin{verbatim}
586 table->SetColDefs(0, "FIRST_NAME", DB_DATA_TYPE_VARCHAR, FirstName,
587 SQL_C_CHAR, sizeof(name), TRUE, TRUE);
588 table->SetColDefs(1, "LAST_NAME", DB_DATA_TYPE_VARCHAR, LastName,
589 SQL_C_CHAR, sizeof(LastName), TRUE, TRUE);
590\end{verbatim}
591
592Notice that column definitions start at index 0 and go up to one less than
593the number of columns specified when the wxDbTable instance was created
594(in this example, two columns - one with index 0, one with index 1).
595
596The above lines of code "bind" the datasource columns specified to the
597memory variables in the client application. So when the application
598makes a call to \helpref{wxDbTable::GetNext}{wxdbtablegetnext} (or any other function that retrieves
599data from the result set), the variables that are bound to the columns will
600have the column value stored into them. See the
601\helpref{wxDbTable::SetColDefs}{wxdbtablesetcoldefs}
602class documentation for more details on all the parameters for this function.
603
604The bound memory variables have undefined data in them until a call to a
605function that retrieves data from a result set is made
606(e.g. \helpref{wxDbTable::GetNext}{wxdbtablegetnext},
607\helpref{wxDbTable::GetPrev}{wxdbtablegetprev}, etc). The variables are not
608initialized to any data by the wxODBC classes, and they still contain
609undefined data after a call to \helpref{wxDbTable::Query}{wxdbtablequery}. Only
610after a successful call to one of the ::GetXxxx() functions is made do the
611variables contain valid data.
612
613It is not necessary to define column definitions for columns whose data is
614not going to be returned to the client. For example, if you want to query
615the datasource for all users with a first name of 'GEORGE', but you only want
616the list of last names associated with those rows (why return the FIRST_NAME
617column every time when you already know it is 'GEORGE'), you would only have
618needed to define one column above.
619
620You may have as many wxDbTable instances accessing the same table using the
621same wxDb instance as you desire. There is no limit imposed by the classes
622on this. All datasources supported (so far) also have no limitations on this.
623
624
625{\bf Open the table}
626
627Opening the table technically is not doing anything with the datasource
628itself. Calling \helpref{wxDbTable::Open}{wxdbtableopen} simply does all the
629house keeping of checking that the specified table exists, that the current
630connected user has at least SELECT privileges for accessing the table,
631setting up the requisite cursors, binding columns and cursors, and
632constructing the default INSERT statement that is used when a new row is
633inserted into the table (non-wxDB_QUERY_ONLY tables only).
634
635\begin{verbatim}
636 if (!table->Open())
637 {
638 // An error occurred opening (setting up) the table
639 }
640\end{verbatim}
641
642The only reason that a call to \helpref{wxDbTable::Open}{wxdbtableopen} will likely fail is if the
643user has insufficient privileges to even SELECT the table. Other problems
644could occur, such as being unable to bind columns, but these other reason
645point to some lack of resource (like memory). Any errors generated
646internally in the \helpref{wxDbTable::Open}{wxdbtableopen} function are logged to the error log
647if SQL logging is turned on for the classes.
648
649
650{\bf Use the table}
651
652To use the table and the definitions that are now set up, we must first
653define what data we want the datasource to collect in to a result set, tell
654it where to get the data from, and in what sequence we want the data returned.
655
656\begin{verbatim}
657 // the WHERE clause limits/specifies which rows in the table
658 // are to be returned in the result set
659 table->SetWhereClause("FIRST_NAME = 'GEORGE'");
660
661 // Result set will be sorted in ascending alphabetical
662 // order on the data in the 'LAST_NAME' column of each row
663 // If the same last name is in the table for two rows,
664 // sub-sort on the 'AGE' column
665 table->SetOrderByClause("LAST_NAME, AGE");
666
667 // No other tables (joins) are used for this query
668 table->SetFromClause("");
669\end{verbatim}
670
671The above lines will be used to tell the datasource to return in the result
672all the rows in the table whose column "FIRST_NAME" contains the name
673'GEORGE' (note the required use of the single quote around the string
674literal) and that the result set will return the rows sorted by ascending
675last names (ascending is the default, and can be overridden with the
676"DESC" keyword for datasources that support it - "LAST_NAME DESC").
677
678Specifying a blank WHERE clause will result in the result set containing
679all rows in the datasource.
680
681Specifying a blank ORDERBY clause means that the datasource will return
682the result set in whatever sequence it encounters rows which match the
683selection criteria. What this sequence is can be hard to determine.
684Typically it depends on the index that the datasource used to find the
685rows which match the WHERE criteria. BEWARE - relying on the datasource
686to return data in a certain sequence when you have not provided an ORDERBY
687clause will eventually cause a problem for your program. Databases can be
688tuned to be COST-based, SPEED-based, or some other basis for how it gets
689your result set. In short, if you need your result set returned in a
690specific sequence, ask for it that way by providing an ORDERBY clause.
691
692Using an ORDERBY clause can be a performance hit, as the database must
693sort the items before making the result set available to the client.
694Creating efficient indexes that cause the data to be "found" in the correct
695ORDERBY sequence can be a big performance benefit. Also, in the large
696majority of cases, the database will be able to sort the records faster
697than your application can read all the records in (unsorted) and then sort
698them. Let the database do the work for you!
699
700Notice in the example above, a column that is not included in the bound
701data columns ('AGE') will be used to sub-sort the result set.
702
703The FROM clause in this example is blanked, as we are not going to be
704performing any table joins with this simple query. When the FROM clause
705is blank, it is assumed that all columns referenced are coming from
706the default table for the wxDbTable instance.
707
708After the selection criteria have been specified, the program can now
709ask the datasource to perform the search and create a result set that
710can be retrieved:
711
712\begin{verbatim}
713 // Instruct the datasource to perform a query based on the
714 // criteria specified above in the where/orderBy/from clauses.
715 if (!table->Query())
716 {
717 // An error occurred performing the query
718 }
719\end{verbatim}
720
721Typically, when an error occurs when calling \helpref{wxDbTable::Query}{wxdbtablequery}, it is a
722syntax problem in the WHERE clause that was specified. The exact SQL
723(datasource specific) reason for what caused the failure of \helpref{wxDbTable::Query}{wxdbtablequery}
724(and all other operations against the datasource can be found by
725parsing the table's database connection's "errorList[]" array member for
726the stored text of the error.
727
728When the \helpref{wxDbTable::Query}{wxdbtablequery} returns TRUE, the
729database was able to successfully complete the requested query using the
730provided criteria. This does not mean that there are any rows in the
731result set, it just mean that the query was successful.
732
733\normalbox{IMPORTANT: The result created by the call to
734\helpref{wxDbTable::Query}{wxdbtablequery} can be one of two forms. It is
735either a snapshot of the data at the exact moment that the database
736determined the record matched the search criteria, or it is a pointer to
737the row that matched the selection criteria. Which form of behavior is
738datasource dependent. If it is a snapshot, the data may have changed
739since the result set was constructed, so beware if your datasource
740uses snapshots and call \helpref{wxDbTable::Refresh}{wxdbtablerefresh}. Most larger brand databases
741do not use snapshots, but it is important to mention so that your application
742can handle it properly if your datasource does. }
743
744To retrieve the data, one of the data fetching routines must be used to
745request a row from the result set, and to store the data from the result
746set into the bound memory variables. After \helpref{wxDbTable::Query}{wxdbtablequery}
747has completed successfully, the default/current cursor is placed so it
748is pointing just before the first record in the result set. If the
749result set is empty (no rows matched the criteria), then any calls to
750retrieve data from the result set will return FALSE.
751
752\begin{verbatim}
753 wxString msg;
754
755 while (table->GetNext())
756 {
757 msg.Printf("Row #%lu -- First Name : %s Last Name is %s",
758 table->GetRowNum(), FirstName, LastName);
759 wxMessageBox(msg, "Data", wxOK | wxICON_INFORMATION, NULL);
760 }
761\end{verbatim}
762
763The sample code above will read the next record in the result set repeatedly
764until the end of the result set has been reached. The first time that
765\helpref{wxDbTable::GetNext}{wxdbtablegetnext} is called right after the successful
766call to \helpref{wxDbTable::Query}{wxdbtablequery}, it actually returns the first record
767in the result set.
768
769When \helpref{wxDbTable::GetNext}{wxdbtablegetnext} is called and there are
770no rows remaining in the result set after the current cursor position,
771\helpref{wxDbTable::GetNext}{wxdbtablegetnext} (as well as all the other
772wxDbTable::GetXxxxx() functions) will return FALSE.
773
774
775{\bf Close the table}
776
777When the program is done using a wxDbTable instance, it is as simple as
778deleting the table pointer (or if declared statically, letting the
779variable go out of scope). Typically the default destructor will take
780care of all that is required for cleaning up the wxDbTable instance.
781
782\begin{verbatim}
783 if (table)
784 {
785 delete table;
786 table = NULL;
787 }
788\end{verbatim}
789
790Deleting a wxDbTable instance releases all of its cursors, deletes the
791column definitions and frees the SQL environment handles used by the
792table (but not the environment handle used by the datasource connection
793that the wxDbTable instance was using).
794
795
796
797{\bf Close the datasource connection}
798
799After all tables that have been using a datasource connection have been
800closed (this can be checked by calling \helpref{wxDb::GetTableCount}{wxdbgettablecount}
801and checking that it returns 0), then you may close the datasource
802connection. The method of doing this is dependent on whether the
803non-caching or caching method was used to obtain the datasource connection.
804
805If the datasource connection was created manually (non-cached), closing the
806connection is done like this:
807
808\begin{verbatim}
809 if (db)
810 {
811 db->Close();
812 delete db;
813 db = NULL;
814 }
815\end{verbatim}
816
817If the program used the \helpref{wxDbGetConnection}{wxdbfunctions} function to get a datasource
818connection, the following is the code that should be used to free the
819connection(s):
820
821\begin{verbatim}
822 if (db)
823 {
824 wxDbFreeConnection(db);
825 db = NULL;
826 }
827\end{verbatim}
828
829Note that the above code just frees the connection so that it can be
830re-used on the next call the \helpref{wxDbGetConnection}{wxdbfunctions}. To actually dispose
831of the connection, releasing all of its resources (other than the
832environment handle), do the following:
833
834\begin{verbatim}
835 wxDbCloseConnections();
836\end{verbatim}
837
838
839{\bf Release the ODBC environment handle}
840
841Once all of the connections that used the ODBC environment handle (in
842this example it was stored in "DbConnectInf.Henv") have been closed, then
843it is safe to release the environment handle:
844
845\begin{verbatim}
846 SQLFreeEnv(DbConnectInf.Henv);
847
848\end{verbatim}
849
850\normalbox{Remember to never release this environment handle if there are any
851connections still using the handle.
852}
853
854
855\subsection{wxODBC - Known Issues}\label{wxodbcknownissues}
856
857As with creating wxWindows, writing the wxODBC classes was not the simple
858task of writing an application to run on a single type of computer system.
859The classes need to be cross-platform for different operating systems, and
860they also needed to take in to account different database manufacturers and
861different ODBC driver manufacturers. Because of all the possible combinations
862of OS/database/drivers, it is impossible to say that these classes will work
863perfectly with datasource ABC, ODBC driver XYZ, on platform LMN. You may run
864in to some incompatibilities or unsupported features when moving your
865application from one environment to another. But that is what makes
866cross-platform programming fun. It is also pinpoints one of the great
867things about open source software. It can evolve!
868
869The most common difference between different database/ODBC driver
870manufacturers in regards to these wxODBC classes is the lack of
871standard error codes being returned to the calling program. Sometimes
872manufacturers have even changed the error codes between versions of
873their databases/drivers.
874
875In all the tested databases, every effort has been made to determine
876the correct error codes and handle them in the class members that need
877to check for specific error codes (such as TABLE DOES NOT EXIST when
878you try to open a table that has not been created yet). Adding support
879for additional databases in the future requires adding an entry for the
880database in the \helpref{wxDb::Dbms}{wxdbdbms} function, and then handling any error codes
881returned by the datasource that do not match the expected values.
882
883
884{\bf Databases}
885
886Following is a list of known issues and incompatibilities that the
887wxODBC classes have between different datasources. An up to date
888listing of known issues can be seen in the comments of the source
889for \helpref{wxDb::Dbms}{wxdbdbms}.
890
891{\it ORACLE}
892\begin{itemize}\itemsep=0pt
893\item Currently the only database supported by the wxODBC classes to support VIEWS
894\end{itemize}
895
896{\it DBASE}
897
898NOTE: dBase is not a true ODBC datasource. You only have access to as much
899functionality as the driver can emulate.
900
901\begin{itemize}\itemsep=0pt
902\item Does not support the SQL_TIMESTAMP structure
903\item Supports only one cursor and one connect (apparently? with Microsoft driver only?)
904\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}
905\item Table names can only be 8 characters long
906\item Column names can only be 10 characters long
907\item Currently cannot CREATE a dBase table - bug or limitation of the drivers used??
908\item Currently cannot insert rows that have integer columns - bug??
909\end{itemize}
910
911{\it SYBASE (all)}
912\begin{itemize}\itemsep=0pt
913\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
914\item Ignores the keywords 'FOR UPDATE'. Use the HOLDLOCK functionality described above
915\end{itemize}
916
917{\it SYBASE (Enterprise)}
918\begin{itemize}\itemsep=0pt
919\item If a column is part of the Primary Key, the column cannot be NULL
920\item Maximum row size is somewhere in the neighborhood of 1920 bytes
921\end{itemize}
922
923{\it MY_SQL}
924\begin{itemize}\itemsep=0pt
925\item If a column is part of the Primary Key, the column cannot be NULL.
926\item Cannot support selecting for update [\helpref{wxDbTable::CanSelectForUpdate}{wxdbtablecanselectforupdate}]. Always returns FALSE.
927\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)
928\item Does not support sub-queries in SQL statements
929\end{itemize}
930
931{\it POSTGRES}
932\begin{itemize}\itemsep=0pt
933\item Does not support the keywords 'ASC' or 'DESC' as of release v6.5.0
934\item Does not support sub-queries in SQL statements
935\end{itemize}
936
937{\it DB2}
938\begin{itemize}\itemsep=0pt
939\item Columns which are part of a primary key must be declared as NOT NULL
940\end{itemize}
941
942
943{\bf UNICODE with wxODBC classes}
944
945Currently there is no support for Unicode with the wxODBC classes. In
946fact, Unicode builds must be disabled if wxWindows is compiled with
947wxUSE_ODBC set to 1 in setup.h
948
949
950\subsection{wxODBC - Sample Code #1}\label{wxodbcsamplecode1}
951
952Simplest example of establishing/opening a connection to an ODBC datasource,
953binding variables to the columns for read/write usage, opening an
954existing table in the datasource, setting the query parameters
955(where/orderBy/from), querying the datasource, reading each row of the
956result set, then cleaning up.
957
958NOTE: Not all error trapping is shown here, to reduce the size of the
959code and to make it more easily readable.
960
961\begin{verbatim}
962struct wxDbConnectInf DbConnectInf;
963
964wxDb *db = NULL; // The database connection
965wxDbTable *table = NULL; // The data table to access
966
967char FirstName[50+1]; // buffer for data from column "FIRST_NAME"
968char LastName[50+1]; // buffer for data from column "LAST_NAME"
969
970bool errorOccured = FALSE;
971
972const char tableName[] = "CONTACTS";
973const int numTableColumns = 2; // Number of bound columns
974
975FirstName[0] = 0;
976LastName[0] = 0;
977
978// Initialize the ODBC Environment for database operations
979// and store the handle in 'DbConnectInf.Henv'
980if (SQLAllocEnv(&DbConnectInf.Henv) != SQL_SUCCESS)
981{
982wxMessageBox("Unable to allocate an ODBC environment handle",
983 "DB CONNECTION ERROR", wxOK | wxICON_EXCLAMATION);
984return;
985}
986
987wxStrcpy(DbConnectInf.Dsn, "Your DSN");
988wxStrcpy(DbConnectInf.Uid, "YourUserName");
989wxStrcpy(DbConnectInf.AuthStr, "YourUserPassword");
990wxStrcpy(DbConnectInf.defaultDir, "");
991
992// Get a database connection from the cached connections
993db = wxDbGetConnection(&DbConnectInf);
994
995// Create the table connection
996table = new wxDbTable(db, tableName, numTableColumns, 0,
997 !wxDB_QUERY_ONLY, NULL);
998
999//
1000// Bind the columns that you wish to retrieve. Note that there must be
1001// 'numTableColumns' calls to SetColDefs(), to match the wxDbTable definition
1002//
1003// Not all columns need to be bound, only columns whose values are to be
1004// returned back to the client.
1005//
1006table->SetColDefs(0, "FIRST_NAME", DB_DATA_TYPE_VARCHAR, FirstName,
1007 SQL_C_CHAR, sizeof(name), TRUE, TRUE);
1008table->SetColDefs(1, "LAST_NAME", DB_DATA_TYPE_VARCHAR, LastName,
1009 SQL_C_CHAR, sizeof(LastName), TRUE, TRUE);
1010
1011// Open the table for access
1012table->Open();
1013
1014// Set the WHERE clause to limit the result set to only
1015// return all rows that have a value of 'GEORGE' in the
1016// FIRST_NAME column of the table.
1017table->SetWhereClause("FIRST_NAME = 'GEORGE'");
1018
1019// Result set will be sorted in ascending alphabetical
1020// order on the data in the 'LAST_NAME' column of each row
1021table->SetOrderByClause("LAST_NAME");
1022
1023// No other tables (joins) are used for this query
1024table->SetFromClause("");
1025
1026// Instruct the datasource to perform a query based on the
1027// criteria specified above in the where/orderBy/from clauses.
1028if (!table->Query())
1029{
1030 wxMessageBox("Error on Query()","ERROR!",
1031 wxOK | wxICON_EXCLAMATION);
1032 errorOccured = TRUE;
1033}
1034
1035wxString msg;
1036
1037// Start and continue reading every record in the table
1038// displaying info about each record read.
1039while (table->GetNext())
1040{
1041 msg.Printf("Row #%lu -- First Name : %s Last Name is %s",
1042 table->GetRowNum(), FirstName, LastName);
1043 wxMessageBox(msg, "Data", wxOK | wxICON_INFORMATION, NULL);
1044}
1045
1046// If the wxDbTable instance was successfully created
1047// then delete it as I am done with it now.
1048if (table)
1049{
1050 delete table;
1051 table = NULL;
1052}
1053
1054// If we have a valid wxDb instance, then free the connection
1055// (meaning release it back in to the cache of datasource
1056// connections) for the next time a call to wxDbGetConnection()
1057// is made.
1058if (db)
1059{
1060 wxDbFreeConnection(db);
1061 db = NULL;
1062}
1063
1064// The program is now ending, so we need to close
1065// any cached connections that are still being
1066// maintained.
1067wxDbCloseConnections();
1068
1069// Release the environment handle that was created
1070// for use with the ODBC datasource connections
1071SQLFreeEnv(DbConnectInf.Henv);
1072
1073\end{verbatim}
1074
1075
1076\subsection{wxDatabase ODBC class overview [DEPRECATED]}\label{wxodbcoverview}
1077
a660d684
KB
1078Classes: \helpref{wxDatabase}{wxdatabase}, \helpref{wxRecordSet}{wxrecordset}, \helpref{wxQueryCol}{wxquerycol},
1079\rtfsp\helpref{wxQueryField}{wxqueryfield}
1080
b25ba5b7
GT
1081\normalboxd{The more sophisticated wxODBC classes (wxDb/wxDbTable) are the
1082recommended classes for doing database/ODBC work with wxWindows. These new
1083classes replace the wxWindows v1.6x classes wxDatabase.
1084
1085Documentation for the old wxDatabase class and its associated classes is still
1086included in the class documentation and in this overview section, but support
1087for these old classes has been phased out, and all future development work
1088is being done solely on the new wxDb/wxDbTable classes.}
a660d684
KB
1089
1090wxWindows provides a set of classes for accessing a subset of Microsoft's ODBC (Open Database Connectivity)
1091product. Currently, this wrapper is available under MS Windows only, although
1092ODBC may appear on other platforms, and a generic or product-specific SQL emulator for the ODBC
1093classes may be provided in wxWindows at a later date.
1094
1095ODBC presents a unified API (Application Programmer's Interface) to a
1096wide variety of databases, by interfacing indirectly to each database or
1097file via an ODBC driver. The language for most of the database
1098operations is SQL, so you need to learn a small amount of SQL as well as
1099the wxWindows ODBC wrapper API. Even though the databases may not be
1100SQL-based, the ODBC drivers translate SQL into appropriate operations
f6bcfd97 1101for the database or file: even text files have rudimentary ODBC support,
a660d684
KB
1102along with dBASE, Access, Excel and other file formats.
1103
1104The run-time files for ODBC are bundled with many existing database
1105packages, including MS Office. The required header files, sql.h and
1106sqlext.h, are bundled with several compilers including MS VC++ and
1107Watcom C++. The only other way to obtain these header files is from the
1108ODBC SDK, which is only available with the MS Developer Network CD-ROMs
1109-- at great expense. If you have odbc.dll, you can make the required
1110import library odbc.lib using the tool `implib'. You need to have odbc.lib
1111in your compiler library path.
1112
1113The minimum you need to distribute with your application is odbc.dll, which must
1114go in the Windows system directory. For the application to function correctly,
1115ODBC drivers must be installed on the user's machine. If you do not use the database
1116classes, odbc.dll will be loaded but not called (so ODBC does not need to be
1117setup fully if no ODBC calls will be made).
1118
1119A sample is distributed with wxWindows in {\tt samples/odbc}. You will need to install
1120the sample dbf file as a data source using the ODBC setup utility, available from
1121the control panel if ODBC has been fully installed.
1122
b25ba5b7 1123\subsection{Procedures for writing an ODBC application using wxDatabase [DEPRECATED]}
a660d684
KB
1124
1125You first need to create a wxDatabase object. If you want to get information
1126from the ODBC manager instead of from a particular database (for example
1127using \helpref{wxRecordSet::GetDataSources}{wxrecordsetgetdatasources}), then you
1128do not need to call \helpref{wxDatabase::Open}{wxdatabaseopen}.
1129If you do wish to connect to a datasource, then call wxDatabase::Open.
1130You can reuse your wxDatabase object, calling wxDatabase::Close and wxDatabase::Open
1131multiple times.
1132
1133Then, create a wxRecordSet object for retrieving or sending information.
1134For ODBC manager information retrieval, you can create it as a dynaset (retrieve the
1135information as needed) or a snapshot (get all the data at once).
1136If you are going to call \helpref{wxRecordSet::ExecuteSQL}{wxrecordsetexecutesql}, you need to create it as a snapshot.
1137Dynaset mode is not yet implemented for user data.
1138
1139Having called a function such as wxRecordSet::ExecuteSQL or
1140wxRecordSet::GetDataSources, you may have a number of records
1141associated with the recordset, if appropriate to the operation. You can
1142now retrieve information such as the number of records retrieved and the
1143actual data itself. Use \helpref{wxRecordSet::GetFieldData}{wxrecordsetgetfielddata} or
1144\helpref{wxRecordSet::GetFieldDataPtr}{wxrecordsetgetfielddataptr} to get the data or a pointer to it, passing
1145a column index or name. The data returned will be for the current
1146record. To move around the records, use \helpref{wxRecordSet::MoveNext}{wxrecordsetmovenext},
1147\rtfsp\helpref{wxRecordSet::MovePrev}{wxrecordsetmoveprev} and associated functions.
1148
1149You can use the same recordset for multiple operations, or delete
1150the recordset and create a new one.
1151
1152Note that when you delete a wxDatabase, any associated recordsets
1153also get deleted, so beware of holding onto invalid pointers.
1154
b25ba5b7
GT
1155\subsection{wxDatabase class overview [DEPRECATED]}\label{wxdatabaseoverview}
1156
1157Class: \helpref{wxDatabase}{wxdatabase}
a660d684 1158
b25ba5b7
GT
1159\wxheading{DEPRECATED}
1160
1161Use \helpref{wxDb}{wxdb} and \helpref{wxDbTable}{wxdbtable} instead.
a660d684
KB
1162
1163Every database object represents an ODBC connection. To do anything useful
1164with a database object you need to bind a wxRecordSet object to it. All you
1165can do with wxDatabase is opening/closing connections and getting some info
1166about it (users, passwords, and so on).
1167
fa482912 1168\wxheading{See also}
a660d684 1169
fa482912
JS
1170\helpref{Database classes overview}{odbcoverview}
1171
b25ba5b7 1172\subsection{wxQueryCol class overview [DEPRECATED]}\label{wxquerycoloverview}
a660d684
KB
1173
1174Class: \helpref{wxQueryCol}{wxquerycol}
1175
b25ba5b7
GT
1176\wxheading{DEPRECATED}
1177
1178Use \helpref{wxDb}{wxdb} and \helpref{wxDbTable}{wxdbtable} instead.
1179
a660d684
KB
1180Every data column is represented by an instance of this class.
1181It contains the name and type of a column and a list of wxQueryFields where
1182the real data is stored. The links to user-defined variables are stored
1183here, as well.
1184
fa482912 1185\wxheading{See also}
a660d684 1186
fa482912
JS
1187\helpref{Database classes overview}{odbcoverview}
1188
b25ba5b7 1189\subsection{wxQueryField class overview [DEPRECATED]}\label{wxqueryfieldoverview}
a660d684
KB
1190
1191Class: \helpref{wxQueryField}{wxqueryfield}
1192
b25ba5b7
GT
1193\wxheading{DEPRECATED}
1194
1195Use \helpref{wxDb}{wxdb} and \helpref{wxDbTable}{wxdbtable} instead.
1196
a660d684
KB
1197As every data column is represented by an instance of the class wxQueryCol,
1198every data item of a specific column is represented by an instance of
1199wxQueryField. Each column contains a list of wxQueryFields. If wxRecordSet is
1200of the type wxOPEN\_TYPE\_DYNASET, there will be only one field for each column,
1201which will be updated every time you call functions like wxRecordSet::Move
1202or wxRecordSet::GoTo. If wxRecordSet is of the type wxOPEN\_TYPE\_SNAPSHOT,
1203all data returned by an ODBC function will be loaded at once and the number
1204of wxQueryField instances for each column will depend on the number of records.
1205
fa482912 1206\wxheading{See also}
a660d684 1207
fa482912
JS
1208\helpref{Database classes overview}{odbcoverview}
1209
b25ba5b7 1210\subsection{wxRecordSet overview [DEPRECATED]}\label{wxrecordsetoverview}
a660d684
KB
1211
1212Class: \helpref{wxRecordSet}{wxrecordset}
1213
b25ba5b7
GT
1214\wxheading{DEPRECATED}
1215
1216Use \helpref{wxDb}{wxdb} and \helpref{wxDbTable}{wxdbtable} instead.
1217
1218
a660d684
KB
1219Each wxRecordSet represents a database query. You can make multiple queries
1220at a time by using multiple wxRecordSets with a wxDatabase or you can make
1221your queries in sequential order using the same wxRecordSet.
1222
fa482912
JS
1223\wxheading{See also}
1224
1225\helpref{Database classes overview}{odbcoverview}
a660d684 1226
b25ba5b7 1227\subsection{ODBC SQL data types [DEPRECATED]}\label{sqltypes}
a660d684
KB
1228
1229These are the data types supported in ODBC SQL. Note that there are other, extended level conformance
1230types, not currently supported in wxWindows.
1231
1232\begin{twocollist}\itemsep=0pt
1233\twocolitem{CHAR(n)}{A character string of fixed length {\it n}.}
1234\twocolitem{VARCHAR(n)}{A varying length character string of maximum length {\it n}.}
1235\twocolitem{LONG VARCHAR(n)}{A varying length character string: equivalent to VARCHAR for the purposes
1236of ODBC.}
1237\twocolitem{DECIMAL(p, s)}{An exact numeric of precision {\it p} and scale {\it s}.}
1238\twocolitem{NUMERIC(p, s)}{Same as DECIMAL.}
1239\twocolitem{SMALLINT}{A 2 byte integer.}
1240\twocolitem{INTEGER}{A 4 byte integer.}
1241\twocolitem{REAL}{A 4 byte floating point number.}
1242\twocolitem{FLOAT}{An 8 byte floating point number.}
1243\twocolitem{DOUBLE PRECISION}{Same as FLOAT.}
1244\end{twocollist}
1245
1246These data types correspond to the following ODBC identifiers:
1247
1248\begin{twocollist}\itemsep=0pt
1249\twocolitem{SQL\_CHAR}{A character string of fixed length.}
1250\twocolitem{SQL\_VARCHAR}{A varying length character string.}
1251\twocolitem{SQL\_DECIMAL}{An exact numeric.}
1252\twocolitem{SQL\_NUMERIC}{Same as SQL\_DECIMAL.}
1253\twocolitem{SQL\_SMALLINT}{A 2 byte integer.}
1254\twocolitem{SQL\_INTEGER}{A 4 byte integer.}
1255\twocolitem{SQL\_REAL}{A 4 byte floating point number.}
1256\twocolitem{SQL\_FLOAT}{An 8 byte floating point number.}
1257\twocolitem{SQL\_DOUBLE}{Same as SQL\_FLOAT.}
1258\end{twocollist}
1259
fa482912
JS
1260\wxheading{See also}
1261
1262\helpref{Database classes overview}{odbcoverview}
a660d684 1263
b25ba5b7 1264\subsection{A selection of SQL commands [DEPRECATED]}\label{sqlcommands}
a660d684
KB
1265
1266The following is a very brief description of some common SQL commands, with
1267examples.
1268
fa482912
JS
1269\wxheading{See also}
1270
1271\helpref{Database classes overview}{odbcoverview}
1272
a660d684
KB
1273\subsubsection{Create}
1274
1275Creates a table.
1276
1277Example:
1278
1279\begin{verbatim}
1280CREATE TABLE Book
1281 (BookNumber INTEGER PRIMARY KEY
1282 , CategoryCode CHAR(2) DEFAULT 'RO' NOT NULL
1283 , Title VARCHAR(100) UNIQUE
1284 , NumberOfPages SMALLINT
1285 , RetailPriceAmount NUMERIC(5,2)
1286 )
1287\end{verbatim}
1288
1289\subsubsection{Insert}
1290
1291Inserts records into a table.
1292
1293Example:
1294
1295\begin{verbatim}
1296INSERT INTO Book
1297 (BookNumber, CategoryCode, Title)
1298 VALUES(5, 'HR', 'The Lark Ascending')
1299\end{verbatim}
1300
1301\subsubsection{Select}
1302
1303The Select operation retrieves rows and columns from a table. The criteria
1304for selection and the columns returned may be specified.
1305
1306Examples:
1307
1308\verb$SELECT * FROM Book$
1309
1310Selects all rows and columns from table Book.
1311
1312\verb$SELECT Title, RetailPriceAmount FROM Book WHERE RetailPriceAmount > 20.0$
1313
1314Selects columns Title and RetailPriceAmount from table Book, returning only
1315the rows that match the WHERE clause.
1316
1317\verb$SELECT * FROM Book WHERE CatCode = 'LL' OR CatCode = 'RR'$
1318
1319Selects all columns from table Book, returning only
1320the rows that match the WHERE clause.
1321
1322\verb$SELECT * FROM Book WHERE CatCode IS NULL$
1323
1324Selects all columns from table Book, returning only rows where the CatCode column
1325is NULL.
1326
1327\verb$SELECT * FROM Book ORDER BY Title$
1328
1329Selects all columns from table Book, ordering by Title, in ascending order. To specify
1330descending order, add DESC after the ORDER BY Title clause.
1331
1332\verb$SELECT Title FROM Book WHERE RetailPriceAmount >= 20.0 AND RetailPriceAmount <= 35.0$
1333
1334Selects records where RetailPriceAmount conforms to the WHERE expression.
1335
1336\subsubsection{Update}
1337
1338Updates records in a table.
1339
1340Example:
1341
1342\verb$UPDATE Incident SET X = 123 WHERE ASSET = 'BD34'$
1343
1344This example sets a field in column `X' to the number 123, for the record
1345where the column ASSET has the value `BD34'.
1346
b25ba5b7
GT
1347
1348