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 #include "listdb.h"
  60
  61 // Global structure for holding ODBC connection information
  62 extern DbStuff DbConnectInf;
  63
  64 // Global database connection
  65 extern wxDB *READONLY_DB;
  66
  67
  68 // Used for passing the selected listbox selection back to the calling
  69 // routine.  This variable must be declared as 'extern' in the calling
  70 // source module
  71 char ListDB_Selection[LOOKUP_COL_LEN+1];
  72
  73 // If the listbox contains two columns of data, the second column is
  74 // returned in this variable.
  75 char ListDB_Selection2[LOOKUP_COL_LEN+1];
  76
  77 // Constants
  78 const int LISTDB_NO_SPACES_BETWEEN_COLS = 3;
  79
  80
  81 // Clookup constructor
  82 Clookup::Clookup(char *tblName, char *colName) : wxTable(READONLY_DB, tblName, 1)
  83 {
  84
  85         SetColDefs (0, colName, DB_DATA_TYPE_VARCHAR, lookupCol,        SQL_C_CHAR, LOOKUP_COL_LEN+1, FALSE, FALSE);
  86
  87 }  // Clookup()
  88
  89
  90 // Clookup2 constructor
  91 Clookup2::Clookup2(char *tblName, char *colName1, char *colName2, wxDB *pDb)
  92    : wxTable(pDb, tblName, (1 + (strlen(colName2) > 0)))
  93 {
  94         int i = 0;
  95
  96         SetColDefs (i, colName1, DB_DATA_TYPE_VARCHAR, lookupCol1,      SQL_C_CHAR, LOOKUP_COL_LEN+1, FALSE, FALSE);
  97
  98         if (strlen(colName2) > 0)
  99                 SetColDefs (++i, colName2, DB_DATA_TYPE_VARCHAR, lookupCol2,    SQL_C_CHAR, LOOKUP_COL_LEN+1, FALSE, FALSE);
 100
 101 }  // Clookup2()
 102
 103
 104 // This is a generic lookup constructor that will work with any table and any column
 105 ClookUpDlg::ClookUpDlg(wxWindow *parent, char *windowTitle, char *tableName, char *colName,
 106         char *where, char *orderBy)  : wxDialog (parent, LOOKUP_DIALOG, "Select...", wxPoint(-1, -1), wxSize(400, 290))
 107 {
 108         wxBeginBusyCursor();
 109
 110         strcpy(ListDB_Selection,"");
 111         widgetPtrsSet = FALSE;
 112         lookup  = 0;
 113         lookup2 = 0;
 114         noDisplayCols = 1;
 115         col1Len = 0;
 116
 117         pLookUpSelectList               = new wxListBox(this, LOOKUP_DIALOG_SELECT, wxPoint(5, 15), wxSize(384, 195), 0, 0, wxLB_SINGLE|wxLB_ALWAYS_SB, wxDefaultValidator, "LookUpSelectList");
 118         pLookUpOkBtn                    = new wxButton(this, LOOKUP_DIALOG_OK, "&Ok",           wxPoint(113, 222), wxSize(70, 35), 0, wxDefaultValidator, "LookUpOkBtn");
 119         pLookUpCancelBtn                = new wxButton(this, LOOKUP_DIALOG_CANCEL, "C&ancel",   wxPoint(212, 222), wxSize(70, 35), 0, wxDefaultValidator, "LookUpCancelBtn");
 120
 121         widgetPtrsSet = TRUE;
 122
 123         // Query the lookup table and display the result set
 124         if (!(lookup = new Clookup(tableName, colName)))
 125         {
 126                 wxMessageBox("Error allocating memory for 'Clookup'object.","Error...");
 127                 Close();
 128                 return;
 129         }
 130
 131         if (!lookup->Open())
 132         {
 133                 wxString tStr;
 134                 tStr.Printf("Unable to open the table '%s'.",tableName);
 135                 wxMessageBox(tStr,"ODBC Error...");
 136                 Close();
 137                 return;
 138         }
 139
 140         lookup->orderBy = orderBy;
 141         lookup->where = where;
 142         if (!lookup->Query())
 143         {
 144                 wxMessageBox("ODBC error during Query()","ODBC Error...");
 145                 Close();
 146                 return;
 147         }
 148
 149         // Fill in the list box from the query result set
 150         while (lookup->GetNext())
 151                 pLookUpSelectList->Append(lookup->lookupCol);
 152
 153         // Highlight the first list item
 154         pLookUpSelectList->SetSelection(0);
 155
 156         // Make the OK activate by pressing Enter
 157         if (pLookUpSelectList->Number())
 158                 pLookUpOkBtn->SetDefault();
 159         else
 160         {
 161                 pLookUpCancelBtn->SetDefault();
 162                 pLookUpOkBtn->Enable(FALSE);
 163         }
 164
 165         // Display the dialog window
 166         SetTitle(windowTitle);
 167         Centre(wxBOTH);
 168         wxEndBusyCursor();
 169         ShowModal();
 170
 171 }  // Generic lookup constructor
 172
 173
 174 //
 175 // This is a generic lookup constructor that will work with any table and any column.
 176 // It extends the capabilites of the lookup dialog in the following ways:
 177 //
 178 //      1) 2 columns rather than one
 179 // 2) The ability to select DISTINCT column values
 180 //
 181 // Only set distinctValues equal to true if necessary.  In many cases, the constraints
 182 // of the index(es) will enforce this uniqueness.  Selecting DISTINCT does require
 183 // overhead by the database to ensure that all values returned are distinct.  Therefore,
 184 // use this ONLY when you need it.
 185 //
 186 // For complicated queries, you can pass in the sql select statement.  This would be
 187 // necessary if joins are involved since by default both columns must come from the
 188 // same table.
 189 //
 190 // If you do query by sql statement, you must pass in the maximum lenght of column1,
 191 // since it cannot be derived when you query using your own sql statement.
 192 //
 193 // The optional database connection can be used if you'd like the lookup class
 194 // to use a database pointer other than the global READONLY_DB.  This is necessary if
 195 // records are being saved, but not committed to the db, yet should be included
 196 // in the lookup window.
 197 //
 198 ClookUpDlg::ClookUpDlg(wxWindow *parent, char *windowTitle, char *tableName,
 199         char *dispCol1, char *dispCol2, char *where, char *orderBy, bool distinctValues,
 200         char *selectStmt, int maxLenCol1, wxDB *pDb, bool allowOk)  : wxDialog (parent, LOOKUP_DIALOG, "Select...", wxPoint(-1, -1), wxSize(400, 290))
 201 {
 202         wxBeginBusyCursor();
 203
 204         strcpy(ListDB_Selection,"");
 205         strcpy(ListDB_Selection2,"");
 206         widgetPtrsSet = FALSE;
 207         lookup  = 0;
 208         lookup2 = 0;
 209         noDisplayCols = (strlen(dispCol2) ? 2 : 1);
 210         col1Len = 0;
 211
 212         wxFont fixedFont(12,wxMODERN,wxNORMAL,wxNORMAL);
 213
 214         // this is done with fixed font so that the second column (if any) will be left
 215         // justified in the second column
 216         pLookUpSelectList               = new wxListBox(this, LOOKUP_DIALOG_SELECT, wxPoint(5, 15), wxSize(384, 195), 0, 0, wxLB_SINGLE|wxLB_ALWAYS_SB, wxDefaultValidator, "LookUpSelectList");
 217
 218     pLookUpSelectList->SetFont(fixedFont);
 219
 220         pLookUpOkBtn                    = new wxButton(this, LOOKUP_DIALOG_OK, "&Ok",           wxPoint(113, 222), wxSize(70, 35), 0, wxDefaultValidator, "LookUpOkBtn");
 221         pLookUpCancelBtn                = new wxButton(this, LOOKUP_DIALOG_CANCEL, "C&ancel",   wxPoint(212, 222), wxSize(70, 35), 0, wxDefaultValidator, "LookUpCancelBtn");
 222
 223         widgetPtrsSet = TRUE;
 224
 225         // Query the lookup table and display the result set
 226         if (!(lookup2 = new Clookup2(tableName, dispCol1, dispCol2, pDb)))
 227         {
 228                 wxMessageBox("Error allocating memory for 'Clookup2'object.","Error...");
 229                 Close();
 230                 return;
 231         }
 232
 233         if (!lookup2->Open())
 234         {
 235                 wxString tStr;
 236                 tStr.Printf("Unable to open the table '%s'.",tableName);
 237                 wxMessageBox(tStr,"ODBC Error...");
 238                 Close();
 239                 return;
 240         }
 241
 242         // If displaying 2 columns, determine the maximum length of column1
 243         int maxColLen;
 244         if (maxLenCol1)
 245                 maxColLen = col1Len = maxLenCol1;  // user passed in max col length for column 1
 246         else
 247         {
 248                 maxColLen = LOOKUP_COL_LEN;
 249                 if (strlen(dispCol2))
 250                 {
 251                         wxString q = "SELECT MAX({fn LENGTH(";
 252                         q += dispCol1;
 253                         q += ")}), NULL";
 254                         q += " FROM ";
 255                         q += tableName;
 256                         if (strlen(where))
 257                         {
 258                                 q += " WHERE ";
 259                                 q += where;
 260                         }
 261                         if (!lookup2->QueryBySqlStmt((char*) (const char*) q))
 262                         {
 263                                 wxMessageBox("ODBC error during QueryBySqlStmt()","ODBC Error...");
 264                                 Close();
 265                                 return;
 266                         }
 267                         if (lookup2->GetNext())
 268                                 maxColLen = col1Len = atoi(lookup2->lookupCol1);
 269                         else
 270                                 wxMessageBox("ODBC error during GetNext()","ODBC Error...");
 271                 }
 272         }
 273
 274         // Query the actual record set
 275         if (selectStmt && strlen(selectStmt))   // Query by sql stmt passed in
 276         {
 277                 if (!lookup2->QueryBySqlStmt(selectStmt))
 278                 {
 279                         wxMessageBox("ODBC error during QueryBySqlStmt()","ODBC Error...");
 280                         Close();
 281                         return;
 282                 }
 283         }
 284         else    // Query using where and order by clauses
 285         {
 286                 lookup2->orderBy = orderBy;
 287                 lookup2->where = where;
 288                 if (!lookup2->Query(FALSE, distinctValues))
 289                 {
 290                         wxMessageBox("ODBC error during Query()","ODBC Error...");
 291                         Close();
 292                         return;
 293                 }
 294         }
 295
 296         // Fill in the list box from the query result set
 297         wxString s;
 298         while (lookup2->GetNext())
 299         {
 300                 s = lookup2->lookupCol1;
 301                 if (strlen(dispCol2))           // Append the optional column 2
 302                 {
 303                         s.Append(' ', (maxColLen + LISTDB_NO_SPACES_BETWEEN_COLS - strlen(lookup2->lookupCol1)));
 304                         s.Append(lookup2->lookupCol2);
 305                 }
 306                 pLookUpSelectList->Append(s);
 307         }
 308
 309         // Highlight the first list item
 310         pLookUpSelectList->SetSelection(0);
 311
 312         // Make the OK activate by pressing Enter
 313         if (pLookUpSelectList->Number())
 314                 pLookUpOkBtn->SetDefault();
 315         else
 316         {
 317                 pLookUpCancelBtn->SetDefault();
 318                 pLookUpOkBtn->Enable(FALSE);
 319         }
 320
 321         pLookUpOkBtn->Enable(allowOk);
 322
 323         // Display the dialog window
 324         SetTitle(windowTitle);
 325         Centre(wxBOTH);
 326         wxEndBusyCursor();
 327         ShowModal();
 328
 329 }  // Generic lookup constructor 2
 330
 331
 332 bool ClookUpDlg::OnClose(void)
 333 {
 334         widgetPtrsSet = FALSE;
 335         GetParent()->Enable(TRUE);
 336
 337         if (lookup)
 338                 delete lookup;
 339         if (lookup2)
 340                 delete lookup2;
 341
 342         wxEndBusyCursor();
 343         return TRUE;
 344
 345 }  // ClookUpDlg::OnClose
 346
 347
 348 void ClookUpDlg::OnCommand(wxWindow& win, wxCommandEvent& event)
 349 {
 350         wxString widgetName = win.GetName();
 351
 352         if (widgetPtrsSet)
 353         {
 354                 // OK Button
 355                 if (widgetName == pLookUpOkBtn->GetName())
 356                 {
 357                         if (pLookUpSelectList->GetSelection() != -1)
 358                         {
 359                                 if (noDisplayCols == 1)
 360                                         strcpy (ListDB_Selection, pLookUpSelectList->GetStringSelection());
 361                                 else  // 2 display columns
 362                                 {
 363                                         wxString s = pLookUpSelectList->GetStringSelection();
 364                                         // Column 1
 365                                         s = s.SubString(0, col1Len-1);
 366                                         s = s.Strip();
 367                                         strcpy(ListDB_Selection, s);
 368                                         // Column 2
 369                                         s = pLookUpSelectList->GetStringSelection();
 370                                         s = s.Mid(col1Len + LISTDB_NO_SPACES_BETWEEN_COLS);
 371                                         s = s.Strip();
 372                                         strcpy(ListDB_Selection2, s);
 373                                 }
 374                         }
 375                         else
 376                         {
 377                                 strcpy(ListDB_Selection,"");
 378                                 strcpy(ListDB_Selection2,"");
 379                         }
 380                         Close();
 381                 }  // OK Button
 382
 383                 // Cancel Button
 384                 if (widgetName == pLookUpCancelBtn->GetName())
 385                 {
 386                         strcpy (ListDB_Selection,"");
 387                         strcpy (ListDB_Selection2,"");
 388                         Close();
 389                 }  // Cancel Button
 390         }
 391
 392 };  // ClookUpDlg::OnCommand
 393
 394 // *********************************** listdb.cpp **********************************