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