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