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