]> git.saurik.com Git - wxWidgets.git/blob - src/common/dbtable.cpp
projects / wxWidgets.git / blob
? search:


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