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