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);
 206         wxMessageBox(tStr, wxT("ODBC Error..."));
 207         Close();
 208         return;
 209     }
 210
 211     lookup->SetOrderByClause(orderBy);
 212     lookup->SetWhereClause(where);
 213     if (!lookup->Query())
 214     {
 215         wxMessageBox(wxT("ODBC error during Query()"), wxT("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     // Make the OK activate by pressing Enter
 225     if (pLookUpSelectList->GetCount())
 226     {
 227         pLookUpSelectList->SetSelection(0);
 228         pLookUpOkBtn->SetDefault();
 229     }
 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 length 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 wxGetApp().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, const wxString &windowTitle, const wxString &tableName,
 270                        const wxString &dispCol1, const wxString &dispCol2,
 271                        const wxString &where, const wxString &orderBy,
 272                        wxDb *pDb, const wxString &defDir, bool distinctValues,
 273                        const wxString &selectStmt, int maxLenCol1, bool allowOk)
 274    : wxDialog (parent, LOOKUP_DIALOG, wxT("Select..."), wxDefaultPosition, wxSize(400, 290))
 275 {
 276     wxBeginBusyCursor();
 277
 278     wxStrcpy(ListDB_Selection,wxT(""));
 279     wxStrcpy(ListDB_Selection2,wxT(""));
 280     widgetPtrsSet = false;
 281     lookup  = 0;
 282     lookup2 = 0;
 283
 284     noDisplayCols = (dispCol2.Length() == 0 ? 1 : 2);
 285     col1Len = 0;
 286
 287     wxFont fixedFont(12, wxMODERN, wxNORMAL, wxNORMAL);
 288
 289     // this is done with fixed font so that the second column (if any) will be left
 290     // justified in the second column
 291     pLookUpSelectList        = new wxListBox(this, LOOKUP_DIALOG_SELECT, wxPoint(5, 15), wxSize(384, 195), 0, 0, wxLB_SINGLE|wxLB_ALWAYS_SB, wxDefaultValidator, wxT("LookUpSelectList"));
 292
 293     pLookUpSelectList->SetFont(fixedFont);
 294
 295     pLookUpOkBtn            = new wxButton(this, LOOKUP_DIALOG_OK,      wxT("&Ok"),        wxPoint(113, 222), wxSize(70, 35), 0, wxDefaultValidator, wxT("LookUpOkBtn"));
 296     pLookUpCancelBtn        = new wxButton(this, LOOKUP_DIALOG_CANCEL,  wxT("C&ancel"),    wxPoint(212, 222), wxSize(70, 35), 0, wxDefaultValidator, wxT("LookUpCancelBtn"));
 297
 298     widgetPtrsSet = true;
 299
 300     // Query the lookup table and display the result set
 301     lookup2 = new Clookup2(tableName, dispCol1, dispCol2, pDb, defDir);
 302     if (!lookup2)
 303     {
 304         wxMessageBox(wxT("Error allocating memory for 'Clookup2' object."),wxT("Error..."));
 305         Close();
 306         return;
 307     }
 308
 309     if (!lookup2->Open())
 310     {
 311         wxString tStr;
 312         tStr.Printf(wxT("Unable to open the table '%s'."),tableName);
 313         tStr += GetExtendedDBErrorMsg2(pDb,__TFILE__,__LINE__);
 314         wxMessageBox(tStr,wxT("ODBC Error..."));
 315         Close();
 316         return;
 317     }
 318
 319     // If displaying 2 columns, determine the maximum length of column1
 320     int maxColLen;
 321     if (maxLenCol1)
 322         maxColLen = col1Len = maxLenCol1;  // user passed in max col length for column 1
 323     else
 324     {
 325         maxColLen = LOOKUP_COL_LEN;
 326         if (wxStrlen(dispCol2))
 327         {
 328             wxString q = wxT("SELECT MAX({fn LENGTH(");
 329             q += dispCol1;
 330             q += wxT(")}), NULL");
 331             q += wxT(" FROM ");
 332             q += tableName;
 333             if (wxStrlen(where))
 334             {
 335                 q += wxT(" WHERE ");
 336                 q += where;
 337             }
 338             if (!lookup2->QueryBySqlStmt(q))
 339             {
 340                 wxMessageBox(wxT("ODBC error during QueryBySqlStmt()"),wxT("ODBC Error..."));
 341                 Close();
 342                 return;
 343             }
 344             if (lookup2->GetNext())
 345                 maxColLen = col1Len = wxAtoi(lookup2->lookupCol1);
 346             else
 347                 wxMessageBox(wxT("ODBC error during GetNext()"),wxT("ODBC Error..."));
 348         }
 349     }
 350
 351     // Query the actual record set
 352     if (selectStmt && wxStrlen(selectStmt))    // Query by sql stmt passed in
 353     {
 354         if (!lookup2->QueryBySqlStmt(selectStmt))
 355         {
 356             wxMessageBox(wxT("ODBC error during QueryBySqlStmt()"),wxT("ODBC Error..."));
 357             Close();
 358             return;
 359         }
 360     }
 361     else    // Query using where and order by clauses
 362     {
 363         lookup2->SetOrderByClause(orderBy);
 364         lookup2->SetWhereClause(where);
 365         if (!lookup2->Query(false, distinctValues))
 366         {
 367             wxMessageBox(wxT("ODBC error during Query()"),wxT("ODBC Error..."));
 368             Close();
 369             return;
 370         }
 371     }
 372
 373     // Fill in the list box from the query result set
 374     wxString s;
 375     while (lookup2->GetNext())
 376     {
 377         s = lookup2->lookupCol1;
 378         if (wxStrlen(dispCol2))        // Append the optional column 2
 379         {
 380             s.Append(wxT(' '), (maxColLen + LISTDB_NO_SPACES_BETWEEN_COLS - wxStrlen(lookup2->lookupCol1)));
 381             s.Append(lookup2->lookupCol2);
 382         }
 383         pLookUpSelectList->Append(s);
 384     }
 385
 386     // Make the OK activate by pressing Enter
 387     if (pLookUpSelectList->GetCount())
 388     {
 389         pLookUpSelectList->SetSelection(0);
 390         pLookUpOkBtn->SetDefault();
 391     }
 392     else
 393     {
 394         pLookUpCancelBtn->SetDefault();
 395         pLookUpOkBtn->Enable(false);
 396     }
 397
 398     pLookUpOkBtn->Enable(allowOk);
 399
 400     // Display the dialog window
 401     SetTitle(windowTitle);
 402     Centre(wxBOTH);
 403     wxEndBusyCursor();
 404     ShowModal();
 405
 406 }  // Generic lookup constructor 2
 407
 408
 409 void ClookUpDlg::OnClose(wxCloseEvent& event)
 410 {
 411     widgetPtrsSet = false;
 412     GetParent()->Enable(true);
 413
 414     if (lookup)
 415         delete lookup;
 416     if (lookup2)
 417         delete lookup2;
 418
 419      SetReturnCode(1);
 420
 421     while (wxIsBusy()) wxEndBusyCursor();
 422     event.Skip();
 423
 424 //    return true;
 425
 426 }  // ClookUpDlg::OnClose
 427
 428
 429 void ClookUpDlg::OnDClick( wxCommandEvent &event )
 430 {
 431   wxWindow *win = (wxWindow*) event.GetEventObject();
 432   OnCommand( *win, event );
 433 }
 434
 435
 436 void ClookUpDlg::OnButton( wxCommandEvent &event )
 437 {
 438   wxWindow *win = (wxWindow*) event.GetEventObject();
 439   OnCommand( *win, event );
 440 }
 441
 442
 443 void ClookUpDlg::OnCommand(wxWindow& win, wxCommandEvent& WXUNUSED(event))
 444 {
 445     wxString widgetName = win.GetName();
 446
 447     if (widgetPtrsSet)
 448     {
 449         bool doubleclick = false;
 450         // Double click
 451         if (widgetName == pLookUpSelectList->GetName())
 452         {
 453             doubleclick = true;
 454         }  // Double click
 455
 456         // OK Button
 457         if (widgetName == pLookUpOkBtn->GetName() || doubleclick)
 458         {
 459             if (pLookUpSelectList->GetSelection() != -1)
 460             {
 461                 if (noDisplayCols == 1)
 462                     wxStrcpy (ListDB_Selection, pLookUpSelectList->GetStringSelection());
 463                 else  // 2 display columns
 464                 {
 465                     wxString s = pLookUpSelectList->GetStringSelection();
 466                     // Column 1
 467                     s = s.SubString(0, col1Len-1);
 468                     s = s.Strip();
 469                     wxStrcpy(ListDB_Selection, s);
 470                     // Column 2
 471                     s = pLookUpSelectList->GetStringSelection();
 472                     s = s.Mid(col1Len + LISTDB_NO_SPACES_BETWEEN_COLS);
 473                     s = s.Strip();
 474                     wxStrcpy(ListDB_Selection2, s);
 475                 }
 476             }
 477             else
 478             {
 479                 wxStrcpy(ListDB_Selection,wxT(""));
 480                 wxStrcpy(ListDB_Selection2,wxT(""));
 481             }
 482             Close();
 483         }  // OK Button
 484
 485         // Cancel Button
 486         if (widgetName == pLookUpCancelBtn->GetName())
 487         {
 488             wxStrcpy (ListDB_Selection,wxT(""));
 489             wxStrcpy (ListDB_Selection2,wxT(""));
 490             Close();
 491         }  // Cancel Button
 492
 493     }
 494
 495 };  // ClookUpDlg::OnCommand
 496
 497 // *********************************** listdb.cpp **********************************