\section{Database classes overview}\label{odbcoverview}
-Following is a detailed overview of how to use the wxWindows ODBC classes - \helpref{wxDb}{wxdb}
+Following is a detailed overview of how to use the wxWidgets ODBC classes - \helpref{wxDb}{wxdb}
and \helpref{wxDbTable}{wxdbtable} and their associated functions. These are
the ODBC classes donated by Remstar International, and are collectively
referred to herein as the wxODBC classes.
users have successfully used the classes with the following datasources:
\begin{itemize}\itemsep=0pt
-\item Oracle (v7, v8, v8i)
-\item Sybase (ASA and ASE)
-\item MS SQL Server (v7 - minimal testing)
-\item MS Access (97 and 2000)
-\item MySQL
+\item DB2
\item DBase (IV, V)**
-\item PostgreSQL
+\item Firebird
\item INFORMIX
-\item VIRTUOSO
-\item DB2
\item Interbase
+\item MS SQL Server (v7 - minimal testing)
+\item MS Access (97, 2000, 2002, and 2003)
+\item MySQL (2.x and 3.5 - use the 2.5x drivers though)
+\item Oracle (v7, v8, v8i)
\item Pervasive SQL
+\item PostgreSQL
+\item Sybase (ASA and ASE)
+\item XBase Sequiter
+\item VIRTUOSO
\end{itemize}
An up-to-date list can be obtained by looking in the comments of the function
For each result set, a cursor is maintained (typically by the database)
which keeps track of where in the result set the user currently is.
Depending on the database, ODBC driver, and how you configured the
-wxWindows ODBC settings in setup.h (see \helpref{wxODBC - Compiling}{wxodbccompiling}), cursors can be
+wxWidgets ODBC settings in setup.h (see \helpref{wxODBC - Compiling}{wxodbccompiling}), cursors can be
either forward or backward scrolling. At a minimum, cursors must scroll
forward. For example, if a query resulted in a result set with 100 rows,
as the data is read by the client application, it will read row 1, then 2,
Under Unix, iODBC is used for implementation of the ODBC API. To compile the
wxODBC classes, you must first obtain iODBC from \urlref{http://www.iodbc.org}{www.iodbc.org} and install it.
-(Note: wxWindows currently includes a version of iODBC.) Then you must create the file "~/.odbc.ini" (or optionally create
+(Note: wxWidgets currently includes a version of iODBC.) Then you must create the file "~/.odbc.ini" (or optionally create
"/etc/odbc.ini" for access for all users on the system). This file contains
the settings for your system/datasource. Below is an example section of a
odbc.ini file for use with the "samples/db" sample program using MySQL:
\subsection{wxODBC - Compiling}\label{wxodbccompiling}
-The wxWindows setup.h file has several settings in it pertaining to compiling
+The wxWidgets setup.h file has several settings in it pertaining to compiling
the wxODBC classes.
\begin{twocollist}\itemsep=0pt
(see the section in "WHERE TO START" on cursors for more information on
cursors). This default can be overridden by passing a second parameter to
either the \helpref{wxDbGetConnection}{wxdbfunctions} or
-\helpref{wxDb constructor}{wxdbconstr}. The default is 1.}
+\helpref{wxDb constructor}{wxdbctor}. The default is 1.}
\twocolitem{wxODBC\_BACKWARD\_COMPATABILITY}{Between v2.0 and 2.2, massive
renaming efforts were done to the ODBC classes to get naming conventions
-similar to those used throughout wxWindows, as well as to preface all wxODBC
+similar to those used throughout wxWidgets, as well as to preface all wxODBC
classes names and functions with a wxDb preface. Because this renaming would
affect applications written using the v2.0 names, this compile-time directive
was added to allow those programs written for v2.0 to still compile using the
You are required to include the "odbc32.lib" provided by your compiler vendor
in the list of external libraries to be linked in. If using the makefiles
-supplied with wxWindows, this library should already be included for use with
+supplied with wxWidgets, this library should already be included for use with
makefile.b32, makefile.vc, and makefile.g95.
-You cannot compile the wxODBC classes under Win16 - sorry.
-
\normalbox{MORE TO COME}
{\it Under Unix}
directory indicating where the data file is stored, is required for Text and
dBase drivers for ODBC.
-The wxWindows data class wxDbConnectInf exists for holding all of these
+The wxWidgets data class wxDbConnectInf exists for holding all of these
values, plus some others that may be desired.
The 'Henv' member is the environment handle used to access memory for use by the
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
+compile the wxWidgets 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
\begin{verbatim}
table->SetColDefs(0, "FIRST_NAME", DB_DATA_TYPE_VARCHAR, FirstName,
- SQL_C_CHAR, sizeof(name), true, true);
+ SQL_C_WXCHAR, sizeof(FirstName), true, true);
table->SetColDefs(1, "LAST_NAME", DB_DATA_TYPE_VARCHAR, LastName,
- SQL_C_CHAR, sizeof(LastName), true, true);
+ SQL_C_WXCHAR, sizeof(LastName), true, true);
\end{verbatim}
Notice that column definitions start at index 0 and go up to one less than
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.
+it where to get the data from, and in which sequence we want the data returned.
\begin{verbatim}
// the WHERE clause limits/specifies which rows in the table
\subsection{wxODBC - Known Issues}\label{wxodbcknownissues}
-As with creating wxWindows, writing the wxODBC classes was not the simple
+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
\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 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}
{\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.
+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, setting the query parameters
+existing table in the datasource, inserting a record, setting query parameters
(where/orderBy/from), querying the datasource, reading each row of the
-result set, then cleaning up.
+result set, deleting a record, releasing the connection, 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.
+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}
-wxDbConnectInf *DbConnectInf = NULL;
+// ----------------------------------------------------------------------------
+// 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;
+}
-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"
+// ----------------------------------------------------------------------------
+// entry point
+// ----------------------------------------------------------------------------
+int main(int argc, char **argv)
+{
+wxDbConnectInf *DbConnectInf = NULL; // DB connection information
-bool errorOccured = false;
+wxDb *db = NULL; // Database connection
-const wxChar tableName[] = "CONTACTS";
-const UWORD numTableColumns = 2; // Number of bound columns
+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"
-FirstName[0] = 0;
-LastName[0] = 0;
+wxString msg; // Used for display messages
-DbConnectInf = new wxDbConnectInf(NULL,"MyDSN","MyUserName", "MyPassword");
+// -----------------------------------------------------------------------
+// DEFINE THE CONNECTION HANDLE FOR THE DATABASE
+// -----------------------------------------------------------------------
+DbConnectInf = new wxDbConnectInf(NULL,
+ wxT("CONTACTS-SqlServer"),
+ wxT("sa"),
+ wxT("abk"));
+// Error checking....
if (!DbConnectInf || !DbConnectInf->GetHenv())
{
- wxMessageBox("Unable to allocate an ODBC environment handle",
- "DB CONNECTION ERROR", wxOK | wxICON_EXCLAMATION);
- return;
-}
+ return HandleError(wxT("DB ENV ERROR: Cannot allocate ODBC env handle"));
+}
-// Get a database connection from the cached connections
+
+// -----------------------------------------------------------------------
+// GET A DATABASE CONNECTION
+// -----------------------------------------------------------------------
db = wxDbGetConnection(DbConnectInf);
-// Create the table connection
-table = new wxDbTable(db, tableName, numTableColumns, "",
- !wxDB_QUERY_ONLY, "");
+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 definition
+// '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, "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);
+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());
+}
-// 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'");
+// -----------------------------------------------------------------------
+// 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("LAST_NAME");
+table->SetOrderByClause(wxT("LAST_NAME"));
// No other tables (joins) are used for this query
-table->SetFromClause("");
+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())
{
- wxMessageBox("Error on Query()","ERROR!",
- wxOK | wxICON_EXCLAMATION);
- errorOccured = true;
+ return HandleError(wxT("QUERY ERROR: "), table->GetDb());
}
-wxString msg;
-
-// Start and continue reading every record in the table
-// displaying info about each record read.
+// Loop through all rows matching the query criteria until
+// there are no more records to 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);
+ 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());
}
-// If the wxDbTable instance was successfully created
-// then delete it as I am done with it now.
-if (table)
+
+// -----------------------------------------------------------------------
+// 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'")))
{
- delete table;
- table = NULL;
+ 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);
-// If we have a valid wxDb instance, then free the connection
+// 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.
-if (db)
-{
- wxDbFreeConnection(db);
- db = NULL;
-}
+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.
// Release the environment handle that was created
// for use with the ODBC datasource connections
-delete DbConnectInf;
+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}
\helpref{Database classes overview}{odbcoverview}
-\subsubsection{Create}
+\subsubsection{Create}\label{odbccreateexample}
Creates a table.
)
\end{verbatim}
-\subsubsection{Insert}
+\subsubsection{Insert}\label{odbcinsertexample}
Inserts records into a table.
VALUES(5, 'HR', 'The Lark Ascending')
\end{verbatim}
-\subsubsection{Select}
+\subsubsection{Select}\label{odbcselectexample}
The Select operation retrieves rows and columns from a table. The criteria
for selection and the columns returned may be specified.
Selects records where RetailPriceAmount conforms to the WHERE expression.
-\subsubsection{Update}
+\subsubsection{Update}\label{odbcupdateexample}
Updates records in a table.
projects / wxWidgets.git / blobdiff