]> git.saurik.com Git - wxWidgets.git/blob - src/common/dbtable.cpp
projects / wxWidgets.git / blob
? search:


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