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