]> 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
wxNEED_STRDUP was wrong tested with #if instead of #ifdef
[wxWidgets.git] / src / common / dbtable.cpp
1 ///////////////////////////////////////////////////////////////////////////////
2 // Name: dbtable.cpp
3 // Purpose: Implementation of the wxDbTable class.
4 // Author: Doug Card
5 // Modified by: George Tasker
6 // Bart Jourquin
7 // Mark Johnson
8 // Created: 9.96
9 // RCS-ID: $Id$
10 // Copyright: (c) 1996 Remstar International, Inc.
11 // Licence: wxWindows licence
12 ///////////////////////////////////////////////////////////////////////////////
13
14 /*
15 // SYNOPSIS START
16 // SYNOPSIS STOP
17 */
18
19 #if defined(__GNUG__) && !defined(NO_GCC_PRAGMA)
20 #pragma implementation "dbtable.h"
21 #endif
22
23 #include "wx/wxprec.h"
24
25 #ifdef __BORLANDC__
26 #pragma hdrstop
27 #endif
28
29 #ifdef DBDEBUG_CONSOLE
30 #if wxUSE_IOSTREAMH
31 #include <iostream.h>
32 #else
33 #include <iostream>
34 #endif
35 #include "wx/ioswrap.h"
36 #endif
37
38 #ifndef WX_PRECOMP
39 #include "wx/string.h"
40 #include "wx/object.h"
41 #include "wx/list.h"
42 #include "wx/utils.h"
43 #include "wx/log.h"
44 #endif
45 #include "wx/filefn.h"
46
47 #if wxUSE_ODBC
48
49 #include <stdio.h>
50 #include <stdlib.h>
51 #include <string.h>
52
53 #include "wx/dbtable.h"
54
55 #ifdef __UNIX__
56 // The HPUX preprocessor lines below were commented out on 8/20/97
57 // because macros.h currently redefines DEBUG and is unneeded.
58 // # ifdef HPUX
59 // # include <macros.h>
60 // # endif
61 # ifdef LINUX
62 # include <sys/minmax.h>
63 # endif
64 #endif
65
66 ULONG lastTableID = 0;
67
68
69 #ifdef __WXDEBUG__
70 wxList TablesInUse;
71 #endif
72
73
74 void csstrncpyt(wxChar *target, const wxChar *source, int n)
75 {
76 while ( (*target++ = *source++) != '\0' && --n )
77 ;
78
79 *target = '\0';
80 }
81
82
83
84 /********** wxDbColDef::wxDbColDef() Constructor **********/
85 wxDbColDef::wxDbColDef()
86 {
87 Initialize();
88 } // Constructor
89
90
91 bool wxDbColDef::Initialize()
92 {
93 ColName[0] = 0;
94 DbDataType = DB_DATA_TYPE_INTEGER;
95 SqlCtype = SQL_C_LONG;
96 PtrDataObj = NULL;
97 SzDataObj = 0;
98 KeyField = false;
99 Updateable = false;
100 InsertAllowed = false;
101 DerivedCol = false;
102 CbValue = 0;
103 Null = false;
104
105 return true;
106 } // wxDbColDef::Initialize()
107
108
109 /********** wxDbTable::wxDbTable() Constructor **********/
110 wxDbTable::wxDbTable(wxDb *pwxDb, const wxString &tblName, const UWORD numColumns,
111 const wxString &qryTblName, bool qryOnly, const wxString &tblPath)
112 {
113 if (!initialize(pwxDb, tblName, numColumns, qryTblName, qryOnly, tblPath))
114 cleanup();
115 } // wxDbTable::wxDbTable()
116
117
118 /***** DEPRECATED: use wxDbTable::wxDbTable() format above *****/
119 #if WXWIN_COMPATIBILITY_2_4
120 wxDbTable::wxDbTable(wxDb *pwxDb, const wxString &tblName, const UWORD numColumns,
121 const wxChar *qryTblName, bool qryOnly, const wxString &tblPath)
122 {
123 wxString tempQryTblName;
124 tempQryTblName = qryTblName;
125 if (!initialize(pwxDb, tblName, numColumns, tempQryTblName, qryOnly, tblPath))
126 cleanup();
127 } // wxDbTable::wxDbTable()
128 #endif // WXWIN_COMPATIBILITY_2_4
129
130
131 /********** wxDbTable::~wxDbTable() **********/
132 wxDbTable::~wxDbTable()
133 {
134 this->cleanup();
135 } // wxDbTable::~wxDbTable()
136
137
138 bool wxDbTable::initialize(wxDb *pwxDb, const wxString &tblName, const UWORD numColumns,
139 const wxString &qryTblName, bool qryOnly, const wxString &tblPath)
140 {
141 // Initializing member variables
142 pDb = pwxDb; // Pointer to the wxDb object
143 henv = 0;
144 hdbc = 0;
145 hstmt = 0;
146 m_hstmtGridQuery = 0;
147 hstmtDefault = 0; // Initialized below
148 hstmtCount = 0; // Initialized first time it is needed
149 hstmtInsert = 0;
150 hstmtDelete = 0;
151 hstmtUpdate = 0;
152 hstmtInternal = 0;
153 colDefs = 0;
154 tableID = 0;
155 m_numCols = numColumns; // Number of columns in the table
156 where.Empty(); // Where clause
157 orderBy.Empty(); // Order By clause
158 from.Empty(); // From clause
159 selectForUpdate = false; // SELECT ... FOR UPDATE; Indicates whether to include the FOR UPDATE phrase
160 queryOnly = qryOnly;
161 insertable = true;
162 tablePath.Empty();
163 tableName.Empty();
164 queryTableName.Empty();
165
166 wxASSERT(tblName.Length());
167 wxASSERT(pDb);
168
169 if (!pDb)
170 return false;
171
172 tableName = tblName; // Table Name
173 if ((pDb->Dbms() == dbmsORACLE) ||
174 (pDb->Dbms() == dbmsFIREBIRD) ||
175 (pDb->Dbms() == dbmsINTERBASE))
176 tableName = tableName.Upper();
177
178 if (tblPath.Length())
179 tablePath = tblPath; // Table Path - used for dBase files
180 else
181 tablePath.Empty();
182
183 if (qryTblName.Length()) // Name of the table/view to query
184 queryTableName = qryTblName;
185 else
186 queryTableName = tblName;
187
188 if ((pDb->Dbms() == dbmsORACLE) ||
189 (pDb->Dbms() == dbmsFIREBIRD) ||
190 (pDb->Dbms() == dbmsINTERBASE))
191 queryTableName = queryTableName.Upper();
192
193 pDb->incrementTableCount();
194
195 wxString s;
196 tableID = ++lastTableID;
197 s.Printf(wxT("wxDbTable constructor (%-20s) tableID:[%6lu] pDb:[%p]"), tblName.c_str(), tableID, pDb);
198
199 #ifdef __WXDEBUG__
200 wxTablesInUse *tableInUse;
201 tableInUse = new wxTablesInUse();
202 tableInUse->tableName = tblName;
203 tableInUse->tableID = tableID;
204 tableInUse->pDb = pDb;
205 TablesInUse.Append(tableInUse);
206 #endif
207
208 pDb->WriteSqlLog(s);
209
210 // Grab the HENV and HDBC from the wxDb object
211 henv = pDb->GetHENV();
212 hdbc = pDb->GetHDBC();
213
214 // Allocate space for column definitions
215 if (m_numCols)
216 colDefs = new wxDbColDef[m_numCols]; // Points to the first column definition
217
218 // Allocate statement handles for the table
219 if (!queryOnly)
220 {
221 // Allocate a separate statement handle for performing inserts
222 if (SQLAllocStmt(hdbc, &hstmtInsert) != SQL_SUCCESS)
223 pDb->DispAllErrors(henv, hdbc);
224 // Allocate a separate statement handle for performing deletes
225 if (SQLAllocStmt(hdbc, &hstmtDelete) != SQL_SUCCESS)
226 pDb->DispAllErrors(henv, hdbc);
227 // Allocate a separate statement handle for performing updates
228 if (SQLAllocStmt(hdbc, &hstmtUpdate) != SQL_SUCCESS)
229 pDb->DispAllErrors(henv, hdbc);
230 }
231 // Allocate a separate statement handle for internal use
232 if (SQLAllocStmt(hdbc, &hstmtInternal) != SQL_SUCCESS)
233 pDb->DispAllErrors(henv, hdbc);
234
235 // Set the cursor type for the statement handles
236 cursorType = SQL_CURSOR_STATIC;
237
238 if (SQLSetStmtOption(hstmtInternal, SQL_CURSOR_TYPE, cursorType) != SQL_SUCCESS)
239 {
240 // Check to see if cursor type is supported
241 pDb->GetNextError(henv, hdbc, hstmtInternal);
242 if (! wxStrcmp(pDb->sqlState, wxT("01S02"))) // Option Value Changed
243 {
244 // Datasource does not support static cursors. Driver
245 // will substitute a cursor type. Call SQLGetStmtOption()
246 // to determine which cursor type was selected.
247 if (SQLGetStmtOption(hstmtInternal, SQL_CURSOR_TYPE, &cursorType) != SQL_SUCCESS)
248 pDb->DispAllErrors(henv, hdbc, hstmtInternal);
249 #ifdef DBDEBUG_CONSOLE
250 cout << wxT("Static cursor changed to: ");
251 switch(cursorType)
252 {
253 case SQL_CURSOR_FORWARD_ONLY:
254 cout << wxT("Forward Only");
255 break;
256 case SQL_CURSOR_STATIC:
257 cout << wxT("Static");
258 break;
259 case SQL_CURSOR_KEYSET_DRIVEN:
260 cout << wxT("Keyset Driven");
261 break;
262 case SQL_CURSOR_DYNAMIC:
263 cout << wxT("Dynamic");
264 break;
265 }
266 cout << endl << endl;
267 #endif
268 // BJO20000425
269 if (pDb->FwdOnlyCursors() && cursorType != SQL_CURSOR_FORWARD_ONLY)
270 {
271 // Force the use of a forward only cursor...
272 cursorType = SQL_CURSOR_FORWARD_ONLY;
273 if (SQLSetStmtOption(hstmtInternal, SQL_CURSOR_TYPE, cursorType) != SQL_SUCCESS)
274 {
275 // Should never happen
276 pDb->GetNextError(henv, hdbc, hstmtInternal);
277 return false;
278 }
279 }
280 }
281 else
282 {
283 pDb->DispNextError();
284 pDb->DispAllErrors(henv, hdbc, hstmtInternal);
285 }
286 }
287 #ifdef DBDEBUG_CONSOLE
288 else
289 cout << wxT("Cursor Type set to STATIC") << endl << endl;
290 #endif
291
292 if (!queryOnly)
293 {
294 // Set the cursor type for the INSERT statement handle
295 if (SQLSetStmtOption(hstmtInsert, SQL_CURSOR_TYPE, SQL_CURSOR_FORWARD_ONLY) != SQL_SUCCESS)
296 pDb->DispAllErrors(henv, hdbc, hstmtInsert);
297 // Set the cursor type for the DELETE statement handle
298 if (SQLSetStmtOption(hstmtDelete, SQL_CURSOR_TYPE, SQL_CURSOR_FORWARD_ONLY) != SQL_SUCCESS)
299 pDb->DispAllErrors(henv, hdbc, hstmtDelete);
300 // Set the cursor type for the UPDATE statement handle
301 if (SQLSetStmtOption(hstmtUpdate, SQL_CURSOR_TYPE, SQL_CURSOR_FORWARD_ONLY) != SQL_SUCCESS)
302 pDb->DispAllErrors(henv, hdbc, hstmtUpdate);
303 }
304
305 // Make the default cursor the active cursor
306 hstmtDefault = GetNewCursor(false,false);
307 wxASSERT(hstmtDefault);
308 hstmt = *hstmtDefault;
309
310 return true;
311
312 } // wxDbTable::initialize()
313
314
315 void wxDbTable::cleanup()
316 {
317 wxString s;
318 if (pDb)
319 {
320 s.Printf(wxT("wxDbTable destructor (%-20s) tableID:[%6lu] pDb:[%p]"), tableName.c_str(), tableID, pDb);
321 pDb->WriteSqlLog(s);
322 }
323
324 #ifdef __WXDEBUG__
325 if (tableID)
326 {
327 bool found = false;
328
329 wxList::compatibility_iterator pNode;
330 pNode = TablesInUse.GetFirst();
331 while (pNode && !found)
332 {
333 if (((wxTablesInUse *)pNode->GetData())->tableID == tableID)
334 {
335 found = true;
336 delete (wxTablesInUse *)pNode->GetData();
337 TablesInUse.Erase(pNode);
338 }
339 else
340 pNode = pNode->GetNext();
341 }
342 if (!found)
343 {
344 wxString msg;
345 msg.Printf(wxT("Unable to find the tableID in the linked\nlist of tables in use.\n\n%s"),s.c_str());
346 wxLogDebug (msg,wxT("NOTICE..."));
347 }
348 }
349 #endif
350
351 // Decrement the wxDb table count
352 if (pDb)
353 pDb->decrementTableCount();
354
355 // Delete memory allocated for column definitions
356 if (colDefs)
357 delete [] colDefs;
358
359 // Free statement handles
360 if (!queryOnly)
361 {
362 if (hstmtInsert)
363 {
364 /*
365 ODBC 3.0 says to use this form
366 if (SQLFreeHandle(*hstmtDel, SQL_DROP) != SQL_SUCCESS)
367 */
368 if (SQLFreeStmt(hstmtInsert, SQL_DROP) != SQL_SUCCESS)
369 pDb->DispAllErrors(henv, hdbc);
370 }
371
372 if (hstmtDelete)
373 {
374 /*
375 ODBC 3.0 says to use this form
376 if (SQLFreeHandle(*hstmtDel, SQL_DROP) != SQL_SUCCESS)
377 */
378 if (SQLFreeStmt(hstmtDelete, SQL_DROP) != SQL_SUCCESS)
379 pDb->DispAllErrors(henv, hdbc);
380 }
381
382 if (hstmtUpdate)
383 {
384 /*
385 ODBC 3.0 says to use this form
386 if (SQLFreeHandle(*hstmtDel, SQL_DROP) != SQL_SUCCESS)
387 */
388 if (SQLFreeStmt(hstmtUpdate, SQL_DROP) != SQL_SUCCESS)
389 pDb->DispAllErrors(henv, hdbc);
390 }
391 }
392
393 if (hstmtInternal)
394 {
395 if (SQLFreeStmt(hstmtInternal, SQL_DROP) != SQL_SUCCESS)
396 pDb->DispAllErrors(henv, hdbc);
397 }
398
399 // Delete dynamically allocated cursors
400 if (hstmtDefault)
401 DeleteCursor(hstmtDefault);
402
403 if (hstmtCount)
404 DeleteCursor(hstmtCount);
405
406 if (m_hstmtGridQuery)
407 DeleteCursor(m_hstmtGridQuery);
408
409 } // wxDbTable::cleanup()
410
411
412 /***************************** PRIVATE FUNCTIONS *****************************/
413
414
415 void wxDbTable::setCbValueForColumn(int columnIndex)
416 {
417 switch(colDefs[columnIndex].DbDataType)
418 {
419 case DB_DATA_TYPE_VARCHAR:
420 if (colDefs[columnIndex].Null)
421 colDefs[columnIndex].CbValue = SQL_NULL_DATA;
422 else
423 colDefs[columnIndex].CbValue = SQL_NTS;
424 break;
425 case DB_DATA_TYPE_INTEGER:
426 if (colDefs[columnIndex].Null)
427 colDefs[columnIndex].CbValue = SQL_NULL_DATA;
428 else
429 colDefs[columnIndex].CbValue = 0;
430 break;
431 case DB_DATA_TYPE_FLOAT:
432 if (colDefs[columnIndex].Null)
433 colDefs[columnIndex].CbValue = SQL_NULL_DATA;
434 else
435 colDefs[columnIndex].CbValue = 0;
436 break;
437 case DB_DATA_TYPE_DATE:
438 if (colDefs[columnIndex].Null)
439 colDefs[columnIndex].CbValue = SQL_NULL_DATA;
440 else
441 colDefs[columnIndex].CbValue = 0;
442 break;
443 case DB_DATA_TYPE_BLOB:
444 if (colDefs[columnIndex].Null)
445 colDefs[columnIndex].CbValue = SQL_NULL_DATA;
446 else
447 if (colDefs[columnIndex].SqlCtype == SQL_C_WXCHAR)
448 colDefs[columnIndex].CbValue = SQL_NTS;
449 else
450 colDefs[columnIndex].CbValue = SQL_LEN_DATA_AT_EXEC(colDefs[columnIndex].SzDataObj);
451 break;
452 }
453 }
454
455 /********** wxDbTable::bindParams() **********/
456 bool wxDbTable::bindParams(bool forUpdate)
457 {
458 wxASSERT(!queryOnly);
459 if (queryOnly)
460 return false;
461
462 SWORD fSqlType = 0;
463 SDWORD precision = 0;
464 SWORD scale = 0;
465
466 // Bind each column of the table that should be bound
467 // to a parameter marker
468 int i;
469 UWORD colNumber;
470
471 for (i=0, colNumber=1; i < m_numCols; i++)
472 {
473 if (forUpdate)
474 {
475 if (!colDefs[i].Updateable)
476 continue;
477 }
478 else
479 {
480 if (!colDefs[i].InsertAllowed)
481 continue;
482 }
483
484 switch(colDefs[i].DbDataType)
485 {
486 case DB_DATA_TYPE_VARCHAR:
487 fSqlType = pDb->GetTypeInfVarchar().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 SDWORD 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 UDWORD 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 SDWORD 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 SDWORD 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_INTEGER:
1422 cout << pDb->GetTypeInfInteger().TypeName;
1423 break;
1424 case DB_DATA_TYPE_FLOAT:
1425 cout << pDb->GetTypeInfFloat().TypeName;
1426 break;
1427 case DB_DATA_TYPE_DATE:
1428 cout << pDb->GetTypeInfDate().TypeName;
1429 break;
1430 case DB_DATA_TYPE_BLOB:
1431 cout << pDb->GetTypeInfBlob().TypeName;
1432 break;
1433 }
1434 cout << endl;
1435 }
1436 #endif
1437
1438 // Build a CREATE TABLE string from the colDefs structure.
1439 bool needComma = false;
1440
1441 sqlStmt.Printf(wxT("CREATE TABLE %s ("),
1442 pDb->SQLTableName(tableName.c_str()).c_str());
1443
1444 for (i = 0; i < m_numCols; i++)
1445 {
1446 // Exclude derived columns since they are NOT part of the base table
1447 if (colDefs[i].DerivedCol)
1448 continue;
1449 // Comma Delimiter
1450 if (needComma)
1451 sqlStmt += wxT(",");
1452 // Column Name
1453 sqlStmt += pDb->SQLColumnName(colDefs[i].ColName);
1454 // sqlStmt += colDefs[i].ColName;
1455 sqlStmt += wxT(" ");
1456 // Column Type
1457 switch(colDefs[i].DbDataType)
1458 {
1459 case DB_DATA_TYPE_VARCHAR:
1460 sqlStmt += pDb->GetTypeInfVarchar().TypeName;
1461 break;
1462 case DB_DATA_TYPE_INTEGER:
1463 sqlStmt += pDb->GetTypeInfInteger().TypeName;
1464 break;
1465 case DB_DATA_TYPE_FLOAT:
1466 sqlStmt += pDb->GetTypeInfFloat().TypeName;
1467 break;
1468 case DB_DATA_TYPE_DATE:
1469 sqlStmt += pDb->GetTypeInfDate().TypeName;
1470 break;
1471 case DB_DATA_TYPE_BLOB:
1472 sqlStmt += pDb->GetTypeInfBlob().TypeName;
1473 break;
1474 }
1475 // For varchars, append the size of the string
1476 if (colDefs[i].DbDataType == DB_DATA_TYPE_VARCHAR &&
1477 (pDb->Dbms() != dbmsMY_SQL || pDb->GetTypeInfVarchar().TypeName != _T("text")))// ||
1478 // colDefs[i].DbDataType == DB_DATA_TYPE_BLOB)
1479 {
1480 wxString s;
1481 s.Printf(wxT("(%d)"), (int)(colDefs[i].SzDataObj / sizeof(wxChar)));
1482 sqlStmt += s;
1483 }
1484
1485 if (pDb->Dbms() == dbmsDB2 ||
1486 pDb->Dbms() == dbmsMY_SQL ||
1487 pDb->Dbms() == dbmsSYBASE_ASE ||
1488 pDb->Dbms() == dbmsINTERBASE ||
1489 pDb->Dbms() == dbmsFIREBIRD ||
1490 pDb->Dbms() == dbmsMS_SQL_SERVER)
1491 {
1492 if (colDefs[i].KeyField)
1493 {
1494 sqlStmt += wxT(" NOT NULL");
1495 }
1496 }
1497
1498 needComma = true;
1499 }
1500 // If there is a primary key defined, include it in the create statement
1501 for (i = j = 0; i < m_numCols; i++)
1502 {
1503 if (colDefs[i].KeyField)
1504 {
1505 j++;
1506 break;
1507 }
1508 }
1509 if ( j && (pDb->Dbms() != dbmsDBASE)
1510 && (pDb->Dbms() != dbmsXBASE_SEQUITER) ) // Found a keyfield
1511 {
1512 switch (pDb->Dbms())
1513 {
1514 case dbmsACCESS:
1515 case dbmsINFORMIX:
1516 case dbmsSYBASE_ASA:
1517 case dbmsSYBASE_ASE:
1518 case dbmsMY_SQL:
1519 case dbmsFIREBIRD:
1520 {
1521 // MySQL goes out on this one. We also declare the relevant key NON NULL above
1522 sqlStmt += wxT(",PRIMARY KEY (");
1523 break;
1524 }
1525 default:
1526 {
1527 sqlStmt += wxT(",CONSTRAINT ");
1528 // DB2 is limited to 18 characters for index names
1529 if (pDb->Dbms() == dbmsDB2)
1530 {
1531 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."));
1532 sqlStmt += pDb->SQLTableName(tableName.substr(0, 13).c_str());
1533 // sqlStmt += tableName.substr(0, 13);
1534 }
1535 else
1536 sqlStmt += pDb->SQLTableName(tableName.c_str());
1537 // sqlStmt += tableName;
1538
1539 sqlStmt += wxT("_PIDX PRIMARY KEY (");
1540 break;
1541 }
1542 }
1543
1544 // List column name(s) of column(s) comprising the primary key
1545 for (i = j = 0; i < m_numCols; i++)
1546 {
1547 if (colDefs[i].KeyField)
1548 {
1549 if (j++) // Multi part key, comma separate names
1550 sqlStmt += wxT(",");
1551 sqlStmt += pDb->SQLColumnName(colDefs[i].ColName);
1552
1553 if (pDb->Dbms() == dbmsMY_SQL &&
1554 colDefs[i].DbDataType == DB_DATA_TYPE_VARCHAR)
1555 {
1556 wxString s;
1557 s.Printf(wxT("(%d)"), (int)(colDefs[i].SzDataObj / sizeof(wxChar)));
1558 sqlStmt += s;
1559 }
1560 }
1561 }
1562 sqlStmt += wxT(")");
1563
1564 if (pDb->Dbms() == dbmsINFORMIX ||
1565 pDb->Dbms() == dbmsSYBASE_ASA ||
1566 pDb->Dbms() == dbmsSYBASE_ASE)
1567 {
1568 sqlStmt += wxT(" CONSTRAINT ");
1569 sqlStmt += pDb->SQLTableName(tableName);
1570 // sqlStmt += tableName;
1571 sqlStmt += wxT("_PIDX");
1572 }
1573 }
1574 // Append the closing parentheses for the create table statement
1575 sqlStmt += wxT(")");
1576
1577 pDb->WriteSqlLog(sqlStmt);
1578
1579 #ifdef DBDEBUG_CONSOLE
1580 cout << endl << sqlStmt.c_str() << endl;
1581 #endif
1582
1583 // Execute the CREATE TABLE statement
1584 RETCODE retcode = SQLExecDirect(hstmt, (SQLTCHAR FAR *) sqlStmt.c_str(), SQL_NTS);
1585 if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
1586 {
1587 pDb->DispAllErrors(henv, hdbc, hstmt);
1588 pDb->RollbackTrans();
1589 CloseCursor(hstmt);
1590 return false;
1591 }
1592
1593 // Commit the transaction and close the cursor
1594 if (!pDb->CommitTrans())
1595 return false;
1596 if (!CloseCursor(hstmt))
1597 return false;
1598
1599 // Database table created successfully
1600 return true;
1601
1602 } // wxDbTable::CreateTable()
1603
1604
1605 /********** wxDbTable::DropTable() **********/
1606 bool wxDbTable::DropTable()
1607 {
1608 // NOTE: This function returns true if the Table does not exist, but
1609 // only for identified databases. Code will need to be added
1610 // below for any other databases when those databases are defined
1611 // to handle this situation consistently
1612
1613 wxString sqlStmt;
1614
1615 sqlStmt.Printf(wxT("DROP TABLE %s"),
1616 pDb->SQLTableName(tableName.c_str()).c_str());
1617
1618 pDb->WriteSqlLog(sqlStmt);
1619
1620 #ifdef DBDEBUG_CONSOLE
1621 cout << endl << sqlStmt.c_str() << endl;
1622 #endif
1623
1624 RETCODE retcode = SQLExecDirect(hstmt, (SQLTCHAR FAR *) sqlStmt.c_str(), SQL_NTS);
1625 if (retcode != SQL_SUCCESS)
1626 {
1627 // Check for "Base table not found" error and ignore
1628 pDb->GetNextError(henv, hdbc, hstmt);
1629 if (wxStrcmp(pDb->sqlState, wxT("S0002")) /*&&
1630 wxStrcmp(pDb->sqlState, wxT("S1000"))*/) // "Base table not found"
1631 {
1632 // Check for product specific error codes
1633 if (!((pDb->Dbms() == dbmsSYBASE_ASA && !wxStrcmp(pDb->sqlState,wxT("42000"))) || // 5.x (and lower?)
1634 (pDb->Dbms() == dbmsSYBASE_ASE && !wxStrcmp(pDb->sqlState,wxT("37000"))) ||
1635 (pDb->Dbms() == dbmsPERVASIVE_SQL && !wxStrcmp(pDb->sqlState,wxT("S1000"))) || // Returns an S1000 then an S0002
1636 (pDb->Dbms() == dbmsPOSTGRES && !wxStrcmp(pDb->sqlState,wxT("08S01")))))
1637 {
1638 pDb->DispNextError();
1639 pDb->DispAllErrors(henv, hdbc, hstmt);
1640 pDb->RollbackTrans();
1641 // CloseCursor(hstmt);
1642 return false;
1643 }
1644 }
1645 }
1646
1647 // Commit the transaction and close the cursor
1648 if (! pDb->CommitTrans())
1649 return false;
1650 if (! CloseCursor(hstmt))
1651 return false;
1652
1653 return true;
1654 } // wxDbTable::DropTable()
1655
1656
1657 /********** wxDbTable::CreateIndex() **********/
1658 bool wxDbTable::CreateIndex(const wxString &indexName, bool unique, UWORD numIndexColumns,
1659 wxDbIdxDef *pIndexDefs, bool attemptDrop)
1660 {
1661 wxString sqlStmt;
1662
1663 // Drop the index first
1664 if (attemptDrop && !DropIndex(indexName))
1665 return false;
1666
1667 // MySQL (and possibly Sybase ASE?? - gt) require that any columns which are used as portions
1668 // of an index have the columns defined as "NOT NULL". During initial table creation though,
1669 // it may not be known which columns are necessarily going to be part of an index (e.g. the
1670 // table was created, then months later you determine that an additional index while
1671 // give better performance, so you want to add an index).
1672 //
1673 // The following block of code will modify the column definition to make the column be
1674 // defined with the "NOT NULL" qualifier.
1675 if (pDb->Dbms() == dbmsMY_SQL)
1676 {
1677 wxString sqlStmt;
1678 int i;
1679 bool ok = true;
1680 for (i = 0; i < numIndexColumns && ok; i++)
1681 {
1682 int j = 0;
1683 bool found = false;
1684 // Find the column definition that has the ColName that matches the
1685 // index column name. We need to do this to get the DB_DATA_TYPE of
1686 // the index column, as MySQL's syntax for the ALTER column requires
1687 // this information
1688 while (!found && (j < this->m_numCols))
1689 {
1690 if (wxStrcmp(colDefs[j].ColName,pIndexDefs[i].ColName) == 0)
1691 found = true;
1692 if (!found)
1693 j++;
1694 }
1695
1696 if (found)
1697 {
1698 ok = pDb->ModifyColumn(tableName, pIndexDefs[i].ColName,
1699 colDefs[j].DbDataType, (int)(colDefs[j].SzDataObj / sizeof(wxChar)),
1700 wxT("NOT NULL"));
1701
1702 if (!ok)
1703 {
1704 #if 0
1705 // retcode is not used
1706 wxODBC_ERRORS retcode;
1707 // Oracle returns a DB_ERR_GENERAL_ERROR if the column is already
1708 // defined to be NOT NULL, but reportedly MySQL doesn't mind.
1709 // This line is just here for debug checking of the value
1710 retcode = (wxODBC_ERRORS)pDb->DB_STATUS;
1711 #endif
1712 }
1713 }
1714 else
1715 ok = false;
1716 }
1717 if (ok)
1718 pDb->CommitTrans();
1719 else
1720 {
1721 pDb->RollbackTrans();
1722 return false;
1723 }
1724 }
1725
1726 // Build a CREATE INDEX statement
1727 sqlStmt = wxT("CREATE ");
1728 if (unique)
1729 sqlStmt += wxT("UNIQUE ");
1730
1731 sqlStmt += wxT("INDEX ");
1732 sqlStmt += pDb->SQLTableName(indexName);
1733 sqlStmt += wxT(" ON ");
1734
1735 sqlStmt += pDb->SQLTableName(tableName);
1736 // sqlStmt += tableName;
1737 sqlStmt += wxT(" (");
1738
1739 // Append list of columns making up index
1740 int i;
1741 for (i = 0; i < numIndexColumns; i++)
1742 {
1743 sqlStmt += pDb->SQLColumnName(pIndexDefs[i].ColName);
1744 // sqlStmt += pIndexDefs[i].ColName;
1745
1746 // MySQL requires a key length on VARCHAR keys
1747 if ( pDb->Dbms() == dbmsMY_SQL )
1748 {
1749 // Find the details on this column
1750 int j;
1751 for ( j = 0; j < m_numCols; ++j )
1752 {
1753 if ( wxStrcmp( pIndexDefs[i].ColName, colDefs[j].ColName ) == 0 )
1754 {
1755 break;
1756 }
1757 }
1758 if ( colDefs[j].DbDataType == DB_DATA_TYPE_VARCHAR)
1759 {
1760 wxString s;
1761 s.Printf(wxT("(%d)"), (int)(colDefs[i].SzDataObj / sizeof(wxChar)));
1762 sqlStmt += s;
1763 }
1764 }
1765
1766 // Postgres and SQL Server 7 do not support the ASC/DESC keywords for index columns
1767 if (!((pDb->Dbms() == dbmsMS_SQL_SERVER) && (wxStrncmp(pDb->dbInf.dbmsVer,_T("07"),2)==0)) &&
1768 !(pDb->Dbms() == dbmsFIREBIRD) &&
1769 !(pDb->Dbms() == dbmsPOSTGRES))
1770 {
1771 if (pIndexDefs[i].Ascending)
1772 sqlStmt += wxT(" ASC");
1773 else
1774 sqlStmt += wxT(" DESC");
1775 }
1776 else
1777 wxASSERT_MSG(pIndexDefs[i].Ascending, _T("Datasource does not support DESCending index columns"));
1778
1779 if ((i + 1) < numIndexColumns)
1780 sqlStmt += wxT(",");
1781 }
1782
1783 // Append closing parentheses
1784 sqlStmt += wxT(")");
1785
1786 pDb->WriteSqlLog(sqlStmt);
1787
1788 #ifdef DBDEBUG_CONSOLE
1789 cout << endl << sqlStmt.c_str() << endl << endl;
1790 #endif
1791
1792 // Execute the CREATE INDEX statement
1793 RETCODE retcode = SQLExecDirect(hstmt, (SQLTCHAR FAR *) sqlStmt.c_str(), SQL_NTS);
1794 if (retcode != SQL_SUCCESS)
1795 {
1796 pDb->DispAllErrors(henv, hdbc, hstmt);
1797 pDb->RollbackTrans();
1798 CloseCursor(hstmt);
1799 return false;
1800 }
1801
1802 // Commit the transaction and close the cursor
1803 if (! pDb->CommitTrans())
1804 return false;
1805 if (! CloseCursor(hstmt))
1806 return false;
1807
1808 // Index Created Successfully
1809 return true;
1810
1811 } // wxDbTable::CreateIndex()
1812
1813
1814 /********** wxDbTable::DropIndex() **********/
1815 bool wxDbTable::DropIndex(const wxString &indexName)
1816 {
1817 // NOTE: This function returns true if the Index does not exist, but
1818 // only for identified databases. Code will need to be added
1819 // below for any other databases when those databases are defined
1820 // to handle this situation consistently
1821
1822 wxString sqlStmt;
1823
1824 if (pDb->Dbms() == dbmsACCESS || pDb->Dbms() == dbmsMY_SQL ||
1825 pDb->Dbms() == dbmsDBASE /*|| Paradox needs this syntax too when we add support*/)
1826 sqlStmt.Printf(wxT("DROP INDEX %s ON %s"),
1827 pDb->SQLTableName(indexName.c_str()).c_str(),
1828 pDb->SQLTableName(tableName.c_str()).c_str());
1829 else if ((pDb->Dbms() == dbmsMS_SQL_SERVER) ||
1830 (pDb->Dbms() == dbmsSYBASE_ASE) ||
1831 (pDb->Dbms() == dbmsXBASE_SEQUITER))
1832 sqlStmt.Printf(wxT("DROP INDEX %s.%s"),
1833 pDb->SQLTableName(tableName.c_str()).c_str(),
1834 pDb->SQLTableName(indexName.c_str()).c_str());
1835 else
1836 sqlStmt.Printf(wxT("DROP INDEX %s"),
1837 pDb->SQLTableName(indexName.c_str()).c_str());
1838
1839 pDb->WriteSqlLog(sqlStmt);
1840
1841 #ifdef DBDEBUG_CONSOLE
1842 cout << endl << sqlStmt.c_str() << endl;
1843 #endif
1844 RETCODE retcode = SQLExecDirect(hstmt, (SQLTCHAR FAR *) sqlStmt.c_str(), SQL_NTS);
1845 if (retcode != SQL_SUCCESS)
1846 {
1847 // Check for "Index not found" error and ignore
1848 pDb->GetNextError(henv, hdbc, hstmt);
1849 if (wxStrcmp(pDb->sqlState,wxT("S0012"))) // "Index not found"
1850 {
1851 // Check for product specific error codes
1852 if (!((pDb->Dbms() == dbmsSYBASE_ASA && !wxStrcmp(pDb->sqlState,wxT("42000"))) || // v5.x (and lower?)
1853 (pDb->Dbms() == dbmsSYBASE_ASE && !wxStrcmp(pDb->sqlState,wxT("37000"))) ||
1854 (pDb->Dbms() == dbmsMS_SQL_SERVER && !wxStrcmp(pDb->sqlState,wxT("S1000"))) ||
1855 (pDb->Dbms() == dbmsINTERBASE && !wxStrcmp(pDb->sqlState,wxT("S1000"))) ||
1856 (pDb->Dbms() == dbmsMAXDB && !wxStrcmp(pDb->sqlState,wxT("S1000"))) ||
1857 (pDb->Dbms() == dbmsFIREBIRD && !wxStrcmp(pDb->sqlState,wxT("HY000"))) ||
1858 (pDb->Dbms() == dbmsSYBASE_ASE && !wxStrcmp(pDb->sqlState,wxT("S0002"))) || // Base table not found
1859 (pDb->Dbms() == dbmsMY_SQL && !wxStrcmp(pDb->sqlState,wxT("42S12"))) || // tested by Christopher Ludwik Marino-Cebulski using v3.23.21beta
1860 (pDb->Dbms() == dbmsPOSTGRES && !wxStrcmp(pDb->sqlState,wxT("08S01")))
1861 ))
1862 {
1863 pDb->DispNextError();
1864 pDb->DispAllErrors(henv, hdbc, hstmt);
1865 pDb->RollbackTrans();
1866 CloseCursor(hstmt);
1867 return false;
1868 }
1869 }
1870 }
1871
1872 // Commit the transaction and close the cursor
1873 if (! pDb->CommitTrans())
1874 return false;
1875 if (! CloseCursor(hstmt))
1876 return false;
1877
1878 return true;
1879 } // wxDbTable::DropIndex()
1880
1881
1882 /********** wxDbTable::SetOrderByColNums() **********/
1883 bool wxDbTable::SetOrderByColNums(UWORD first, ... )
1884 {
1885 int colNumber = first; // using 'int' to be able to look for wxDB_NO_MORE_COLUN_NUMBERS
1886 va_list argptr;
1887
1888 bool abort = false;
1889 wxString tempStr;
1890
1891 va_start(argptr, first); /* Initialize variable arguments. */
1892 while (!abort && (colNumber != wxDB_NO_MORE_COLUMN_NUMBERS))
1893 {
1894 // Make sure the passed in column number
1895 // is within the valid range of columns
1896 //
1897 // Valid columns are 0 thru m_numCols-1
1898 if (colNumber >= m_numCols || colNumber < 0)
1899 {
1900 abort = true;
1901 continue;
1902 }
1903
1904 if (colNumber != first)
1905 tempStr += wxT(",");
1906
1907 tempStr += colDefs[colNumber].ColName;
1908 colNumber = va_arg (argptr, int);
1909 }
1910 va_end (argptr); /* Reset variable arguments. */
1911
1912 SetOrderByClause(tempStr);
1913
1914 return (!abort);
1915 } // wxDbTable::SetOrderByColNums()
1916
1917
1918 /********** wxDbTable::Insert() **********/
1919 int wxDbTable::Insert(void)
1920 {
1921 wxASSERT(!queryOnly);
1922 if (queryOnly || !insertable)
1923 return(DB_FAILURE);
1924
1925 bindInsertParams();
1926
1927 // Insert the record by executing the already prepared insert statement
1928 RETCODE retcode;
1929 retcode = SQLExecute(hstmtInsert);
1930 if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO &&
1931 retcode != SQL_NEED_DATA)
1932 {
1933 // Check to see if integrity constraint was violated
1934 pDb->GetNextError(henv, hdbc, hstmtInsert);
1935 if (! wxStrcmp(pDb->sqlState, wxT("23000"))) // Integrity constraint violated
1936 return(DB_ERR_INTEGRITY_CONSTRAINT_VIOL);
1937 else
1938 {
1939 pDb->DispNextError();
1940 pDb->DispAllErrors(henv, hdbc, hstmtInsert);
1941 return(DB_FAILURE);
1942 }
1943 }
1944 if (retcode == SQL_NEED_DATA)
1945 {
1946 PTR pParmID;
1947 retcode = SQLParamData(hstmtInsert, &pParmID);
1948 while (retcode == SQL_NEED_DATA)
1949 {
1950 // Find the parameter
1951 int i;
1952 for (i=0; i < m_numCols; i++)
1953 {
1954 if (colDefs[i].PtrDataObj == pParmID)
1955 {
1956 // We found it. Store the parameter.
1957 retcode = SQLPutData(hstmtInsert, pParmID, colDefs[i].SzDataObj);
1958 if (retcode != SQL_SUCCESS)
1959 {
1960 pDb->DispNextError();
1961 pDb->DispAllErrors(henv, hdbc, hstmtInsert);
1962 return(DB_FAILURE);
1963 }
1964 break;
1965 }
1966 }
1967 retcode = SQLParamData(hstmtInsert, &pParmID);
1968 if (retcode != SQL_SUCCESS &&
1969 retcode != SQL_SUCCESS_WITH_INFO)
1970 {
1971 // record was not inserted
1972 pDb->DispNextError();
1973 pDb->DispAllErrors(henv, hdbc, hstmtInsert);
1974 return(DB_FAILURE);
1975 }
1976 }
1977 }
1978
1979 // Record inserted into the datasource successfully
1980 return(DB_SUCCESS);
1981
1982 } // wxDbTable::Insert()
1983
1984
1985 /********** wxDbTable::Update() **********/
1986 bool wxDbTable::Update(void)
1987 {
1988 wxASSERT(!queryOnly);
1989 if (queryOnly)
1990 return false;
1991
1992 wxString sqlStmt;
1993
1994 // Build the SQL UPDATE statement
1995 BuildUpdateStmt(sqlStmt, DB_UPD_KEYFIELDS);
1996
1997 pDb->WriteSqlLog(sqlStmt);
1998
1999 #ifdef DBDEBUG_CONSOLE
2000 cout << endl << sqlStmt.c_str() << endl << endl;
2001 #endif
2002
2003 // Execute the SQL UPDATE statement
2004 return(execUpdate(sqlStmt));
2005
2006 } // wxDbTable::Update()
2007
2008
2009 /********** wxDbTable::Update(pSqlStmt) **********/
2010 bool wxDbTable::Update(const wxString &pSqlStmt)
2011 {
2012 wxASSERT(!queryOnly);
2013 if (queryOnly)
2014 return false;
2015
2016 pDb->WriteSqlLog(pSqlStmt);
2017
2018 return(execUpdate(pSqlStmt));
2019
2020 } // wxDbTable::Update(pSqlStmt)
2021
2022
2023 /********** wxDbTable::UpdateWhere() **********/
2024 bool wxDbTable::UpdateWhere(const wxString &pWhereClause)
2025 {
2026 wxASSERT(!queryOnly);
2027 if (queryOnly)
2028 return false;
2029
2030 wxString sqlStmt;
2031
2032 // Build the SQL UPDATE statement
2033 BuildUpdateStmt(sqlStmt, DB_UPD_WHERE, pWhereClause);
2034
2035 pDb->WriteSqlLog(sqlStmt);
2036
2037 #ifdef DBDEBUG_CONSOLE
2038 cout << endl << sqlStmt.c_str() << endl << endl;
2039 #endif
2040
2041 // Execute the SQL UPDATE statement
2042 return(execUpdate(sqlStmt));
2043
2044 } // wxDbTable::UpdateWhere()
2045
2046
2047 /********** wxDbTable::Delete() **********/
2048 bool wxDbTable::Delete(void)
2049 {
2050 wxASSERT(!queryOnly);
2051 if (queryOnly)
2052 return false;
2053
2054 wxString sqlStmt;
2055 sqlStmt.Empty();
2056
2057 // Build the SQL DELETE statement
2058 BuildDeleteStmt(sqlStmt, DB_DEL_KEYFIELDS);
2059
2060 pDb->WriteSqlLog(sqlStmt);
2061
2062 // Execute the SQL DELETE statement
2063 return(execDelete(sqlStmt));
2064
2065 } // wxDbTable::Delete()
2066
2067
2068 /********** wxDbTable::DeleteWhere() **********/
2069 bool wxDbTable::DeleteWhere(const wxString &pWhereClause)
2070 {
2071 wxASSERT(!queryOnly);
2072 if (queryOnly)
2073 return false;
2074
2075 wxString sqlStmt;
2076 sqlStmt.Empty();
2077
2078 // Build the SQL DELETE statement
2079 BuildDeleteStmt(sqlStmt, DB_DEL_WHERE, pWhereClause);
2080
2081 pDb->WriteSqlLog(sqlStmt);
2082
2083 // Execute the SQL DELETE statement
2084 return(execDelete(sqlStmt));
2085
2086 } // wxDbTable::DeleteWhere()
2087
2088
2089 /********** wxDbTable::DeleteMatching() **********/
2090 bool wxDbTable::DeleteMatching(void)
2091 {
2092 wxASSERT(!queryOnly);
2093 if (queryOnly)
2094 return false;
2095
2096 wxString sqlStmt;
2097 sqlStmt.Empty();
2098
2099 // Build the SQL DELETE statement
2100 BuildDeleteStmt(sqlStmt, DB_DEL_MATCHING);
2101
2102 pDb->WriteSqlLog(sqlStmt);
2103
2104 // Execute the SQL DELETE statement
2105 return(execDelete(sqlStmt));
2106
2107 } // wxDbTable::DeleteMatching()
2108
2109
2110 /********** wxDbTable::IsColNull() **********/
2111 bool wxDbTable::IsColNull(UWORD colNumber) const
2112 {
2113 /*
2114 This logic is just not right. It would indicate true
2115 if a numeric field were set to a value of 0.
2116
2117 switch(colDefs[colNumber].SqlCtype)
2118 {
2119 case SQL_C_CHAR:
2120 case SQL_C_WCHAR:
2121 //case SQL_C_WXCHAR: SQL_C_WXCHAR is covered by either SQL_C_CHAR or SQL_C_WCHAR
2122 return(((UCHAR FAR *) colDefs[colNumber].PtrDataObj)[0] == 0);
2123 case SQL_C_SSHORT:
2124 return(( *((SWORD *) colDefs[colNumber].PtrDataObj)) == 0);
2125 case SQL_C_USHORT:
2126 return(( *((UWORD*) colDefs[colNumber].PtrDataObj)) == 0);
2127 case SQL_C_SLONG:
2128 return(( *((SDWORD *) colDefs[colNumber].PtrDataObj)) == 0);
2129 case SQL_C_ULONG:
2130 return(( *((UDWORD *) colDefs[colNumber].PtrDataObj)) == 0);
2131 case SQL_C_FLOAT:
2132 return(( *((SFLOAT *) colDefs[colNumber].PtrDataObj)) == 0);
2133 case SQL_C_DOUBLE:
2134 return((*((SDOUBLE *) colDefs[colNumber].PtrDataObj)) == 0);
2135 case SQL_C_TIMESTAMP:
2136 TIMESTAMP_STRUCT *pDt;
2137 pDt = (TIMESTAMP_STRUCT *) colDefs[colNumber].PtrDataObj;
2138 if (pDt->year == 0 && pDt->month == 0 && pDt->day == 0)
2139 return true;
2140 else
2141 return false;
2142 default:
2143 return true;
2144 }
2145 */
2146 return (colDefs[colNumber].Null);
2147 } // wxDbTable::IsColNull()
2148
2149
2150 /********** wxDbTable::CanSelectForUpdate() **********/
2151 bool wxDbTable::CanSelectForUpdate(void)
2152 {
2153 if (queryOnly)
2154 return false;
2155
2156 if (pDb->Dbms() == dbmsMY_SQL)
2157 return false;
2158
2159 if ((pDb->Dbms() == dbmsORACLE) ||
2160 (pDb->dbInf.posStmts & SQL_PS_SELECT_FOR_UPDATE))
2161 return true;
2162 else
2163 return false;
2164
2165 } // wxDbTable::CanSelectForUpdate()
2166
2167
2168 /********** wxDbTable::CanUpdateByROWID() **********/
2169 bool wxDbTable::CanUpdateByROWID(void)
2170 {
2171 /*
2172 * NOTE: Returning false for now until this can be debugged,
2173 * as the ROWID is not getting updated correctly
2174 */
2175 return false;
2176 /*
2177 if (pDb->Dbms() == dbmsORACLE)
2178 return true;
2179 else
2180 return false;
2181 */
2182 } // wxDbTable::CanUpdateByROWID()
2183
2184
2185 /********** wxDbTable::IsCursorClosedOnCommit() **********/
2186 bool wxDbTable::IsCursorClosedOnCommit(void)
2187 {
2188 if (pDb->dbInf.cursorCommitBehavior == SQL_CB_PRESERVE)
2189 return false;
2190 else
2191 return true;
2192
2193 } // wxDbTable::IsCursorClosedOnCommit()
2194
2195
2196
2197 /********** wxDbTable::ClearMemberVar() **********/
2198 void wxDbTable::ClearMemberVar(UWORD colNumber, bool setToNull)
2199 {
2200 wxASSERT(colNumber < m_numCols);
2201
2202 switch(colDefs[colNumber].SqlCtype)
2203 {
2204 case SQL_C_CHAR:
2205 #ifdef SQL_C_WCHAR
2206 case SQL_C_WCHAR:
2207 #endif
2208 //case SQL_C_WXCHAR: SQL_C_WXCHAR is covered by either SQL_C_CHAR or SQL_C_WCHAR
2209 ((UCHAR FAR *) colDefs[colNumber].PtrDataObj)[0] = 0;
2210 break;
2211 case SQL_C_SSHORT:
2212 *((SWORD *) colDefs[colNumber].PtrDataObj) = 0;
2213 break;
2214 case SQL_C_USHORT:
2215 *((UWORD*) colDefs[colNumber].PtrDataObj) = 0;
2216 break;
2217 case SQL_C_LONG:
2218 case SQL_C_SLONG:
2219 *((SDWORD *) colDefs[colNumber].PtrDataObj) = 0;
2220 break;
2221 case SQL_C_ULONG:
2222 *((UDWORD *) colDefs[colNumber].PtrDataObj) = 0;
2223 break;
2224 case SQL_C_FLOAT:
2225 *((SFLOAT *) colDefs[colNumber].PtrDataObj) = 0.0f;
2226 break;
2227 case SQL_C_DOUBLE:
2228 *((SDOUBLE *) colDefs[colNumber].PtrDataObj) = 0.0f;
2229 break;
2230 case SQL_C_TIMESTAMP:
2231 TIMESTAMP_STRUCT *pDt;
2232 pDt = (TIMESTAMP_STRUCT *) colDefs[colNumber].PtrDataObj;
2233 pDt->year = 0;
2234 pDt->month = 0;
2235 pDt->day = 0;
2236 pDt->hour = 0;
2237 pDt->minute = 0;
2238 pDt->second = 0;
2239 pDt->fraction = 0;
2240 break;
2241 }
2242
2243 if (setToNull)
2244 SetColNull(colNumber);
2245 } // wxDbTable::ClearMemberVar()
2246
2247
2248 /********** wxDbTable::ClearMemberVars() **********/
2249 void wxDbTable::ClearMemberVars(bool setToNull)
2250 {
2251 int i;
2252
2253 // Loop through the columns setting each member variable to zero
2254 for (i=0; i < m_numCols; i++)
2255 ClearMemberVar((UWORD)i,setToNull);
2256
2257 } // wxDbTable::ClearMemberVars()
2258
2259
2260 /********** wxDbTable::SetQueryTimeout() **********/
2261 bool wxDbTable::SetQueryTimeout(UDWORD nSeconds)
2262 {
2263 if (SQLSetStmtOption(hstmtInsert, SQL_QUERY_TIMEOUT, nSeconds) != SQL_SUCCESS)
2264 return(pDb->DispAllErrors(henv, hdbc, hstmtInsert));
2265 if (SQLSetStmtOption(hstmtUpdate, SQL_QUERY_TIMEOUT, nSeconds) != SQL_SUCCESS)
2266 return(pDb->DispAllErrors(henv, hdbc, hstmtUpdate));
2267 if (SQLSetStmtOption(hstmtDelete, SQL_QUERY_TIMEOUT, nSeconds) != SQL_SUCCESS)
2268 return(pDb->DispAllErrors(henv, hdbc, hstmtDelete));
2269 if (SQLSetStmtOption(hstmtInternal, SQL_QUERY_TIMEOUT, nSeconds) != SQL_SUCCESS)
2270 return(pDb->DispAllErrors(henv, hdbc, hstmtInternal));
2271
2272 // Completed Successfully
2273 return true;
2274
2275 } // wxDbTable::SetQueryTimeout()
2276
2277
2278 /********** wxDbTable::SetColDefs() **********/
2279 bool wxDbTable::SetColDefs(UWORD index, const wxString &fieldName, int dataType, void *pData,
2280 SWORD cType, int size, bool keyField, bool updateable,
2281 bool insertAllowed, bool derivedColumn)
2282 {
2283 wxString tmpStr;
2284
2285 if (index >= m_numCols) // Columns numbers are zero based....
2286 {
2287 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);
2288 wxFAIL_MSG(tmpStr);
2289 wxLogDebug(tmpStr);
2290 return false;
2291 }
2292
2293 if (!colDefs) // May happen if the database connection fails
2294 return false;
2295
2296 if (fieldName.Length() > (unsigned int) DB_MAX_COLUMN_NAME_LEN)
2297 {
2298 wxStrncpy(colDefs[index].ColName, fieldName, DB_MAX_COLUMN_NAME_LEN);
2299 colDefs[index].ColName[DB_MAX_COLUMN_NAME_LEN] = 0; // Prevent buffer overrun
2300
2301 tmpStr.Printf(wxT("Column name '%s' is too long. Truncated to '%s'."),
2302 fieldName.c_str(),colDefs[index].ColName);
2303 wxFAIL_MSG(tmpStr);
2304 wxLogDebug(tmpStr);
2305 }
2306 else
2307 wxStrcpy(colDefs[index].ColName, fieldName);
2308
2309 colDefs[index].DbDataType = dataType;
2310 colDefs[index].PtrDataObj = pData;
2311 colDefs[index].SqlCtype = cType;
2312 colDefs[index].SzDataObj = size; //TODO: glt ??? * sizeof(wxChar) ???
2313 colDefs[index].KeyField = keyField;
2314 colDefs[index].DerivedCol = derivedColumn;
2315 // Derived columns by definition would NOT be "Insertable" or "Updateable"
2316 if (derivedColumn)
2317 {
2318 colDefs[index].Updateable = false;
2319 colDefs[index].InsertAllowed = false;
2320 }
2321 else
2322 {
2323 colDefs[index].Updateable = updateable;
2324 colDefs[index].InsertAllowed = insertAllowed;
2325 }
2326
2327 colDefs[index].Null = false;
2328
2329 return true;
2330
2331 } // wxDbTable::SetColDefs()
2332
2333
2334 /********** wxDbTable::SetColDefs() **********/
2335 wxDbColDataPtr* wxDbTable::SetColDefs(wxDbColInf *pColInfs, UWORD numCols)
2336 {
2337 wxASSERT(pColInfs);
2338 wxDbColDataPtr *pColDataPtrs = NULL;
2339
2340 if (pColInfs)
2341 {
2342 UWORD index;
2343
2344 pColDataPtrs = new wxDbColDataPtr[numCols+1];
2345
2346 for (index = 0; index < numCols; index++)
2347 {
2348 // Process the fields
2349 switch (pColInfs[index].dbDataType)
2350 {
2351 case DB_DATA_TYPE_VARCHAR:
2352 pColDataPtrs[index].PtrDataObj = new wxChar[pColInfs[index].bufferSize+(1*sizeof(wxChar))];
2353 pColDataPtrs[index].SzDataObj = pColInfs[index].bufferSize+(1*sizeof(wxChar));
2354 pColDataPtrs[index].SqlCtype = SQL_C_WXCHAR;
2355 break;
2356 case DB_DATA_TYPE_INTEGER:
2357 // Can be long or short
2358 if (pColInfs[index].bufferSize == sizeof(long))
2359 {
2360 pColDataPtrs[index].PtrDataObj = new long;
2361 pColDataPtrs[index].SzDataObj = sizeof(long);
2362 pColDataPtrs[index].SqlCtype = SQL_C_SLONG;
2363 }
2364 else
2365 {
2366 pColDataPtrs[index].PtrDataObj = new short;
2367 pColDataPtrs[index].SzDataObj = sizeof(short);
2368 pColDataPtrs[index].SqlCtype = SQL_C_SSHORT;
2369 }
2370 break;
2371 case DB_DATA_TYPE_FLOAT:
2372 // Can be float or double
2373 if (pColInfs[index].bufferSize == sizeof(float))
2374 {
2375 pColDataPtrs[index].PtrDataObj = new float;
2376 pColDataPtrs[index].SzDataObj = sizeof(float);
2377 pColDataPtrs[index].SqlCtype = SQL_C_FLOAT;
2378 }
2379 else
2380 {
2381 pColDataPtrs[index].PtrDataObj = new double;
2382 pColDataPtrs[index].SzDataObj = sizeof(double);
2383 pColDataPtrs[index].SqlCtype = SQL_C_DOUBLE;
2384 }
2385 break;
2386 case DB_DATA_TYPE_DATE:
2387 pColDataPtrs[index].PtrDataObj = new TIMESTAMP_STRUCT;
2388 pColDataPtrs[index].SzDataObj = sizeof(TIMESTAMP_STRUCT);
2389 pColDataPtrs[index].SqlCtype = SQL_C_TIMESTAMP;
2390 break;
2391 case DB_DATA_TYPE_BLOB:
2392 wxFAIL_MSG(wxT("This form of ::SetColDefs() cannot be used with BLOB columns"));
2393 pColDataPtrs[index].PtrDataObj = /*BLOB ADDITION NEEDED*/NULL;
2394 pColDataPtrs[index].SzDataObj = /*BLOB ADDITION NEEDED*/sizeof(void *);
2395 pColDataPtrs[index].SqlCtype = SQL_VARBINARY;
2396 break;
2397 }
2398 if (pColDataPtrs[index].PtrDataObj != NULL)
2399 SetColDefs (index,pColInfs[index].colName,pColInfs[index].dbDataType, pColDataPtrs[index].PtrDataObj, pColDataPtrs[index].SqlCtype, pColDataPtrs[index].SzDataObj);
2400 else
2401 {
2402 // Unable to build all the column definitions, as either one of
2403 // the calls to "new" failed above, or there was a BLOB field
2404 // to have a column definition for. If BLOBs are to be used,
2405 // the other form of ::SetColDefs() must be used, as it is impossible
2406 // to know the maximum size to create the PtrDataObj to be.
2407 delete [] pColDataPtrs;
2408 return NULL;
2409 }
2410 }
2411 }
2412
2413 return (pColDataPtrs);
2414
2415 } // wxDbTable::SetColDefs()
2416
2417
2418 /********** wxDbTable::SetCursor() **********/
2419 void wxDbTable::SetCursor(HSTMT *hstmtActivate)
2420 {
2421 if (hstmtActivate == wxDB_DEFAULT_CURSOR)
2422 hstmt = *hstmtDefault;
2423 else
2424 hstmt = *hstmtActivate;
2425
2426 } // wxDbTable::SetCursor()
2427
2428
2429 /********** wxDbTable::Count(const wxString &) **********/
2430 ULONG wxDbTable::Count(const wxString &args)
2431 {
2432 ULONG count;
2433 wxString sqlStmt;
2434 SDWORD cb;
2435
2436 // Build a "SELECT COUNT(*) FROM queryTableName [WHERE whereClause]" SQL Statement
2437 sqlStmt = wxT("SELECT COUNT(");
2438 sqlStmt += args;
2439 sqlStmt += wxT(") FROM ");
2440 sqlStmt += pDb->SQLTableName(queryTableName);
2441 // sqlStmt += queryTableName;
2442 #if wxODBC_BACKWARD_COMPATABILITY
2443 if (from && wxStrlen(from))
2444 #else
2445 if (from.Length())
2446 #endif
2447 sqlStmt += from;
2448
2449 // Add the where clause if one is provided
2450 #if wxODBC_BACKWARD_COMPATABILITY
2451 if (where && wxStrlen(where))
2452 #else
2453 if (where.Length())
2454 #endif
2455 {
2456 sqlStmt += wxT(" WHERE ");
2457 sqlStmt += where;
2458 }
2459
2460 pDb->WriteSqlLog(sqlStmt);
2461
2462 // Initialize the Count cursor if it's not already initialized
2463 if (!hstmtCount)
2464 {
2465 hstmtCount = GetNewCursor(false,false);
2466 wxASSERT(hstmtCount);
2467 if (!hstmtCount)
2468 return(0);
2469 }
2470
2471 // Execute the SQL statement
2472 if (SQLExecDirect(*hstmtCount, (SQLTCHAR FAR *) sqlStmt.c_str(), SQL_NTS) != SQL_SUCCESS)
2473 {
2474 pDb->DispAllErrors(henv, hdbc, *hstmtCount);
2475 return(0);
2476 }
2477
2478 // Fetch the record
2479 if (SQLFetch(*hstmtCount) != SQL_SUCCESS)
2480 {
2481 pDb->DispAllErrors(henv, hdbc, *hstmtCount);
2482 return(0);
2483 }
2484
2485 // Obtain the result
2486 if (SQLGetData(*hstmtCount, (UWORD)1, SQL_C_ULONG, &count, sizeof(count), &cb) != SQL_SUCCESS)
2487 {
2488 pDb->DispAllErrors(henv, hdbc, *hstmtCount);
2489 return(0);
2490 }
2491
2492 // Free the cursor
2493 if (SQLFreeStmt(*hstmtCount, SQL_CLOSE) != SQL_SUCCESS)
2494 pDb->DispAllErrors(henv, hdbc, *hstmtCount);
2495
2496 // Return the record count
2497 return(count);
2498
2499 } // wxDbTable::Count()
2500
2501
2502 /********** wxDbTable::Refresh() **********/
2503 bool wxDbTable::Refresh(void)
2504 {
2505 bool result = true;
2506
2507 // Switch to the internal cursor so any active cursors are not corrupted
2508 HSTMT currCursor = GetCursor();
2509 hstmt = hstmtInternal;
2510 #if wxODBC_BACKWARD_COMPATABILITY
2511 // Save the where and order by clauses
2512 wxChar *saveWhere = where;
2513 wxChar *saveOrderBy = orderBy;
2514 #else
2515 wxString saveWhere = where;
2516 wxString saveOrderBy = orderBy;
2517 #endif
2518 // Build a where clause to refetch the record with. Try and use the
2519 // ROWID if it's available, ow use the key fields.
2520 wxString whereClause;
2521 whereClause.Empty();
2522
2523 if (CanUpdateByROWID())
2524 {
2525 SDWORD cb;
2526 wxChar rowid[wxDB_ROWID_LEN+1];
2527
2528 // Get the ROWID value. If not successful retreiving the ROWID,
2529 // simply fall down through the code and build the WHERE clause
2530 // based on the key fields.
2531 if (SQLGetData(hstmt, (UWORD)(m_numCols+1), SQL_C_WXCHAR, (UCHAR*) rowid, sizeof(rowid), &cb) == SQL_SUCCESS)
2532 {
2533 whereClause += pDb->SQLTableName(queryTableName);
2534 // whereClause += queryTableName;
2535 whereClause += wxT(".ROWID = '");
2536 whereClause += rowid;
2537 whereClause += wxT("'");
2538 }
2539 }
2540
2541 // If unable to use the ROWID, build a where clause from the keyfields
2542 if (wxStrlen(whereClause) == 0)
2543 BuildWhereClause(whereClause, DB_WHERE_KEYFIELDS, queryTableName);
2544
2545 // Requery the record
2546 where = whereClause;
2547 orderBy.Empty();
2548 if (!Query())
2549 result = false;
2550
2551 if (result && !GetNext())
2552 result = false;
2553
2554 // Switch back to original cursor
2555 SetCursor(&currCursor);
2556
2557 // Free the internal cursor
2558 if (SQLFreeStmt(hstmtInternal, SQL_CLOSE) != SQL_SUCCESS)
2559 pDb->DispAllErrors(henv, hdbc, hstmtInternal);
2560
2561 // Restore the original where and order by clauses
2562 where = saveWhere;
2563 orderBy = saveOrderBy;
2564
2565 return(result);
2566
2567 } // wxDbTable::Refresh()
2568
2569
2570 /********** wxDbTable::SetColNull() **********/
2571 bool wxDbTable::SetColNull(UWORD colNumber, bool set)
2572 {
2573 if (colNumber < m_numCols)
2574 {
2575 colDefs[colNumber].Null = set;
2576 if (set) // Blank out the values in the member variable
2577 ClearMemberVar(colNumber, false); // Must call with false here, or infinite recursion will happen
2578
2579 setCbValueForColumn(colNumber);
2580
2581 return true;
2582 }
2583 else
2584 return false;
2585
2586 } // wxDbTable::SetColNull()
2587
2588
2589 /********** wxDbTable::SetColNull() **********/
2590 bool wxDbTable::SetColNull(const wxString &colName, bool set)
2591 {
2592 int colNumber;
2593 for (colNumber = 0; colNumber < m_numCols; colNumber++)
2594 {
2595 if (!wxStricmp(colName, colDefs[colNumber].ColName))
2596 break;
2597 }
2598
2599 if (colNumber < m_numCols)
2600 {
2601 colDefs[colNumber].Null = set;
2602 if (set) // Blank out the values in the member variable
2603 ClearMemberVar((UWORD)colNumber,false); // Must call with false here, or infinite recursion will happen
2604
2605 setCbValueForColumn(colNumber);
2606
2607 return true;
2608 }
2609 else
2610 return false;
2611
2612 } // wxDbTable::SetColNull()
2613
2614
2615 /********** wxDbTable::GetNewCursor() **********/
2616 HSTMT *wxDbTable::GetNewCursor(bool setCursor, bool bindColumns)
2617 {
2618 HSTMT *newHSTMT = new HSTMT;
2619 wxASSERT(newHSTMT);
2620 if (!newHSTMT)
2621 return(0);
2622
2623 if (SQLAllocStmt(hdbc, newHSTMT) != SQL_SUCCESS)
2624 {
2625 pDb->DispAllErrors(henv, hdbc);
2626 delete newHSTMT;
2627 return(0);
2628 }
2629
2630 if (SQLSetStmtOption(*newHSTMT, SQL_CURSOR_TYPE, cursorType) != SQL_SUCCESS)
2631 {
2632 pDb->DispAllErrors(henv, hdbc, *newHSTMT);
2633 delete newHSTMT;
2634 return(0);
2635 }
2636
2637 if (bindColumns)
2638 {
2639 if (!bindCols(*newHSTMT))
2640 {
2641 delete newHSTMT;
2642 return(0);
2643 }
2644 }
2645
2646 if (setCursor)
2647 SetCursor(newHSTMT);
2648
2649 return(newHSTMT);
2650
2651 } // wxDbTable::GetNewCursor()
2652
2653
2654 /********** wxDbTable::DeleteCursor() **********/
2655 bool wxDbTable::DeleteCursor(HSTMT *hstmtDel)
2656 {
2657 bool result = true;
2658
2659 if (!hstmtDel) // Cursor already deleted
2660 return(result);
2661
2662 /*
2663 ODBC 3.0 says to use this form
2664 if (SQLFreeHandle(*hstmtDel, SQL_DROP) != SQL_SUCCESS)
2665
2666 */
2667 if (SQLFreeStmt(*hstmtDel, SQL_DROP) != SQL_SUCCESS)
2668 {
2669 pDb->DispAllErrors(henv, hdbc);
2670 result = false;
2671 }
2672
2673 delete hstmtDel;
2674
2675 return(result);
2676
2677 } // wxDbTable::DeleteCursor()
2678
2679 //////////////////////////////////////////////////////////////
2680 // wxDbGrid support functions
2681 //////////////////////////////////////////////////////////////
2682
2683 void wxDbTable::SetRowMode(const rowmode_t rowmode)
2684 {
2685 if (!m_hstmtGridQuery)
2686 {
2687 m_hstmtGridQuery = GetNewCursor(false,false);
2688 if (!bindCols(*m_hstmtGridQuery))
2689 return;
2690 }
2691
2692 m_rowmode = rowmode;
2693 switch (m_rowmode)
2694 {
2695 case WX_ROW_MODE_QUERY:
2696 SetCursor(m_hstmtGridQuery);
2697 break;
2698 case WX_ROW_MODE_INDIVIDUAL:
2699 SetCursor(hstmtDefault);
2700 break;
2701 default:
2702 wxASSERT(0);
2703 }
2704 } // wxDbTable::SetRowMode()
2705
2706
2707 wxVariant wxDbTable::GetColumn(const int colNumber) const
2708 {
2709 wxVariant val;
2710 if ((colNumber < m_numCols) && (!IsColNull((UWORD)colNumber)))
2711 {
2712 switch (colDefs[colNumber].SqlCtype)
2713 {
2714 #if wxUSE_UNICODE
2715 #if defined(SQL_WCHAR)
2716 case SQL_WCHAR:
2717 #endif
2718 #if defined(SQL_WVARCHAR)
2719 case SQL_WVARCHAR:
2720 #endif
2721 #endif
2722 case SQL_CHAR:
2723 case SQL_VARCHAR:
2724 val = (wxChar *)(colDefs[colNumber].PtrDataObj);
2725 break;
2726 case SQL_C_LONG:
2727 case SQL_C_SLONG:
2728 val = *(long *)(colDefs[colNumber].PtrDataObj);
2729 break;
2730 case SQL_C_SHORT:
2731 case SQL_C_SSHORT:
2732 val = (long int )(*(short *)(colDefs[colNumber].PtrDataObj));
2733 break;
2734 case SQL_C_ULONG:
2735 val = (long)(*(unsigned long *)(colDefs[colNumber].PtrDataObj));
2736 break;
2737 case SQL_C_TINYINT:
2738 val = (long)(*(wxChar *)(colDefs[colNumber].PtrDataObj));
2739 break;
2740 case SQL_C_UTINYINT:
2741 val = (long)(*(wxChar *)(colDefs[colNumber].PtrDataObj));
2742 break;
2743 case SQL_C_USHORT:
2744 val = (long)(*(UWORD *)(colDefs[colNumber].PtrDataObj));
2745 break;
2746 case SQL_C_DATE:
2747 val = (DATE_STRUCT *)(colDefs[colNumber].PtrDataObj);
2748 break;
2749 case SQL_C_TIME:
2750 val = (TIME_STRUCT *)(colDefs[colNumber].PtrDataObj);
2751 break;
2752 case SQL_C_TIMESTAMP:
2753 val = (TIMESTAMP_STRUCT *)(colDefs[colNumber].PtrDataObj);
2754 break;
2755 case SQL_C_DOUBLE:
2756 val = *(double *)(colDefs[colNumber].PtrDataObj);
2757 break;
2758 default:
2759 assert(0);
2760 }
2761 }
2762 return val;
2763 } // wxDbTable::GetCol()
2764
2765
2766 void wxDbTable::SetColumn(const int colNumber, const wxVariant val)
2767 {
2768 //FIXME: Add proper wxDateTime support to wxVariant..
2769 wxDateTime dateval;
2770
2771 SetColNull((UWORD)colNumber, val.IsNull());
2772
2773 if (!val.IsNull())
2774 {
2775 if ((colDefs[colNumber].SqlCtype == SQL_C_DATE)
2776 || (colDefs[colNumber].SqlCtype == SQL_C_TIME)
2777 || (colDefs[colNumber].SqlCtype == SQL_C_TIMESTAMP))
2778 {
2779 //Returns null if invalid!
2780 if (!dateval.ParseDate(val.GetString()))
2781 SetColNull((UWORD)colNumber, true);
2782 }
2783
2784 switch (colDefs[colNumber].SqlCtype)
2785 {
2786 #if wxUSE_UNICODE
2787 #if defined(SQL_WCHAR)
2788 case SQL_WCHAR:
2789 #endif
2790 #if defined(SQL_WVARCHAR)
2791 case SQL_WVARCHAR:
2792 #endif
2793 #endif
2794 case SQL_CHAR:
2795 case SQL_VARCHAR:
2796 csstrncpyt((wxChar *)(colDefs[colNumber].PtrDataObj),
2797 val.GetString().c_str(),
2798 colDefs[colNumber].SzDataObj-1); //TODO: glt ??? * sizeof(wxChar) ???
2799 break;
2800 case SQL_C_LONG:
2801 case SQL_C_SLONG:
2802 *(long *)(colDefs[colNumber].PtrDataObj) = val;
2803 break;
2804 case SQL_C_SHORT:
2805 case SQL_C_SSHORT:
2806 *(short *)(colDefs[colNumber].PtrDataObj) = (short)val.GetLong();
2807 break;
2808 case SQL_C_ULONG:
2809 *(unsigned long *)(colDefs[colNumber].PtrDataObj) = val.GetLong();
2810 break;
2811 case SQL_C_TINYINT:
2812 *(wxChar *)(colDefs[colNumber].PtrDataObj) = val.GetChar();
2813 break;
2814 case SQL_C_UTINYINT:
2815 *(wxChar *)(colDefs[colNumber].PtrDataObj) = val.GetChar();
2816 break;
2817 case SQL_C_USHORT:
2818 *(unsigned short *)(colDefs[colNumber].PtrDataObj) = (unsigned short)val.GetLong();
2819 break;
2820 //FIXME: Add proper wxDateTime support to wxVariant..
2821 case SQL_C_DATE:
2822 {
2823 DATE_STRUCT *dataptr =
2824 (DATE_STRUCT *)colDefs[colNumber].PtrDataObj;
2825
2826 dataptr->year = (SWORD)dateval.GetYear();
2827 dataptr->month = (UWORD)(dateval.GetMonth()+1);
2828 dataptr->day = (UWORD)dateval.GetDay();
2829 }
2830 break;
2831 case SQL_C_TIME:
2832 {
2833 TIME_STRUCT *dataptr =
2834 (TIME_STRUCT *)colDefs[colNumber].PtrDataObj;
2835
2836 dataptr->hour = dateval.GetHour();
2837 dataptr->minute = dateval.GetMinute();
2838 dataptr->second = dateval.GetSecond();
2839 }
2840 break;
2841 case SQL_C_TIMESTAMP:
2842 {
2843 TIMESTAMP_STRUCT *dataptr =
2844 (TIMESTAMP_STRUCT *)colDefs[colNumber].PtrDataObj;
2845 dataptr->year = (SWORD)dateval.GetYear();
2846 dataptr->month = (UWORD)(dateval.GetMonth()+1);
2847 dataptr->day = (UWORD)dateval.GetDay();
2848
2849 dataptr->hour = dateval.GetHour();
2850 dataptr->minute = dateval.GetMinute();
2851 dataptr->second = dateval.GetSecond();
2852 }
2853 break;
2854 case SQL_C_DOUBLE:
2855 *(double *)(colDefs[colNumber].PtrDataObj) = val;
2856 break;
2857 default:
2858 assert(0);
2859 } // switch
2860 } // if (!val.IsNull())
2861 } // wxDbTable::SetCol()
2862
2863
2864 GenericKey wxDbTable::GetKey()
2865 {
2866 void *blk;
2867 wxChar *blkptr;
2868
2869 blk = malloc(m_keysize);
2870 blkptr = (wxChar *) blk;
2871
2872 int i;
2873 for (i=0; i < m_numCols; i++)
2874 {
2875 if (colDefs[i].KeyField)
2876 {
2877 memcpy(blkptr,colDefs[i].PtrDataObj, colDefs[i].SzDataObj);
2878 blkptr += colDefs[i].SzDataObj;
2879 }
2880 }
2881
2882 GenericKey k = GenericKey(blk, m_keysize);
2883 free(blk);
2884
2885 return k;
2886 } // wxDbTable::GetKey()
2887
2888
2889 void wxDbTable::SetKey(const GenericKey& k)
2890 {
2891 void *blk;
2892 wxChar *blkptr;
2893
2894 blk = k.GetBlk();
2895 blkptr = (wxChar *)blk;
2896
2897 int i;
2898 for (i=0; i < m_numCols; i++)
2899 {
2900 if (colDefs[i].KeyField)
2901 {
2902 SetColNull((UWORD)i, false);
2903 memcpy(colDefs[i].PtrDataObj, blkptr, colDefs[i].SzDataObj);
2904 blkptr += colDefs[i].SzDataObj;
2905 }
2906 }
2907 } // wxDbTable::SetKey()
2908
2909
2910 #endif // wxUSE_ODBC
2911
wxWidgets (Impactor)