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