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