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