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