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