]> git.saurik.com Git - wxWidgets.git/blob - src/common/dbtable.cpp
projects / wxWidgets.git / blob
? search:


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