samples/db/listdb.cpp

   1 ///////////////////////////////////////////////////////////////////////////////
   2 // Name:        listdb.cpp
   3 // Purpose:     Data table lookup listbox code
   4 // Author:      George Tasker/Doug Card
   5 // Modified by:
   6 // Created:     1996
   7 // RCS-ID:      $Id$
   8 // Copyright:   (c) 1996 Remstar International, Inc.
   9 // Licence:     wxWindows licence
  10 ///////////////////////////////////////////////////////////////////////////////
  11
  12 /*
  13 // SYNOPSIS START
  14
  15     Member functions for the classes defined in LISTDB.H
  16
  17      This class is used to present a generic ListBox lookup window for
  18      use with any of the object creation/selection choice widgets.   This
  19      dialog window will present a (possibly) scrolling list of values
  20      that come from a data table source.  Based on the object type passed
  21      in the constructor, a ListBox is built to present the user with a
  22      single selection listbox.
  23
  24      The string selected from the list box is stored in the Global variable
  25      "ListDB_Seclection", and will remain set until another interation of this
  26      routine is called.
  27
  28      For each object (database) type that is to be used, an overridden
  29      constructor should be written to appropriately link to the proper
  30      data table/object for building the list.
  31
  32     The data table record access is all handled through the routines
  33     in this module, interfacing with the methods defined in wxDbTable.
  34
  35      All objects which use data table access must be initialized and
  36      have opened the table prior to passing them in the dialog
  37      constructor, and the 'where' query should already have been set
  38      and performed before creating this dialog instance.
  39
  40 // SYNOPSIS STOP
  41 */
  42
  43 #ifdef __GNUG__
  44 #pragma implementation "listdb.h"
  45 #endif
  46
  47 #include  "wx/wxprec.h"
  48
  49 #ifdef    __BORLANDC__
  50 #pragma hdrstop
  51 #endif  //__BORLANDC__
  52
  53 #ifndef WX_PRECOMP
  54 #include  <wx/wx.h>
  55 #endif //WX_PRECOMP
  56
  57 #include <wx/dbtable.h>
  58
  59 extern wxDbList WXDLLEXPORT *PtrBegDbList;    /* from db.cpp, used in getting back error results from db connections */
  60
  61 #include "listdb.h"
  62
  63 // Global structure for holding ODBC connection information
  64 extern wxDbConnectInf DbConnectInf;
  65
  66 // Global database connection
  67 extern wxDb *READONLY_DB;
  68
  69
  70 // Used for passing the selected listbox selection back to the calling
  71 // routine.  This variable must be declared as 'extern' in the calling
  72 // source module
  73 char ListDB_Selection[LOOKUP_COL_LEN+1];
  74
  75 // If the listbox contains two columns of data, the second column is
  76 // returned in this variable.
  77 char ListDB_Selection2[LOOKUP_COL_LEN+1];
  78
  79 // Constants
  80 const int LISTDB_NO_SPACES_BETWEEN_COLS = 3;
  81
  82
  83
  84 /*
  85  * This function will return the exact string(s) from the database engine
  86  * indicating all error conditions which have just occured during the
  87  * last call to the database engine.
  88  *
  89  * This demo uses the returned string by displaying it in a wxMessageBox.  The
  90  * formatting therefore is not the greatest, but this is just a demo, not a
  91  * finished product.  :-) gt
  92  *
  93  * NOTE: The value returned by this function is for temporary use only and
  94  *       should be copied for long term use
  95  */
  96 const char *GetExtendedDBErrorMsg2(char *ErrFile, int ErrLine)
  97 {
  98     static wxString msg;
  99     msg = "";
 100
 101     wxString tStr;
 102
 103     if (ErrFile || ErrLine)
 104     {
 105         msg += "File: ";
 106         msg += ErrFile;
 107         msg += "   Line: ";
 108         tStr.Printf("%d",ErrLine);
 109         msg += tStr.c_str();
 110         msg += "\n";
 111     }
 112
 113     msg.Append ("\nODBC errors:\n");
 114     msg += "\n";
 115
 116     /* Scan through each database connection displaying
 117      * any ODBC errors that have occured. */
 118     wxDbList *pDbList;
 119     for (pDbList = PtrBegDbList; pDbList; pDbList = pDbList->PtrNext)
 120     {
 121         // Skip over any free connections
 122         if (pDbList->Free)
 123             continue;
 124         // Display errors for this connection
 125         for (int i = 0; i < DB_MAX_ERROR_HISTORY; i++)
 126         {
 127             if (pDbList->PtrDb->errorList[i])
 128             {
 129                 msg.Append(pDbList->PtrDb->errorList[i]);
 130                 if (wxStrcmp(pDbList->PtrDb->errorList[i],"") != 0)
 131                     msg.Append("\n");
 132                 // Clear the errmsg buffer so the next error will not
 133                 // end up showing the previous error that have occurred
 134                 wxStrcpy(pDbList->PtrDb->errorList[i],"");
 135             }
 136         }
 137     }
 138     msg += "\n";
 139
 140     return /*(char*) (const char*) msg*/msg.c_str();
 141 }  // GetExtendedDBErrorMsg
 142
 143
 144
 145 // Clookup constructor
 146 Clookup::Clookup(char *tblName, char *colName) : wxDbTable(READONLY_DB, tblName, 1, NULL, !wxDB_QUERY_ONLY, DbConnectInf.defaultDir)
 147 {
 148
 149     SetColDefs (0, colName, DB_DATA_TYPE_VARCHAR, lookupCol, SQL_C_CHAR, LOOKUP_COL_LEN+1, FALSE, FALSE);
 150
 151 }  // Clookup()
 152
 153
 154 // Clookup2 constructor
 155 Clookup2::Clookup2(char *tblName, char *colName1, char *colName2, wxDb *pDb)
 156    : wxDbTable(pDb, tblName, (1 + (wxStrlen(colName2) > 0)), NULL, !wxDB_QUERY_ONLY, DbConnectInf.defaultDir)
 157 {
 158     int i = 0;
 159
 160     SetColDefs (i, colName1, DB_DATA_TYPE_VARCHAR, lookupCol1, SQL_C_CHAR, LOOKUP_COL_LEN+1, FALSE, FALSE);
 161
 162     if (wxStrlen(colName2) > 0)
 163         SetColDefs (++i, colName2, DB_DATA_TYPE_VARCHAR, lookupCol2, SQL_C_CHAR, LOOKUP_COL_LEN+1, FALSE, FALSE);
 164
 165 }  // Clookup2()
 166
 167
 168 BEGIN_EVENT_TABLE(ClookUpDlg, wxDialog)
 169 //     EVT_LISTBOX(LOOKUP_DIALOG_SELECT, ClookUpDlg::SelectCallback)
 170     EVT_BUTTON(LOOKUP_DIALOG_OK,  ClookUpDlg::OnButton)
 171     EVT_BUTTON(LOOKUP_DIALOG_CANCEL,  ClookUpDlg::OnButton)
 172     EVT_CLOSE(ClookUpDlg::OnClose)
 173 END_EVENT_TABLE()
 174
 175 // This is a generic lookup constructor that will work with any table and any column
 176 ClookUpDlg::ClookUpDlg(wxWindow *parent, char *windowTitle, char *tableName, char *colName,
 177     char *where, char *orderBy)  : wxDialog (parent, LOOKUP_DIALOG, "Select...", wxPoint(-1, -1), wxSize(400, 290))
 178 {
 179     wxBeginBusyCursor();
 180
 181     wxStrcpy(ListDB_Selection,"");
 182     widgetPtrsSet = FALSE;
 183     lookup  = 0;
 184     lookup2 = 0;
 185     noDisplayCols = 1;
 186     col1Len = 0;
 187
 188     pLookUpSelectList       = new wxListBox(this, LOOKUP_DIALOG_SELECT,             wxPoint(  5,  15), wxSize(384, 195), 0, 0, wxLB_SINGLE|wxLB_ALWAYS_SB, wxDefaultValidator, "LookUpSelectList");
 189     pLookUpOkBtn            = new wxButton(this, LOOKUP_DIALOG_OK,      "&Ok",      wxPoint(113, 222), wxSize( 70,  35), 0, wxDefaultValidator, "LookUpOkBtn");
 190     pLookUpCancelBtn        = new wxButton(this, LOOKUP_DIALOG_CANCEL,  "C&ancel",  wxPoint(212, 222), wxSize( 70,  35), 0, wxDefaultValidator, "LookUpCancelBtn");
 191
 192     widgetPtrsSet = TRUE;
 193
 194     // Query the lookup table and display the result set
 195     if (!(lookup = new Clookup(tableName, colName)))
 196     {
 197         wxMessageBox("Error allocating memory for 'Clookup'object.","Error...");
 198         Close();
 199         return;
 200     }
 201
 202     if (!lookup->Open())
 203     {
 204         wxString tStr;
 205         tStr.Printf("Unable to open the table '%s'.",tableName);
 206         wxMessageBox(tStr,"ODBC Error...");
 207         Close();
 208         return;
 209     }
 210
 211     lookup->SetOrderByClause(orderBy);
 212     lookup->SetWhereClause(where);
 213     if (!lookup->Query())
 214     {
 215         wxMessageBox("ODBC error during Query()","ODBC Error...");
 216         Close();
 217         return;
 218     }
 219
 220     // Fill in the list box from the query result set
 221     while (lookup->GetNext())
 222         pLookUpSelectList->Append(lookup->lookupCol);
 223
 224     // Highlight the first list item
 225     pLookUpSelectList->SetSelection(0);
 226
 227     // Make the OK activate by pressing Enter
 228     if (pLookUpSelectList->Number())
 229         pLookUpOkBtn->SetDefault();
 230     else
 231     {
 232         pLookUpCancelBtn->SetDefault();
 233         pLookUpOkBtn->Enable(FALSE);
 234     }
 235
 236     // Display the dialog window
 237     SetTitle(windowTitle);
 238     Centre(wxBOTH);
 239     wxEndBusyCursor();
 240     ShowModal();
 241
 242 }  // Generic lookup constructor
 243
 244
 245 //
 246 // This is a generic lookup constructor that will work with any table and any column.
 247 // It extends the capabilites of the lookup dialog in the following ways:
 248 //
 249 //    1) 2 columns rather than one
 250 // 2) The ability to select DISTINCT column values
 251 //
 252 // Only set distinctValues equal to true if necessary.  In many cases, the constraints
 253 // of the index(es) will enforce this uniqueness.  Selecting DISTINCT does require
 254 // overhead by the database to ensure that all values returned are distinct.  Therefore,
 255 // use this ONLY when you need it.
 256 //
 257 // For complicated queries, you can pass in the sql select statement.  This would be
 258 // necessary if joins are involved since by default both columns must come from the
 259 // same table.
 260 //
 261 // If you do query by sql statement, you must pass in the maximum lenght of column1,
 262 // since it cannot be derived when you query using your own sql statement.
 263 //
 264 // The optional database connection can be used if you'd like the lookup class
 265 // to use a database pointer other than the global READONLY_DB.  This is necessary if
 266 // records are being saved, but not committed to the db, yet should be included
 267 // in the lookup window.
 268 //
 269 ClookUpDlg::ClookUpDlg(wxWindow *parent, char *windowTitle, char *tableName,
 270     char *dispCol1, char *dispCol2, char *where, char *orderBy, bool distinctValues,
 271     char *selectStmt, int maxLenCol1, wxDb *pDb, bool allowOk)  : wxDialog (parent, LOOKUP_DIALOG, "Select...", wxPoint(-1, -1), wxSize(400, 290))
 272 {
 273     wxBeginBusyCursor();
 274
 275     wxStrcpy(ListDB_Selection,"");
 276     wxStrcpy(ListDB_Selection2,"");
 277     widgetPtrsSet = FALSE;
 278     lookup  = 0;
 279     lookup2 = 0;
 280     noDisplayCols = (wxStrlen(dispCol2) ? 2 : 1);
 281     col1Len = 0;
 282
 283     wxFont fixedFont(12,wxMODERN,wxNORMAL,wxNORMAL);
 284
 285     // this is done with fixed font so that the second column (if any) will be left
 286     // justified in the second column
 287     pLookUpSelectList        = new wxListBox(this, LOOKUP_DIALOG_SELECT, wxPoint(5, 15), wxSize(384, 195), 0, 0, wxLB_SINGLE|wxLB_ALWAYS_SB, wxDefaultValidator, "LookUpSelectList");
 288
 289     pLookUpSelectList->SetFont(fixedFont);
 290
 291     pLookUpOkBtn            = new wxButton(this, LOOKUP_DIALOG_OK,      "&Ok",        wxPoint(113, 222), wxSize(70, 35), 0, wxDefaultValidator, "LookUpOkBtn");
 292     pLookUpCancelBtn        = new wxButton(this, LOOKUP_DIALOG_CANCEL,  "C&ancel",    wxPoint(212, 222), wxSize(70, 35), 0, wxDefaultValidator, "LookUpCancelBtn");
 293
 294     widgetPtrsSet = TRUE;
 295
 296     // Query the lookup table and display the result set
 297     if (!(lookup2 = new Clookup2(tableName, dispCol1, dispCol2, pDb)))
 298     {
 299         wxMessageBox("Error allocating memory for 'Clookup2'object.","Error...");
 300         Close();
 301         return;
 302     }
 303
 304     if (!lookup2->Open())
 305     {
 306         wxString tStr;
 307         tStr.Printf("Unable to open the table '%s'.",tableName);
 308         tStr += GetExtendedDBErrorMsg2(__FILE__,__LINE__);
 309         wxMessageBox(tStr,"ODBC Error...");
 310         Close();
 311         return;
 312     }
 313
 314     // If displaying 2 columns, determine the maximum length of column1
 315     int maxColLen;
 316     if (maxLenCol1)
 317         maxColLen = col1Len = maxLenCol1;  // user passed in max col length for column 1
 318     else
 319     {
 320         maxColLen = LOOKUP_COL_LEN;
 321         if (wxStrlen(dispCol2))
 322         {
 323             wxString q = "SELECT MAX({fn LENGTH(";
 324             q += dispCol1;
 325             q += ")}), NULL";
 326             q += " FROM ";
 327             q += tableName;
 328             if (wxStrlen(where))
 329             {
 330                 q += " WHERE ";
 331                 q += where;
 332             }
 333             if (!lookup2->QueryBySqlStmt((char*) (const char*) q))
 334             {
 335                 wxMessageBox("ODBC error during QueryBySqlStmt()","ODBC Error...");
 336                 Close();
 337                 return;
 338             }
 339             if (lookup2->GetNext())
 340                 maxColLen = col1Len = atoi(lookup2->lookupCol1);
 341             else
 342                 wxMessageBox("ODBC error during GetNext()","ODBC Error...");
 343         }
 344     }
 345
 346     // Query the actual record set
 347     if (selectStmt && wxStrlen(selectStmt))    // Query by sql stmt passed in
 348     {
 349         if (!lookup2->QueryBySqlStmt(selectStmt))
 350         {
 351             wxMessageBox("ODBC error during QueryBySqlStmt()","ODBC Error...");
 352             Close();
 353             return;
 354         }
 355     }
 356     else    // Query using where and order by clauses
 357     {
 358         lookup2->SetOrderByClause(orderBy);
 359         lookup2->SetWhereClause(where);
 360         if (!lookup2->Query(FALSE, distinctValues))
 361         {
 362             wxMessageBox("ODBC error during Query()","ODBC Error...");
 363             Close();
 364             return;
 365         }
 366     }
 367
 368     // Fill in the list box from the query result set
 369     wxString s;
 370     while (lookup2->GetNext())
 371     {
 372         s = lookup2->lookupCol1;
 373         if (wxStrlen(dispCol2))        // Append the optional column 2
 374         {
 375             s.Append(' ', (maxColLen + LISTDB_NO_SPACES_BETWEEN_COLS - wxStrlen(lookup2->lookupCol1)));
 376             s.Append(lookup2->lookupCol2);
 377         }
 378         pLookUpSelectList->Append(s);
 379     }
 380
 381     // Highlight the first list item
 382     pLookUpSelectList->SetSelection(0);
 383
 384     // Make the OK activate by pressing Enter
 385     if (pLookUpSelectList->Number())
 386         pLookUpOkBtn->SetDefault();
 387     else
 388     {
 389         pLookUpCancelBtn->SetDefault();
 390         pLookUpOkBtn->Enable(FALSE);
 391     }
 392
 393     pLookUpOkBtn->Enable(allowOk);
 394
 395     // Display the dialog window
 396     SetTitle(windowTitle);
 397     Centre(wxBOTH);
 398     wxEndBusyCursor();
 399     ShowModal();
 400
 401 }  // Generic lookup constructor 2
 402
 403
 404 void ClookUpDlg::OnClose(wxCloseEvent& event)
 405 {
 406     widgetPtrsSet = FALSE;
 407     GetParent()->Enable(TRUE);
 408
 409     if (lookup)
 410         delete lookup;
 411     if (lookup2)
 412         delete lookup2;
 413
 414      SetReturnCode(1);
 415
 416     while (wxIsBusy()) wxEndBusyCursor();
 417     event.Skip();
 418
 419 //    return TRUE;
 420
 421 }  // ClookUpDlg::OnClose
 422
 423
 424 void ClookUpDlg::OnButton( wxCommandEvent &event )
 425 {
 426   wxWindow *win = (wxWindow*) event.GetEventObject();
 427   OnCommand( *win, event );
 428 }
 429
 430
 431 void ClookUpDlg::OnCommand(wxWindow& win, wxCommandEvent& event)
 432 {
 433     wxString widgetName = win.GetName();
 434
 435     if (widgetPtrsSet)
 436     {
 437         // OK Button
 438         if (widgetName == pLookUpOkBtn->GetName())
 439         {
 440             if (pLookUpSelectList->GetSelection() != -1)
 441             {
 442                 if (noDisplayCols == 1)
 443                     wxStrcpy (ListDB_Selection, pLookUpSelectList->GetStringSelection());
 444                 else  // 2 display columns
 445                 {
 446                     wxString s = pLookUpSelectList->GetStringSelection();
 447                     // Column 1
 448                     s = s.SubString(0, col1Len-1);
 449                     s = s.Strip();
 450                     wxStrcpy(ListDB_Selection, s);
 451                     // Column 2
 452                     s = pLookUpSelectList->GetStringSelection();
 453                     s = s.Mid(col1Len + LISTDB_NO_SPACES_BETWEEN_COLS);
 454                     s = s.Strip();
 455                     wxStrcpy(ListDB_Selection2, s);
 456                 }
 457             }
 458             else
 459             {
 460                 wxStrcpy(ListDB_Selection,"");
 461                 wxStrcpy(ListDB_Selection2,"");
 462             }
 463             Close();
 464         }  // OK Button
 465
 466         // Cancel Button
 467         if (widgetName == pLookUpCancelBtn->GetName())
 468         {
 469             wxStrcpy (ListDB_Selection,"");
 470             wxStrcpy (ListDB_Selection2,"");
 471             Close();
 472         }  // Cancel Button
 473     }
 474
 475 };  // ClookUpDlg::OnCommand
 476
 477 // *********************************** listdb.cpp **********************************