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