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