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