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