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