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