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