]> git.saurik.com Git - wxWidgets.git/blob - src/common/dbtable.cpp
projects / wxWidgets.git / blob
? search:
summary | shortlog | log | commit | commitdiff | tree
blame | history | raw | HEAD
ODBC database class documentation updates
[wxWidgets.git] / src / common / dbtable.cpp
1 ///////////////////////////////////////////////////////////////////////////////
2 // Name: dbtable.cpp
3 // Purpose: Implementation of the wxDbTable class.
4 // Author: Doug Card
5 // Modified by: George Tasker
6 // Bart Jourquin
7 // Mark Johnson
8 // Created: 9.96
9 // RCS-ID: $Id$
10 // Copyright: (c) 1996 Remstar International, Inc.
11 // Licence: wxWindows licence, plus:
12 // Notice: This class library and its intellectual design are free of charge for use,
13 // modification, enhancement, debugging under the following conditions:
14 // 1) These classes may only be used as part of the implementation of a
15 // wxWindows-based application
16 // 2) All enhancements and bug fixes are to be submitted back to the wxWindows
17 // user groups free of all charges for use with the wxWindows library.
18 // 3) These classes may not be distributed as part of any other class library,
19 // DLL, text (written or electronic), other than a complete distribution of
20 // the wxWindows GUI development toolkit.
21 ///////////////////////////////////////////////////////////////////////////////
22
23 /*
24 // SYNOPSIS START
25 // SYNOPSIS STOP
26 */
27
28 // Use this line for wxWindows v1.x
29 //#include "wx_ver.h"
30 // Use this line for wxWindows v2.x
31 #include "wx/wxprec.h"
32 #include "wx/version.h"
33
34 #if wxMAJOR_VERSION == 2
35 #ifdef __GNUG__
36 #pragma implementation "dbtable.h"
37 #endif
38 #endif
39
40 #ifdef DBDEBUG_CONSOLE
41 #include "wx/ioswrap.h"
42 #endif
43
44
45 #ifdef __BORLANDC__
46 #pragma hdrstop
47 #endif //__BORLANDC__
48
49 #if wxMAJOR_VERSION == 2
50 #ifndef WX_PRECOMP
51 #include "wx/string.h"
52 #include "wx/object.h"
53 #include "wx/list.h"
54 #include "wx/utils.h"
55 #include "wx/msgdlg.h"
56 #include "wx/log.h"
57 #endif
58 #include "wx/filefn.h"
59 #endif
60
61 #if wxMAJOR_VERSION == 1
62 # if defined(wx_msw) || defined(wx_x)
63 # ifdef WX_PRECOMP
64 # include "wx_prec.h"
65 # else
66 # include "wx.h"
67 # endif
68 # endif
69 # define wxUSE_ODBC 1
70 #endif
71
72
73 #if wxUSE_ODBC
74
75 #include <stdio.h>
76 #include <stdlib.h>
77 #include <string.h>
78 //#include <assert.h>
79
80 #if wxMAJOR_VERSION == 1
81 #include "table.h"
82 #elif wxMAJOR_VERSION == 2
83 #include "wx/dbtable.h"
84 #endif
85
86 #ifdef __UNIX__
87 // The HPUX preprocessor lines below were commented out on 8/20/97
88 // because macros.h currently redefines DEBUG and is unneeded.
89 // # ifdef HPUX
90 // # include <macros.h>
91 // # endif
92 # ifdef LINUX
93 # include <sys/minmax.h>
94 # endif
95 #endif
96
97 ULONG lastTableID = 0;
98
99
100 #ifdef __WXDEBUG__
101 wxList TablesInUse;
102 #endif
103
104
105 /********** wxDbTable::wxDbTable() **********/
106 wxDbTable::wxDbTable(wxDb *pwxDb, const wxString &tblName, const int nCols,
107 const wxString &qryTblName, bool qryOnly, const wxString &tblPath)
108 {
109 if (!initialize(pwxDb, tblName, nCols, qryTblName, qryOnly, tblPath))
110 cleanup();
111 } // wxDbTable::wxDbTable()
112
113
114 /***** DEPRECATED: use wxDbTable::wxDbTable() format above *****/
115 wxDbTable::wxDbTable(wxDb *pwxDb, const wxString &tblName, const int nCols,
116 const wxChar *qryTblName, bool qryOnly, const wxString &tblPath)
117 {
118 wxString tempQryTblName;
119 tempQryTblName = qryTblName;
120 if (!initialize(pwxDb, tblName, nCols, tempQryTblName, qryOnly, tblPath))
121 cleanup();
122 } // wxDbTable::wxDbTable()
123
124
125 /********** wxDbTable::~wxDbTable() **********/
126 wxDbTable::~wxDbTable()
127 {
128 this->cleanup();
129 } // wxDbTable::~wxDbTable()
130
131
132 bool wxDbTable::initialize(wxDb *pwxDb, const wxString &tblName, const int nCols,
133 const wxString &qryTblName, bool qryOnly, const wxString &tblPath)
134 {
135 // Initializing member variables
136 pDb = pwxDb; // Pointer to the wxDb object
137 henv = 0;
138 hdbc = 0;
139 hstmt = 0;
140 hstmtDefault = 0; // Initialized below
141 hstmtCount = 0; // Initialized first time it is needed
142 hstmtInsert = 0;
143 hstmtDelete = 0;
144 hstmtUpdate = 0;
145 hstmtInternal = 0;
146 colDefs = 0;
147 tableID = 0;
148 noCols = nCols; // No. of cols in the table
149 where.Empty(); // Where clause
150 orderBy.Empty(); // Order By clause
151 from.Empty(); // From clause
152 selectForUpdate = FALSE; // SELECT ... FOR UPDATE; Indicates whether to include the FOR UPDATE phrase
153 queryOnly = qryOnly;
154 insertable = TRUE;
155 tablePath.Empty();
156 tableName.Empty();
157 queryTableName.Empty();
158
159 wxASSERT(tblName.Length());
160 wxASSERT(pDb);
161
162 if (!pDb)
163 return FALSE;
164
165 tableName = tblName; // Table Name
166 if (tblPath.Length())
167 tablePath = tblPath; // Table Path - used for dBase files
168 else
169 tablePath.Empty();
170
171 if (qryTblName.Length()) // Name of the table/view to query
172 queryTableName = qryTblName;
173 else
174 queryTableName = tblName;
175
176 pDb->incrementTableCount();
177
178 wxString s;
179 tableID = ++lastTableID;
180 s.Printf(wxT("wxDbTable constructor (%-20s) tableID:[%6lu] pDb:[%p]"), tblName.c_str(), tableID, pDb);
181
182 #ifdef __WXDEBUG__
183 wxTablesInUse *tableInUse;
184 tableInUse = new wxTablesInUse();
185 tableInUse->tableName = tblName;
186 tableInUse->tableID = tableID;
187 tableInUse->pDb = pDb;
188 TablesInUse.Append(tableInUse);
189 #endif
190
191 pDb->WriteSqlLog(s);
192
193 // Grab the HENV and HDBC from the wxDb object
194 henv = pDb->GetHENV();
195 hdbc = pDb->GetHDBC();
196
197 // Allocate space for column definitions
198 if (noCols)
199 colDefs = new wxDbColDef[noCols]; // Points to the first column definition
200
201 // Allocate statement handles for the table
202 if (!queryOnly)
203 {
204 // Allocate a separate statement handle for performing inserts
205 if (SQLAllocStmt(hdbc, &hstmtInsert) != SQL_SUCCESS)
206 pDb->DispAllErrors(henv, hdbc);
207 // Allocate a separate statement handle for performing deletes
208 if (SQLAllocStmt(hdbc, &hstmtDelete) != SQL_SUCCESS)
209 pDb->DispAllErrors(henv, hdbc);
210 // Allocate a separate statement handle for performing updates
211 if (SQLAllocStmt(hdbc, &hstmtUpdate) != SQL_SUCCESS)
212 pDb->DispAllErrors(henv, hdbc);
213 }
214 // Allocate a separate statement handle for internal use
215 if (SQLAllocStmt(hdbc, &hstmtInternal) != SQL_SUCCESS)
216 pDb->DispAllErrors(henv, hdbc);
217
218 // Set the cursor type for the statement handles
219 cursorType = SQL_CURSOR_STATIC;
220
221 if (SQLSetStmtOption(hstmtInternal, SQL_CURSOR_TYPE, cursorType) != SQL_SUCCESS)
222 {
223 // Check to see if cursor type is supported
224 pDb->GetNextError(henv, hdbc, hstmtInternal);
225 if (! wxStrcmp(pDb->sqlState, wxT("01S02"))) // Option Value Changed
226 {
227 // Datasource does not support static cursors. Driver
228 // will substitute a cursor type. Call SQLGetStmtOption()
229 // to determine which cursor type was selected.
230 if (SQLGetStmtOption(hstmtInternal, SQL_CURSOR_TYPE, &cursorType) != SQL_SUCCESS)
231 pDb->DispAllErrors(henv, hdbc, hstmtInternal);
232 #ifdef DBDEBUG_CONSOLE
233 cout << wxT("Static cursor changed to: ");
234 switch(cursorType)
235 {
236 case SQL_CURSOR_FORWARD_ONLY:
237 cout << wxT("Forward Only");
238 break;
239 case SQL_CURSOR_STATIC:
240 cout << wxT("Static");
241 break;
242 case SQL_CURSOR_KEYSET_DRIVEN:
243 cout << wxT("Keyset Driven");
244 break;
245 case SQL_CURSOR_DYNAMIC:
246 cout << wxT("Dynamic");
247 break;
248 }
249 cout << endl << endl;
250 #endif
251 // BJO20000425
252 if (pDb->FwdOnlyCursors() && cursorType != SQL_CURSOR_FORWARD_ONLY)
253 {
254 // Force the use of a forward only cursor...
255 cursorType = SQL_CURSOR_FORWARD_ONLY;
256 if (SQLSetStmtOption(hstmtInternal, SQL_CURSOR_TYPE, cursorType) != SQL_SUCCESS)
257 {
258 // Should never happen
259 pDb->GetNextError(henv, hdbc, hstmtInternal);
260 return FALSE;
261 }
262 }
263 }
264 else
265 {
266 pDb->DispNextError();
267 pDb->DispAllErrors(henv, hdbc, hstmtInternal);
268 }
269 }
270 #ifdef DBDEBUG_CONSOLE
271 else
272 cout << wxT("Cursor Type set to STATIC") << endl << endl;
273 #endif
274
275 if (!queryOnly)
276 {
277 // Set the cursor type for the INSERT statement handle
278 if (SQLSetStmtOption(hstmtInsert, SQL_CURSOR_TYPE, SQL_CURSOR_FORWARD_ONLY) != SQL_SUCCESS)
279 pDb->DispAllErrors(henv, hdbc, hstmtInsert);
280 // Set the cursor type for the DELETE statement handle
281 if (SQLSetStmtOption(hstmtDelete, SQL_CURSOR_TYPE, SQL_CURSOR_FORWARD_ONLY) != SQL_SUCCESS)
282 pDb->DispAllErrors(henv, hdbc, hstmtDelete);
283 // Set the cursor type for the UPDATE statement handle
284 if (SQLSetStmtOption(hstmtUpdate, SQL_CURSOR_TYPE, SQL_CURSOR_FORWARD_ONLY) != SQL_SUCCESS)
285 pDb->DispAllErrors(henv, hdbc, hstmtUpdate);
286 }
287
288 // Make the default cursor the active cursor
289 hstmtDefault = GetNewCursor(FALSE,FALSE);
290 wxASSERT(hstmtDefault);
291 hstmt = *hstmtDefault;
292
293 return TRUE;
294
295 } // wxDbTable::initialize()
296
297
298 void wxDbTable::cleanup()
299 {
300 wxString s;
301 if (pDb)
302 {
303 s.Printf(wxT("wxDbTable destructor (%-20s) tableID:[%6lu] pDb:[%p]"), tableName.c_str(), tableID, pDb);
304 pDb->WriteSqlLog(s);
305 }
306
307 #ifdef __WXDEBUG__
308 if (tableID)
309 {
310 TablesInUse.DeleteContents(TRUE);
311 bool found = FALSE;
312
313 wxNode *pNode;
314 pNode = TablesInUse.First();
315 while (pNode && !found)
316 {
317 if (((wxTablesInUse *)pNode->Data())->tableID == tableID)
318 {
319 found = TRUE;
320 if (!TablesInUse.DeleteNode(pNode))
321 wxLogDebug (s,wxT("Unable to delete node!"));
322 }
323 else
324 pNode = pNode->Next();
325 }
326 if (!found)
327 {
328 wxString msg;
329 msg.Printf(wxT("Unable to find the tableID in the linked\nlist of tables in use.\n\n%s"),s);
330 wxLogDebug (msg,wxT("NOTICE..."));
331 }
332 }
333 #endif
334
335 // Decrement the wxDb table count
336 if (pDb)
337 pDb->decrementTableCount();
338
339 // Delete memory allocated for column definitions
340 if (colDefs)
341 delete [] colDefs;
342
343 // Free statement handles
344 if (!queryOnly)
345 {
346 if (hstmtInsert)
347 {
348 /*
349 ODBC 3.0 says to use this form
350 if (SQLFreeHandle(*hstmtDel, SQL_DROP) != SQL_SUCCESS)
351 */
352 if (SQLFreeStmt(hstmtInsert, SQL_DROP) != SQL_SUCCESS)
353 pDb->DispAllErrors(henv, hdbc);
354 }
355
356 if (hstmtDelete)
357 {
358 /*
359 ODBC 3.0 says to use this form
360 if (SQLFreeHandle(*hstmtDel, SQL_DROP) != SQL_SUCCESS)
361 */
362 if (SQLFreeStmt(hstmtDelete, SQL_DROP) != SQL_SUCCESS)
363 pDb->DispAllErrors(henv, hdbc);
364 }
365
366 if (hstmtUpdate)
367 {
368 /*
369 ODBC 3.0 says to use this form
370 if (SQLFreeHandle(*hstmtDel, SQL_DROP) != SQL_SUCCESS)
371 */
372 if (SQLFreeStmt(hstmtUpdate, SQL_DROP) != SQL_SUCCESS)
373 pDb->DispAllErrors(henv, hdbc);
374 }
375 }
376
377 if (hstmtInternal)
378 {
379 if (SQLFreeStmt(hstmtInternal, SQL_DROP) != SQL_SUCCESS)
380 pDb->DispAllErrors(henv, hdbc);
381 }
382
383 // Delete dynamically allocated cursors
384 if (hstmtDefault)
385 DeleteCursor(hstmtDefault);
386
387 if (hstmtCount)
388 DeleteCursor(hstmtCount);
389 } // wxDbTable::cleanup()
390
391
392 /***************************** PRIVATE FUNCTIONS *****************************/
393
394
395 /********** wxDbTable::bindUpdateParams() **********/
396 bool wxDbTable::bindParams(bool forUpdate)
397 {
398 wxASSERT(!queryOnly);
399 if (queryOnly)
400 return(FALSE);
401
402 SWORD fSqlType = 0;
403 UDWORD precision = 0;
404 SWORD scale = 0;
405
406 // Bind each column of the table that should be bound
407 // to a parameter marker
408 int i,colNo;
409 for (i = 0, colNo = 1; i < noCols; i++)
410 {
411 if (forUpdate)
412 {
413 if (! colDefs[i].Updateable)
414 continue;
415 }
416 else
417 {
418 if (! colDefs[i].InsertAllowed)
419 continue;
420 }
421
422 switch(colDefs[i].DbDataType)
423 {
424 case DB_DATA_TYPE_VARCHAR:
425 fSqlType = pDb->GetTypeInfVarchar().FsqlType;
426 precision = colDefs[i].SzDataObj;
427 scale = 0;
428 if (colDefs[i].Null)
429 colDefs[i].CbValue = SQL_NULL_DATA;
430 else
431 colDefs[i].CbValue = SQL_NTS;
432 break;
433 case DB_DATA_TYPE_INTEGER:
434 fSqlType = pDb->GetTypeInfInteger().FsqlType;
435 precision = pDb->GetTypeInfInteger().Precision;
436 scale = 0;
437 if (colDefs[i].Null)
438 colDefs[i].CbValue = SQL_NULL_DATA;
439 else
440 colDefs[i].CbValue = 0;
441 break;
442 case DB_DATA_TYPE_FLOAT:
443 fSqlType = pDb->GetTypeInfFloat().FsqlType;
444 precision = pDb->GetTypeInfFloat().Precision;
445 scale = pDb->GetTypeInfFloat().MaximumScale;
446 // SQL Sybase Anywhere v5.5 returned a negative number for the
447 // MaxScale. This caused ODBC to kick out an error on ibscale.
448 // I check for this here and set the scale = precision.
449 //if (scale < 0)
450 // scale = (short) precision;
451 if (colDefs[i].Null)
452 colDefs[i].CbValue = SQL_NULL_DATA;
453 else
454 colDefs[i].CbValue = 0;
455 break;
456 case DB_DATA_TYPE_DATE:
457 fSqlType = pDb->GetTypeInfDate().FsqlType;
458 precision = pDb->GetTypeInfDate().Precision;
459 scale = 0;
460 if (colDefs[i].Null)
461 colDefs[i].CbValue = SQL_NULL_DATA;
462 else
463 colDefs[i].CbValue = 0;
464 break;
465 }
466 if (forUpdate)
467 {
468 if (SQLBindParameter(hstmtUpdate, colNo++, SQL_PARAM_INPUT, colDefs[i].SqlCtype,
469 fSqlType, precision, scale, (UCHAR*) colDefs[i].PtrDataObj,
470 precision+1, &colDefs[i].CbValue) != SQL_SUCCESS)
471 {
472 return(pDb->DispAllErrors(henv, hdbc, hstmtUpdate));
473 }
474 }
475 else
476 {
477 if (SQLBindParameter(hstmtInsert, colNo++, SQL_PARAM_INPUT, colDefs[i].SqlCtype,
478 fSqlType, precision, scale, (UCHAR*) colDefs[i].PtrDataObj,
479 precision+1,&colDefs[i].CbValue) != SQL_SUCCESS)
480 {
481 return(pDb->DispAllErrors(henv, hdbc, hstmtInsert));
482 }
483 }
484 }
485
486 // Completed successfully
487 return(TRUE);
488
489 } // wxDbTable::bindParams()
490
491
492 /********** wxDbTable::bindInsertParams() **********/
493 bool wxDbTable::bindInsertParams(void)
494 {
495 return bindParams(FALSE);
496 } // wxDbTable::bindInsertParams()
497
498
499 /********** wxDbTable::bindUpdateParams() **********/
500 bool wxDbTable::bindUpdateParams(void)
501 {
502 return bindParams(TRUE);
503 } // wxDbTable::bindUpdateParams()
504
505
506 /********** wxDbTable::bindCols() **********/
507 bool wxDbTable::bindCols(HSTMT cursor)
508 {
509 //RG-NULL static SDWORD cb;
510
511 // Bind each column of the table to a memory address for fetching data
512 int i;
513 for (i = 0; i < noCols; i++)
514 {
515 if (SQLBindCol(cursor, i+1, colDefs[i].SqlCtype, (UCHAR*) colDefs[i].PtrDataObj,
516 //RG-NULL colDefs[i].SzDataObj, &cb) != SQL_SUCCESS)
517 colDefs[i].SzDataObj, &colDefs[i].CbValue ) != SQL_SUCCESS)
518 {
519 return (pDb->DispAllErrors(henv, hdbc, cursor));
520 }
521 }
522
523 // Completed successfully
524 return(TRUE);
525
526 } // wxDbTable::bindCols()
527
528
529 /********** wxDbTable::getRec() **********/
530 bool wxDbTable::getRec(UWORD fetchType)
531 {
532 RETCODE retcode;
533
534 if (!pDb->FwdOnlyCursors())
535 {
536 // Fetch the NEXT, PREV, FIRST or LAST record, depending on fetchType
537 UDWORD cRowsFetched;
538 UWORD rowStatus;
539
540 retcode = SQLExtendedFetch(hstmt, fetchType, 0, &cRowsFetched, &rowStatus);
541 if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
542 {
543 if (retcode == SQL_NO_DATA_FOUND)
544 return(FALSE);
545 else
546 return(pDb->DispAllErrors(henv, hdbc, hstmt));
547 }
548 else
549 {
550 // Set the Null member variable to indicate the Null state
551 // of each column just read in.
552 int i;
553 for (i = 0; i < noCols; i++)
554 colDefs[i].Null = (colDefs[i].CbValue == SQL_NULL_DATA);
555 }
556 }
557 else
558 {
559 // Fetch the next record from the record set
560 retcode = SQLFetch(hstmt);
561 if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
562 {
563 if (retcode == SQL_NO_DATA_FOUND)
564 return(FALSE);
565 else
566 return(pDb->DispAllErrors(henv, hdbc, hstmt));
567 }
568 else
569 {
570 // Set the Null member variable to indicate the Null state
571 // of each column just read in.
572 int i;
573 for (i = 0; i < noCols; i++)
574 colDefs[i].Null = (colDefs[i].CbValue == SQL_NULL_DATA);
575 }
576 }
577
578 // Completed successfully
579 return(TRUE);
580
581 } // wxDbTable::getRec()
582
583
584 /********** wxDbTable::execDelete() **********/
585 bool wxDbTable::execDelete(const wxString &pSqlStmt)
586 {
587 // Execute the DELETE statement
588 if (SQLExecDirect(hstmtDelete, (UCHAR FAR *) pSqlStmt.c_str(), SQL_NTS) != SQL_SUCCESS)
589 return(pDb->DispAllErrors(henv, hdbc, hstmtDelete));
590
591 // Record deleted successfully
592 return(TRUE);
593
594 } // wxDbTable::execDelete()
595
596
597 /********** wxDbTable::execUpdate() **********/
598 bool wxDbTable::execUpdate(const wxString &pSqlStmt)
599 {
600 // Execute the UPDATE statement
601 if (SQLExecDirect(hstmtUpdate, (UCHAR FAR *) pSqlStmt.c_str(), SQL_NTS) != SQL_SUCCESS)
602 return(pDb->DispAllErrors(henv, hdbc, hstmtUpdate));
603
604 // Record deleted successfully
605 return(TRUE);
606
607 } // wxDbTable::execUpdate()
608
609
610 /********** wxDbTable::query() **********/
611 bool wxDbTable::query(int queryType, bool forUpdate, bool distinct, const wxString &pSqlStmt)
612 {
613 wxString sqlStmt;
614
615 if (forUpdate)
616 // The user may wish to select for update, but the DBMS may not be capable
617 selectForUpdate = CanSelectForUpdate();
618 else
619 selectForUpdate = FALSE;
620
621 // Set the SQL SELECT string
622 if (queryType != DB_SELECT_STATEMENT) // A select statement was not passed in,
623 { // so generate a select statement.
624 BuildSelectStmt(sqlStmt, queryType, distinct);
625 pDb->WriteSqlLog(sqlStmt);
626 }
627 /*
628 This is the block of code that got added during the 2.2.1 merge with
629 the 2.2 main branch that somehow got added here when it should not have. - gt
630
631 else
632 wxStrcpy(sqlStmt, pSqlStmt);
633
634 SQLFreeStmt(hstmt, SQL_CLOSE);
635 if (SQLExecDirect(hstmt, (UCHAR FAR *) sqlStmt, SQL_NTS) == SQL_SUCCESS)
636 return(TRUE);
637 else
638 {
639 pDb->DispAllErrors(henv, hdbc, hstmt);
640 return(FALSE);
641 }
642 */
643 // Make sure the cursor is closed first
644 if (!CloseCursor(hstmt))
645 return(FALSE);
646
647 // Execute the SQL SELECT statement
648 int retcode;
649 retcode = SQLExecDirect(hstmt, (UCHAR FAR *) (queryType == DB_SELECT_STATEMENT ? pSqlStmt.c_str() : sqlStmt.c_str()), SQL_NTS);
650 if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
651 return(pDb->DispAllErrors(henv, hdbc, hstmt));
652
653 // Completed successfully
654 return(TRUE);
655
656 } // wxDbTable::query()
657
658
659 /***************************** PUBLIC FUNCTIONS *****************************/
660
661
662 /********** wxDbTable::Open() **********/
663 bool wxDbTable::Open(bool checkPrivileges)
664 {
665 if (!pDb)
666 return FALSE;
667
668 int i;
669 wxString sqlStmt;
670 wxString s;
671
672 s.Empty();
673 // Verify that the table exists in the database
674 if (!pDb->TableExists(tableName,/*pDb->GetUsername()*/NULL,tablePath))
675 {
676 s = wxT("Table/view does not exist in the database");
677 if ( *(pDb->dbInf.accessibleTables) == wxT('Y'))
678 s += wxT(", or you have no permissions.\n");
679 else
680 s += wxT(".\n");
681 }
682 else if (checkPrivileges)
683 {
684 // Verify the user has rights to access the table.
685 // Shortcut boolean evaluation to optimize out call to
686 // TablePrivileges
687 //
688 // Unfortunately this optimization doesn't seem to be
689 // reliable!
690 if (// *(pDb->dbInf.accessibleTables) == 'N' &&
691 !pDb->TablePrivileges(tableName,wxT("SELECT"),NULL,pDb->GetUsername(),tablePath))
692 s = wxT("Current logged in user does not have sufficient privileges to access this table.\n");
693 }
694
695 if (!s.IsEmpty())
696 {
697 wxString p;
698
699 if (!tablePath.IsEmpty())
700 p.Printf(wxT("Error opening '%s/%s'.\n"),tablePath.c_str(),tableName.c_str());
701 else
702 p.Printf(wxT("Error opening '%s'.\n"), tableName.c_str());
703
704 p += s;
705 pDb->LogError(p.GetData());
706
707 return(FALSE);
708 }
709
710 // Bind the member variables for field exchange between
711 // the wxDbTable object and the ODBC record.
712 if (!queryOnly)
713 {
714 if (!bindInsertParams()) // Inserts
715 return(FALSE);
716
717 if (!bindUpdateParams()) // Updates
718 return(FALSE);
719 }
720
721 if (!bindCols(*hstmtDefault)) // Selects
722 return(FALSE);
723
724 if (!bindCols(hstmtInternal)) // Internal use only
725 return(FALSE);
726
727 /*
728 * Do NOT bind the hstmtCount cursor!!!
729 */
730
731 // Build an insert statement using parameter markers
732 if (!queryOnly && noCols > 0)
733 {
734 bool needComma = FALSE;
735 sqlStmt.Printf(wxT("INSERT INTO %s ("), tableName.c_str());
736 for (i = 0; i < noCols; i++)
737 {
738 if (! colDefs[i].InsertAllowed)
739 continue;
740 if (needComma)
741 sqlStmt += wxT(",");
742 sqlStmt += colDefs[i].ColName;
743 needComma = TRUE;
744 }
745 needComma = FALSE;
746 sqlStmt += wxT(") VALUES (");
747
748 int insertableCount = 0;
749
750 for (i = 0; i < noCols; i++)
751 {
752 if (! colDefs[i].InsertAllowed)
753 continue;
754 if (needComma)
755 sqlStmt += wxT(",");
756 sqlStmt += wxT("?");
757 needComma = TRUE;
758 insertableCount++;
759 }
760 sqlStmt += wxT(")");
761
762 // Prepare the insert statement for execution
763 if (insertableCount)
764 {
765 if (SQLPrepare(hstmtInsert, (UCHAR FAR *) sqlStmt.c_str(), SQL_NTS) != SQL_SUCCESS)
766 return(pDb->DispAllErrors(henv, hdbc, hstmtInsert));
767 }
768 else
769 insertable= FALSE;
770 }
771
772 // Completed successfully
773 return(TRUE);
774
775 } // wxDbTable::Open()
776
777
778 /********** wxDbTable::Query() **********/
779 bool wxDbTable::Query(bool forUpdate, bool distinct)
780 {
781
782 return(query(DB_SELECT_WHERE, forUpdate, distinct));
783
784 } // wxDbTable::Query()
785
786
787 /********** wxDbTable::QueryBySqlStmt() **********/
788 bool wxDbTable::QueryBySqlStmt(const wxString &pSqlStmt)
789 {
790 pDb->WriteSqlLog(pSqlStmt);
791
792 return(query(DB_SELECT_STATEMENT, FALSE, FALSE, pSqlStmt));
793
794 } // wxDbTable::QueryBySqlStmt()
795
796
797 /********** wxDbTable::QueryMatching() **********/
798 bool wxDbTable::QueryMatching(bool forUpdate, bool distinct)
799 {
800
801 return(query(DB_SELECT_MATCHING, forUpdate, distinct));
802
803 } // wxDbTable::QueryMatching()
804
805
806 /********** wxDbTable::QueryOnKeyFields() **********/
807 bool wxDbTable::QueryOnKeyFields(bool forUpdate, bool distinct)
808 {
809
810 return(query(DB_SELECT_KEYFIELDS, forUpdate, distinct));
811
812 } // wxDbTable::QueryOnKeyFields()
813
814
815 /********** wxDbTable::GetPrev() **********/
816 bool wxDbTable::GetPrev(void)
817 {
818 if (pDb->FwdOnlyCursors())
819 {
820 wxFAIL_MSG(wxT("GetPrev()::Backward scrolling cursors are not enabled for this instance of wxDbTable"));
821 return FALSE;
822 }
823 else
824 return(getRec(SQL_FETCH_PRIOR));
825
826 } // wxDbTable::GetPrev()
827
828
829 /********** wxDbTable::operator-- **********/
830 bool wxDbTable::operator--(int)
831 {
832 if (pDb->FwdOnlyCursors())
833 {
834 wxFAIL_MSG(wxT("operator--:Backward scrolling cursors are not enabled for this instance of wxDbTable"));
835 return FALSE;
836 }
837 else
838 return(getRec(SQL_FETCH_PRIOR));
839
840 } // wxDbTable::operator--
841
842
843 /********** wxDbTable::GetFirst() **********/
844 bool wxDbTable::GetFirst(void)
845 {
846 if (pDb->FwdOnlyCursors())
847 {
848 wxFAIL_MSG(wxT("GetFirst():Backward scrolling cursors are not enabled for this instance of wxDbTable"));
849 return FALSE;
850 }
851 else
852 return(getRec(SQL_FETCH_FIRST));
853
854 } // wxDbTable::GetFirst()
855
856
857 /********** wxDbTable::GetLast() **********/
858 bool wxDbTable::GetLast(void)
859 {
860 if (pDb->FwdOnlyCursors())
861 {
862 wxFAIL_MSG(wxT("GetLast()::Backward scrolling cursors are not enabled for this instance of wxDbTable"));
863 return FALSE;
864 }
865 else
866 return(getRec(SQL_FETCH_LAST));
867
868 } // wxDbTable::GetLast()
869
870
871 /********** wxDbTable::BuildDeleteStmt() **********/
872 void wxDbTable::BuildDeleteStmt(wxString &pSqlStmt, int typeOfDel, const wxString &pWhereClause)
873 {
874 wxASSERT(!queryOnly);
875 if (queryOnly)
876 return;
877
878 wxString whereClause;
879
880 whereClause.Empty();
881
882 // Handle the case of DeleteWhere() and the where clause is blank. It should
883 // delete all records from the database in this case.
884 if (typeOfDel == DB_DEL_WHERE && (pWhereClause.Length() == 0))
885 {
886 pSqlStmt.Printf(wxT("DELETE FROM %s"), tableName.c_str());
887 return;
888 }
889
890 pSqlStmt.Printf(wxT("DELETE FROM %s WHERE "), tableName.c_str());
891
892 // Append the WHERE clause to the SQL DELETE statement
893 switch(typeOfDel)
894 {
895 case DB_DEL_KEYFIELDS:
896 // If the datasource supports the ROWID column, build
897 // the where on ROWID for efficiency purposes.
898 // e.g. DELETE FROM PARTS WHERE ROWID = '111.222.333'
899 if (CanUpdByROWID())
900 {
901 SDWORD cb;
902 wxChar rowid[wxDB_ROWID_LEN+1];
903
904 // Get the ROWID value. If not successful retreiving the ROWID,
905 // simply fall down through the code and build the WHERE clause
906 // based on the key fields.
907 if (SQLGetData(hstmt, noCols+1, SQL_C_CHAR, (UCHAR*) rowid, wxDB_ROWID_LEN, &cb) == SQL_SUCCESS)
908 {
909 pSqlStmt += wxT("ROWID = '");
910 pSqlStmt += rowid;
911 pSqlStmt += wxT("'");
912 break;
913 }
914 }
915 // Unable to delete by ROWID, so build a WHERE
916 // clause based on the keyfields.
917 BuildWhereClause(whereClause, DB_WHERE_KEYFIELDS);
918 pSqlStmt += whereClause;
919 break;
920 case DB_DEL_WHERE:
921 pSqlStmt += pWhereClause;
922 break;
923 case DB_DEL_MATCHING:
924 BuildWhereClause(whereClause, DB_WHERE_MATCHING);
925 pSqlStmt += whereClause;
926 break;
927 }
928
929 } // BuildDeleteStmt()
930
931
932 /***** DEPRECATED: use wxDbTable::BuildDeleteStmt(wxString &....) form *****/
933 void wxDbTable::BuildDeleteStmt(wxChar *pSqlStmt, int typeOfDel, const wxString &pWhereClause)
934 {
935 wxString tempSqlStmt;
936 BuildDeleteStmt(tempSqlStmt, typeOfDel, pWhereClause);
937 wxStrcpy(pSqlStmt, tempSqlStmt);
938 } // wxDbTable::BuildDeleteStmt()
939
940
941 /********** wxDbTable::BuildSelectStmt() **********/
942 void wxDbTable::BuildSelectStmt(wxString &pSqlStmt, int typeOfSelect, bool distinct)
943 {
944 wxString whereClause;
945 whereClause.Empty();
946
947 // Build a select statement to query the database
948 pSqlStmt = wxT("SELECT ");
949
950 // SELECT DISTINCT values only?
951 if (distinct)
952 pSqlStmt += wxT("DISTINCT ");
953
954 // Was a FROM clause specified to join tables to the base table?
955 // Available for ::Query() only!!!
956 bool appendFromClause = FALSE;
957 #if wxODBC_BACKWARD_COMPATABILITY
958 if (typeOfSelect == DB_SELECT_WHERE && from && wxStrlen(from))
959 appendFromClause = TRUE;
960 #else
961 if (typeOfSelect == DB_SELECT_WHERE && from.Length())
962 appendFromClause = TRUE;
963 #endif
964
965 // Add the column list
966 int i;
967 for (i = 0; i < noCols; i++)
968 {
969 // If joining tables, the base table column names must be qualified to avoid ambiguity
970 if (appendFromClause)
971 {
972 pSqlStmt += queryTableName;
973 pSqlStmt += wxT(".");
974 }
975 pSqlStmt += colDefs[i].ColName;
976 if (i + 1 < noCols)
977 pSqlStmt += wxT(",");
978 }
979
980 // If the datasource supports ROWID, get this column as well. Exception: Don't retrieve
981 // the ROWID if querying distinct records. The rowid will always be unique.
982 if (!distinct && CanUpdByROWID())
983 {
984 // If joining tables, the base table column names must be qualified to avoid ambiguity
985 if (appendFromClause)
986 {
987 pSqlStmt += wxT(",");
988 pSqlStmt += queryTableName;
989 pSqlStmt += wxT(".ROWID");
990 }
991 else
992 pSqlStmt += wxT(",ROWID");
993 }
994
995 // Append the FROM tablename portion
996 pSqlStmt += wxT(" FROM ");
997 pSqlStmt += queryTableName;
998
999 // Sybase uses the HOLDLOCK keyword to lock a record during query.
1000 // The HOLDLOCK keyword follows the table name in the from clause.
1001 // Each table in the from clause must specify HOLDLOCK or
1002 // NOHOLDLOCK (the default). Note: The "FOR UPDATE" clause
1003 // is parsed but ignored in SYBASE Transact-SQL.
1004 if (selectForUpdate && (pDb->Dbms() == dbmsSYBASE_ASA || pDb->Dbms() == dbmsSYBASE_ASE))
1005 pSqlStmt += wxT(" HOLDLOCK");
1006
1007 if (appendFromClause)
1008 pSqlStmt += from;
1009
1010 // Append the WHERE clause. Either append the where clause for the class
1011 // or build a where clause. The typeOfSelect determines this.
1012 switch(typeOfSelect)
1013 {
1014 case DB_SELECT_WHERE:
1015 #if wxODBC_BACKWARD_COMPATABILITY
1016 if (where && wxStrlen(where)) // May not want a where clause!!!
1017 #else
1018 if (where.Length()) // May not want a where clause!!!
1019 #endif
1020 {
1021 pSqlStmt += wxT(" WHERE ");
1022 pSqlStmt += where;
1023 }
1024 break;
1025 case DB_SELECT_KEYFIELDS:
1026 BuildWhereClause(whereClause, DB_WHERE_KEYFIELDS);
1027 if (whereClause.Length())
1028 {
1029 pSqlStmt += wxT(" WHERE ");
1030 pSqlStmt += whereClause;
1031 }
1032 break;
1033 case DB_SELECT_MATCHING:
1034 BuildWhereClause(whereClause, DB_WHERE_MATCHING);
1035 if (whereClause.Length())
1036 {
1037 pSqlStmt += wxT(" WHERE ");
1038 pSqlStmt += whereClause;
1039 }
1040 break;
1041 }
1042
1043 // Append the ORDER BY clause
1044 #if wxODBC_BACKWARD_COMPATABILITY
1045 if (orderBy && wxStrlen(orderBy))
1046 #else
1047 if (orderBy.Length())
1048 #endif
1049 {
1050 pSqlStmt += wxT(" ORDER BY ");
1051 pSqlStmt += orderBy;
1052 }
1053
1054 // SELECT FOR UPDATE if told to do so and the datasource is capable. Sybase
1055 // parses the FOR UPDATE clause but ignores it. See the comment above on the
1056 // HOLDLOCK for Sybase.
1057 if (selectForUpdate && CanSelectForUpdate())
1058 pSqlStmt += wxT(" FOR UPDATE");
1059
1060 } // wxDbTable::BuildSelectStmt()
1061
1062
1063 /***** DEPRECATED: use wxDbTable::BuildSelectStmt(wxString &....) form *****/
1064 void wxDbTable::BuildSelectStmt(wxChar *pSqlStmt, int typeOfSelect, bool distinct)
1065 {
1066 wxString tempSqlStmt;
1067 BuildSelectStmt(tempSqlStmt, typeOfSelect, distinct);
1068 wxStrcpy(pSqlStmt, tempSqlStmt);
1069 } // wxDbTable::BuildSelectStmt()
1070
1071
1072 /********** wxDbTable::BuildUpdateStmt() **********/
1073 void wxDbTable::BuildUpdateStmt(wxString &pSqlStmt, int typeOfUpd, const wxString &pWhereClause)
1074 {
1075 wxASSERT(!queryOnly);
1076 if (queryOnly)
1077 return;
1078
1079 wxString whereClause;
1080 whereClause.Empty();
1081
1082 bool firstColumn = TRUE;
1083
1084 pSqlStmt.Printf(wxT("UPDATE %s SET "), tableName.c_str());
1085
1086 // Append a list of columns to be updated
1087 int i;
1088 for (i = 0; i < noCols; i++)
1089 {
1090 // Only append Updateable columns
1091 if (colDefs[i].Updateable)
1092 {
1093 if (! firstColumn)
1094 pSqlStmt += wxT(",");
1095 else
1096 firstColumn = FALSE;
1097 pSqlStmt += colDefs[i].ColName;
1098 pSqlStmt += wxT(" = ?");
1099 }
1100 }
1101
1102 // Append the WHERE clause to the SQL UPDATE statement
1103 pSqlStmt += wxT(" WHERE ");
1104 switch(typeOfUpd)
1105 {
1106 case DB_UPD_KEYFIELDS:
1107 // If the datasource supports the ROWID column, build
1108 // the where on ROWID for efficiency purposes.
1109 // e.g. UPDATE PARTS SET Col1 = ?, Col2 = ? WHERE ROWID = '111.222.333'
1110 if (CanUpdByROWID())
1111 {
1112 SDWORD cb;
1113 wxChar rowid[wxDB_ROWID_LEN+1];
1114
1115 // Get the ROWID value. If not successful retreiving the ROWID,
1116 // simply fall down through the code and build the WHERE clause
1117 // based on the key fields.
1118 if (SQLGetData(hstmt, noCols+1, SQL_C_CHAR, (UCHAR*) rowid, wxDB_ROWID_LEN, &cb) == SQL_SUCCESS)
1119 {
1120 pSqlStmt += wxT("ROWID = '");
1121 pSqlStmt += rowid;
1122 pSqlStmt += wxT("'");
1123 break;
1124 }
1125 }
1126 // Unable to delete by ROWID, so build a WHERE
1127 // clause based on the keyfields.
1128 BuildWhereClause(whereClause, DB_WHERE_KEYFIELDS);
1129 pSqlStmt += whereClause;
1130 break;
1131 case DB_UPD_WHERE:
1132 pSqlStmt += pWhereClause;
1133 break;
1134 }
1135 } // BuildUpdateStmt()
1136
1137
1138 /***** DEPRECATED: use wxDbTable::BuildUpdateStmt(wxString &....) form *****/
1139 void wxDbTable::BuildUpdateStmt(wxChar *pSqlStmt, int typeOfUpd, const wxString &pWhereClause)
1140 {
1141 wxString tempSqlStmt;
1142 BuildUpdateStmt(tempSqlStmt, typeOfUpd, pWhereClause);
1143 wxStrcpy(pSqlStmt, tempSqlStmt);
1144 } // BuildUpdateStmt()
1145
1146
1147 /********** wxDbTable::BuildWhereClause() **********/
1148 void wxDbTable::BuildWhereClause(wxString &pWhereClause, int typeOfWhere,
1149 const wxString &qualTableName, bool useLikeComparison)
1150 /*
1151 * Note: BuildWhereClause() currently ignores timestamp columns.
1152 * They are not included as part of the where clause.
1153 */
1154 {
1155 bool moreThanOneColumn = FALSE;
1156 wxString colValue;
1157
1158 // Loop through the columns building a where clause as you go
1159 int i;
1160 for (i = 0; i < noCols; i++)
1161 {
1162 // Determine if this column should be included in the WHERE clause
1163 if ((typeOfWhere == DB_WHERE_KEYFIELDS && colDefs[i].KeyField) ||
1164 (typeOfWhere == DB_WHERE_MATCHING && (!IsColNull(i))))
1165 {
1166 // Skip over timestamp columns
1167 if (colDefs[i].SqlCtype == SQL_C_TIMESTAMP)
1168 continue;
1169 // If there is more than 1 column, join them with the keyword "AND"
1170 if (moreThanOneColumn)
1171 pWhereClause += wxT(" AND ");
1172 else
1173 moreThanOneColumn = TRUE;
1174 // Concatenate where phrase for the column
1175 if (qualTableName.Length())
1176 {
1177 pWhereClause += qualTableName;
1178 pWhereClause += wxT(".");
1179 }
1180 pWhereClause += colDefs[i].ColName;
1181 if (useLikeComparison && (colDefs[i].SqlCtype == SQL_C_CHAR))
1182 pWhereClause += wxT(" LIKE ");
1183 else
1184 pWhereClause += wxT(" = ");
1185 switch(colDefs[i].SqlCtype)
1186 {
1187 case SQL_C_CHAR:
1188 colValue.Printf(wxT("'%s'"), (UCHAR FAR *) colDefs[i].PtrDataObj);
1189 break;
1190 case SQL_C_SSHORT:
1191 colValue.Printf(wxT("%hi"), *((SWORD *) colDefs[i].PtrDataObj));
1192 break;
1193 case SQL_C_USHORT:
1194 colValue.Printf(wxT("%hu"), *((UWORD *) colDefs[i].PtrDataObj));
1195 break;
1196 case SQL_C_SLONG:
1197 colValue.Printf(wxT("%li"), *((SDWORD *) colDefs[i].PtrDataObj));
1198 break;
1199 case SQL_C_ULONG:
1200 colValue.Printf(wxT("%lu"), *((UDWORD *) colDefs[i].PtrDataObj));
1201 break;
1202 case SQL_C_FLOAT:
1203 colValue.Printf(wxT("%.6f"), *((SFLOAT *) colDefs[i].PtrDataObj));
1204 break;
1205 case SQL_C_DOUBLE:
1206 colValue.Printf(wxT("%.6f"), *((SDOUBLE *) colDefs[i].PtrDataObj));
1207 break;
1208 }
1209 pWhereClause += colValue;
1210 }
1211 }
1212 } // wxDbTable::BuildWhereClause()
1213
1214
1215 /***** DEPRECATED: use wxDbTable::BuildWhereClause(wxString &....) form *****/
1216 void wxDbTable::BuildWhereClause(wxChar *pWhereClause, int typeOfWhere,
1217 const wxString &qualTableName, bool useLikeComparison)
1218 {
1219 wxString tempSqlStmt;
1220 BuildWhereClause(tempSqlStmt, typeOfWhere, qualTableName, useLikeComparison);
1221 wxStrcpy(pWhereClause, tempSqlStmt);
1222 } // wxDbTable::BuildWhereClause()
1223
1224
1225 /********** wxDbTable::GetRowNum() **********/
1226 UWORD wxDbTable::GetRowNum(void)
1227 {
1228 UDWORD rowNum;
1229
1230 if (SQLGetStmtOption(hstmt, SQL_ROW_NUMBER, (UCHAR*) &rowNum) != SQL_SUCCESS)
1231 {
1232 pDb->DispAllErrors(henv, hdbc, hstmt);
1233 return(0);
1234 }
1235
1236 // Completed successfully
1237 return((UWORD) rowNum);
1238
1239 } // wxDbTable::GetRowNum()
1240
1241
1242 /********** wxDbTable::CloseCursor() **********/
1243 bool wxDbTable::CloseCursor(HSTMT cursor)
1244 {
1245 if (SQLFreeStmt(cursor, SQL_CLOSE) != SQL_SUCCESS)
1246 return(pDb->DispAllErrors(henv, hdbc, cursor));
1247
1248 // Completed successfully
1249 return(TRUE);
1250
1251 } // wxDbTable::CloseCursor()
1252
1253
1254 /********** wxDbTable::CreateTable() **********/
1255 bool wxDbTable::CreateTable(bool attemptDrop)
1256 {
1257 if (!pDb)
1258 return FALSE;
1259
1260 int i, j;
1261 wxString sqlStmt;
1262
1263 #ifdef DBDEBUG_CONSOLE
1264 cout << wxT("Creating Table ") << tableName << wxT("...") << endl;
1265 #endif
1266
1267 // Drop table first
1268 if (attemptDrop && !DropTable())
1269 return FALSE;
1270
1271 // Create the table
1272 #ifdef DBDEBUG_CONSOLE
1273 for (i = 0; i < noCols; i++)
1274 {
1275 // Exclude derived columns since they are NOT part of the base table
1276 if (colDefs[i].DerivedCol)
1277 continue;
1278 cout << i + 1 << wxT(": ") << colDefs[i].ColName << wxT("; ");
1279 switch(colDefs[i].DbDataType)
1280 {
1281 case DB_DATA_TYPE_VARCHAR:
1282 cout << pDb->typeInfVarchar.TypeName << wxT("(") << colDefs[i].SzDataObj << wxT(")");
1283 break;
1284 case DB_DATA_TYPE_INTEGER:
1285 cout << pDb->typeInfInteger.TypeName;
1286 break;
1287 case DB_DATA_TYPE_FLOAT:
1288 cout << pDb->typeInfFloat.TypeName;
1289 break;
1290 case DB_DATA_TYPE_DATE:
1291 cout << pDb->typeInfDate.TypeName;
1292 break;
1293 }
1294 cout << endl;
1295 }
1296 #endif
1297
1298 // Build a CREATE TABLE string from the colDefs structure.
1299 bool needComma = FALSE;
1300 sqlStmt.Printf(wxT("CREATE TABLE %s ("), tableName.c_str());
1301
1302 for (i = 0; i < noCols; i++)
1303 {
1304 // Exclude derived columns since they are NOT part of the base table
1305 if (colDefs[i].DerivedCol)
1306 continue;
1307 // Comma Delimiter
1308 if (needComma)
1309 sqlStmt += wxT(",");
1310 // Column Name
1311 sqlStmt += colDefs[i].ColName;
1312 sqlStmt += wxT(" ");
1313 // Column Type
1314 switch(colDefs[i].DbDataType)
1315 {
1316 case DB_DATA_TYPE_VARCHAR:
1317 sqlStmt += pDb->GetTypeInfVarchar().TypeName;
1318 break;
1319 case DB_DATA_TYPE_INTEGER:
1320 sqlStmt += pDb->GetTypeInfInteger().TypeName;
1321 break;
1322 case DB_DATA_TYPE_FLOAT:
1323 sqlStmt += pDb->GetTypeInfFloat().TypeName;
1324 break;
1325 case DB_DATA_TYPE_DATE:
1326 sqlStmt += pDb->GetTypeInfDate().TypeName;
1327 break;
1328 }
1329 // For varchars, append the size of the string
1330 if (colDefs[i].DbDataType == DB_DATA_TYPE_VARCHAR)
1331 {
1332 wxString s;
1333 s.Printf(wxT("(%d)"), colDefs[i].SzDataObj);
1334 sqlStmt += s;
1335 }
1336
1337 if (pDb->Dbms() == dbmsDB2 ||
1338 pDb->Dbms() == dbmsMY_SQL ||
1339 pDb->Dbms() == dbmsSYBASE_ASE ||
1340 pDb->Dbms() == dbmsMS_SQL_SERVER)
1341 {
1342 if (colDefs[i].KeyField)
1343 {
1344 sqlStmt += wxT(" NOT NULL");
1345 }
1346 }
1347
1348 needComma = TRUE;
1349 }
1350 // If there is a primary key defined, include it in the create statement
1351 for (i = j = 0; i < noCols; i++)
1352 {
1353 if (colDefs[i].KeyField)
1354 {
1355 j++;
1356 break;
1357 }
1358 }
1359 if (j && pDb->Dbms() != dbmsDBASE) // Found a keyfield
1360 {
1361 if (pDb->Dbms() != dbmsMY_SQL)
1362 {
1363 sqlStmt += wxT(",CONSTRAINT ");
1364 sqlStmt += tableName;
1365 sqlStmt += wxT("_PIDX PRIMARY KEY (");
1366 }
1367 else
1368 {
1369 /* MySQL goes out on this one. We also declare the relevant key NON NULL above */
1370 sqlStmt += wxT(", PRIMARY KEY (");
1371 }
1372
1373 // List column name(s) of column(s) comprising the primary key
1374 for (i = j = 0; i < noCols; i++)
1375 {
1376 if (colDefs[i].KeyField)
1377 {
1378 if (j++) // Multi part key, comma separate names
1379 sqlStmt += wxT(",");
1380 sqlStmt += colDefs[i].ColName;
1381 }
1382 }
1383 sqlStmt += wxT(")");
1384 }
1385 // Append the closing parentheses for the create table statement
1386 sqlStmt += wxT(")");
1387
1388 pDb->WriteSqlLog(sqlStmt);
1389
1390 #ifdef DBDEBUG_CONSOLE
1391 cout << endl << sqlStmt.c_str() << endl;
1392 #endif
1393
1394 // Execute the CREATE TABLE statement
1395 RETCODE retcode = SQLExecDirect(hstmt, (UCHAR FAR *) sqlStmt.c_str(), SQL_NTS);
1396 if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
1397 {
1398 pDb->DispAllErrors(henv, hdbc, hstmt);
1399 pDb->RollbackTrans();
1400 CloseCursor(hstmt);
1401 return(FALSE);
1402 }
1403
1404 // Commit the transaction and close the cursor
1405 if (!pDb->CommitTrans())
1406 return(FALSE);
1407 if (!CloseCursor(hstmt))
1408 return(FALSE);
1409
1410 // Database table created successfully
1411 return(TRUE);
1412
1413 } // wxDbTable::CreateTable()
1414
1415
1416 /********** wxDbTable::DropTable() **********/
1417 bool wxDbTable::DropTable()
1418 {
1419 // NOTE: This function returns TRUE if the Table does not exist, but
1420 // only for identified databases. Code will need to be added
1421 // below for any other databases when those databases are defined
1422 // to handle this situation consistently
1423
1424 wxString sqlStmt;
1425
1426 sqlStmt.Printf(wxT("DROP TABLE %s"), tableName.c_str());
1427
1428 pDb->WriteSqlLog(sqlStmt);
1429
1430 #ifdef DBDEBUG_CONSOLE
1431 cout << endl << sqlStmt.c_str() << endl;
1432 #endif
1433
1434 if (SQLExecDirect(hstmt, (UCHAR FAR *) sqlStmt.c_str(), SQL_NTS) != SQL_SUCCESS)
1435 {
1436 // Check for "Base table not found" error and ignore
1437 pDb->GetNextError(henv, hdbc, hstmt);
1438 if (wxStrcmp(pDb->sqlState, wxT("S0002")) &&
1439 wxStrcmp(pDb->sqlState, wxT("S1000"))) // "Base table not found"
1440 {
1441 // Check for product specific error codes
1442 if (!((pDb->Dbms() == dbmsSYBASE_ASA && !wxStrcmp(pDb->sqlState,wxT("42000"))) || // 5.x (and lower?)
1443 (pDb->Dbms() == dbmsSYBASE_ASE && !wxStrcmp(pDb->sqlState,wxT("37000"))) ||
1444 (pDb->Dbms() == dbmsPOSTGRES && !wxStrcmp(pDb->sqlState,wxT("08S01")))))
1445 {
1446 pDb->DispNextError();
1447 pDb->DispAllErrors(henv, hdbc, hstmt);
1448 pDb->RollbackTrans();
1449 CloseCursor(hstmt);
1450 return(FALSE);
1451 }
1452 }
1453 }
1454
1455 // Commit the transaction and close the cursor
1456 if (! pDb->CommitTrans())
1457 return(FALSE);
1458 if (! CloseCursor(hstmt))
1459 return(FALSE);
1460
1461 return(TRUE);
1462 } // wxDbTable::DropTable()
1463
1464
1465 /********** wxDbTable::CreateIndex() **********/
1466 bool wxDbTable::CreateIndex(const wxString &idxName, bool unique, int noIdxCols, wxDbIdxDef *pIdxDefs, bool attemptDrop)
1467 {
1468 wxString sqlStmt;
1469
1470 // Drop the index first
1471 if (attemptDrop && !DropIndex(idxName))
1472 return (FALSE);
1473
1474 // MySQL (and possibly Sybase ASE?? - gt) require that any columns which are used as portions
1475 // of an index have the columns defined as "NOT NULL". During initial table creation though,
1476 // it may not be known which columns are necessarily going to be part of an index (e.g. the
1477 // table was created, then months later you determine that an additional index while
1478 // give better performance, so you want to add an index).
1479 //
1480 // The following block of code will modify the column definition to make the column be
1481 // defined with the "NOT NULL" qualifier.
1482 if (pDb->Dbms() == dbmsMY_SQL)
1483 {
1484 wxString sqlStmt;
1485 int i;
1486 bool ok = TRUE;
1487 for (i = 0; i < noIdxCols && ok; i++)
1488 {
1489 int j = 0;
1490 bool found = FALSE;
1491 // Find the column definition that has the ColName that matches the
1492 // index column name. We need to do this to get the DB_DATA_TYPE of
1493 // the index column, as MySQL's syntax for the ALTER column requires
1494 // this information
1495 while (!found && (j < this->noCols))
1496 {
1497 if (wxStrcmp(colDefs[j].ColName,pIdxDefs[i].ColName) == 0)
1498 found = TRUE;
1499 if (!found)
1500 j++;
1501 }
1502
1503 if (found)
1504 {
1505 ok = pDb->ModifyColumn(tableName, pIdxDefs[i].ColName,
1506 colDefs[j].DbDataType, colDefs[j].SzDataObj,
1507 wxT("NOT NULL"));
1508
1509 if (!ok)
1510 {
1511 wxODBC_ERRORS retcode;
1512 // Oracle returns a DB_ERR_GENERAL_ERROR if the column is already
1513 // defined to be NOT NULL, but reportedly MySQL doesn't mind.
1514 // This line is just here for debug checking of the value
1515 retcode = (wxODBC_ERRORS)pDb->DB_STATUS;
1516 }
1517 }
1518 else
1519 ok = FALSE;
1520 }
1521 if (ok)
1522 pDb->CommitTrans();
1523 else
1524 {
1525 pDb->RollbackTrans();
1526 return(FALSE);
1527 }
1528 }
1529
1530 // Build a CREATE INDEX statement
1531 sqlStmt = wxT("CREATE ");
1532 if (unique)
1533 sqlStmt += wxT("UNIQUE ");
1534
1535 sqlStmt += wxT("INDEX ");
1536 sqlStmt += idxName;
1537 sqlStmt += wxT(" ON ");
1538 sqlStmt += tableName;
1539 sqlStmt += wxT(" (");
1540
1541 // Append list of columns making up index
1542 int i;
1543 for (i = 0; i < noIdxCols; i++)
1544 {
1545 sqlStmt += pIdxDefs[i].ColName;
1546 /* Postgres doesn't cope with ASC */
1547 if (pDb->Dbms() != dbmsPOSTGRES)
1548 {
1549 if (pIdxDefs[i].Ascending)
1550 sqlStmt += wxT(" ASC");
1551 else
1552 sqlStmt += wxT(" DESC");
1553 }
1554
1555 if ((i + 1) < noIdxCols)
1556 sqlStmt += wxT(",");
1557 }
1558
1559 // Append closing parentheses
1560 sqlStmt += wxT(")");
1561
1562 pDb->WriteSqlLog(sqlStmt);
1563
1564 #ifdef DBDEBUG_CONSOLE
1565 cout << endl << sqlStmt.c_str() << endl << endl;
1566 #endif
1567
1568 // Execute the CREATE INDEX statement
1569 if (SQLExecDirect(hstmt, (UCHAR FAR *) sqlStmt.c_str(), SQL_NTS) != SQL_SUCCESS)
1570 {
1571 pDb->DispAllErrors(henv, hdbc, hstmt);
1572 pDb->RollbackTrans();
1573 CloseCursor(hstmt);
1574 return(FALSE);
1575 }
1576
1577 // Commit the transaction and close the cursor
1578 if (! pDb->CommitTrans())
1579 return(FALSE);
1580 if (! CloseCursor(hstmt))
1581 return(FALSE);
1582
1583 // Index Created Successfully
1584 return(TRUE);
1585
1586 } // wxDbTable::CreateIndex()
1587
1588
1589 /********** wxDbTable::DropIndex() **********/
1590 bool wxDbTable::DropIndex(const wxString &idxName)
1591 {
1592 // NOTE: This function returns TRUE if the Index does not exist, but
1593 // only for identified databases. Code will need to be added
1594 // below for any other databases when those databases are defined
1595 // to handle this situation consistently
1596
1597 wxString sqlStmt;
1598
1599 if (pDb->Dbms() == dbmsACCESS || pDb->Dbms() == dbmsMY_SQL)
1600 sqlStmt.Printf(wxT("DROP INDEX %s ON %s"),idxName.c_str(), tableName.c_str());
1601 else if ((pDb->Dbms() == dbmsMS_SQL_SERVER) ||
1602 (pDb->Dbms() == dbmsSYBASE_ASE))
1603 sqlStmt.Printf(wxT("DROP INDEX %s.%s"),tableName.c_str(), idxName.c_str());
1604 else
1605 sqlStmt.Printf(wxT("DROP INDEX %s"),idxName.c_str());
1606
1607 pDb->WriteSqlLog(sqlStmt);
1608
1609 #ifdef DBDEBUG_CONSOLE
1610 cout << endl << sqlStmt.c_str() << endl;
1611 #endif
1612
1613 if (SQLExecDirect(hstmt, (UCHAR FAR *) sqlStmt.c_str(), SQL_NTS) != SQL_SUCCESS)
1614 {
1615 // Check for "Index not found" error and ignore
1616 pDb->GetNextError(henv, hdbc, hstmt);
1617 if (wxStrcmp(pDb->sqlState,wxT("S0012"))) // "Index not found"
1618 {
1619 // Check for product specific error codes
1620 if (!((pDb->Dbms() == dbmsSYBASE_ASA && !wxStrcmp(pDb->sqlState,wxT("42000"))) || // v5.x (and lower?)
1621 (pDb->Dbms() == dbmsSYBASE_ASE && !wxStrcmp(pDb->sqlState,wxT("37000"))) ||
1622 (pDb->Dbms() == dbmsMS_SQL_SERVER && !wxStrcmp(pDb->sqlState,wxT("S1000"))) ||
1623 (pDb->Dbms() == dbmsSYBASE_ASE && !wxStrcmp(pDb->sqlState,wxT("S0002"))) || // Base table not found
1624 (pDb->Dbms() == dbmsMY_SQL && !wxStrcmp(pDb->sqlState,wxT("42S12"))) || // tested by Christopher Ludwik Marino-Cebulski using v3.23.21beta
1625 (pDb->Dbms() == dbmsPOSTGRES && !wxStrcmp(pDb->sqlState,wxT("08S01")))
1626 ))
1627 {
1628 pDb->DispNextError();
1629 pDb->DispAllErrors(henv, hdbc, hstmt);
1630 pDb->RollbackTrans();
1631 CloseCursor(hstmt);
1632 return(FALSE);
1633 }
1634 }
1635 }
1636
1637 // Commit the transaction and close the cursor
1638 if (! pDb->CommitTrans())
1639 return(FALSE);
1640 if (! CloseCursor(hstmt))
1641 return(FALSE);
1642
1643 return(TRUE);
1644 } // wxDbTable::DropIndex()
1645
1646
1647 /********** wxDbTable::SetOrderByColNums() **********/
1648 bool wxDbTable::SetOrderByColNums(int first, ... )
1649 {
1650 int colNo = first;
1651 va_list argptr;
1652
1653 bool abort = FALSE;
1654 wxString tempStr;
1655
1656 va_start(argptr, first); /* Initialize variable arguments. */
1657 while (!abort && (colNo != wxDB_NO_MORE_COLUMN_NUMBERS))
1658 {
1659 // Make sure the passed in column number
1660 // is within the valid range of columns
1661 //
1662 // Valid columns are 0 thru noCols-1
1663 if (colNo >= noCols || colNo < 0)
1664 {
1665 abort = TRUE;
1666 continue;
1667 }
1668
1669 if (colNo != first)
1670 tempStr += wxT(",");
1671
1672 tempStr += colDefs[colNo].ColName;
1673 colNo = va_arg (argptr, int);
1674 }
1675 va_end (argptr); /* Reset variable arguments. */
1676
1677 SetOrderByClause(tempStr);
1678
1679 return (!abort);
1680 } // wxDbTable::SetOrderByColNums()
1681
1682
1683 /********** wxDbTable::Insert() **********/
1684 int wxDbTable::Insert(void)
1685 {
1686 wxASSERT(!queryOnly);
1687 if (queryOnly || !insertable)
1688 return(DB_FAILURE);
1689
1690 bindInsertParams();
1691
1692 // Insert the record by executing the already prepared insert statement
1693 RETCODE retcode;
1694 retcode=SQLExecute(hstmtInsert);
1695 if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
1696 {
1697 // Check to see if integrity constraint was violated
1698 pDb->GetNextError(henv, hdbc, hstmtInsert);
1699 if (! wxStrcmp(pDb->sqlState, wxT("23000"))) // Integrity constraint violated
1700 return(DB_ERR_INTEGRITY_CONSTRAINT_VIOL);
1701 else
1702 {
1703 pDb->DispNextError();
1704 pDb->DispAllErrors(henv, hdbc, hstmtInsert);
1705 return(DB_FAILURE);
1706 }
1707 }
1708
1709 // Record inserted into the datasource successfully
1710 return(DB_SUCCESS);
1711
1712 } // wxDbTable::Insert()
1713
1714
1715 /********** wxDbTable::Update() **********/
1716 bool wxDbTable::Update(void)
1717 {
1718 wxASSERT(!queryOnly);
1719 if (queryOnly)
1720 return(FALSE);
1721
1722 wxString sqlStmt;
1723
1724 // Build the SQL UPDATE statement
1725 BuildUpdateStmt(sqlStmt, DB_UPD_KEYFIELDS);
1726
1727 pDb->WriteSqlLog(sqlStmt);
1728
1729 #ifdef DBDEBUG_CONSOLE
1730 cout << endl << sqlStmt.c_str() << endl << endl;
1731 #endif
1732
1733 // Execute the SQL UPDATE statement
1734 return(execUpdate(sqlStmt));
1735
1736 } // wxDbTable::Update()
1737
1738
1739 /********** wxDbTable::Update(pSqlStmt) **********/
1740 bool wxDbTable::Update(const wxString &pSqlStmt)
1741 {
1742 wxASSERT(!queryOnly);
1743 if (queryOnly)
1744 return(FALSE);
1745
1746 pDb->WriteSqlLog(pSqlStmt);
1747
1748 return(execUpdate(pSqlStmt));
1749
1750 } // wxDbTable::Update(pSqlStmt)
1751
1752
1753 /********** wxDbTable::UpdateWhere() **********/
1754 bool wxDbTable::UpdateWhere(const wxString &pWhereClause)
1755 {
1756 wxASSERT(!queryOnly);
1757 if (queryOnly)
1758 return(FALSE);
1759
1760 wxString sqlStmt;
1761
1762 // Build the SQL UPDATE statement
1763 BuildUpdateStmt(sqlStmt, DB_UPD_WHERE, pWhereClause);
1764
1765 pDb->WriteSqlLog(sqlStmt);
1766
1767 #ifdef DBDEBUG_CONSOLE
1768 cout << endl << sqlStmt.c_str() << endl << endl;
1769 #endif
1770
1771 // Execute the SQL UPDATE statement
1772 return(execUpdate(sqlStmt));
1773
1774 } // wxDbTable::UpdateWhere()
1775
1776
1777 /********** wxDbTable::Delete() **********/
1778 bool wxDbTable::Delete(void)
1779 {
1780 wxASSERT(!queryOnly);
1781 if (queryOnly)
1782 return(FALSE);
1783
1784 wxString sqlStmt;
1785 sqlStmt.Empty();
1786
1787 // Build the SQL DELETE statement
1788 BuildDeleteStmt(sqlStmt, DB_DEL_KEYFIELDS);
1789
1790 pDb->WriteSqlLog(sqlStmt);
1791
1792 // Execute the SQL DELETE statement
1793 return(execDelete(sqlStmt));
1794
1795 } // wxDbTable::Delete()
1796
1797
1798 /********** wxDbTable::DeleteWhere() **********/
1799 bool wxDbTable::DeleteWhere(const wxString &pWhereClause)
1800 {
1801 wxASSERT(!queryOnly);
1802 if (queryOnly)
1803 return(FALSE);
1804
1805 wxString sqlStmt;
1806 sqlStmt.Empty();
1807
1808 // Build the SQL DELETE statement
1809 BuildDeleteStmt(sqlStmt, DB_DEL_WHERE, pWhereClause);
1810
1811 pDb->WriteSqlLog(sqlStmt);
1812
1813 // Execute the SQL DELETE statement
1814 return(execDelete(sqlStmt));
1815
1816 } // wxDbTable::DeleteWhere()
1817
1818
1819 /********** wxDbTable::DeleteMatching() **********/
1820 bool wxDbTable::DeleteMatching(void)
1821 {
1822 wxASSERT(!queryOnly);
1823 if (queryOnly)
1824 return(FALSE);
1825
1826 wxString sqlStmt;
1827 sqlStmt.Empty();
1828
1829 // Build the SQL DELETE statement
1830 BuildDeleteStmt(sqlStmt, DB_DEL_MATCHING);
1831
1832 pDb->WriteSqlLog(sqlStmt);
1833
1834 // Execute the SQL DELETE statement
1835 return(execDelete(sqlStmt));
1836
1837 } // wxDbTable::DeleteMatching()
1838
1839
1840 /********** wxDbTable::IsColNull() **********/
1841 bool wxDbTable::IsColNull(int colNo)
1842 {
1843 /*
1844 This logic is just not right. It would indicate TRUE
1845 if a numeric field were set to a value of 0.
1846
1847 switch(colDefs[colNo].SqlCtype)
1848 {
1849 case SQL_C_CHAR:
1850 return(((UCHAR FAR *) colDefs[colNo].PtrDataObj)[0] == 0);
1851 case SQL_C_SSHORT:
1852 return(( *((SWORD *) colDefs[colNo].PtrDataObj)) == 0);
1853 case SQL_C_USHORT:
1854 return(( *((UWORD*) colDefs[colNo].PtrDataObj)) == 0);
1855 case SQL_C_SLONG:
1856 return(( *((SDWORD *) colDefs[colNo].PtrDataObj)) == 0);
1857 case SQL_C_ULONG:
1858 return(( *((UDWORD *) colDefs[colNo].PtrDataObj)) == 0);
1859 case SQL_C_FLOAT:
1860 return(( *((SFLOAT *) colDefs[colNo].PtrDataObj)) == 0);
1861 case SQL_C_DOUBLE:
1862 return((*((SDOUBLE *) colDefs[colNo].PtrDataObj)) == 0);
1863 case SQL_C_TIMESTAMP:
1864 TIMESTAMP_STRUCT *pDt;
1865 pDt = (TIMESTAMP_STRUCT *) colDefs[colNo].PtrDataObj;
1866 if (pDt->year == 0 && pDt->month == 0 && pDt->day == 0)
1867 return(TRUE);
1868 else
1869 return(FALSE);
1870 default:
1871 return(TRUE);
1872 }
1873 */
1874 return (colDefs[colNo].Null);
1875 } // wxDbTable::IsColNull()
1876
1877
1878 /********** wxDbTable::CanSelectForUpdate() **********/
1879 bool wxDbTable::CanSelectForUpdate(void)
1880 {
1881 if (queryOnly)
1882 return FALSE;
1883
1884 if (pDb->Dbms() == dbmsMY_SQL)
1885 return FALSE;
1886
1887 if ((pDb->Dbms() == dbmsORACLE) ||
1888 (pDb->dbInf.posStmts & SQL_PS_SELECT_FOR_UPDATE))
1889 return(TRUE);
1890 else
1891 return(FALSE);
1892
1893 } // wxDbTable::CanSelectForUpdate()
1894
1895
1896 /********** wxDbTable::CanUpdByROWID() **********/
1897 bool wxDbTable::CanUpdByROWID(void)
1898 {
1899 /*
1900 * NOTE: Returning FALSE for now until this can be debugged,
1901 * as the ROWID is not getting updated correctly
1902 */
1903 return FALSE;
1904
1905 if (pDb->Dbms() == dbmsORACLE)
1906 return(TRUE);
1907 else
1908 return(FALSE);
1909
1910 } // wxDbTable::CanUpdByROWID()
1911
1912
1913 /********** wxDbTable::IsCursorClosedOnCommit() **********/
1914 bool wxDbTable::IsCursorClosedOnCommit(void)
1915 {
1916 if (pDb->dbInf.cursorCommitBehavior == SQL_CB_PRESERVE)
1917 return(FALSE);
1918 else
1919 return(TRUE);
1920
1921 } // wxDbTable::IsCursorClosedOnCommit()
1922
1923
1924
1925 /********** wxDbTable::ClearMemberVar() **********/
1926 void wxDbTable::ClearMemberVar(int colNo, bool setToNull)
1927 {
1928 wxASSERT(colNo < noCols);
1929
1930 switch(colDefs[colNo].SqlCtype)
1931 {
1932 case SQL_C_CHAR:
1933 ((UCHAR FAR *) colDefs[colNo].PtrDataObj)[0] = 0;
1934 break;
1935 case SQL_C_SSHORT:
1936 *((SWORD *) colDefs[colNo].PtrDataObj) = 0;
1937 break;
1938 case SQL_C_USHORT:
1939 *((UWORD*) colDefs[colNo].PtrDataObj) = 0;
1940 break;
1941 case SQL_C_SLONG:
1942 *((SDWORD *) colDefs[colNo].PtrDataObj) = 0;
1943 break;
1944 case SQL_C_ULONG:
1945 *((UDWORD *) colDefs[colNo].PtrDataObj) = 0;
1946 break;
1947 case SQL_C_FLOAT:
1948 *((SFLOAT *) colDefs[colNo].PtrDataObj) = 0.0f;
1949 break;
1950 case SQL_C_DOUBLE:
1951 *((SDOUBLE *) colDefs[colNo].PtrDataObj) = 0.0f;
1952 break;
1953 case SQL_C_TIMESTAMP:
1954 TIMESTAMP_STRUCT *pDt;
1955 pDt = (TIMESTAMP_STRUCT *) colDefs[colNo].PtrDataObj;
1956 pDt->year = 0;
1957 pDt->month = 0;
1958 pDt->day = 0;
1959 pDt->hour = 0;
1960 pDt->minute = 0;
1961 pDt->second = 0;
1962 pDt->fraction = 0;
1963 break;
1964 }
1965
1966 if (setToNull)
1967 SetColNull(colNo);
1968 } // wxDbTable::ClearMemberVar()
1969
1970
1971 /********** wxDbTable::ClearMemberVars() **********/
1972 void wxDbTable::ClearMemberVars(bool setToNull)
1973 {
1974 int i;
1975
1976 // Loop through the columns setting each member variable to zero
1977 for (i=0; i < noCols; i++)
1978 ClearMemberVar(i,setToNull);
1979
1980 } // wxDbTable::ClearMemberVars()
1981
1982
1983 /********** wxDbTable::SetQueryTimeout() **********/
1984 bool wxDbTable::SetQueryTimeout(UDWORD nSeconds)
1985 {
1986 if (SQLSetStmtOption(hstmtInsert, SQL_QUERY_TIMEOUT, nSeconds) != SQL_SUCCESS)
1987 return(pDb->DispAllErrors(henv, hdbc, hstmtInsert));
1988 if (SQLSetStmtOption(hstmtUpdate, SQL_QUERY_TIMEOUT, nSeconds) != SQL_SUCCESS)
1989 return(pDb->DispAllErrors(henv, hdbc, hstmtUpdate));
1990 if (SQLSetStmtOption(hstmtDelete, SQL_QUERY_TIMEOUT, nSeconds) != SQL_SUCCESS)
1991 return(pDb->DispAllErrors(henv, hdbc, hstmtDelete));
1992 if (SQLSetStmtOption(hstmtInternal, SQL_QUERY_TIMEOUT, nSeconds) != SQL_SUCCESS)
1993 return(pDb->DispAllErrors(henv, hdbc, hstmtInternal));
1994
1995 // Completed Successfully
1996 return(TRUE);
1997
1998 } // wxDbTable::SetQueryTimeout()
1999
2000
2001 /********** wxDbTable::SetColDefs() **********/
2002 void wxDbTable::SetColDefs(int index, const wxString &fieldName, int dataType, void *pData,
2003 int cType, int size, bool keyField, bool upd,
2004 bool insAllow, bool derivedCol)
2005 {
2006 if (!colDefs) // May happen if the database connection fails
2007 return;
2008
2009 if (fieldName.Length() > (unsigned int) DB_MAX_COLUMN_NAME_LEN)
2010 {
2011 wxStrncpy (colDefs[index].ColName, fieldName, DB_MAX_COLUMN_NAME_LEN);
2012 colDefs[index].ColName[DB_MAX_COLUMN_NAME_LEN] = 0;
2013 }
2014 else
2015 wxStrcpy(colDefs[index].ColName, fieldName);
2016
2017 colDefs[index].DbDataType = dataType;
2018 colDefs[index].PtrDataObj = pData;
2019 colDefs[index].SqlCtype = cType;
2020 colDefs[index].SzDataObj = size;
2021 colDefs[index].KeyField = keyField;
2022 colDefs[index].DerivedCol = derivedCol;
2023 // Derived columns by definition would NOT be "Insertable" or "Updateable"
2024 if (derivedCol)
2025 {
2026 colDefs[index].Updateable = FALSE;
2027 colDefs[index].InsertAllowed = FALSE;
2028 }
2029 else
2030 {
2031 colDefs[index].Updateable = upd;
2032 colDefs[index].InsertAllowed = insAllow;
2033 }
2034
2035 colDefs[index].Null = FALSE;
2036
2037 } // wxDbTable::SetColDefs()
2038
2039
2040 /********** wxDbTable::SetColDefs() **********/
2041 wxDbColDataPtr* wxDbTable::SetColDefs(wxDbColInf *pColInfs, ULONG numCols)
2042 {
2043 wxASSERT(pColInfs);
2044 wxDbColDataPtr *pColDataPtrs = NULL;
2045
2046 if (pColInfs)
2047 {
2048 ULONG index;
2049
2050 pColDataPtrs = new wxDbColDataPtr[numCols+1];
2051
2052 for (index = 0; index < numCols; index++)
2053 {
2054 // Process the fields
2055 switch (pColInfs[index].dbDataType)
2056 {
2057 case DB_DATA_TYPE_VARCHAR:
2058 pColDataPtrs[index].PtrDataObj = new wxChar[pColInfs[index].bufferLength+1];
2059 pColDataPtrs[index].SzDataObj = pColInfs[index].columnSize;
2060 pColDataPtrs[index].SqlCtype = SQL_C_CHAR;
2061 break;
2062 case DB_DATA_TYPE_INTEGER:
2063 // Can be long or short
2064 if (pColInfs[index].bufferLength == sizeof(long))
2065 {
2066 pColDataPtrs[index].PtrDataObj = new long;
2067 pColDataPtrs[index].SzDataObj = sizeof(long);
2068 pColDataPtrs[index].SqlCtype = SQL_C_SLONG;
2069 }
2070 else
2071 {
2072 pColDataPtrs[index].PtrDataObj = new short;
2073 pColDataPtrs[index].SzDataObj = sizeof(short);
2074 pColDataPtrs[index].SqlCtype = SQL_C_SSHORT;
2075 }
2076 break;
2077 case DB_DATA_TYPE_FLOAT:
2078 // Can be float or double
2079 if (pColInfs[index].bufferLength == sizeof(float))
2080 {
2081 pColDataPtrs[index].PtrDataObj = new float;
2082 pColDataPtrs[index].SzDataObj = sizeof(float);
2083 pColDataPtrs[index].SqlCtype = SQL_C_FLOAT;
2084 }
2085 else
2086 {
2087 pColDataPtrs[index].PtrDataObj = new double;
2088 pColDataPtrs[index].SzDataObj = sizeof(double);
2089 pColDataPtrs[index].SqlCtype = SQL_C_DOUBLE;
2090 }
2091 break;
2092 case DB_DATA_TYPE_DATE:
2093 pColDataPtrs[index].PtrDataObj = new TIMESTAMP_STRUCT;
2094 pColDataPtrs[index].SzDataObj = sizeof(TIMESTAMP_STRUCT);
2095 pColDataPtrs[index].SqlCtype = SQL_C_TIMESTAMP;
2096 break;
2097 }
2098 SetColDefs (index,pColInfs[index].colName,pColInfs[index].dbDataType, pColDataPtrs[index].PtrDataObj, pColDataPtrs[index].SqlCtype, pColDataPtrs[index].SzDataObj);
2099 }
2100 }
2101
2102 return (pColDataPtrs);
2103
2104 } // wxDbTable::SetColDefs()
2105
2106
2107 /********** wxDbTable::SetCursor() **********/
2108 void wxDbTable::SetCursor(HSTMT *hstmtActivate)
2109 {
2110 if (hstmtActivate == wxDB_DEFAULT_CURSOR)
2111 hstmt = *hstmtDefault;
2112 else
2113 hstmt = *hstmtActivate;
2114
2115 } // wxDbTable::SetCursor()
2116
2117
2118 /********** wxDbTable::Count(const wxString &) **********/
2119 ULONG wxDbTable::Count(const wxString &args)
2120 {
2121 ULONG count;
2122 wxString sqlStmt;
2123 SDWORD cb;
2124
2125 // Build a "SELECT COUNT(*) FROM queryTableName [WHERE whereClause]" SQL Statement
2126 sqlStmt = wxT("SELECT COUNT(");
2127 sqlStmt += args;
2128 sqlStmt += wxT(") FROM ");
2129 sqlStmt += queryTableName;
2130 #if wxODBC_BACKWARD_COMPATABILITY
2131 if (from && wxStrlen(from))
2132 #else
2133 if (from.Length())
2134 #endif
2135 sqlStmt += from;
2136
2137 // Add the where clause if one is provided
2138 #if wxODBC_BACKWARD_COMPATABILITY
2139 if (where && wxStrlen(where))
2140 #else
2141 if (where.Length())
2142 #endif
2143 {
2144 sqlStmt += wxT(" WHERE ");
2145 sqlStmt += where;
2146 }
2147
2148 pDb->WriteSqlLog(sqlStmt);
2149
2150 // Initialize the Count cursor if it's not already initialized
2151 if (!hstmtCount)
2152 {
2153 hstmtCount = GetNewCursor(FALSE,FALSE);
2154 wxASSERT(hstmtCount);
2155 if (!hstmtCount)
2156 return(0);
2157 }
2158
2159 // Execute the SQL statement
2160 if (SQLExecDirect(*hstmtCount, (UCHAR FAR *) sqlStmt.c_str(), SQL_NTS) != SQL_SUCCESS)
2161 {
2162 pDb->DispAllErrors(henv, hdbc, *hstmtCount);
2163 return(0);
2164 }
2165
2166 // Fetch the record
2167 if (SQLFetch(*hstmtCount) != SQL_SUCCESS)
2168 {
2169 pDb->DispAllErrors(henv, hdbc, *hstmtCount);
2170 return(0);
2171 }
2172
2173 // Obtain the result
2174 if (SQLGetData(*hstmtCount, 1, SQL_C_ULONG, &count, sizeof(count), &cb) != SQL_SUCCESS)
2175 {
2176 pDb->DispAllErrors(henv, hdbc, *hstmtCount);
2177 return(0);
2178 }
2179
2180 // Free the cursor
2181 if (SQLFreeStmt(*hstmtCount, SQL_CLOSE) != SQL_SUCCESS)
2182 pDb->DispAllErrors(henv, hdbc, *hstmtCount);
2183
2184 // Return the record count
2185 return(count);
2186
2187 } // wxDbTable::Count()
2188
2189
2190 /********** wxDbTable::Refresh() **********/
2191 bool wxDbTable::Refresh(void)
2192 {
2193 bool result = TRUE;
2194
2195 // Switch to the internal cursor so any active cursors are not corrupted
2196 HSTMT currCursor = GetCursor();
2197 hstmt = hstmtInternal;
2198 #if wxODBC_BACKWARD_COMPATABILITY
2199 // Save the where and order by clauses
2200 char *saveWhere = where;
2201 char *saveOrderBy = orderBy;
2202 #else
2203 wxString saveWhere = where;
2204 wxString saveOrderBy = orderBy;
2205 #endif
2206 // Build a where clause to refetch the record with. Try and use the
2207 // ROWID if it's available, ow use the key fields.
2208 wxString whereClause;
2209 whereClause.Empty();
2210
2211 if (CanUpdByROWID())
2212 {
2213 SDWORD cb;
2214 wxChar rowid[wxDB_ROWID_LEN+1];
2215
2216 // Get the ROWID value. If not successful retreiving the ROWID,
2217 // simply fall down through the code and build the WHERE clause
2218 // based on the key fields.
2219 if (SQLGetData(hstmt, noCols+1, SQL_C_CHAR, (UCHAR*) rowid, wxDB_ROWID_LEN, &cb) == SQL_SUCCESS)
2220 {
2221 whereClause += queryTableName;
2222 whereClause += wxT(".ROWID = '");
2223 whereClause += rowid;
2224 whereClause += wxT("'");
2225 }
2226 }
2227
2228 // If unable to use the ROWID, build a where clause from the keyfields
2229 if (wxStrlen(whereClause) == 0)
2230 BuildWhereClause(whereClause, DB_WHERE_KEYFIELDS, queryTableName);
2231
2232 // Requery the record
2233 where = whereClause;
2234 orderBy.Empty();
2235 if (!Query())
2236 result = FALSE;
2237
2238 if (result && !GetNext())
2239 result = FALSE;
2240
2241 // Switch back to original cursor
2242 SetCursor(&currCursor);
2243
2244 // Free the internal cursor
2245 if (SQLFreeStmt(hstmtInternal, SQL_CLOSE) != SQL_SUCCESS)
2246 pDb->DispAllErrors(henv, hdbc, hstmtInternal);
2247
2248 // Restore the original where and order by clauses
2249 where = saveWhere;
2250 orderBy = saveOrderBy;
2251
2252 return(result);
2253
2254 } // wxDbTable::Refresh()
2255
2256
2257 /********** wxDbTable::SetColNull(int colNo, bool set) **********/
2258 bool wxDbTable::SetColNull(int colNo, bool set)
2259 {
2260 if (colNo < noCols)
2261 {
2262 colDefs[colNo].Null = set;
2263 if (set) // Blank out the values in the member variable
2264 ClearMemberVar(colNo,FALSE); // Must call with FALSE, or infinite recursion will happen
2265 return(TRUE);
2266 }
2267 else
2268 return(FALSE);
2269
2270 } // wxDbTable::SetColNull()
2271
2272
2273 /********** wxDbTable::SetColNull(const wxString &colName, bool set) **********/
2274 bool wxDbTable::SetColNull(const wxString &colName, bool set)
2275 {
2276 int i;
2277 for (i = 0; i < noCols; i++)
2278 {
2279 if (!wxStricmp(colName, colDefs[i].ColName))
2280 break;
2281 }
2282
2283 if (i < noCols)
2284 {
2285 colDefs[i].Null = set;
2286 if (set) // Blank out the values in the member variable
2287 ClearMemberVar(i,FALSE); // Must call with FALSE, or infinite recursion will happen
2288 return(TRUE);
2289 }
2290 else
2291 return(FALSE);
2292
2293 } // wxDbTable::SetColNull()
2294
2295
2296 /********** wxDbTable::GetNewCursor() **********/
2297 HSTMT *wxDbTable::GetNewCursor(bool setCursor, bool bindColumns)
2298 {
2299 HSTMT *newHSTMT = new HSTMT;
2300 wxASSERT(newHSTMT);
2301 if (!newHSTMT)
2302 return(0);
2303
2304 if (SQLAllocStmt(hdbc, newHSTMT) != SQL_SUCCESS)
2305 {
2306 pDb->DispAllErrors(henv, hdbc);
2307 delete newHSTMT;
2308 return(0);
2309 }
2310
2311 if (SQLSetStmtOption(*newHSTMT, SQL_CURSOR_TYPE, cursorType) != SQL_SUCCESS)
2312 {
2313 pDb->DispAllErrors(henv, hdbc, *newHSTMT);
2314 delete newHSTMT;
2315 return(0);
2316 }
2317
2318 if (bindColumns)
2319 {
2320 if(!bindCols(*newHSTMT))
2321 {
2322 delete newHSTMT;
2323 return(0);
2324 }
2325 }
2326
2327 if (setCursor)
2328 SetCursor(newHSTMT);
2329
2330 return(newHSTMT);
2331
2332 } // wxDbTable::GetNewCursor()
2333
2334
2335 /********** wxDbTable::DeleteCursor() **********/
2336 bool wxDbTable::DeleteCursor(HSTMT *hstmtDel)
2337 {
2338 bool result = TRUE;
2339
2340 if (!hstmtDel) // Cursor already deleted
2341 return(result);
2342
2343 /*
2344 ODBC 3.0 says to use this form
2345 if (SQLFreeHandle(*hstmtDel, SQL_DROP) != SQL_SUCCESS)
2346
2347 */
2348 if (SQLFreeStmt(*hstmtDel, SQL_DROP) != SQL_SUCCESS)
2349 {
2350 pDb->DispAllErrors(henv, hdbc);
2351 result = FALSE;
2352 }
2353
2354 delete hstmtDel;
2355
2356 return(result);
2357
2358 } // wxDbTable::DeleteCursor()
2359
2360 #endif // wxUSE_ODBC
2361
wxWidgets (Impactor)