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