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