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