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