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