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