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 wxString GetExtendedDBErrorMsg2(wxDb *pDb, wxChar *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;
 129 }  // GetExtendedDBErrorMsg
 130
 131
 132 // Clookup constructor
 133 Clookup::Clookup(wxString tblName, wxString colName, wxDb *pDb, const wxString &defDir)
 134    : wxDbTable(pDb, tblName, 1, (const wxString &)wxEmptyString, !wxDB_QUERY_ONLY,
 135                defDir)
 136 {
 137
 138     SetColDefs (0, colName, DB_DATA_TYPE_VARCHAR, lookupCol, SQL_C_WXCHAR, LOOKUP_COL_LEN+1, false, false);
 139
 140 }  // Clookup()
 141
 142
 143 // Clookup2 constructor
 144 Clookup2::Clookup2(wxString tblName, wxString colName1, wxString colName2,
 145                    wxDb *pDb, const wxString &defDir)
 146    : wxDbTable(pDb, tblName, (UWORD)(1 + (wxStrlen(colName2) > 0)), (const wxString &)wxEmptyString,
 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 ((UWORD)i, colName1, DB_DATA_TYPE_VARCHAR, lookupCol1, SQL_C_WXCHAR, LOOKUP_COL_LEN+1, false, false);
 157
 158     if (wxStrlen(colName2) > 0)
 159         SetColDefs ((UWORD)(++i), colName2, DB_DATA_TYPE_VARCHAR, lookupCol2, SQL_C_WXCHAR, 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     EVT_LISTBOX_DCLICK(LOOKUP_DIALOG_SELECT,  ClookUpDlg::OnDClick)
 169 END_EVENT_TABLE()
 170
 171
 172 // This is a generic lookup constructor that will work with any table and any column
 173 ClookUpDlg::ClookUpDlg(wxWindow *parent, const wxString &windowTitle, const wxString &tableName,
 174                        const wxString &colName, const wxString &where, const wxString &orderBy,
 175                        wxDb *pDb, const wxString &defDir)
 176    : wxDialog (parent, LOOKUP_DIALOG, wxT("Select..."), wxDefaultPosition, wxSize(400, 290))
 177 {
 178     wxBeginBusyCursor();
 179
 180     wxStrcpy(ListDB_Selection,wxT(""));
 181     widgetPtrsSet = false;
 182     lookup  = 0;
 183     lookup2 = 0;
 184     noDisplayCols = 1;
 185     col1Len = 0;
 186
 187     pLookUpSelectList = new wxListBox(this, LOOKUP_DIALOG_SELECT,                  wxPoint(  5,  15), wxSize(384, 195), 0, 0, wxLB_SINGLE|wxLB_ALWAYS_SB, wxDefaultValidator, wxT("LookUpSelectList"));
 188     pLookUpOkBtn      = new wxButton(this,  LOOKUP_DIALOG_OK,      wxT("&Ok"),     wxPoint(113, 222), wxSize( 70,  35), 0, wxDefaultValidator, wxT("LookUpOkBtn"));
 189     pLookUpCancelBtn  = new wxButton(this,  LOOKUP_DIALOG_CANCEL,  wxT("C&ancel"), wxPoint(212, 222), wxSize( 70,  35), 0, wxDefaultValidator, wxT("LookUpCancelBtn"));
 190
 191     widgetPtrsSet = true;
 192
 193     // Query the lookup table and display the result set
 194     lookup = new Clookup(tableName, colName, pDb, defDir);
 195     if (!lookup)
 196     {
 197         wxMessageBox(wxT("Error allocating memory for 'Clookup' object."),wxT("Error..."));
 198         Close();
 199         return;
 200     }
 201
 202     if (!lookup->Open())
 203     {
 204         wxString tStr;
 205         tStr.Printf(wxT("Unable to open the table '%s'."), tableName.c_str());
 206         wxMessageBox(wxDbLogExtendedErrorMsg(tStr.c_str(),lookup->GetDb(),__TFILE__,__LINE__),
 207                     wxT("ODBC Error..."),wxOK | wxICON_EXCLAMATION);
 208
 209         Close();
 210         return;
 211     }
 212
 213     lookup->SetOrderByClause(orderBy);
 214     lookup->SetWhereClause(where);
 215     if (!lookup->Query())
 216     {
 217         wxString tStr;
 218         tStr = wxT("ODBC error during Query()\n\n");
 219         wxMessageBox(wxDbLogExtendedErrorMsg(tStr.c_str(),lookup->GetDb(),__TFILE__,__LINE__),
 220                     wxT("ODBC Error..."),wxOK | wxICON_EXCLAMATION);
 221
 222         Close();
 223         return;
 224     }
 225
 226     // Fill in the list box from the query result set
 227     while (lookup->GetNext())
 228         pLookUpSelectList->Append(lookup->lookupCol);
 229
 230     // Make the OK activate by pressing Enter
 231     if (pLookUpSelectList->GetCount())
 232     {
 233         pLookUpSelectList->SetSelection(0);
 234         pLookUpOkBtn->SetDefault();
 235     }
 236     else
 237     {
 238         pLookUpCancelBtn->SetDefault();
 239         pLookUpOkBtn->Enable(false);
 240     }
 241
 242     // Display the dialog window
 243     SetTitle(windowTitle);
 244     Centre(wxBOTH);
 245     wxEndBusyCursor();
 246     ShowModal();
 247
 248 }  // Generic lookup constructor
 249
 250
 251 //
 252 // This is a generic lookup constructor that will work with any table and any column.
 253 // It extends the capabilites of the lookup dialog in the following ways:
 254 //
 255 //    1) 2 columns rather than one
 256 // 2) The ability to select DISTINCT column values
 257 //
 258 // Only set distinctValues equal to true if necessary.  In many cases, the constraints
 259 // of the index(es) will enforce this uniqueness.  Selecting DISTINCT does require
 260 // overhead by the database to ensure that all values returned are distinct.  Therefore,
 261 // use this ONLY when you need it.
 262 //
 263 // For complicated queries, you can pass in the sql select statement.  This would be
 264 // necessary if joins are involved since by default both columns must come from the
 265 // same table.
 266 //
 267 // If you do query by sql statement, you must pass in the maximum length of column1,
 268 // since it cannot be derived when you query using your own sql statement.
 269 //
 270 // The optional database connection can be used if you'd like the lookup class
 271 // to use a database pointer other than wxGetApp().READONLY_DB.  This is necessary if
 272 // records are being saved, but not committed to the db, yet should be included
 273 // in the lookup window.
 274 //
 275 ClookUpDlg::ClookUpDlg(wxWindow *parent, const wxString &windowTitle, const wxString &tableName,
 276                        const wxString &dispCol1, const wxString &dispCol2,
 277                        const wxString &where, const wxString &orderBy,
 278                        wxDb *pDb, const wxString &defDir, bool distinctValues,
 279                        const wxString &selectStmt, int maxLenCol1, bool allowOk)
 280    : wxDialog (parent, LOOKUP_DIALOG, wxT("Select..."), wxDefaultPosition, wxSize(400, 290))
 281 {
 282     wxBeginBusyCursor();
 283
 284     wxStrcpy(ListDB_Selection,wxT(""));
 285     wxStrcpy(ListDB_Selection2,wxT(""));
 286     widgetPtrsSet = false;
 287     lookup  = 0;
 288     lookup2 = 0;
 289
 290     noDisplayCols = (dispCol2.Length() == 0 ? 1 : 2);
 291     col1Len = 0;
 292
 293     wxFont fixedFont(12, wxMODERN, wxNORMAL, wxNORMAL);
 294
 295     // this is done with fixed font so that the second column (if any) will be left
 296     // justified in the second column
 297     pLookUpSelectList        = new wxListBox(this, LOOKUP_DIALOG_SELECT, wxPoint(5, 15), wxSize(384, 195), 0, 0, wxLB_SINGLE|wxLB_ALWAYS_SB, wxDefaultValidator, wxT("LookUpSelectList"));
 298
 299     pLookUpSelectList->SetFont(fixedFont);
 300
 301     pLookUpOkBtn            = new wxButton(this, LOOKUP_DIALOG_OK,      wxT("&Ok"),        wxPoint(113, 222), wxSize(70, 35), 0, wxDefaultValidator, wxT("LookUpOkBtn"));
 302     pLookUpCancelBtn        = new wxButton(this, LOOKUP_DIALOG_CANCEL,  wxT("C&ancel"),    wxPoint(212, 222), wxSize(70, 35), 0, wxDefaultValidator, wxT("LookUpCancelBtn"));
 303
 304     widgetPtrsSet = true;
 305
 306     // Query the lookup table and display the result set
 307     lookup2 = new Clookup2(tableName, dispCol1, dispCol2, pDb, defDir);
 308     if (!lookup2)
 309     {
 310         wxMessageBox(wxT("Error allocating memory for 'Clookup2' object."),wxT("Error..."));
 311         Close();
 312         return;
 313     }
 314
 315     if (!lookup2->Open())
 316     {
 317         wxString tStr;
 318         tStr.Printf(wxT("Unable to open the table '%s'."),tableName.c_str());
 319         tStr += GetExtendedDBErrorMsg2(pDb,__TFILE__,__LINE__);
 320         wxMessageBox(tStr,wxT("ODBC Error..."));
 321         Close();
 322         return;
 323     }
 324
 325     // If displaying 2 columns, determine the maximum length of column1
 326     int maxColLen;
 327     if (maxLenCol1 > 0)
 328         maxColLen = col1Len = maxLenCol1;  // user passed in max col length for column 1
 329     else
 330     {
 331         // NOTE: Some databases (Firebird/Interbase) cannot handle the "fn" and "MAX()" functions
 332
 333         maxColLen = LOOKUP_COL_LEN;
 334         if (wxStrlen(dispCol2))
 335         {
 336             wxString q = wxT("SELECT MAX({fn LENGTH(");
 337             q += dispCol1;
 338             q += wxT(")}), NULL");
 339             q += wxT(" FROM ");
 340             q += tableName;
 341             if (wxStrlen(where))
 342             {
 343                 q += wxT(" WHERE ");
 344                 q += where;
 345             }
 346             if (!lookup2->QueryBySqlStmt(q))
 347             {
 348                 wxString tStr;
 349                 tStr = wxT("ODBC error during QueryBySqlStmt()\n\n");
 350                 wxMessageBox(wxDbLogExtendedErrorMsg(tStr.c_str(),lookup2->GetDb(),__TFILE__,__LINE__),
 351                             wxT("ODBC Error..."),wxOK | wxICON_EXCLAMATION);
 352
 353                 Close();
 354                 return;
 355             }
 356             if (lookup2->GetNext())
 357                 maxColLen = col1Len = wxAtoi(lookup2->lookupCol1);
 358             else
 359             {
 360                 wxString tStr;
 361                 tStr = wxT("ODBC error during GetNext()\n\n");
 362                 wxMessageBox(wxDbLogExtendedErrorMsg(tStr.c_str(),lookup2->GetDb(),__TFILE__,__LINE__),
 363                             wxT("ODBC Error..."),wxOK | wxICON_EXCLAMATION);
 364             }
 365         }
 366     }
 367
 368     // Query the actual record set
 369     if (selectStmt && wxStrlen(selectStmt))    // Query by sql stmt passed in
 370     {
 371         if (!lookup2->QueryBySqlStmt(selectStmt))
 372         {
 373             wxString tStr;
 374             tStr = wxT("ODBC error during QueryBySqlStmt()\n\n");
 375             wxMessageBox(wxDbLogExtendedErrorMsg(tStr.c_str(),lookup2->GetDb(),__TFILE__,__LINE__),
 376                         wxT("ODBC Error..."),wxOK | wxICON_EXCLAMATION);
 377
 378             Close();
 379             return;
 380         }
 381     }
 382     else    // Query using where and order by clauses
 383     {
 384         lookup2->SetOrderByClause(orderBy);
 385         lookup2->SetWhereClause(where);
 386         if (!lookup2->Query(false, distinctValues))
 387         {
 388             wxString tStr;
 389             tStr = wxT("ODBC error during Query()\n\n");
 390             wxMessageBox(wxDbLogExtendedErrorMsg(tStr.c_str(),lookup2->GetDb(),__TFILE__,__LINE__),
 391                         wxT("ODBC Error..."),wxOK | wxICON_EXCLAMATION);
 392
 393             Close();
 394             return;
 395         }
 396     }
 397
 398     // Fill in the list box from the query result set
 399     wxString s;
 400     while (lookup2->GetNext())
 401     {
 402         s = lookup2->lookupCol1;
 403         if (wxStrlen(dispCol2))        // Append the optional column 2
 404         {
 405             s.Append(wxT(' '), (maxColLen + LISTDB_NO_SPACES_BETWEEN_COLS - wxStrlen(lookup2->lookupCol1)));
 406             s.Append(lookup2->lookupCol2);
 407         }
 408         pLookUpSelectList->Append(s);
 409     }
 410
 411     // Make the OK activate by pressing Enter
 412     if (pLookUpSelectList->GetCount())
 413     {
 414         pLookUpSelectList->SetSelection(0);
 415         pLookUpOkBtn->SetDefault();
 416     }
 417     else
 418     {
 419         pLookUpCancelBtn->SetDefault();
 420         pLookUpOkBtn->Enable(false);
 421     }
 422
 423     pLookUpOkBtn->Enable(allowOk);
 424
 425     // Display the dialog window
 426     SetTitle(windowTitle);
 427     Centre(wxBOTH);
 428     wxEndBusyCursor();
 429     ShowModal();
 430
 431 }  // Generic lookup constructor 2
 432
 433
 434 void ClookUpDlg::OnClose(wxCloseEvent& event)
 435 {
 436     widgetPtrsSet = false;
 437     GetParent()->Enable(true);
 438
 439     if (lookup)
 440         delete lookup;
 441     if (lookup2)
 442         delete lookup2;
 443
 444      SetReturnCode(1);
 445
 446     while (wxIsBusy()) wxEndBusyCursor();
 447     event.Skip();
 448
 449 //    return true;
 450
 451 }  // ClookUpDlg::OnClose
 452
 453
 454 void ClookUpDlg::OnDClick( wxCommandEvent &event )
 455 {
 456   wxWindow *win = (wxWindow*) event.GetEventObject();
 457   OnCommand( *win, event );
 458 }
 459
 460
 461 void ClookUpDlg::OnButton( wxCommandEvent &event )
 462 {
 463   wxWindow *win = (wxWindow*) event.GetEventObject();
 464   OnCommand( *win, event );
 465 }
 466
 467
 468 void ClookUpDlg::OnCommand(wxWindow& win, wxCommandEvent& WXUNUSED(event))
 469 {
 470     wxString widgetName = win.GetName();
 471
 472     if (widgetPtrsSet)
 473     {
 474         bool doubleclick = false;
 475         // Double click
 476         if (widgetName == pLookUpSelectList->GetName())
 477         {
 478             doubleclick = true;
 479         }  // Double click
 480
 481         // OK Button
 482         if (widgetName == pLookUpOkBtn->GetName() || doubleclick)
 483         {
 484             if (pLookUpSelectList->GetSelection() != -1)
 485             {
 486                 if (noDisplayCols == 1)
 487                     wxStrcpy (ListDB_Selection, pLookUpSelectList->GetStringSelection());
 488                 else  // 2 display columns
 489                 {
 490                     wxString s = pLookUpSelectList->GetStringSelection();
 491                     // Column 1
 492                     s = s.SubString(0, col1Len-1);
 493                     s = s.Strip();
 494                     wxStrcpy(ListDB_Selection, s);
 495                     // Column 2
 496                     s = pLookUpSelectList->GetStringSelection();
 497                     s = s.Mid(col1Len + LISTDB_NO_SPACES_BETWEEN_COLS);
 498                     s = s.Strip();
 499                     wxStrcpy(ListDB_Selection2, s);
 500                 }
 501             }
 502             else
 503             {
 504                 wxStrcpy(ListDB_Selection,wxT(""));
 505                 wxStrcpy(ListDB_Selection2,wxT(""));
 506             }
 507             Close();
 508         }  // OK Button
 509
 510         // Cancel Button
 511         if (widgetName == pLookUpCancelBtn->GetName())
 512         {
 513             wxStrcpy (ListDB_Selection,wxT(""));
 514             wxStrcpy (ListDB_Selection2,wxT(""));
 515             Close();
 516         }  // Cancel Button
 517
 518     }
 519
 520 };  // ClookUpDlg::OnCommand
 521
 522 // *********************************** listdb.cpp **********************************