| 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 | |