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