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