From e31a9900251df6675d39234cd7eaaa5758ce920a Mon Sep 17 00:00:00 2001
From: George Tasker <gtasker@allenbrook.com>
Date: Fri, 30 Apr 2004 11:11:40 +0000
Subject: [PATCH] Updated the embedded sample program to reflect latest class
 functions.  Added a generic HandleError() function as part of the sample
 program to show a simple way of getting error messages fro database calls
 Insert/Query/Delete examples shown in the sample

git-svn-id: https://svn.wxwidgets.org/svn/wx/wxWidgets/trunk@27021 c3d73ce0-8a6f-49c7-b76d-6d57e0e08775
---
 docs/latex/wx/tdb.tex | 226 ++++++++++++++++++++++++++++--------------
 1 file changed, 151 insertions(+), 75 deletions(-)

diff --git a/docs/latex/wx/tdb.tex b/docs/latex/wx/tdb.tex
index e72687c568..5936f9b384 100644
--- a/docs/latex/wx/tdb.tex
+++ b/docs/latex/wx/tdb.tex
@@ -557,7 +557,7 @@ 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);
+                      SQL_C_CHAR, sizeof(FirstName), true, true);
     table->SetColDefs(1, "LAST_NAME", DB_DATA_TYPE_VARCHAR, LastName,
                       SQL_C_CHAR, sizeof(LastName), true, true);
 \end{verbatim}
@@ -920,136 +920,207 @@ report the bugs/problems you have encountered in the latter case.
 
 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_CHAR, sizeof(FirstName), true, true);
+table->SetColDefs(1, wxT("LAST_NAME"), DB_DATA_TYPE_VARCHAR, LastName,
+                SQL_C_CHAR, sizeof(LastName), true, true);
 
-// Open the table for access
-table->Open();
 
+// -----------------------------------------------------------------------
+// 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 'GEORGE' in the FIRST_NAME
+// all rows that have a value of 'JULIAN' in the FIRST_NAME
 // column of the table.
-table->SetWhereClause("FIRST_NAME = 'GEORGE'");
+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());
 }
 
 
+// -----------------------------------------------------------------------
+// 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
 //
-// Select the row which has FIRST_NAME of 'GEORGE' and LAST_NAME
-// of 'TASKER', then delete the retrieved row
-//
-table->SetWhereClause("FIRST_NAME = 'GEORGE' and "LAST_NAME = 'TASKER'");
-if (table->Query())
+if (!table->DeleteWhere(wxT("FIRST_NAME = 'JULIAN' and LAST_NAME = 'SMART'")))
 {
-    table->Delete();
-	
-	// Must commit the deletion
-	table->GetDb()->CommitTrans();
+    return HandleError(wxT("DELETION ERROR: "), table->GetDb());
 }
-
-
-//
-// Insert a new row into the table
-//
-wxStrcpy(FirstName, "JULIAN");
-wxStrcpy(LastName, "SMART");
-table->Insert();
-
-// Must commit the insert
+    
+// 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.
-if (table)
-{
-    wxDelete(table);
-}
+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.
@@ -1057,8 +1128,13 @@ wxDbCloseConnections();
 
 // Release the environment handle that was created
 // for use with the ODBC datasource connections
-wxDelete(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}
-- 
2.50.0

