+The wxDb pointer that is returned is both initialized and opened. If
+something failed in creating or opening the connection, the return value
+from \helpref{wxDbGetConnection}{wxdbfunctions} will be NULL.
+
+The connection that is returned is either a new connection, or it is a
+"free" connection from the cache of connections that the class maintains
+that was no longer in use. Any wxDb instance created with a call to
+\helpref{wxDbGetConnection}{wxdbfunctions} is recorded in a linked list of established
+connections. When a program is finished with a connection, a call to
+\helpref{wxDbFreeConnection}{wxdbfunctions} is made, and the datasource
+connection will then be tagged as FREE, making it available for the next
+call to \helpref{wxDbGetConnection}{wxdbfunctions} that needs a connection
+using the same connection information (Dsn, Uid, AuthStr). The cached
+connections remain cached until a call to \helpref{wxDbCloseConnections}{wxdbfunctions} is made,
+at which time all cached connections are closed and deleted.
+
+Besides the obvious advantage of using the single command caching routine to
+obtain a datasource connection, using cached connections can be quite a
+performance boost as well. Each time that a new connection is created
+(not retrieved from the cache of free connections), the wxODBC classes
+perform many queries against the datasource to determine the datasource's
+datatypes and other fundamental behaviours. Depending on the hardware,
+network bandwidth, and datasource speed, this can in some cases take a
+few seconds to establish the new connection (with well-balanced systems,
+it should only be a fraction of a second). Re-using already established
+datasource connections rather than creating/deleting, creating/deleting
+connections can be quite a time-saver.
+
+Another time-saver is the "copy connection" features of both
+\helpref{wxDb::Open}{wxdbopen} and \helpref{wxDbGetConnection}{wxdbfunctions}.
+If manually creating a wxDb instance and opening it, you must pass an existing
+connection to the \helpref{wxDb::Open}{wxdbopen} function yourself to gain the performance
+benefit of copying existing connection settings. The
+\helpref{wxDbGetConnection}{wxdbfunctions} function automatically does this
+for you, checking the Dsn, Uid, and AuthStr parameters when you request
+a connection for any existing connections that use those same settings.
+If one is found, \helpref{wxDbGetConnection}{wxdbfunctions} copies the datasource settings for
+datatypes and other datasource specific information that was previously
+queried, rather than re-querying the datasource for all those same settings.
+
+One final note on creating a connection. When a connection is created, it
+will default to only allowing cursor scrolling to be either forward only,
+or both backward and forward scrolling. The default behavior is
+determined by the setting {\tt wxODBC\_FWD\_ONLY\_CURSORS} in setup.h when you
+compile the wxWindows library. The library default is to only support
+forward scrolling cursors only, though this can be overridden by parameters
+for wxDb() constructor or the \helpref{wxDbGetConnection}{wxdbfunctions}
+function. All datasources and ODBC drivers must support forward scrolling
+cursors. Many datasources support backward scrolling cursors, and many
+ODBC drivers support backward scrolling cursors. Before planning on using
+backward scrolling cursors, you must be certain that both your datasource
+and ODBC driver fully support backward scrolling cursors. See the small
+blurb about "Scrolling cursors" in the definitions at the beginning of
+this overview, or other details of setting the cursor behavior in the wxDb
+class documentation.
+
+{\bf Create Table Definition}
+
+Data can be accessed in a datasource's tables directly through various
+functions of the wxDb class (see \helpref{wxDb::GetData}{wxdbgetdata}). But to make life much
+simpler, the wxDbTable class encapsulates all of the SQL specific API calls
+that would be necessary to do this, wrapping it in an intuitive class of APIs.
+
+The first step in accessing data in a datasource's tables via the wxDbTable
+class is to create a wxDbTable instance.
+
+\begin{verbatim}
+ table = new wxDbTable(db, tableName, numTableColumns, "",
+ !wxDB_QUERY_ONLY, "");
+\end{verbatim}
+
+When you create the instance, you indicate the previously established
+datasource connection to be used to access the table, the name of the
+primary table that is to be accessed with the datasource's tables, how many
+columns of each row are going to be returned, the name of the view of the
+table that will actually be used to query against (works with Oracle only
+at this time), whether the data returned is for query purposes only, and
+finally the path to the table, if different than the path specified when
+connecting to the datasource.
+
+Each of the above parameters are described in detail in the wxDbTable
+class' description, but one special note here about the fifth
+parameter - the queryOnly setting. If a wxDbTable instance is created as
+{\tt wxDB\_QUERY\_ONLY}, then no inserts/deletes/updates can be performed
+using this instance of the wxDbTable. Any calls to \helpref{wxDb::CommitTrans}{wxdbcommittrans}
+or \helpref{wxDb::RollbackTrans}{wxdbrollbacktrans} against the datasource
+connection used by this wxDbTable instance are ignored by this instance. If
+the wxDbTable instance is created with {\tt !wxDB\_QUERY\_ONLY} as shown above,
+then all the cursors and other overhead associated with being able to
+insert/update/delete data in the table are created, and thereby those
+operations can then be performed against the associated table with this
+wxDbTable instance.
+
+If a table is to be accessed via a wxDbTable instance, and the table will
+only be read from, not written to, there is a performance benefit (not as
+many cursors need to be maintained/updated, hence speeding up access times),
+as well as a resource savings due to fewer cursors being created for the
+wxDbTable instance. Also, with some datasources, the number of
+simultaneous cursors is limited.
+
+When defining the columns to be retrievable by the wxDbTable instance, you
+can specify anywhere from one column up to all columns in the table.
+
+\begin{verbatim}
+ table->SetColDefs(0, "FIRST_NAME", DB_DATA_TYPE_VARCHAR, FirstName,
+ SQL_C_CHAR, sizeof(name), true, true);
+ table->SetColDefs(1, "LAST_NAME", DB_DATA_TYPE_VARCHAR, LastName,
+ SQL_C_CHAR, sizeof(LastName), true, true);
+\end{verbatim}
+
+Notice that column definitions start at index 0 and go up to one less than
+the number of columns specified when the wxDbTable instance was created
+(in this example, two columns - one with index 0, one with index 1).
+
+The above lines of code "bind" the datasource columns specified to the
+memory variables in the client application. So when the application
+makes a call to \helpref{wxDbTable::GetNext}{wxdbtablegetnext} (or any other function that retrieves
+data from the result set), the variables that are bound to the columns will
+have the column value stored into them. See the
+\helpref{wxDbTable::SetColDefs}{wxdbtablesetcoldefs}
+class documentation for more details on all the parameters for this function.
+
+The bound memory variables have undefined data in them until a call to a
+function that retrieves data from a result set is made
+(e.g. \helpref{wxDbTable::GetNext}{wxdbtablegetnext},
+\helpref{wxDbTable::GetPrev}{wxdbtablegetprev}, etc). The variables are not
+initialized to any data by the wxODBC classes, and they still contain
+undefined data after a call to \helpref{wxDbTable::Query}{wxdbtablequery}. Only
+after a successful call to one of the ::GetXxxx() functions is made do the
+variables contain valid data.
+
+It is not necessary to define column definitions for columns whose data is
+not going to be returned to the client. For example, if you want to query
+the datasource for all users with a first name of 'GEORGE', but you only want
+the list of last names associated with those rows (why return the FIRST\_NAME
+column every time when you already know it is 'GEORGE'), you would only have
+needed to define one column above.
+
+You may have as many wxDbTable instances accessing the same table using the
+same wxDb instance as you desire. There is no limit imposed by the classes
+on this. All datasources supported (so far) also have no limitations on this.
+
+{\bf Open the table}
+
+Opening the table is not technically doing anything with the datasource
+itself. Calling \helpref{wxDbTable::Open}{wxdbtableopen} simply does all the
+housekeeping of checking that the specified table exists, that the current
+connected user has at least SELECT privileges for accessing the table,
+setting up the requisite cursors, binding columns and cursors, and
+constructing the default INSERT statement that is used when a new row is
+inserted into the table (non-wxDB\_QUERY\_ONLY tables only).
+
+\begin{verbatim}
+ if (!table->Open())
+ {
+ // An error occurred opening (setting up) the table
+ }
+\end{verbatim}
+
+The only reason that a call to \helpref{wxDbTable::Open}{wxdbtableopen} is likely to fail is if the
+user has insufficient privileges to even SELECT the table. Other problems
+could occur, such as being unable to bind columns, but these other reason
+point to some lack of resource (like memory). Any errors generated
+internally in the \helpref{wxDbTable::Open}{wxdbtableopen} function are logged to the error log
+if SQL logging is turned on for the classes.
+
+{\bf Use the table}
+
+To use the table and the definitions that are now set up, we must first
+define what data we want the datasource to collect in to a result set, tell
+it where to get the data from, and in what sequence we want the data returned.
+
+\begin{verbatim}
+ // the WHERE clause limits/specifies which rows in the table
+ // are to be returned in the result set
+ table->SetWhereClause("FIRST_NAME = 'GEORGE'");
+
+ // Result set will be sorted in ascending alphabetical
+ // order on the data in the 'LAST_NAME' column of each row
+ // If the same last name is in the table for two rows,
+ // sub-sort on the 'AGE' column
+ table->SetOrderByClause("LAST_NAME, AGE");
+
+ // No other tables (joins) are used for this query
+ table->SetFromClause("");
+\end{verbatim}
+
+The above lines will be used to tell the datasource to return in the result
+all the rows in the table whose column "FIRST\_NAME" contains the name
+'GEORGE' (note the required use of the single quote around the string
+literal) and that the result set will return the rows sorted by ascending
+last names (ascending is the default, and can be overridden with the
+"DESC" keyword for datasources that support it - "LAST\_NAME DESC").
+
+Specifying a blank WHERE clause will result in the result set containing
+all rows in the datasource.
+
+Specifying a blank ORDERBY clause means that the datasource will return
+the result set in whatever sequence it encounters rows which match the
+selection criteria. What this sequence is can be hard to determine.
+Typically it depends on the index that the datasource used to find the
+rows which match the WHERE criteria. BEWARE - relying on the datasource
+to return data in a certain sequence when you have not provided an ORDERBY
+clause will eventually cause a problem for your program. Databases can be
+tuned to be COST-based, SPEED-based, or some other basis for how it gets
+your result set. In short, if you need your result set returned in a
+specific sequence, ask for it that way by providing an ORDERBY clause.
+
+Using an ORDERBY clause can be a performance hit, as the database must
+sort the items before making the result set available to the client.
+Creating efficient indexes that cause the data to be "found" in the correct
+ORDERBY sequence can be a big performance benefit. Also, in the large
+majority of cases, the database will be able to sort the records faster
+than your application can read all the records in (unsorted) and then sort
+them. Let the database do the work for you!
+
+Notice in the example above, a column that is not included in the bound
+data columns ('AGE') will be used to sub-sort the result set.
+
+The FROM clause in this example is blanked, as we are not going to be
+performing any table joins with this simple query. When the FROM clause
+is blank, it is assumed that all columns referenced are coming from
+the default table for the wxDbTable instance.
+
+After the selection criteria have been specified, the program can now
+ask the datasource to perform the search and create a result set that
+can be retrieved:
+
+\begin{verbatim}
+ // Instruct the datasource to perform a query based on the
+ // criteria specified above in the where/orderBy/from clauses.
+ if (!table->Query())
+ {
+ // An error occurred performing the query
+ }
+\end{verbatim}
+
+Typically, when an error occurs when calling \helpref{wxDbTable::Query}{wxdbtablequery}, it is a
+syntax problem in the WHERE clause that was specified. The exact SQL
+(datasource-specific) reason for what caused the failure of \helpref{wxDbTable::Query}{wxdbtablequery}
+(and all other operations against the datasource can be found by
+parsing the table's database connection's "errorList[]" array member for
+the stored text of the error.
+
+When the \helpref{wxDbTable::Query}{wxdbtablequery} returns true, the
+database was able to successfully complete the requested query using the
+provided criteria. This does not mean that there are any rows in the
+result set, it just mean that the query was successful.
+
+\normalbox{IMPORTANT: The result created by the call to
+\helpref{wxDbTable::Query}{wxdbtablequery} can take one of two forms. It is
+either a snapshot of the data at the exact moment that the database
+determined the record matched the search criteria, or it is a pointer to
+the row that matched the selection criteria. Which form of behavior is
+datasource dependent. If it is a snapshot, the data may have changed
+since the result set was constructed, so beware if your datasource
+uses snapshots and call \helpref{wxDbTable::Refresh}{wxdbtablerefresh}. Most larger brand databases
+do not use snapshots, but it is important to mention so that your application
+can handle it properly if your datasource does.}
+
+To retrieve the data, one of the data fetching routines must be used to
+request a row from the result set, and to store the data from the result
+set into the bound memory variables. After \helpref{wxDbTable::Query}{wxdbtablequery}
+has completed successfully, the default/current cursor is placed so it
+is pointing just before the first record in the result set. If the
+result set is empty (no rows matched the criteria), then any calls to
+retrieve data from the result set will return false.
+
+\begin{verbatim}
+ wxString msg;
+
+ while (table->GetNext())
+ {
+ msg.Printf("Row #%lu -- First Name : %s Last Name is %s",
+ table->GetRowNum(), FirstName, LastName);
+ wxMessageBox(msg, "Data", wxOK | wxICON_INFORMATION, NULL);
+ }
+\end{verbatim}
+
+The sample code above will read the next record in the result set repeatedly
+until the end of the result set has been reached. The first time that
+\helpref{wxDbTable::GetNext}{wxdbtablegetnext} is called right after the successful
+call to \helpref{wxDbTable::Query}{wxdbtablequery}, it actually returns the first record
+in the result set.
+
+When \helpref{wxDbTable::GetNext}{wxdbtablegetnext} is called and there are
+no rows remaining in the result set after the current cursor position,
+\helpref{wxDbTable::GetNext}{wxdbtablegetnext} (as well as all the other
+wxDbTable::GetXxxxx() functions) will return false.
+
+{\bf Close the table}
+
+When the program is done using a wxDbTable instance, it is as simple as
+deleting the table pointer (or if declared statically, letting the
+variable go out of scope). Typically the default destructor will take
+care of all that is required for cleaning up the wxDbTable instance.
+
+\begin{verbatim}
+ if (table)
+ {
+ delete table;
+ table = NULL;
+ }
+\end{verbatim}
+
+Deleting a wxDbTable instance releases all of its cursors, deletes the
+column definitions and frees the SQL environment handles used by the
+table (but not the environment handle used by the datasource connection
+that the wxDbTable instance was using).
+
+{\bf Close the datasource connection}
+
+After all tables that have been using a datasource connection have been
+closed (this can be verified by calling \helpref{wxDb::GetTableCount}{wxdbgettablecount}
+and checking that it returns 0), then you may close the datasource
+connection. The method of doing this is dependent on whether the
+non-caching or caching method was used to obtain the datasource connection.
+
+If the datasource connection was created manually (non-cached), closing the
+connection is done like this:
+
+\begin{verbatim}
+ if (db)
+ {
+ db->Close();
+ delete db;
+ db = NULL;
+ }
+\end{verbatim}
+
+If the program used the \helpref{wxDbGetConnection}{wxdbfunctions} function to get a datasource
+connection, the following is the code that should be used to free the
+connection(s):
+
+\begin{verbatim}
+ if (db)
+ {
+ wxDbFreeConnection(db);
+ db = NULL;
+ }
+\end{verbatim}
+
+Note that the above code just frees the connection so that it can be
+re-used on the next call the \helpref{wxDbGetConnection}{wxdbfunctions}. To actually dispose
+of the connection, releasing all of its resources (other than the
+environment handle), do the following:
+
+\begin{verbatim}
+ wxDbCloseConnections();
+\end{verbatim}
+
+{\bf Release the ODBC environment handle}
+
+Once all of the connections that used the ODBC environment handle (in
+this example it was stored in "DbConnectInf.Henv") have been closed, then
+it is safe to release the environment handle:
+
+\begin{verbatim}
+ DbConnectInf->FreeHenv();
+\end{verbatim}
+
+Or, if the long form of the constructor was used and the constructor was allowed
+to allocate its own SQL environment handle, leaving scope or destruction of the
+wxDbConnectInf will free the handle automatically.
+
+\begin{verbatim}
+ delete DbConnectInf;
+\end{verbatim}
+
+\normalbox{Remember to never release this environment handle if there are any
+connections still using the handle.}
+
+\subsection{wxODBC - Known Issues}\label{wxodbcknownissues}
+
+As with creating wxWindows, writing the wxODBC classes was not the simple
+task of writing an application to run on a single type of computer system.
+The classes need to be cross-platform for different operating systems, and
+they also needed to take in to account different database manufacturers and
+different ODBC driver manufacturers. Because of all the possible combinations
+of OS/database/drivers, it is impossible to say that these classes will work
+perfectly with datasource ABC, ODBC driver XYZ, on platform LMN. You may run
+in to some incompatibilities or unsupported features when moving your
+application from one environment to another. But that is what makes
+cross-platform programming fun. It is also pinpoints one of the great
+things about open source software. It can evolve!
+
+The most common difference between different database/ODBC driver
+manufacturers in regards to these wxODBC classes is the lack of
+standard error codes being returned to the calling program. Sometimes
+manufacturers have even changed the error codes between versions of
+their databases/drivers.
+
+In all the tested databases, every effort has been made to determine
+the correct error codes and handle them in the class members that need
+to check for specific error codes (such as TABLE DOES NOT EXIST when
+you try to open a table that has not been created yet). Adding support
+for additional databases in the future requires adding an entry for the
+database in the \helpref{wxDb::Dbms}{wxdbdbms} function, and then handling any error codes
+returned by the datasource that do not match the expected values.
+
+{\bf Databases}
+
+Following is a list of known issues and incompatibilities that the
+wxODBC classes have between different datasources. An up to date
+listing of known issues can be seen in the comments of the source
+for \helpref{wxDb::Dbms}{wxdbdbms}.
+
+{\it ORACLE}
+\begin{itemize}\itemsep=0pt
+\item Currently the only database supported by the wxODBC classes to support VIEWS
+\end{itemize}
+
+{\it DBASE}
+
+NOTE: dBase is not a true ODBC datasource. You only have access to as much
+functionality as the driver can emulate.
+
+\begin{itemize}\itemsep=0pt
+\item Does not support the SQL\_TIMESTAMP structure
+\item Supports only one cursor and one connect (apparently? with Microsoft driver only?)
+\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}
+\item Table names can only be 8 characters long
+\item Column names can only be 10 characters long
+\item Currently cannot CREATE a dBase table - bug or limitation of the drivers used??
+\item Currently cannot insert rows that have integer columns - bug??
+\end{itemize}
+
+{\it SYBASE (all)}
+\begin{itemize}\itemsep=0pt
+\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
+\item Ignores the keywords 'FOR UPDATE'. Use the HOLDLOCK functionality described above
+\end{itemize}
+
+{\it SYBASE (Enterprise)}
+\begin{itemize}\itemsep=0pt
+\item If a column is part of the Primary Key, the column cannot be NULL
+\item Maximum row size is somewhere in the neighborhood of 1920 bytes
+\end{itemize}
+
+{\it mySQL}
+\begin{itemize}\itemsep=0pt
+\item If a column is part of the Primary Key, the column cannot be NULL.
+\item Cannot support selecting for update [\helpref{wxDbTable::CanSelectForUpdate}{wxdbtablecanselectforupdate}]. Always returns false.
+\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)
+\item Does not support sub-queries in SQL statements
+\end{itemize}
+
+{\it POSTGRES}
+\begin{itemize}\itemsep=0pt
+\item Does not support the keywords 'ASC' or 'DESC' as of release v6.5.0
+\item Does not support sub-queries in SQL statements
+\end{itemize}
+
+{\it DB2}
+\begin{itemize}\itemsep=0pt
+\item Columns which are part of a primary key must be declared as NOT NULL
+\end{itemize}
+
+{\bf UNICODE with wxODBC classes}
+
+The ODBC classes support for Unicode is yet in early experimental stage and
+hasn't been tested extensively. It might work for you or it might not: please
+report the bugs/problems you have encountered in the latter case.
+
+\subsection{wxODBC - Sample Code}\label{wxodbcsamplecode1}
+
+Simplest example of establishing/opening a connection to an ODBC datasource,
+binding variables to the columns for read/write usage, opening an
+existing table in the datasource, setting the query parameters
+(where/orderBy/from), querying the datasource, reading each row of the
+result set, then cleaning up.
+
+NOTE: Not all error trapping is shown here, to reduce the size of the
+code and to make it more easily readable.
+
+\begin{verbatim}
+wxDbConnectInf *DbConnectInf = NULL;
+
+wxDb *db = NULL; // The database connection
+wxDbTable *table = NULL; // The data table to access
+
+wxChar FirstName[50+1]; // buffer for data from column "FIRST_NAME"
+wxChar LastName[50+1]; // buffer for data from column "LAST_NAME"
+
+bool errorOccured = false;
+
+const wxChar tableName[] = "CONTACTS";
+const UWORD numTableColumns = 2; // Number of bound columns
+
+FirstName[0] = 0;
+LastName[0] = 0;
+
+DbConnectInf = new wxDbConnectInf(NULL,"MyDSN","MyUserName", "MyPassword");
+
+if (!DbConnectInf || !DbConnectInf->GetHenv())
+{
+ wxMessageBox("Unable to allocate an ODBC environment handle",
+ "DB CONNECTION ERROR", wxOK | wxICON_EXCLAMATION);
+ return;
+}
+
+// Get a database connection from the cached connections
+db = wxDbGetConnection(DbConnectInf);
+
+// Create the table connection
+table = new wxDbTable(db, tableName, numTableColumns, "",
+ !wxDB_QUERY_ONLY, "");
+
+//
+// Bind the columns that you wish to retrieve. Note that there must be
+// 'numTableColumns' calls to SetColDefs(), to match the wxDbTable definition
+//
+// Not all columns need to be bound, only columns whose values are to be
+// returned back to the client.
+//
+table->SetColDefs(0, "FIRST_NAME", DB_DATA_TYPE_VARCHAR, FirstName,
+ SQL_C_CHAR, sizeof(name), true, true);
+table->SetColDefs(1, "LAST_NAME", DB_DATA_TYPE_VARCHAR, LastName,
+ SQL_C_CHAR, sizeof(LastName), true, true);
+
+// Open the table for access
+table->Open();
+
+// Set the WHERE clause to limit the result set to only
+// return all rows that have a value of 'GEORGE' in the
+// FIRST_NAME column of the table.
+table->SetWhereClause("FIRST_NAME = 'GEORGE'");
+
+// Result set will be sorted in ascending alphabetical
+// order on the data in the 'LAST_NAME' column of each row
+table->SetOrderByClause("LAST_NAME");
+
+// No other tables (joins) are used for this query
+table->SetFromClause("");
+
+// Instruct the datasource to perform a query based on the
+// criteria specified above in the where/orderBy/from clauses.
+if (!table->Query())
+{
+ wxMessageBox("Error on Query()","ERROR!",
+ wxOK | wxICON_EXCLAMATION);
+ errorOccured = true;
+}
+
+wxString msg;
+
+// Start and continue reading every record in the table
+// displaying info about each record read.
+while (table->GetNext())
+{
+ msg.Printf("Row #%lu -- First Name : %s Last Name is %s",
+ table->GetRowNum(), FirstName, LastName);
+ wxMessageBox(msg, "Data", wxOK | wxICON_INFORMATION, NULL);
+}
+
+// If the wxDbTable instance was successfully created
+// then delete it as I am done with it now.
+if (table)
+{
+ delete table;
+ table = NULL;
+}
+
+// If we have a valid wxDb instance, then free the connection
+// (meaning release it back in to the cache of datasource
+// connections) for the next time a call to wxDbGetConnection()
+// is made.
+if (db)
+{
+ wxDbFreeConnection(db);
+ db = NULL;
+}
+
+// The program is now ending, so we need to close
+// any cached connections that are still being
+// maintained.
+wxDbCloseConnections();
+
+// Release the environment handle that was created
+// for use with the ODBC datasource connections
+delete DbConnectInf;
+
+\end{verbatim}
projects / wxWidgets.git / blobdiff