]> 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
test for timegm() added
[wxWidgets.git] / src / common / dbtable.cpp
1 ///////////////////////////////////////////////////////////////////////////////
2 // Name: dbtable.cpp
3 // Purpose: Implementation of the wxTable class.
4 // Author: Doug Card
5 // Mods: April 1999
6 // -Dynamic cursor support - Only one predefined cursor, as many others as
7 // you need may be created on demand
8 // -Reduced number of active cursors significantly
9 // -Query-Only wxTable objects
10 // Created: 9.96
11 // RCS-ID: $Id$
12 // Copyright: (c) 1996 Remstar International, Inc.
13 // Licence: wxWindows licence, plus:
14 // Notice: This class library and its intellectual design are free of charge for use,
15 // modification, enhancement, debugging under the following conditions:
16 // 1) These classes may only be used as part of the implementation of a
17 // wxWindows-based application
18 // 2) All enhancements and bug fixes are to be submitted back to the wxWindows
19 // user groups free of all charges for use with the wxWindows library.
20 // 3) These classes may not be distributed as part of any other class library,
21 // DLL, text (written or electronic), other than a complete distribution of
22 // the wxWindows GUI development toolkit.
23 ///////////////////////////////////////////////////////////////////////////////
24
25 /*
26 // SYNOPSIS START
27 // SYNOPSIS STOP
28 */
29
30 // Use this line for wxWindows v1.x
31 //#include "wx_ver.h"
32 // Use this line for wxWindows v2.x
33 #include "wx/version.h"
34 #include "wx/wxprec.h"
35
36 #if wxMAJOR_VERSION == 2
37 # ifdef __GNUG__
38 # pragma implementation "dbtable.h"
39 # endif
40 #endif
41
42 #ifdef DBDEBUG_CONSOLE
43 #include <iostream.h>
44 #endif
45
46 #ifdef __BORLANDC__
47 #pragma hdrstop
48 #endif //__BORLANDC__
49
50 #if wxMAJOR_VERSION == 2
51 #ifndef WX_PRECOMP
52 #include "wx/string.h"
53 #include "wx/object.h"
54 #include "wx/list.h"
55 #include "wx/utils.h"
56 #include "wx/msgdlg.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 #if wxUSE_ODBC
73
74 #include <stdio.h>
75 #include <stdlib.h>
76 #include <string.h>
77 #include <assert.h>
78 #if wxMAJOR_VERSION == 1
79 #include "table.h"
80 #elif wxMAJOR_VERSION == 2
81 #include "wx/dbtable.h"
82 #endif
83
84 #ifdef __UNIX__
85 // The HPUX preprocessor lines below were commented out on 8/20/97
86 // because macros.h currently redefines DEBUG and is unneeded.
87 // # ifdef HPUX
88 // # include <macros.h>
89 // # endif
90 # ifdef LINUX
91 # include <sys/minmax.h>
92 # endif
93 #endif
94
95 ULONG lastTableID = 0;
96
97
98 #ifdef __WXDEBUG__
99 wxList TablesInUse;
100 #endif
101
102
103 /********** wxTable::wxTable() **********/
104 wxTable::wxTable(wxDB *pwxDB, const char *tblName, const int nCols,
105 const char *qryTblName, bool qryOnly, const char *tblPath)
106 {
107 pDb = pwxDB; // Pointer to the wxDB object
108 henv = 0;
109 hdbc = 0;
110 hstmt = 0;
111 hstmtDefault = 0; // Initialized below
112 hstmtCount = 0; // Initialized first time it is needed
113 hstmtInsert = 0;
114 hstmtDelete = 0;
115 hstmtUpdate = 0;
116 hstmtInternal = 0;
117 colDefs = 0;
118 tableID = 0;
119 noCols = nCols; // No. of cols in the table
120 where = 0; // Where clause
121 orderBy = 0; // Order By clause
122 from = 0; // From clause
123 selectForUpdate = FALSE; // SELECT ... FOR UPDATE; Indicates whether to include the FOR UPDATE phrase
124 queryOnly = qryOnly;
125
126 assert (tblName);
127
128 wxStrcpy(tableName, tblName); // Table Name
129 if (tblPath)
130 wxStrcpy(tablePath, tblPath); // Table Path - used for dBase files
131
132 if (qryTblName) // Name of the table/view to query
133 wxStrcpy(queryTableName, qryTblName);
134 else
135 wxStrcpy(queryTableName, tblName);
136
137 // assert(pDb); // Assert is placed after table name is assigned for error reporting reasons
138 if (!pDb)
139 return;
140
141 pDb->nTables++;
142
143 char s[200];
144 tableID = ++lastTableID;
145 sprintf(s, "wxTable constructor (%-20s) tableID:[%6lu] pDb:[%p]", tblName,tableID,pDb);
146
147 #ifdef __WXDEBUG__
148 CstructTablesInUse *tableInUse;
149 tableInUse = new CstructTablesInUse();
150 tableInUse->tableName = tblName;
151 tableInUse->tableID = tableID;
152 tableInUse->pDb = pDb;
153 TablesInUse.Append(tableInUse);
154 #endif
155
156 pDb->WriteSqlLog(s);
157
158 // Grab the HENV and HDBC from the wxDB object
159 henv = pDb->henv;
160 hdbc = pDb->hdbc;
161
162 // Allocate space for column definitions
163 if (noCols)
164 colDefs = new CcolDef[noCols]; // Points to the first column defintion
165
166 // Allocate statement handles for the table
167 if (!queryOnly)
168 {
169 // Allocate a separate statement handle for performing inserts
170 if (SQLAllocStmt(hdbc, &hstmtInsert) != SQL_SUCCESS)
171 pDb->DispAllErrors(henv, hdbc);
172 // Allocate a separate statement handle for performing deletes
173 if (SQLAllocStmt(hdbc, &hstmtDelete) != SQL_SUCCESS)
174 pDb->DispAllErrors(henv, hdbc);
175 // Allocate a separate statement handle for performing updates
176 if (SQLAllocStmt(hdbc, &hstmtUpdate) != SQL_SUCCESS)
177 pDb->DispAllErrors(henv, hdbc);
178 }
179 // Allocate a separate statement handle for internal use
180 if (SQLAllocStmt(hdbc, &hstmtInternal) != SQL_SUCCESS)
181 pDb->DispAllErrors(henv, hdbc);
182
183 // Set the cursor type for the statement handles
184 cursorType = SQL_CURSOR_STATIC;
185 if (SQLSetStmtOption(hstmtInternal, SQL_CURSOR_TYPE, cursorType) != SQL_SUCCESS)
186 {
187 // Check to see if cursor type is supported
188 pDb->GetNextError(henv, hdbc, hstmtInternal);
189 if (! wxStrcmp(pDb->sqlState, "01S02")) // Option Value Changed
190 {
191 // Datasource does not support static cursors. Driver
192 // will substitute a cursor type. Call SQLGetStmtOption()
193 // to determine which cursor type was selected.
194 if (SQLGetStmtOption(hstmtInternal, SQL_CURSOR_TYPE, &cursorType) != SQL_SUCCESS)
195 pDb->DispAllErrors(henv, hdbc, hstmtInternal);
196 #ifdef DBDEBUG_CONSOLE
197 cout << "Static cursor changed to: ";
198 switch(cursorType)
199 {
200 case SQL_CURSOR_FORWARD_ONLY:
201 cout << "Forward Only"; break;
202 case SQL_CURSOR_STATIC:
203 cout << "Static"; break;
204 case SQL_CURSOR_KEYSET_DRIVEN:
205 cout << "Keyset Driven"; break;
206 case SQL_CURSOR_DYNAMIC:
207 cout << "Dynamic"; break;
208 }
209 cout << endl << endl;
210 #endif
211 }
212 else
213 {
214 pDb->DispNextError();
215 pDb->DispAllErrors(henv, hdbc, hstmtInternal);
216 }
217 }
218 #ifdef DBDEBUG_CONSOLE
219 else
220 cout << "Cursor Type set to STATIC" << endl << endl;
221 #endif
222
223 if (!queryOnly)
224 {
225 // Set the cursor type for the INSERT statement handle
226 if (SQLSetStmtOption(hstmtInsert, SQL_CURSOR_TYPE, SQL_CURSOR_FORWARD_ONLY) != SQL_SUCCESS)
227 pDb->DispAllErrors(henv, hdbc, hstmtInsert);
228 // Set the cursor type for the DELETE statement handle
229 if (SQLSetStmtOption(hstmtDelete, SQL_CURSOR_TYPE, SQL_CURSOR_FORWARD_ONLY) != SQL_SUCCESS)
230 pDb->DispAllErrors(henv, hdbc, hstmtDelete);
231 // Set the cursor type for the UPDATE statement handle
232 if (SQLSetStmtOption(hstmtUpdate, SQL_CURSOR_TYPE, SQL_CURSOR_FORWARD_ONLY) != SQL_SUCCESS)
233 pDb->DispAllErrors(henv, hdbc, hstmtUpdate);
234 }
235
236 // Make the default cursor the active cursor
237 hstmtDefault = NewCursor(FALSE,FALSE);
238 assert(hstmtDefault);
239 hstmt = *hstmtDefault;
240
241 } // wxTable::wxTable()
242
243 /********** wxTable::~wxTable() **********/
244 wxTable::~wxTable()
245 {
246 char s[80];
247 if (pDb)
248 {
249 sprintf(s, "wxTable destructor (%-20s) tableID:[%6lu] pDb:[%p]", tableName,tableID,pDb);
250 pDb->WriteSqlLog(s);
251 }
252
253 #ifdef __WXDEBUG__
254 if (tableID)
255 {
256 bool found = FALSE;
257 wxNode *pNode;
258 pNode = TablesInUse.First();
259 while (pNode && !found)
260 {
261 if (((CstructTablesInUse *)pNode->Data())->tableID == tableID)
262 {
263 found = TRUE;
264 if (!TablesInUse.DeleteNode(pNode))
265 wxMessageBox (s,"Unable to delete node!");
266 }
267 else
268 pNode = pNode->Next();
269 }
270 if (!found)
271 {
272 char msg[250];
273 sprintf(msg,"Unable to find the tableID in the linked\nlist of tables in use.\n\n%s",s);
274 wxMessageBox (msg,"NOTICE...");
275 }
276 }
277 #endif
278
279 // Decrement the wxDB table count
280 if (pDb)
281 pDb->nTables--;
282
283 // Delete memory allocated for column definitions
284 if (colDefs)
285 delete [] colDefs;
286
287 // Free statement handles
288 if (!queryOnly)
289 {
290 if (hstmtInsert)
291 if (SQLFreeStmt(hstmtInsert, SQL_DROP) != SQL_SUCCESS)
292 pDb->DispAllErrors(henv, hdbc);
293 if (hstmtDelete)
294 if (SQLFreeStmt(hstmtDelete, SQL_DROP) != SQL_SUCCESS)
295 pDb->DispAllErrors(henv, hdbc);
296 if (hstmtUpdate)
297 if (SQLFreeStmt(hstmtUpdate, SQL_DROP) != SQL_SUCCESS)
298 pDb->DispAllErrors(henv, hdbc);
299 }
300 if (hstmtInternal)
301 if (SQLFreeStmt(hstmtInternal, SQL_DROP) != SQL_SUCCESS)
302 pDb->DispAllErrors(henv, hdbc);
303
304 // Delete dynamically allocated cursors
305 if (hstmtDefault)
306 DeleteCursor(hstmtDefault);
307 if (hstmtCount)
308 DeleteCursor(hstmtCount);
309
310 } // wxTable::~wxTable()
311
312 /***************************** PRIVATE FUNCTIONS *****************************/
313
314 /********** wxTable::bindInsertParams() **********/
315 bool wxTable::bindInsertParams(void)
316 {
317 assert(!queryOnly);
318 if (queryOnly)
319 return(FALSE);
320
321 SWORD fSqlType = 0;
322 UDWORD precision = 0;
323 SWORD scale = 0;
324
325 // Bind each column (that can be inserted) of the table to a parameter marker
326 int i;
327 for (i = 0; i < noCols; i++)
328 {
329 if (! colDefs[i].InsertAllowed)
330 continue;
331 switch(colDefs[i].DbDataType)
332 {
333 case DB_DATA_TYPE_VARCHAR:
334 fSqlType = pDb->typeInfVarchar.FsqlType;
335 precision = colDefs[i].SzDataObj;
336 scale = 0;
337 colDefs[i].CbValue = SQL_NTS;
338 break;
339 case DB_DATA_TYPE_INTEGER:
340 fSqlType = pDb->typeInfInteger.FsqlType;
341 precision = pDb->typeInfInteger.Precision;
342 scale = 0;
343 colDefs[i].CbValue = 0;
344 break;
345 case DB_DATA_TYPE_FLOAT:
346 fSqlType = pDb->typeInfFloat.FsqlType;
347 precision = pDb->typeInfFloat.Precision;
348 scale = pDb->typeInfFloat.MaximumScale;
349 // SQL Sybase Anywhere v5.5 returned a negative number for the
350 // MaxScale. This caused ODBC to kick out an error on ibscale.
351 // I check for this here and set the scale = precision.
352 //if (scale < 0)
353 // scale = (short) precision;
354 colDefs[i].CbValue = 0;
355 break;
356 case DB_DATA_TYPE_DATE:
357 fSqlType = pDb->typeInfDate.FsqlType;
358 precision = pDb->typeInfDate.Precision;
359 scale = 0;
360 colDefs[i].CbValue = 0;
361 break;
362 }
363 // Null values
364 if (colDefs[i].Null)
365 {
366 colDefs[i].CbValue = SQL_NULL_DATA;
367 colDefs[i].Null = FALSE;
368 }
369 if (SQLBindParameter(hstmtInsert, i+1, SQL_PARAM_INPUT, colDefs[i].SqlCtype,
370 fSqlType, precision, scale, (UCHAR*) colDefs[i].PtrDataObj,
371 precision+1,&colDefs[i].CbValue) != SQL_SUCCESS)
372 return(pDb->DispAllErrors(henv, hdbc, hstmtInsert));
373 }
374
375 // Completed successfully
376 return(TRUE);
377
378 } // wxTable::bindInsertParams()
379
380 /********** wxTable::bindUpdateParams() **********/
381 bool wxTable::bindUpdateParams(void)
382 {
383 assert(!queryOnly);
384 if (queryOnly)
385 return(FALSE);
386
387 SWORD fSqlType = 0;
388 UDWORD precision = 0;
389 SWORD scale = 0;
390
391 // Bind each UPDATEABLE column of the table to a parameter marker
392 int i,colNo;
393 for (i = 0, colNo = 1; i < noCols; i++)
394 {
395 if (! colDefs[i].Updateable)
396 continue;
397 switch(colDefs[i].DbDataType)
398 {
399 case DB_DATA_TYPE_VARCHAR:
400 fSqlType = pDb->typeInfVarchar.FsqlType;
401 precision = colDefs[i].SzDataObj;
402 scale = 0;
403 colDefs[i].CbValue = SQL_NTS;
404 break;
405 case DB_DATA_TYPE_INTEGER:
406 fSqlType = pDb->typeInfInteger.FsqlType;
407 precision = pDb->typeInfInteger.Precision;
408 scale = 0;
409 colDefs[i].CbValue = 0;
410 break;
411 case DB_DATA_TYPE_FLOAT:
412 fSqlType = pDb->typeInfFloat.FsqlType;
413 precision = pDb->typeInfFloat.Precision;
414 scale = pDb->typeInfFloat.MaximumScale;
415 // SQL Sybase Anywhere v5.5 returned a negative number for the
416 // MaxScale. This caused ODBC to kick out an error on ibscale.
417 // I check for this here and set the scale = precision.
418 //if (scale < 0)
419 // scale = (short) precision;
420 colDefs[i].CbValue = 0;
421 break;
422 case DB_DATA_TYPE_DATE:
423 fSqlType = pDb->typeInfDate.FsqlType;
424 precision = pDb->typeInfDate.Precision;
425 scale = 0;
426 colDefs[i].CbValue = 0;
427 break;
428 }
429 if (SQLBindParameter(hstmtUpdate, colNo++, SQL_PARAM_INPUT, colDefs[i].SqlCtype,
430 fSqlType, precision, scale, (UCHAR*) colDefs[i].PtrDataObj,
431 precision+1, &colDefs[i].CbValue) != SQL_SUCCESS)
432 return(pDb->DispAllErrors(henv, hdbc, hstmtUpdate));
433 }
434
435 // Completed successfully
436 return(TRUE);
437
438 } // wxTable::bindUpdateParams()
439
440 /********** wxTable::bindCols() **********/
441 bool wxTable::bindCols(HSTMT cursor)
442 {
443 static SDWORD cb;
444
445 // Bind each column of the table to a memory address for fetching data
446 int i;
447 for (i = 0; i < noCols; i++)
448 {
449 if (SQLBindCol(cursor, i+1, colDefs[i].SqlCtype, (UCHAR*) colDefs[i].PtrDataObj,
450 colDefs[i].SzDataObj, &cb) != SQL_SUCCESS)
451 return(pDb->DispAllErrors(henv, hdbc, cursor));
452 }
453
454 // Completed successfully
455 return(TRUE);
456
457 } // wxTable::bindCols()
458
459 /********** wxTable::getRec() **********/
460 bool wxTable::getRec(UWORD fetchType)
461 {
462 RETCODE retcode;
463
464 if (!pDb->FwdOnlyCursors())
465 {
466 // Fetch the NEXT, PREV, FIRST or LAST record, depending on fetchType
467 UDWORD cRowsFetched;
468 UWORD rowStatus;
469
470 retcode = SQLExtendedFetch(hstmt, fetchType, 0, &cRowsFetched, &rowStatus);
471 if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
472 if (retcode == SQL_NO_DATA_FOUND)
473 return(FALSE);
474 else
475 return(pDb->DispAllErrors(henv, hdbc, hstmt));
476 }
477 else
478 {
479 // Fetch the next record from the record set
480 retcode = SQLFetch(hstmt);
481 if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
482 {
483 if (retcode == SQL_NO_DATA_FOUND)
484 return(FALSE);
485 else
486 return(pDb->DispAllErrors(henv, hdbc, hstmt));
487 }
488 }
489
490 // Completed successfully
491 return(TRUE);
492
493 } // wxTable::getRec()
494
495 /********** wxTable::execDelete() **********/
496 bool wxTable::execDelete(const char *pSqlStmt)
497 {
498 // Execute the DELETE statement
499 if (SQLExecDirect(hstmtDelete, (UCHAR FAR *) pSqlStmt, SQL_NTS) != SQL_SUCCESS)
500 return(pDb->DispAllErrors(henv, hdbc, hstmtDelete));
501
502 // Record deleted successfully
503 return(TRUE);
504
505 } // wxTable::execDelete()
506
507 /********** wxTable::execUpdate() **********/
508 bool wxTable::execUpdate(const char *pSqlStmt)
509 {
510 // Execute the UPDATE statement
511 if (SQLExecDirect(hstmtUpdate, (UCHAR FAR *) pSqlStmt, SQL_NTS) != SQL_SUCCESS)
512 return(pDb->DispAllErrors(henv, hdbc, hstmtUpdate));
513
514 // Record deleted successfully
515 return(TRUE);
516
517 } // wxTable::execUpdate()
518
519 /********** wxTable::query() **********/
520 bool wxTable::query(int queryType, bool forUpdate, bool distinct, char *pSqlStmt)
521 {
522 char sqlStmt[DB_MAX_STATEMENT_LEN];
523
524 // Set the selectForUpdate member variable
525 if (forUpdate)
526 // The user may wish to select for update, but the DBMS may not be capable
527 selectForUpdate = CanSelectForUpdate();
528 else
529 selectForUpdate = FALSE;
530
531 // Set the SQL SELECT string
532 if (queryType != DB_SELECT_STATEMENT) // A select statement was not passed in,
533 { // so generate a select statement.
534 GetSelectStmt(sqlStmt, queryType, distinct);
535 pDb->WriteSqlLog(sqlStmt);
536 }
537
538 // Make sure the cursor is closed first
539 if (! CloseCursor(hstmt))
540 return(FALSE);
541
542 // Execute the SQL SELECT statement
543 int retcode;
544
545 retcode = SQLExecDirect(hstmt, (UCHAR FAR *) (queryType == DB_SELECT_STATEMENT ? pSqlStmt : sqlStmt), SQL_NTS);
546 if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
547 return(pDb->DispAllErrors(henv, hdbc, hstmt));
548
549 // Completed successfully
550 return(TRUE);
551
552 } // wxTable::query()
553
554
555
556 /********** wxTable::Open() **********/
557 bool wxTable::Open(void)
558 {
559 if (!pDb)
560 return FALSE;
561
562 int i;
563 char sqlStmt[DB_MAX_STATEMENT_LEN];
564
565 // Verify that the table exists in the database
566 if (!pDb->TableExists(tableName,NULL,tablePath))
567 {
568 char s[128];
569 sprintf(s, "Error opening '%s', table/view does not exist in the database.", tableName);
570 pDb->LogError(s);
571 return(FALSE);
572 }
573
574 // Bind the member variables for field exchange between
575 // the wxTable object and the ODBC record.
576 if (!queryOnly)
577 {
578 if (!bindInsertParams()) // Inserts
579 return(FALSE);
580 if (!bindUpdateParams()) // Updates
581 return(FALSE);
582 }
583 if (!bindCols(*hstmtDefault)) // Selects
584 return(FALSE);
585 if (!bindCols(hstmtInternal)) // Internal use only
586 return(FALSE);
587 /*
588 * Do NOT bind the hstmtCount cursor!!!
589 */
590
591 // Build an insert statement using parameter markers
592 if (!queryOnly && noCols > 0)
593 {
594 bool needComma = FALSE;
595 sprintf(sqlStmt, "INSERT INTO %s (", tableName);
596 for (i = 0; i < noCols; i++)
597 {
598 if (! colDefs[i].InsertAllowed)
599 continue;
600 if (needComma)
601 wxStrcat(sqlStmt, ",");
602 wxStrcat(sqlStmt, colDefs[i].ColName);
603 needComma = TRUE;
604 }
605 needComma = FALSE;
606 wxStrcat(sqlStmt, ") VALUES (");
607 for (i = 0; i < noCols; i++)
608 {
609 if (! colDefs[i].InsertAllowed)
610 continue;
611 if (needComma)
612 wxStrcat(sqlStmt, ",");
613 wxStrcat(sqlStmt, "?");
614 needComma = TRUE;
615 }
616 wxStrcat(sqlStmt, ")");
617
618 // pDb->WriteSqlLog(sqlStmt);
619
620 // Prepare the insert statement for execution
621 if (SQLPrepare(hstmtInsert, (UCHAR FAR *) sqlStmt, SQL_NTS) != SQL_SUCCESS)
622 return(pDb->DispAllErrors(henv, hdbc, hstmtInsert));
623 }
624
625 // Completed successfully
626 return(TRUE);
627
628 } // wxTable::Open()
629
630 /********** wxTable::Query() **********/
631 bool wxTable::Query(bool forUpdate, bool distinct)
632 {
633
634 return(query(DB_SELECT_WHERE, forUpdate, distinct));
635
636 } // wxTable::Query()
637
638 /********** wxTable::QueryBySqlStmt() **********/
639 bool wxTable::QueryBySqlStmt(char *pSqlStmt)
640 {
641 pDb->WriteSqlLog(pSqlStmt);
642
643 return(query(DB_SELECT_STATEMENT, FALSE, FALSE, pSqlStmt));
644
645 } // wxTable::QueryBySqlStmt()
646
647 /********** wxTable::QueryMatching() **********/
648 bool wxTable::QueryMatching(bool forUpdate, bool distinct)
649 {
650
651 return(query(DB_SELECT_MATCHING, forUpdate, distinct));
652
653 } // wxTable::QueryMatching()
654
655 /********** wxTable::QueryOnKeyFields() **********/
656 bool wxTable::QueryOnKeyFields(bool forUpdate, bool distinct)
657 {
658
659 return(query(DB_SELECT_KEYFIELDS, forUpdate, distinct));
660
661 } // wxTable::QueryOnKeyFields()
662
663 /********** wxTable::GetPrev() **********/
664 bool wxTable::GetPrev(void)
665 {
666 if (pDb->FwdOnlyCursors())
667 {
668 wxFAIL_MSG(wxT("GetPrev()::Backward scrolling cursors are not enabled for this instance of wxTable"));
669 return FALSE;
670 }
671 else
672 return(getRec(SQL_FETCH_PRIOR));
673 } // wxTable::GetPrev()
674
675 /********** wxTable::operator-- **********/
676 bool wxTable::operator--(int)
677 {
678 if (pDb->FwdOnlyCursors())
679 {
680 wxFAIL_MSG(wxT("operator--:Backward scrolling cursors are not enabled for this instance of wxTable"));
681 return FALSE;
682 }
683 else
684 return(getRec(SQL_FETCH_PRIOR));
685 } // wxTable::operator--
686
687 /********** wxTable::GetFirst() **********/
688 bool wxTable::GetFirst(void)
689 {
690 if (pDb->FwdOnlyCursors())
691 {
692 wxFAIL_MSG(wxT("GetFirst():Backward scrolling cursors are not enabled for this instance of wxTable"));
693 return FALSE;
694 }
695 else
696 return(getRec(SQL_FETCH_FIRST));
697 } // wxTable::GetFirst()
698
699 /********** wxTable::GetLast() **********/
700 bool wxTable::GetLast(void)
701 {
702 if (pDb->FwdOnlyCursors())
703 {
704 wxFAIL_MSG(wxT("GetLast()::Backward scrolling cursors are not enabled for this instance of wxTable"));
705 return FALSE;
706 }
707 else
708 return(getRec(SQL_FETCH_LAST));
709 } // wxTable::GetLast()
710
711 /********** wxTable::GetSelectStmt() **********/
712 void wxTable::GetSelectStmt(char *pSqlStmt, int typeOfSelect, bool distinct)
713 {
714 char whereClause[DB_MAX_WHERE_CLAUSE_LEN];
715
716 whereClause[0] = 0;
717
718 // Build a select statement to query the database
719 wxStrcpy(pSqlStmt, "SELECT ");
720
721 // SELECT DISTINCT values only?
722 if (distinct)
723 wxStrcat(pSqlStmt, "DISTINCT ");
724
725 // Was a FROM clause specified to join tables to the base table?
726 // Available for ::Query() only!!!
727 bool appendFromClause = FALSE;
728 if (typeOfSelect == DB_SELECT_WHERE && from && wxStrlen(from))
729 appendFromClause = TRUE;
730
731 // Add the column list
732 int i;
733 for (i = 0; i < noCols; i++)
734 {
735 // If joining tables, the base table column names must be qualified to avoid ambiguity
736 if (appendFromClause)
737 {
738 wxStrcat(pSqlStmt, queryTableName);
739 wxStrcat(pSqlStmt, ".");
740 }
741 wxStrcat(pSqlStmt, colDefs[i].ColName);
742 if (i + 1 < noCols)
743 wxStrcat(pSqlStmt, ",");
744 }
745
746 // If the datasource supports ROWID, get this column as well. Exception: Don't retrieve
747 // the ROWID if querying distinct records. The rowid will always be unique.
748 if (!distinct && CanUpdByROWID())
749 {
750 // If joining tables, the base table column names must be qualified to avoid ambiguity
751 if (appendFromClause)
752 {
753 wxStrcat(pSqlStmt, ",");
754 wxStrcat(pSqlStmt, queryTableName);
755 wxStrcat(pSqlStmt, ".ROWID");
756 }
757 else
758 wxStrcat(pSqlStmt, ",ROWID");
759 }
760
761 // Append the FROM tablename portion
762 wxStrcat(pSqlStmt, " FROM ");
763 wxStrcat(pSqlStmt, queryTableName);
764
765 // Sybase uses the HOLDLOCK keyword to lock a record during query.
766 // The HOLDLOCK keyword follows the table name in the from clause.
767 // Each table in the from clause must specify HOLDLOCK or
768 // NOHOLDLOCK (the default). Note: The "FOR UPDATE" clause
769 // is parsed but ignored in SYBASE Transact-SQL.
770 if (selectForUpdate && (pDb->Dbms() == dbmsSYBASE_ASA || pDb->Dbms() == dbmsSYBASE_ASE))
771 wxStrcat(pSqlStmt, " HOLDLOCK");
772
773 if (appendFromClause)
774 wxStrcat(pSqlStmt, from);
775
776 // Append the WHERE clause. Either append the where clause for the class
777 // or build a where clause. The typeOfSelect determines this.
778 switch(typeOfSelect)
779 {
780 case DB_SELECT_WHERE:
781 if (where && wxStrlen(where)) // May not want a where clause!!!
782 {
783 wxStrcat(pSqlStmt, " WHERE ");
784 wxStrcat(pSqlStmt, where);
785 }
786 break;
787 case DB_SELECT_KEYFIELDS:
788 GetWhereClause(whereClause, DB_WHERE_KEYFIELDS);
789 if (wxStrlen(whereClause))
790 {
791 wxStrcat(pSqlStmt, " WHERE ");
792 wxStrcat(pSqlStmt, whereClause);
793 }
794 break;
795 case DB_SELECT_MATCHING:
796 GetWhereClause(whereClause, DB_WHERE_MATCHING);
797 if (wxStrlen(whereClause))
798 {
799 wxStrcat(pSqlStmt, " WHERE ");
800 wxStrcat(pSqlStmt, whereClause);
801 }
802 break;
803 }
804
805 // Append the ORDER BY clause
806 if (orderBy && wxStrlen(orderBy))
807 {
808 wxStrcat(pSqlStmt, " ORDER BY ");
809 wxStrcat(pSqlStmt, orderBy);
810 }
811
812 // SELECT FOR UPDATE if told to do so and the datasource is capable. Sybase
813 // parses the FOR UPDATE clause but ignores it. See the comment above on the
814 // HOLDLOCK for Sybase.
815 if (selectForUpdate && CanSelectForUpdate())
816 wxStrcat(pSqlStmt, " FOR UPDATE");
817
818 } // wxTable::GetSelectStmt()
819
820 /********** wxTable::GetRowNum() **********/
821 UWORD wxTable::GetRowNum(void)
822 {
823 UDWORD rowNum;
824
825 if (SQLGetStmtOption(hstmt, SQL_ROW_NUMBER, (UCHAR*) &rowNum) != SQL_SUCCESS)
826 {
827 pDb->DispAllErrors(henv, hdbc, hstmt);
828 return(0);
829 }
830
831 // Completed successfully
832 return((UWORD) rowNum);
833
834 } // wxTable::GetRowNum()
835
836 /********** wxTable::CloseCursor() **********/
837 bool wxTable::CloseCursor(HSTMT cursor)
838 {
839 if (SQLFreeStmt(cursor, SQL_CLOSE) != SQL_SUCCESS)
840 return(pDb->DispAllErrors(henv, hdbc, cursor));
841
842 // Completed successfully
843 return(TRUE);
844
845 } // wxTable::CloseCursor()
846
847 /********** wxTable::CreateTable() **********/
848 bool wxTable::CreateTable(bool attemptDrop)
849 {
850 if (!pDb)
851 return FALSE;
852
853 int i, j;
854 char sqlStmt[DB_MAX_STATEMENT_LEN];
855
856 #ifdef DBDEBUG_CONSOLE
857 cout << "Creating Table " << tableName << "..." << endl;
858 #endif
859
860 // Drop table first
861 if (attemptDrop && !DropTable())
862 return FALSE;
863
864 // Create the table
865 #ifdef DBDEBUG_CONSOLE
866 for (i = 0; i < noCols; i++)
867 {
868 // Exclude derived columns since they are NOT part of the base table
869 if (colDefs[i].DerivedCol)
870 continue;
871 cout << i + 1 << ": " << colDefs[i].ColName << "; ";
872 switch(colDefs[i].DbDataType)
873 {
874 case DB_DATA_TYPE_VARCHAR:
875 cout << pDb->typeInfVarchar.TypeName << "(" << colDefs[i].SzDataObj << ")";
876 break;
877 case DB_DATA_TYPE_INTEGER:
878 cout << pDb->typeInfInteger.TypeName;
879 break;
880 case DB_DATA_TYPE_FLOAT:
881 cout << pDb->typeInfFloat.TypeName;
882 break;
883 case DB_DATA_TYPE_DATE:
884 cout << pDb->typeInfDate.TypeName;
885 break;
886 }
887 cout << endl;
888 }
889 #endif
890
891 // Build a CREATE TABLE string from the colDefs structure.
892 bool needComma = FALSE;
893 sprintf(sqlStmt, "CREATE TABLE %s (", tableName);
894 for (i = 0; i < noCols; i++)
895 {
896 // Exclude derived columns since they are NOT part of the base table
897 if (colDefs[i].DerivedCol)
898 continue;
899 // Comma Delimiter
900 if (needComma)
901 wxStrcat(sqlStmt, ",");
902 // Column Name
903 wxStrcat(sqlStmt, colDefs[i].ColName);
904 wxStrcat(sqlStmt, " ");
905 // Column Type
906 switch(colDefs[i].DbDataType)
907 {
908 case DB_DATA_TYPE_VARCHAR:
909 wxStrcat(sqlStmt, pDb->typeInfVarchar.TypeName); break;
910 case DB_DATA_TYPE_INTEGER:
911 wxStrcat(sqlStmt, pDb->typeInfInteger.TypeName); break;
912 case DB_DATA_TYPE_FLOAT:
913 wxStrcat(sqlStmt, pDb->typeInfFloat.TypeName); break;
914 case DB_DATA_TYPE_DATE:
915 wxStrcat(sqlStmt, pDb->typeInfDate.TypeName); break;
916 }
917 // For varchars, append the size of the string
918 if (colDefs[i].DbDataType == DB_DATA_TYPE_VARCHAR)
919 {
920 char s[10];
921 // wxStrcat(sqlStmt, "(");
922 // wxStrcat(sqlStmt, itoa(colDefs[i].SzDataObj, s, 10));
923 // wxStrcat(sqlStmt, ")");
924 sprintf(s, "(%d)", colDefs[i].SzDataObj);
925 wxStrcat(sqlStmt, s);
926 }
927
928 if (pDb->Dbms() == dbmsSYBASE_ASE || pDb->Dbms() == dbmsMY_SQL)
929 {
930 if (colDefs[i].KeyField)
931 {
932 wxStrcat(sqlStmt, " NOT NULL");
933 }
934 }
935
936 needComma = TRUE;
937 }
938 // If there is a primary key defined, include it in the create statement
939 for (i = j = 0; i < noCols; i++)
940 {
941 if (colDefs[i].KeyField)
942 {
943 j++;
944 break;
945 }
946 }
947 if (j && pDb->Dbms() != dbmsDBASE) // Found a keyfield
948 {
949 if (pDb->Dbms() != dbmsMY_SQL)
950 {
951 wxStrcat(sqlStmt, ",CONSTRAINT ");
952 wxStrcat(sqlStmt, tableName);
953 wxStrcat(sqlStmt, "_PIDX PRIMARY KEY (");
954 }
955 else
956 {
957 /* MySQL goes out on this one. We also declare the relevant key NON NULL above */
958 wxStrcat(sqlStmt, ", PRIMARY KEY (");
959 }
960
961 // List column name(s) of column(s) comprising the primary key
962 for (i = j = 0; i < noCols; i++)
963 {
964 if (colDefs[i].KeyField)
965 {
966 if (j++) // Multi part key, comma separate names
967 wxStrcat(sqlStmt, ",");
968 wxStrcat(sqlStmt, colDefs[i].ColName);
969 }
970 }
971 wxStrcat(sqlStmt, ")");
972 }
973 // Append the closing parentheses for the create table statement
974 wxStrcat(sqlStmt, ")");
975
976 pDb->WriteSqlLog(sqlStmt);
977
978 #ifdef DBDEBUG_CONSOLE
979 cout << endl << sqlStmt << endl;
980 #endif
981
982 // Execute the CREATE TABLE statement
983 if (SQLExecDirect(hstmt, (UCHAR FAR *) sqlStmt, SQL_NTS) != SQL_SUCCESS)
984 {
985 pDb->DispAllErrors(henv, hdbc, hstmt);
986 pDb->RollbackTrans();
987 CloseCursor(hstmt);
988 return(FALSE);
989 }
990
991 // Commit the transaction and close the cursor
992 if (! pDb->CommitTrans())
993 return(FALSE);
994 if (! CloseCursor(hstmt))
995 return(FALSE);
996
997 // Database table created successfully
998 return(TRUE);
999
1000 } // wxTable::CreateTable()
1001
1002 /********** wxTable::DropTable() **********/
1003 bool wxTable::DropTable()
1004 {
1005 // NOTE: This function returns TRUE if the Table does not exist, but
1006 // only for identified databases. Code will need to be added
1007 // below for any other databases when those databases are defined
1008 // to handle this situation consistently
1009
1010 char sqlStmt[DB_MAX_STATEMENT_LEN];
1011
1012 sprintf(sqlStmt, "DROP TABLE %s", tableName);
1013
1014 pDb->WriteSqlLog(sqlStmt);
1015
1016 #ifdef DBDEBUG_CONSOLE
1017 cout << endl << sqlStmt << endl;
1018 #endif
1019
1020 if (SQLExecDirect(hstmt, (UCHAR FAR *) sqlStmt, SQL_NTS) != SQL_SUCCESS)
1021 {
1022 // Check for "Base table not found" error and ignore
1023 pDb->GetNextError(henv, hdbc, hstmt);
1024 if (wxStrcmp(pDb->sqlState,"S0002")) // "Base table not found"
1025 {
1026 // Check for product specific error codes
1027 if (!((pDb->Dbms() == dbmsSYBASE_ASA && !wxStrcmp(pDb->sqlState,"42000")) || // 5.x (and lower?)
1028 (pDb->Dbms() == dbmsMY_SQL && !wxStrcmp(pDb->sqlState,"S1000")) || // untested
1029 (pDb->Dbms() == dbmsPOSTGRES && !wxStrcmp(pDb->sqlState,"08S01")))) // untested
1030 {
1031 pDb->DispNextError();
1032 pDb->DispAllErrors(henv, hdbc, hstmt);
1033 pDb->RollbackTrans();
1034 CloseCursor(hstmt);
1035 return(FALSE);
1036 }
1037 }
1038 }
1039
1040 // Commit the transaction and close the cursor
1041 if (! pDb->CommitTrans())
1042 return(FALSE);
1043 if (! CloseCursor(hstmt))
1044 return(FALSE);
1045
1046 return(TRUE);
1047 } // wxTable::DropTable()
1048
1049 /********** wxTable::CreateIndex() **********/
1050 bool wxTable::CreateIndex(const char * idxName, bool unique, int noIdxCols, CidxDef *pIdxDefs, bool attemptDrop)
1051 {
1052 char sqlStmt[DB_MAX_STATEMENT_LEN];
1053
1054 // Drop the index first
1055 if (attemptDrop && !DropIndex(idxName))
1056 return (FALSE);
1057
1058 // Build a CREATE INDEX statement
1059 wxStrcpy(sqlStmt, "CREATE ");
1060 if (unique)
1061 wxStrcat(sqlStmt, "UNIQUE ");
1062
1063 wxStrcat(sqlStmt, "INDEX ");
1064 wxStrcat(sqlStmt, idxName);
1065 wxStrcat(sqlStmt, " ON ");
1066 wxStrcat(sqlStmt, tableName);
1067 wxStrcat(sqlStmt, " (");
1068
1069 // Append list of columns making up index
1070 int i;
1071 for (i = 0; i < noIdxCols; i++)
1072 {
1073 wxStrcat(sqlStmt, pIdxDefs[i].ColName);
1074 /* Postgres doesn't cope with ASC */
1075 if (pDb->Dbms() != dbmsPOSTGRES)
1076 {
1077 if (pIdxDefs[i].Ascending)
1078 wxStrcat(sqlStmt, " ASC");
1079 else
1080 wxStrcat(sqlStmt, " DESC");
1081 }
1082
1083 if ((i + 1) < noIdxCols)
1084 wxStrcat(sqlStmt, ",");
1085 }
1086
1087 // Append closing parentheses
1088 wxStrcat(sqlStmt, ")");
1089
1090 pDb->WriteSqlLog(sqlStmt);
1091
1092 #ifdef DBDEBUG_CONSOLE
1093 cout << endl << sqlStmt << endl << endl;
1094 #endif
1095
1096 // Execute the CREATE INDEX statement
1097 if (SQLExecDirect(hstmt, (UCHAR FAR *) sqlStmt, SQL_NTS) != SQL_SUCCESS)
1098 {
1099 pDb->DispAllErrors(henv, hdbc, hstmt);
1100 pDb->RollbackTrans();
1101 CloseCursor(hstmt);
1102 return(FALSE);
1103 }
1104
1105 // Commit the transaction and close the cursor
1106 if (! pDb->CommitTrans())
1107 return(FALSE);
1108 if (! CloseCursor(hstmt))
1109 return(FALSE);
1110
1111 // Index Created Successfully
1112 return(TRUE);
1113
1114 } // wxTable::CreateIndex()
1115
1116 /********** wxTable::DropIndex() **********/
1117 bool wxTable::DropIndex(const char * idxName)
1118 {
1119 // NOTE: This function returns TRUE if the Index does not exist, but
1120 // only for identified databases. Code will need to be added
1121 // below for any other databases when those databases are defined
1122 // to handle this situation consistently
1123
1124 char sqlStmt[DB_MAX_STATEMENT_LEN];
1125
1126 if (pDb->Dbms() == dbmsACCESS)
1127 sprintf(sqlStmt, "DROP INDEX %s ON %s",idxName,tableName);
1128 else if (pDb->Dbms() == dbmsSYBASE_ASE)
1129 sprintf(sqlStmt, "DROP INDEX %s.%s",tableName,idxName);
1130 else
1131 sprintf(sqlStmt, "DROP INDEX %s",idxName);
1132
1133 pDb->WriteSqlLog(sqlStmt);
1134
1135 #ifdef DBDEBUG_CONSOLE
1136 cout << endl << sqlStmt << endl;
1137 #endif
1138
1139 if (SQLExecDirect(hstmt, (UCHAR FAR *) sqlStmt, SQL_NTS) != SQL_SUCCESS)
1140 {
1141 // Check for "Index not found" error and ignore
1142 pDb->GetNextError(henv, hdbc, hstmt);
1143 if (wxStrcmp(pDb->sqlState,"S0012")) // "Index not found"
1144 {
1145 // Check for product specific error codes
1146 if (!((pDb->Dbms() == dbmsSYBASE_ASA && !wxStrcmp(pDb->sqlState,"42000")) || // v5.x (and lower?)
1147 (pDb->Dbms() == dbmsSYBASE_ASE && !wxStrcmp(pDb->sqlState,"S0002")) || // Base table not found
1148 (pDb->Dbms() == dbmsMY_SQL && !wxStrcmp(pDb->sqlState,"42S02")) // untested
1149 ))
1150 {
1151 pDb->DispNextError();
1152 pDb->DispAllErrors(henv, hdbc, hstmt);
1153 pDb->RollbackTrans();
1154 CloseCursor(hstmt);
1155 return(FALSE);
1156 }
1157 }
1158 }
1159
1160 // Commit the transaction and close the cursor
1161 if (! pDb->CommitTrans())
1162 return(FALSE);
1163 if (! CloseCursor(hstmt))
1164 return(FALSE);
1165
1166 return(TRUE);
1167 } // wxTable::DropIndex()
1168
1169 /********** wxTable::Insert() **********/
1170 int wxTable::Insert(void)
1171 {
1172 assert(!queryOnly);
1173 if (queryOnly)
1174 return(DB_FAILURE);
1175
1176 bindInsertParams();
1177
1178 // Insert the record by executing the already prepared insert statement
1179 RETCODE retcode;
1180 retcode=SQLExecute(hstmtInsert);
1181 if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
1182 {
1183 // Check to see if integrity constraint was violated
1184 pDb->GetNextError(henv, hdbc, hstmtInsert);
1185 if (! wxStrcmp(pDb->sqlState, "23000")) // Integrity constraint violated
1186 return(DB_ERR_INTEGRITY_CONSTRAINT_VIOL);
1187 else
1188 {
1189 pDb->DispNextError();
1190 pDb->DispAllErrors(henv, hdbc, hstmtInsert);
1191 return(DB_FAILURE);
1192 }
1193 }
1194
1195 // Record inserted into the datasource successfully
1196 return(DB_SUCCESS);
1197
1198 } // wxTable::Insert()
1199
1200 /********** wxTable::Update() **********/
1201 bool wxTable::Update(void)
1202 {
1203 assert(!queryOnly);
1204 if (queryOnly)
1205 return(FALSE);
1206
1207 char sqlStmt[DB_MAX_STATEMENT_LEN];
1208
1209 // Build the SQL UPDATE statement
1210 GetUpdateStmt(sqlStmt, DB_UPD_KEYFIELDS);
1211
1212 pDb->WriteSqlLog(sqlStmt);
1213
1214 #ifdef DBDEBUG_CONSOLE
1215 cout << endl << sqlStmt << endl << endl;
1216 #endif
1217
1218 // Execute the SQL UPDATE statement
1219 return(execUpdate(sqlStmt));
1220
1221 } // wxTable::Update()
1222
1223 /********** wxTable::Update(pSqlStmt) **********/
1224 bool wxTable::Update(const char *pSqlStmt)
1225 {
1226 assert(!queryOnly);
1227 if (queryOnly)
1228 return(FALSE);
1229
1230 pDb->WriteSqlLog(pSqlStmt);
1231
1232 return(execUpdate(pSqlStmt));
1233
1234 } // wxTable::Update(pSqlStmt)
1235
1236 /********** wxTable::UpdateWhere() **********/
1237 bool wxTable::UpdateWhere(const char *pWhereClause)
1238 {
1239 assert(!queryOnly);
1240 if (queryOnly)
1241 return(FALSE);
1242
1243 char sqlStmt[DB_MAX_STATEMENT_LEN];
1244
1245 // Build the SQL UPDATE statement
1246 GetUpdateStmt(sqlStmt, DB_UPD_WHERE, pWhereClause);
1247
1248 pDb->WriteSqlLog(sqlStmt);
1249
1250 #ifdef DBDEBUG_CONSOLE
1251 cout << endl << sqlStmt << endl << endl;
1252 #endif
1253
1254 // Execute the SQL UPDATE statement
1255 return(execUpdate(sqlStmt));
1256
1257 } // wxTable::UpdateWhere()
1258
1259 /********** wxTable::Delete() **********/
1260 bool wxTable::Delete(void)
1261 {
1262 assert(!queryOnly);
1263 if (queryOnly)
1264 return(FALSE);
1265
1266 char sqlStmt[DB_MAX_STATEMENT_LEN];
1267
1268 // Build the SQL DELETE statement
1269 GetDeleteStmt(sqlStmt, DB_DEL_KEYFIELDS);
1270
1271 pDb->WriteSqlLog(sqlStmt);
1272
1273 // Execute the SQL DELETE statement
1274 return(execDelete(sqlStmt));
1275
1276 } // wxTable::Delete()
1277
1278 /********** wxTable::DeleteWhere() **********/
1279 bool wxTable::DeleteWhere(const char *pWhereClause)
1280 {
1281 assert(!queryOnly);
1282 if (queryOnly)
1283 return(FALSE);
1284
1285 char sqlStmt[DB_MAX_STATEMENT_LEN];
1286
1287 // Build the SQL DELETE statement
1288 GetDeleteStmt(sqlStmt, DB_DEL_WHERE, pWhereClause);
1289
1290 pDb->WriteSqlLog(sqlStmt);
1291
1292 // Execute the SQL DELETE statement
1293 return(execDelete(sqlStmt));
1294
1295 } // wxTable::DeleteWhere()
1296
1297 /********** wxTable::DeleteMatching() **********/
1298 bool wxTable::DeleteMatching(void)
1299 {
1300 assert(!queryOnly);
1301 if (queryOnly)
1302 return(FALSE);
1303
1304 char sqlStmt[DB_MAX_STATEMENT_LEN];
1305
1306 // Build the SQL DELETE statement
1307 GetDeleteStmt(sqlStmt, DB_DEL_MATCHING);
1308
1309 pDb->WriteSqlLog(sqlStmt);
1310
1311 // Execute the SQL DELETE statement
1312 return(execDelete(sqlStmt));
1313
1314 } // wxTable::DeleteMatching()
1315
1316 /********** wxTable::GetUpdateStmt() **********/
1317 void wxTable::GetUpdateStmt(char *pSqlStmt, int typeOfUpd, const char *pWhereClause)
1318 {
1319 assert(!queryOnly);
1320 if (queryOnly)
1321 return;
1322
1323 char whereClause[DB_MAX_WHERE_CLAUSE_LEN];
1324 bool firstColumn = TRUE;
1325
1326 whereClause[0] = 0;
1327 sprintf(pSqlStmt, "UPDATE %s SET ", tableName);
1328
1329 // Append a list of columns to be updated
1330 int i;
1331 for (i = 0; i < noCols; i++)
1332 {
1333 // Only append Updateable columns
1334 if (colDefs[i].Updateable)
1335 {
1336 if (! firstColumn)
1337 wxStrcat(pSqlStmt, ",");
1338 else
1339 firstColumn = FALSE;
1340 wxStrcat(pSqlStmt, colDefs[i].ColName);
1341 wxStrcat(pSqlStmt, " = ?");
1342 }
1343 }
1344
1345 // Append the WHERE clause to the SQL UPDATE statement
1346 wxStrcat(pSqlStmt, " WHERE ");
1347 switch(typeOfUpd)
1348 {
1349 case DB_UPD_KEYFIELDS:
1350 // If the datasource supports the ROWID column, build
1351 // the where on ROWID for efficiency purposes.
1352 // e.g. UPDATE PARTS SET Col1 = ?, Col2 = ? WHERE ROWID = '111.222.333'
1353 if (CanUpdByROWID())
1354 {
1355 SDWORD cb;
1356 char rowid[ROWID_LEN];
1357
1358 // Get the ROWID value. If not successful retreiving the ROWID,
1359 // simply fall down through the code and build the WHERE clause
1360 // based on the key fields.
1361 if (SQLGetData(hstmt, noCols+1, SQL_C_CHAR, (UCHAR*) rowid, ROWID_LEN, &cb) == SQL_SUCCESS)
1362 {
1363 wxStrcat(pSqlStmt, "ROWID = '");
1364 wxStrcat(pSqlStmt, rowid);
1365 wxStrcat(pSqlStmt, "'");
1366 break;
1367 }
1368 }
1369 // Unable to delete by ROWID, so build a WHERE
1370 // clause based on the keyfields.
1371 GetWhereClause(whereClause, DB_WHERE_KEYFIELDS);
1372 wxStrcat(pSqlStmt, whereClause);
1373 break;
1374 case DB_UPD_WHERE:
1375 wxStrcat(pSqlStmt, pWhereClause);
1376 break;
1377 }
1378 } // GetUpdateStmt()
1379
1380 /********** wxTable::GetDeleteStmt() **********/
1381 void wxTable::GetDeleteStmt(char *pSqlStmt, int typeOfDel, const char *pWhereClause)
1382 {
1383 assert(!queryOnly);
1384 if (queryOnly)
1385 return;
1386
1387 char whereClause[DB_MAX_WHERE_CLAUSE_LEN];
1388
1389 whereClause[0] = 0;
1390
1391 // Handle the case of DeleteWhere() and the where clause is blank. It should
1392 // delete all records from the database in this case.
1393 if (typeOfDel == DB_DEL_WHERE && (pWhereClause == 0 || wxStrlen(pWhereClause) == 0))
1394 {
1395 sprintf(pSqlStmt, "DELETE FROM %s", tableName);
1396 return;
1397 }
1398
1399 sprintf(pSqlStmt, "DELETE FROM %s WHERE ", tableName);
1400
1401 // Append the WHERE clause to the SQL DELETE statement
1402 switch(typeOfDel)
1403 {
1404 case DB_DEL_KEYFIELDS:
1405 // If the datasource supports the ROWID column, build
1406 // the where on ROWID for efficiency purposes.
1407 // e.g. DELETE FROM PARTS WHERE ROWID = '111.222.333'
1408 if (CanUpdByROWID())
1409 {
1410 SDWORD cb;
1411 char rowid[ROWID_LEN];
1412
1413 // Get the ROWID value. If not successful retreiving the ROWID,
1414 // simply fall down through the code and build the WHERE clause
1415 // based on the key fields.
1416 if (SQLGetData(hstmt, noCols+1, SQL_C_CHAR, (UCHAR*) rowid, ROWID_LEN, &cb) == SQL_SUCCESS)
1417 {
1418 wxStrcat(pSqlStmt, "ROWID = '");
1419 wxStrcat(pSqlStmt, rowid);
1420 wxStrcat(pSqlStmt, "'");
1421 break;
1422 }
1423 }
1424 // Unable to delete by ROWID, so build a WHERE
1425 // clause based on the keyfields.
1426 GetWhereClause(whereClause, DB_WHERE_KEYFIELDS);
1427 wxStrcat(pSqlStmt, whereClause);
1428 break;
1429 case DB_DEL_WHERE:
1430 wxStrcat(pSqlStmt, pWhereClause);
1431 break;
1432 case DB_DEL_MATCHING:
1433 GetWhereClause(whereClause, DB_WHERE_MATCHING);
1434 wxStrcat(pSqlStmt, whereClause);
1435 break;
1436 }
1437
1438 } // GetDeleteStmt()
1439
1440 /********** wxTable::GetWhereClause() **********/
1441 /*
1442 * Note: GetWhereClause() currently ignores timestamp columns.
1443 * They are not included as part of the where clause.
1444 */
1445
1446 void wxTable::GetWhereClause(char *pWhereClause, int typeOfWhere, const char *qualTableName)
1447 {
1448 bool moreThanOneColumn = FALSE;
1449 char colValue[255];
1450
1451 // Loop through the columns building a where clause as you go
1452 int i;
1453 for (i = 0; i < noCols; i++)
1454 {
1455 // Determine if this column should be included in the WHERE clause
1456 if ((typeOfWhere == DB_WHERE_KEYFIELDS && colDefs[i].KeyField) ||
1457 (typeOfWhere == DB_WHERE_MATCHING && (! IsColNull(i))))
1458 {
1459 // Skip over timestamp columns
1460 if (colDefs[i].SqlCtype == SQL_C_TIMESTAMP)
1461 continue;
1462 // If there is more than 1 column, join them with the keyword "AND"
1463 if (moreThanOneColumn)
1464 wxStrcat(pWhereClause, " AND ");
1465 else
1466 moreThanOneColumn = TRUE;
1467 // Concatenate where phrase for the column
1468 if (qualTableName && wxStrlen(qualTableName))
1469 {
1470 wxStrcat(pWhereClause, qualTableName);
1471 wxStrcat(pWhereClause, ".");
1472 }
1473 wxStrcat(pWhereClause, colDefs[i].ColName);
1474 wxStrcat(pWhereClause, " = ");
1475 switch(colDefs[i].SqlCtype)
1476 {
1477 case SQL_C_CHAR:
1478 sprintf(colValue, "'%s'", (UCHAR FAR *) colDefs[i].PtrDataObj);
1479 break;
1480 case SQL_C_SSHORT:
1481 sprintf(colValue, "%hi", *((SWORD *) colDefs[i].PtrDataObj));
1482 break;
1483 case SQL_C_USHORT:
1484 sprintf(colValue, "%hu", *((UWORD *) colDefs[i].PtrDataObj));
1485 break;
1486 case SQL_C_SLONG:
1487 sprintf(colValue, "%li", *((SDWORD *) colDefs[i].PtrDataObj));
1488 break;
1489 case SQL_C_ULONG:
1490 sprintf(colValue, "%lu", *((UDWORD *) colDefs[i].PtrDataObj));
1491 break;
1492 case SQL_C_FLOAT:
1493 sprintf(colValue, "%.6f", *((SFLOAT *) colDefs[i].PtrDataObj));
1494 break;
1495 case SQL_C_DOUBLE:
1496 sprintf(colValue, "%.6f", *((SDOUBLE *) colDefs[i].PtrDataObj));
1497 break;
1498 }
1499 wxStrcat(pWhereClause, colValue);
1500 }
1501 }
1502
1503 } // wxTable::GetWhereClause()
1504
1505 /********** wxTable::IsColNull() **********/
1506 bool wxTable::IsColNull(int colNo)
1507 {
1508 switch(colDefs[colNo].SqlCtype)
1509 {
1510 case SQL_C_CHAR:
1511 return(((UCHAR FAR *) colDefs[colNo].PtrDataObj)[0] == 0);
1512 case SQL_C_SSHORT:
1513 return(( *((SWORD *) colDefs[colNo].PtrDataObj)) == 0);
1514 case SQL_C_USHORT:
1515 return(( *((UWORD*) colDefs[colNo].PtrDataObj)) == 0);
1516 case SQL_C_SLONG:
1517 return(( *((SDWORD *) colDefs[colNo].PtrDataObj)) == 0);
1518 case SQL_C_ULONG:
1519 return(( *((UDWORD *) colDefs[colNo].PtrDataObj)) == 0);
1520 case SQL_C_FLOAT:
1521 return(( *((SFLOAT *) colDefs[colNo].PtrDataObj)) == 0);
1522 case SQL_C_DOUBLE:
1523 return((*((SDOUBLE *) colDefs[colNo].PtrDataObj)) == 0);
1524 case SQL_C_TIMESTAMP:
1525 TIMESTAMP_STRUCT *pDt;
1526 pDt = (TIMESTAMP_STRUCT *) colDefs[colNo].PtrDataObj;
1527 if (pDt->year == 0 && pDt->month == 0 && pDt->day == 0)
1528 return(TRUE);
1529 else
1530 return(FALSE);
1531 default:
1532 return(TRUE);
1533 }
1534
1535 } // wxTable::IsColNull()
1536
1537 /********** wxTable::CanSelectForUpdate() **********/
1538 bool wxTable::CanSelectForUpdate(void)
1539 {
1540 if (pDb->Dbms() == dbmsMY_SQL)
1541 return FALSE;
1542
1543 if (pDb->dbInf.posStmts & SQL_PS_SELECT_FOR_UPDATE)
1544 return(TRUE);
1545 else
1546 return(FALSE);
1547
1548 } // wxTable::CanSelectForUpdate()
1549
1550 /********** wxTable::CanUpdByROWID() **********/
1551 bool wxTable::CanUpdByROWID(void)
1552 {
1553
1554 //NOTE: Returning FALSE for now until this can be debugged,
1555 // as the ROWID is not getting updated correctly
1556 return FALSE;
1557
1558 if (pDb->Dbms() == dbmsORACLE)
1559 return(TRUE);
1560 else
1561 return(FALSE);
1562
1563 } // wxTable::CanUpdByROWID()
1564
1565 /********** wxTable::IsCursorClosedOnCommit() **********/
1566 bool wxTable::IsCursorClosedOnCommit(void)
1567 {
1568 if (pDb->dbInf.cursorCommitBehavior == SQL_CB_PRESERVE)
1569 return(FALSE);
1570 else
1571 return(TRUE);
1572
1573 } // wxTable::IsCursorClosedOnCommit()
1574
1575 /********** wxTable::ClearMemberVars() **********/
1576 void wxTable::ClearMemberVars(void)
1577 {
1578 // Loop through the columns setting each member variable to zero
1579 int i;
1580 for (i = 0; i < noCols; i++)
1581 {
1582 switch(colDefs[i].SqlCtype)
1583 {
1584 case SQL_C_CHAR:
1585 ((UCHAR FAR *) colDefs[i].PtrDataObj)[0] = 0;
1586 break;
1587 case SQL_C_SSHORT:
1588 *((SWORD *) colDefs[i].PtrDataObj) = 0;
1589 break;
1590 case SQL_C_USHORT:
1591 *((UWORD*) colDefs[i].PtrDataObj) = 0;
1592 break;
1593 case SQL_C_SLONG:
1594 *((SDWORD *) colDefs[i].PtrDataObj) = 0;
1595 break;
1596 case SQL_C_ULONG:
1597 *((UDWORD *) colDefs[i].PtrDataObj) = 0;
1598 break;
1599 case SQL_C_FLOAT:
1600 *((SFLOAT *) colDefs[i].PtrDataObj) = 0.0f;
1601 break;
1602 case SQL_C_DOUBLE:
1603 *((SDOUBLE *) colDefs[i].PtrDataObj) = 0.0f;
1604 break;
1605 case SQL_C_TIMESTAMP:
1606 TIMESTAMP_STRUCT *pDt;
1607 pDt = (TIMESTAMP_STRUCT *) colDefs[i].PtrDataObj;
1608 pDt->year = 0;
1609 pDt->month = 0;
1610 pDt->day = 0;
1611 pDt->hour = 0;
1612 pDt->minute = 0;
1613 pDt->second = 0;
1614 pDt->fraction = 0;
1615 break;
1616 }
1617 }
1618
1619 } // wxTable::ClearMemberVars()
1620
1621 /********** wxTable::SetQueryTimeout() **********/
1622 bool wxTable::SetQueryTimeout(UDWORD nSeconds)
1623 {
1624 if (SQLSetStmtOption(hstmtInsert, SQL_QUERY_TIMEOUT, nSeconds) != SQL_SUCCESS)
1625 return(pDb->DispAllErrors(henv, hdbc, hstmtInsert));
1626 if (SQLSetStmtOption(hstmtUpdate, SQL_QUERY_TIMEOUT, nSeconds) != SQL_SUCCESS)
1627 return(pDb->DispAllErrors(henv, hdbc, hstmtUpdate));
1628 if (SQLSetStmtOption(hstmtDelete, SQL_QUERY_TIMEOUT, nSeconds) != SQL_SUCCESS)
1629 return(pDb->DispAllErrors(henv, hdbc, hstmtDelete));
1630 if (SQLSetStmtOption(hstmtInternal, SQL_QUERY_TIMEOUT, nSeconds) != SQL_SUCCESS)
1631 return(pDb->DispAllErrors(henv, hdbc, hstmtInternal));
1632
1633 // Completed Successfully
1634 return(TRUE);
1635
1636 } // wxTable::SetQueryTimeout()
1637
1638 /********** wxTable::SetColDefs() **********/
1639 void wxTable::SetColDefs (int index, const char *fieldName, int dataType, void *pData,
1640 int cType, int size, bool keyField, bool upd,
1641 bool insAllow, bool derivedCol)
1642 {
1643 if (!colDefs) // May happen if the database connection fails
1644 return;
1645
1646 if (wxStrlen(fieldName) > (unsigned int) DB_MAX_COLUMN_NAME_LEN)
1647 {
1648 wxStrncpy (colDefs[index].ColName, fieldName, DB_MAX_COLUMN_NAME_LEN);
1649 colDefs[index].ColName[DB_MAX_COLUMN_NAME_LEN] = 0;
1650 }
1651 else
1652 wxStrcpy(colDefs[index].ColName, fieldName);
1653
1654 colDefs[index].DbDataType = dataType;
1655 colDefs[index].PtrDataObj = pData;
1656 colDefs[index].SqlCtype = cType;
1657 colDefs[index].SzDataObj = size;
1658 colDefs[index].KeyField = keyField;
1659 colDefs[index].DerivedCol = derivedCol;
1660 // Derived columns by definition would NOT be "Insertable" or "Updateable"
1661 if (derivedCol)
1662 {
1663 colDefs[index].Updateable = FALSE;
1664 colDefs[index].InsertAllowed = FALSE;
1665 }
1666 else
1667 {
1668 colDefs[index].Updateable = upd;
1669 colDefs[index].InsertAllowed = insAllow;
1670 }
1671
1672 colDefs[index].Null = FALSE;
1673
1674 } // wxTable::SetColDefs()
1675
1676 /********** wxTable::SetCursor() **********/
1677 void wxTable::SetCursor(HSTMT *hstmtActivate)
1678 {
1679 if (hstmtActivate == DEFAULT_CURSOR)
1680 hstmt = *hstmtDefault;
1681 else
1682 hstmt = *hstmtActivate;
1683
1684 } // wxTable::SetCursor()
1685
1686 /********** wxTable::Count(const char *) **********/
1687 ULONG wxTable::Count(const char *args)
1688 {
1689 ULONG l;
1690 char sqlStmt[DB_MAX_STATEMENT_LEN];
1691 SDWORD cb;
1692
1693 // Build a "SELECT COUNT(*) FROM queryTableName [WHERE whereClause]" SQL Statement
1694 wxStrcpy(sqlStmt, "SELECT COUNT(");
1695 wxStrcat(sqlStmt, args);
1696 wxStrcat(sqlStmt, ") FROM ");
1697 wxStrcat(sqlStmt, queryTableName);
1698
1699 if (from && wxStrlen(from))
1700 wxStrcat(sqlStmt, from);
1701
1702 // Add the where clause if one is provided
1703 if (where && wxStrlen(where))
1704 {
1705 wxStrcat(sqlStmt, " WHERE ");
1706 wxStrcat(sqlStmt, where);
1707 }
1708
1709 pDb->WriteSqlLog(sqlStmt);
1710
1711 // Initialize the Count cursor if it's not already initialized
1712 if (!hstmtCount)
1713 {
1714 hstmtCount = NewCursor(FALSE,FALSE);
1715 assert(hstmtCount);
1716 if (!hstmtCount)
1717 return(0);
1718 }
1719
1720 // Execute the SQL statement
1721 if (SQLExecDirect(*hstmtCount, (UCHAR FAR *) sqlStmt, SQL_NTS) != SQL_SUCCESS)
1722 {
1723 pDb->DispAllErrors(henv, hdbc, *hstmtCount);
1724 return(0);
1725 }
1726
1727 // Fetch the record
1728 if (SQLFetch(*hstmtCount) != SQL_SUCCESS)
1729 {
1730 pDb->DispAllErrors(henv, hdbc, *hstmtCount);
1731 return(0);
1732 }
1733
1734 // Obtain the result
1735 if (SQLGetData(*hstmtCount, 1, SQL_C_ULONG, &l, sizeof(l), &cb) != SQL_SUCCESS)
1736 {
1737 pDb->DispAllErrors(henv, hdbc, *hstmtCount);
1738 return(0);
1739 }
1740
1741 // Free the cursor
1742 if (SQLFreeStmt(*hstmtCount, SQL_CLOSE) != SQL_SUCCESS)
1743 pDb->DispAllErrors(henv, hdbc, *hstmtCount);
1744
1745 // Return the record count
1746 return(l);
1747
1748 } // wxTable::Count()
1749
1750 /********** wxTable::Refresh() **********/
1751 bool wxTable::Refresh(void)
1752 {
1753 bool result = TRUE;
1754
1755 // Switch to the internal cursor so any active cursors are not corrupted
1756 HSTMT currCursor = GetCursor();
1757 hstmt = hstmtInternal;
1758
1759 // Save the where and order by clauses
1760 char *saveWhere = where;
1761 char *saveOrderBy = orderBy;
1762
1763 // Build a where clause to refetch the record with. Try and use the
1764 // ROWID if it's available, ow use the key fields.
1765 char whereClause[DB_MAX_WHERE_CLAUSE_LEN+1];
1766 wxStrcpy(whereClause, "");
1767 if (CanUpdByROWID())
1768 {
1769 SDWORD cb;
1770 char rowid[ROWID_LEN+1];
1771
1772 // Get the ROWID value. If not successful retreiving the ROWID,
1773 // simply fall down through the code and build the WHERE clause
1774 // based on the key fields.
1775 if (SQLGetData(hstmt, noCols+1, SQL_C_CHAR, (UCHAR*) rowid, ROWID_LEN, &cb) == SQL_SUCCESS)
1776 {
1777 wxStrcat(whereClause, queryTableName);
1778 wxStrcat(whereClause, ".ROWID = '");
1779 wxStrcat(whereClause, rowid);
1780 wxStrcat(whereClause, "'");
1781 }
1782 }
1783
1784 // If unable to use the ROWID, build a where clause from the keyfields
1785 if (wxStrlen(whereClause) == 0)
1786 GetWhereClause(whereClause, DB_WHERE_KEYFIELDS, queryTableName);
1787
1788 // Requery the record
1789 where = whereClause;
1790 orderBy = 0;
1791 if (!Query())
1792 result = FALSE;
1793
1794 if (result && !GetNext())
1795 result = FALSE;
1796
1797 // Switch back to original cursor
1798 SetCursor(&currCursor);
1799
1800 // Free the internal cursor
1801 if (SQLFreeStmt(hstmtInternal, SQL_CLOSE) != SQL_SUCCESS)
1802 pDb->DispAllErrors(henv, hdbc, hstmtInternal);
1803
1804 // Restore the original where and order by clauses
1805 where = saveWhere;
1806 orderBy = saveOrderBy;
1807
1808 return(result);
1809
1810 } // wxTable::Refresh()
1811
1812 /********** wxTable::SetNull(UINT colNo) **********/
1813 bool wxTable::SetNull(int colNo)
1814 {
1815 if (colNo < noCols)
1816 return(colDefs[colNo].Null = TRUE);
1817 else
1818 return(FALSE);
1819
1820 } // wxTable::SetNull(UINT colNo)
1821
1822 /********** wxTable::SetNull(char *colName) **********/
1823 bool wxTable::SetNull(const char *colName)
1824 {
1825 int i;
1826 for (i = 0; i < noCols; i++)
1827 {
1828 if (!wxStricmp(colName, colDefs[i].ColName))
1829 break;
1830 }
1831
1832 if (i < noCols)
1833 return(colDefs[i].Null = TRUE);
1834 else
1835 return(FALSE);
1836
1837 } // wxTable::SetNull(char *colName)
1838
1839 /********** wxTable::NewCursor() **********/
1840 HSTMT *wxTable::NewCursor(bool setCursor, bool bindColumns)
1841 {
1842 HSTMT *newHSTMT = new HSTMT;
1843 assert(newHSTMT);
1844 if (!newHSTMT)
1845 return(0);
1846
1847 if (SQLAllocStmt(hdbc, newHSTMT) != SQL_SUCCESS)
1848 {
1849 pDb->DispAllErrors(henv, hdbc);
1850 delete newHSTMT;
1851 return(0);
1852 }
1853
1854 if (SQLSetStmtOption(*newHSTMT, SQL_CURSOR_TYPE, cursorType) != SQL_SUCCESS)
1855 {
1856 pDb->DispAllErrors(henv, hdbc, *newHSTMT);
1857 delete newHSTMT;
1858 return(0);
1859 }
1860
1861 if (bindColumns)
1862 {
1863 if(!bindCols(*newHSTMT))
1864 {
1865 delete newHSTMT;
1866 return(0);
1867 }
1868 }
1869
1870 if (setCursor)
1871 SetCursor(newHSTMT);
1872
1873 return(newHSTMT);
1874
1875 } // wxTable::NewCursor()
1876
1877 /********** wxTable::DeleteCursor() **********/
1878 bool wxTable::DeleteCursor(HSTMT *hstmtDel)
1879 {
1880 bool result = TRUE;
1881
1882 if (!hstmtDel) // Cursor already deleted
1883 return(result);
1884
1885 if (SQLFreeStmt(*hstmtDel, SQL_DROP) != SQL_SUCCESS)
1886 {
1887 pDb->DispAllErrors(henv, hdbc);
1888 result = FALSE;
1889 }
1890
1891 delete hstmtDel;
1892
1893 return(result);
1894
1895 } // wxTable::DeleteCursor()
1896
1897 #endif // wxUSE_ODBC
1898
wxWidgets (Impactor)