]> 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
ODBC updates
[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;
512 UDWORD precision;
513 SWORD scale;
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;
567 UDWORD precision;
568 SWORD scale;
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 pDb->GetNextError(henv, hdbc, hstmt);
673 if (strcmp(pDb->sqlState, "S0002") &&
674 strcmp(pDb->sqlState, "42000") &&
675 strcmp(pDb->sqlState, "08S01"))
676 {
677 pDb->DispNextError();
678 pDb->DispAllErrors(henv, hdbc, hstmt);
679 pDb->RollbackTrans();
680 CloseCursor(hstmt);
681 return(FALSE);
682 }
683 }
684
685 pDb->WriteSqlLog(sqlStmt);
686
687 // Commit the transaction and close the cursor
688 if (! pDb->CommitTrans())
689 return(FALSE);
690 if (! CloseCursor(hstmt))
691 return(FALSE);
692
693 // Create the table
694 #ifdef _CONSOLE
695 for (i = 0; i < noCols; i++)
696 {
697 // Exclude derived columns since they are NOT part of the base table
698 if (colDefs[i].DerivedCol)
699 continue;
700 cout << i + 1 << ": " << colDefs[i].ColName << "; ";
701 switch(colDefs[i].DbDataType)
702 {
703 case DB_DATA_TYPE_VARCHAR:
704 cout << pDb->typeInfVarchar.TypeName << "(" << colDefs[i].SzDataObj << ")";
705 break;
706 case DB_DATA_TYPE_INTEGER:
707 cout << pDb->typeInfInteger.TypeName;
708 break;
709 case DB_DATA_TYPE_FLOAT:
710 cout << pDb->typeInfFloat.TypeName;
711 break;
712 case DB_DATA_TYPE_DATE:
713 cout << pDb->typeInfDate.TypeName;
714 break;
715 }
716 cout << endl;
717 }
718 #endif
719
720 // Build a CREATE TABLE string from the colDefs structure.
721 bool needComma = FALSE;
722 sprintf(sqlStmt, "CREATE TABLE %s (", tableName);
723 for (i = 0; i < noCols; i++)
724 {
725 // Exclude derived columns since they are NOT part of the base table
726 if (colDefs[i].DerivedCol)
727 continue;
728 // Comma Delimiter
729 if (needComma)
730 strcat(sqlStmt, ",");
731 // Column Name
732 strcat(sqlStmt, colDefs[i].ColName);
733 strcat(sqlStmt, " ");
734 // Column Type
735 switch(colDefs[i].DbDataType)
736 {
737 case DB_DATA_TYPE_VARCHAR:
738 strcat(sqlStmt, pDb->typeInfVarchar.TypeName); break;
739 case DB_DATA_TYPE_INTEGER:
740 strcat(sqlStmt, pDb->typeInfInteger.TypeName); break;
741 case DB_DATA_TYPE_FLOAT:
742 strcat(sqlStmt, pDb->typeInfFloat.TypeName); break;
743 case DB_DATA_TYPE_DATE:
744 strcat(sqlStmt, pDb->typeInfDate.TypeName); break;
745 }
746 // For varchars, append the size of the string
747 if (colDefs[i].DbDataType == DB_DATA_TYPE_VARCHAR)
748 {
749 char s[10];
750 // strcat(sqlStmt, "(");
751 // strcat(sqlStmt, itoa(colDefs[i].SzDataObj, s, 10));
752 // strcat(sqlStmt, ")");
753 sprintf(s, "(%d)", colDefs[i].SzDataObj);
754 strcat(sqlStmt, s);
755 }
756 needComma = TRUE;
757 }
758 // If there is a primary key defined, include it in the create statement
759 for (i = j = 0; i < noCols; i++)
760 {
761 if (colDefs[i].KeyField)
762 {
763 j++;
764 break;
765 }
766 }
767 if (j) // Found a keyfield
768 {
769 strcat(sqlStmt, ",CONSTRAINT ");
770 strcat(sqlStmt, tableName);
771 strcat(sqlStmt, "_PIDX PRIMARY KEY (");
772 // List column name(s) of column(s) comprising the primary key
773 for (i = j = 0; i < noCols; i++)
774 {
775 if (colDefs[i].KeyField)
776 {
777 if (j++) // Multi part key, comma separate names
778 strcat(sqlStmt, ",");
779 strcat(sqlStmt, colDefs[i].ColName);
780 }
781 }
782 strcat(sqlStmt, ")");
783 }
784 // Append the closing parentheses for the create table statement
785 strcat(sqlStmt, ")");
786
787 pDb->WriteSqlLog(sqlStmt);
788
789 #ifdef _CONSOLE
790 cout << endl << sqlStmt << endl;
791 #endif
792
793 // Execute the CREATE TABLE statement
794 if (SQLExecDirect(hstmt, (UCHAR FAR *) sqlStmt, SQL_NTS) != SQL_SUCCESS)
795 {
796 pDb->DispAllErrors(henv, hdbc, hstmt);
797 pDb->RollbackTrans();
798 CloseCursor(hstmt);
799 return(FALSE);
800 }
801
802 // Commit the transaction and close the cursor
803 if (! pDb->CommitTrans())
804 return(FALSE);
805 if (! CloseCursor(hstmt))
806 return(FALSE);
807
808 // Database table created successfully
809 return(TRUE);
810
811 } // wxTable::CreateTable()
812
813 /********** wxTable::CreateIndex() **********/
814 bool wxTable::CreateIndex(char * idxName, bool unique, int noIdxCols, CidxDef *pIdxDefs)
815 {
816 char sqlStmt[DB_MAX_STATEMENT_LEN];
817
818 // Build a CREATE INDEX statement
819 strcpy(sqlStmt, "CREATE ");
820 if (unique)
821 strcat(sqlStmt, "UNIQUE ");
822
823 strcat(sqlStmt, "INDEX ");
824 strcat(sqlStmt, idxName);
825 strcat(sqlStmt, " ON ");
826 strcat(sqlStmt, tableName);
827 strcat(sqlStmt, " (");
828
829 // Append list of columns making up index
830 for (int i = 0; i < noIdxCols; i++)
831 {
832 strcat(sqlStmt, pIdxDefs[i].ColName);
833 if (pIdxDefs[i].Ascending)
834 strcat(sqlStmt, " ASC");
835 else
836 strcat(sqlStmt, " DESC");
837 if ((i + 1) < noIdxCols)
838 strcat(sqlStmt, ", ");
839 }
840
841 // Append closing parentheses
842 strcat(sqlStmt, ")");
843
844 pDb->WriteSqlLog(sqlStmt);
845
846 #ifdef _CONSOLE
847 cout << endl << sqlStmt << endl << endl;
848 #endif
849
850 // Execute the CREATE INDEX statement
851 if (SQLExecDirect(hstmt, (UCHAR FAR *) sqlStmt, SQL_NTS) != SQL_SUCCESS)
852 {
853 pDb->DispAllErrors(henv, hdbc, hstmt);
854 pDb->RollbackTrans();
855 CloseCursor(hstmt);
856 return(FALSE);
857 }
858
859 // Commit the transaction and close the cursor
860 if (! pDb->CommitTrans())
861 return(FALSE);
862 if (! CloseCursor(hstmt))
863 return(FALSE);
864
865 // Index Created Successfully
866 return(TRUE);
867
868 } // wxTable::CreateIndex()
869
870 /********** wxTable::Insert() **********/
871 int wxTable::Insert(void)
872 {
873 // Insert the record by executing the already prepared insert statement
874 if (SQLExecute(hstmtInsert) != SQL_SUCCESS)
875 {
876 // Check to see if integrity constraint was violated
877 pDb->GetNextError(henv, hdbc, hstmtInsert);
878 if (! strcmp(pDb->sqlState, "23000")) // Integrity constraint violated
879 return(DB_ERR_INTEGRITY_CONSTRAINT_VIOL);
880 else
881 {
882 pDb->DispNextError();
883 pDb->DispAllErrors(henv, hdbc, hstmtInsert);
884 return(DB_FAILURE);
885 }
886 }
887
888 // Record inserted into the datasource successfully
889 return(DB_SUCCESS);
890
891 } // wxTable::Insert()
892
893 /********** wxTable::Update(pSqlStmt) **********/
894 bool wxTable::Update(char *pSqlStmt)
895 {
896 pDb->WriteSqlLog(pSqlStmt);
897
898 return(execUpdate(pSqlStmt));
899
900 } // wxTable::Update(pSqlStmt)
901
902 /********** wxTable::Update() **********/
903 bool wxTable::Update(void)
904 {
905 char sqlStmt[DB_MAX_STATEMENT_LEN];
906
907 // Build the SQL UPDATE statement
908 GetUpdateStmt(sqlStmt, DB_UPD_KEYFIELDS);
909
910 pDb->WriteSqlLog(sqlStmt);
911
912 #ifdef _CONSOLE
913 cout << endl << sqlStmt << endl << endl;
914 #endif
915
916 // Execute the SQL UPDATE statement
917 return(execUpdate(sqlStmt));
918
919 } // wxTable::Update()
920
921 /********** wxTable::UpdateWhere() **********/
922 bool wxTable::UpdateWhere(char *pWhereClause)
923 {
924 char sqlStmt[DB_MAX_STATEMENT_LEN];
925
926 // Build the SQL UPDATE statement
927 GetUpdateStmt(sqlStmt, DB_UPD_WHERE, pWhereClause);
928
929 pDb->WriteSqlLog(sqlStmt);
930
931 #ifdef _CONSOLE
932 cout << endl << sqlStmt << endl << endl;
933 #endif
934
935 // Execute the SQL UPDATE statement
936 return(execUpdate(sqlStmt));
937
938 } // wxTable::UpdateWhere()
939
940 /********** wxTable::Delete() **********/
941 bool wxTable::Delete(void)
942 {
943 char sqlStmt[DB_MAX_STATEMENT_LEN];
944
945 // Build the SQL DELETE statement
946 GetDeleteStmt(sqlStmt, DB_DEL_KEYFIELDS);
947
948 pDb->WriteSqlLog(sqlStmt);
949
950 // Execute the SQL DELETE statement
951 return(execDelete(sqlStmt));
952
953 } // wxTable::Delete()
954
955 /********** wxTable::DeleteWhere() **********/
956 bool wxTable::DeleteWhere(char *pWhereClause)
957 {
958 char sqlStmt[DB_MAX_STATEMENT_LEN];
959
960 // Build the SQL DELETE statement
961 GetDeleteStmt(sqlStmt, DB_DEL_WHERE, pWhereClause);
962
963 pDb->WriteSqlLog(sqlStmt);
964
965 // Execute the SQL DELETE statement
966 return(execDelete(sqlStmt));
967
968 } // wxTable::DeleteWhere()
969
970 /********** wxTable::DeleteMatching() **********/
971 bool wxTable::DeleteMatching(void)
972 {
973 char sqlStmt[DB_MAX_STATEMENT_LEN];
974
975 // Build the SQL DELETE statement
976 GetDeleteStmt(sqlStmt, DB_DEL_MATCHING);
977
978 pDb->WriteSqlLog(sqlStmt);
979
980 // Execute the SQL DELETE statement
981 return(execDelete(sqlStmt));
982
983 } // wxTable::DeleteMatching()
984
985 /********** wxTable::execDelete() **********/
986 bool wxTable::execDelete(char *pSqlStmt)
987 {
988 // Execute the DELETE statement
989 if (SQLExecDirect(hstmtDelete, (UCHAR FAR *) pSqlStmt, SQL_NTS) != SQL_SUCCESS)
990 return(pDb->DispAllErrors(henv, hdbc, hstmtDelete));
991
992 // Record deleted successfully
993 return(TRUE);
994
995 } // wxTable::execDelete()
996
997 /********** wxTable::execUpdate() **********/
998 bool wxTable::execUpdate(char *pSqlStmt)
999 {
1000 // Execute the UPDATE statement
1001 if (SQLExecDirect(hstmtUpdate, (UCHAR FAR *) pSqlStmt, SQL_NTS) != SQL_SUCCESS)
1002 return(pDb->DispAllErrors(henv, hdbc, hstmtUpdate));
1003
1004 // Record deleted successfully
1005 return(TRUE);
1006
1007 } // wxTable::execUpdate()
1008
1009 /********** wxTable::GetUpdateStmt() **********/
1010 void wxTable::GetUpdateStmt(char *pSqlStmt, int typeOfUpd, char *pWhereClause)
1011 {
1012 char whereClause[DB_MAX_WHERE_CLAUSE_LEN];
1013 bool firstColumn = TRUE;
1014
1015 whereClause[0] = 0;
1016 sprintf(pSqlStmt, "UPDATE %s SET ", tableName);
1017
1018 // Append a list of columns to be updated
1019 for (int i = 0; i < noCols; i++)
1020 {
1021 // Only append Updateable columns
1022 if (colDefs[i].Updateable)
1023 {
1024 if (! firstColumn)
1025 strcat(pSqlStmt, ",");
1026 else
1027 firstColumn = FALSE;
1028 strcat(pSqlStmt, colDefs[i].ColName);
1029 strcat(pSqlStmt, " = ?");
1030 }
1031 }
1032
1033 // Append the WHERE clause to the SQL UPDATE statement
1034 strcat(pSqlStmt, " WHERE ");
1035 switch(typeOfUpd)
1036 {
1037 case DB_UPD_KEYFIELDS:
1038 // If the datasource supports the ROWID column, build
1039 // the where on ROWID for efficiency purposes.
1040 // e.g. UPDATE PARTS SET C1 = ?, C2 = ? WHERE ROWID = '111.222.333'
1041 if (CanUpdByROWID())
1042 {
1043 SDWORD cb;
1044 char rowid[ROWID_LEN];
1045
1046 // Get the ROWID value. If not successful retreiving the ROWID,
1047 // simply fall down through the code and build the WHERE clause
1048 // based on the key fields.
1049 if (SQLGetData(hstmt, noCols+1, SQL_C_CHAR, (UCHAR*) rowid, ROWID_LEN, &cb) == SQL_SUCCESS)
1050 {
1051 strcat(pSqlStmt, "ROWID = '");
1052 strcat(pSqlStmt, rowid);
1053 strcat(pSqlStmt, "'");
1054 break;
1055 }
1056 }
1057 // Unable to delete by ROWID, so build a WHERE
1058 // clause based on the keyfields.
1059 GetWhereClause(whereClause, DB_WHERE_KEYFIELDS);
1060 strcat(pSqlStmt, whereClause);
1061 break;
1062 case DB_UPD_WHERE:
1063 strcat(pSqlStmt, pWhereClause);
1064 break;
1065 }
1066
1067 } // GetUpdateStmt()
1068
1069 /********** wxTable::GetDeleteStmt() **********/
1070 void wxTable::GetDeleteStmt(char *pSqlStmt, int typeOfDel, char *pWhereClause)
1071 {
1072 char whereClause[DB_MAX_WHERE_CLAUSE_LEN];
1073
1074 whereClause[0] = 0;
1075
1076 // Handle the case of DeleteWhere() and the where clause is blank. It should
1077 // delete all records from the database in this case.
1078 if (typeOfDel == DB_DEL_WHERE && (pWhereClause == 0 || strlen(pWhereClause) == 0))
1079 {
1080 sprintf(pSqlStmt, "DELETE FROM %s", tableName);
1081 return;
1082 }
1083
1084 sprintf(pSqlStmt, "DELETE FROM %s WHERE ", tableName);
1085
1086 // Append the WHERE clause to the SQL DELETE statement
1087 switch(typeOfDel)
1088 {
1089 case DB_DEL_KEYFIELDS:
1090 // If the datasource supports the ROWID column, build
1091 // the where on ROWID for efficiency purposes.
1092 // e.g. DELETE FROM PARTS WHERE ROWID = '111.222.333'
1093 if (CanUpdByROWID())
1094 {
1095 SDWORD cb;
1096 char rowid[ROWID_LEN];
1097
1098 // Get the ROWID value. If not successful retreiving the ROWID,
1099 // simply fall down through the code and build the WHERE clause
1100 // based on the key fields.
1101 if (SQLGetData(hstmt, noCols+1, SQL_C_CHAR, (UCHAR*) rowid, ROWID_LEN, &cb) == SQL_SUCCESS)
1102 {
1103 strcat(pSqlStmt, "ROWID = '");
1104 strcat(pSqlStmt, rowid);
1105 strcat(pSqlStmt, "'");
1106 break;
1107 }
1108 }
1109 // Unable to delete by ROWID, so build a WHERE
1110 // clause based on the keyfields.
1111 GetWhereClause(whereClause, DB_WHERE_KEYFIELDS);
1112 strcat(pSqlStmt, whereClause);
1113 break;
1114 case DB_DEL_WHERE:
1115 strcat(pSqlStmt, pWhereClause);
1116 break;
1117 case DB_DEL_MATCHING:
1118 GetWhereClause(whereClause, DB_WHERE_MATCHING);
1119 strcat(pSqlStmt, whereClause);
1120 break;
1121 }
1122
1123 } // GetDeleteStmt()
1124
1125 /********** wxTable::GetWhereClause() **********/
1126 /*
1127 * Note: GetWhereClause() currently ignores timestamp columns.
1128 * They are not included as part of the where clause.
1129 */
1130
1131 void wxTable::GetWhereClause(char *pWhereClause, int typeOfWhere, char *qualTableName)
1132 {
1133 bool moreThanOneColumn = FALSE;
1134 char colValue[255];
1135
1136 // Loop through the columns building a where clause as you go
1137 for (int i = 0; i < noCols; i++)
1138 {
1139 // Determine if this column should be included in the WHERE clause
1140 if ((typeOfWhere == DB_WHERE_KEYFIELDS && colDefs[i].KeyField) ||
1141 (typeOfWhere == DB_WHERE_MATCHING && (! IsColNull(i))))
1142 {
1143 // Skip over timestamp columns
1144 if (colDefs[i].SqlCtype == SQL_C_TIMESTAMP)
1145 continue;
1146 // If there is more than 1 column, join them with the keyword "AND"
1147 if (moreThanOneColumn)
1148 strcat(pWhereClause, " AND ");
1149 else
1150 moreThanOneColumn = TRUE;
1151 // Concatenate where phrase for the column
1152 if (qualTableName && strlen(qualTableName))
1153 {
1154 strcat(pWhereClause, qualTableName);
1155 strcat(pWhereClause, ".");
1156 }
1157 strcat(pWhereClause, colDefs[i].ColName);
1158 strcat(pWhereClause, " = ");
1159 switch(colDefs[i].SqlCtype)
1160 {
1161 case SQL_C_CHAR:
1162 sprintf(colValue, "'%s'", (UCHAR FAR *) colDefs[i].PtrDataObj);
1163 break;
1164 case SQL_C_SSHORT:
1165 sprintf(colValue, "%hi", *((SWORD *) colDefs[i].PtrDataObj));
1166 break;
1167 case SQL_C_USHORT:
1168 sprintf(colValue, "%hu", *((UWORD *) colDefs[i].PtrDataObj));
1169 break;
1170 case SQL_C_SLONG:
1171 sprintf(colValue, "%li", *((SDWORD *) colDefs[i].PtrDataObj));
1172 break;
1173 case SQL_C_ULONG:
1174 sprintf(colValue, "%lu", *((UDWORD *) colDefs[i].PtrDataObj));
1175 break;
1176 case SQL_C_FLOAT:
1177 sprintf(colValue, "%.6f", *((SFLOAT *) colDefs[i].PtrDataObj));
1178 break;
1179 case SQL_C_DOUBLE:
1180 sprintf(colValue, "%.6f", *((SDOUBLE *) colDefs[i].PtrDataObj));
1181 break;
1182 }
1183 strcat(pWhereClause, colValue);
1184 }
1185 }
1186
1187 } // wxTable::GetWhereClause()
1188
1189 /********** wxTable::IsColNull() **********/
1190 bool wxTable::IsColNull(int colNo)
1191 {
1192 switch(colDefs[colNo].SqlCtype)
1193 {
1194 case SQL_C_CHAR:
1195 return(((UCHAR FAR *) colDefs[colNo].PtrDataObj)[0] == 0);
1196 case SQL_C_SSHORT:
1197 return(( *((SWORD *) colDefs[colNo].PtrDataObj)) == 0);
1198 case SQL_C_USHORT:
1199 return(( *((UWORD*) colDefs[colNo].PtrDataObj)) == 0);
1200 case SQL_C_SLONG:
1201 return(( *((SDWORD *) colDefs[colNo].PtrDataObj)) == 0);
1202 case SQL_C_ULONG:
1203 return(( *((UDWORD *) colDefs[colNo].PtrDataObj)) == 0);
1204 case SQL_C_FLOAT:
1205 return(( *((SFLOAT *) colDefs[colNo].PtrDataObj)) == 0);
1206 case SQL_C_DOUBLE:
1207 return((*((SDOUBLE *) colDefs[colNo].PtrDataObj)) == 0);
1208 case SQL_C_TIMESTAMP:
1209 TIMESTAMP_STRUCT *pDt;
1210 pDt = (TIMESTAMP_STRUCT *) colDefs[colNo].PtrDataObj;
1211 if (pDt->year == 0 && pDt->month == 0 && pDt->day == 0)
1212 return(TRUE);
1213 else
1214 return(FALSE);
1215 default:
1216 return(TRUE);
1217 }
1218
1219 } // wxTable::IsColNull()
1220
1221 /********** wxTable::CanSelectForUpdate() **********/
1222
1223 bool wxTable::CanSelectForUpdate(void)
1224 {
1225 #ifndef __WXGTK__
1226 if (pDb->dbInf.posStmts & SQL_PS_SELECT_FOR_UPDATE)
1227 return(TRUE);
1228 else
1229 #endif
1230 return(FALSE);
1231
1232 } // wxTable::CanSelectForUpdate()
1233
1234 /********** wxTable::CanUpdByROWID() **********/
1235 bool wxTable::CanUpdByROWID(void)
1236 {
1237
1238 //NOTE: Returning FALSE for now until this can be debugged,
1239 // as the ROWID is not getting updated correctly
1240 return FALSE;
1241
1242 if ((! strcmp(pDb->dbInf.dbmsName, "Oracle")) || (! strcmp(pDb->dbInf.dbmsName, "ORACLE")))
1243 return(TRUE);
1244 else
1245 return(FALSE);
1246
1247 } // wxTable::CanUpdByROWID()
1248
1249 /********** wxTable::IsCursorClosedOnCommit() **********/
1250 bool wxTable::IsCursorClosedOnCommit(void)
1251 {
1252 if (pDb->dbInf.cursorCommitBehavior == SQL_CB_PRESERVE)
1253 return(FALSE);
1254 else
1255 return(TRUE);
1256
1257 } // wxTable::IsCursorClosedOnCommit()
1258
1259 /********** wxTable::ClearMemberVars() **********/
1260 void wxTable::ClearMemberVars(void)
1261 {
1262 // Loop through the columns setting each member variable to zero
1263 for (int i = 0; i < noCols; i++)
1264 {
1265 switch(colDefs[i].SqlCtype)
1266 {
1267 case SQL_C_CHAR:
1268 ((UCHAR FAR *) colDefs[i].PtrDataObj)[0] = 0;
1269 break;
1270 case SQL_C_SSHORT:
1271 *((SWORD *) colDefs[i].PtrDataObj) = 0;
1272 break;
1273 case SQL_C_USHORT:
1274 *((UWORD*) colDefs[i].PtrDataObj) = 0;
1275 break;
1276 case SQL_C_SLONG:
1277 *((SDWORD *) colDefs[i].PtrDataObj) = 0;
1278 break;
1279 case SQL_C_ULONG:
1280 *((UDWORD *) colDefs[i].PtrDataObj) = 0;
1281 break;
1282 case SQL_C_FLOAT:
1283 *((SFLOAT *) colDefs[i].PtrDataObj) = 0.0f;
1284 break;
1285 case SQL_C_DOUBLE:
1286 *((SDOUBLE *) colDefs[i].PtrDataObj) = 0.0f;
1287 break;
1288 case SQL_C_TIMESTAMP:
1289 TIMESTAMP_STRUCT *pDt;
1290 pDt = (TIMESTAMP_STRUCT *) colDefs[i].PtrDataObj;
1291 pDt->year = 0;
1292 pDt->month = 0;
1293 pDt->day = 0;
1294 pDt->hour = 0;
1295 pDt->minute = 0;
1296 pDt->second = 0;
1297 pDt->fraction = 0;
1298 break;
1299 }
1300 }
1301
1302 } // wxTable::ClearMemberVars()
1303
1304 /********** wxTable::SetQueryTimeout() **********/
1305 bool wxTable::SetQueryTimeout(UDWORD nSeconds)
1306 {
1307 if (SQLSetStmtOption(c0, SQL_QUERY_TIMEOUT, nSeconds) != SQL_SUCCESS)
1308 return(pDb->DispAllErrors(henv, hdbc, c0));
1309 if (SQLSetStmtOption(c1, SQL_QUERY_TIMEOUT, nSeconds) != SQL_SUCCESS)
1310 return(pDb->DispAllErrors(henv, hdbc, c1));
1311 if (SQLSetStmtOption(c2, SQL_QUERY_TIMEOUT, nSeconds) != SQL_SUCCESS)
1312 return(pDb->DispAllErrors(henv, hdbc, c2));
1313 // if (SQLSetStmtOption(c3, SQL_QUERY_TIMEOUT, nSeconds) != SQL_SUCCESS)
1314 // return(pDb->DispAllErrors(henv, hdbc, c3));
1315 // if (SQLSetStmtOption(c4, SQL_QUERY_TIMEOUT, nSeconds) != SQL_SUCCESS)
1316 // return(pDb->DispAllErrors(henv, hdbc, c4));
1317 // if (SQLSetStmtOption(c5, SQL_QUERY_TIMEOUT, nSeconds) != SQL_SUCCESS)
1318 // return(pDb->DispAllErrors(henv, hdbc, c5));
1319 if (SQLSetStmtOption(hstmtInsert, SQL_QUERY_TIMEOUT, nSeconds) != SQL_SUCCESS)
1320 return(pDb->DispAllErrors(henv, hdbc, hstmtInsert));
1321 if (SQLSetStmtOption(hstmtUpdate, SQL_QUERY_TIMEOUT, nSeconds) != SQL_SUCCESS)
1322 return(pDb->DispAllErrors(henv, hdbc, hstmtUpdate));
1323 if (SQLSetStmtOption(hstmtDelete, SQL_QUERY_TIMEOUT, nSeconds) != SQL_SUCCESS)
1324 return(pDb->DispAllErrors(henv, hdbc, hstmtDelete));
1325 if (SQLSetStmtOption(hstmtCount, SQL_QUERY_TIMEOUT, nSeconds) != SQL_SUCCESS)
1326 return(pDb->DispAllErrors(henv, hdbc, hstmtCount));
1327
1328 // Completed Successfully
1329 return(TRUE);
1330
1331 } // wxTable::SetQueryTimeout()
1332
1333 /********** wxTable::SetColDefs() **********/
1334 void wxTable::SetColDefs (int index, char *fieldName, int dataType, void *pData,
1335 int cType, int size, bool keyField, bool upd,
1336 bool insAllow, bool derivedCol)
1337 {
1338 // Please, no uint, it doesn't exist for VC++
1339 if (strlen(fieldName) > (unsigned int) DB_MAX_COLUMN_NAME_LEN) // glt 4/21/97
1340 {
1341 strncpy (colDefs[index].ColName, fieldName, DB_MAX_COLUMN_NAME_LEN);
1342 colDefs[index].ColName[DB_MAX_COLUMN_NAME_LEN] = 0; // glt 10/23/97
1343 }
1344 else
1345 strcpy(colDefs[index].ColName, fieldName);
1346
1347 colDefs[index].DbDataType = dataType;
1348 colDefs[index].PtrDataObj = pData;
1349 colDefs[index].SqlCtype = cType;
1350 colDefs[index].SzDataObj = size;
1351 colDefs[index].KeyField = keyField;
1352 colDefs[index].DerivedCol = derivedCol;
1353 // Derived columns by definition would NOT be "Insertable" or "Updateable"
1354 if (derivedCol)
1355 {
1356 colDefs[index].Updateable = FALSE;
1357 colDefs[index].InsertAllowed = FALSE;
1358 }
1359 else
1360 {
1361 colDefs[index].Updateable = upd;
1362 colDefs[index].InsertAllowed = insAllow;
1363 }
1364
1365 } // wxTable::SetColDefs()
1366
1367 /********** wxTable::SetCursor() **********/
1368 bool wxTable::SetCursor(int cursorNo)
1369 {
1370 switch(cursorNo)
1371 {
1372 case DB_CURSOR0:
1373 hstmt = c0;
1374 // currCursorNo doesn't change since Cursor0 is a temp cursor
1375 break;
1376 case DB_CURSOR1:
1377 hstmt = c1;
1378 currCursorNo = DB_CURSOR1;
1379 break;
1380 case DB_CURSOR2:
1381 hstmt = c2;
1382 currCursorNo = DB_CURSOR2;
1383 break;
1384 // case DB_CURSOR3:
1385 // hstmt = c3;
1386 // currCursorNo = DB_CURSOR3;
1387 // break;
1388 // case DB_CURSOR4:
1389 // hstmt = c4;
1390 // currCursorNo = DB_CURSOR4;
1391 // break;
1392 // case DB_CURSOR5:
1393 // hstmt = c5;
1394 // currCursorNo = DB_CURSOR5;
1395 // break;
1396 default:
1397 return(FALSE);
1398 }
1399
1400 // Refresh the current record
1401 #ifndef FWD_ONLY_CURSORS
1402 UDWORD cRowsFetched;
1403 UWORD rowStatus;
1404 SQLExtendedFetch(hstmt, SQL_FETCH_NEXT, 0, &cRowsFetched, &rowStatus);
1405 SQLExtendedFetch(hstmt, SQL_FETCH_PRIOR, 0, &cRowsFetched, &rowStatus);
1406 #endif
1407
1408 // Completed successfully
1409 return(TRUE);
1410
1411 } // wxTable::SetCursor()
1412
1413 /********** wxTable::Count() **********/
1414 ULONG wxTable::Count(void)
1415 {
1416 ULONG l;
1417 char sqlStmt[DB_MAX_STATEMENT_LEN];
1418 SDWORD cb;
1419
1420 // Build a "SELECT COUNT(*) FROM queryTableName [WHERE whereClause]" SQL Statement
1421 strcpy(sqlStmt, "SELECT COUNT(*) FROM ");
1422 strcat(sqlStmt, queryTableName);
1423
1424 if (from && strlen(from))
1425 strcat(sqlStmt, from);
1426
1427 // Add the where clause if one is provided
1428 if (where && strlen(where))
1429 {
1430 strcat(sqlStmt, " WHERE ");
1431 strcat(sqlStmt, where);
1432 }
1433
1434 pDb->WriteSqlLog(sqlStmt);
1435
1436 // Execute the SQL statement
1437 if (SQLExecDirect(hstmtCount, (UCHAR FAR *) sqlStmt, SQL_NTS) != SQL_SUCCESS)
1438 {
1439 pDb->DispAllErrors(henv, hdbc, hstmtCount);
1440 return(0);
1441 }
1442
1443 // Fetch the record
1444 if (SQLFetch(hstmtCount) != SQL_SUCCESS)
1445 {
1446 pDb->DispAllErrors(henv, hdbc, hstmtCount);
1447 return(0);
1448 }
1449
1450 // Obtain the result
1451 if (SQLGetData(hstmtCount, 1, SQL_C_ULONG, (UCHAR*) &l, sizeof(l), &cb) != SQL_SUCCESS)
1452 {
1453 pDb->DispAllErrors(henv, hdbc, hstmtCount);
1454 return(0);
1455 }
1456
1457 // Free the cursor
1458 if (SQLFreeStmt(hstmtCount, SQL_CLOSE) != SQL_SUCCESS)
1459 pDb->DispAllErrors(henv, hdbc, hstmtCount);
1460
1461 // Return the record count
1462 return(l);
1463
1464 } // wxTable::Count()
1465
1466 /********** wxTable::Refresh() **********/
1467 bool wxTable::Refresh(void)
1468 {
1469 bool result = TRUE;
1470
1471 // Switch to cursor 0
1472 int cursorNo = GetCursor();
1473 if (!SetCursor())
1474 return(FALSE);
1475
1476 // Save the where and order by clauses
1477 char *saveWhere = where;
1478 char *saveOrderBy = orderBy;
1479
1480 // Build a where clause to refetch the record with. Try and use the
1481 // ROWID if it's available, ow use the key fields.
1482 char whereClause[DB_MAX_WHERE_CLAUSE_LEN+1];
1483 strcpy(whereClause, "");
1484 if (CanUpdByROWID())
1485 {
1486 SDWORD cb;
1487 char rowid[ROWID_LEN+1];
1488
1489 // Get the ROWID value. If not successful retreiving the ROWID,
1490 // simply fall down through the code and build the WHERE clause
1491 // based on the key fields.
1492 if (SQLGetData(hstmt, noCols+1, SQL_C_CHAR, (UCHAR*) rowid, ROWID_LEN, &cb) == SQL_SUCCESS)
1493 {
1494 strcat(whereClause, queryTableName);
1495 strcat(whereClause, ".ROWID = '");
1496 strcat(whereClause, rowid);
1497 strcat(whereClause, "'");
1498 }
1499 }
1500
1501 // If unable to use the ROWID, build a where clause from the keyfields
1502 if (strlen(whereClause) == 0)
1503 GetWhereClause(whereClause, DB_WHERE_KEYFIELDS, queryTableName);
1504
1505 // Requery the record
1506 where = whereClause;
1507 orderBy = 0;
1508 if (!Query())
1509 result = FALSE;
1510
1511 if (result && !GetNext())
1512 result = FALSE;
1513
1514 // Switch back to original cursor
1515 if (!SetCursor(cursorNo))
1516 result = FALSE;
1517
1518 // Restore the original where and order by clauses
1519 where = saveWhere;
1520 orderBy = saveOrderBy;
1521
1522 return(result);
1523
1524 } // wxTable::Refresh()
1525
1526 #endif
1527 // wxUSE_ODBC
1528
wxWidgets (Impactor)