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