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