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