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