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