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