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