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