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