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