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 **********************************