+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 wxWidgets, 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
+into some incompatibilities or unsupported features when moving your
+application from one environment to another. But that is what makes
+cross-platform programming fun. It 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 wxWidgets 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}
+
+As of v2.6 of wxWidgets, the wxODBC classes now fully support the compilation
+and use of the classes in a Unicode build of wxWidgets, assuming the compiler
+and OS on which the program will be compiled/run is Unicode capable.
+
+The one major difference in writing code that can be compiled in either
+unicode or non-unicode builds that is specific to the wxODBC classes is to
+use the SQL\_C\_WXCHAR datatype for string columns rather than SQL\_C\_CHAR or
+SQL\_C\_WCHAR.
+
+\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, inserting a record, setting query parameters
+(where/orderBy/from), querying the datasource, reading each row of the
+result set, deleting a record, releasing the connection, then cleaning up.
+
+NOTE: Very basic error handling is shown here, to reduce the size of the
+code and to make it more easily readable. The HandleError() function uses the wxDbLogExtendedErrorMsg() function for retrieving database error messages.
+
+\begin{verbatim}
+// ----------------------------------------------------------------------------
+// HEADERS
+// ----------------------------------------------------------------------------
+#include "wx/log.h" // #included to enable output of messages only
+#include "wx/dbtable.h"
+
+// ----------------------------------------------------------------------------
+// FUNCTION USED FOR HANDLING/DISPLAYING ERRORS
+// ----------------------------------------------------------------------------
+// Very generic error handling function.
+// If a connection to the database is passed in, then we retrieve all the
+// database errors for the connection and add them to the displayed message
+int HandleError(wxString errmsg, wxDb *pDb=NULL)
+{
+ // Retrieve all the error message for the errors that occurred
+ wxString allErrors;
+ if (!pDb == NULL)
+ // Get the database errors and append them to the error message
+ allErrors = wxDbLogExtendedErrorMsg(errmsg.c_str(), pDb, 0, 0);
+ else
+ allErrors = errmsg;
+
+ // Do whatever you wish with the error message here
+ // wxLogDebug() is called inside wxDbLogExtendedErrorMsg() so this
+ // console program will show the errors in the console window,
+ // but these lines will show the errors in RELEASE builds also
+ wxFprintf(stderr, wxT("\n%s\n"), allErrors.c_str());
+ fflush(stderr);
+
+ return 1;
+}
+
+
+// ----------------------------------------------------------------------------
+// entry point
+// ----------------------------------------------------------------------------
+int main(int argc, char **argv)
+{
+wxDbConnectInf *DbConnectInf = NULL; // DB connection information
+
+wxDb *db = NULL; // Database connection
+
+wxDbTable *table = NULL; // Data table to access
+const wxChar tableName[] = wxT("USERS"); // Name of database table
+const UWORD numTableColumns = 2; // Number table columns
+wxChar FirstName[50+1]; // column data: "FIRST_NAME"
+wxChar LastName[50+1]; // column data: "LAST_NAME"
+
+wxString msg; // Used for display messages
+
+// -----------------------------------------------------------------------
+// DEFINE THE CONNECTION HANDLE FOR THE DATABASE
+// -----------------------------------------------------------------------
+DbConnectInf = new wxDbConnectInf(NULL,
+ wxT("CONTACTS-SqlServer"),
+ wxT("sa"),
+ wxT("abk"));
+
+// Error checking....
+if (!DbConnectInf || !DbConnectInf->GetHenv())
+{
+ return HandleError(wxT("DB ENV ERROR: Cannot allocate ODBC env handle"));
+}
+
+
+// -----------------------------------------------------------------------
+// GET A DATABASE CONNECTION
+// -----------------------------------------------------------------------
+db = wxDbGetConnection(DbConnectInf);
+
+if (!db)
+{
+ return HandleError(wxT("CONNECTION ERROR - Cannot get DB connection"));
+}
+
+
+// -----------------------------------------------------------------------
+// DEFINE THE TABLE, AND THE COLUMNS THAT WILL BE ACCESSED
+// -----------------------------------------------------------------------
+table = new wxDbTable(db, tableName, numTableColumns, wxT(""),
+ !wxDB_QUERY_ONLY, wxT(""));
+//
+// Bind the columns that you wish to retrieve. Note that there must be
+// 'numTableColumns' calls to SetColDefs(), to match the wxDbTable def
+//
+// Not all columns need to be bound, only columns whose values are to be
+// returned back to the client.
+//
+table->SetColDefs(0, wxT("FIRST_NAME"), DB_DATA_TYPE_VARCHAR, FirstName,
+ SQL_C_WXCHAR, sizeof(FirstName), true, true);
+table->SetColDefs(1, wxT("LAST_NAME"), DB_DATA_TYPE_VARCHAR, LastName,
+ SQL_C_WXCHAR, sizeof(LastName), true, true);
+
+
+// -----------------------------------------------------------------------
+// CREATE (or RECREATE) THE TABLE IN THE DATABASE
+// -----------------------------------------------------------------------
+if (!table->CreateTable(true)) //NOTE: No CommitTrans is required
+{
+ return HandleError(wxT("TABLE CREATION ERROR: "), table->GetDb());
+}
+
+
+// -----------------------------------------------------------------------
+// OPEN THE TABLE FOR ACCESS
+// -----------------------------------------------------------------------
+if (!table->Open())
+{
+ return HandleError(wxT("TABLE OPEN ERROR: "), table->GetDb());
+}
+
+
+// -----------------------------------------------------------------------
+// INSERT A NEW ROW INTO THE TABLE
+// -----------------------------------------------------------------------
+wxStrcpy(FirstName, wxT("JULIAN"));
+wxStrcpy(LastName, wxT("SMART"));
+if (!table->Insert())
+{
+ return HandleError(wxT("INSERTION ERROR: "), table->GetDb());
+}
+
+// Must commit the insert to write the data to the DB
+table->GetDb()->CommitTrans();
+
+
+// -----------------------------------------------------------------------
+// RETRIEVE ROWS FROM THE TABLE BASED ON SUPPLIED CRITERIA
+// -----------------------------------------------------------------------
+// Set the WHERE clause to limit the result set to return
+// all rows that have a value of 'JULIAN' in the FIRST_NAME
+// column of the table.
+table->SetWhereClause(wxT("FIRST_NAME = 'JULIAN'"));
+
+// Result set will be sorted in ascending alphabetical
+// order on the data in the 'LAST_NAME' column of each row
+table->SetOrderByClause(wxT("LAST_NAME"));
+
+// No other tables (joins) are used for this query
+table->SetFromClause(wxT(""));
+
+// Instruct the datasource to perform a query based on the
+// criteria specified above in the where/orderBy/from clauses.
+if (!table->Query())
+{
+ return HandleError(wxT("QUERY ERROR: "), table->GetDb());
+}
+
+// Loop through all rows matching the query criteria until
+// there are no more records to read
+while (table->GetNext())
+{
+ msg.Printf(wxT("Row #%lu -- First Name : %s Last Name is %s"),
+ table->GetRowNum(), FirstName, LastName);
+
+ // Code to display 'msg' here
+ wxLogMessage(wxT("\n%s\n"), msg.c_str());
+}
+
+
+// -----------------------------------------------------------------------
+// DELETE A ROW FROM THE TABLE
+// -----------------------------------------------------------------------
+// Select the row which has FIRST_NAME of 'JULIAN' and LAST_NAME
+// of 'SMART', then delete the retrieved row
+//
+if (!table->DeleteWhere(wxT("FIRST_NAME = 'JULIAN' and LAST_NAME = 'SMART'")))
+{
+ return HandleError(wxT("DELETION ERROR: "), table->GetDb());
+}
+
+// Must commit the deletion to the database
+table->GetDb()->CommitTrans();
+
+
+// -----------------------------------------------------------------------
+// TAKE CARE OF THE ODBC CLASS INSTANCES THAT WERE BEING USED
+// -----------------------------------------------------------------------
+// If the wxDbTable instance was successfully created
+// then delete it as we are done with it now.
+wxDELETE(table);
+
+// Free the cached connection
+// (meaning release it back in to the cache of datasource
+// connections) for the next time a call to wxDbGetConnection()
+// is made.
+wxDbFreeConnection(db);
+db = NULL;
+
+
+// -----------------------------------------------------------------------
+// CLEANUP BEFORE EXITING APP
+// -----------------------------------------------------------------------
+// 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
+wxDELETE(DbConnectInf);
+
+wxUnusedVar(argc); // Here just to prevent compiler warnings
+wxUnusedVar(argv); // Here just to prevent compiler warnings
+
+return 0;
+}
+\end{verbatim}
+
+\subsection{A selection of SQL commands}\label{sqlcommands}
projects / wxWidgets.git / blobdiff