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