]> 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 some potential buffer overruns
[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 noCols = numColumns; // Number of cols 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 (noCols)
216 colDefs = new wxDbColDef[noCols]; // 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 colNo;
470
471 for (i=0, colNo=1; i < noCols; i++)
472 {
473 if (forUpdate)
474 {
475 if (!colDefs[i].Updateable)
476 continue;
477 }
478 else
479 {
480 if (!colDefs[i].InsertAllowed)
481 continue;
482 }
483
484 switch(colDefs[i].DbDataType)
485 {
486 case DB_DATA_TYPE_VARCHAR:
487 fSqlType = pDb->GetTypeInfVarchar().FsqlType;
488 precision = colDefs[i].SzDataObj;
489 scale = 0;
490 break;
491 case DB_DATA_TYPE_INTEGER:
492 fSqlType = pDb->GetTypeInfInteger().FsqlType;
493 precision = pDb->GetTypeInfInteger().Precision;
494 scale = 0;
495 break;
496 case DB_DATA_TYPE_FLOAT:
497 fSqlType = pDb->GetTypeInfFloat().FsqlType;
498 precision = pDb->GetTypeInfFloat().Precision;
499 scale = pDb->GetTypeInfFloat().MaximumScale;
500 // SQL Sybase Anywhere v5.5 returned a negative number for the
501 // MaxScale. This caused ODBC to kick out an error on ibscale.
502 // I check for this here and set the scale = precision.
503 //if (scale < 0)
504 // scale = (short) precision;
505 break;
506 case DB_DATA_TYPE_DATE:
507 fSqlType = pDb->GetTypeInfDate().FsqlType;
508 precision = pDb->GetTypeInfDate().Precision;
509 scale = 0;
510 break;
511 case DB_DATA_TYPE_BLOB:
512 fSqlType = pDb->GetTypeInfBlob().FsqlType;
513 precision = colDefs[i].SzDataObj;
514 scale = 0;
515 break;
516 }
517
518 setCbValueForColumn(i);
519
520 if (forUpdate)
521 {
522 if (SQLBindParameter(hstmtUpdate, colNo++, SQL_PARAM_INPUT, colDefs[i].SqlCtype,
523 fSqlType, precision, scale, (UCHAR*) colDefs[i].PtrDataObj,
524 precision+1, &colDefs[i].CbValue) != SQL_SUCCESS)
525 {
526 return(pDb->DispAllErrors(henv, hdbc, hstmtUpdate));
527 }
528 }
529 else
530 {
531 if (SQLBindParameter(hstmtInsert, colNo++, SQL_PARAM_INPUT, colDefs[i].SqlCtype,
532 fSqlType, precision, scale, (UCHAR*) colDefs[i].PtrDataObj,
533 precision+1, &colDefs[i].CbValue) != SQL_SUCCESS)
534 {
535 return(pDb->DispAllErrors(henv, hdbc, hstmtInsert));
536 }
537 }
538 }
539
540 // Completed successfully
541 return true;
542
543 } // wxDbTable::bindParams()
544
545
546 /********** wxDbTable::bindInsertParams() **********/
547 bool wxDbTable::bindInsertParams(void)
548 {
549 return bindParams(false);
550 } // wxDbTable::bindInsertParams()
551
552
553 /********** wxDbTable::bindUpdateParams() **********/
554 bool wxDbTable::bindUpdateParams(void)
555 {
556 return bindParams(true);
557 } // wxDbTable::bindUpdateParams()
558
559
560 /********** wxDbTable::bindCols() **********/
561 bool wxDbTable::bindCols(HSTMT cursor)
562 {
563 static SDWORD cb;
564
565 // Bind each column of the table to a memory address for fetching data
566 UWORD i;
567 for (i = 0; i < noCols; i++)
568 {
569 cb = colDefs[i].CbValue;
570 if (SQLBindCol(cursor, (UWORD)(i+1), colDefs[i].SqlCtype, (UCHAR*) colDefs[i].PtrDataObj,
571 colDefs[i].SzDataObj, &cb ) != SQL_SUCCESS)
572 return (pDb->DispAllErrors(henv, hdbc, cursor));
573 }
574
575 // Completed successfully
576 return true;
577
578 } // wxDbTable::bindCols()
579
580
581 /********** wxDbTable::getRec() **********/
582 bool wxDbTable::getRec(UWORD fetchType)
583 {
584 RETCODE retcode;
585
586 if (!pDb->FwdOnlyCursors())
587 {
588 // Fetch the NEXT, PREV, FIRST or LAST record, depending on fetchType
589 UDWORD cRowsFetched;
590 UWORD rowStatus;
591
592 retcode = SQLExtendedFetch(hstmt, fetchType, 0, &cRowsFetched, &rowStatus);
593 if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
594 {
595 if (retcode == SQL_NO_DATA_FOUND)
596 return false;
597 else
598 return(pDb->DispAllErrors(henv, hdbc, hstmt));
599 }
600 else
601 {
602 // Set the Null member variable to indicate the Null state
603 // of each column just read in.
604 int i;
605 for (i = 0; i < noCols; i++)
606 colDefs[i].Null = (colDefs[i].CbValue == SQL_NULL_DATA);
607 }
608 }
609 else
610 {
611 // Fetch the next record from the record set
612 retcode = SQLFetch(hstmt);
613 if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
614 {
615 if (retcode == SQL_NO_DATA_FOUND)
616 return false;
617 else
618 return(pDb->DispAllErrors(henv, hdbc, hstmt));
619 }
620 else
621 {
622 // Set the Null member variable to indicate the Null state
623 // of each column just read in.
624 int i;
625 for (i = 0; i < noCols; i++)
626 colDefs[i].Null = (colDefs[i].CbValue == SQL_NULL_DATA);
627 }
628 }
629
630 // Completed successfully
631 return true;
632
633 } // wxDbTable::getRec()
634
635
636 /********** wxDbTable::execDelete() **********/
637 bool wxDbTable::execDelete(const wxString &pSqlStmt)
638 {
639 RETCODE retcode;
640
641 // Execute the DELETE statement
642 retcode = SQLExecDirect(hstmtDelete, (SQLTCHAR FAR *) pSqlStmt.c_str(), SQL_NTS);
643
644 if (retcode == SQL_SUCCESS ||
645 retcode == SQL_NO_DATA_FOUND ||
646 retcode == SQL_SUCCESS_WITH_INFO)
647 {
648 // Record deleted successfully
649 return true;
650 }
651
652 // Problem deleting record
653 return(pDb->DispAllErrors(henv, hdbc, hstmtDelete));
654
655 } // wxDbTable::execDelete()
656
657
658 /********** wxDbTable::execUpdate() **********/
659 bool wxDbTable::execUpdate(const wxString &pSqlStmt)
660 {
661 RETCODE retcode;
662
663 // Execute the UPDATE statement
664 retcode = SQLExecDirect(hstmtUpdate, (SQLTCHAR FAR *) pSqlStmt.c_str(), SQL_NTS);
665
666 if (retcode == SQL_SUCCESS ||
667 retcode == SQL_NO_DATA_FOUND ||
668 retcode == SQL_SUCCESS_WITH_INFO)
669 {
670 // Record updated successfully
671 return true;
672 }
673 else if (retcode == SQL_NEED_DATA)
674 {
675 PTR pParmID;
676 retcode = SQLParamData(hstmtUpdate, &pParmID);
677 while (retcode == SQL_NEED_DATA)
678 {
679 // Find the parameter
680 int i;
681 for (i=0; i < noCols; i++)
682 {
683 if (colDefs[i].PtrDataObj == pParmID)
684 {
685 // We found it. Store the parameter.
686 retcode = SQLPutData(hstmtUpdate, pParmID, colDefs[i].SzDataObj);
687 if (retcode != SQL_SUCCESS)
688 {
689 pDb->DispNextError();
690 return pDb->DispAllErrors(henv, hdbc, hstmtUpdate);
691 }
692 break;
693 }
694 }
695 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 < noCols; 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 && noCols > 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 < noCols; 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 < noCols; 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 (CanUpdByROWID())
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)(noCols+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 < noCols; 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 < noCols)
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 && CanUpdByROWID())
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 typeOfUpd, 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 < noCols; 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(typeOfUpd)
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 (CanUpdByROWID())
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)(noCols+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 typeOfUpd, const wxString &pWhereClause)
1251 {
1252 wxString tempSqlStmt;
1253 BuildUpdateStmt(tempSqlStmt, typeOfUpd, 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 colNo;
1271 for (colNo = 0; colNo < noCols; colNo++)
1272 {
1273 // Determine if this column should be included in the WHERE clause
1274 if ((typeOfWhere == DB_WHERE_KEYFIELDS && colDefs[colNo].KeyField) ||
1275 (typeOfWhere == DB_WHERE_MATCHING && (!IsColNull((UWORD)colNo))))
1276 {
1277 // Skip over timestamp columns
1278 if (colDefs[colNo].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[colNo].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[colNo].ColName);
1295
1296 if (useLikeComparison && (colDefs[colNo].SqlCtype == SQL_C_WXCHAR))
1297 pWhereClause += wxT(" LIKE ");
1298 else
1299 pWhereClause += wxT(" = ");
1300
1301 switch(colDefs[colNo].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[colNo].PtrDataObj);
1309 break;
1310 case SQL_C_SHORT:
1311 case SQL_C_SSHORT:
1312 colValue.Printf(wxT("%hi"), *((SWORD *) colDefs[colNo].PtrDataObj));
1313 break;
1314 case SQL_C_USHORT:
1315 colValue.Printf(wxT("%hu"), *((UWORD *) colDefs[colNo].PtrDataObj));
1316 break;
1317 case SQL_C_LONG:
1318 case SQL_C_SLONG:
1319 colValue.Printf(wxT("%li"), *((SDWORD *) colDefs[colNo].PtrDataObj));
1320 break;
1321 case SQL_C_ULONG:
1322 colValue.Printf(wxT("%lu"), *((UDWORD *) colDefs[colNo].PtrDataObj));
1323 break;
1324 case SQL_C_FLOAT:
1325 colValue.Printf(wxT("%.6f"), *((SFLOAT *) colDefs[colNo].PtrDataObj));
1326 break;
1327 case SQL_C_DOUBLE:
1328 colValue.Printf(wxT("%.6f"), *((SDOUBLE *) colDefs[colNo].PtrDataObj));
1329 break;
1330 default:
1331 {
1332 wxString strMsg;
1333 strMsg.Printf(wxT("wxDbTable::bindParams(): Unknown column type for colDefs %d colName %s"),
1334 colNo,colDefs[colNo].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 < noCols; 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 < noCols; 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 < noCols; 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 < noCols; 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 &idxName, bool unique, UWORD noIdxCols,
1652 wxDbIdxDef *pIdxDefs, bool attemptDrop)
1653 {
1654 wxString sqlStmt;
1655
1656 // Drop the index first
1657 if (attemptDrop && !DropIndex(idxName))
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 < noIdxCols && 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->noCols))
1682 {
1683 if (wxStrcmp(colDefs[j].ColName,pIdxDefs[i].ColName) == 0)
1684 found = true;
1685 if (!found)
1686 j++;
1687 }
1688
1689 if (found)
1690 {
1691 ok = pDb->ModifyColumn(tableName, pIdxDefs[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(idxName);
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 < noIdxCols; i++)
1735 {
1736 sqlStmt += pDb->SQLColumnName(pIdxDefs[i].ColName);
1737 // sqlStmt += pIdxDefs[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 < noCols; ++j )
1745 {
1746 if ( wxStrcmp( pIdxDefs[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 (pIdxDefs[i].Ascending)
1765 sqlStmt += wxT(" ASC");
1766 else
1767 sqlStmt += wxT(" DESC");
1768 }
1769 else
1770 wxASSERT_MSG(pIdxDefs[i].Ascending, _T("Datasource does not support DESCending index columns"));
1771
1772 if ((i + 1) < noIdxCols)
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 &idxName)
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(idxName.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(idxName.c_str()).c_str());
1828 else
1829 sqlStmt.Printf(wxT("DROP INDEX %s"),
1830 pDb->SQLTableName(idxName.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 colNo = 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 && (colNo != 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 noCols-1
1890 if (colNo >= noCols || colNo < 0)
1891 {
1892 abort = true;
1893 continue;
1894 }
1895
1896 if (colNo != first)
1897 tempStr += wxT(",");
1898
1899 tempStr += colDefs[colNo].ColName;
1900 colNo = 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 < noCols; 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 colNo) 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[colNo].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[colNo].PtrDataObj)[0] == 0);
2115 case SQL_C_SSHORT:
2116 return(( *((SWORD *) colDefs[colNo].PtrDataObj)) == 0);
2117 case SQL_C_USHORT:
2118 return(( *((UWORD*) colDefs[colNo].PtrDataObj)) == 0);
2119 case SQL_C_SLONG:
2120 return(( *((SDWORD *) colDefs[colNo].PtrDataObj)) == 0);
2121 case SQL_C_ULONG:
2122 return(( *((UDWORD *) colDefs[colNo].PtrDataObj)) == 0);
2123 case SQL_C_FLOAT:
2124 return(( *((SFLOAT *) colDefs[colNo].PtrDataObj)) == 0);
2125 case SQL_C_DOUBLE:
2126 return((*((SDOUBLE *) colDefs[colNo].PtrDataObj)) == 0);
2127 case SQL_C_TIMESTAMP:
2128 TIMESTAMP_STRUCT *pDt;
2129 pDt = (TIMESTAMP_STRUCT *) colDefs[colNo].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[colNo].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::CanUpdByROWID() **********/
2161 bool wxDbTable::CanUpdByROWID(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::CanUpdByROWID()
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 colNo, bool setToNull)
2191 {
2192 wxASSERT(colNo < noCols);
2193
2194 switch(colDefs[colNo].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[colNo].PtrDataObj)[0] = 0;
2202 break;
2203 case SQL_C_SSHORT:
2204 *((SWORD *) colDefs[colNo].PtrDataObj) = 0;
2205 break;
2206 case SQL_C_USHORT:
2207 *((UWORD*) colDefs[colNo].PtrDataObj) = 0;
2208 break;
2209 case SQL_C_LONG:
2210 case SQL_C_SLONG:
2211 *((SDWORD *) colDefs[colNo].PtrDataObj) = 0;
2212 break;
2213 case SQL_C_ULONG:
2214 *((UDWORD *) colDefs[colNo].PtrDataObj) = 0;
2215 break;
2216 case SQL_C_FLOAT:
2217 *((SFLOAT *) colDefs[colNo].PtrDataObj) = 0.0f;
2218 break;
2219 case SQL_C_DOUBLE:
2220 *((SDOUBLE *) colDefs[colNo].PtrDataObj) = 0.0f;
2221 break;
2222 case SQL_C_TIMESTAMP:
2223 TIMESTAMP_STRUCT *pDt;
2224 pDt = (TIMESTAMP_STRUCT *) colDefs[colNo].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(colNo);
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 < noCols; 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 void wxDbTable::SetColDefs(UWORD index, const wxString &fieldName, int dataType, void *pData,
2272 SWORD cType, int size, bool keyField, bool upd,
2273 bool insAllow, bool derivedCol)
2274 {
2275 wxASSERT_MSG( index < noCols,
2276 _T("Specified column index exceeds the maximum number of columns for this table.") );
2277
2278 if (!colDefs) // May happen if the database connection fails
2279 return;
2280
2281 if (fieldName.Length() > (unsigned int) DB_MAX_COLUMN_NAME_LEN)
2282 {
2283 wxStrncpy(colDefs[index].ColName, fieldName, DB_MAX_COLUMN_NAME_LEN);
2284 colDefs[index].ColName[DB_MAX_COLUMN_NAME_LEN] = 0;
2285
2286 #ifdef __WXDEBUG__
2287 wxString tmpMsg;
2288 tmpMsg.Printf(_T("Column name '%s' is too long. Truncated to '%s'."),
2289 fieldName.c_str(),colDefs[index].ColName);
2290 wxFAIL_MSG(tmpMsg);
2291 #endif // __WXDEBUG__
2292 }
2293 else
2294 wxStrcpy(colDefs[index].ColName, fieldName);
2295
2296 colDefs[index].DbDataType = dataType;
2297 colDefs[index].PtrDataObj = pData;
2298 colDefs[index].SqlCtype = cType;
2299 colDefs[index].SzDataObj = size; //TODO: glt ??? * sizeof(wxChar) ???
2300 colDefs[index].KeyField = keyField;
2301 colDefs[index].DerivedCol = derivedCol;
2302 // Derived columns by definition would NOT be "Insertable" or "Updateable"
2303 if (derivedCol)
2304 {
2305 colDefs[index].Updateable = false;
2306 colDefs[index].InsertAllowed = false;
2307 }
2308 else
2309 {
2310 colDefs[index].Updateable = upd;
2311 colDefs[index].InsertAllowed = insAllow;
2312 }
2313
2314 colDefs[index].Null = false;
2315
2316 } // wxDbTable::SetColDefs()
2317
2318
2319 /********** wxDbTable::SetColDefs() **********/
2320 wxDbColDataPtr* wxDbTable::SetColDefs(wxDbColInf *pColInfs, UWORD numCols)
2321 {
2322 wxASSERT(pColInfs);
2323 wxDbColDataPtr *pColDataPtrs = NULL;
2324
2325 if (pColInfs)
2326 {
2327 UWORD index;
2328
2329 pColDataPtrs = new wxDbColDataPtr[numCols+1];
2330
2331 for (index = 0; index < numCols; index++)
2332 {
2333 // Process the fields
2334 switch (pColInfs[index].dbDataType)
2335 {
2336 case DB_DATA_TYPE_VARCHAR:
2337 pColDataPtrs[index].PtrDataObj = new wxChar[pColInfs[index].bufferSize+(1*sizeof(wxChar))];
2338 pColDataPtrs[index].SzDataObj = pColInfs[index].bufferSize+(1*sizeof(wxChar));
2339 pColDataPtrs[index].SqlCtype = SQL_C_WXCHAR;
2340 break;
2341 case DB_DATA_TYPE_INTEGER:
2342 // Can be long or short
2343 if (pColInfs[index].bufferSize == sizeof(long))
2344 {
2345 pColDataPtrs[index].PtrDataObj = new long;
2346 pColDataPtrs[index].SzDataObj = sizeof(long);
2347 pColDataPtrs[index].SqlCtype = SQL_C_SLONG;
2348 }
2349 else
2350 {
2351 pColDataPtrs[index].PtrDataObj = new short;
2352 pColDataPtrs[index].SzDataObj = sizeof(short);
2353 pColDataPtrs[index].SqlCtype = SQL_C_SSHORT;
2354 }
2355 break;
2356 case DB_DATA_TYPE_FLOAT:
2357 // Can be float or double
2358 if (pColInfs[index].bufferSize == sizeof(float))
2359 {
2360 pColDataPtrs[index].PtrDataObj = new float;
2361 pColDataPtrs[index].SzDataObj = sizeof(float);
2362 pColDataPtrs[index].SqlCtype = SQL_C_FLOAT;
2363 }
2364 else
2365 {
2366 pColDataPtrs[index].PtrDataObj = new double;
2367 pColDataPtrs[index].SzDataObj = sizeof(double);
2368 pColDataPtrs[index].SqlCtype = SQL_C_DOUBLE;
2369 }
2370 break;
2371 case DB_DATA_TYPE_DATE:
2372 pColDataPtrs[index].PtrDataObj = new TIMESTAMP_STRUCT;
2373 pColDataPtrs[index].SzDataObj = sizeof(TIMESTAMP_STRUCT);
2374 pColDataPtrs[index].SqlCtype = SQL_C_TIMESTAMP;
2375 break;
2376 case DB_DATA_TYPE_BLOB:
2377 wxFAIL_MSG(wxT("This form of ::SetColDefs() cannot be used with BLOB columns"));
2378 pColDataPtrs[index].PtrDataObj = /*BLOB ADDITION NEEDED*/NULL;
2379 pColDataPtrs[index].SzDataObj = /*BLOB ADDITION NEEDED*/sizeof(void *);
2380 pColDataPtrs[index].SqlCtype = SQL_VARBINARY;
2381 break;
2382 }
2383 if (pColDataPtrs[index].PtrDataObj != NULL)
2384 SetColDefs (index,pColInfs[index].colName,pColInfs[index].dbDataType, pColDataPtrs[index].PtrDataObj, pColDataPtrs[index].SqlCtype, pColDataPtrs[index].SzDataObj);
2385 else
2386 {
2387 // Unable to build all the column definitions, as either one of
2388 // the calls to "new" failed above, or there was a BLOB field
2389 // to have a column definition for. If BLOBs are to be used,
2390 // the other form of ::SetColDefs() must be used, as it is impossible
2391 // to know the maximum size to create the PtrDataObj to be.
2392 delete [] pColDataPtrs;
2393 return NULL;
2394 }
2395 }
2396 }
2397
2398 return (pColDataPtrs);
2399
2400 } // wxDbTable::SetColDefs()
2401
2402
2403 /********** wxDbTable::SetCursor() **********/
2404 void wxDbTable::SetCursor(HSTMT *hstmtActivate)
2405 {
2406 if (hstmtActivate == wxDB_DEFAULT_CURSOR)
2407 hstmt = *hstmtDefault;
2408 else
2409 hstmt = *hstmtActivate;
2410
2411 } // wxDbTable::SetCursor()
2412
2413
2414 /********** wxDbTable::Count(const wxString &) **********/
2415 ULONG wxDbTable::Count(const wxString &args)
2416 {
2417 ULONG count;
2418 wxString sqlStmt;
2419 SDWORD cb;
2420
2421 // Build a "SELECT COUNT(*) FROM queryTableName [WHERE whereClause]" SQL Statement
2422 sqlStmt = wxT("SELECT COUNT(");
2423 sqlStmt += args;
2424 sqlStmt += wxT(") FROM ");
2425 sqlStmt += pDb->SQLTableName(queryTableName);
2426 // sqlStmt += queryTableName;
2427 #if wxODBC_BACKWARD_COMPATABILITY
2428 if (from && wxStrlen(from))
2429 #else
2430 if (from.Length())
2431 #endif
2432 sqlStmt += from;
2433
2434 // Add the where clause if one is provided
2435 #if wxODBC_BACKWARD_COMPATABILITY
2436 if (where && wxStrlen(where))
2437 #else
2438 if (where.Length())
2439 #endif
2440 {
2441 sqlStmt += wxT(" WHERE ");
2442 sqlStmt += where;
2443 }
2444
2445 pDb->WriteSqlLog(sqlStmt);
2446
2447 // Initialize the Count cursor if it's not already initialized
2448 if (!hstmtCount)
2449 {
2450 hstmtCount = GetNewCursor(false,false);
2451 wxASSERT(hstmtCount);
2452 if (!hstmtCount)
2453 return(0);
2454 }
2455
2456 // Execute the SQL statement
2457 if (SQLExecDirect(*hstmtCount, (SQLTCHAR FAR *) sqlStmt.c_str(), SQL_NTS) != SQL_SUCCESS)
2458 {
2459 pDb->DispAllErrors(henv, hdbc, *hstmtCount);
2460 return(0);
2461 }
2462
2463 // Fetch the record
2464 if (SQLFetch(*hstmtCount) != SQL_SUCCESS)
2465 {
2466 pDb->DispAllErrors(henv, hdbc, *hstmtCount);
2467 return(0);
2468 }
2469
2470 // Obtain the result
2471 if (SQLGetData(*hstmtCount, (UWORD)1, SQL_C_ULONG, &count, sizeof(count), &cb) != SQL_SUCCESS)
2472 {
2473 pDb->DispAllErrors(henv, hdbc, *hstmtCount);
2474 return(0);
2475 }
2476
2477 // Free the cursor
2478 if (SQLFreeStmt(*hstmtCount, SQL_CLOSE) != SQL_SUCCESS)
2479 pDb->DispAllErrors(henv, hdbc, *hstmtCount);
2480
2481 // Return the record count
2482 return(count);
2483
2484 } // wxDbTable::Count()
2485
2486
2487 /********** wxDbTable::Refresh() **********/
2488 bool wxDbTable::Refresh(void)
2489 {
2490 bool result = true;
2491
2492 // Switch to the internal cursor so any active cursors are not corrupted
2493 HSTMT currCursor = GetCursor();
2494 hstmt = hstmtInternal;
2495 #if wxODBC_BACKWARD_COMPATABILITY
2496 // Save the where and order by clauses
2497 wxChar *saveWhere = where;
2498 wxChar *saveOrderBy = orderBy;
2499 #else
2500 wxString saveWhere = where;
2501 wxString saveOrderBy = orderBy;
2502 #endif
2503 // Build a where clause to refetch the record with. Try and use the
2504 // ROWID if it's available, ow use the key fields.
2505 wxString whereClause;
2506 whereClause.Empty();
2507
2508 if (CanUpdByROWID())
2509 {
2510 SDWORD cb;
2511 wxChar rowid[wxDB_ROWID_LEN+1];
2512
2513 // Get the ROWID value. If not successful retreiving the ROWID,
2514 // simply fall down through the code and build the WHERE clause
2515 // based on the key fields.
2516 if (SQLGetData(hstmt, (UWORD)(noCols+1), SQL_C_WXCHAR, (UCHAR*) rowid, sizeof(rowid), &cb) == SQL_SUCCESS)
2517 {
2518 whereClause += pDb->SQLTableName(queryTableName);
2519 // whereClause += queryTableName;
2520 whereClause += wxT(".ROWID = '");
2521 whereClause += rowid;
2522 whereClause += wxT("'");
2523 }
2524 }
2525
2526 // If unable to use the ROWID, build a where clause from the keyfields
2527 if (wxStrlen(whereClause) == 0)
2528 BuildWhereClause(whereClause, DB_WHERE_KEYFIELDS, queryTableName);
2529
2530 // Requery the record
2531 where = whereClause;
2532 orderBy.Empty();
2533 if (!Query())
2534 result = false;
2535
2536 if (result && !GetNext())
2537 result = false;
2538
2539 // Switch back to original cursor
2540 SetCursor(&currCursor);
2541
2542 // Free the internal cursor
2543 if (SQLFreeStmt(hstmtInternal, SQL_CLOSE) != SQL_SUCCESS)
2544 pDb->DispAllErrors(henv, hdbc, hstmtInternal);
2545
2546 // Restore the original where and order by clauses
2547 where = saveWhere;
2548 orderBy = saveOrderBy;
2549
2550 return(result);
2551
2552 } // wxDbTable::Refresh()
2553
2554
2555 /********** wxDbTable::SetColNull() **********/
2556 bool wxDbTable::SetColNull(UWORD colNo, bool set)
2557 {
2558 if (colNo < noCols)
2559 {
2560 colDefs[colNo].Null = set;
2561 if (set) // Blank out the values in the member variable
2562 ClearMemberVar(colNo, false); // Must call with false here, or infinite recursion will happen
2563
2564 setCbValueForColumn(colNo);
2565
2566 return true;
2567 }
2568 else
2569 return false;
2570
2571 } // wxDbTable::SetColNull()
2572
2573
2574 /********** wxDbTable::SetColNull() **********/
2575 bool wxDbTable::SetColNull(const wxString &colName, bool set)
2576 {
2577 int colNo;
2578 for (colNo = 0; colNo < noCols; colNo++)
2579 {
2580 if (!wxStricmp(colName, colDefs[colNo].ColName))
2581 break;
2582 }
2583
2584 if (colNo < noCols)
2585 {
2586 colDefs[colNo].Null = set;
2587 if (set) // Blank out the values in the member variable
2588 ClearMemberVar((UWORD)colNo,false); // Must call with false here, or infinite recursion will happen
2589
2590 setCbValueForColumn(colNo);
2591
2592 return true;
2593 }
2594 else
2595 return false;
2596
2597 } // wxDbTable::SetColNull()
2598
2599
2600 /********** wxDbTable::GetNewCursor() **********/
2601 HSTMT *wxDbTable::GetNewCursor(bool setCursor, bool bindColumns)
2602 {
2603 HSTMT *newHSTMT = new HSTMT;
2604 wxASSERT(newHSTMT);
2605 if (!newHSTMT)
2606 return(0);
2607
2608 if (SQLAllocStmt(hdbc, newHSTMT) != SQL_SUCCESS)
2609 {
2610 pDb->DispAllErrors(henv, hdbc);
2611 delete newHSTMT;
2612 return(0);
2613 }
2614
2615 if (SQLSetStmtOption(*newHSTMT, SQL_CURSOR_TYPE, cursorType) != SQL_SUCCESS)
2616 {
2617 pDb->DispAllErrors(henv, hdbc, *newHSTMT);
2618 delete newHSTMT;
2619 return(0);
2620 }
2621
2622 if (bindColumns)
2623 {
2624 if (!bindCols(*newHSTMT))
2625 {
2626 delete newHSTMT;
2627 return(0);
2628 }
2629 }
2630
2631 if (setCursor)
2632 SetCursor(newHSTMT);
2633
2634 return(newHSTMT);
2635
2636 } // wxDbTable::GetNewCursor()
2637
2638
2639 /********** wxDbTable::DeleteCursor() **********/
2640 bool wxDbTable::DeleteCursor(HSTMT *hstmtDel)
2641 {
2642 bool result = true;
2643
2644 if (!hstmtDel) // Cursor already deleted
2645 return(result);
2646
2647 /*
2648 ODBC 3.0 says to use this form
2649 if (SQLFreeHandle(*hstmtDel, SQL_DROP) != SQL_SUCCESS)
2650
2651 */
2652 if (SQLFreeStmt(*hstmtDel, SQL_DROP) != SQL_SUCCESS)
2653 {
2654 pDb->DispAllErrors(henv, hdbc);
2655 result = false;
2656 }
2657
2658 delete hstmtDel;
2659
2660 return(result);
2661
2662 } // wxDbTable::DeleteCursor()
2663
2664 //////////////////////////////////////////////////////////////
2665 // wxDbGrid support functions
2666 //////////////////////////////////////////////////////////////
2667
2668 void wxDbTable::SetRowMode(const rowmode_t rowmode)
2669 {
2670 if (!m_hstmtGridQuery)
2671 {
2672 m_hstmtGridQuery = GetNewCursor(false,false);
2673 if (!bindCols(*m_hstmtGridQuery))
2674 return;
2675 }
2676
2677 m_rowmode = rowmode;
2678 switch (m_rowmode)
2679 {
2680 case WX_ROW_MODE_QUERY:
2681 SetCursor(m_hstmtGridQuery);
2682 break;
2683 case WX_ROW_MODE_INDIVIDUAL:
2684 SetCursor(hstmtDefault);
2685 break;
2686 default:
2687 wxASSERT(0);
2688 }
2689 } // wxDbTable::SetRowMode()
2690
2691
2692 wxVariant wxDbTable::GetCol(const int colNo) const
2693 {
2694 wxVariant val;
2695 if ((colNo < noCols) && (!IsColNull((UWORD)colNo)))
2696 {
2697 switch (colDefs[colNo].SqlCtype)
2698 {
2699 case SQL_CHAR:
2700 case SQL_VARCHAR:
2701 val = (wxChar *)(colDefs[colNo].PtrDataObj);
2702 break;
2703 case SQL_C_LONG:
2704 case SQL_C_SLONG:
2705 val = *(long *)(colDefs[colNo].PtrDataObj);
2706 break;
2707 case SQL_C_SHORT:
2708 case SQL_C_SSHORT:
2709 val = (long int )(*(short *)(colDefs[colNo].PtrDataObj));
2710 break;
2711 case SQL_C_ULONG:
2712 val = (long)(*(unsigned long *)(colDefs[colNo].PtrDataObj));
2713 break;
2714 case SQL_C_TINYINT:
2715 val = (long)(*(wxChar *)(colDefs[colNo].PtrDataObj));
2716 break;
2717 case SQL_C_UTINYINT:
2718 val = (long)(*(wxChar *)(colDefs[colNo].PtrDataObj));
2719 break;
2720 case SQL_C_USHORT:
2721 val = (long)(*(UWORD *)(colDefs[colNo].PtrDataObj));
2722 break;
2723 case SQL_C_DATE:
2724 val = (DATE_STRUCT *)(colDefs[colNo].PtrDataObj);
2725 break;
2726 case SQL_C_TIME:
2727 val = (TIME_STRUCT *)(colDefs[colNo].PtrDataObj);
2728 break;
2729 case SQL_C_TIMESTAMP:
2730 val = (TIMESTAMP_STRUCT *)(colDefs[colNo].PtrDataObj);
2731 break;
2732 case SQL_C_DOUBLE:
2733 val = *(double *)(colDefs[colNo].PtrDataObj);
2734 break;
2735 default:
2736 assert(0);
2737 }
2738 }
2739 return val;
2740 } // wxDbTable::GetCol()
2741
2742
2743 void wxDbTable::SetCol(const int colNo, const wxVariant val)
2744 {
2745 //FIXME: Add proper wxDateTime support to wxVariant..
2746 wxDateTime dateval;
2747
2748 SetColNull((UWORD)colNo, val.IsNull());
2749
2750 if (!val.IsNull())
2751 {
2752 if ((colDefs[colNo].SqlCtype == SQL_C_DATE)
2753 || (colDefs[colNo].SqlCtype == SQL_C_TIME)
2754 || (colDefs[colNo].SqlCtype == SQL_C_TIMESTAMP))
2755 {
2756 //Returns null if invalid!
2757 if (!dateval.ParseDate(val.GetString()))
2758 SetColNull((UWORD)colNo, true);
2759 }
2760
2761 switch (colDefs[colNo].SqlCtype)
2762 {
2763 case SQL_CHAR:
2764 case SQL_VARCHAR:
2765 csstrncpyt((wxChar *)(colDefs[colNo].PtrDataObj),
2766 val.GetString().c_str(),
2767 colDefs[colNo].SzDataObj-1); //TODO: glt ??? * sizeof(wxChar) ???
2768 break;
2769 case SQL_C_LONG:
2770 case SQL_C_SLONG:
2771 *(long *)(colDefs[colNo].PtrDataObj) = val;
2772 break;
2773 case SQL_C_SHORT:
2774 case SQL_C_SSHORT:
2775 *(short *)(colDefs[colNo].PtrDataObj) = (short)val.GetLong();
2776 break;
2777 case SQL_C_ULONG:
2778 *(unsigned long *)(colDefs[colNo].PtrDataObj) = val.GetLong();
2779 break;
2780 case SQL_C_TINYINT:
2781 *(wxChar *)(colDefs[colNo].PtrDataObj) = val.GetChar();
2782 break;
2783 case SQL_C_UTINYINT:
2784 *(wxChar *)(colDefs[colNo].PtrDataObj) = val.GetChar();
2785 break;
2786 case SQL_C_USHORT:
2787 *(unsigned short *)(colDefs[colNo].PtrDataObj) = (unsigned short)val.GetLong();
2788 break;
2789 //FIXME: Add proper wxDateTime support to wxVariant..
2790 case SQL_C_DATE:
2791 {
2792 DATE_STRUCT *dataptr =
2793 (DATE_STRUCT *)colDefs[colNo].PtrDataObj;
2794
2795 dataptr->year = (SWORD)dateval.GetYear();
2796 dataptr->month = (UWORD)(dateval.GetMonth()+1);
2797 dataptr->day = (UWORD)dateval.GetDay();
2798 }
2799 break;
2800 case SQL_C_TIME:
2801 {
2802 TIME_STRUCT *dataptr =
2803 (TIME_STRUCT *)colDefs[colNo].PtrDataObj;
2804
2805 dataptr->hour = dateval.GetHour();
2806 dataptr->minute = dateval.GetMinute();
2807 dataptr->second = dateval.GetSecond();
2808 }
2809 break;
2810 case SQL_C_TIMESTAMP:
2811 {
2812 TIMESTAMP_STRUCT *dataptr =
2813 (TIMESTAMP_STRUCT *)colDefs[colNo].PtrDataObj;
2814 dataptr->year = (SWORD)dateval.GetYear();
2815 dataptr->month = (UWORD)(dateval.GetMonth()+1);
2816 dataptr->day = (UWORD)dateval.GetDay();
2817
2818 dataptr->hour = dateval.GetHour();
2819 dataptr->minute = dateval.GetMinute();
2820 dataptr->second = dateval.GetSecond();
2821 }
2822 break;
2823 case SQL_C_DOUBLE:
2824 *(double *)(colDefs[colNo].PtrDataObj) = val;
2825 break;
2826 default:
2827 assert(0);
2828 } // switch
2829 } // if (!val.IsNull())
2830 } // wxDbTable::SetCol()
2831
2832
2833 GenericKey wxDbTable::GetKey()
2834 {
2835 void *blk;
2836 wxChar *blkptr;
2837
2838 blk = malloc(m_keysize);
2839 blkptr = (wxChar *) blk;
2840
2841 int i;
2842 for (i=0; i < noCols; i++)
2843 {
2844 if (colDefs[i].KeyField)
2845 {
2846 memcpy(blkptr,colDefs[i].PtrDataObj, colDefs[i].SzDataObj);
2847 blkptr += colDefs[i].SzDataObj;
2848 }
2849 }
2850
2851 GenericKey k = GenericKey(blk, m_keysize);
2852 free(blk);
2853
2854 return k;
2855 } // wxDbTable::GetKey()
2856
2857
2858 void wxDbTable::SetKey(const GenericKey& k)
2859 {
2860 void *blk;
2861 wxChar *blkptr;
2862
2863 blk = k.GetBlk();
2864 blkptr = (wxChar *)blk;
2865
2866 int i;
2867 for (i=0; i < noCols; i++)
2868 {
2869 if (colDefs[i].KeyField)
2870 {
2871 SetColNull((UWORD)i, false);
2872 memcpy(colDefs[i].PtrDataObj, blkptr, colDefs[i].SzDataObj);
2873 blkptr += colDefs[i].SzDataObj;
2874 }
2875 }
2876 } // wxDbTable::SetKey()
2877
2878
2879 #endif // wxUSE_ODBC
2880
wxWidgets (Impactor)