Lines Matching refs:to
3 Following is a detailed overview of how to use the wxWidgets ODBC classes - \helpref{wxDb}{wxdb}
6 referred to herein as the wxODBC classes.
14 support to be in compliance with specifications, different database vendors
16 requires all user names for the datasources to be supplied in uppercase
18 to make this transparent to the programmer when using functions that require
23 supported databases is certain to grow as more users start implementing
44 An up-to-date list can be obtained by looking in the comments of the function
49 emulate much of the functionality of an ODBC connection to a dBase table.
56 First, if you are not familiar with SQL and ODBC, go to your local bookstore
57 and pick up a good book on each. This documentation is not meant to teach
62 things you will need to un-learn. First some terminology as these phrases will
71 commands sent by your application and converts them to the SQL format expected
74 the ODBC driver which in turn has a connection to the target datasource.
82 \twocolitem{Rows}{Similar to records in old relational databases, a row is a
87 \twocolitem{Query}{Request from the client to the datasource asking for
92 in a query sent to the datasource. Dependent on drivers, a result set
93 typically remains at the datasource (no data is transmitted to the ODBC driver)
94 until the client actually instructs the ODBC driver to retrieve it.}
96 generates, indicating the next record that will be returned to the client
98 \twocolitem{Scrolling cursors}{Scrolling refers to the movement of cursors
101 cursors, once a row in the result set has been returned to the ODBC driver
102 and on to the client, there is no way to have the cursor move backward in
103 the result set to look at the row that is previous to the current row in
111 must take this in to account.}
117 connection to the datasource are all committed/rolled back at the same
118 time when a call to
122 that allow the datasource to quickly locate data rows based on the values
123 of certain columns. Without indexes, the datasource would need to do a
128 Before you are able to read data from a data table in a datasource, you must
129 have a connection to the datasource. Each datasource connection may be used
130 to open multiple tables all on the same connection (number of tables open are
132 the client workstation). Multiple connections can be opened to the same
136 When a query is performed, the client passes the query to the ODBC driver,
137 and the driver then translates it and passes it along to the datasource. The
140 for the requested data. The client simply waits for a status to come back
144 database server side, or is transferred to the machine that the driver is
146 some or all of the result set to be returned before any data rows are
147 returned to the client application.
149 Result sets do not need to include all columns of every row matching the
161 then 3, etc. With forward only cursors, once the cursor has moved to
164 allow you to request the previous row from the result set, actually
170 cursors and you wish to use them, make the appropriate changes in setup.h
171 to enable them (see \helpref{wxODBC - Compiling}{wxodbccompiling}). For greatest portability between
177 There is a limit to the number of cursors that can be open on each connection
178 to the datasource, and usually a maximum number of cursors for the datasource
187 use for additional cursors is to track multiple scroll points in result
194 tables, etc) through ODBC, the program must issue a "commit" to the
195 datasource to tell the datasource that the action(s) it has been told to
196 perform are to be recorded as permanent. Until a commit is performed,
198 have been made (although there are databases that can be configured to
201 will be able to see the changes that are uncommitted. Check your
202 database's documentation/configuration to verify this before relying on it
207 to do since the last commit that was performed.
217 Before you are able to access a datasource, you must have installed and
219 covered in detail here. But here are a few details to get you started.
222 database product. In practice, many of these drivers have proven to be slow
224 you using the ODBC interface to their products; they want you to use their
225 applications to access the data.
227 Whatever the reason, for database-intensive applications, you may want to
233 Under Microsoft Windows, install the ODBC driver you are planning to use. You
234 will then use the ODBC Administrator in the Control Panel to configure an
238 available to all users (if you are logged in as 'administrator'), otherwise
239 the datasource will only be available to the user who configured the DSN.
262 The wxWidgets setup.h file has several settings in it pertaining to compiling
266 \twocolitem{wxUSE\_ODBC}{This must be set to 1 in order for the compiler to
267 compile the wxODBC classes. Without setting this to 1, there will be no
268 access to any of the wxODBC classes. The default is 0.}
273 cursors). This default can be overridden by passing a second parameter to
277 renaming efforts were done to the ODBC classes to get naming conventions
278 similar to those used throughout wxWidgets, as well as to preface all wxODBC
281 was added to allow those programs written for v2.0 to still compile using the
282 old naming conventions. These deprecated names are all {\tt\#}define'd to their
291 You are required to include the "odbc32.lib" provided by your compiler vendor
292 in the list of external libraries to be linked in. If using the makefiles
318 Following each of these steps is detailed to explain the step, and to
320 to when first starting to use the classes. Throughout the steps, small
321 snippets of code are provided to show the syntax of performing the step. A
328 To be able to connect to a datasource through the ODBC driver, a program must
337 The 'Henv' member is the environment handle used to access memory for use by the
339 to the Datasource" section.
341 The 'Dsn' must exactly match the datasource name used to configure the ODBC
344 The 'Uid' is the User ID that is to be used to log in to the datasource. This
346 datasource to which you are connecting. The user that the connection is
348 connection will allow the program to have when using the connection that
349 this connection information was used to establish. Some datasources are
350 case sensitive for User IDs, and though the wxODBC classes attempt to hide
351 this from you by manipulating whatever data you pass in to match the
352 datasource's needs, it is always best to pass the 'Uid' in the case that
357 The wxODBC classes do NOT try to manage the case of the 'AuthStr' at all.
358 It is passed verbatim to the datasource, so you must use the case that the
362 FoxPro, text files). It contains a full path to the location where the
364 slashes '/' rather than backslashes '\' to avoid compatibility differences
368 they will be used to write our own ODBC Administrator type program that will
370 work has been done on this to date.
374 There are two methods of establishing a connection to a datasource. You
376 or you may use the caching functions provided with the wxODBC classes to
382 using this though, you must allocate an environment handle to the 'Henv'
393 To allocate an environment handle for the ODBC connection to use, the
400 wxMessageBox("Unable to allocate an ODBC environment handle",
421 environment handle, telling the constructor to allocate a handle during
425 Once the wxDbConnectInf instance is initialized, you are ready to
426 connect to the datasource.
437 The first line does the house keeping needed to initialize all
439 to the ODBC driver to open a connection to its associated datasource using
440 the parameters supplied in the call to \helpref{wxDb::Open}{wxdbopen}.
442 A more advanced form of opening a connection is to use the connection
444 mechanisms perform the same functions as the manual approach to opening a
447 needing to do the coding.
449 To use the caching function \helpref{wxDbGetConnection}{wxdbfunctions} to get
450 a connection to a datasource, simply call it with a single parameter of the
463 that was no longer in use. Any wxDb instance created with a call to
465 connections. When a program is finished with a connection, a call to
468 call to \helpref{wxDbGetConnection}{wxdbfunctions} that needs a connection
470 connections remain cached until a call to \helpref{wxDbCloseConnections}{wxdbfunctions} is made,
473 Besides the obvious advantage of using the single command caching routine to
477 perform many queries against the datasource to determine the datasource's
480 few seconds to establish the new connection (with well-balanced systems,
488 connection to the \helpref{wxDb::Open}{wxdbopen} function yourself to gain the performance
498 will default to only allowing cursor scrolling to be either forward only,
501 compile the wxWidgets library. The library default is to only support
516 functions of the wxDb class (see \helpref{wxDb::GetData}{wxdbgetdata}). But to make life much
518 that would be necessary to do this, wrapping it in an intuitive class of APIs.
521 class is to create a wxDbTable instance.
529 datasource connection to be used to access the table, the name of the
530 primary table that is to be accessed with the datasource's tables, how many
531 columns of each row are going to be returned, the name of the view of the
532 table that will actually be used to query against (works with Oracle only
534 finally the path to the table, if different than the path specified when
535 connecting to the datasource.
541 using this instance of the wxDbTable. Any calls to \helpref{wxDb::CommitTrans}{wxdbcommittrans}
545 then all the cursors and other overhead associated with being able to
550 If a table is to be accessed via a wxDbTable instance, and the table will
551 only be read from, not written to, there is a performance benefit (not as
552 many cursors need to be maintained/updated, hence speeding up access times),
553 as well as a resource savings due to fewer cursors being created for the
557 When defining the columns to be retrievable by the wxDbTable instance, you
558 can specify anywhere from one column up to all columns in the table.
567 Notice that column definitions start at index 0 and go up to one less than
571 The above lines of code "bind" the datasource columns specified to the
573 makes a call to \helpref{wxDbTable::GetNext}{wxdbtablegetnext} (or any other function that retrieves
574 data from the result set), the variables that are bound to the columns will
579 The bound memory variables have undefined data in them until a call to a
583 initialized to any data by the wxODBC classes, and they still contain
584 undefined data after a call to \helpref{wxDbTable::Query}{wxdbtablequery}. Only
585 after a successful call to one of the ::GetXxxx() functions is made do the
588 It is not necessary to define column definitions for columns whose data is
589 not going to be returned to the client. For example, if you want to query
593 needed to define one column above.
616 The only reason that a call to \helpref{wxDbTable::Open}{wxdbtableopen} is likely to fail is if the
617 user has insufficient privileges to even SELECT the table. Other problems
618 could occur, such as being unable to bind columns, but these other reason
619 point to some lack of resource (like memory). Any errors generated
620 internally in the \helpref{wxDbTable::Open}{wxdbtableopen} function are logged to the error log
626 define what data we want the datasource to collect in to a result set, tell
627 it where to get the data from, and in which sequence we want the data returned.
631 // are to be returned in the result set
644 The above lines will be used to tell the datasource to return in the result
656 selection criteria. What this sequence is can be hard to determine.
657 Typically it depends on the index that the datasource used to find the
659 to return data in a certain sequence when you have not provided an ORDERBY
661 tuned to be COST-based, SPEED-based, or some other basis for how it gets
666 sort the items before making the result set available to the client.
667 Creating efficient indexes that cause the data to be "found" in the correct
669 majority of cases, the database will be able to sort the records faster
674 data columns ('AGE') will be used to sub-sort the result set.
676 The FROM clause in this example is blanked, as we are not going to be
682 ask the datasource to perform the search and create a result set that
686 // Instruct the datasource to perform a query based on the
702 database was able to successfully complete the requested query using the
706 \normalbox{IMPORTANT: The result created by the call to
709 determined the record matched the search criteria, or it is a pointer to
714 do not use snapshots, but it is important to mention so that your application
717 To retrieve the data, one of the data fetching routines must be used to
718 request a row from the result set, and to store the data from the result
722 result set is empty (no rows matched the criteria), then any calls to
739 call to \helpref{wxDbTable::Query}{wxdbtablequery}, it actually returns the first record
773 non-caching or caching method was used to obtain the datasource connection.
787 If the program used the \helpref{wxDbGetConnection}{wxdbfunctions} function to get a datasource
788 connection, the following is the code that should be used to free the
812 it is safe to release the environment handle:
819 to allocate its own SQL environment handle, leaving scope or destruction of the
826 \normalbox{Remember to never release this environment handle if there are any
832 task of writing an application to run on a single type of computer system.
833 The classes need to be cross-platform for different operating systems, and
834 they also needed to take in to account different database manufacturers and
836 of OS/database/drivers, it is impossible to say that these classes will work
839 application from one environment to another. But that is what makes
844 manufacturers in regards to these wxODBC classes is the lack of
845 standard error codes being returned to the calling program. Sometimes
849 In all the tested databases, every effort has been made to determine
851 to check for specific error codes (such as TABLE DOES NOT EXIST when
852 you try to open a table that has not been created yet). Adding support
860 wxODBC classes have between different datasources. An up to date
866 \item Currently the only database supported by the wxODBC classes to support VIEWS
871 NOTE: dBase is not a true ODBC datasource. You only have access to as much
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}
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
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)
922 unicode or non-unicode builds that is specific to the wxODBC classes is to
928 Simplest example of establishing/opening a connection to an ODBC datasource,
929 binding variables to the columns for read/write usage, opening an
934 NOTE: Very basic error handling is shown here, to reduce the size of the
935 code and to make it more easily readable. The HandleError() function uses the wxDbLogExtendedErrorMsg() function for retrieving database error messages.
941 #include "wx/log.h" // #included to enable output of messages only
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
955 // Get the database errors and append them to the error message
980 wxDbTable *table = NULL; // Data table to access
1020 // Bind the columns that you wish to retrieve. Note that there must be
1021 // 'numTableColumns' calls to SetColDefs(), to match the wxDbTable def
1023 // Not all columns need to be bound, only columns whose values are to be
1024 // returned back to the client.
1060 // Must commit the insert to write the data to the DB
1067 // Set the WHERE clause to limit the result set to return
1079 // Instruct the datasource to perform a query based on the
1087 // there are no more records to read
1093 // Code to display 'msg' here
1109 // Must commit the deletion to the database
1121 // (meaning release it back in to the cache of datasource
1122 // connections) for the next time a call to wxDbGetConnection()
1131 // The program is now ending, so we need to close
1140 wxUnusedVar(argc); // Here just to prevent compiler warnings
1141 wxUnusedVar(argv); // Here just to prevent compiler warnings
1217 Selects records where RetailPriceAmount conforms to the WHERE expression.
1227 This example sets a field in column `X' to the number 123, for the record