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