]> 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
Wrap within #if wxUSE_NOTEBOOK
[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 #if defined(__GNUG__) && !defined(NO_GCC_PRAGMA)
29 #pragma implementation "dbtable.h"
30 #endif
31
32 #include "wx/wxprec.h"
33
34 #ifdef __BORLANDC__
35 #pragma hdrstop
36 #endif
37
38 #ifdef DBDEBUG_CONSOLE
39 #if wxUSE_IOSTREAMH
40 #include <iostream.h>
41 #else
42 #include <iostream>
43 #endif
44 #include "wx/ioswrap.h"
45 #endif
46
47 #ifndef WX_PRECOMP
48 #include "wx/string.h"
49 #include "wx/object.h"
50 #include "wx/list.h"
51 #include "wx/utils.h"
52 #include "wx/log.h"
53 #endif
54 #include "wx/filefn.h"
55
56 #if wxUSE_ODBC
57
58 #include <stdio.h>
59 #include <stdlib.h>
60 #include <string.h>
61
62 #include "wx/dbtable.h"
63
64 #ifdef __UNIX__
65 // The HPUX preprocessor lines below were commented out on 8/20/97
66 // because macros.h currently redefines DEBUG and is unneeded.
67 // # ifdef HPUX
68 // # include <macros.h>
69 // # endif
70 # ifdef LINUX
71 # include <sys/minmax.h>
72 # endif
73 #endif
74
75 ULONG lastTableID = 0;
76
77
78 #ifdef __WXDEBUG__
79 wxList TablesInUse;
80 #endif
81
82
83 void csstrncpyt(wxChar *target, const wxChar *source, int n)
84 {
85 while ( (*target++ = *source++) != '\0' && --n )
86 ;
87
88 *target = '\0';
89 }
90
91
92
93 /********** wxDbColDef::wxDbColDef() Constructor **********/
94 wxDbColDef::wxDbColDef()
95 {
96 Initialize();
97 } // Constructor
98
99
100 bool wxDbColDef::Initialize()
101 {
102 ColName[0] = 0;
103 DbDataType = DB_DATA_TYPE_INTEGER;
104 SqlCtype = SQL_C_LONG;
105 PtrDataObj = NULL;
106 SzDataObj = 0;
107 KeyField = FALSE;
108 Updateable = FALSE;
109 InsertAllowed = FALSE;
110 DerivedCol = FALSE;
111 CbValue = 0;
112 Null = FALSE;
113
114 return TRUE;
115 } // wxDbColDef::Initialize()
116
117
118 /********** wxDbTable::wxDbTable() Constructor **********/
119 wxDbTable::wxDbTable(wxDb *pwxDb, const wxString &tblName, const UWORD numColumns,
120 const wxString &qryTblName, bool qryOnly, const wxString &tblPath)
121 {
122 if (!initialize(pwxDb, tblName, numColumns, qryTblName, qryOnly, tblPath))
123 cleanup();
124 } // wxDbTable::wxDbTable()
125
126
127 /***** DEPRECATED: use wxDbTable::wxDbTable() format above *****/
128 wxDbTable::wxDbTable(wxDb *pwxDb, const wxString &tblName, const UWORD numColumns,
129 const wxChar *qryTblName, bool qryOnly, const wxString &tblPath)
130 {
131 wxString tempQryTblName;
132 tempQryTblName = qryTblName;
133 if (!initialize(pwxDb, tblName, numColumns, tempQryTblName, qryOnly, tblPath))
134 cleanup();
135 } // wxDbTable::wxDbTable()
136
137
138 /********** wxDbTable::~wxDbTable() **********/
139 wxDbTable::~wxDbTable()
140 {
141 this->cleanup();
142 } // wxDbTable::~wxDbTable()
143
144
145 bool wxDbTable::initialize(wxDb *pwxDb, const wxString &tblName, const UWORD numColumns,
146 const wxString &qryTblName, bool qryOnly, const wxString &tblPath)
147 {
148 // Initializing member variables
149 pDb = pwxDb; // Pointer to the wxDb object
150 henv = 0;
151 hdbc = 0;
152 hstmt = 0;
153 m_hstmtGridQuery = 0;
154 hstmtDefault = 0; // Initialized below
155 hstmtCount = 0; // Initialized first time it is needed
156 hstmtInsert = 0;
157 hstmtDelete = 0;
158 hstmtUpdate = 0;
159 hstmtInternal = 0;
160 colDefs = 0;
161 tableID = 0;
162 noCols = numColumns; // Number of cols in the table
163 where.Empty(); // Where clause
164 orderBy.Empty(); // Order By clause
165 from.Empty(); // From clause
166 selectForUpdate = FALSE; // SELECT ... FOR UPDATE; Indicates whether to include the FOR UPDATE phrase
167 queryOnly = qryOnly;
168 insertable = TRUE;
169 tablePath.Empty();
170 tableName.Empty();
171 queryTableName.Empty();
172
173 wxASSERT(tblName.Length());
174 wxASSERT(pDb);
175
176 if (!pDb)
177 return FALSE;
178
179 tableName = tblName; // Table Name
180 if (tblPath.Length())
181 tablePath = tblPath; // Table Path - used for dBase files
182 else
183 tablePath.Empty();
184
185 if (qryTblName.Length()) // Name of the table/view to query
186 queryTableName = qryTblName;
187 else
188 queryTableName = tblName;
189
190 pDb->incrementTableCount();
191
192 wxString s;
193 tableID = ++lastTableID;
194 s.Printf(wxT("wxDbTable constructor (%-20s) tableID:[%6lu] pDb:[%p]"), tblName.c_str(), tableID, pDb);
195
196 #ifdef __WXDEBUG__
197 wxTablesInUse *tableInUse;
198 tableInUse = new wxTablesInUse();
199 tableInUse->tableName = tblName;
200 tableInUse->tableID = tableID;
201 tableInUse->pDb = pDb;
202 TablesInUse.Append(tableInUse);
203 #endif
204
205 pDb->WriteSqlLog(s);
206
207 // Grab the HENV and HDBC from the wxDb object
208 henv = pDb->GetHENV();
209 hdbc = pDb->GetHDBC();
210
211 // Allocate space for column definitions
212 if (noCols)
213 colDefs = new wxDbColDef[noCols]; // Points to the first column definition
214
215 // Allocate statement handles for the table
216 if (!queryOnly)
217 {
218 // Allocate a separate statement handle for performing inserts
219 if (SQLAllocStmt(hdbc, &hstmtInsert) != SQL_SUCCESS)
220 pDb->DispAllErrors(henv, hdbc);
221 // Allocate a separate statement handle for performing deletes
222 if (SQLAllocStmt(hdbc, &hstmtDelete) != SQL_SUCCESS)
223 pDb->DispAllErrors(henv, hdbc);
224 // Allocate a separate statement handle for performing updates
225 if (SQLAllocStmt(hdbc, &hstmtUpdate) != SQL_SUCCESS)
226 pDb->DispAllErrors(henv, hdbc);
227 }
228 // Allocate a separate statement handle for internal use
229 if (SQLAllocStmt(hdbc, &hstmtInternal) != SQL_SUCCESS)
230 pDb->DispAllErrors(henv, hdbc);
231
232 // Set the cursor type for the statement handles
233 cursorType = SQL_CURSOR_STATIC;
234
235 if (SQLSetStmtOption(hstmtInternal, SQL_CURSOR_TYPE, cursorType) != SQL_SUCCESS)
236 {
237 // Check to see if cursor type is supported
238 pDb->GetNextError(henv, hdbc, hstmtInternal);
239 if (! wxStrcmp(pDb->sqlState, wxT("01S02"))) // Option Value Changed
240 {
241 // Datasource does not support static cursors. Driver
242 // will substitute a cursor type. Call SQLGetStmtOption()
243 // to determine which cursor type was selected.
244 if (SQLGetStmtOption(hstmtInternal, SQL_CURSOR_TYPE, &cursorType) != SQL_SUCCESS)
245 pDb->DispAllErrors(henv, hdbc, hstmtInternal);
246 #ifdef DBDEBUG_CONSOLE
247 cout << wxT("Static cursor changed to: ");
248 switch(cursorType)
249 {
250 case SQL_CURSOR_FORWARD_ONLY:
251 cout << wxT("Forward Only");
252 break;
253 case SQL_CURSOR_STATIC:
254 cout << wxT("Static");
255 break;
256 case SQL_CURSOR_KEYSET_DRIVEN:
257 cout << wxT("Keyset Driven");
258 break;
259 case SQL_CURSOR_DYNAMIC:
260 cout << wxT("Dynamic");
261 break;
262 }
263 cout << endl << endl;
264 #endif
265 // BJO20000425
266 if (pDb->FwdOnlyCursors() && cursorType != SQL_CURSOR_FORWARD_ONLY)
267 {
268 // Force the use of a forward only cursor...
269 cursorType = SQL_CURSOR_FORWARD_ONLY;
270 if (SQLSetStmtOption(hstmtInternal, SQL_CURSOR_TYPE, cursorType) != SQL_SUCCESS)
271 {
272 // Should never happen
273 pDb->GetNextError(henv, hdbc, hstmtInternal);
274 return FALSE;
275 }
276 }
277 }
278 else
279 {
280 pDb->DispNextError();
281 pDb->DispAllErrors(henv, hdbc, hstmtInternal);
282 }
283 }
284 #ifdef DBDEBUG_CONSOLE
285 else
286 cout << wxT("Cursor Type set to STATIC") << endl << endl;
287 #endif
288
289 if (!queryOnly)
290 {
291 // Set the cursor type for the INSERT statement handle
292 if (SQLSetStmtOption(hstmtInsert, SQL_CURSOR_TYPE, SQL_CURSOR_FORWARD_ONLY) != SQL_SUCCESS)
293 pDb->DispAllErrors(henv, hdbc, hstmtInsert);
294 // Set the cursor type for the DELETE statement handle
295 if (SQLSetStmtOption(hstmtDelete, SQL_CURSOR_TYPE, SQL_CURSOR_FORWARD_ONLY) != SQL_SUCCESS)
296 pDb->DispAllErrors(henv, hdbc, hstmtDelete);
297 // Set the cursor type for the UPDATE statement handle
298 if (SQLSetStmtOption(hstmtUpdate, SQL_CURSOR_TYPE, SQL_CURSOR_FORWARD_ONLY) != SQL_SUCCESS)
299 pDb->DispAllErrors(henv, hdbc, hstmtUpdate);
300 }
301
302 // Make the default cursor the active cursor
303 hstmtDefault = GetNewCursor(FALSE,FALSE);
304 wxASSERT(hstmtDefault);
305 hstmt = *hstmtDefault;
306
307 return TRUE;
308
309 } // wxDbTable::initialize()
310
311
312 void wxDbTable::cleanup()
313 {
314 wxString s;
315 if (pDb)
316 {
317 s.Printf(wxT("wxDbTable destructor (%-20s) tableID:[%6lu] pDb:[%p]"), tableName.c_str(), tableID, pDb);
318 pDb->WriteSqlLog(s);
319 }
320
321 #ifdef __WXDEBUG__
322 if (tableID)
323 {
324 TablesInUse.DeleteContents(TRUE);
325 bool found = FALSE;
326
327 wxNode *pNode;
328 pNode = TablesInUse.GetFirst();
329 while (pNode && !found)
330 {
331 if (((wxTablesInUse *)pNode->GetData())->tableID == tableID)
332 {
333 found = TRUE;
334 if (!TablesInUse.DeleteNode(pNode))
335 wxLogDebug (s,wxT("Unable to delete node!"));
336 }
337 else
338 pNode = pNode->GetNext();
339 }
340 if (!found)
341 {
342 wxString msg;
343 msg.Printf(wxT("Unable to find the tableID in the linked\nlist of tables in use.\n\n%s"),s.c_str());
344 wxLogDebug (msg,wxT("NOTICE..."));
345 }
346 }
347 #endif
348
349 // Decrement the wxDb table count
350 if (pDb)
351 pDb->decrementTableCount();
352
353 // Delete memory allocated for column definitions
354 if (colDefs)
355 delete [] colDefs;
356
357 // Free statement handles
358 if (!queryOnly)
359 {
360 if (hstmtInsert)
361 {
362 /*
363 ODBC 3.0 says to use this form
364 if (SQLFreeHandle(*hstmtDel, SQL_DROP) != SQL_SUCCESS)
365 */
366 if (SQLFreeStmt(hstmtInsert, SQL_DROP) != SQL_SUCCESS)
367 pDb->DispAllErrors(henv, hdbc);
368 }
369
370 if (hstmtDelete)
371 {
372 /*
373 ODBC 3.0 says to use this form
374 if (SQLFreeHandle(*hstmtDel, SQL_DROP) != SQL_SUCCESS)
375 */
376 if (SQLFreeStmt(hstmtDelete, SQL_DROP) != SQL_SUCCESS)
377 pDb->DispAllErrors(henv, hdbc);
378 }
379
380 if (hstmtUpdate)
381 {
382 /*
383 ODBC 3.0 says to use this form
384 if (SQLFreeHandle(*hstmtDel, SQL_DROP) != SQL_SUCCESS)
385 */
386 if (SQLFreeStmt(hstmtUpdate, SQL_DROP) != SQL_SUCCESS)
387 pDb->DispAllErrors(henv, hdbc);
388 }
389 }
390
391 if (hstmtInternal)
392 {
393 if (SQLFreeStmt(hstmtInternal, SQL_DROP) != SQL_SUCCESS)
394 pDb->DispAllErrors(henv, hdbc);
395 }
396
397 // Delete dynamically allocated cursors
398 if (hstmtDefault)
399 DeleteCursor(hstmtDefault);
400
401 if (hstmtCount)
402 DeleteCursor(hstmtCount);
403
404 if (m_hstmtGridQuery)
405 DeleteCursor(m_hstmtGridQuery);
406
407 } // wxDbTable::cleanup()
408
409
410 /***************************** PRIVATE FUNCTIONS *****************************/
411
412
413 bool wxDbTable::setCbValueForColumn(int columnIndex)
414 {
415 switch(colDefs[columnIndex].DbDataType)
416 {
417 case DB_DATA_TYPE_VARCHAR:
418 if (colDefs[columnIndex].Null)
419 colDefs[columnIndex].CbValue = SQL_NULL_DATA;
420 else
421 colDefs[columnIndex].CbValue = SQL_NTS;
422 break;
423 case DB_DATA_TYPE_INTEGER:
424 if (colDefs[columnIndex].Null)
425 colDefs[columnIndex].CbValue = SQL_NULL_DATA;
426 else
427 colDefs[columnIndex].CbValue = 0;
428 break;
429 case DB_DATA_TYPE_FLOAT:
430 if (colDefs[columnIndex].Null)
431 colDefs[columnIndex].CbValue = SQL_NULL_DATA;
432 else
433 colDefs[columnIndex].CbValue = 0;
434 break;
435 case DB_DATA_TYPE_DATE:
436 if (colDefs[columnIndex].Null)
437 colDefs[columnIndex].CbValue = SQL_NULL_DATA;
438 else
439 colDefs[columnIndex].CbValue = 0;
440 break;
441 case DB_DATA_TYPE_BLOB:
442 if (colDefs[columnIndex].Null)
443 colDefs[columnIndex].CbValue = SQL_NULL_DATA;
444 else
445 if (colDefs[columnIndex].SqlCtype == SQL_C_BINARY)
446 colDefs[columnIndex].CbValue = 0;
447 else if (colDefs[columnIndex].SqlCtype == SQL_C_CHAR)
448 colDefs[columnIndex].CbValue = SQL_LEN_DATA_AT_EXEC(0);
449 else
450 colDefs[columnIndex].CbValue = SQL_LEN_DATA_AT_EXEC(colDefs[columnIndex].SzDataObj);
451 break;
452 }
453 }
454
455 /********** wxDbTable::bindParams() **********/
456 bool wxDbTable::bindParams(bool forUpdate)
457 {
458 wxASSERT(!queryOnly);
459 if (queryOnly)
460 return(FALSE);
461
462 SWORD fSqlType = 0;
463 SDWORD precision = 0;
464 SWORD scale = 0;
465
466 // Bind each column of the table that should be bound
467 // to a parameter marker
468 int i;
469 UWORD colNo;
470
471 for (i=0, colNo=1; i < noCols; i++)
472 {
473 if (forUpdate)
474 {
475 if (!colDefs[i].Updateable)
476 continue;
477 }
478 else
479 {
480 if (!colDefs[i].InsertAllowed)
481 continue;
482 }
483
484 switch(colDefs[i].DbDataType)
485 {
486 case DB_DATA_TYPE_VARCHAR:
487 fSqlType = pDb->GetTypeInfVarchar().FsqlType;
488 precision = colDefs[i].SzDataObj;
489 scale = 0;
490 break;
491 case DB_DATA_TYPE_INTEGER:
492 fSqlType = pDb->GetTypeInfInteger().FsqlType;
493 precision = pDb->GetTypeInfInteger().Precision;
494 scale = 0;
495 break;
496 case DB_DATA_TYPE_FLOAT:
497 fSqlType = pDb->GetTypeInfFloat().FsqlType;
498 precision = pDb->GetTypeInfFloat().Precision;
499 scale = pDb->GetTypeInfFloat().MaximumScale;
500 // SQL Sybase Anywhere v5.5 returned a negative number for the
501 // MaxScale. This caused ODBC to kick out an error on ibscale.
502 // I check for this here and set the scale = precision.
503 //if (scale < 0)
504 // scale = (short) precision;
505 break;
506 case DB_DATA_TYPE_DATE:
507 fSqlType = pDb->GetTypeInfDate().FsqlType;
508 precision = pDb->GetTypeInfDate().Precision;
509 scale = 0;
510 break;
511 case DB_DATA_TYPE_BLOB:
512 fSqlType = pDb->GetTypeInfBlob().FsqlType;
513 precision = -1;
514 scale = 0;
515 break;
516 }
517
518 setCbValueForColumn(i);
519
520 if (forUpdate)
521 {
522 if (SQLBindParameter(hstmtUpdate, colNo++, SQL_PARAM_INPUT, colDefs[i].SqlCtype,
523 fSqlType, precision, scale, (UCHAR*) colDefs[i].PtrDataObj,
524 precision+1, &colDefs[i].CbValue) != SQL_SUCCESS)
525 {
526 return(pDb->DispAllErrors(henv, hdbc, hstmtUpdate));
527 }
528 }
529 else
530 {
531 if (SQLBindParameter(hstmtInsert, colNo++, SQL_PARAM_INPUT, colDefs[i].SqlCtype,
532 fSqlType, precision, scale, (UCHAR*) colDefs[i].PtrDataObj,
533 precision+1,&colDefs[i].CbValue) != SQL_SUCCESS)
534 {
535 return(pDb->DispAllErrors(henv, hdbc, hstmtInsert));
536 }
537 }
538 }
539
540 // Completed successfully
541 return(TRUE);
542
543 } // wxDbTable::bindParams()
544
545
546 /********** wxDbTable::bindInsertParams() **********/
547 bool wxDbTable::bindInsertParams(void)
548 {
549 return bindParams(FALSE);
550 } // wxDbTable::bindInsertParams()
551
552
553 /********** wxDbTable::bindUpdateParams() **********/
554 bool wxDbTable::bindUpdateParams(void)
555 {
556 return bindParams(TRUE);
557 } // wxDbTable::bindUpdateParams()
558
559
560 /********** wxDbTable::bindCols() **********/
561 bool wxDbTable::bindCols(HSTMT cursor)
562 {
563 static SDWORD cb;
564
565 // Bind each column of the table to a memory address for fetching data
566 UWORD i;
567 for (i = 0; i < noCols; i++)
568 {
569 cb = colDefs[i].CbValue;
570 if (SQLBindCol(cursor, (UWORD)(i+1), colDefs[i].SqlCtype, (UCHAR*) colDefs[i].PtrDataObj,
571 colDefs[i].SzDataObj, &cb ) != SQL_SUCCESS)
572 return (pDb->DispAllErrors(henv, hdbc, cursor));
573 }
574
575 // Completed successfully
576 return(TRUE);
577
578 } // wxDbTable::bindCols()
579
580
581 /********** wxDbTable::getRec() **********/
582 bool wxDbTable::getRec(UWORD fetchType)
583 {
584 RETCODE retcode;
585
586 if (!pDb->FwdOnlyCursors())
587 {
588 // Fetch the NEXT, PREV, FIRST or LAST record, depending on fetchType
589 UDWORD cRowsFetched;
590 UWORD rowStatus;
591
592 retcode = SQLExtendedFetch(hstmt, fetchType, 0, &cRowsFetched, &rowStatus);
593 if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
594 {
595 if (retcode == SQL_NO_DATA_FOUND)
596 return(FALSE);
597 else
598 return(pDb->DispAllErrors(henv, hdbc, hstmt));
599 }
600 else
601 {
602 // Set the Null member variable to indicate the Null state
603 // of each column just read in.
604 int i;
605 for (i = 0; i < noCols; i++)
606 colDefs[i].Null = (colDefs[i].CbValue == SQL_NULL_DATA);
607 }
608 }
609 else
610 {
611 // Fetch the next record from the record set
612 retcode = SQLFetch(hstmt);
613 if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
614 {
615 if (retcode == SQL_NO_DATA_FOUND)
616 return(FALSE);
617 else
618 return(pDb->DispAllErrors(henv, hdbc, hstmt));
619 }
620 else
621 {
622 // Set the Null member variable to indicate the Null state
623 // of each column just read in.
624 int i;
625 for (i = 0; i < noCols; i++)
626 colDefs[i].Null = (colDefs[i].CbValue == SQL_NULL_DATA);
627 }
628 }
629
630 // Completed successfully
631 return(TRUE);
632
633 } // wxDbTable::getRec()
634
635
636 /********** wxDbTable::execDelete() **********/
637 bool wxDbTable::execDelete(const wxString &pSqlStmt)
638 {
639 RETCODE retcode;
640
641 // Execute the DELETE statement
642 retcode = SQLExecDirect(hstmtDelete, (SQLTCHAR FAR *) pSqlStmt.c_str(), SQL_NTS);
643
644 if (retcode == SQL_SUCCESS ||
645 retcode == SQL_NO_DATA_FOUND ||
646 retcode == SQL_SUCCESS_WITH_INFO)
647 {
648 // Record deleted successfully
649 return(TRUE);
650 }
651
652 // Problem deleting record
653 return(pDb->DispAllErrors(henv, hdbc, hstmtDelete));
654
655 } // wxDbTable::execDelete()
656
657
658 /********** wxDbTable::execUpdate() **********/
659 bool wxDbTable::execUpdate(const wxString &pSqlStmt)
660 {
661 RETCODE retcode;
662
663 // Execute the UPDATE statement
664 retcode = SQLExecDirect(hstmtUpdate, (SQLTCHAR FAR *) pSqlStmt.c_str(), SQL_NTS);
665
666 if (retcode == SQL_SUCCESS ||
667 retcode == SQL_NO_DATA_FOUND ||
668 retcode == SQL_SUCCESS_WITH_INFO)
669 {
670 // Record updated successfully
671 return(TRUE);
672 }
673 else if (retcode == SQL_NEED_DATA)
674 {
675 PTR pParmID;
676 retcode = SQLParamData(hstmtUpdate, &pParmID);
677 while (retcode == SQL_NEED_DATA)
678 {
679 // Find the parameter
680 int i;
681 for (i=0; i < noCols; i++)
682 {
683 if (colDefs[i].PtrDataObj == pParmID)
684 {
685 // We found it. Store the parameter.
686 retcode = SQLPutData(hstmtUpdate, pParmID, colDefs[i].SzDataObj);
687 if (retcode != SQL_SUCCESS)
688 {
689 pDb->DispNextError();
690 return pDb->DispAllErrors(henv, hdbc, hstmtUpdate);
691 }
692 break;
693 }
694 }
695 }
696 if (retcode == SQL_SUCCESS ||
697 retcode == SQL_NO_DATA_FOUND ||
698 retcode == SQL_SUCCESS_WITH_INFO)
699 {
700 // Record updated successfully
701 return(TRUE);
702 }
703 }
704
705 // Problem updating record
706 return(pDb->DispAllErrors(henv, hdbc, hstmtUpdate));
707
708 } // wxDbTable::execUpdate()
709
710
711 /********** wxDbTable::query() **********/
712 bool wxDbTable::query(int queryType, bool forUpdate, bool distinct, const wxString &pSqlStmt)
713 {
714 wxString sqlStmt;
715
716 if (forUpdate)
717 // The user may wish to select for update, but the DBMS may not be capable
718 selectForUpdate = CanSelectForUpdate();
719 else
720 selectForUpdate = FALSE;
721
722 // Set the SQL SELECT string
723 if (queryType != DB_SELECT_STATEMENT) // A select statement was not passed in,
724 { // so generate a select statement.
725 BuildSelectStmt(sqlStmt, queryType, distinct);
726 pDb->WriteSqlLog(sqlStmt);
727 }
728
729 // Make sure the cursor is closed first
730 if (!CloseCursor(hstmt))
731 return(FALSE);
732
733 // Execute the SQL SELECT statement
734 int retcode;
735 retcode = SQLExecDirect(hstmt, (SQLTCHAR FAR *) (queryType == DB_SELECT_STATEMENT ? pSqlStmt.c_str() : sqlStmt.c_str()), SQL_NTS);
736 if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
737 return(pDb->DispAllErrors(henv, hdbc, hstmt));
738
739 // Completed successfully
740 return(TRUE);
741
742 } // wxDbTable::query()
743
744
745 /***************************** PUBLIC FUNCTIONS *****************************/
746
747
748 /********** wxDbTable::Open() **********/
749 bool wxDbTable::Open(bool checkPrivileges, bool checkTableExists)
750 {
751 if (!pDb)
752 return FALSE;
753
754 int i;
755 wxString sqlStmt;
756 wxString s;
757 // int NumKeyCols=0;
758
759 // Calculate the maximum size of the concatenated
760 // keys for use with wxDbGrid
761 m_keysize = 0;
762 for (i=0; i < noCols; i++)
763 {
764 if (colDefs[i].KeyField)
765 {
766 // NumKeyCols++;
767 m_keysize += colDefs[i].SzDataObj;
768 }
769 }
770
771 s.Empty();
772 // Verify that the table exists in the database
773 if (checkTableExists && !pDb->TableExists(tableName, pDb->GetUsername(), tablePath))
774 {
775 s = wxT("Table/view does not exist in the database");
776 if ( *(pDb->dbInf.accessibleTables) == wxT('Y'))
777 s += wxT(", or you have no permissions.\n");
778 else
779 s += wxT(".\n");
780 }
781 else if (checkPrivileges)
782 {
783 // Verify the user has rights to access the table.
784 // Shortcut boolean evaluation to optimize out call to
785 // TablePrivileges
786 //
787 // Unfortunately this optimization doesn't seem to be
788 // reliable!
789 if (// *(pDb->dbInf.accessibleTables) == 'N' &&
790 !pDb->TablePrivileges(tableName,wxT("SELECT"), pDb->GetUsername(), pDb->GetUsername(), tablePath))
791 s = wxT("Current logged in user does not have sufficient privileges to access this table.\n");
792 }
793
794 if (!s.IsEmpty())
795 {
796 wxString p;
797
798 if (!tablePath.IsEmpty())
799 p.Printf(wxT("Error opening '%s/%s'.\n"),tablePath.c_str(),tableName.c_str());
800 else
801 p.Printf(wxT("Error opening '%s'.\n"), tableName.c_str());
802
803 p += s;
804 pDb->LogError(p.GetData());
805
806 return(FALSE);
807 }
808
809 // Bind the member variables for field exchange between
810 // the wxDbTable object and the ODBC record.
811 if (!queryOnly)
812 {
813 if (!bindInsertParams()) // Inserts
814 return(FALSE);
815
816 if (!bindUpdateParams()) // Updates
817 return(FALSE);
818 }
819
820 if (!bindCols(*hstmtDefault)) // Selects
821 return(FALSE);
822
823 if (!bindCols(hstmtInternal)) // Internal use only
824 return(FALSE);
825
826 /*
827 * Do NOT bind the hstmtCount cursor!!!
828 */
829
830 // Build an insert statement using parameter markers
831 if (!queryOnly && noCols > 0)
832 {
833 bool needComma = FALSE;
834 sqlStmt.Printf(wxT("INSERT INTO %s ("),
835 pDb->SQLTableName(tableName.c_str()).c_str());
836 for (i = 0; i < noCols; i++)
837 {
838 if (! colDefs[i].InsertAllowed)
839 continue;
840 if (needComma)
841 sqlStmt += wxT(",");
842 sqlStmt += pDb->SQLColumnName(colDefs[i].ColName);
843 // sqlStmt += colDefs[i].ColName;
844 needComma = TRUE;
845 }
846 needComma = FALSE;
847 sqlStmt += wxT(") VALUES (");
848
849 int insertableCount = 0;
850
851 for (i = 0; i < noCols; i++)
852 {
853 if (! colDefs[i].InsertAllowed)
854 continue;
855 if (needComma)
856 sqlStmt += wxT(",");
857 sqlStmt += wxT("?");
858 needComma = TRUE;
859 insertableCount++;
860 }
861 sqlStmt += wxT(")");
862
863 // Prepare the insert statement for execution
864 if (insertableCount)
865 {
866 if (SQLPrepare(hstmtInsert, (SQLTCHAR FAR *) sqlStmt.c_str(), SQL_NTS) != SQL_SUCCESS)
867 return(pDb->DispAllErrors(henv, hdbc, hstmtInsert));
868 }
869 else
870 insertable= FALSE;
871 }
872
873 // Completed successfully
874 return(TRUE);
875
876 } // wxDbTable::Open()
877
878
879 /********** wxDbTable::Query() **********/
880 bool wxDbTable::Query(bool forUpdate, bool distinct)
881 {
882
883 return(query(DB_SELECT_WHERE, forUpdate, distinct));
884
885 } // wxDbTable::Query()
886
887
888 /********** wxDbTable::QueryBySqlStmt() **********/
889 bool wxDbTable::QueryBySqlStmt(const wxString &pSqlStmt)
890 {
891 pDb->WriteSqlLog(pSqlStmt);
892
893 return(query(DB_SELECT_STATEMENT, FALSE, FALSE, pSqlStmt));
894
895 } // wxDbTable::QueryBySqlStmt()
896
897
898 /********** wxDbTable::QueryMatching() **********/
899 bool wxDbTable::QueryMatching(bool forUpdate, bool distinct)
900 {
901
902 return(query(DB_SELECT_MATCHING, forUpdate, distinct));
903
904 } // wxDbTable::QueryMatching()
905
906
907 /********** wxDbTable::QueryOnKeyFields() **********/
908 bool wxDbTable::QueryOnKeyFields(bool forUpdate, bool distinct)
909 {
910
911 return(query(DB_SELECT_KEYFIELDS, forUpdate, distinct));
912
913 } // wxDbTable::QueryOnKeyFields()
914
915
916 /********** wxDbTable::GetPrev() **********/
917 bool wxDbTable::GetPrev(void)
918 {
919 if (pDb->FwdOnlyCursors())
920 {
921 wxFAIL_MSG(wxT("GetPrev()::Backward scrolling cursors are not enabled for this instance of wxDbTable"));
922 return FALSE;
923 }
924 else
925 return(getRec(SQL_FETCH_PRIOR));
926
927 } // wxDbTable::GetPrev()
928
929
930 /********** wxDbTable::operator-- **********/
931 bool wxDbTable::operator--(int)
932 {
933 if (pDb->FwdOnlyCursors())
934 {
935 wxFAIL_MSG(wxT("operator--:Backward scrolling cursors are not enabled for this instance of wxDbTable"));
936 return FALSE;
937 }
938 else
939 return(getRec(SQL_FETCH_PRIOR));
940
941 } // wxDbTable::operator--
942
943
944 /********** wxDbTable::GetFirst() **********/
945 bool wxDbTable::GetFirst(void)
946 {
947 if (pDb->FwdOnlyCursors())
948 {
949 wxFAIL_MSG(wxT("GetFirst():Backward scrolling cursors are not enabled for this instance of wxDbTable"));
950 return FALSE;
951 }
952 else
953 return(getRec(SQL_FETCH_FIRST));
954
955 } // wxDbTable::GetFirst()
956
957
958 /********** wxDbTable::GetLast() **********/
959 bool wxDbTable::GetLast(void)
960 {
961 if (pDb->FwdOnlyCursors())
962 {
963 wxFAIL_MSG(wxT("GetLast()::Backward scrolling cursors are not enabled for this instance of wxDbTable"));
964 return FALSE;
965 }
966 else
967 return(getRec(SQL_FETCH_LAST));
968
969 } // wxDbTable::GetLast()
970
971
972 /********** wxDbTable::BuildDeleteStmt() **********/
973 void wxDbTable::BuildDeleteStmt(wxString &pSqlStmt, int typeOfDel, const wxString &pWhereClause)
974 {
975 wxASSERT(!queryOnly);
976 if (queryOnly)
977 return;
978
979 wxString whereClause;
980
981 whereClause.Empty();
982
983 // Handle the case of DeleteWhere() and the where clause is blank. It should
984 // delete all records from the database in this case.
985 if (typeOfDel == DB_DEL_WHERE && (pWhereClause.Length() == 0))
986 {
987 pSqlStmt.Printf(wxT("DELETE FROM %s"),
988 pDb->SQLTableName(tableName.c_str()).c_str());
989 return;
990 }
991
992 pSqlStmt.Printf(wxT("DELETE FROM %s WHERE "),
993 pDb->SQLTableName(tableName.c_str()).c_str());
994
995 // Append the WHERE clause to the SQL DELETE statement
996 switch(typeOfDel)
997 {
998 case DB_DEL_KEYFIELDS:
999 // If the datasource supports the ROWID column, build
1000 // the where on ROWID for efficiency purposes.
1001 // e.g. DELETE FROM PARTS WHERE ROWID = '111.222.333'
1002 if (CanUpdByROWID())
1003 {
1004 SDWORD cb;
1005 wxChar rowid[wxDB_ROWID_LEN+1];
1006
1007 // Get the ROWID value. If not successful retreiving the ROWID,
1008 // simply fall down through the code and build the WHERE clause
1009 // based on the key fields.
1010 if (SQLGetData(hstmt, (UWORD)(noCols+1), SQL_C_CHAR, (UCHAR*) rowid, wxDB_ROWID_LEN, &cb) == SQL_SUCCESS)
1011 {
1012 pSqlStmt += wxT("ROWID = '");
1013 pSqlStmt += rowid;
1014 pSqlStmt += wxT("'");
1015 break;
1016 }
1017 }
1018 // Unable to delete by ROWID, so build a WHERE
1019 // clause based on the keyfields.
1020 BuildWhereClause(whereClause, DB_WHERE_KEYFIELDS);
1021 pSqlStmt += whereClause;
1022 break;
1023 case DB_DEL_WHERE:
1024 pSqlStmt += pWhereClause;
1025 break;
1026 case DB_DEL_MATCHING:
1027 BuildWhereClause(whereClause, DB_WHERE_MATCHING);
1028 pSqlStmt += whereClause;
1029 break;
1030 }
1031
1032 } // BuildDeleteStmt()
1033
1034
1035 /***** DEPRECATED: use wxDbTable::BuildDeleteStmt(wxString &....) form *****/
1036 void wxDbTable::BuildDeleteStmt(wxChar *pSqlStmt, int typeOfDel, const wxString &pWhereClause)
1037 {
1038 wxString tempSqlStmt;
1039 BuildDeleteStmt(tempSqlStmt, typeOfDel, pWhereClause);
1040 wxStrcpy(pSqlStmt, tempSqlStmt);
1041 } // wxDbTable::BuildDeleteStmt()
1042
1043
1044 /********** wxDbTable::BuildSelectStmt() **********/
1045 void wxDbTable::BuildSelectStmt(wxString &pSqlStmt, int typeOfSelect, bool distinct)
1046 {
1047 wxString whereClause;
1048 whereClause.Empty();
1049
1050 // Build a select statement to query the database
1051 pSqlStmt = wxT("SELECT ");
1052
1053 // SELECT DISTINCT values only?
1054 if (distinct)
1055 pSqlStmt += wxT("DISTINCT ");
1056
1057 // Was a FROM clause specified to join tables to the base table?
1058 // Available for ::Query() only!!!
1059 bool appendFromClause = FALSE;
1060 #if wxODBC_BACKWARD_COMPATABILITY
1061 if (typeOfSelect == DB_SELECT_WHERE && from && wxStrlen(from))
1062 appendFromClause = TRUE;
1063 #else
1064 if (typeOfSelect == DB_SELECT_WHERE && from.Length())
1065 appendFromClause = TRUE;
1066 #endif
1067
1068 // Add the column list
1069 int i;
1070 wxString tStr;
1071 for (i = 0; i < noCols; i++)
1072 {
1073 tStr = colDefs[i].ColName;
1074 // If joining tables, the base table column names must be qualified to avoid ambiguity
1075 if ((appendFromClause || pDb->Dbms() == dbmsACCESS) && !tStr.Find(wxT('.')))
1076 {
1077 pSqlStmt += pDb->SQLTableName(queryTableName.c_str());
1078 pSqlStmt += wxT(".");
1079 }
1080 pSqlStmt += pDb->SQLColumnName(colDefs[i].ColName);
1081 if (i + 1 < noCols)
1082 pSqlStmt += wxT(",");
1083 }
1084
1085 // If the datasource supports ROWID, get this column as well. Exception: Don't retrieve
1086 // the ROWID if querying distinct records. The rowid will always be unique.
1087 if (!distinct && CanUpdByROWID())
1088 {
1089 // If joining tables, the base table column names must be qualified to avoid ambiguity
1090 if (appendFromClause || pDb->Dbms() == dbmsACCESS)
1091 {
1092 pSqlStmt += wxT(",");
1093 pSqlStmt += pDb->SQLTableName(queryTableName);
1094 // pSqlStmt += queryTableName;
1095 pSqlStmt += wxT(".ROWID");
1096 }
1097 else
1098 pSqlStmt += wxT(",ROWID");
1099 }
1100
1101 // Append the FROM tablename portion
1102 pSqlStmt += wxT(" FROM ");
1103 pSqlStmt += pDb->SQLTableName(queryTableName);
1104 // pSqlStmt += queryTableName;
1105
1106 // Sybase uses the HOLDLOCK keyword to lock a record during query.
1107 // The HOLDLOCK keyword follows the table name in the from clause.
1108 // Each table in the from clause must specify HOLDLOCK or
1109 // NOHOLDLOCK (the default). Note: The "FOR UPDATE" clause
1110 // is parsed but ignored in SYBASE Transact-SQL.
1111 if (selectForUpdate && (pDb->Dbms() == dbmsSYBASE_ASA || pDb->Dbms() == dbmsSYBASE_ASE))
1112 pSqlStmt += wxT(" HOLDLOCK");
1113
1114 if (appendFromClause)
1115 pSqlStmt += from;
1116
1117 // Append the WHERE clause. Either append the where clause for the class
1118 // or build a where clause. The typeOfSelect determines this.
1119 switch(typeOfSelect)
1120 {
1121 case DB_SELECT_WHERE:
1122 #if wxODBC_BACKWARD_COMPATABILITY
1123 if (where && wxStrlen(where)) // May not want a where clause!!!
1124 #else
1125 if (where.Length()) // May not want a where clause!!!
1126 #endif
1127 {
1128 pSqlStmt += wxT(" WHERE ");
1129 pSqlStmt += where;
1130 }
1131 break;
1132 case DB_SELECT_KEYFIELDS:
1133 BuildWhereClause(whereClause, DB_WHERE_KEYFIELDS);
1134 if (whereClause.Length())
1135 {
1136 pSqlStmt += wxT(" WHERE ");
1137 pSqlStmt += whereClause;
1138 }
1139 break;
1140 case DB_SELECT_MATCHING:
1141 BuildWhereClause(whereClause, DB_WHERE_MATCHING);
1142 if (whereClause.Length())
1143 {
1144 pSqlStmt += wxT(" WHERE ");
1145 pSqlStmt += whereClause;
1146 }
1147 break;
1148 }
1149
1150 // Append the ORDER BY clause
1151 #if wxODBC_BACKWARD_COMPATABILITY
1152 if (orderBy && wxStrlen(orderBy))
1153 #else
1154 if (orderBy.Length())
1155 #endif
1156 {
1157 pSqlStmt += wxT(" ORDER BY ");
1158 pSqlStmt += orderBy;
1159 }
1160
1161 // SELECT FOR UPDATE if told to do so and the datasource is capable. Sybase
1162 // parses the FOR UPDATE clause but ignores it. See the comment above on the
1163 // HOLDLOCK for Sybase.
1164 if (selectForUpdate && CanSelectForUpdate())
1165 pSqlStmt += wxT(" FOR UPDATE");
1166
1167 } // wxDbTable::BuildSelectStmt()
1168
1169
1170 /***** DEPRECATED: use wxDbTable::BuildSelectStmt(wxString &....) form *****/
1171 void wxDbTable::BuildSelectStmt(wxChar *pSqlStmt, int typeOfSelect, bool distinct)
1172 {
1173 wxString tempSqlStmt;
1174 BuildSelectStmt(tempSqlStmt, typeOfSelect, distinct);
1175 wxStrcpy(pSqlStmt, tempSqlStmt);
1176 } // wxDbTable::BuildSelectStmt()
1177
1178
1179 /********** wxDbTable::BuildUpdateStmt() **********/
1180 void wxDbTable::BuildUpdateStmt(wxString &pSqlStmt, int typeOfUpd, const wxString &pWhereClause)
1181 {
1182 wxASSERT(!queryOnly);
1183 if (queryOnly)
1184 return;
1185
1186 wxString whereClause;
1187 whereClause.Empty();
1188
1189 bool firstColumn = TRUE;
1190
1191 pSqlStmt.Printf(wxT("UPDATE %s SET "),
1192 pDb->SQLTableName(tableName.c_str()).c_str());
1193
1194 // Append a list of columns to be updated
1195 int i;
1196 for (i = 0; i < noCols; i++)
1197 {
1198 // Only append Updateable columns
1199 if (colDefs[i].Updateable)
1200 {
1201 if (!firstColumn)
1202 pSqlStmt += wxT(",");
1203 else
1204 firstColumn = FALSE;
1205
1206 pSqlStmt += pDb->SQLColumnName(colDefs[i].ColName);
1207 // pSqlStmt += colDefs[i].ColName;
1208 pSqlStmt += wxT(" = ?");
1209 }
1210 }
1211
1212 // Append the WHERE clause to the SQL UPDATE statement
1213 pSqlStmt += wxT(" WHERE ");
1214 switch(typeOfUpd)
1215 {
1216 case DB_UPD_KEYFIELDS:
1217 // If the datasource supports the ROWID column, build
1218 // the where on ROWID for efficiency purposes.
1219 // e.g. UPDATE PARTS SET Col1 = ?, Col2 = ? WHERE ROWID = '111.222.333'
1220 if (CanUpdByROWID())
1221 {
1222 SDWORD cb;
1223 wxChar rowid[wxDB_ROWID_LEN+1];
1224
1225 // Get the ROWID value. If not successful retreiving the ROWID,
1226 // simply fall down through the code and build the WHERE clause
1227 // based on the key fields.
1228 if (SQLGetData(hstmt, (UWORD)(noCols+1), SQL_C_CHAR, (UCHAR*) rowid, wxDB_ROWID_LEN, &cb) == SQL_SUCCESS)
1229 {
1230 pSqlStmt += wxT("ROWID = '");
1231 pSqlStmt += rowid;
1232 pSqlStmt += wxT("'");
1233 break;
1234 }
1235 }
1236 // Unable to delete by ROWID, so build a WHERE
1237 // clause based on the keyfields.
1238 BuildWhereClause(whereClause, DB_WHERE_KEYFIELDS);
1239 pSqlStmt += whereClause;
1240 break;
1241 case DB_UPD_WHERE:
1242 pSqlStmt += pWhereClause;
1243 break;
1244 }
1245 } // BuildUpdateStmt()
1246
1247
1248 /***** DEPRECATED: use wxDbTable::BuildUpdateStmt(wxString &....) form *****/
1249 void wxDbTable::BuildUpdateStmt(wxChar *pSqlStmt, int typeOfUpd, const wxString &pWhereClause)
1250 {
1251 wxString tempSqlStmt;
1252 BuildUpdateStmt(tempSqlStmt, typeOfUpd, pWhereClause);
1253 wxStrcpy(pSqlStmt, tempSqlStmt);
1254 } // BuildUpdateStmt()
1255
1256
1257 /********** wxDbTable::BuildWhereClause() **********/
1258 void wxDbTable::BuildWhereClause(wxString &pWhereClause, int typeOfWhere,
1259 const wxString &qualTableName, bool useLikeComparison)
1260 /*
1261 * Note: BuildWhereClause() currently ignores timestamp columns.
1262 * They are not included as part of the where clause.
1263 */
1264 {
1265 bool moreThanOneColumn = FALSE;
1266 wxString colValue;
1267
1268 // Loop through the columns building a where clause as you go
1269 int colNo;
1270 for (colNo = 0; colNo < noCols; colNo++)
1271 {
1272 // Determine if this column should be included in the WHERE clause
1273 if ((typeOfWhere == DB_WHERE_KEYFIELDS && colDefs[colNo].KeyField) ||
1274 (typeOfWhere == DB_WHERE_MATCHING && (!IsColNull(colNo))))
1275 {
1276 // Skip over timestamp columns
1277 if (colDefs[colNo].SqlCtype == SQL_C_TIMESTAMP)
1278 continue;
1279 // If there is more than 1 column, join them with the keyword "AND"
1280 if (moreThanOneColumn)
1281 pWhereClause += wxT(" AND ");
1282 else
1283 moreThanOneColumn = TRUE;
1284
1285 // Concatenate where phrase for the column
1286 wxString tStr = colDefs[colNo].ColName;
1287
1288 if (qualTableName.Length() && !tStr.Find(wxT('.')))
1289 {
1290 pWhereClause += pDb->SQLTableName(qualTableName);
1291 pWhereClause += wxT(".");
1292 }
1293 pWhereClause += pDb->SQLColumnName(colDefs[colNo].ColName);
1294
1295 if (useLikeComparison && (colDefs[colNo].SqlCtype == SQL_C_CHAR))
1296 pWhereClause += wxT(" LIKE ");
1297 else
1298 pWhereClause += wxT(" = ");
1299
1300 switch(colDefs[colNo].SqlCtype)
1301 {
1302 case SQL_C_CHAR:
1303 colValue.Printf(wxT("'%s'"), (UCHAR FAR *) colDefs[colNo].PtrDataObj);
1304 break;
1305 case SQL_C_SHORT:
1306 case SQL_C_SSHORT:
1307 colValue.Printf(wxT("%hi"), *((SWORD *) colDefs[colNo].PtrDataObj));
1308 break;
1309 case SQL_C_USHORT:
1310 colValue.Printf(wxT("%hu"), *((UWORD *) colDefs[colNo].PtrDataObj));
1311 break;
1312 case SQL_C_LONG:
1313 case SQL_C_SLONG:
1314 colValue.Printf(wxT("%li"), *((SDWORD *) colDefs[colNo].PtrDataObj));
1315 break;
1316 case SQL_C_ULONG:
1317 colValue.Printf(wxT("%lu"), *((UDWORD *) colDefs[colNo].PtrDataObj));
1318 break;
1319 case SQL_C_FLOAT:
1320 colValue.Printf(wxT("%.6f"), *((SFLOAT *) colDefs[colNo].PtrDataObj));
1321 break;
1322 case SQL_C_DOUBLE:
1323 colValue.Printf(wxT("%.6f"), *((SDOUBLE *) colDefs[colNo].PtrDataObj));
1324 break;
1325 default:
1326 {
1327 wxString strMsg;
1328 strMsg.Printf(wxT("wxDbTable::bindParams(): Unknown column type for colDefs %d colName %s"),
1329 colNo,colDefs[colNo].ColName);
1330 wxFAIL_MSG(wxT(strMsg));
1331 }
1332 break;
1333 }
1334 pWhereClause += colValue;
1335 }
1336 }
1337 } // wxDbTable::BuildWhereClause()
1338
1339
1340 /***** DEPRECATED: use wxDbTable::BuildWhereClause(wxString &....) form *****/
1341 void wxDbTable::BuildWhereClause(wxChar *pWhereClause, int typeOfWhere,
1342 const wxString &qualTableName, bool useLikeComparison)
1343 {
1344 wxString tempSqlStmt;
1345 BuildWhereClause(tempSqlStmt, typeOfWhere, qualTableName, useLikeComparison);
1346 wxStrcpy(pWhereClause, tempSqlStmt);
1347 } // wxDbTable::BuildWhereClause()
1348
1349
1350 /********** wxDbTable::GetRowNum() **********/
1351 UWORD wxDbTable::GetRowNum(void)
1352 {
1353 UDWORD rowNum;
1354
1355 if (SQLGetStmtOption(hstmt, SQL_ROW_NUMBER, (UCHAR*) &rowNum) != SQL_SUCCESS)
1356 {
1357 pDb->DispAllErrors(henv, hdbc, hstmt);
1358 return(0);
1359 }
1360
1361 // Completed successfully
1362 return((UWORD) rowNum);
1363
1364 } // wxDbTable::GetRowNum()
1365
1366
1367 /********** wxDbTable::CloseCursor() **********/
1368 bool wxDbTable::CloseCursor(HSTMT cursor)
1369 {
1370 if (SQLFreeStmt(cursor, SQL_CLOSE) != SQL_SUCCESS)
1371 return(pDb->DispAllErrors(henv, hdbc, cursor));
1372
1373 // Completed successfully
1374 return(TRUE);
1375
1376 } // wxDbTable::CloseCursor()
1377
1378
1379 /********** wxDbTable::CreateTable() **********/
1380 bool wxDbTable::CreateTable(bool attemptDrop)
1381 {
1382 if (!pDb)
1383 return FALSE;
1384
1385 int i, j;
1386 wxString sqlStmt;
1387
1388 #ifdef DBDEBUG_CONSOLE
1389 cout << wxT("Creating Table ") << tableName << wxT("...") << endl;
1390 #endif
1391
1392 // Drop table first
1393 if (attemptDrop && !DropTable())
1394 return FALSE;
1395
1396 // Create the table
1397 #ifdef DBDEBUG_CONSOLE
1398 for (i = 0; i < noCols; i++)
1399 {
1400 // Exclude derived columns since they are NOT part of the base table
1401 if (colDefs[i].DerivedCol)
1402 continue;
1403 cout << i + 1 << wxT(": ") << colDefs[i].ColName << wxT("; ");
1404 switch(colDefs[i].DbDataType)
1405 {
1406 case DB_DATA_TYPE_VARCHAR:
1407 cout << pDb->GetTypeInfVarchar().TypeName << wxT("(") << colDefs[i].SzDataObj << wxT(")");
1408 break;
1409 case DB_DATA_TYPE_INTEGER:
1410 cout << pDb->GetTypeInfInteger().TypeName;
1411 break;
1412 case DB_DATA_TYPE_FLOAT:
1413 cout << pDb->GetTypeInfFloat().TypeName;
1414 break;
1415 case DB_DATA_TYPE_DATE:
1416 cout << pDb->GetTypeInfDate().TypeName;
1417 break;
1418 case DB_DATA_TYPE_BLOB:
1419 cout << pDb->GetTypeInfBlob().TypeName;
1420 break;
1421 }
1422 cout << endl;
1423 }
1424 #endif
1425
1426 // Build a CREATE TABLE string from the colDefs structure.
1427 bool needComma = FALSE;
1428
1429 sqlStmt.Printf(wxT("CREATE TABLE %s ("),
1430 pDb->SQLTableName(tableName.c_str()).c_str());
1431
1432 for (i = 0; i < noCols; i++)
1433 {
1434 // Exclude derived columns since they are NOT part of the base table
1435 if (colDefs[i].DerivedCol)
1436 continue;
1437 // Comma Delimiter
1438 if (needComma)
1439 sqlStmt += wxT(",");
1440 // Column Name
1441 sqlStmt += pDb->SQLColumnName(colDefs[i].ColName);
1442 // sqlStmt += colDefs[i].ColName;
1443 sqlStmt += wxT(" ");
1444 // Column Type
1445 switch(colDefs[i].DbDataType)
1446 {
1447 case DB_DATA_TYPE_VARCHAR:
1448 sqlStmt += pDb->GetTypeInfVarchar().TypeName;
1449 break;
1450 case DB_DATA_TYPE_INTEGER:
1451 sqlStmt += pDb->GetTypeInfInteger().TypeName;
1452 break;
1453 case DB_DATA_TYPE_FLOAT:
1454 sqlStmt += pDb->GetTypeInfFloat().TypeName;
1455 break;
1456 case DB_DATA_TYPE_DATE:
1457 sqlStmt += pDb->GetTypeInfDate().TypeName;
1458 break;
1459 case DB_DATA_TYPE_BLOB:
1460 sqlStmt += pDb->GetTypeInfBlob().TypeName;
1461 break;
1462 }
1463 // For varchars, append the size of the string
1464 if (colDefs[i].DbDataType == DB_DATA_TYPE_VARCHAR &&
1465 (pDb->Dbms() != dbmsMY_SQL || pDb->GetTypeInfVarchar().TypeName != _T("text")))// ||
1466 // colDefs[i].DbDataType == DB_DATA_TYPE_BLOB)
1467 {
1468 wxString s;
1469 s.Printf(wxT("(%d)"), colDefs[i].SzDataObj);
1470 sqlStmt += s;
1471 }
1472
1473 if (pDb->Dbms() == dbmsDB2 ||
1474 pDb->Dbms() == dbmsMY_SQL ||
1475 pDb->Dbms() == dbmsSYBASE_ASE ||
1476 pDb->Dbms() == dbmsINTERBASE ||
1477 pDb->Dbms() == dbmsMS_SQL_SERVER)
1478 {
1479 if (colDefs[i].KeyField)
1480 {
1481 sqlStmt += wxT(" NOT NULL");
1482 }
1483 }
1484
1485 needComma = TRUE;
1486 }
1487 // If there is a primary key defined, include it in the create statement
1488 for (i = j = 0; i < noCols; i++)
1489 {
1490 if (colDefs[i].KeyField)
1491 {
1492 j++;
1493 break;
1494 }
1495 }
1496 if ( j && (pDb->Dbms() != dbmsDBASE)
1497 && (pDb->Dbms() != dbmsXBASE_SEQUITER) ) // Found a keyfield
1498 {
1499 switch (pDb->Dbms())
1500 {
1501 case dbmsACCESS:
1502 case dbmsINFORMIX:
1503 case dbmsSYBASE_ASA:
1504 case dbmsSYBASE_ASE:
1505 case dbmsMY_SQL:
1506 {
1507 // MySQL goes out on this one. We also declare the relevant key NON NULL above
1508 sqlStmt += wxT(",PRIMARY KEY (");
1509 break;
1510 }
1511 default:
1512 {
1513 sqlStmt += wxT(",CONSTRAINT ");
1514 // DB2 is limited to 18 characters for index names
1515 if (pDb->Dbms() == dbmsDB2)
1516 {
1517 wxASSERT_MSG((tableName && wxStrlen(tableName) <= 13), wxT("DB2 table/index names must be no longer than 13 characters in length.\n\nTruncating table name to 13 characters."));
1518 sqlStmt += pDb->SQLTableName(tableName.substr(0, 13).c_str());
1519 // sqlStmt += tableName.substr(0, 13);
1520 }
1521 else
1522 sqlStmt += pDb->SQLTableName(tableName.c_str());
1523 // sqlStmt += tableName;
1524
1525 sqlStmt += wxT("_PIDX PRIMARY KEY (");
1526 break;
1527 }
1528 }
1529
1530 // List column name(s) of column(s) comprising the primary key
1531 for (i = j = 0; i < noCols; i++)
1532 {
1533 if (colDefs[i].KeyField)
1534 {
1535 if (j++) // Multi part key, comma separate names
1536 sqlStmt += wxT(",");
1537 sqlStmt += pDb->SQLColumnName(colDefs[i].ColName);
1538
1539 if (pDb->Dbms() == dbmsMY_SQL &&
1540 colDefs[i].DbDataType == DB_DATA_TYPE_VARCHAR)
1541 {
1542 wxString s;
1543 s.Printf(wxT("(%d)"), colDefs[i].SzDataObj);
1544 sqlStmt += s;
1545 }
1546 }
1547 }
1548 sqlStmt += wxT(")");
1549
1550 if (pDb->Dbms() == dbmsINFORMIX ||
1551 pDb->Dbms() == dbmsSYBASE_ASA ||
1552 pDb->Dbms() == dbmsSYBASE_ASE)
1553 {
1554 sqlStmt += wxT(" CONSTRAINT ");
1555 sqlStmt += pDb->SQLTableName(tableName);
1556 // sqlStmt += tableName;
1557 sqlStmt += wxT("_PIDX");
1558 }
1559 }
1560 // Append the closing parentheses for the create table statement
1561 sqlStmt += wxT(")");
1562
1563 pDb->WriteSqlLog(sqlStmt);
1564
1565 #ifdef DBDEBUG_CONSOLE
1566 cout << endl << sqlStmt.c_str() << endl;
1567 #endif
1568
1569 // Execute the CREATE TABLE statement
1570 RETCODE retcode = SQLExecDirect(hstmt, (SQLTCHAR FAR *) sqlStmt.c_str(), SQL_NTS);
1571 if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
1572 {
1573 pDb->DispAllErrors(henv, hdbc, hstmt);
1574 pDb->RollbackTrans();
1575 CloseCursor(hstmt);
1576 return(FALSE);
1577 }
1578
1579 // Commit the transaction and close the cursor
1580 if (!pDb->CommitTrans())
1581 return(FALSE);
1582 if (!CloseCursor(hstmt))
1583 return(FALSE);
1584
1585 // Database table created successfully
1586 return(TRUE);
1587
1588 } // wxDbTable::CreateTable()
1589
1590
1591 /********** wxDbTable::DropTable() **********/
1592 bool wxDbTable::DropTable()
1593 {
1594 // NOTE: This function returns TRUE if the Table does not exist, but
1595 // only for identified databases. Code will need to be added
1596 // below for any other databases when those databases are defined
1597 // to handle this situation consistently
1598
1599 wxString sqlStmt;
1600
1601 sqlStmt.Printf(wxT("DROP TABLE %s"),
1602 pDb->SQLTableName(tableName.c_str()).c_str());
1603
1604 pDb->WriteSqlLog(sqlStmt);
1605
1606 #ifdef DBDEBUG_CONSOLE
1607 cout << endl << sqlStmt.c_str() << endl;
1608 #endif
1609
1610 RETCODE retcode = SQLExecDirect(hstmt, (SQLTCHAR FAR *) sqlStmt.c_str(), SQL_NTS);
1611 if (retcode != SQL_SUCCESS)
1612 {
1613 // Check for "Base table not found" error and ignore
1614 pDb->GetNextError(henv, hdbc, hstmt);
1615 if (wxStrcmp(pDb->sqlState, wxT("S0002")) /*&&
1616 wxStrcmp(pDb->sqlState, wxT("S1000"))*/) // "Base table not found"
1617 {
1618 // Check for product specific error codes
1619 if (!((pDb->Dbms() == dbmsSYBASE_ASA && !wxStrcmp(pDb->sqlState,wxT("42000"))) || // 5.x (and lower?)
1620 (pDb->Dbms() == dbmsSYBASE_ASE && !wxStrcmp(pDb->sqlState,wxT("37000"))) ||
1621 (pDb->Dbms() == dbmsPERVASIVE_SQL && !wxStrcmp(pDb->sqlState,wxT("S1000"))) || // Returns an S1000 then an S0002
1622 (pDb->Dbms() == dbmsPOSTGRES && !wxStrcmp(pDb->sqlState,wxT("08S01")))))
1623 {
1624 pDb->DispNextError();
1625 pDb->DispAllErrors(henv, hdbc, hstmt);
1626 pDb->RollbackTrans();
1627 // CloseCursor(hstmt);
1628 return(FALSE);
1629 }
1630 }
1631 }
1632
1633 // Commit the transaction and close the cursor
1634 if (! pDb->CommitTrans())
1635 return(FALSE);
1636 if (! CloseCursor(hstmt))
1637 return(FALSE);
1638
1639 return(TRUE);
1640 } // wxDbTable::DropTable()
1641
1642
1643 /********** wxDbTable::CreateIndex() **********/
1644 bool wxDbTable::CreateIndex(const wxString &idxName, bool unique, UWORD noIdxCols,
1645 wxDbIdxDef *pIdxDefs, bool attemptDrop)
1646 {
1647 wxString sqlStmt;
1648
1649 // Drop the index first
1650 if (attemptDrop && !DropIndex(idxName))
1651 return (FALSE);
1652
1653 // MySQL (and possibly Sybase ASE?? - gt) require that any columns which are used as portions
1654 // of an index have the columns defined as "NOT NULL". During initial table creation though,
1655 // it may not be known which columns are necessarily going to be part of an index (e.g. the
1656 // table was created, then months later you determine that an additional index while
1657 // give better performance, so you want to add an index).
1658 //
1659 // The following block of code will modify the column definition to make the column be
1660 // defined with the "NOT NULL" qualifier.
1661 if (pDb->Dbms() == dbmsMY_SQL)
1662 {
1663 wxString sqlStmt;
1664 int i;
1665 bool ok = TRUE;
1666 for (i = 0; i < noIdxCols && ok; i++)
1667 {
1668 int j = 0;
1669 bool found = FALSE;
1670 // Find the column definition that has the ColName that matches the
1671 // index column name. We need to do this to get the DB_DATA_TYPE of
1672 // the index column, as MySQL's syntax for the ALTER column requires
1673 // this information
1674 while (!found && (j < this->noCols))
1675 {
1676 if (wxStrcmp(colDefs[j].ColName,pIdxDefs[i].ColName) == 0)
1677 found = TRUE;
1678 if (!found)
1679 j++;
1680 }
1681
1682 if (found)
1683 {
1684 ok = pDb->ModifyColumn(tableName, pIdxDefs[i].ColName,
1685 colDefs[j].DbDataType, colDefs[j].SzDataObj,
1686 wxT("NOT NULL"));
1687
1688 if (!ok)
1689 {
1690 #if 0
1691 // retcode is not used
1692 wxODBC_ERRORS retcode;
1693 // Oracle returns a DB_ERR_GENERAL_ERROR if the column is already
1694 // defined to be NOT NULL, but reportedly MySQL doesn't mind.
1695 // This line is just here for debug checking of the value
1696 retcode = (wxODBC_ERRORS)pDb->DB_STATUS;
1697 #endif
1698 }
1699 }
1700 else
1701 ok = FALSE;
1702 }
1703 if (ok)
1704 pDb->CommitTrans();
1705 else
1706 {
1707 pDb->RollbackTrans();
1708 return(FALSE);
1709 }
1710 }
1711
1712 // Build a CREATE INDEX statement
1713 sqlStmt = wxT("CREATE ");
1714 if (unique)
1715 sqlStmt += wxT("UNIQUE ");
1716
1717 sqlStmt += wxT("INDEX ");
1718 sqlStmt += pDb->SQLTableName(idxName);
1719 sqlStmt += wxT(" ON ");
1720
1721 sqlStmt += pDb->SQLTableName(tableName);
1722 // sqlStmt += tableName;
1723 sqlStmt += wxT(" (");
1724
1725 // Append list of columns making up index
1726 int i;
1727 for (i = 0; i < noIdxCols; i++)
1728 {
1729 sqlStmt += pDb->SQLColumnName(pIdxDefs[i].ColName);
1730 // sqlStmt += pIdxDefs[i].ColName;
1731
1732 // MySQL requires a key length on VARCHAR keys
1733 if ( pDb->Dbms() == dbmsMY_SQL )
1734 {
1735 // Find the details on this column
1736 int j;
1737 for ( j = 0; j < noCols; ++j )
1738 {
1739 if ( wxStrcmp( pIdxDefs[i].ColName, colDefs[j].ColName ) == 0 )
1740 {
1741 break;
1742 }
1743 }
1744 if ( colDefs[j].DbDataType == DB_DATA_TYPE_VARCHAR)
1745 {
1746 wxString s;
1747 s.Printf(wxT("(%d)"), colDefs[i].SzDataObj);
1748 sqlStmt += s;
1749 }
1750 }
1751
1752 // Postgres and SQL Server 7 do not support the ASC/DESC keywords for index columns
1753 if (!((pDb->Dbms() == dbmsMS_SQL_SERVER) && (wxStrncmp(pDb->dbInf.dbmsVer,_T("07"),2)==0)) &&
1754 !(pDb->Dbms() == dbmsPOSTGRES))
1755 {
1756 if (pIdxDefs[i].Ascending)
1757 sqlStmt += wxT(" ASC");
1758 else
1759 sqlStmt += wxT(" DESC");
1760 }
1761 else
1762 wxASSERT_MSG(pIdxDefs[i].Ascending, _T("Datasource does not support DESCending index columns"));
1763
1764 if ((i + 1) < noIdxCols)
1765 sqlStmt += wxT(",");
1766 }
1767
1768 // Append closing parentheses
1769 sqlStmt += wxT(")");
1770
1771 pDb->WriteSqlLog(sqlStmt);
1772
1773 #ifdef DBDEBUG_CONSOLE
1774 cout << endl << sqlStmt.c_str() << endl << endl;
1775 #endif
1776
1777 // Execute the CREATE INDEX statement
1778 if (SQLExecDirect(hstmt, (SQLTCHAR FAR *) sqlStmt.c_str(), SQL_NTS) != SQL_SUCCESS)
1779 {
1780 pDb->DispAllErrors(henv, hdbc, hstmt);
1781 pDb->RollbackTrans();
1782 CloseCursor(hstmt);
1783 return(FALSE);
1784 }
1785
1786 // Commit the transaction and close the cursor
1787 if (! pDb->CommitTrans())
1788 return(FALSE);
1789 if (! CloseCursor(hstmt))
1790 return(FALSE);
1791
1792 // Index Created Successfully
1793 return(TRUE);
1794
1795 } // wxDbTable::CreateIndex()
1796
1797
1798 /********** wxDbTable::DropIndex() **********/
1799 bool wxDbTable::DropIndex(const wxString &idxName)
1800 {
1801 // NOTE: This function returns TRUE if the Index does not exist, but
1802 // only for identified databases. Code will need to be added
1803 // below for any other databases when those databases are defined
1804 // to handle this situation consistently
1805
1806 wxString sqlStmt;
1807
1808 if (pDb->Dbms() == dbmsACCESS || pDb->Dbms() == dbmsMY_SQL ||
1809 pDb->Dbms() == dbmsDBASE /*|| Paradox needs this syntax too when we add support*/)
1810 sqlStmt.Printf(wxT("DROP INDEX %s ON %s"),
1811 pDb->SQLTableName(idxName.c_str()).c_str(),
1812 pDb->SQLTableName(tableName.c_str()).c_str());
1813 else if ((pDb->Dbms() == dbmsMS_SQL_SERVER) ||
1814 (pDb->Dbms() == dbmsSYBASE_ASE) ||
1815 (pDb->Dbms() == dbmsXBASE_SEQUITER))
1816 sqlStmt.Printf(wxT("DROP INDEX %s.%s"),
1817 pDb->SQLTableName(tableName.c_str()).c_str(),
1818 pDb->SQLTableName(idxName.c_str()).c_str());
1819 else
1820 sqlStmt.Printf(wxT("DROP INDEX %s"),
1821 pDb->SQLTableName(idxName.c_str()).c_str());
1822
1823 pDb->WriteSqlLog(sqlStmt);
1824
1825 #ifdef DBDEBUG_CONSOLE
1826 cout << endl << sqlStmt.c_str() << endl;
1827 #endif
1828
1829 if (SQLExecDirect(hstmt, (SQLTCHAR FAR *) sqlStmt.c_str(), SQL_NTS) != SQL_SUCCESS)
1830 {
1831 // Check for "Index not found" error and ignore
1832 pDb->GetNextError(henv, hdbc, hstmt);
1833 if (wxStrcmp(pDb->sqlState,wxT("S0012"))) // "Index not found"
1834 {
1835 // Check for product specific error codes
1836 if (!((pDb->Dbms() == dbmsSYBASE_ASA && !wxStrcmp(pDb->sqlState,wxT("42000"))) || // v5.x (and lower?)
1837 (pDb->Dbms() == dbmsSYBASE_ASE && !wxStrcmp(pDb->sqlState,wxT("37000"))) ||
1838 (pDb->Dbms() == dbmsMS_SQL_SERVER && !wxStrcmp(pDb->sqlState,wxT("S1000"))) ||
1839 (pDb->Dbms() == dbmsINTERBASE && !wxStrcmp(pDb->sqlState,wxT("S1000"))) ||
1840 (pDb->Dbms() == dbmsSYBASE_ASE && !wxStrcmp(pDb->sqlState,wxT("S0002"))) || // Base table not found
1841 (pDb->Dbms() == dbmsMY_SQL && !wxStrcmp(pDb->sqlState,wxT("42S12"))) || // tested by Christopher Ludwik Marino-Cebulski using v3.23.21beta
1842 (pDb->Dbms() == dbmsPOSTGRES && !wxStrcmp(pDb->sqlState,wxT("08S01")))
1843 ))
1844 {
1845 pDb->DispNextError();
1846 pDb->DispAllErrors(henv, hdbc, hstmt);
1847 pDb->RollbackTrans();
1848 CloseCursor(hstmt);
1849 return(FALSE);
1850 }
1851 }
1852 }
1853
1854 // Commit the transaction and close the cursor
1855 if (! pDb->CommitTrans())
1856 return(FALSE);
1857 if (! CloseCursor(hstmt))
1858 return(FALSE);
1859
1860 return(TRUE);
1861 } // wxDbTable::DropIndex()
1862
1863
1864 /********** wxDbTable::SetOrderByColNums() **********/
1865 bool wxDbTable::SetOrderByColNums(UWORD first, ... )
1866 {
1867 int colNo = first; // using 'int' to be able to look for wxDB_NO_MORE_COLUN_NUMBERS
1868 va_list argptr;
1869
1870 bool abort = FALSE;
1871 wxString tempStr;
1872
1873 va_start(argptr, first); /* Initialize variable arguments. */
1874 while (!abort && (colNo != wxDB_NO_MORE_COLUMN_NUMBERS))
1875 {
1876 // Make sure the passed in column number
1877 // is within the valid range of columns
1878 //
1879 // Valid columns are 0 thru noCols-1
1880 if (colNo >= noCols || colNo < 0)
1881 {
1882 abort = TRUE;
1883 continue;
1884 }
1885
1886 if (colNo != first)
1887 tempStr += wxT(",");
1888
1889 tempStr += colDefs[colNo].ColName;
1890 colNo = va_arg (argptr, int);
1891 }
1892 va_end (argptr); /* Reset variable arguments. */
1893
1894 SetOrderByClause(tempStr);
1895
1896 return (!abort);
1897 } // wxDbTable::SetOrderByColNums()
1898
1899
1900 /********** wxDbTable::Insert() **********/
1901 int wxDbTable::Insert(void)
1902 {
1903 wxASSERT(!queryOnly);
1904 if (queryOnly || !insertable)
1905 return(DB_FAILURE);
1906
1907 bindInsertParams();
1908
1909 // Insert the record by executing the already prepared insert statement
1910 RETCODE retcode;
1911 retcode=SQLExecute(hstmtInsert);
1912 if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO &&
1913 retcode != SQL_NEED_DATA)
1914 {
1915 // Check to see if integrity constraint was violated
1916 pDb->GetNextError(henv, hdbc, hstmtInsert);
1917 if (! wxStrcmp(pDb->sqlState, wxT("23000"))) // Integrity constraint violated
1918 return(DB_ERR_INTEGRITY_CONSTRAINT_VIOL);
1919 else
1920 {
1921 pDb->DispNextError();
1922 pDb->DispAllErrors(henv, hdbc, hstmtInsert);
1923 return(DB_FAILURE);
1924 }
1925 }
1926 if (retcode == SQL_NEED_DATA)
1927 {
1928 PTR pParmID;
1929 retcode = SQLParamData(hstmtInsert, &pParmID);
1930 while (retcode == SQL_NEED_DATA)
1931 {
1932 // Find the parameter
1933 int i;
1934 for (i=0; i < noCols; i++)
1935 {
1936 if (colDefs[i].PtrDataObj == pParmID)
1937 {
1938 // We found it. Store the parameter.
1939 retcode = SQLPutData(hstmtInsert, pParmID, colDefs[i].SzDataObj);
1940 if (retcode != SQL_SUCCESS)
1941 {
1942 pDb->DispNextError();
1943 pDb->DispAllErrors(henv, hdbc, hstmtInsert);
1944 return(DB_FAILURE);
1945 }
1946 break;
1947 }
1948 }
1949 }
1950 }
1951
1952 // Record inserted into the datasource successfully
1953 return(DB_SUCCESS);
1954
1955 } // wxDbTable::Insert()
1956
1957
1958 /********** wxDbTable::Update() **********/
1959 bool wxDbTable::Update(void)
1960 {
1961 wxASSERT(!queryOnly);
1962 if (queryOnly)
1963 return(FALSE);
1964
1965 wxString sqlStmt;
1966
1967 // Build the SQL UPDATE statement
1968 BuildUpdateStmt(sqlStmt, DB_UPD_KEYFIELDS);
1969
1970 pDb->WriteSqlLog(sqlStmt);
1971
1972 #ifdef DBDEBUG_CONSOLE
1973 cout << endl << sqlStmt.c_str() << endl << endl;
1974 #endif
1975
1976 // Execute the SQL UPDATE statement
1977 return(execUpdate(sqlStmt));
1978
1979 } // wxDbTable::Update()
1980
1981
1982 /********** wxDbTable::Update(pSqlStmt) **********/
1983 bool wxDbTable::Update(const wxString &pSqlStmt)
1984 {
1985 wxASSERT(!queryOnly);
1986 if (queryOnly)
1987 return(FALSE);
1988
1989 pDb->WriteSqlLog(pSqlStmt);
1990
1991 return(execUpdate(pSqlStmt));
1992
1993 } // wxDbTable::Update(pSqlStmt)
1994
1995
1996 /********** wxDbTable::UpdateWhere() **********/
1997 bool wxDbTable::UpdateWhere(const wxString &pWhereClause)
1998 {
1999 wxASSERT(!queryOnly);
2000 if (queryOnly)
2001 return(FALSE);
2002
2003 wxString sqlStmt;
2004
2005 // Build the SQL UPDATE statement
2006 BuildUpdateStmt(sqlStmt, DB_UPD_WHERE, pWhereClause);
2007
2008 pDb->WriteSqlLog(sqlStmt);
2009
2010 #ifdef DBDEBUG_CONSOLE
2011 cout << endl << sqlStmt.c_str() << endl << endl;
2012 #endif
2013
2014 // Execute the SQL UPDATE statement
2015 return(execUpdate(sqlStmt));
2016
2017 } // wxDbTable::UpdateWhere()
2018
2019
2020 /********** wxDbTable::Delete() **********/
2021 bool wxDbTable::Delete(void)
2022 {
2023 wxASSERT(!queryOnly);
2024 if (queryOnly)
2025 return(FALSE);
2026
2027 wxString sqlStmt;
2028 sqlStmt.Empty();
2029
2030 // Build the SQL DELETE statement
2031 BuildDeleteStmt(sqlStmt, DB_DEL_KEYFIELDS);
2032
2033 pDb->WriteSqlLog(sqlStmt);
2034
2035 // Execute the SQL DELETE statement
2036 return(execDelete(sqlStmt));
2037
2038 } // wxDbTable::Delete()
2039
2040
2041 /********** wxDbTable::DeleteWhere() **********/
2042 bool wxDbTable::DeleteWhere(const wxString &pWhereClause)
2043 {
2044 wxASSERT(!queryOnly);
2045 if (queryOnly)
2046 return(FALSE);
2047
2048 wxString sqlStmt;
2049 sqlStmt.Empty();
2050
2051 // Build the SQL DELETE statement
2052 BuildDeleteStmt(sqlStmt, DB_DEL_WHERE, pWhereClause);
2053
2054 pDb->WriteSqlLog(sqlStmt);
2055
2056 // Execute the SQL DELETE statement
2057 return(execDelete(sqlStmt));
2058
2059 } // wxDbTable::DeleteWhere()
2060
2061
2062 /********** wxDbTable::DeleteMatching() **********/
2063 bool wxDbTable::DeleteMatching(void)
2064 {
2065 wxASSERT(!queryOnly);
2066 if (queryOnly)
2067 return(FALSE);
2068
2069 wxString sqlStmt;
2070 sqlStmt.Empty();
2071
2072 // Build the SQL DELETE statement
2073 BuildDeleteStmt(sqlStmt, DB_DEL_MATCHING);
2074
2075 pDb->WriteSqlLog(sqlStmt);
2076
2077 // Execute the SQL DELETE statement
2078 return(execDelete(sqlStmt));
2079
2080 } // wxDbTable::DeleteMatching()
2081
2082
2083 /********** wxDbTable::IsColNull() **********/
2084 bool wxDbTable::IsColNull(UWORD colNo) const
2085 {
2086 /*
2087 This logic is just not right. It would indicate TRUE
2088 if a numeric field were set to a value of 0.
2089
2090 switch(colDefs[colNo].SqlCtype)
2091 {
2092 case SQL_C_CHAR:
2093 return(((UCHAR FAR *) colDefs[colNo].PtrDataObj)[0] == 0);
2094 case SQL_C_SSHORT:
2095 return(( *((SWORD *) colDefs[colNo].PtrDataObj)) == 0);
2096 case SQL_C_USHORT:
2097 return(( *((UWORD*) colDefs[colNo].PtrDataObj)) == 0);
2098 case SQL_C_SLONG:
2099 return(( *((SDWORD *) colDefs[colNo].PtrDataObj)) == 0);
2100 case SQL_C_ULONG:
2101 return(( *((UDWORD *) colDefs[colNo].PtrDataObj)) == 0);
2102 case SQL_C_FLOAT:
2103 return(( *((SFLOAT *) colDefs[colNo].PtrDataObj)) == 0);
2104 case SQL_C_DOUBLE:
2105 return((*((SDOUBLE *) colDefs[colNo].PtrDataObj)) == 0);
2106 case SQL_C_TIMESTAMP:
2107 TIMESTAMP_STRUCT *pDt;
2108 pDt = (TIMESTAMP_STRUCT *) colDefs[colNo].PtrDataObj;
2109 if (pDt->year == 0 && pDt->month == 0 && pDt->day == 0)
2110 return(TRUE);
2111 else
2112 return(FALSE);
2113 default:
2114 return(TRUE);
2115 }
2116 */
2117 return (colDefs[colNo].Null);
2118 } // wxDbTable::IsColNull()
2119
2120
2121 /********** wxDbTable::CanSelectForUpdate() **********/
2122 bool wxDbTable::CanSelectForUpdate(void)
2123 {
2124 if (queryOnly)
2125 return FALSE;
2126
2127 if (pDb->Dbms() == dbmsMY_SQL)
2128 return FALSE;
2129
2130 if ((pDb->Dbms() == dbmsORACLE) ||
2131 (pDb->dbInf.posStmts & SQL_PS_SELECT_FOR_UPDATE))
2132 return(TRUE);
2133 else
2134 return(FALSE);
2135
2136 } // wxDbTable::CanSelectForUpdate()
2137
2138
2139 /********** wxDbTable::CanUpdByROWID() **********/
2140 bool wxDbTable::CanUpdByROWID(void)
2141 {
2142 /*
2143 * NOTE: Returning FALSE for now until this can be debugged,
2144 * as the ROWID is not getting updated correctly
2145 */
2146 return FALSE;
2147 /*
2148 if (pDb->Dbms() == dbmsORACLE)
2149 return(TRUE);
2150 else
2151 return(FALSE);
2152 */
2153 } // wxDbTable::CanUpdByROWID()
2154
2155
2156 /********** wxDbTable::IsCursorClosedOnCommit() **********/
2157 bool wxDbTable::IsCursorClosedOnCommit(void)
2158 {
2159 if (pDb->dbInf.cursorCommitBehavior == SQL_CB_PRESERVE)
2160 return(FALSE);
2161 else
2162 return(TRUE);
2163
2164 } // wxDbTable::IsCursorClosedOnCommit()
2165
2166
2167
2168 /********** wxDbTable::ClearMemberVar() **********/
2169 void wxDbTable::ClearMemberVar(UWORD colNo, bool setToNull)
2170 {
2171 wxASSERT(colNo < noCols);
2172
2173 switch(colDefs[colNo].SqlCtype)
2174 {
2175 case SQL_C_CHAR:
2176 ((UCHAR FAR *) colDefs[colNo].PtrDataObj)[0] = 0;
2177 break;
2178 case SQL_C_SSHORT:
2179 *((SWORD *) colDefs[colNo].PtrDataObj) = 0;
2180 break;
2181 case SQL_C_USHORT:
2182 *((UWORD*) colDefs[colNo].PtrDataObj) = 0;
2183 break;
2184 case SQL_C_SLONG:
2185 *((SDWORD *) colDefs[colNo].PtrDataObj) = 0;
2186 break;
2187 case SQL_C_ULONG:
2188 *((UDWORD *) colDefs[colNo].PtrDataObj) = 0;
2189 break;
2190 case SQL_C_FLOAT:
2191 *((SFLOAT *) colDefs[colNo].PtrDataObj) = 0.0f;
2192 break;
2193 case SQL_C_DOUBLE:
2194 *((SDOUBLE *) colDefs[colNo].PtrDataObj) = 0.0f;
2195 break;
2196 case SQL_C_TIMESTAMP:
2197 TIMESTAMP_STRUCT *pDt;
2198 pDt = (TIMESTAMP_STRUCT *) colDefs[colNo].PtrDataObj;
2199 pDt->year = 0;
2200 pDt->month = 0;
2201 pDt->day = 0;
2202 pDt->hour = 0;
2203 pDt->minute = 0;
2204 pDt->second = 0;
2205 pDt->fraction = 0;
2206 break;
2207 }
2208
2209 if (setToNull)
2210 SetColNull(colNo);
2211 } // wxDbTable::ClearMemberVar()
2212
2213
2214 /********** wxDbTable::ClearMemberVars() **********/
2215 void wxDbTable::ClearMemberVars(bool setToNull)
2216 {
2217 int i;
2218
2219 // Loop through the columns setting each member variable to zero
2220 for (i=0; i < noCols; i++)
2221 ClearMemberVar(i,setToNull);
2222
2223 } // wxDbTable::ClearMemberVars()
2224
2225
2226 /********** wxDbTable::SetQueryTimeout() **********/
2227 bool wxDbTable::SetQueryTimeout(UDWORD nSeconds)
2228 {
2229 if (SQLSetStmtOption(hstmtInsert, SQL_QUERY_TIMEOUT, nSeconds) != SQL_SUCCESS)
2230 return(pDb->DispAllErrors(henv, hdbc, hstmtInsert));
2231 if (SQLSetStmtOption(hstmtUpdate, SQL_QUERY_TIMEOUT, nSeconds) != SQL_SUCCESS)
2232 return(pDb->DispAllErrors(henv, hdbc, hstmtUpdate));
2233 if (SQLSetStmtOption(hstmtDelete, SQL_QUERY_TIMEOUT, nSeconds) != SQL_SUCCESS)
2234 return(pDb->DispAllErrors(henv, hdbc, hstmtDelete));
2235 if (SQLSetStmtOption(hstmtInternal, SQL_QUERY_TIMEOUT, nSeconds) != SQL_SUCCESS)
2236 return(pDb->DispAllErrors(henv, hdbc, hstmtInternal));
2237
2238 // Completed Successfully
2239 return(TRUE);
2240
2241 } // wxDbTable::SetQueryTimeout()
2242
2243
2244 /********** wxDbTable::SetColDefs() **********/
2245 void wxDbTable::SetColDefs(UWORD index, const wxString &fieldName, int dataType, void *pData,
2246 SWORD cType, int size, bool keyField, bool upd,
2247 bool insAllow, bool derivedCol)
2248 {
2249 wxASSERT_MSG( index < noCols,
2250 _T("Specified column index exceeds the maximum number of columns for this table.") );
2251
2252 if (!colDefs) // May happen if the database connection fails
2253 return;
2254
2255 if (fieldName.Length() > (unsigned int) DB_MAX_COLUMN_NAME_LEN)
2256 {
2257 wxStrncpy(colDefs[index].ColName, fieldName, DB_MAX_COLUMN_NAME_LEN);
2258 colDefs[index].ColName[DB_MAX_COLUMN_NAME_LEN] = 0;
2259
2260 #ifdef __WXDEBUG__
2261 wxString tmpMsg;
2262 tmpMsg.Printf(_T("Column name '%s' is too long. Truncated to '%s'."),
2263 fieldName.c_str(),colDefs[index].ColName);
2264 wxFAIL_MSG(tmpMsg);
2265 #endif // __WXDEBUG__
2266 }
2267 else
2268 wxStrcpy(colDefs[index].ColName, fieldName);
2269
2270 colDefs[index].DbDataType = dataType;
2271 colDefs[index].PtrDataObj = pData;
2272 colDefs[index].SqlCtype = cType;
2273 colDefs[index].SzDataObj = size;
2274 colDefs[index].KeyField = keyField;
2275 colDefs[index].DerivedCol = derivedCol;
2276 // Derived columns by definition would NOT be "Insertable" or "Updateable"
2277 if (derivedCol)
2278 {
2279 colDefs[index].Updateable = FALSE;
2280 colDefs[index].InsertAllowed = FALSE;
2281 }
2282 else
2283 {
2284 colDefs[index].Updateable = upd;
2285 colDefs[index].InsertAllowed = insAllow;
2286 }
2287
2288 colDefs[index].Null = FALSE;
2289
2290 } // wxDbTable::SetColDefs()
2291
2292
2293 /********** wxDbTable::SetColDefs() **********/
2294 wxDbColDataPtr* wxDbTable::SetColDefs(wxDbColInf *pColInfs, UWORD numCols)
2295 {
2296 wxASSERT(pColInfs);
2297 wxDbColDataPtr *pColDataPtrs = NULL;
2298
2299 if (pColInfs)
2300 {
2301 UWORD index;
2302
2303 pColDataPtrs = new wxDbColDataPtr[numCols+1];
2304
2305 for (index = 0; index < numCols; index++)
2306 {
2307 // Process the fields
2308 switch (pColInfs[index].dbDataType)
2309 {
2310 case DB_DATA_TYPE_VARCHAR:
2311 pColDataPtrs[index].PtrDataObj = new wxChar[pColInfs[index].bufferLength+1];
2312 pColDataPtrs[index].SzDataObj = pColInfs[index].columnSize;
2313 pColDataPtrs[index].SqlCtype = SQL_C_CHAR;
2314 break;
2315 case DB_DATA_TYPE_INTEGER:
2316 // Can be long or short
2317 if (pColInfs[index].bufferLength == sizeof(long))
2318 {
2319 pColDataPtrs[index].PtrDataObj = new long;
2320 pColDataPtrs[index].SzDataObj = sizeof(long);
2321 pColDataPtrs[index].SqlCtype = SQL_C_SLONG;
2322 }
2323 else
2324 {
2325 pColDataPtrs[index].PtrDataObj = new short;
2326 pColDataPtrs[index].SzDataObj = sizeof(short);
2327 pColDataPtrs[index].SqlCtype = SQL_C_SSHORT;
2328 }
2329 break;
2330 case DB_DATA_TYPE_FLOAT:
2331 // Can be float or double
2332 if (pColInfs[index].bufferLength == sizeof(float))
2333 {
2334 pColDataPtrs[index].PtrDataObj = new float;
2335 pColDataPtrs[index].SzDataObj = sizeof(float);
2336 pColDataPtrs[index].SqlCtype = SQL_C_FLOAT;
2337 }
2338 else
2339 {
2340 pColDataPtrs[index].PtrDataObj = new double;
2341 pColDataPtrs[index].SzDataObj = sizeof(double);
2342 pColDataPtrs[index].SqlCtype = SQL_C_DOUBLE;
2343 }
2344 break;
2345 case DB_DATA_TYPE_DATE:
2346 pColDataPtrs[index].PtrDataObj = new TIMESTAMP_STRUCT;
2347 pColDataPtrs[index].SzDataObj = sizeof(TIMESTAMP_STRUCT);
2348 pColDataPtrs[index].SqlCtype = SQL_C_TIMESTAMP;
2349 break;
2350 case DB_DATA_TYPE_BLOB:
2351 wxFAIL_MSG(wxT("This form of ::SetColDefs() cannot be used with BLOB columns"));
2352 pColDataPtrs[index].PtrDataObj = /*BLOB ADDITION NEEDED*/NULL;
2353 pColDataPtrs[index].SzDataObj = /*BLOB ADDITION NEEDED*/sizeof(void *);
2354 pColDataPtrs[index].SqlCtype = SQL_VARBINARY;
2355 break;
2356 }
2357 if (pColDataPtrs[index].PtrDataObj != NULL)
2358 SetColDefs (index,pColInfs[index].colName,pColInfs[index].dbDataType, pColDataPtrs[index].PtrDataObj, pColDataPtrs[index].SqlCtype, pColDataPtrs[index].SzDataObj);
2359 else
2360 {
2361 // Unable to build all the column definitions, as either one of
2362 // the calls to "new" failed above, or there was a BLOB field
2363 // to have a column definition for. If BLOBs are to be used,
2364 // the other form of ::SetColDefs() must be used, as it is impossible
2365 // to know the maximum size to create the PtrDataObj to be.
2366 delete [] pColDataPtrs;
2367 return NULL;
2368 }
2369 }
2370 }
2371
2372 return (pColDataPtrs);
2373
2374 } // wxDbTable::SetColDefs()
2375
2376
2377 /********** wxDbTable::SetCursor() **********/
2378 void wxDbTable::SetCursor(HSTMT *hstmtActivate)
2379 {
2380 if (hstmtActivate == wxDB_DEFAULT_CURSOR)
2381 hstmt = *hstmtDefault;
2382 else
2383 hstmt = *hstmtActivate;
2384
2385 } // wxDbTable::SetCursor()
2386
2387
2388 /********** wxDbTable::Count(const wxString &) **********/
2389 ULONG wxDbTable::Count(const wxString &args)
2390 {
2391 ULONG count;
2392 wxString sqlStmt;
2393 SDWORD cb;
2394
2395 // Build a "SELECT COUNT(*) FROM queryTableName [WHERE whereClause]" SQL Statement
2396 sqlStmt = wxT("SELECT COUNT(");
2397 sqlStmt += args;
2398 sqlStmt += wxT(") FROM ");
2399 sqlStmt += pDb->SQLTableName(queryTableName);
2400 // sqlStmt += queryTableName;
2401 #if wxODBC_BACKWARD_COMPATABILITY
2402 if (from && wxStrlen(from))
2403 #else
2404 if (from.Length())
2405 #endif
2406 sqlStmt += from;
2407
2408 // Add the where clause if one is provided
2409 #if wxODBC_BACKWARD_COMPATABILITY
2410 if (where && wxStrlen(where))
2411 #else
2412 if (where.Length())
2413 #endif
2414 {
2415 sqlStmt += wxT(" WHERE ");
2416 sqlStmt += where;
2417 }
2418
2419 pDb->WriteSqlLog(sqlStmt);
2420
2421 // Initialize the Count cursor if it's not already initialized
2422 if (!hstmtCount)
2423 {
2424 hstmtCount = GetNewCursor(FALSE,FALSE);
2425 wxASSERT(hstmtCount);
2426 if (!hstmtCount)
2427 return(0);
2428 }
2429
2430 // Execute the SQL statement
2431 if (SQLExecDirect(*hstmtCount, (SQLTCHAR FAR *) sqlStmt.c_str(), SQL_NTS) != SQL_SUCCESS)
2432 {
2433 pDb->DispAllErrors(henv, hdbc, *hstmtCount);
2434 return(0);
2435 }
2436
2437 // Fetch the record
2438 if (SQLFetch(*hstmtCount) != SQL_SUCCESS)
2439 {
2440 pDb->DispAllErrors(henv, hdbc, *hstmtCount);
2441 return(0);
2442 }
2443
2444 // Obtain the result
2445 if (SQLGetData(*hstmtCount, (UWORD)1, SQL_C_ULONG, &count, sizeof(count), &cb) != SQL_SUCCESS)
2446 {
2447 pDb->DispAllErrors(henv, hdbc, *hstmtCount);
2448 return(0);
2449 }
2450
2451 // Free the cursor
2452 if (SQLFreeStmt(*hstmtCount, SQL_CLOSE) != SQL_SUCCESS)
2453 pDb->DispAllErrors(henv, hdbc, *hstmtCount);
2454
2455 // Return the record count
2456 return(count);
2457
2458 } // wxDbTable::Count()
2459
2460
2461 /********** wxDbTable::Refresh() **********/
2462 bool wxDbTable::Refresh(void)
2463 {
2464 bool result = TRUE;
2465
2466 // Switch to the internal cursor so any active cursors are not corrupted
2467 HSTMT currCursor = GetCursor();
2468 hstmt = hstmtInternal;
2469 #if wxODBC_BACKWARD_COMPATABILITY
2470 // Save the where and order by clauses
2471 wxChar *saveWhere = where;
2472 wxChar *saveOrderBy = orderBy;
2473 #else
2474 wxString saveWhere = where;
2475 wxString saveOrderBy = orderBy;
2476 #endif
2477 // Build a where clause to refetch the record with. Try and use the
2478 // ROWID if it's available, ow use the key fields.
2479 wxString whereClause;
2480 whereClause.Empty();
2481
2482 if (CanUpdByROWID())
2483 {
2484 SDWORD cb;
2485 wxChar rowid[wxDB_ROWID_LEN+1];
2486
2487 // Get the ROWID value. If not successful retreiving the ROWID,
2488 // simply fall down through the code and build the WHERE clause
2489 // based on the key fields.
2490 if (SQLGetData(hstmt, (UWORD)(noCols+1), SQL_C_CHAR, (UCHAR*) rowid, wxDB_ROWID_LEN, &cb) == SQL_SUCCESS)
2491 {
2492 whereClause += pDb->SQLTableName(queryTableName);
2493 // whereClause += queryTableName;
2494 whereClause += wxT(".ROWID = '");
2495 whereClause += rowid;
2496 whereClause += wxT("'");
2497 }
2498 }
2499
2500 // If unable to use the ROWID, build a where clause from the keyfields
2501 if (wxStrlen(whereClause) == 0)
2502 BuildWhereClause(whereClause, DB_WHERE_KEYFIELDS, queryTableName);
2503
2504 // Requery the record
2505 where = whereClause;
2506 orderBy.Empty();
2507 if (!Query())
2508 result = FALSE;
2509
2510 if (result && !GetNext())
2511 result = FALSE;
2512
2513 // Switch back to original cursor
2514 SetCursor(&currCursor);
2515
2516 // Free the internal cursor
2517 if (SQLFreeStmt(hstmtInternal, SQL_CLOSE) != SQL_SUCCESS)
2518 pDb->DispAllErrors(henv, hdbc, hstmtInternal);
2519
2520 // Restore the original where and order by clauses
2521 where = saveWhere;
2522 orderBy = saveOrderBy;
2523
2524 return(result);
2525
2526 } // wxDbTable::Refresh()
2527
2528
2529 /********** wxDbTable::SetColNull() **********/
2530 bool wxDbTable::SetColNull(UWORD colNo, bool set)
2531 {
2532 if (colNo < noCols)
2533 {
2534 colDefs[colNo].Null = set;
2535 if (set) // Blank out the values in the member variable
2536 ClearMemberVar(colNo, FALSE); // Must call with FALSE here, or infinite recursion will happen
2537
2538 setCbValueForColumn(i);
2539
2540 return(TRUE);
2541 }
2542 else
2543 return(FALSE);
2544
2545 } // wxDbTable::SetColNull()
2546
2547
2548 /********** wxDbTable::SetColNull() **********/
2549 bool wxDbTable::SetColNull(const wxString &colName, bool set)
2550 {
2551 int colNo;
2552 for (colNo = 0; colNo < noCols; colNo++)
2553 {
2554 if (!wxStricmp(colName, colDefs[colNo].ColName))
2555 break;
2556 }
2557
2558 if (colNo < noCols)
2559 {
2560 colDefs[colNo].Null = set;
2561 if (set) // Blank out the values in the member variable
2562 ClearMemberVar(colNo,FALSE); // Must call with FALSE here, or infinite recursion will happen
2563
2564 setCbValueForColumn(i);
2565
2566 return(TRUE);
2567 }
2568 else
2569 return(FALSE);
2570
2571 } // wxDbTable::SetColNull()
2572
2573
2574 /********** wxDbTable::GetNewCursor() **********/
2575 HSTMT *wxDbTable::GetNewCursor(bool setCursor, bool bindColumns)
2576 {
2577 HSTMT *newHSTMT = new HSTMT;
2578 wxASSERT(newHSTMT);
2579 if (!newHSTMT)
2580 return(0);
2581
2582 if (SQLAllocStmt(hdbc, newHSTMT) != SQL_SUCCESS)
2583 {
2584 pDb->DispAllErrors(henv, hdbc);
2585 delete newHSTMT;
2586 return(0);
2587 }
2588
2589 if (SQLSetStmtOption(*newHSTMT, SQL_CURSOR_TYPE, cursorType) != SQL_SUCCESS)
2590 {
2591 pDb->DispAllErrors(henv, hdbc, *newHSTMT);
2592 delete newHSTMT;
2593 return(0);
2594 }
2595
2596 if (bindColumns)
2597 {
2598 if (!bindCols(*newHSTMT))
2599 {
2600 delete newHSTMT;
2601 return(0);
2602 }
2603 }
2604
2605 if (setCursor)
2606 SetCursor(newHSTMT);
2607
2608 return(newHSTMT);
2609
2610 } // wxDbTable::GetNewCursor()
2611
2612
2613 /********** wxDbTable::DeleteCursor() **********/
2614 bool wxDbTable::DeleteCursor(HSTMT *hstmtDel)
2615 {
2616 bool result = TRUE;
2617
2618 if (!hstmtDel) // Cursor already deleted
2619 return(result);
2620
2621 /*
2622 ODBC 3.0 says to use this form
2623 if (SQLFreeHandle(*hstmtDel, SQL_DROP) != SQL_SUCCESS)
2624
2625 */
2626 if (SQLFreeStmt(*hstmtDel, SQL_DROP) != SQL_SUCCESS)
2627 {
2628 pDb->DispAllErrors(henv, hdbc);
2629 result = FALSE;
2630 }
2631
2632 delete hstmtDel;
2633
2634 return(result);
2635
2636 } // wxDbTable::DeleteCursor()
2637
2638 //////////////////////////////////////////////////////////////
2639 // wxDbGrid support functions
2640 //////////////////////////////////////////////////////////////
2641
2642 void wxDbTable::SetRowMode(const rowmode_t rowmode)
2643 {
2644 if (!m_hstmtGridQuery)
2645 {
2646 m_hstmtGridQuery = GetNewCursor(FALSE,FALSE);
2647 if (!bindCols(*m_hstmtGridQuery))
2648 return;
2649 }
2650
2651 m_rowmode = rowmode;
2652 switch (m_rowmode)
2653 {
2654 case WX_ROW_MODE_QUERY:
2655 SetCursor(m_hstmtGridQuery);
2656 break;
2657 case WX_ROW_MODE_INDIVIDUAL:
2658 SetCursor(hstmtDefault);
2659 break;
2660 default:
2661 wxASSERT(0);
2662 }
2663 } // wxDbTable::SetRowMode()
2664
2665
2666 wxVariant wxDbTable::GetCol(const int colNo) const
2667 {
2668 wxVariant val;
2669 if ((colNo < noCols) && (!IsColNull(colNo)))
2670 {
2671 switch (colDefs[colNo].SqlCtype)
2672 {
2673 case SQL_CHAR:
2674 case SQL_VARCHAR:
2675 val = (wxChar *)(colDefs[colNo].PtrDataObj);
2676 break;
2677 case SQL_C_LONG:
2678 case SQL_C_SLONG:
2679 val = *(long *)(colDefs[colNo].PtrDataObj);
2680 break;
2681 case SQL_C_SHORT:
2682 case SQL_C_SSHORT:
2683 val = (long int )(*(short *)(colDefs[colNo].PtrDataObj));
2684 break;
2685 case SQL_C_ULONG:
2686 val = (long)(*(unsigned long *)(colDefs[colNo].PtrDataObj));
2687 break;
2688 case SQL_C_TINYINT:
2689 val = (long)(*(wxChar *)(colDefs[colNo].PtrDataObj));
2690 break;
2691 case SQL_C_UTINYINT:
2692 val = (long)(*(wxChar *)(colDefs[colNo].PtrDataObj));
2693 break;
2694 case SQL_C_USHORT:
2695 val = (long)(*(UWORD *)(colDefs[colNo].PtrDataObj));
2696 break;
2697 case SQL_C_DATE:
2698 val = (DATE_STRUCT *)(colDefs[colNo].PtrDataObj);
2699 break;
2700 case SQL_C_TIME:
2701 val = (TIME_STRUCT *)(colDefs[colNo].PtrDataObj);
2702 break;
2703 case SQL_C_TIMESTAMP:
2704 val = (TIMESTAMP_STRUCT *)(colDefs[colNo].PtrDataObj);
2705 break;
2706 case SQL_C_DOUBLE:
2707 val = *(double *)(colDefs[colNo].PtrDataObj);
2708 break;
2709 default:
2710 assert(0);
2711 }
2712 }
2713 return val;
2714 } // wxDbTable::GetCol()
2715
2716
2717 void wxDbTable::SetCol(const int colNo, const wxVariant val)
2718 {
2719 //FIXME: Add proper wxDateTime support to wxVariant..
2720 wxDateTime dateval;
2721
2722 SetColNull(colNo, val.IsNull());
2723
2724 if (!val.IsNull())
2725 {
2726 if ((colDefs[colNo].SqlCtype == SQL_C_DATE)
2727 || (colDefs[colNo].SqlCtype == SQL_C_TIME)
2728 || (colDefs[colNo].SqlCtype == SQL_C_TIMESTAMP))
2729 {
2730 //Returns null if invalid!
2731 if (!dateval.ParseDate(val.GetString()))
2732 SetColNull(colNo, TRUE);
2733 }
2734
2735 switch (colDefs[colNo].SqlCtype)
2736 {
2737 case SQL_CHAR:
2738 case SQL_VARCHAR:
2739 csstrncpyt((wxChar *)(colDefs[colNo].PtrDataObj),
2740 val.GetString().c_str(),
2741 colDefs[colNo].SzDataObj-1);
2742 break;
2743 case SQL_C_LONG:
2744 case SQL_C_SLONG:
2745 *(long *)(colDefs[colNo].PtrDataObj) = val;
2746 break;
2747 case SQL_C_SHORT:
2748 case SQL_C_SSHORT:
2749 *(short *)(colDefs[colNo].PtrDataObj) = val.GetLong();
2750 break;
2751 case SQL_C_ULONG:
2752 *(unsigned long *)(colDefs[colNo].PtrDataObj) = val.GetLong();
2753 break;
2754 case SQL_C_TINYINT:
2755 *(wxChar *)(colDefs[colNo].PtrDataObj) = val.GetChar();
2756 break;
2757 case SQL_C_UTINYINT:
2758 *(wxChar *)(colDefs[colNo].PtrDataObj) = val.GetChar();
2759 break;
2760 case SQL_C_USHORT:
2761 *(unsigned short *)(colDefs[colNo].PtrDataObj) = val.GetLong();
2762 break;
2763 //FIXME: Add proper wxDateTime support to wxVariant..
2764 case SQL_C_DATE:
2765 {
2766 DATE_STRUCT *dataptr =
2767 (DATE_STRUCT *)colDefs[colNo].PtrDataObj;
2768
2769 dataptr->year = dateval.GetYear();
2770 dataptr->month = dateval.GetMonth()+1;
2771 dataptr->day = dateval.GetDay();
2772 }
2773 break;
2774 case SQL_C_TIME:
2775 {
2776 TIME_STRUCT *dataptr =
2777 (TIME_STRUCT *)colDefs[colNo].PtrDataObj;
2778
2779 dataptr->hour = dateval.GetHour();
2780 dataptr->minute = dateval.GetMinute();
2781 dataptr->second = dateval.GetSecond();
2782 }
2783 break;
2784 case SQL_C_TIMESTAMP:
2785 {
2786 TIMESTAMP_STRUCT *dataptr =
2787 (TIMESTAMP_STRUCT *)colDefs[colNo].PtrDataObj;
2788 dataptr->year = dateval.GetYear();
2789 dataptr->month = dateval.GetMonth()+1;
2790 dataptr->day = dateval.GetDay();
2791
2792 dataptr->hour = dateval.GetHour();
2793 dataptr->minute = dateval.GetMinute();
2794 dataptr->second = dateval.GetSecond();
2795 }
2796 break;
2797 case SQL_C_DOUBLE:
2798 *(double *)(colDefs[colNo].PtrDataObj) = val;
2799 break;
2800 default:
2801 assert(0);
2802 } // switch
2803 } // if (!val.IsNull())
2804 } // wxDbTable::SetCol()
2805
2806
2807 GenericKey wxDbTable::GetKey()
2808 {
2809 void *blk;
2810 wxChar *blkptr;
2811
2812 blk = malloc(m_keysize);
2813 blkptr = (wxChar *) blk;
2814
2815 int i;
2816 for (i=0; i < noCols; i++)
2817 {
2818 if (colDefs[i].KeyField)
2819 {
2820 memcpy(blkptr,colDefs[i].PtrDataObj, colDefs[i].SzDataObj);
2821 blkptr += colDefs[i].SzDataObj;
2822 }
2823 }
2824
2825 GenericKey k = GenericKey(blk, m_keysize);
2826 free(blk);
2827
2828 return k;
2829 } // wxDbTable::GetKey()
2830
2831
2832 void wxDbTable::SetKey(const GenericKey& k)
2833 {
2834 void *blk;
2835 wxChar *blkptr;
2836
2837 blk = k.GetBlk();
2838 blkptr = (wxChar *)blk;
2839
2840 int i;
2841 for (i=0; i < noCols; i++)
2842 {
2843 if (colDefs[i].KeyField)
2844 {
2845 SetColNull(i, FALSE);
2846 memcpy(colDefs[i].PtrDataObj, blkptr, colDefs[i].SzDataObj);
2847 blkptr += colDefs[i].SzDataObj;
2848 }
2849 }
2850 } // wxDbTable::SetKey()
2851
2852
2853 #endif // wxUSE_ODBC
2854
wxWidgets (Impactor)