]> 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
added CreateByName(), use it in wxColourDatabase
[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.First();
329 while (pNode && !found)
330 {
331 if (((wxTablesInUse *)pNode->Data())->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->Next();
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)
1463 ) // Found a keyfield
1464 {
1465 switch (pDb->Dbms())
1466 {
1467 case dbmsACCESS:
1468 case dbmsINFORMIX:
1469 case dbmsSYBASE_ASA:
1470 case dbmsSYBASE_ASE:
1471 case dbmsMY_SQL:
1472 {
1473 // MySQL goes out on this one. We also declare the relevant key NON NULL above
1474 sqlStmt += wxT(",PRIMARY KEY (");
1475 break;
1476 }
1477 default:
1478 {
1479 sqlStmt += wxT(",CONSTRAINT ");
1480 // DB2 is limited to 18 characters for index names
1481 if (pDb->Dbms() == dbmsDB2)
1482 {
1483 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."));
1484 sqlStmt += pDb->SQLTableName(tableName.substr(0, 13).c_str());
1485 // sqlStmt += tableName.substr(0, 13);
1486 }
1487 else
1488 sqlStmt += pDb->SQLTableName(tableName.c_str());
1489 // sqlStmt += tableName;
1490
1491 sqlStmt += wxT("_PIDX PRIMARY KEY (");
1492 break;
1493 }
1494 }
1495
1496 // List column name(s) of column(s) comprising the primary key
1497 for (i = j = 0; i < noCols; i++)
1498 {
1499 if (colDefs[i].KeyField)
1500 {
1501 if (j++) // Multi part key, comma separate names
1502 sqlStmt += wxT(",");
1503 sqlStmt += pDb->SQLColumnName(colDefs[i].ColName);
1504
1505 if (pDb->Dbms() == dbmsMY_SQL &&
1506 colDefs[i].DbDataType == DB_DATA_TYPE_VARCHAR)
1507 {
1508 wxString s;
1509 s.Printf(wxT("(%d)"), colDefs[i].SzDataObj);
1510 sqlStmt += s;
1511 }
1512 }
1513 }
1514 sqlStmt += wxT(")");
1515
1516 if (pDb->Dbms() == dbmsINFORMIX ||
1517 pDb->Dbms() == dbmsSYBASE_ASA ||
1518 pDb->Dbms() == dbmsSYBASE_ASE)
1519 {
1520 sqlStmt += wxT(" CONSTRAINT ");
1521 sqlStmt += pDb->SQLTableName(tableName);
1522 // sqlStmt += tableName;
1523 sqlStmt += wxT("_PIDX");
1524 }
1525 }
1526 // Append the closing parentheses for the create table statement
1527 sqlStmt += wxT(")");
1528
1529 pDb->WriteSqlLog(sqlStmt);
1530
1531 #ifdef DBDEBUG_CONSOLE
1532 cout << endl << sqlStmt.c_str() << endl;
1533 #endif
1534
1535 // Execute the CREATE TABLE statement
1536 RETCODE retcode = SQLExecDirect(hstmt, (SQLTCHAR FAR *) sqlStmt.c_str(), SQL_NTS);
1537 if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
1538 {
1539 pDb->DispAllErrors(henv, hdbc, hstmt);
1540 pDb->RollbackTrans();
1541 CloseCursor(hstmt);
1542 return(FALSE);
1543 }
1544
1545 // Commit the transaction and close the cursor
1546 if (!pDb->CommitTrans())
1547 return(FALSE);
1548 if (!CloseCursor(hstmt))
1549 return(FALSE);
1550
1551 // Database table created successfully
1552 return(TRUE);
1553
1554 } // wxDbTable::CreateTable()
1555
1556
1557 /********** wxDbTable::DropTable() **********/
1558 bool wxDbTable::DropTable()
1559 {
1560 // NOTE: This function returns TRUE if the Table does not exist, but
1561 // only for identified databases. Code will need to be added
1562 // below for any other databases when those databases are defined
1563 // to handle this situation consistently
1564
1565 wxString sqlStmt;
1566
1567 sqlStmt.Printf(wxT("DROP TABLE %s"),
1568 pDb->SQLTableName(tableName.c_str()).c_str());
1569
1570 pDb->WriteSqlLog(sqlStmt);
1571
1572 #ifdef DBDEBUG_CONSOLE
1573 cout << endl << sqlStmt.c_str() << endl;
1574 #endif
1575
1576 RETCODE retcode = SQLExecDirect(hstmt, (SQLTCHAR FAR *) sqlStmt.c_str(), SQL_NTS);
1577 if (retcode != SQL_SUCCESS)
1578 {
1579 // Check for "Base table not found" error and ignore
1580 pDb->GetNextError(henv, hdbc, hstmt);
1581 if (wxStrcmp(pDb->sqlState, wxT("S0002")) /*&&
1582 wxStrcmp(pDb->sqlState, wxT("S1000"))*/) // "Base table not found"
1583 {
1584 // Check for product specific error codes
1585 if (!((pDb->Dbms() == dbmsSYBASE_ASA && !wxStrcmp(pDb->sqlState,wxT("42000"))) || // 5.x (and lower?)
1586 (pDb->Dbms() == dbmsSYBASE_ASE && !wxStrcmp(pDb->sqlState,wxT("37000"))) ||
1587 (pDb->Dbms() == dbmsPERVASIVE_SQL && !wxStrcmp(pDb->sqlState,wxT("S1000"))) || // Returns an S1000 then an S0002
1588 (pDb->Dbms() == dbmsPOSTGRES && !wxStrcmp(pDb->sqlState,wxT("08S01")))))
1589 {
1590 pDb->DispNextError();
1591 pDb->DispAllErrors(henv, hdbc, hstmt);
1592 pDb->RollbackTrans();
1593 // CloseCursor(hstmt);
1594 return(FALSE);
1595 }
1596 }
1597 }
1598
1599 // Commit the transaction and close the cursor
1600 if (! pDb->CommitTrans())
1601 return(FALSE);
1602 if (! CloseCursor(hstmt))
1603 return(FALSE);
1604
1605 return(TRUE);
1606 } // wxDbTable::DropTable()
1607
1608
1609 /********** wxDbTable::CreateIndex() **********/
1610 bool wxDbTable::CreateIndex(const wxString &idxName, bool unique, UWORD noIdxCols,
1611 wxDbIdxDef *pIdxDefs, bool attemptDrop)
1612 {
1613 wxString sqlStmt;
1614
1615 // Drop the index first
1616 if (attemptDrop && !DropIndex(idxName))
1617 return (FALSE);
1618
1619 // MySQL (and possibly Sybase ASE?? - gt) require that any columns which are used as portions
1620 // of an index have the columns defined as "NOT NULL". During initial table creation though,
1621 // it may not be known which columns are necessarily going to be part of an index (e.g. the
1622 // table was created, then months later you determine that an additional index while
1623 // give better performance, so you want to add an index).
1624 //
1625 // The following block of code will modify the column definition to make the column be
1626 // defined with the "NOT NULL" qualifier.
1627 if (pDb->Dbms() == dbmsMY_SQL)
1628 {
1629 wxString sqlStmt;
1630 int i;
1631 bool ok = TRUE;
1632 for (i = 0; i < noIdxCols && ok; i++)
1633 {
1634 int j = 0;
1635 bool found = FALSE;
1636 // Find the column definition that has the ColName that matches the
1637 // index column name. We need to do this to get the DB_DATA_TYPE of
1638 // the index column, as MySQL's syntax for the ALTER column requires
1639 // this information
1640 while (!found && (j < this->noCols))
1641 {
1642 if (wxStrcmp(colDefs[j].ColName,pIdxDefs[i].ColName) == 0)
1643 found = TRUE;
1644 if (!found)
1645 j++;
1646 }
1647
1648 if (found)
1649 {
1650 ok = pDb->ModifyColumn(tableName, pIdxDefs[i].ColName,
1651 colDefs[j].DbDataType, colDefs[j].SzDataObj,
1652 wxT("NOT NULL"));
1653
1654 if (!ok)
1655 {
1656 #if 0
1657 // retcode is not used
1658 wxODBC_ERRORS retcode;
1659 // Oracle returns a DB_ERR_GENERAL_ERROR if the column is already
1660 // defined to be NOT NULL, but reportedly MySQL doesn't mind.
1661 // This line is just here for debug checking of the value
1662 retcode = (wxODBC_ERRORS)pDb->DB_STATUS;
1663 #endif
1664 }
1665 }
1666 else
1667 ok = FALSE;
1668 }
1669 if (ok)
1670 pDb->CommitTrans();
1671 else
1672 {
1673 pDb->RollbackTrans();
1674 return(FALSE);
1675 }
1676 }
1677
1678 // Build a CREATE INDEX statement
1679 sqlStmt = wxT("CREATE ");
1680 if (unique)
1681 sqlStmt += wxT("UNIQUE ");
1682
1683 sqlStmt += wxT("INDEX ");
1684 sqlStmt += pDb->SQLTableName(idxName);
1685 sqlStmt += wxT(" ON ");
1686
1687 sqlStmt += pDb->SQLTableName(tableName);
1688 // sqlStmt += tableName;
1689 sqlStmt += wxT(" (");
1690
1691 // Append list of columns making up index
1692 int i;
1693 for (i = 0; i < noIdxCols; i++)
1694 {
1695 sqlStmt += pDb->SQLColumnName(pIdxDefs[i].ColName);
1696 // sqlStmt += pIdxDefs[i].ColName;
1697
1698 // MySQL requires a key length on VARCHAR keys
1699 if ( pDb->Dbms() == dbmsMY_SQL )
1700 {
1701 // Find the details on this column
1702 int j;
1703 for ( j = 0; j < noCols; ++j )
1704 {
1705 if ( wxStrcmp( pIdxDefs[i].ColName, colDefs[j].ColName ) == 0 )
1706 {
1707 break;
1708 }
1709 }
1710 if ( colDefs[j].DbDataType == DB_DATA_TYPE_VARCHAR)
1711 {
1712 wxString s;
1713 s.Printf(wxT("(%d)"), colDefs[i].SzDataObj);
1714 sqlStmt += s;
1715 }
1716 }
1717
1718 // Postgres and SQL Server 7 do not support the ASC/DESC keywords for index columns
1719 if (!((pDb->Dbms() == dbmsMS_SQL_SERVER) && (wxStrncmp(pDb->dbInf.dbmsVer,_T("07"),2)==0)) &&
1720 !(pDb->Dbms() == dbmsPOSTGRES))
1721 {
1722 if (pIdxDefs[i].Ascending)
1723 sqlStmt += wxT(" ASC");
1724 else
1725 sqlStmt += wxT(" DESC");
1726 }
1727 else
1728 wxASSERT_MSG(pIdxDefs[i].Ascending, _T("Datasource does not support DESCending index columns"));
1729
1730 if ((i + 1) < noIdxCols)
1731 sqlStmt += wxT(",");
1732 }
1733
1734 // Append closing parentheses
1735 sqlStmt += wxT(")");
1736
1737 pDb->WriteSqlLog(sqlStmt);
1738
1739 #ifdef DBDEBUG_CONSOLE
1740 cout << endl << sqlStmt.c_str() << endl << endl;
1741 #endif
1742
1743 // Execute the CREATE INDEX statement
1744 if (SQLExecDirect(hstmt, (SQLTCHAR FAR *) sqlStmt.c_str(), SQL_NTS) != SQL_SUCCESS)
1745 {
1746 pDb->DispAllErrors(henv, hdbc, hstmt);
1747 pDb->RollbackTrans();
1748 CloseCursor(hstmt);
1749 return(FALSE);
1750 }
1751
1752 // Commit the transaction and close the cursor
1753 if (! pDb->CommitTrans())
1754 return(FALSE);
1755 if (! CloseCursor(hstmt))
1756 return(FALSE);
1757
1758 // Index Created Successfully
1759 return(TRUE);
1760
1761 } // wxDbTable::CreateIndex()
1762
1763
1764 /********** wxDbTable::DropIndex() **********/
1765 bool wxDbTable::DropIndex(const wxString &idxName)
1766 {
1767 // NOTE: This function returns TRUE if the Index does not exist, but
1768 // only for identified databases. Code will need to be added
1769 // below for any other databases when those databases are defined
1770 // to handle this situation consistently
1771
1772 wxString sqlStmt;
1773
1774 if (pDb->Dbms() == dbmsACCESS || pDb->Dbms() == dbmsMY_SQL ||
1775 pDb->Dbms() == dbmsDBASE /*|| Paradox needs this syntax too when we add support*/)
1776 sqlStmt.Printf(wxT("DROP INDEX %s ON %s"),
1777 pDb->SQLTableName(idxName.c_str()).c_str(),
1778 pDb->SQLTableName(tableName.c_str()).c_str());
1779 else if ((pDb->Dbms() == dbmsMS_SQL_SERVER) ||
1780 (pDb->Dbms() == dbmsSYBASE_ASE) ||
1781 (pDb->Dbms() == dbmsXBASE_SEQUITER))
1782 sqlStmt.Printf(wxT("DROP INDEX %s.%s"),
1783 pDb->SQLTableName(tableName.c_str()).c_str(),
1784 pDb->SQLTableName(idxName.c_str()).c_str());
1785 else
1786 sqlStmt.Printf(wxT("DROP INDEX %s"),
1787 pDb->SQLTableName(idxName.c_str()).c_str());
1788
1789 pDb->WriteSqlLog(sqlStmt);
1790
1791 #ifdef DBDEBUG_CONSOLE
1792 cout << endl << sqlStmt.c_str() << endl;
1793 #endif
1794
1795 if (SQLExecDirect(hstmt, (SQLTCHAR FAR *) sqlStmt.c_str(), SQL_NTS) != SQL_SUCCESS)
1796 {
1797 // Check for "Index not found" error and ignore
1798 pDb->GetNextError(henv, hdbc, hstmt);
1799 if (wxStrcmp(pDb->sqlState,wxT("S0012"))) // "Index not found"
1800 {
1801 // Check for product specific error codes
1802 if (!((pDb->Dbms() == dbmsSYBASE_ASA && !wxStrcmp(pDb->sqlState,wxT("42000"))) || // v5.x (and lower?)
1803 (pDb->Dbms() == dbmsSYBASE_ASE && !wxStrcmp(pDb->sqlState,wxT("37000"))) ||
1804 (pDb->Dbms() == dbmsMS_SQL_SERVER && !wxStrcmp(pDb->sqlState,wxT("S1000"))) ||
1805 (pDb->Dbms() == dbmsINTERBASE && !wxStrcmp(pDb->sqlState,wxT("S1000"))) ||
1806 (pDb->Dbms() == dbmsSYBASE_ASE && !wxStrcmp(pDb->sqlState,wxT("S0002"))) || // Base table not found
1807 (pDb->Dbms() == dbmsMY_SQL && !wxStrcmp(pDb->sqlState,wxT("42S12"))) || // tested by Christopher Ludwik Marino-Cebulski using v3.23.21beta
1808 (pDb->Dbms() == dbmsPOSTGRES && !wxStrcmp(pDb->sqlState,wxT("08S01")))
1809 ))
1810 {
1811 pDb->DispNextError();
1812 pDb->DispAllErrors(henv, hdbc, hstmt);
1813 pDb->RollbackTrans();
1814 CloseCursor(hstmt);
1815 return(FALSE);
1816 }
1817 }
1818 }
1819
1820 // Commit the transaction and close the cursor
1821 if (! pDb->CommitTrans())
1822 return(FALSE);
1823 if (! CloseCursor(hstmt))
1824 return(FALSE);
1825
1826 return(TRUE);
1827 } // wxDbTable::DropIndex()
1828
1829
1830 /********** wxDbTable::SetOrderByColNums() **********/
1831 bool wxDbTable::SetOrderByColNums(UWORD first, ... )
1832 {
1833 int colNo = first; // using 'int' to be able to look for wxDB_NO_MORE_COLUN_NUMBERS
1834 va_list argptr;
1835
1836 bool abort = FALSE;
1837 wxString tempStr;
1838
1839 va_start(argptr, first); /* Initialize variable arguments. */
1840 while (!abort && (colNo != wxDB_NO_MORE_COLUMN_NUMBERS))
1841 {
1842 // Make sure the passed in column number
1843 // is within the valid range of columns
1844 //
1845 // Valid columns are 0 thru noCols-1
1846 if (colNo >= noCols || colNo < 0)
1847 {
1848 abort = TRUE;
1849 continue;
1850 }
1851
1852 if (colNo != first)
1853 tempStr += wxT(",");
1854
1855 tempStr += colDefs[colNo].ColName;
1856 colNo = va_arg (argptr, int);
1857 }
1858 va_end (argptr); /* Reset variable arguments. */
1859
1860 SetOrderByClause(tempStr);
1861
1862 return (!abort);
1863 } // wxDbTable::SetOrderByColNums()
1864
1865
1866 /********** wxDbTable::Insert() **********/
1867 int wxDbTable::Insert(void)
1868 {
1869 wxASSERT(!queryOnly);
1870 if (queryOnly || !insertable)
1871 return(DB_FAILURE);
1872
1873 bindInsertParams();
1874
1875 // Insert the record by executing the already prepared insert statement
1876 RETCODE retcode;
1877 retcode=SQLExecute(hstmtInsert);
1878 if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO &&
1879 retcode != SQL_NEED_DATA)
1880 {
1881 // Check to see if integrity constraint was violated
1882 pDb->GetNextError(henv, hdbc, hstmtInsert);
1883 if (! wxStrcmp(pDb->sqlState, wxT("23000"))) // Integrity constraint violated
1884 return(DB_ERR_INTEGRITY_CONSTRAINT_VIOL);
1885 else
1886 {
1887 pDb->DispNextError();
1888 pDb->DispAllErrors(henv, hdbc, hstmtInsert);
1889 return(DB_FAILURE);
1890 }
1891 }
1892 if (retcode == SQL_NEED_DATA)
1893 {
1894 PTR pParmID;
1895 retcode = SQLParamData(hstmtInsert, &pParmID);
1896 while (retcode == SQL_NEED_DATA)
1897 {
1898 // Find the parameter
1899 int i;
1900 for (i=0; i < noCols; i++)
1901 {
1902 if (colDefs[i].PtrDataObj == pParmID)
1903 {
1904 // We found it. Store the parameter.
1905 retcode = SQLPutData(hstmtInsert, pParmID, colDefs[i].SzDataObj);
1906 if (retcode != SQL_SUCCESS)
1907 {
1908 pDb->DispNextError();
1909 pDb->DispAllErrors(henv, hdbc, hstmtInsert);
1910 return(DB_FAILURE);
1911 }
1912 break;
1913 }
1914 }
1915 }
1916 }
1917
1918 // Record inserted into the datasource successfully
1919 return(DB_SUCCESS);
1920
1921 } // wxDbTable::Insert()
1922
1923
1924 /********** wxDbTable::Update() **********/
1925 bool wxDbTable::Update(void)
1926 {
1927 wxASSERT(!queryOnly);
1928 if (queryOnly)
1929 return(FALSE);
1930
1931 wxString sqlStmt;
1932
1933 // Build the SQL UPDATE statement
1934 BuildUpdateStmt(sqlStmt, DB_UPD_KEYFIELDS);
1935
1936 pDb->WriteSqlLog(sqlStmt);
1937
1938 #ifdef DBDEBUG_CONSOLE
1939 cout << endl << sqlStmt.c_str() << endl << endl;
1940 #endif
1941
1942 // Execute the SQL UPDATE statement
1943 return(execUpdate(sqlStmt));
1944
1945 } // wxDbTable::Update()
1946
1947
1948 /********** wxDbTable::Update(pSqlStmt) **********/
1949 bool wxDbTable::Update(const wxString &pSqlStmt)
1950 {
1951 wxASSERT(!queryOnly);
1952 if (queryOnly)
1953 return(FALSE);
1954
1955 pDb->WriteSqlLog(pSqlStmt);
1956
1957 return(execUpdate(pSqlStmt));
1958
1959 } // wxDbTable::Update(pSqlStmt)
1960
1961
1962 /********** wxDbTable::UpdateWhere() **********/
1963 bool wxDbTable::UpdateWhere(const wxString &pWhereClause)
1964 {
1965 wxASSERT(!queryOnly);
1966 if (queryOnly)
1967 return(FALSE);
1968
1969 wxString sqlStmt;
1970
1971 // Build the SQL UPDATE statement
1972 BuildUpdateStmt(sqlStmt, DB_UPD_WHERE, pWhereClause);
1973
1974 pDb->WriteSqlLog(sqlStmt);
1975
1976 #ifdef DBDEBUG_CONSOLE
1977 cout << endl << sqlStmt.c_str() << endl << endl;
1978 #endif
1979
1980 // Execute the SQL UPDATE statement
1981 return(execUpdate(sqlStmt));
1982
1983 } // wxDbTable::UpdateWhere()
1984
1985
1986 /********** wxDbTable::Delete() **********/
1987 bool wxDbTable::Delete(void)
1988 {
1989 wxASSERT(!queryOnly);
1990 if (queryOnly)
1991 return(FALSE);
1992
1993 wxString sqlStmt;
1994 sqlStmt.Empty();
1995
1996 // Build the SQL DELETE statement
1997 BuildDeleteStmt(sqlStmt, DB_DEL_KEYFIELDS);
1998
1999 pDb->WriteSqlLog(sqlStmt);
2000
2001 // Execute the SQL DELETE statement
2002 return(execDelete(sqlStmt));
2003
2004 } // wxDbTable::Delete()
2005
2006
2007 /********** wxDbTable::DeleteWhere() **********/
2008 bool wxDbTable::DeleteWhere(const wxString &pWhereClause)
2009 {
2010 wxASSERT(!queryOnly);
2011 if (queryOnly)
2012 return(FALSE);
2013
2014 wxString sqlStmt;
2015 sqlStmt.Empty();
2016
2017 // Build the SQL DELETE statement
2018 BuildDeleteStmt(sqlStmt, DB_DEL_WHERE, pWhereClause);
2019
2020 pDb->WriteSqlLog(sqlStmt);
2021
2022 // Execute the SQL DELETE statement
2023 return(execDelete(sqlStmt));
2024
2025 } // wxDbTable::DeleteWhere()
2026
2027
2028 /********** wxDbTable::DeleteMatching() **********/
2029 bool wxDbTable::DeleteMatching(void)
2030 {
2031 wxASSERT(!queryOnly);
2032 if (queryOnly)
2033 return(FALSE);
2034
2035 wxString sqlStmt;
2036 sqlStmt.Empty();
2037
2038 // Build the SQL DELETE statement
2039 BuildDeleteStmt(sqlStmt, DB_DEL_MATCHING);
2040
2041 pDb->WriteSqlLog(sqlStmt);
2042
2043 // Execute the SQL DELETE statement
2044 return(execDelete(sqlStmt));
2045
2046 } // wxDbTable::DeleteMatching()
2047
2048
2049 /********** wxDbTable::IsColNull() **********/
2050 bool wxDbTable::IsColNull(UWORD colNo) const
2051 {
2052 /*
2053 This logic is just not right. It would indicate TRUE
2054 if a numeric field were set to a value of 0.
2055
2056 switch(colDefs[colNo].SqlCtype)
2057 {
2058 case SQL_C_CHAR:
2059 return(((UCHAR FAR *) colDefs[colNo].PtrDataObj)[0] == 0);
2060 case SQL_C_SSHORT:
2061 return(( *((SWORD *) colDefs[colNo].PtrDataObj)) == 0);
2062 case SQL_C_USHORT:
2063 return(( *((UWORD*) colDefs[colNo].PtrDataObj)) == 0);
2064 case SQL_C_SLONG:
2065 return(( *((SDWORD *) colDefs[colNo].PtrDataObj)) == 0);
2066 case SQL_C_ULONG:
2067 return(( *((UDWORD *) colDefs[colNo].PtrDataObj)) == 0);
2068 case SQL_C_FLOAT:
2069 return(( *((SFLOAT *) colDefs[colNo].PtrDataObj)) == 0);
2070 case SQL_C_DOUBLE:
2071 return((*((SDOUBLE *) colDefs[colNo].PtrDataObj)) == 0);
2072 case SQL_C_TIMESTAMP:
2073 TIMESTAMP_STRUCT *pDt;
2074 pDt = (TIMESTAMP_STRUCT *) colDefs[colNo].PtrDataObj;
2075 if (pDt->year == 0 && pDt->month == 0 && pDt->day == 0)
2076 return(TRUE);
2077 else
2078 return(FALSE);
2079 default:
2080 return(TRUE);
2081 }
2082 */
2083 return (colDefs[colNo].Null);
2084 } // wxDbTable::IsColNull()
2085
2086
2087 /********** wxDbTable::CanSelectForUpdate() **********/
2088 bool wxDbTable::CanSelectForUpdate(void)
2089 {
2090 if (queryOnly)
2091 return FALSE;
2092
2093 if (pDb->Dbms() == dbmsMY_SQL)
2094 return FALSE;
2095
2096 if ((pDb->Dbms() == dbmsORACLE) ||
2097 (pDb->dbInf.posStmts & SQL_PS_SELECT_FOR_UPDATE))
2098 return(TRUE);
2099 else
2100 return(FALSE);
2101
2102 } // wxDbTable::CanSelectForUpdate()
2103
2104
2105 /********** wxDbTable::CanUpdByROWID() **********/
2106 bool wxDbTable::CanUpdByROWID(void)
2107 {
2108 /*
2109 * NOTE: Returning FALSE for now until this can be debugged,
2110 * as the ROWID is not getting updated correctly
2111 */
2112 return FALSE;
2113 /*
2114 if (pDb->Dbms() == dbmsORACLE)
2115 return(TRUE);
2116 else
2117 return(FALSE);
2118 */
2119 } // wxDbTable::CanUpdByROWID()
2120
2121
2122 /********** wxDbTable::IsCursorClosedOnCommit() **********/
2123 bool wxDbTable::IsCursorClosedOnCommit(void)
2124 {
2125 if (pDb->dbInf.cursorCommitBehavior == SQL_CB_PRESERVE)
2126 return(FALSE);
2127 else
2128 return(TRUE);
2129
2130 } // wxDbTable::IsCursorClosedOnCommit()
2131
2132
2133
2134 /********** wxDbTable::ClearMemberVar() **********/
2135 void wxDbTable::ClearMemberVar(UWORD colNo, bool setToNull)
2136 {
2137 wxASSERT(colNo < noCols);
2138
2139 switch(colDefs[colNo].SqlCtype)
2140 {
2141 case SQL_C_CHAR:
2142 ((UCHAR FAR *) colDefs[colNo].PtrDataObj)[0] = 0;
2143 break;
2144 case SQL_C_SSHORT:
2145 *((SWORD *) colDefs[colNo].PtrDataObj) = 0;
2146 break;
2147 case SQL_C_USHORT:
2148 *((UWORD*) colDefs[colNo].PtrDataObj) = 0;
2149 break;
2150 case SQL_C_SLONG:
2151 *((SDWORD *) colDefs[colNo].PtrDataObj) = 0;
2152 break;
2153 case SQL_C_ULONG:
2154 *((UDWORD *) colDefs[colNo].PtrDataObj) = 0;
2155 break;
2156 case SQL_C_FLOAT:
2157 *((SFLOAT *) colDefs[colNo].PtrDataObj) = 0.0f;
2158 break;
2159 case SQL_C_DOUBLE:
2160 *((SDOUBLE *) colDefs[colNo].PtrDataObj) = 0.0f;
2161 break;
2162 case SQL_C_TIMESTAMP:
2163 TIMESTAMP_STRUCT *pDt;
2164 pDt = (TIMESTAMP_STRUCT *) colDefs[colNo].PtrDataObj;
2165 pDt->year = 0;
2166 pDt->month = 0;
2167 pDt->day = 0;
2168 pDt->hour = 0;
2169 pDt->minute = 0;
2170 pDt->second = 0;
2171 pDt->fraction = 0;
2172 break;
2173 }
2174
2175 if (setToNull)
2176 SetColNull(colNo);
2177 } // wxDbTable::ClearMemberVar()
2178
2179
2180 /********** wxDbTable::ClearMemberVars() **********/
2181 void wxDbTable::ClearMemberVars(bool setToNull)
2182 {
2183 int i;
2184
2185 // Loop through the columns setting each member variable to zero
2186 for (i=0; i < noCols; i++)
2187 ClearMemberVar(i,setToNull);
2188
2189 } // wxDbTable::ClearMemberVars()
2190
2191
2192 /********** wxDbTable::SetQueryTimeout() **********/
2193 bool wxDbTable::SetQueryTimeout(UDWORD nSeconds)
2194 {
2195 if (SQLSetStmtOption(hstmtInsert, SQL_QUERY_TIMEOUT, nSeconds) != SQL_SUCCESS)
2196 return(pDb->DispAllErrors(henv, hdbc, hstmtInsert));
2197 if (SQLSetStmtOption(hstmtUpdate, SQL_QUERY_TIMEOUT, nSeconds) != SQL_SUCCESS)
2198 return(pDb->DispAllErrors(henv, hdbc, hstmtUpdate));
2199 if (SQLSetStmtOption(hstmtDelete, SQL_QUERY_TIMEOUT, nSeconds) != SQL_SUCCESS)
2200 return(pDb->DispAllErrors(henv, hdbc, hstmtDelete));
2201 if (SQLSetStmtOption(hstmtInternal, SQL_QUERY_TIMEOUT, nSeconds) != SQL_SUCCESS)
2202 return(pDb->DispAllErrors(henv, hdbc, hstmtInternal));
2203
2204 // Completed Successfully
2205 return(TRUE);
2206
2207 } // wxDbTable::SetQueryTimeout()
2208
2209
2210 /********** wxDbTable::SetColDefs() **********/
2211 void wxDbTable::SetColDefs(UWORD index, const wxString &fieldName, int dataType, void *pData,
2212 SWORD cType, int size, bool keyField, bool upd,
2213 bool insAllow, bool derivedCol)
2214 {
2215 wxASSERT_MSG( index < noCols,
2216 _T("Specified column index exceeds the maximum number of columns for this table.") );
2217
2218 if (!colDefs) // May happen if the database connection fails
2219 return;
2220
2221 if (fieldName.Length() > (unsigned int) DB_MAX_COLUMN_NAME_LEN)
2222 {
2223 wxStrncpy(colDefs[index].ColName, fieldName, DB_MAX_COLUMN_NAME_LEN);
2224 colDefs[index].ColName[DB_MAX_COLUMN_NAME_LEN] = 0;
2225
2226 #ifdef __WXDEBUG__
2227 wxString tmpMsg;
2228 tmpMsg.Printf(_T("Column name '%s' is too long. Truncated to '%s'."),
2229 fieldName.c_str(),colDefs[index].ColName);
2230 wxFAIL_MSG(tmpMsg);
2231 #endif // __WXDEBUG__
2232 }
2233 else
2234 wxStrcpy(colDefs[index].ColName, fieldName);
2235
2236 colDefs[index].DbDataType = dataType;
2237 colDefs[index].PtrDataObj = pData;
2238 colDefs[index].SqlCtype = cType;
2239 colDefs[index].SzDataObj = size;
2240 colDefs[index].KeyField = keyField;
2241 colDefs[index].DerivedCol = derivedCol;
2242 // Derived columns by definition would NOT be "Insertable" or "Updateable"
2243 if (derivedCol)
2244 {
2245 colDefs[index].Updateable = FALSE;
2246 colDefs[index].InsertAllowed = FALSE;
2247 }
2248 else
2249 {
2250 colDefs[index].Updateable = upd;
2251 colDefs[index].InsertAllowed = insAllow;
2252 }
2253
2254 colDefs[index].Null = FALSE;
2255
2256 } // wxDbTable::SetColDefs()
2257
2258
2259 /********** wxDbTable::SetColDefs() **********/
2260 wxDbColDataPtr* wxDbTable::SetColDefs(wxDbColInf *pColInfs, UWORD numCols)
2261 {
2262 wxASSERT(pColInfs);
2263 wxDbColDataPtr *pColDataPtrs = NULL;
2264
2265 if (pColInfs)
2266 {
2267 UWORD index;
2268
2269 pColDataPtrs = new wxDbColDataPtr[numCols+1];
2270
2271 for (index = 0; index < numCols; index++)
2272 {
2273 // Process the fields
2274 switch (pColInfs[index].dbDataType)
2275 {
2276 case DB_DATA_TYPE_VARCHAR:
2277 pColDataPtrs[index].PtrDataObj = new wxChar[pColInfs[index].bufferLength+1];
2278 pColDataPtrs[index].SzDataObj = pColInfs[index].columnSize;
2279 pColDataPtrs[index].SqlCtype = SQL_C_CHAR;
2280 break;
2281 case DB_DATA_TYPE_INTEGER:
2282 // Can be long or short
2283 if (pColInfs[index].bufferLength == sizeof(long))
2284 {
2285 pColDataPtrs[index].PtrDataObj = new long;
2286 pColDataPtrs[index].SzDataObj = sizeof(long);
2287 pColDataPtrs[index].SqlCtype = SQL_C_SLONG;
2288 }
2289 else
2290 {
2291 pColDataPtrs[index].PtrDataObj = new short;
2292 pColDataPtrs[index].SzDataObj = sizeof(short);
2293 pColDataPtrs[index].SqlCtype = SQL_C_SSHORT;
2294 }
2295 break;
2296 case DB_DATA_TYPE_FLOAT:
2297 // Can be float or double
2298 if (pColInfs[index].bufferLength == sizeof(float))
2299 {
2300 pColDataPtrs[index].PtrDataObj = new float;
2301 pColDataPtrs[index].SzDataObj = sizeof(float);
2302 pColDataPtrs[index].SqlCtype = SQL_C_FLOAT;
2303 }
2304 else
2305 {
2306 pColDataPtrs[index].PtrDataObj = new double;
2307 pColDataPtrs[index].SzDataObj = sizeof(double);
2308 pColDataPtrs[index].SqlCtype = SQL_C_DOUBLE;
2309 }
2310 break;
2311 case DB_DATA_TYPE_DATE:
2312 pColDataPtrs[index].PtrDataObj = new TIMESTAMP_STRUCT;
2313 pColDataPtrs[index].SzDataObj = sizeof(TIMESTAMP_STRUCT);
2314 pColDataPtrs[index].SqlCtype = SQL_C_TIMESTAMP;
2315 break;
2316 case DB_DATA_TYPE_BLOB:
2317 wxFAIL_MSG(wxT("This form of ::SetColDefs() cannot be used with BLOB columns"));
2318 pColDataPtrs[index].PtrDataObj = /*BLOB ADDITION NEEDED*/NULL;
2319 pColDataPtrs[index].SzDataObj = /*BLOB ADDITION NEEDED*/sizeof(void *);
2320 pColDataPtrs[index].SqlCtype = SQL_VARBINARY;
2321 break;
2322 }
2323 if (pColDataPtrs[index].PtrDataObj != NULL)
2324 SetColDefs (index,pColInfs[index].colName,pColInfs[index].dbDataType, pColDataPtrs[index].PtrDataObj, pColDataPtrs[index].SqlCtype, pColDataPtrs[index].SzDataObj);
2325 else
2326 {
2327 // Unable to build all the column definitions, as either one of
2328 // the calls to "new" failed above, or there was a BLOB field
2329 // to have a column definition for. If BLOBs are to be used,
2330 // the other form of ::SetColDefs() must be used, as it is impossible
2331 // to know the maximum size to create the PtrDataObj to be.
2332 delete [] pColDataPtrs;
2333 return NULL;
2334 }
2335 }
2336 }
2337
2338 return (pColDataPtrs);
2339
2340 } // wxDbTable::SetColDefs()
2341
2342
2343 /********** wxDbTable::SetCursor() **********/
2344 void wxDbTable::SetCursor(HSTMT *hstmtActivate)
2345 {
2346 if (hstmtActivate == wxDB_DEFAULT_CURSOR)
2347 hstmt = *hstmtDefault;
2348 else
2349 hstmt = *hstmtActivate;
2350
2351 } // wxDbTable::SetCursor()
2352
2353
2354 /********** wxDbTable::Count(const wxString &) **********/
2355 ULONG wxDbTable::Count(const wxString &args)
2356 {
2357 ULONG count;
2358 wxString sqlStmt;
2359 SDWORD cb;
2360
2361 // Build a "SELECT COUNT(*) FROM queryTableName [WHERE whereClause]" SQL Statement
2362 sqlStmt = wxT("SELECT COUNT(");
2363 sqlStmt += args;
2364 sqlStmt += wxT(") FROM ");
2365 sqlStmt += pDb->SQLTableName(queryTableName);
2366 // sqlStmt += queryTableName;
2367 #if wxODBC_BACKWARD_COMPATABILITY
2368 if (from && wxStrlen(from))
2369 #else
2370 if (from.Length())
2371 #endif
2372 sqlStmt += from;
2373
2374 // Add the where clause if one is provided
2375 #if wxODBC_BACKWARD_COMPATABILITY
2376 if (where && wxStrlen(where))
2377 #else
2378 if (where.Length())
2379 #endif
2380 {
2381 sqlStmt += wxT(" WHERE ");
2382 sqlStmt += where;
2383 }
2384
2385 pDb->WriteSqlLog(sqlStmt);
2386
2387 // Initialize the Count cursor if it's not already initialized
2388 if (!hstmtCount)
2389 {
2390 hstmtCount = GetNewCursor(FALSE,FALSE);
2391 wxASSERT(hstmtCount);
2392 if (!hstmtCount)
2393 return(0);
2394 }
2395
2396 // Execute the SQL statement
2397 if (SQLExecDirect(*hstmtCount, (SQLTCHAR FAR *) sqlStmt.c_str(), SQL_NTS) != SQL_SUCCESS)
2398 {
2399 pDb->DispAllErrors(henv, hdbc, *hstmtCount);
2400 return(0);
2401 }
2402
2403 // Fetch the record
2404 if (SQLFetch(*hstmtCount) != SQL_SUCCESS)
2405 {
2406 pDb->DispAllErrors(henv, hdbc, *hstmtCount);
2407 return(0);
2408 }
2409
2410 // Obtain the result
2411 if (SQLGetData(*hstmtCount, (UWORD)1, SQL_C_ULONG, &count, sizeof(count), &cb) != SQL_SUCCESS)
2412 {
2413 pDb->DispAllErrors(henv, hdbc, *hstmtCount);
2414 return(0);
2415 }
2416
2417 // Free the cursor
2418 if (SQLFreeStmt(*hstmtCount, SQL_CLOSE) != SQL_SUCCESS)
2419 pDb->DispAllErrors(henv, hdbc, *hstmtCount);
2420
2421 // Return the record count
2422 return(count);
2423
2424 } // wxDbTable::Count()
2425
2426
2427 /********** wxDbTable::Refresh() **********/
2428 bool wxDbTable::Refresh(void)
2429 {
2430 bool result = TRUE;
2431
2432 // Switch to the internal cursor so any active cursors are not corrupted
2433 HSTMT currCursor = GetCursor();
2434 hstmt = hstmtInternal;
2435 #if wxODBC_BACKWARD_COMPATABILITY
2436 // Save the where and order by clauses
2437 wxChar *saveWhere = where;
2438 wxChar *saveOrderBy = orderBy;
2439 #else
2440 wxString saveWhere = where;
2441 wxString saveOrderBy = orderBy;
2442 #endif
2443 // Build a where clause to refetch the record with. Try and use the
2444 // ROWID if it's available, ow use the key fields.
2445 wxString whereClause;
2446 whereClause.Empty();
2447
2448 if (CanUpdByROWID())
2449 {
2450 SDWORD cb;
2451 wxChar rowid[wxDB_ROWID_LEN+1];
2452
2453 // Get the ROWID value. If not successful retreiving the ROWID,
2454 // simply fall down through the code and build the WHERE clause
2455 // based on the key fields.
2456 if (SQLGetData(hstmt, (UWORD)(noCols+1), SQL_C_CHAR, (UCHAR*) rowid, wxDB_ROWID_LEN, &cb) == SQL_SUCCESS)
2457 {
2458 whereClause += pDb->SQLTableName(queryTableName);
2459 // whereClause += queryTableName;
2460 whereClause += wxT(".ROWID = '");
2461 whereClause += rowid;
2462 whereClause += wxT("'");
2463 }
2464 }
2465
2466 // If unable to use the ROWID, build a where clause from the keyfields
2467 if (wxStrlen(whereClause) == 0)
2468 BuildWhereClause(whereClause, DB_WHERE_KEYFIELDS, queryTableName);
2469
2470 // Requery the record
2471 where = whereClause;
2472 orderBy.Empty();
2473 if (!Query())
2474 result = FALSE;
2475
2476 if (result && !GetNext())
2477 result = FALSE;
2478
2479 // Switch back to original cursor
2480 SetCursor(&currCursor);
2481
2482 // Free the internal cursor
2483 if (SQLFreeStmt(hstmtInternal, SQL_CLOSE) != SQL_SUCCESS)
2484 pDb->DispAllErrors(henv, hdbc, hstmtInternal);
2485
2486 // Restore the original where and order by clauses
2487 where = saveWhere;
2488 orderBy = saveOrderBy;
2489
2490 return(result);
2491
2492 } // wxDbTable::Refresh()
2493
2494
2495 /********** wxDbTable::SetColNull() **********/
2496 bool wxDbTable::SetColNull(UWORD colNo, bool set)
2497 {
2498 if (colNo < noCols)
2499 {
2500 colDefs[colNo].Null = set;
2501 if (set) // Blank out the values in the member variable
2502 {
2503 colDefs[colNo].CbValue = SQL_NULL_DATA; // SF PATCH#766404
2504 ClearMemberVar(colNo,FALSE); // Must call with FALSE, or infinite recursion will happen
2505 }
2506 return(TRUE);
2507 }
2508 else
2509 return(FALSE);
2510
2511 } // wxDbTable::SetColNull()
2512
2513
2514 /********** wxDbTable::SetColNull() **********/
2515 bool wxDbTable::SetColNull(const wxString &colName, bool set)
2516 {
2517 int colNo;
2518 for (colNo = 0; colNo < noCols; colNo++)
2519 {
2520 if (!wxStricmp(colName, colDefs[colNo].ColName))
2521 break;
2522 }
2523
2524 if (colNo < noCols)
2525 {
2526 colDefs[colNo].Null = set;
2527 if (set) // Blank out the values in the member variable
2528 {
2529 colDefs[colNo].CbValue = SQL_NULL_DATA; // SF PATCH#766404
2530 ClearMemberVar(colNo,FALSE); // Must call with FALSE, or infinite recursion will happen
2531 }
2532 return(TRUE);
2533 }
2534 else
2535 return(FALSE);
2536
2537 } // wxDbTable::SetColNull()
2538
2539
2540 /********** wxDbTable::GetNewCursor() **********/
2541 HSTMT *wxDbTable::GetNewCursor(bool setCursor, bool bindColumns)
2542 {
2543 HSTMT *newHSTMT = new HSTMT;
2544 wxASSERT(newHSTMT);
2545 if (!newHSTMT)
2546 return(0);
2547
2548 if (SQLAllocStmt(hdbc, newHSTMT) != SQL_SUCCESS)
2549 {
2550 pDb->DispAllErrors(henv, hdbc);
2551 delete newHSTMT;
2552 return(0);
2553 }
2554
2555 if (SQLSetStmtOption(*newHSTMT, SQL_CURSOR_TYPE, cursorType) != SQL_SUCCESS)
2556 {
2557 pDb->DispAllErrors(henv, hdbc, *newHSTMT);
2558 delete newHSTMT;
2559 return(0);
2560 }
2561
2562 if (bindColumns)
2563 {
2564 if (!bindCols(*newHSTMT))
2565 {
2566 delete newHSTMT;
2567 return(0);
2568 }
2569 }
2570
2571 if (setCursor)
2572 SetCursor(newHSTMT);
2573
2574 return(newHSTMT);
2575
2576 } // wxDbTable::GetNewCursor()
2577
2578
2579 /********** wxDbTable::DeleteCursor() **********/
2580 bool wxDbTable::DeleteCursor(HSTMT *hstmtDel)
2581 {
2582 bool result = TRUE;
2583
2584 if (!hstmtDel) // Cursor already deleted
2585 return(result);
2586
2587 /*
2588 ODBC 3.0 says to use this form
2589 if (SQLFreeHandle(*hstmtDel, SQL_DROP) != SQL_SUCCESS)
2590
2591 */
2592 if (SQLFreeStmt(*hstmtDel, SQL_DROP) != SQL_SUCCESS)
2593 {
2594 pDb->DispAllErrors(henv, hdbc);
2595 result = FALSE;
2596 }
2597
2598 delete hstmtDel;
2599
2600 return(result);
2601
2602 } // wxDbTable::DeleteCursor()
2603
2604 //////////////////////////////////////////////////////////////
2605 // wxDbGrid support functions
2606 //////////////////////////////////////////////////////////////
2607
2608 void wxDbTable::SetRowMode(const rowmode_t rowmode)
2609 {
2610 if (!m_hstmtGridQuery)
2611 {
2612 m_hstmtGridQuery = GetNewCursor(FALSE,FALSE);
2613 if (!bindCols(*m_hstmtGridQuery))
2614 return;
2615 }
2616
2617 m_rowmode = rowmode;
2618 switch (m_rowmode)
2619 {
2620 case WX_ROW_MODE_QUERY:
2621 SetCursor(m_hstmtGridQuery);
2622 break;
2623 case WX_ROW_MODE_INDIVIDUAL:
2624 SetCursor(hstmtDefault);
2625 break;
2626 default:
2627 wxASSERT(0);
2628 }
2629 } // wxDbTable::SetRowMode()
2630
2631
2632 wxVariant wxDbTable::GetCol(const int colNo) const
2633 {
2634 wxVariant val;
2635 if ((colNo < noCols) && (!IsColNull(colNo)))
2636 {
2637 switch (colDefs[colNo].SqlCtype)
2638 {
2639 case SQL_CHAR:
2640 case SQL_VARCHAR:
2641 val = (wxChar *)(colDefs[colNo].PtrDataObj);
2642 break;
2643 case SQL_C_LONG:
2644 case SQL_C_SLONG:
2645 val = *(long *)(colDefs[colNo].PtrDataObj);
2646 break;
2647 case SQL_C_SHORT:
2648 case SQL_C_SSHORT:
2649 val = (long int )(*(short *)(colDefs[colNo].PtrDataObj));
2650 break;
2651 case SQL_C_ULONG:
2652 val = (long)(*(unsigned long *)(colDefs[colNo].PtrDataObj));
2653 break;
2654 case SQL_C_TINYINT:
2655 val = (long)(*(wxChar *)(colDefs[colNo].PtrDataObj));
2656 break;
2657 case SQL_C_UTINYINT:
2658 val = (long)(*(wxChar *)(colDefs[colNo].PtrDataObj));
2659 break;
2660 case SQL_C_USHORT:
2661 val = (long)(*(UWORD *)(colDefs[colNo].PtrDataObj));
2662 break;
2663 case SQL_C_DATE:
2664 val = (DATE_STRUCT *)(colDefs[colNo].PtrDataObj);
2665 break;
2666 case SQL_C_TIME:
2667 val = (TIME_STRUCT *)(colDefs[colNo].PtrDataObj);
2668 break;
2669 case SQL_C_TIMESTAMP:
2670 val = (TIMESTAMP_STRUCT *)(colDefs[colNo].PtrDataObj);
2671 break;
2672 case SQL_C_DOUBLE:
2673 val = *(double *)(colDefs[colNo].PtrDataObj);
2674 break;
2675 default:
2676 assert(0);
2677 }
2678 }
2679 return val;
2680 } // wxDbTable::GetCol()
2681
2682
2683 void wxDbTable::SetCol(const int colNo, const wxVariant val)
2684 {
2685 //FIXME: Add proper wxDateTime support to wxVariant..
2686 wxDateTime dateval;
2687
2688 SetColNull(colNo, val.IsNull());
2689
2690 if (!val.IsNull())
2691 {
2692 if ((colDefs[colNo].SqlCtype == SQL_C_DATE)
2693 || (colDefs[colNo].SqlCtype == SQL_C_TIME)
2694 || (colDefs[colNo].SqlCtype == SQL_C_TIMESTAMP))
2695 {
2696 //Returns null if invalid!
2697 if (!dateval.ParseDate(val.GetString()))
2698 SetColNull(colNo, TRUE);
2699 }
2700
2701 switch (colDefs[colNo].SqlCtype)
2702 {
2703 case SQL_CHAR:
2704 case SQL_VARCHAR:
2705 csstrncpyt((wxChar *)(colDefs[colNo].PtrDataObj),
2706 val.GetString().c_str(),
2707 colDefs[colNo].SzDataObj-1);
2708 break;
2709 case SQL_C_LONG:
2710 case SQL_C_SLONG:
2711 *(long *)(colDefs[colNo].PtrDataObj) = val;
2712 break;
2713 case SQL_C_SHORT:
2714 case SQL_C_SSHORT:
2715 *(short *)(colDefs[colNo].PtrDataObj) = val.GetLong();
2716 break;
2717 case SQL_C_ULONG:
2718 *(unsigned long *)(colDefs[colNo].PtrDataObj) = val.GetLong();
2719 break;
2720 case SQL_C_TINYINT:
2721 *(wxChar *)(colDefs[colNo].PtrDataObj) = val.GetChar();
2722 break;
2723 case SQL_C_UTINYINT:
2724 *(wxChar *)(colDefs[colNo].PtrDataObj) = val.GetChar();
2725 break;
2726 case SQL_C_USHORT:
2727 *(unsigned short *)(colDefs[colNo].PtrDataObj) = val.GetLong();
2728 break;
2729 //FIXME: Add proper wxDateTime support to wxVariant..
2730 case SQL_C_DATE:
2731 {
2732 DATE_STRUCT *dataptr =
2733 (DATE_STRUCT *)colDefs[colNo].PtrDataObj;
2734
2735 dataptr->year = dateval.GetYear();
2736 dataptr->month = dateval.GetMonth()+1;
2737 dataptr->day = dateval.GetDay();
2738 }
2739 break;
2740 case SQL_C_TIME:
2741 {
2742 TIME_STRUCT *dataptr =
2743 (TIME_STRUCT *)colDefs[colNo].PtrDataObj;
2744
2745 dataptr->hour = dateval.GetHour();
2746 dataptr->minute = dateval.GetMinute();
2747 dataptr->second = dateval.GetSecond();
2748 }
2749 break;
2750 case SQL_C_TIMESTAMP:
2751 {
2752 TIMESTAMP_STRUCT *dataptr =
2753 (TIMESTAMP_STRUCT *)colDefs[colNo].PtrDataObj;
2754 dataptr->year = dateval.GetYear();
2755 dataptr->month = dateval.GetMonth()+1;
2756 dataptr->day = dateval.GetDay();
2757
2758 dataptr->hour = dateval.GetHour();
2759 dataptr->minute = dateval.GetMinute();
2760 dataptr->second = dateval.GetSecond();
2761 }
2762 break;
2763 case SQL_C_DOUBLE:
2764 *(double *)(colDefs[colNo].PtrDataObj) = val;
2765 break;
2766 default:
2767 assert(0);
2768 } // switch
2769 } // if (!val.IsNull())
2770 } // wxDbTable::SetCol()
2771
2772
2773 GenericKey wxDbTable::GetKey()
2774 {
2775 void *blk;
2776 wxChar *blkptr;
2777
2778 blk = malloc(m_keysize);
2779 blkptr = (wxChar *) blk;
2780
2781 int i;
2782 for (i=0; i < noCols; i++)
2783 {
2784 if (colDefs[i].KeyField)
2785 {
2786 memcpy(blkptr,colDefs[i].PtrDataObj, colDefs[i].SzDataObj);
2787 blkptr += colDefs[i].SzDataObj;
2788 }
2789 }
2790
2791 GenericKey k = GenericKey(blk, m_keysize);
2792 free(blk);
2793
2794 return k;
2795 } // wxDbTable::GetKey()
2796
2797
2798 void wxDbTable::SetKey(const GenericKey& k)
2799 {
2800 void *blk;
2801 wxChar *blkptr;
2802
2803 blk = k.GetBlk();
2804 blkptr = (wxChar *)blk;
2805
2806 int i;
2807 for (i=0; i < noCols; i++)
2808 {
2809 if (colDefs[i].KeyField)
2810 {
2811 SetColNull(i, FALSE);
2812 memcpy(colDefs[i].PtrDataObj, blkptr, colDefs[i].SzDataObj);
2813 blkptr += colDefs[i].SzDataObj;
2814 }
2815 }
2816 } // wxDbTable::SetKey()
2817
2818
2819 #endif // wxUSE_ODBC
2820
wxWidgets (Impactor)