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