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