1 ///////////////////////////////////////////////////////////////////////////////
3 // Purpose: Data table lookup listbox code
4 // Author: George Tasker/Doug Card
8 // Copyright: (c) 1996 Remstar International, Inc.
9 // Licence: wxWindows licence
10 ///////////////////////////////////////////////////////////////////////////////
15 Member functions for the classes defined in LISTDB.H
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.
24 The string selected from the list box is stored in the Global variable
25 "ListDB_Selection", and will remain set until another interation of this
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.
32 The data table record access is all handled through the routines
33 in this module, interfacing with the methods defined in wxDbTable.
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.
44 #pragma implementation "listdb.h"
47 #include "wx/wxprec.h"
57 #include <wx/dbtable.h>
59 extern wxDbList WXDLLEXPORT
*PtrBegDbList
; /* from db.cpp, used in getting back error results from db connections */
64 // Used for passing the selected listbox selection back to the calling
65 // routine. This variable must be declared as 'extern' in the calling
67 wxChar ListDB_Selection
[LOOKUP_COL_LEN
+1];
69 // If the listbox contains two columns of data, the second column is
70 // returned in this variable.
71 wxChar ListDB_Selection2
[LOOKUP_COL_LEN
+1];
74 const int LISTDB_NO_SPACES_BETWEEN_COLS
= 3;
77 extern wxApp
*DatabaseDemoApp
;
81 * This function will return the exact string(s) from the database engine
82 * indicating all error conditions which have just occured during the
83 * last call to the database engine.
85 * This demo uses the returned string by displaying it in a wxMessageBox. The
86 * formatting therefore is not the greatest, but this is just a demo, not a
87 * finished product. :-) gt
89 * NOTE: The value returned by this function is for temporary use only and
90 * should be copied for long term use
92 const wxChar
*GetExtendedDBErrorMsg2(wxChar
*ErrFile
, int ErrLine
)
99 if (ErrFile
|| ErrLine
)
101 msg
+= wxT("File: ");
103 msg
+= wxT(" Line: ");
104 tStr
.Printf(wxT("%d"),ErrLine
);
109 msg
.Append (wxT("\nODBC errors:\n"));
112 /* Scan through each database connection displaying
113 * any ODBC errors that have occured. */
115 for (pDbList
= PtrBegDbList
; pDbList
; pDbList
= pDbList
->PtrNext
)
117 // Skip over any free connections
120 // Display errors for this connection
121 for (int i
= 0; i
< DB_MAX_ERROR_HISTORY
; i
++)
123 if (pDbList
->PtrDb
->errorList
[i
])
125 msg
.Append(pDbList
->PtrDb
->errorList
[i
]);
126 if (wxStrcmp(pDbList
->PtrDb
->errorList
[i
],wxT("")) != 0)
127 msg
.Append(wxT("\n"));
128 // Clear the errmsg buffer so the next error will not
129 // end up showing the previous error that have occurred
130 wxStrcpy(pDbList
->PtrDb
->errorList
[i
],wxT(""));
136 return /*(wxChar*) (const wxChar*) msg*/msg
.c_str();
137 } // GetExtendedDBErrorMsg
141 // Clookup constructor
142 Clookup::Clookup(wxChar
*tblName
, wxChar
*colName
, wxDb
*pDb
, const wxString
&defDir
)
143 : wxDbTable(pDb
, tblName
, 1, wxT(""), !wxDB_QUERY_ONLY
,
147 SetColDefs (0, colName
, DB_DATA_TYPE_VARCHAR
, lookupCol
, SQL_C_CHAR
, LOOKUP_COL_LEN
+1, FALSE
, FALSE
);
152 // Clookup2 constructor
153 Clookup2::Clookup2(wxChar
*tblName
, wxChar
*colName1
, wxChar
*colName2
,
154 wxDb
*pDb
, const wxString
&defDir
)
155 : wxDbTable(pDb
, tblName
, (1 + (wxStrlen(colName2
) > 0)), wxT(""),
156 !wxDB_QUERY_ONLY
, defDir
)
165 SetColDefs (i
, colName1
, DB_DATA_TYPE_VARCHAR
, lookupCol1
, SQL_C_CHAR
, LOOKUP_COL_LEN
+1, FALSE
, FALSE
);
167 if (wxStrlen(colName2
) > 0)
168 SetColDefs (++i
, colName2
, DB_DATA_TYPE_VARCHAR
, lookupCol2
, SQL_C_CHAR
, LOOKUP_COL_LEN
+1, FALSE
, FALSE
);
173 BEGIN_EVENT_TABLE(ClookUpDlg
, wxDialog
)
174 EVT_BUTTON(LOOKUP_DIALOG_OK
, ClookUpDlg::OnButton
)
175 EVT_BUTTON(LOOKUP_DIALOG_CANCEL
, ClookUpDlg::OnButton
)
176 EVT_CLOSE(ClookUpDlg::OnClose
)
180 // This is a generic lookup constructor that will work with any table and any column
181 ClookUpDlg::ClookUpDlg(wxWindow
*parent
, wxChar
*windowTitle
, wxChar
*tableName
,
182 wxChar
*colName
, wxChar
*where
, wxChar
*orderBy
,
183 wxDb
*pDb
, const wxString
&defDir
)
184 : wxDialog (parent
, LOOKUP_DIALOG
, wxT("Select..."), wxPoint(-1, -1), wxSize(400, 290))
188 wxStrcpy(ListDB_Selection
,wxT(""));
189 widgetPtrsSet
= FALSE
;
195 pLookUpSelectList
= new wxListBox(this, LOOKUP_DIALOG_SELECT
, wxPoint( 5, 15), wxSize(384, 195), 0, 0, wxLB_SINGLE
|wxLB_ALWAYS_SB
, wxDefaultValidator
, wxT("LookUpSelectList"));
196 pLookUpOkBtn
= new wxButton(this, LOOKUP_DIALOG_OK
, wxT("&Ok"), wxPoint(113, 222), wxSize( 70, 35), 0, wxDefaultValidator
, wxT("LookUpOkBtn"));
197 pLookUpCancelBtn
= new wxButton(this, LOOKUP_DIALOG_CANCEL
, wxT("C&ancel"), wxPoint(212, 222), wxSize( 70, 35), 0, wxDefaultValidator
, wxT("LookUpCancelBtn"));
199 widgetPtrsSet
= TRUE
;
201 // Query the lookup table and display the result set
202 if (!(lookup
= new Clookup(tableName
, colName
, pDb
, defDir
)))
204 wxMessageBox(wxT("Error allocating memory for 'Clookup'object."),wxT("Error..."));
212 tStr
.Printf(wxT("Unable to open the table '%s'."),tableName
);
213 wxMessageBox(tStr
,wxT("ODBC Error..."));
218 lookup
->SetOrderByClause(orderBy
);
219 lookup
->SetWhereClause(where
);
220 if (!lookup
->Query())
222 wxMessageBox(wxT("ODBC error during Query()"),wxT("ODBC Error..."));
227 // Fill in the list box from the query result set
228 while (lookup
->GetNext())
229 pLookUpSelectList
->Append(lookup
->lookupCol
);
231 // Highlight the first list item
232 pLookUpSelectList
->SetSelection(0);
234 // Make the OK activate by pressing Enter
235 if (pLookUpSelectList
->Number())
236 pLookUpOkBtn
->SetDefault();
239 pLookUpCancelBtn
->SetDefault();
240 pLookUpOkBtn
->Enable(FALSE
);
243 // Display the dialog window
244 SetTitle(windowTitle
);
249 } // Generic lookup constructor
253 // This is a generic lookup constructor that will work with any table and any column.
254 // It extends the capabilites of the lookup dialog in the following ways:
256 // 1) 2 columns rather than one
257 // 2) The ability to select DISTINCT column values
259 // Only set distinctValues equal to true if necessary. In many cases, the constraints
260 // of the index(es) will enforce this uniqueness. Selecting DISTINCT does require
261 // overhead by the database to ensure that all values returned are distinct. Therefore,
262 // use this ONLY when you need it.
264 // For complicated queries, you can pass in the sql select statement. This would be
265 // necessary if joins are involved since by default both columns must come from the
268 // If you do query by sql statement, you must pass in the maximum lenght of column1,
269 // since it cannot be derived when you query using your own sql statement.
271 // The optional database connection can be used if you'd like the lookup class
272 // to use a database pointer other than wxGetApp().READONLY_DB. This is necessary if
273 // records are being saved, but not committed to the db, yet should be included
274 // in the lookup window.
276 ClookUpDlg::ClookUpDlg(wxWindow
*parent
, wxChar
*windowTitle
, wxChar
*tableName
,
277 wxChar
*dispCol1
, wxChar
*dispCol2
, wxChar
*where
, wxChar
*orderBy
,
278 wxDb
*pDb
, const wxString
&defDir
, bool distinctValues
,
279 wxChar
*selectStmt
, int maxLenCol1
, bool allowOk
)
280 : wxDialog (parent
, LOOKUP_DIALOG
, wxT("Select..."), wxPoint(-1, -1), wxSize(400, 290))
284 wxStrcpy(ListDB_Selection
,wxT(""));
285 wxStrcpy(ListDB_Selection2
,wxT(""));
286 widgetPtrsSet
= FALSE
;
289 noDisplayCols
= (wxStrlen(dispCol2
) ? 2 : 1);
292 wxFont
fixedFont(12,wxMODERN
,wxNORMAL
,wxNORMAL
);
294 // this is done with fixed font so that the second column (if any) will be left
295 // justified in the second column
296 pLookUpSelectList
= new wxListBox(this, LOOKUP_DIALOG_SELECT
, wxPoint(5, 15), wxSize(384, 195), 0, 0, wxLB_SINGLE
|wxLB_ALWAYS_SB
, wxDefaultValidator
, wxT("LookUpSelectList"));
298 pLookUpSelectList
->SetFont(fixedFont
);
300 pLookUpOkBtn
= new wxButton(this, LOOKUP_DIALOG_OK
, wxT("&Ok"), wxPoint(113, 222), wxSize(70, 35), 0, wxDefaultValidator
, wxT("LookUpOkBtn"));
301 pLookUpCancelBtn
= new wxButton(this, LOOKUP_DIALOG_CANCEL
, wxT("C&ancel"), wxPoint(212, 222), wxSize(70, 35), 0, wxDefaultValidator
, wxT("LookUpCancelBtn"));
303 widgetPtrsSet
= TRUE
;
305 // Query the lookup table and display the result set
306 if (!(lookup2
= new Clookup2(tableName
, dispCol1
, dispCol2
, pDb
, defDir
)))
308 wxMessageBox(wxT("Error allocating memory for 'Clookup2' object."),wxT("Error..."));
313 if (!lookup2
->Open())
316 tStr
.Printf(wxT("Unable to open the table '%s'."),tableName
);
317 tStr
+= GetExtendedDBErrorMsg2(__FILE__
,__LINE__
);
318 wxMessageBox(tStr
,wxT("ODBC Error..."));
323 // If displaying 2 columns, determine the maximum length of column1
326 maxColLen
= col1Len
= maxLenCol1
; // user passed in max col length for column 1
329 maxColLen
= LOOKUP_COL_LEN
;
330 if (wxStrlen(dispCol2
))
332 wxString q
= wxT("SELECT MAX({fn LENGTH(");
334 q
+= wxT(")}), NULL");
342 if (!lookup2
->QueryBySqlStmt(q
))
344 wxMessageBox(wxT("ODBC error during QueryBySqlStmt()"),wxT("ODBC Error..."));
348 if (lookup2
->GetNext())
349 maxColLen
= col1Len
= atoi(lookup2
->lookupCol1
);
351 wxMessageBox(wxT("ODBC error during GetNext()"),wxT("ODBC Error..."));
355 // Query the actual record set
356 if (selectStmt
&& wxStrlen(selectStmt
)) // Query by sql stmt passed in
358 if (!lookup2
->QueryBySqlStmt(selectStmt
))
360 wxMessageBox(wxT("ODBC error during QueryBySqlStmt()"),wxT("ODBC Error..."));
365 else // Query using where and order by clauses
367 lookup2
->SetOrderByClause(orderBy
);
368 lookup2
->SetWhereClause(where
);
369 if (!lookup2
->Query(FALSE
, distinctValues
))
371 wxMessageBox(wxT("ODBC error during Query()"),wxT("ODBC Error..."));
377 // Fill in the list box from the query result set
379 while (lookup2
->GetNext())
381 s
= lookup2
->lookupCol1
;
382 if (wxStrlen(dispCol2
)) // Append the optional column 2
384 s
.Append(wxT(' '), (maxColLen
+ LISTDB_NO_SPACES_BETWEEN_COLS
- wxStrlen(lookup2
->lookupCol1
)));
385 s
.Append(lookup2
->lookupCol2
);
387 pLookUpSelectList
->Append(s
);
390 // Highlight the first list item
391 pLookUpSelectList
->SetSelection(0);
393 // Make the OK activate by pressing Enter
394 if (pLookUpSelectList
->Number())
395 pLookUpOkBtn
->SetDefault();
398 pLookUpCancelBtn
->SetDefault();
399 pLookUpOkBtn
->Enable(FALSE
);
402 pLookUpOkBtn
->Enable(allowOk
);
404 // Display the dialog window
405 SetTitle(windowTitle
);
410 } // Generic lookup constructor 2
413 void ClookUpDlg::OnClose(wxCloseEvent
& event
)
415 widgetPtrsSet
= FALSE
;
416 GetParent()->Enable(TRUE
);
425 while (wxIsBusy()) wxEndBusyCursor();
430 } // ClookUpDlg::OnClose
433 void ClookUpDlg::OnButton( wxCommandEvent
&event
)
435 wxWindow
*win
= (wxWindow
*) event
.GetEventObject();
436 OnCommand( *win
, event
);
440 void ClookUpDlg::OnCommand(wxWindow
& win
, wxCommandEvent
& event
)
442 wxString widgetName
= win
.GetName();
447 if (widgetName
== pLookUpOkBtn
->GetName())
449 if (pLookUpSelectList
->GetSelection() != -1)
451 if (noDisplayCols
== 1)
452 wxStrcpy (ListDB_Selection
, pLookUpSelectList
->GetStringSelection());
453 else // 2 display columns
455 wxString s
= pLookUpSelectList
->GetStringSelection();
457 s
= s
.SubString(0, col1Len
-1);
459 wxStrcpy(ListDB_Selection
, s
);
461 s
= pLookUpSelectList
->GetStringSelection();
462 s
= s
.Mid(col1Len
+ LISTDB_NO_SPACES_BETWEEN_COLS
);
464 wxStrcpy(ListDB_Selection2
, s
);
469 wxStrcpy(ListDB_Selection
,wxT(""));
470 wxStrcpy(ListDB_Selection2
,wxT(""));
476 if (widgetName
== pLookUpCancelBtn
->GetName())
478 wxStrcpy (ListDB_Selection
,wxT(""));
479 wxStrcpy (ListDB_Selection2
,wxT(""));
484 }; // ClookUpDlg::OnCommand
486 // *********************************** listdb.cpp **********************************