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