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