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