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