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