]> git.saurik.com Git - wxWidgets.git/blob - src/common/dbtable.cpp
projects / wxWidgets.git / blob
? search:


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