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