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