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