]> 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
Added m_destroybuf initialisation
[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, plus:
12// Notice: This class library and its intellectual design are free of charge for use,
13// modification, enhancement, debugging under the following conditions:
14// 1) These classes may only be used as part of the implementation of a
15// wxWindows-based application
16// 2) All enhancements and bug fixes are to be submitted back to the wxWindows
17// user groups free of all charges for use with the wxWindows library.
18// 3) These classes may not be distributed as part of any other class library,
19// DLL, text (written or electronic), other than a complete distribution of
20// the wxWindows GUI development toolkit.
21///////////////////////////////////////////////////////////////////////////////
22
23/*
24// SYNOPSIS START
25// SYNOPSIS STOP
26*/
27
28// Use this line for wxWindows v1.x
29//#include "wx_ver.h"
30// Use this line for wxWindows v2.x
31#include "wx/wxprec.h"
32#include "wx/version.h"
33
34#if wxMAJOR_VERSION == 2
35 #ifdef __GNUG__
36 #pragma implementation "dbtable.h"
37 #endif
38#endif
39
40#ifdef DBDEBUG_CONSOLE
41 #include "wx/ioswrap.h"
42#endif
43
44
45#ifdef __BORLANDC__
46 #pragma hdrstop
47#endif //__BORLANDC__
48
49#if wxMAJOR_VERSION == 2
50 #ifndef WX_PRECOMP
51 #include "wx/string.h"
52 #include "wx/object.h"
53 #include "wx/list.h"
54 #include "wx/utils.h"
55 #include "wx/msgdlg.h"
56 #include "wx/log.h"
57 #endif
58 #include "wx/filefn.h"
59#endif
60
61#if wxMAJOR_VERSION == 1
62# if defined(wx_msw) || defined(wx_x)
63# ifdef WX_PRECOMP
64# include "wx_prec.h"
65# else
66# include "wx.h"
67# endif
68# endif
69# define wxUSE_ODBC 1
70#endif
71
72
73#if wxUSE_ODBC
74
75#include <stdio.h>
76#include <stdlib.h>
77#include <string.h>
78#include <assert.h>
79
80#if wxMAJOR_VERSION == 1
81 #include "table.h"
82#elif wxMAJOR_VERSION == 2
83 #include "wx/dbtable.h"
84#endif
85
86#ifdef __UNIX__
87// The HPUX preprocessor lines below were commented out on 8/20/97
88// because macros.h currently redefines DEBUG and is unneeded.
89// # ifdef HPUX
90// # include <macros.h>
91// # endif
92# ifdef LINUX
93# include <sys/minmax.h>
94# endif
95#endif
96
97ULONG lastTableID = 0;
98
99
100#ifdef __WXDEBUG__
101 wxList TablesInUse;
102#endif
103
104
105/********** wxDbTable::wxDbTable() **********/
106wxDbTable::wxDbTable(wxDb *pwxDb, const char *tblName, const int nCols,
107 const char *qryTblName, bool qryOnly, const char *tblPath)
108{
109 pDb = pwxDb; // Pointer to the wxDb object
110 henv = 0;
111 hdbc = 0;
112 hstmt = 0;
113 hstmtDefault = 0; // Initialized below
114 hstmtCount = 0; // Initialized first time it is needed
115 hstmtInsert = 0;
116 hstmtDelete = 0;
117 hstmtUpdate = 0;
118 hstmtInternal = 0;
119 colDefs = 0;
120 tableID = 0;
121 noCols = nCols; // No. of cols in the table
122 where = ""; // Where clause
123 orderBy = ""; // Order By clause
124 from = ""; // From clause
125 selectForUpdate = FALSE; // SELECT ... FOR UPDATE; Indicates whether to include the FOR UPDATE phrase
126 queryOnly = qryOnly;
127 insertable = TRUE;
128 wxStrcpy(tablePath,"");
129 wxStrcpy(tableName,"");
130 wxStrcpy(queryTableName,"");
131
132 assert (tblName);
133
134 wxStrcpy(tableName, tblName); // Table Name
135 if (tblPath)
136 wxStrcpy(tablePath, tblPath); // Table Path - used for dBase files
137 else
138 tablePath[0] = 0;
139
140 if (qryTblName) // Name of the table/view to query
141 wxStrcpy(queryTableName, qryTblName);
142 else
143 wxStrcpy(queryTableName, tblName);
144
145 if (!pDb)
146 return;
147
148 pDb->incrementTableCount();
149
150 wxString s;
151 tableID = ++lastTableID;
152 s.sprintf("wxDbTable constructor (%-20s) tableID:[%6lu] pDb:[%p]", tblName,tableID,pDb);
153
154#ifdef __WXDEBUG__
155 wxTablesInUse *tableInUse;
156 tableInUse = new wxTablesInUse();
157 tableInUse->tableName = tblName;
158 tableInUse->tableID = tableID;
159 tableInUse->pDb = pDb;
160 TablesInUse.Append(tableInUse);
161#endif
162
163 pDb->WriteSqlLog(s.c_str());
164
165 // Grab the HENV and HDBC from the wxDb object
166 henv = pDb->GetHENV();
167 hdbc = pDb->GetHDBC();
168
169 // Allocate space for column definitions
170 if (noCols)
171 colDefs = new wxDbColDef[noCols]; // Points to the first column defintion
172
173 // Allocate statement handles for the table
174 if (!queryOnly)
175 {
176 // Allocate a separate statement handle for performing inserts
177 if (SQLAllocStmt(hdbc, &hstmtInsert) != SQL_SUCCESS)
178 pDb->DispAllErrors(henv, hdbc);
179 // Allocate a separate statement handle for performing deletes
180 if (SQLAllocStmt(hdbc, &hstmtDelete) != SQL_SUCCESS)
181 pDb->DispAllErrors(henv, hdbc);
182 // Allocate a separate statement handle for performing updates
183 if (SQLAllocStmt(hdbc, &hstmtUpdate) != SQL_SUCCESS)
184 pDb->DispAllErrors(henv, hdbc);
185 }
186 // Allocate a separate statement handle for internal use
187 if (SQLAllocStmt(hdbc, &hstmtInternal) != SQL_SUCCESS)
188 pDb->DispAllErrors(henv, hdbc);
189
190 // Set the cursor type for the statement handles
191 cursorType = SQL_CURSOR_STATIC;
192
193 if (SQLSetStmtOption(hstmtInternal, SQL_CURSOR_TYPE, cursorType) != SQL_SUCCESS)
194 {
195 // Check to see if cursor type is supported
196 pDb->GetNextError(henv, hdbc, hstmtInternal);
197 if (! wxStrcmp(pDb->sqlState, "01S02")) // Option Value Changed
198 {
199 // Datasource does not support static cursors. Driver
200 // will substitute a cursor type. Call SQLGetStmtOption()
201 // to determine which cursor type was selected.
202 if (SQLGetStmtOption(hstmtInternal, SQL_CURSOR_TYPE, &cursorType) != SQL_SUCCESS)
203 pDb->DispAllErrors(henv, hdbc, hstmtInternal);
204#ifdef DBDEBUG_CONSOLE
205 cout << "Static cursor changed to: ";
206 switch(cursorType)
207 {
208 case SQL_CURSOR_FORWARD_ONLY:
209 cout << "Forward Only";
210 break;
211 case SQL_CURSOR_STATIC:
212 cout << "Static";
213 break;
214 case SQL_CURSOR_KEYSET_DRIVEN:
215 cout << "Keyset Driven";
216 break;
217 case SQL_CURSOR_DYNAMIC:
218 cout << "Dynamic";
219 break;
220 }
221 cout << endl << endl;
222#endif
223 // BJO20000425
224 if (pDb->FwdOnlyCursors() && cursorType != SQL_CURSOR_FORWARD_ONLY)
225 {
226 // Force the use of a forward only cursor...
227 cursorType = SQL_CURSOR_FORWARD_ONLY;
228 if (SQLSetStmtOption(hstmtInternal, SQL_CURSOR_TYPE, cursorType) != SQL_SUCCESS)
229 {
230 // Should never happen
231 pDb->GetNextError(henv, hdbc, hstmtInternal);
232 return;
233 }
234 }
235 }
236 else
237 {
238 pDb->DispNextError();
239 pDb->DispAllErrors(henv, hdbc, hstmtInternal);
240 }
241 }
242#ifdef DBDEBUG_CONSOLE
243 else
244 cout << "Cursor Type set to STATIC" << endl << endl;
245#endif
246
247 if (!queryOnly)
248 {
249 // Set the cursor type for the INSERT statement handle
250 if (SQLSetStmtOption(hstmtInsert, SQL_CURSOR_TYPE, SQL_CURSOR_FORWARD_ONLY) != SQL_SUCCESS)
251 pDb->DispAllErrors(henv, hdbc, hstmtInsert);
252 // Set the cursor type for the DELETE statement handle
253 if (SQLSetStmtOption(hstmtDelete, SQL_CURSOR_TYPE, SQL_CURSOR_FORWARD_ONLY) != SQL_SUCCESS)
254 pDb->DispAllErrors(henv, hdbc, hstmtDelete);
255 // Set the cursor type for the UPDATE statement handle
256 if (SQLSetStmtOption(hstmtUpdate, SQL_CURSOR_TYPE, SQL_CURSOR_FORWARD_ONLY) != SQL_SUCCESS)
257 pDb->DispAllErrors(henv, hdbc, hstmtUpdate);
258 }
259
260 // Make the default cursor the active cursor
261 hstmtDefault = GetNewCursor(FALSE,FALSE);
262 assert(hstmtDefault);
263 hstmt = *hstmtDefault;
264
265} // wxDbTable::wxDbTable()
266
267
268/********** wxDbTable::~wxDbTable() **********/
269wxDbTable::~wxDbTable()
270{
271 wxString s;
272 if (pDb)
273 {
274 s.sprintf("wxDbTable destructor (%-20s) tableID:[%6lu] pDb:[%p]", tableName,tableID,pDb);
275 pDb->WriteSqlLog(s.c_str());
276 }
277
278#ifdef __WXDEBUG__
279 if (tableID)
280 {
281 TablesInUse.DeleteContents(TRUE);
282 bool found = FALSE;
283
284 wxNode *pNode;
285 pNode = TablesInUse.First();
286 while (pNode && !found)
287 {
288 if (((wxTablesInUse *)pNode->Data())->tableID == tableID)
289 {
290 found = TRUE;
291 if (!TablesInUse.DeleteNode(pNode))
292 wxLogDebug (s.c_str(),wxT("Unable to delete node!"));
293 }
294 else
295 pNode = pNode->Next();
296 }
297 if (!found)
298 {
299 wxString msg;
300 msg.sprintf(wxT("Unable to find the tableID in the linked\nlist of tables in use.\n\n%s"),s.c_str());
301 wxLogDebug (msg.c_str(),wxT("NOTICE..."));
302 }
303 }
304#endif
305
306 // Decrement the wxDb table count
307 if (pDb)
308 pDb->decrementTableCount();
309
310 // Delete memory allocated for column definitions
311 if (colDefs)
312 delete [] colDefs;
313
314 // Free statement handles
315 if (!queryOnly)
316 {
317 if (hstmtInsert)
318 if (SQLFreeStmt(hstmtInsert, SQL_DROP) != SQL_SUCCESS)
319 pDb->DispAllErrors(henv, hdbc);
320
321 if (hstmtDelete)
322 if (SQLFreeStmt(hstmtDelete, SQL_DROP) != SQL_SUCCESS)
323
324 if (hstmtUpdate)
325 if (SQLFreeStmt(hstmtUpdate, SQL_DROP) != SQL_SUCCESS)
326 pDb->DispAllErrors(henv, hdbc);
327 }
328
329 if (hstmtInternal)
330 if (SQLFreeStmt(hstmtInternal, SQL_DROP) != SQL_SUCCESS)
331 pDb->DispAllErrors(henv, hdbc);
332
333 // Delete dynamically allocated cursors
334 if (hstmtDefault)
335 DeleteCursor(hstmtDefault);
336
337 if (hstmtCount)
338 DeleteCursor(hstmtCount);
339
340} // wxDbTable::~wxDbTable()
341
342
343
344/***************************** PRIVATE FUNCTIONS *****************************/
345
346
347
348/********** wxDbTable::bindInsertParams() **********/
349bool wxDbTable::bindInsertParams(void)
350{
351 assert(!queryOnly);
352 if (queryOnly)
353 return(FALSE);
354
355 SWORD fSqlType = 0;
356 UDWORD precision = 0;
357 SWORD scale = 0;
358
359 // Bind each column (that can be inserted) of the table to a parameter marker
360 int i,colNo;
361 for (i = 0, colNo = 1; i < noCols; i++)
362 {
363 if (! colDefs[i].InsertAllowed)
364 continue;
365 switch(colDefs[i].DbDataType)
366 {
367 case DB_DATA_TYPE_VARCHAR:
368 fSqlType = pDb->GetTypeInfVarchar().FsqlType;
369 precision = colDefs[i].SzDataObj;
370 scale = 0;
371 if (colDefs[i].Null)
372 colDefs[i].CbValue = SQL_NULL_DATA;
373 else
374 colDefs[i].CbValue = SQL_NTS;
375 break;
376 case DB_DATA_TYPE_INTEGER:
377 fSqlType = pDb->GetTypeInfInteger().FsqlType;
378 precision = pDb->GetTypeInfInteger().Precision;
379 scale = 0;
380 if (colDefs[i].Null)
381 colDefs[i].CbValue = SQL_NULL_DATA;
382 else
383 colDefs[i].CbValue = 0;
384 break;
385 case DB_DATA_TYPE_FLOAT:
386 fSqlType = pDb->GetTypeInfFloat().FsqlType;
387 precision = pDb->GetTypeInfFloat().Precision;
388 scale = pDb->GetTypeInfFloat().MaximumScale;
389 // SQL Sybase Anywhere v5.5 returned a negative number for the
390 // MaxScale. This caused ODBC to kick out an error on ibscale.
391 // I check for this here and set the scale = precision.
392 //if (scale < 0)
393 // scale = (short) precision;
394 if (colDefs[i].Null)
395 colDefs[i].CbValue = SQL_NULL_DATA;
396 else
397 colDefs[i].CbValue = 0;
398 break;
399 case DB_DATA_TYPE_DATE:
400 fSqlType = pDb->GetTypeInfDate().FsqlType;
401 precision = pDb->GetTypeInfDate().Precision;
402 scale = 0;
403 if (colDefs[i].Null)
404 colDefs[i].CbValue = SQL_NULL_DATA;
405 else
406 colDefs[i].CbValue = 0;
407 break;
408 }
409 // Null values
410//RG-NULL
411//RG-NULL if (colDefs[i].Null)
412//RG-NULL {
413//RG-NULL colDefs[i].CbValue = SQL_NULL_DATA;
414//RG-NULL colDefs[i].Null = FALSE;
415//RG-NULL }
416
417 if (SQLBindParameter(hstmtInsert, colNo++, SQL_PARAM_INPUT, colDefs[i].SqlCtype,
418 fSqlType, precision, scale, (UCHAR*) colDefs[i].PtrDataObj,
419 precision+1,&colDefs[i].CbValue) != SQL_SUCCESS)
420 {
421 return(pDb->DispAllErrors(henv, hdbc, hstmtInsert));
422 }
423 }
424
425 // Completed successfully
426 return(TRUE);
427
428} // wxDbTable::bindInsertParams()
429
430
431/********** wxDbTable::bindUpdateParams() **********/
432bool wxDbTable::bindUpdateParams(void)
433{
434 assert(!queryOnly);
435 if (queryOnly)
436 return(FALSE);
437
438 SWORD fSqlType = 0;
439 UDWORD precision = 0;
440 SWORD scale = 0;
441
442 // Bind each UPDATEABLE column of the table to a parameter marker
443 int i,colNo;
444 for (i = 0, colNo = 1; i < noCols; i++)
445 {
446 if (! colDefs[i].Updateable)
447 continue;
448 switch(colDefs[i].DbDataType)
449 {
450 case DB_DATA_TYPE_VARCHAR:
451 fSqlType = pDb->GetTypeInfVarchar().FsqlType;
452 precision = colDefs[i].SzDataObj;
453 scale = 0;
454 if (colDefs[i].Null)
455 colDefs[i].CbValue = SQL_NULL_DATA;
456 else
457 colDefs[i].CbValue = SQL_NTS;
458 break;
459 case DB_DATA_TYPE_INTEGER:
460 fSqlType = pDb->GetTypeInfInteger().FsqlType;
461 precision = pDb->GetTypeInfInteger().Precision;
462 scale = 0;
463 if (colDefs[i].Null)
464 colDefs[i].CbValue = SQL_NULL_DATA;
465 else
466 colDefs[i].CbValue = 0;
467 break;
468 case DB_DATA_TYPE_FLOAT:
469 fSqlType = pDb->GetTypeInfFloat().FsqlType;
470 precision = pDb->GetTypeInfFloat().Precision;
471 scale = pDb->GetTypeInfFloat().MaximumScale;
472 // SQL Sybase Anywhere v5.5 returned a negative number for the
473 // MaxScale. This caused ODBC to kick out an error on ibscale.
474 // I check for this here and set the scale = precision.
475 //if (scale < 0)
476 // scale = (short) precision;
477 if (colDefs[i].Null)
478 colDefs[i].CbValue = SQL_NULL_DATA;
479 else
480 colDefs[i].CbValue = 0;
481 break;
482 case DB_DATA_TYPE_DATE:
483 fSqlType = pDb->GetTypeInfDate().FsqlType;
484 precision = pDb->GetTypeInfDate().Precision;
485 scale = 0;
486 if (colDefs[i].Null)
487 colDefs[i].CbValue = SQL_NULL_DATA;
488 else
489 colDefs[i].CbValue = 0;
490 break;
491 }
492
493 if (SQLBindParameter(hstmtUpdate, colNo++, SQL_PARAM_INPUT, colDefs[i].SqlCtype,
494 fSqlType, precision, scale, (UCHAR*) colDefs[i].PtrDataObj,
495 precision+1, &colDefs[i].CbValue) != SQL_SUCCESS)
496 {
497 return(pDb->DispAllErrors(henv, hdbc, hstmtUpdate));
498 }
499 }
500
501 // Completed successfully
502 return(TRUE);
503
504} // wxDbTable::bindUpdateParams()
505
506
507/********** wxDbTable::bindCols() **********/
508bool wxDbTable::bindCols(HSTMT cursor)
509{
510//RG-NULL static SDWORD cb;
511
512 // Bind each column of the table to a memory address for fetching data
513 int i;
514 for (i = 0; i < noCols; i++)
515 {
516 if (SQLBindCol(cursor, i+1, colDefs[i].SqlCtype, (UCHAR*) colDefs[i].PtrDataObj,
517//RG-NULL colDefs[i].SzDataObj, &cb) != SQL_SUCCESS)
518 colDefs[i].SzDataObj, &colDefs[i].CbValue ) != SQL_SUCCESS)
519 {
520 return (pDb->DispAllErrors(henv, hdbc, cursor));
521 }
522 }
523
524 // Completed successfully
525 return(TRUE);
526
527} // wxDbTable::bindCols()
528
529
530/********** wxDbTable::getRec() **********/
531bool wxDbTable::getRec(UWORD fetchType)
532{
533 RETCODE retcode;
534
535 if (!pDb->FwdOnlyCursors())
536 {
537 // Fetch the NEXT, PREV, FIRST or LAST record, depending on fetchType
538 UDWORD cRowsFetched;
539 UWORD rowStatus;
540
541 retcode = SQLExtendedFetch(hstmt, fetchType, 0, &cRowsFetched, &rowStatus);
542 if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
543 {
544 if (retcode == SQL_NO_DATA_FOUND)
545 return(FALSE);
546 else
547 return(pDb->DispAllErrors(henv, hdbc, hstmt));
548 }
549 else
550 {
551 // Set the Null member variable to indicate the Null state
552 // of each column just read in.
553 int i;
554 for (i = 0; i < noCols; i++)
555 colDefs[i].Null = (colDefs[i].CbValue == SQL_NULL_DATA);
556 }
557 }
558 else
559 {
560 // Fetch the next record from the record set
561 retcode = SQLFetch(hstmt);
562 if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
563 {
564 if (retcode == SQL_NO_DATA_FOUND)
565 return(FALSE);
566 else
567 return(pDb->DispAllErrors(henv, hdbc, hstmt));
568 }
569 else
570 {
571 // Set the Null member variable to indicate the Null state
572 // of each column just read in.
573 int i;
574 for (i = 0; i < noCols; i++)
575 colDefs[i].Null = (colDefs[i].CbValue == SQL_NULL_DATA);
576 }
577 }
578
579 // Completed successfully
580 return(TRUE);
581
582} // wxDbTable::getRec()
583
584
585/********** wxDbTable::execDelete() **********/
586bool wxDbTable::execDelete(const char *pSqlStmt)
587{
588 // Execute the DELETE statement
589 if (SQLExecDirect(hstmtDelete, (UCHAR FAR *) pSqlStmt, SQL_NTS) != SQL_SUCCESS)
590 return(pDb->DispAllErrors(henv, hdbc, hstmtDelete));
591
592 // Record deleted successfully
593 return(TRUE);
594
595} // wxDbTable::execDelete()
596
597
598/********** wxDbTable::execUpdate() **********/
599bool wxDbTable::execUpdate(const char *pSqlStmt)
600{
601 // Execute the UPDATE statement
602 if (SQLExecDirect(hstmtUpdate, (UCHAR FAR *) pSqlStmt, SQL_NTS) != SQL_SUCCESS)
603 return(pDb->DispAllErrors(henv, hdbc, hstmtUpdate));
604
605 // Record deleted successfully
606 return(TRUE);
607
608} // wxDbTable::execUpdate()
609
610
611/********** wxDbTable::query() **********/
612bool wxDbTable::query(int queryType, bool forUpdate, bool distinct, const char *pSqlStmt)
613{
614 char sqlStmt[DB_MAX_STATEMENT_LEN];
615
616 if (forUpdate)
617 // The user may wish to select for update, but the DBMS may not be capable
618 selectForUpdate = CanSelectForUpdate();
619 else
620 selectForUpdate = FALSE;
621
622 // Set the SQL SELECT string
623 if (queryType != DB_SELECT_STATEMENT) // A select statement was not passed in,
624 { // so generate a select statement.
625 BuildSelectStmt(sqlStmt, queryType, distinct);
626 pDb->WriteSqlLog(sqlStmt);
627 }
628/*
629 This is the block of code that got added during the 2.2.1 merge with
630 the 2.2 main branch that somehow got added here when it should not have. - gt
631
632 else
633 wxStrcpy(sqlStmt, pSqlStmt);
634
635 SQLFreeStmt(hstmt, SQL_CLOSE);
636 if (SQLExecDirect(hstmt, (UCHAR FAR *) sqlStmt, SQL_NTS) == SQL_SUCCESS)
637 return(TRUE);
638 else
639 {
640 pDb->DispAllErrors(henv, hdbc, hstmt);
641 return(FALSE);
642 }
643*/
644 // Make sure the cursor is closed first
645 if (!CloseCursor(hstmt))
646 return(FALSE);
647
648 // Execute the SQL SELECT statement
649 int retcode;
650 retcode = SQLExecDirect(hstmt, (UCHAR FAR *) (queryType == DB_SELECT_STATEMENT ? pSqlStmt : sqlStmt), SQL_NTS);
651 if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
652 return(pDb->DispAllErrors(henv, hdbc, hstmt));
653
654 // Completed successfully
655 return(TRUE);
656
657} // wxDbTable::query()
658
659
660/***************************** PUBLIC FUNCTIONS *****************************/
661
662
663/********** wxDbTable::Open() **********/
664bool wxDbTable::Open(bool checkPrivileges)
665{
666 if (!pDb)
667 return FALSE;
668
669 int i;
670 wxString sqlStmt;
671 wxString s;
672
673 s = "";
674 // Verify that the table exists in the database
675 if (!pDb->TableExists(tableName,/*pDb->GetUsername()*/NULL,tablePath))
676 {
677 s = "Table/view does not exist in the database";
678 if ( *(pDb->dbInf.accessibleTables) == 'Y')
679 s += ", or you have no permissions.\n";
680 else
681 s += ".\n";
682 }
683 else if (checkPrivileges)
684 {
685 // Verify the user has rights to access the table.
686 // Shortcut boolean evaluation to optimize out call to
687 // TablePrivileges
688 //
689 // Unfortunately this optimization doesn't seem to be
690 // reliable!
691 if (// *(pDb->dbInf.accessibleTables) == 'N' &&
692 !pDb->TablePrivileges(tableName,"SELECT",NULL,pDb->GetUsername(),tablePath))
693 s = "Current logged in user does not have sufficient privileges to access this table.\n";
694 }
695
696 if (!s.IsEmpty())
697 {
698 wxString p;
699
700 if (wxStrcmp(tablePath,""))
701 p.sprintf("Error opening '%s/%s'.\n",tablePath,tableName);
702 else
703 p.sprintf("Error opening '%s'.\n", tableName);
704
705 p += s;
706 pDb->LogError(p.GetData());
707
708 return(FALSE);
709 }
710
711 // Bind the member variables for field exchange between
712 // the wxDbTable object and the ODBC record.
713 if (!queryOnly)
714 {
715 if (!bindInsertParams()) // Inserts
716 return(FALSE);
717
718 if (!bindUpdateParams()) // Updates
719 return(FALSE);
720 }
721
722 if (!bindCols(*hstmtDefault)) // Selects
723 return(FALSE);
724
725 if (!bindCols(hstmtInternal)) // Internal use only
726 return(FALSE);
727
728 /*
729 * Do NOT bind the hstmtCount cursor!!!
730 */
731
732 // Build an insert statement using parameter markers
733 if (!queryOnly && noCols > 0)
734 {
735 bool needComma = FALSE;
736 sqlStmt.sprintf("INSERT INTO %s (", tableName);
737 for (i = 0; i < noCols; i++)
738 {
739 if (! colDefs[i].InsertAllowed)
740 continue;
741 if (needComma)
742 sqlStmt += ",";
743 sqlStmt += colDefs[i].ColName;
744 needComma = TRUE;
745 }
746 needComma = FALSE;
747 sqlStmt += ") VALUES (";
748
749 int insertableCount = 0;
750
751 for (i = 0; i < noCols; i++)
752 {
753 if (! colDefs[i].InsertAllowed)
754 continue;
755 if (needComma)
756 sqlStmt += ",";
757 sqlStmt += "?";
758 needComma = TRUE;
759 insertableCount++;
760 }
761 sqlStmt += ")";
762
763 // Prepare the insert statement for execution
764 if (insertableCount)
765 {
766 if (SQLPrepare(hstmtInsert, (UCHAR FAR *) sqlStmt.c_str(), SQL_NTS) != SQL_SUCCESS)
767 return(pDb->DispAllErrors(henv, hdbc, hstmtInsert));
768 }
769 else
770 insertable= FALSE;
771 }
772
773 // Completed successfully
774 return(TRUE);
775
776} // wxDbTable::Open()
777
778
779/********** wxDbTable::Query() **********/
780bool wxDbTable::Query(bool forUpdate, bool distinct)
781{
782
783 return(query(DB_SELECT_WHERE, forUpdate, distinct));
784
785} // wxDbTable::Query()
786
787
788/********** wxDbTable::QueryBySqlStmt() **********/
789bool wxDbTable::QueryBySqlStmt(const char *pSqlStmt)
790{
791 pDb->WriteSqlLog(pSqlStmt);
792
793 return(query(DB_SELECT_STATEMENT, FALSE, FALSE, pSqlStmt));
794
795} // wxDbTable::QueryBySqlStmt()
796
797
798/********** wxDbTable::QueryMatching() **********/
799bool wxDbTable::QueryMatching(bool forUpdate, bool distinct)
800{
801
802 return(query(DB_SELECT_MATCHING, forUpdate, distinct));
803
804} // wxDbTable::QueryMatching()
805
806
807/********** wxDbTable::QueryOnKeyFields() **********/
808bool wxDbTable::QueryOnKeyFields(bool forUpdate, bool distinct)
809{
810
811 return(query(DB_SELECT_KEYFIELDS, forUpdate, distinct));
812
813} // wxDbTable::QueryOnKeyFields()
814
815
816/********** wxDbTable::GetPrev() **********/
817bool wxDbTable::GetPrev(void)
818{
819 if (pDb->FwdOnlyCursors())
820 {
821 wxFAIL_MSG(wxT("GetPrev()::Backward scrolling cursors are not enabled for this instance of wxDbTable"));
822 return FALSE;
823 }
824 else
825 return(getRec(SQL_FETCH_PRIOR));
826
827} // wxDbTable::GetPrev()
828
829
830/********** wxDbTable::operator-- **********/
831bool wxDbTable::operator--(int)
832{
833 if (pDb->FwdOnlyCursors())
834 {
835 wxFAIL_MSG(wxT("operator--:Backward scrolling cursors are not enabled for this instance of wxDbTable"));
836 return FALSE;
837 }
838 else
839 return(getRec(SQL_FETCH_PRIOR));
840
841} // wxDbTable::operator--
842
843
844/********** wxDbTable::GetFirst() **********/
845bool wxDbTable::GetFirst(void)
846{
847 if (pDb->FwdOnlyCursors())
848 {
849 wxFAIL_MSG(wxT("GetFirst():Backward scrolling cursors are not enabled for this instance of wxDbTable"));
850 return FALSE;
851 }
852 else
853 return(getRec(SQL_FETCH_FIRST));
854
855} // wxDbTable::GetFirst()
856
857
858/********** wxDbTable::GetLast() **********/
859bool wxDbTable::GetLast(void)
860{
861 if (pDb->FwdOnlyCursors())
862 {
863 wxFAIL_MSG(wxT("GetLast()::Backward scrolling cursors are not enabled for this instance of wxDbTable"));
864 return FALSE;
865 }
866 else
867 return(getRec(SQL_FETCH_LAST));
868
869} // wxDbTable::GetLast()
870
871
872/********** wxDbTable::BuildSelectStmt() **********/
873void wxDbTable::BuildSelectStmt(char *pSqlStmt, int typeOfSelect, bool distinct)
874{
875 char whereClause[DB_MAX_WHERE_CLAUSE_LEN];
876
877 whereClause[0] = 0;
878
879 // Build a select statement to query the database
880 wxStrcpy(pSqlStmt, "SELECT ");
881
882 // SELECT DISTINCT values only?
883 if (distinct)
884 wxStrcat(pSqlStmt, "DISTINCT ");
885
886 // Was a FROM clause specified to join tables to the base table?
887 // Available for ::Query() only!!!
888 bool appendFromClause = FALSE;
889#if wxODBC_BACKWARD_COMPATABILITY
890 if (typeOfSelect == DB_SELECT_WHERE && from && wxStrlen(from))
891 appendFromClause = TRUE;
892#else
893 if (typeOfSelect == DB_SELECT_WHERE && from.Length())
894 appendFromClause = TRUE;
895#endif
896
897 // Add the column list
898 int i;
899 for (i = 0; i < noCols; i++)
900 {
901 // If joining tables, the base table column names must be qualified to avoid ambiguity
902 if (appendFromClause)
903 {
904 wxStrcat(pSqlStmt, queryTableName);
905 wxStrcat(pSqlStmt, ".");
906 }
907 wxStrcat(pSqlStmt, colDefs[i].ColName);
908 if (i + 1 < noCols)
909 wxStrcat(pSqlStmt, ",");
910 }
911
912 // If the datasource supports ROWID, get this column as well. Exception: Don't retrieve
913 // the ROWID if querying distinct records. The rowid will always be unique.
914 if (!distinct && CanUpdByROWID())
915 {
916 // If joining tables, the base table column names must be qualified to avoid ambiguity
917 if (appendFromClause)
918 {
919 wxStrcat(pSqlStmt, ",");
920 wxStrcat(pSqlStmt, queryTableName);
921 wxStrcat(pSqlStmt, ".ROWID");
922 }
923 else
924 wxStrcat(pSqlStmt, ",ROWID");
925 }
926
927 // Append the FROM tablename portion
928 wxStrcat(pSqlStmt, " FROM ");
929 wxStrcat(pSqlStmt, queryTableName);
930
931 // Sybase uses the HOLDLOCK keyword to lock a record during query.
932 // The HOLDLOCK keyword follows the table name in the from clause.
933 // Each table in the from clause must specify HOLDLOCK or
934 // NOHOLDLOCK (the default). Note: The "FOR UPDATE" clause
935 // is parsed but ignored in SYBASE Transact-SQL.
936 if (selectForUpdate && (pDb->Dbms() == dbmsSYBASE_ASA || pDb->Dbms() == dbmsSYBASE_ASE))
937 wxStrcat(pSqlStmt, " HOLDLOCK");
938
939 if (appendFromClause)
940 wxStrcat(pSqlStmt, from);
941
942 // Append the WHERE clause. Either append the where clause for the class
943 // or build a where clause. The typeOfSelect determines this.
944 switch(typeOfSelect)
945 {
946 case DB_SELECT_WHERE:
947#if wxODBC_BACKWARD_COMPATABILITY
948 if (where && wxStrlen(where)) // May not want a where clause!!!
949#else
950 if (where.Length()) // May not want a where clause!!!
951#endif
952 {
953 wxStrcat(pSqlStmt, " WHERE ");
954 wxStrcat(pSqlStmt, where);
955 }
956 break;
957 case DB_SELECT_KEYFIELDS:
958 BuildWhereClause(whereClause, DB_WHERE_KEYFIELDS);
959 if (wxStrlen(whereClause))
960 {
961 wxStrcat(pSqlStmt, " WHERE ");
962 wxStrcat(pSqlStmt, whereClause);
963 }
964 break;
965 case DB_SELECT_MATCHING:
966 BuildWhereClause(whereClause, DB_WHERE_MATCHING);
967 if (wxStrlen(whereClause))
968 {
969 wxStrcat(pSqlStmt, " WHERE ");
970 wxStrcat(pSqlStmt, whereClause);
971 }
972 break;
973 }
974
975 // Append the ORDER BY clause
976#if wxODBC_BACKWARD_COMPATABILITY
977 if (orderBy && wxStrlen(orderBy))
978#else
979 if (orderBy.Length())
980#endif
981 {
982 wxStrcat(pSqlStmt, " ORDER BY ");
983 wxStrcat(pSqlStmt, orderBy);
984 }
985
986 // SELECT FOR UPDATE if told to do so and the datasource is capable. Sybase
987 // parses the FOR UPDATE clause but ignores it. See the comment above on the
988 // HOLDLOCK for Sybase.
989 if (selectForUpdate && CanSelectForUpdate())
990 wxStrcat(pSqlStmt, " FOR UPDATE");
991
992} // wxDbTable::BuildSelectStmt()
993
994
995/********** wxDbTable::GetRowNum() **********/
996UWORD wxDbTable::GetRowNum(void)
997{
998 UDWORD rowNum;
999
1000 if (SQLGetStmtOption(hstmt, SQL_ROW_NUMBER, (UCHAR*) &rowNum) != SQL_SUCCESS)
1001 {
1002 pDb->DispAllErrors(henv, hdbc, hstmt);
1003 return(0);
1004 }
1005
1006 // Completed successfully
1007 return((UWORD) rowNum);
1008
1009} // wxDbTable::GetRowNum()
1010
1011
1012/********** wxDbTable::CloseCursor() **********/
1013bool wxDbTable::CloseCursor(HSTMT cursor)
1014{
1015 if (SQLFreeStmt(cursor, SQL_CLOSE) != SQL_SUCCESS)
1016 return(pDb->DispAllErrors(henv, hdbc, cursor));
1017
1018 // Completed successfully
1019 return(TRUE);
1020
1021} // wxDbTable::CloseCursor()
1022
1023
1024/********** wxDbTable::CreateTable() **********/
1025bool wxDbTable::CreateTable(bool attemptDrop)
1026{
1027 if (!pDb)
1028 return FALSE;
1029
1030 int i, j;
1031 wxString sqlStmt;
1032
1033#ifdef DBDEBUG_CONSOLE
1034 cout << "Creating Table " << tableName << "..." << endl;
1035#endif
1036
1037 // Drop table first
1038 if (attemptDrop && !DropTable())
1039 return FALSE;
1040
1041 // Create the table
1042#ifdef DBDEBUG_CONSOLE
1043 for (i = 0; i < noCols; i++)
1044 {
1045 // Exclude derived columns since they are NOT part of the base table
1046 if (colDefs[i].DerivedCol)
1047 continue;
1048 cout << i + 1 << ": " << colDefs[i].ColName << "; ";
1049 switch(colDefs[i].DbDataType)
1050 {
1051 case DB_DATA_TYPE_VARCHAR:
1052 cout << pDb->typeInfVarchar.TypeName << "(" << colDefs[i].SzDataObj << ")";
1053 break;
1054 case DB_DATA_TYPE_INTEGER:
1055 cout << pDb->typeInfInteger.TypeName;
1056 break;
1057 case DB_DATA_TYPE_FLOAT:
1058 cout << pDb->typeInfFloat.TypeName;
1059 break;
1060 case DB_DATA_TYPE_DATE:
1061 cout << pDb->typeInfDate.TypeName;
1062 break;
1063 }
1064 cout << endl;
1065 }
1066#endif
1067
1068 // Build a CREATE TABLE string from the colDefs structure.
1069 bool needComma = FALSE;
1070 sqlStmt.sprintf("CREATE TABLE %s (", tableName);
1071
1072 for (i = 0; i < noCols; i++)
1073 {
1074 // Exclude derived columns since they are NOT part of the base table
1075 if (colDefs[i].DerivedCol)
1076 continue;
1077 // Comma Delimiter
1078 if (needComma)
1079 sqlStmt += ",";
1080 // Column Name
1081 sqlStmt += colDefs[i].ColName;
1082 sqlStmt += " ";
1083 // Column Type
1084 switch(colDefs[i].DbDataType)
1085 {
1086 case DB_DATA_TYPE_VARCHAR:
1087 sqlStmt += pDb->GetTypeInfVarchar().TypeName;
1088 break;
1089 case DB_DATA_TYPE_INTEGER:
1090 sqlStmt += pDb->GetTypeInfInteger().TypeName;
1091 break;
1092 case DB_DATA_TYPE_FLOAT:
1093 sqlStmt += pDb->GetTypeInfFloat().TypeName;
1094 break;
1095 case DB_DATA_TYPE_DATE:
1096 sqlStmt += pDb->GetTypeInfDate().TypeName;
1097 break;
1098 }
1099 // For varchars, append the size of the string
1100 if (colDefs[i].DbDataType == DB_DATA_TYPE_VARCHAR)
1101 {
1102 wxString s;
1103 // wxStrcat(sqlStmt, "(");
1104 // wxStrcat(sqlStmt, itoa(colDefs[i].SzDataObj, s, 10));
1105 // wxStrcat(sqlStmt, ")");
1106 s.sprintf("(%d)", colDefs[i].SzDataObj);
1107 sqlStmt += s.c_str();
1108 }
1109
1110 if (pDb->Dbms() == dbmsDB2 ||
1111 pDb->Dbms() == dbmsMY_SQL ||
1112 pDb->Dbms() == dbmsSYBASE_ASE ||
1113 pDb->Dbms() == dbmsMS_SQL_SERVER)
1114 {
1115 if (colDefs[i].KeyField)
1116 {
1117 sqlStmt += " NOT NULL";
1118 }
1119 }
1120
1121 needComma = TRUE;
1122 }
1123 // If there is a primary key defined, include it in the create statement
1124 for (i = j = 0; i < noCols; i++)
1125 {
1126 if (colDefs[i].KeyField)
1127 {
1128 j++;
1129 break;
1130 }
1131 }
1132 if (j && pDb->Dbms() != dbmsDBASE) // Found a keyfield
1133 {
1134 if (pDb->Dbms() != dbmsMY_SQL)
1135 {
1136 sqlStmt += ",CONSTRAINT ";
1137 sqlStmt += tableName;
1138 sqlStmt += "_PIDX PRIMARY KEY (";
1139 }
1140 else
1141 {
1142 /* MySQL goes out on this one. We also declare the relevant key NON NULL above */
1143 sqlStmt += ", PRIMARY KEY (";
1144 }
1145
1146 // List column name(s) of column(s) comprising the primary key
1147 for (i = j = 0; i < noCols; i++)
1148 {
1149 if (colDefs[i].KeyField)
1150 {
1151 if (j++) // Multi part key, comma separate names
1152 sqlStmt += ",";
1153 sqlStmt += colDefs[i].ColName;
1154 }
1155 }
1156 sqlStmt += ")";
1157 }
1158 // Append the closing parentheses for the create table statement
1159 sqlStmt += ")";
1160
1161 pDb->WriteSqlLog(sqlStmt.c_str());
1162
1163#ifdef DBDEBUG_CONSOLE
1164 cout << endl << sqlStmt.c_str() << endl;
1165#endif
1166
1167 // Execute the CREATE TABLE statement
1168 RETCODE retcode = SQLExecDirect(hstmt, (UCHAR FAR *) sqlStmt.c_str(), SQL_NTS);
1169 if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
1170 {
1171 pDb->DispAllErrors(henv, hdbc, hstmt);
1172 pDb->RollbackTrans();
1173 CloseCursor(hstmt);
1174 return(FALSE);
1175 }
1176
1177 // Commit the transaction and close the cursor
1178 if (!pDb->CommitTrans())
1179 return(FALSE);
1180 if (!CloseCursor(hstmt))
1181 return(FALSE);
1182
1183 // Database table created successfully
1184 return(TRUE);
1185
1186} // wxDbTable::CreateTable()
1187
1188
1189/********** wxDbTable::DropTable() **********/
1190bool wxDbTable::DropTable()
1191{
1192 // NOTE: This function returns TRUE if the Table does not exist, but
1193 // only for identified databases. Code will need to be added
1194 // below for any other databases when those databases are defined
1195 // to handle this situation consistently
1196
1197 wxString sqlStmt;
1198
1199 sqlStmt.sprintf("DROP TABLE %s", tableName);
1200
1201 pDb->WriteSqlLog(sqlStmt.c_str());
1202
1203#ifdef DBDEBUG_CONSOLE
1204 cout << endl << sqlStmt.c_str() << endl;
1205#endif
1206
1207 if (SQLExecDirect(hstmt, (UCHAR FAR *) sqlStmt.c_str(), SQL_NTS) != SQL_SUCCESS)
1208 {
1209 // Check for "Base table not found" error and ignore
1210 pDb->GetNextError(henv, hdbc, hstmt);
1211 if (wxStrcmp(pDb->sqlState,"S0002") && wxStrcmp(pDb->sqlState, "S1000")) // "Base table not found"
1212 {
1213 // Check for product specific error codes
1214 if (!((pDb->Dbms() == dbmsSYBASE_ASA && !wxStrcmp(pDb->sqlState,"42000")) || // 5.x (and lower?)
1215 (pDb->Dbms() == dbmsSYBASE_ASE && !wxStrcmp(pDb->sqlState,"37000")) ||
1216 (pDb->Dbms() == dbmsPOSTGRES && !wxStrcmp(pDb->sqlState,"08S01"))))
1217 {
1218 pDb->DispNextError();
1219 pDb->DispAllErrors(henv, hdbc, hstmt);
1220 pDb->RollbackTrans();
1221 CloseCursor(hstmt);
1222 return(FALSE);
1223 }
1224 }
1225 }
1226
1227 // Commit the transaction and close the cursor
1228 if (! pDb->CommitTrans())
1229 return(FALSE);
1230 if (! CloseCursor(hstmt))
1231 return(FALSE);
1232
1233 return(TRUE);
1234} // wxDbTable::DropTable()
1235
1236
1237/********** wxDbTable::CreateIndex() **********/
1238bool wxDbTable::CreateIndex(const char * idxName, bool unique, int noIdxCols, wxDbIdxDef *pIdxDefs, bool attemptDrop)
1239{
1240 wxString sqlStmt;
1241
1242 // Drop the index first
1243 if (attemptDrop && !DropIndex(idxName))
1244 return (FALSE);
1245
1246 // MySQL (and possibly Sybase ASE?? - gt) require that any columns which are used as portions
1247 // of an index have the columns defined as "NOT NULL". During initial table creation though,
1248 // it may not be known which columns are necessarily going to be part of an index (e.g. the
1249 // table was created, then months later you determine that an additional index while
1250 // give better performance, so you want to add an index).
1251 //
1252 // The following block of code will modify the column definition to make the column be
1253 // defined with the "NOT NULL" qualifier.
1254 if (pDb->Dbms() == dbmsMY_SQL)
1255 {
1256 wxString sqlStmt;
1257 int i;
1258 bool ok = TRUE;
1259 for (i = 0; i < noIdxCols && ok; i++)
1260 {
1261 int j = 0;
1262 bool found = FALSE;
1263 // Find the column definition that has the ColName that matches the
1264 // index column name. We need to do this to get the DB_DATA_TYPE of
1265 // the index column, as MySQL's syntax for the ALTER column requires
1266 // this information
1267 while (!found && (j < this->noCols))
1268 {
1269 if (wxStrcmp(colDefs[j].ColName,pIdxDefs[i].ColName) == 0)
1270 found = TRUE;
1271 if (!found)
1272 j++;
1273 }
1274
1275 if (found)
1276 {
1277 wxString typeNameAndSize;
1278
1279 switch(colDefs[j].DbDataType)
1280 {
1281 case DB_DATA_TYPE_VARCHAR:
1282 typeNameAndSize = pDb->GetTypeInfVarchar().TypeName;
1283 break;
1284 case DB_DATA_TYPE_INTEGER:
1285 typeNameAndSize = pDb->GetTypeInfInteger().TypeName;
1286 break;
1287 case DB_DATA_TYPE_FLOAT:
1288 typeNameAndSize = pDb->GetTypeInfFloat().TypeName;
1289 break;
1290 case DB_DATA_TYPE_DATE:
1291 typeNameAndSize = pDb->GetTypeInfDate().TypeName;
1292 break;
1293 }
1294
1295 // For varchars, append the size of the string
1296 if (colDefs[j].DbDataType == DB_DATA_TYPE_VARCHAR)
1297 {
1298 wxString s;
1299 s.sprintf("(%d)", colDefs[i].SzDataObj);
1300 typeNameAndSize += s.c_str();
1301 }
1302
1303 sqlStmt.sprintf("ALTER TABLE %s MODIFY %s %s NOT NULL",tableName,pIdxDefs[i].ColName,typeNameAndSize.c_str());
1304 ok = pDb->ExecSql(sqlStmt.c_str());
1305
1306 if (!ok)
1307 {
1308 wxODBC_ERRORS retcode;
1309 // Oracle returns a DB_ERR_GENERAL_ERROR if the column is already
1310 // defined to be NOT NULL, but reportedly MySQL doesn't mind.
1311 // This line is just here for debug checking of the value
1312 retcode = (wxODBC_ERRORS)pDb->DB_STATUS;
1313 }
1314 }
1315 else
1316 ok = FALSE;
1317 }
1318 if (ok)
1319 pDb->CommitTrans();
1320 else
1321 {
1322 pDb->RollbackTrans();
1323 return(FALSE);
1324 }
1325 }
1326
1327 // Build a CREATE INDEX statement
1328 sqlStmt = "CREATE ";
1329 if (unique)
1330 sqlStmt += "UNIQUE ";
1331
1332 sqlStmt += "INDEX ";
1333 sqlStmt += idxName;
1334 sqlStmt += " ON ";
1335 sqlStmt += tableName;
1336 sqlStmt += " (";
1337
1338 // Append list of columns making up index
1339 int i;
1340 for (i = 0; i < noIdxCols; i++)
1341 {
1342 sqlStmt += pIdxDefs[i].ColName;
1343 /* Postgres doesn't cope with ASC */
1344 if (pDb->Dbms() != dbmsPOSTGRES)
1345 {
1346 if (pIdxDefs[i].Ascending)
1347 sqlStmt += " ASC";
1348 else
1349 sqlStmt += " DESC";
1350 }
1351
1352 if ((i + 1) < noIdxCols)
1353 sqlStmt += ",";
1354 }
1355
1356 // Append closing parentheses
1357 sqlStmt += ")";
1358
1359 pDb->WriteSqlLog(sqlStmt.c_str());
1360
1361#ifdef DBDEBUG_CONSOLE
1362 cout << endl << sqlStmt.c_str() << endl << endl;
1363#endif
1364
1365 // Execute the CREATE INDEX statement
1366 if (SQLExecDirect(hstmt, (UCHAR FAR *) sqlStmt.c_str(), SQL_NTS) != SQL_SUCCESS)
1367 {
1368 pDb->DispAllErrors(henv, hdbc, hstmt);
1369 pDb->RollbackTrans();
1370 CloseCursor(hstmt);
1371 return(FALSE);
1372 }
1373
1374 // Commit the transaction and close the cursor
1375 if (! pDb->CommitTrans())
1376 return(FALSE);
1377 if (! CloseCursor(hstmt))
1378 return(FALSE);
1379
1380 // Index Created Successfully
1381 return(TRUE);
1382
1383} // wxDbTable::CreateIndex()
1384
1385
1386/********** wxDbTable::DropIndex() **********/
1387bool wxDbTable::DropIndex(const char * idxName)
1388{
1389 // NOTE: This function returns TRUE if the Index does not exist, but
1390 // only for identified databases. Code will need to be added
1391 // below for any other databases when those databases are defined
1392 // to handle this situation consistently
1393
1394 wxString sqlStmt;
1395
1396 if (pDb->Dbms() == dbmsACCESS || pDb->Dbms() == dbmsMY_SQL)
1397 sqlStmt.sprintf("DROP INDEX %s ON %s",idxName,tableName);
1398 else if ((pDb->Dbms() == dbmsMS_SQL_SERVER) ||
1399 (pDb->Dbms() == dbmsSYBASE_ASE))
1400 sqlStmt.sprintf("DROP INDEX %s.%s",tableName,idxName);
1401 else
1402 sqlStmt.sprintf("DROP INDEX %s",idxName);
1403
1404 pDb->WriteSqlLog(sqlStmt.c_str());
1405
1406#ifdef DBDEBUG_CONSOLE
1407 cout << endl << sqlStmt.c_str() << endl;
1408#endif
1409
1410 if (SQLExecDirect(hstmt, (UCHAR FAR *) sqlStmt.c_str(), SQL_NTS) != SQL_SUCCESS)
1411 {
1412 // Check for "Index not found" error and ignore
1413 pDb->GetNextError(henv, hdbc, hstmt);
1414 if (wxStrcmp(pDb->sqlState,"S0012")) // "Index not found"
1415 {
1416 // Check for product specific error codes
1417 if (!((pDb->Dbms() == dbmsSYBASE_ASA && !wxStrcmp(pDb->sqlState,"42000")) || // v5.x (and lower?)
1418 (pDb->Dbms() == dbmsSYBASE_ASE && !wxStrcmp(pDb->sqlState,"37000")) ||
1419 (pDb->Dbms() == dbmsMS_SQL_SERVER && !wxStrcmp(pDb->sqlState,"S1000")) ||
1420 (pDb->Dbms() == dbmsSYBASE_ASE && !wxStrcmp(pDb->sqlState,"S0002")) || // Base table not found
1421 (pDb->Dbms() == dbmsMY_SQL && !wxStrcmp(pDb->sqlState,"42S12")) || // tested by Christopher Ludwik Marino-Cebulski using v3.23.21beta
1422 (pDb->Dbms() == dbmsPOSTGRES && !wxStrcmp(pDb->sqlState,"08S01"))
1423 ))
1424 {
1425 pDb->DispNextError();
1426 pDb->DispAllErrors(henv, hdbc, hstmt);
1427 pDb->RollbackTrans();
1428 CloseCursor(hstmt);
1429 return(FALSE);
1430 }
1431 }
1432 }
1433
1434 // Commit the transaction and close the cursor
1435 if (! pDb->CommitTrans())
1436 return(FALSE);
1437 if (! CloseCursor(hstmt))
1438 return(FALSE);
1439
1440 return(TRUE);
1441} // wxDbTable::DropIndex()
1442
1443
1444/********** wxDbTable::SetOrderByColNums() **********/
1445bool wxDbTable::SetOrderByColNums(int first, ... )
1446{
1447 int colNo = first;
1448 va_list argptr;
1449
1450 bool abort = FALSE;
1451 wxString tempStr;
1452
1453 va_start(argptr, first); /* Initialize variable arguments. */
1454 while (!abort && (colNo != wxDB_NO_MORE_COLUMN_NUMBERS))
1455 {
1456 // Make sure the passed in column number
1457 // is within the valid range of columns
1458 //
1459 // Valid columns are 0 thru noCols-1
1460 if (colNo >= noCols || colNo < 0)
1461 {
1462 abort = TRUE;
1463 continue;
1464 }
1465
1466 if (colNo != first)
1467 tempStr += ",";
1468
1469 tempStr += colDefs[colNo].ColName;
1470 colNo = va_arg (argptr, int);
1471 }
1472 va_end (argptr); /* Reset variable arguments. */
1473
1474 SetOrderByClause(tempStr.c_str());
1475
1476 return (!abort);
1477} // wxDbTable::SetOrderByColNums()
1478
1479
1480/********** wxDbTable::Insert() **********/
1481int wxDbTable::Insert(void)
1482{
1483 assert(!queryOnly);
1484 if (queryOnly || !insertable)
1485 return(DB_FAILURE);
1486
1487 bindInsertParams();
1488
1489 // Insert the record by executing the already prepared insert statement
1490 RETCODE retcode;
1491 retcode=SQLExecute(hstmtInsert);
1492 if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
1493 {
1494 // Check to see if integrity constraint was violated
1495 pDb->GetNextError(henv, hdbc, hstmtInsert);
1496 if (! wxStrcmp(pDb->sqlState, "23000")) // Integrity constraint violated
1497 return(DB_ERR_INTEGRITY_CONSTRAINT_VIOL);
1498 else
1499 {
1500 pDb->DispNextError();
1501 pDb->DispAllErrors(henv, hdbc, hstmtInsert);
1502 return(DB_FAILURE);
1503 }
1504 }
1505
1506 // Record inserted into the datasource successfully
1507 return(DB_SUCCESS);
1508
1509} // wxDbTable::Insert()
1510
1511
1512/********** wxDbTable::Update() **********/
1513bool wxDbTable::Update(void)
1514{
1515 assert(!queryOnly);
1516 if (queryOnly)
1517 return(FALSE);
1518
1519 char sqlStmt[DB_MAX_STATEMENT_LEN];
1520
1521 // Build the SQL UPDATE statement
1522 BuildUpdateStmt(sqlStmt, DB_UPD_KEYFIELDS);
1523
1524 pDb->WriteSqlLog(sqlStmt);
1525
1526#ifdef DBDEBUG_CONSOLE
1527 cout << endl << sqlStmt << endl << endl;
1528#endif
1529
1530 // Execute the SQL UPDATE statement
1531 return(execUpdate(sqlStmt));
1532
1533} // wxDbTable::Update()
1534
1535
1536/********** wxDbTable::Update(pSqlStmt) **********/
1537bool wxDbTable::Update(const char *pSqlStmt)
1538{
1539 assert(!queryOnly);
1540 if (queryOnly)
1541 return(FALSE);
1542
1543 pDb->WriteSqlLog(pSqlStmt);
1544
1545 return(execUpdate(pSqlStmt));
1546
1547} // wxDbTable::Update(pSqlStmt)
1548
1549
1550/********** wxDbTable::UpdateWhere() **********/
1551bool wxDbTable::UpdateWhere(const char *pWhereClause)
1552{
1553 assert(!queryOnly);
1554 if (queryOnly)
1555 return(FALSE);
1556
1557 char sqlStmt[DB_MAX_STATEMENT_LEN];
1558
1559 // Build the SQL UPDATE statement
1560 BuildUpdateStmt(sqlStmt, DB_UPD_WHERE, pWhereClause);
1561
1562 pDb->WriteSqlLog(sqlStmt);
1563
1564#ifdef DBDEBUG_CONSOLE
1565 cout << endl << sqlStmt << endl << endl;
1566#endif
1567
1568 // Execute the SQL UPDATE statement
1569 return(execUpdate(sqlStmt));
1570
1571} // wxDbTable::UpdateWhere()
1572
1573
1574/********** wxDbTable::Delete() **********/
1575bool wxDbTable::Delete(void)
1576{
1577 assert(!queryOnly);
1578 if (queryOnly)
1579 return(FALSE);
1580
1581 char sqlStmt[DB_MAX_STATEMENT_LEN];
1582
1583 // Build the SQL DELETE statement
1584 BuildDeleteStmt(sqlStmt, DB_DEL_KEYFIELDS);
1585
1586 pDb->WriteSqlLog(sqlStmt);
1587
1588 // Execute the SQL DELETE statement
1589 return(execDelete(sqlStmt));
1590
1591} // wxDbTable::Delete()
1592
1593
1594/********** wxDbTable::DeleteWhere() **********/
1595bool wxDbTable::DeleteWhere(const char *pWhereClause)
1596{
1597 assert(!queryOnly);
1598 if (queryOnly)
1599 return(FALSE);
1600
1601 char sqlStmt[DB_MAX_STATEMENT_LEN];
1602
1603 // Build the SQL DELETE statement
1604 BuildDeleteStmt(sqlStmt, DB_DEL_WHERE, pWhereClause);
1605
1606 pDb->WriteSqlLog(sqlStmt);
1607
1608 // Execute the SQL DELETE statement
1609 return(execDelete(sqlStmt));
1610
1611} // wxDbTable::DeleteWhere()
1612
1613
1614/********** wxDbTable::DeleteMatching() **********/
1615bool wxDbTable::DeleteMatching(void)
1616{
1617 assert(!queryOnly);
1618 if (queryOnly)
1619 return(FALSE);
1620
1621 char sqlStmt[DB_MAX_STATEMENT_LEN];
1622
1623 // Build the SQL DELETE statement
1624 BuildDeleteStmt(sqlStmt, DB_DEL_MATCHING);
1625
1626 pDb->WriteSqlLog(sqlStmt);
1627
1628 // Execute the SQL DELETE statement
1629 return(execDelete(sqlStmt));
1630
1631} // wxDbTable::DeleteMatching()
1632
1633
1634/********** wxDbTable::BuildUpdateStmt() **********/
1635void wxDbTable::BuildUpdateStmt(char *pSqlStmt, int typeOfUpd, const char *pWhereClause)
1636{
1637 assert(!queryOnly);
1638 if (queryOnly)
1639 return;
1640
1641 char whereClause[DB_MAX_WHERE_CLAUSE_LEN];
1642 bool firstColumn = TRUE;
1643
1644 whereClause[0] = 0;
1645 sprintf(pSqlStmt, "UPDATE %s SET ", tableName);
1646
1647 // Append a list of columns to be updated
1648 int i;
1649 for (i = 0; i < noCols; i++)
1650 {
1651 // Only append Updateable columns
1652 if (colDefs[i].Updateable)
1653 {
1654 if (! firstColumn)
1655 wxStrcat(pSqlStmt, ",");
1656 else
1657 firstColumn = FALSE;
1658 wxStrcat(pSqlStmt, colDefs[i].ColName);
1659 wxStrcat(pSqlStmt, " = ?");
1660 }
1661 }
1662
1663 // Append the WHERE clause to the SQL UPDATE statement
1664 wxStrcat(pSqlStmt, " WHERE ");
1665 switch(typeOfUpd)
1666 {
1667 case DB_UPD_KEYFIELDS:
1668 // If the datasource supports the ROWID column, build
1669 // the where on ROWID for efficiency purposes.
1670 // e.g. UPDATE PARTS SET Col1 = ?, Col2 = ? WHERE ROWID = '111.222.333'
1671 if (CanUpdByROWID())
1672 {
1673 SDWORD cb;
1674 char rowid[wxDB_ROWID_LEN];
1675
1676 // Get the ROWID value. If not successful retreiving the ROWID,
1677 // simply fall down through the code and build the WHERE clause
1678 // based on the key fields.
1679 if (SQLGetData(hstmt, noCols+1, SQL_C_CHAR, (UCHAR*) rowid, wxDB_ROWID_LEN, &cb) == SQL_SUCCESS)
1680 {
1681 wxStrcat(pSqlStmt, "ROWID = '");
1682 wxStrcat(pSqlStmt, rowid);
1683 wxStrcat(pSqlStmt, "'");
1684 break;
1685 }
1686 }
1687 // Unable to delete by ROWID, so build a WHERE
1688 // clause based on the keyfields.
1689 BuildWhereClause(whereClause, DB_WHERE_KEYFIELDS);
1690 wxStrcat(pSqlStmt, whereClause);
1691 break;
1692 case DB_UPD_WHERE:
1693 wxStrcat(pSqlStmt, pWhereClause);
1694 break;
1695 }
1696} // BuildUpdateStmt()
1697
1698
1699/********** wxDbTable::BuildDeleteStmt() **********/
1700void wxDbTable::BuildDeleteStmt(char *pSqlStmt, int typeOfDel, const char *pWhereClause)
1701{
1702 assert(!queryOnly);
1703 if (queryOnly)
1704 return;
1705
1706 char whereClause[DB_MAX_WHERE_CLAUSE_LEN];
1707
1708 whereClause[0] = 0;
1709
1710 // Handle the case of DeleteWhere() and the where clause is blank. It should
1711 // delete all records from the database in this case.
1712 if (typeOfDel == DB_DEL_WHERE && (pWhereClause == 0 || wxStrlen(pWhereClause) == 0))
1713 {
1714 sprintf(pSqlStmt, "DELETE FROM %s", tableName);
1715 return;
1716 }
1717
1718 sprintf(pSqlStmt, "DELETE FROM %s WHERE ", tableName);
1719
1720 // Append the WHERE clause to the SQL DELETE statement
1721 switch(typeOfDel)
1722 {
1723 case DB_DEL_KEYFIELDS:
1724 // If the datasource supports the ROWID column, build
1725 // the where on ROWID for efficiency purposes.
1726 // e.g. DELETE FROM PARTS WHERE ROWID = '111.222.333'
1727 if (CanUpdByROWID())
1728 {
1729 SDWORD cb;
1730 char rowid[wxDB_ROWID_LEN];
1731
1732 // Get the ROWID value. If not successful retreiving the ROWID,
1733 // simply fall down through the code and build the WHERE clause
1734 // based on the key fields.
1735 if (SQLGetData(hstmt, noCols+1, SQL_C_CHAR, (UCHAR*) rowid, wxDB_ROWID_LEN, &cb) == SQL_SUCCESS)
1736 {
1737 wxStrcat(pSqlStmt, "ROWID = '");
1738 wxStrcat(pSqlStmt, rowid);
1739 wxStrcat(pSqlStmt, "'");
1740 break;
1741 }
1742 }
1743 // Unable to delete by ROWID, so build a WHERE
1744 // clause based on the keyfields.
1745 BuildWhereClause(whereClause, DB_WHERE_KEYFIELDS);
1746 wxStrcat(pSqlStmt, whereClause);
1747 break;
1748 case DB_DEL_WHERE:
1749 wxStrcat(pSqlStmt, pWhereClause);
1750 break;
1751 case DB_DEL_MATCHING:
1752 BuildWhereClause(whereClause, DB_WHERE_MATCHING);
1753 wxStrcat(pSqlStmt, whereClause);
1754 break;
1755 }
1756
1757} // BuildDeleteStmt()
1758
1759
1760/********** wxDbTable::BuildWhereClause() **********/
1761void wxDbTable::BuildWhereClause(char *pWhereClause, int typeOfWhere,
1762 const char *qualTableName, bool useLikeComparison)
1763/*
1764 * Note: BuildWhereClause() currently ignores timestamp columns.
1765 * They are not included as part of the where clause.
1766 */
1767{
1768 bool moreThanOneColumn = FALSE;
1769 char colValue[255];
1770
1771 // Loop through the columns building a where clause as you go
1772 int i;
1773 for (i = 0; i < noCols; i++)
1774 {
1775 // Determine if this column should be included in the WHERE clause
1776 if ((typeOfWhere == DB_WHERE_KEYFIELDS && colDefs[i].KeyField) ||
1777 (typeOfWhere == DB_WHERE_MATCHING && (!IsColNull(i))))
1778 {
1779 // Skip over timestamp columns
1780 if (colDefs[i].SqlCtype == SQL_C_TIMESTAMP)
1781 continue;
1782 // If there is more than 1 column, join them with the keyword "AND"
1783 if (moreThanOneColumn)
1784 wxStrcat(pWhereClause, " AND ");
1785 else
1786 moreThanOneColumn = TRUE;
1787 // Concatenate where phrase for the column
1788 if (qualTableName && wxStrlen(qualTableName))
1789 {
1790 wxStrcat(pWhereClause, qualTableName);
1791 wxStrcat(pWhereClause, ".");
1792 }
1793 wxStrcat(pWhereClause, colDefs[i].ColName);
1794 if (useLikeComparison && (colDefs[i].SqlCtype == SQL_C_CHAR))
1795 wxStrcat(pWhereClause, " LIKE ");
1796 else
1797 wxStrcat(pWhereClause, " = ");
1798 switch(colDefs[i].SqlCtype)
1799 {
1800 case SQL_C_CHAR:
1801 sprintf(colValue, "'%s'", (UCHAR FAR *) colDefs[i].PtrDataObj);
1802 break;
1803 case SQL_C_SSHORT:
1804 sprintf(colValue, "%hi", *((SWORD *) colDefs[i].PtrDataObj));
1805 break;
1806 case SQL_C_USHORT:
1807 sprintf(colValue, "%hu", *((UWORD *) colDefs[i].PtrDataObj));
1808 break;
1809 case SQL_C_SLONG:
1810 sprintf(colValue, "%li", *((SDWORD *) colDefs[i].PtrDataObj));
1811 break;
1812 case SQL_C_ULONG:
1813 sprintf(colValue, "%lu", *((UDWORD *) colDefs[i].PtrDataObj));
1814 break;
1815 case SQL_C_FLOAT:
1816 sprintf(colValue, "%.6f", *((SFLOAT *) colDefs[i].PtrDataObj));
1817 break;
1818 case SQL_C_DOUBLE:
1819 sprintf(colValue, "%.6f", *((SDOUBLE *) colDefs[i].PtrDataObj));
1820 break;
1821 }
1822 wxStrcat(pWhereClause, colValue);
1823 }
1824 }
1825} // wxDbTable::BuildWhereClause()
1826
1827
1828/********** wxDbTable::IsColNull() **********/
1829bool wxDbTable::IsColNull(int colNo)
1830{
1831/*
1832 This logic is just not right. It would indicate TRUE
1833 if a numeric field were set to a value of 0.
1834
1835 switch(colDefs[colNo].SqlCtype)
1836 {
1837 case SQL_C_CHAR:
1838 return(((UCHAR FAR *) colDefs[colNo].PtrDataObj)[0] == 0);
1839 case SQL_C_SSHORT:
1840 return(( *((SWORD *) colDefs[colNo].PtrDataObj)) == 0);
1841 case SQL_C_USHORT:
1842 return(( *((UWORD*) colDefs[colNo].PtrDataObj)) == 0);
1843 case SQL_C_SLONG:
1844 return(( *((SDWORD *) colDefs[colNo].PtrDataObj)) == 0);
1845 case SQL_C_ULONG:
1846 return(( *((UDWORD *) colDefs[colNo].PtrDataObj)) == 0);
1847 case SQL_C_FLOAT:
1848 return(( *((SFLOAT *) colDefs[colNo].PtrDataObj)) == 0);
1849 case SQL_C_DOUBLE:
1850 return((*((SDOUBLE *) colDefs[colNo].PtrDataObj)) == 0);
1851 case SQL_C_TIMESTAMP:
1852 TIMESTAMP_STRUCT *pDt;
1853 pDt = (TIMESTAMP_STRUCT *) colDefs[colNo].PtrDataObj;
1854 if (pDt->year == 0 && pDt->month == 0 && pDt->day == 0)
1855 return(TRUE);
1856 else
1857 return(FALSE);
1858 default:
1859 return(TRUE);
1860 }
1861*/
1862 return (colDefs[colNo].Null);
1863} // wxDbTable::IsColNull()
1864
1865
1866/********** wxDbTable::CanSelectForUpdate() **********/
1867bool wxDbTable::CanSelectForUpdate(void)
1868{
1869 if (queryOnly)
1870 return FALSE;
1871
1872 if (pDb->Dbms() == dbmsMY_SQL)
1873 return FALSE;
1874
1875 if ((pDb->Dbms() == dbmsORACLE) ||
1876 (pDb->dbInf.posStmts & SQL_PS_SELECT_FOR_UPDATE))
1877 return(TRUE);
1878 else
1879 return(FALSE);
1880
1881} // wxDbTable::CanSelectForUpdate()
1882
1883
1884/********** wxDbTable::CanUpdByROWID() **********/
1885bool wxDbTable::CanUpdByROWID(void)
1886{
1887/*
1888 * NOTE: Returning FALSE for now until this can be debugged,
1889 * as the ROWID is not getting updated correctly
1890 */
1891 return FALSE;
1892
1893 if (pDb->Dbms() == dbmsORACLE)
1894 return(TRUE);
1895 else
1896 return(FALSE);
1897
1898} // wxDbTable::CanUpdByROWID()
1899
1900
1901/********** wxDbTable::IsCursorClosedOnCommit() **********/
1902bool wxDbTable::IsCursorClosedOnCommit(void)
1903{
1904 if (pDb->dbInf.cursorCommitBehavior == SQL_CB_PRESERVE)
1905 return(FALSE);
1906 else
1907 return(TRUE);
1908
1909} // wxDbTable::IsCursorClosedOnCommit()
1910
1911
1912
1913/********** wxDbTable::ClearMemberVar() **********/
1914void wxDbTable::ClearMemberVar(int colNo, bool setToNull)
1915{
1916 assert(colNo < noCols);
1917
1918 switch(colDefs[colNo].SqlCtype)
1919 {
1920 case SQL_C_CHAR:
1921 ((UCHAR FAR *) colDefs[colNo].PtrDataObj)[0] = 0;
1922 break;
1923 case SQL_C_SSHORT:
1924 *((SWORD *) colDefs[colNo].PtrDataObj) = 0;
1925 break;
1926 case SQL_C_USHORT:
1927 *((UWORD*) colDefs[colNo].PtrDataObj) = 0;
1928 break;
1929 case SQL_C_SLONG:
1930 *((SDWORD *) colDefs[colNo].PtrDataObj) = 0;
1931 break;
1932 case SQL_C_ULONG:
1933 *((UDWORD *) colDefs[colNo].PtrDataObj) = 0;
1934 break;
1935 case SQL_C_FLOAT:
1936 *((SFLOAT *) colDefs[colNo].PtrDataObj) = 0.0f;
1937 break;
1938 case SQL_C_DOUBLE:
1939 *((SDOUBLE *) colDefs[colNo].PtrDataObj) = 0.0f;
1940 break;
1941 case SQL_C_TIMESTAMP:
1942 TIMESTAMP_STRUCT *pDt;
1943 pDt = (TIMESTAMP_STRUCT *) colDefs[colNo].PtrDataObj;
1944 pDt->year = 0;
1945 pDt->month = 0;
1946 pDt->day = 0;
1947 pDt->hour = 0;
1948 pDt->minute = 0;
1949 pDt->second = 0;
1950 pDt->fraction = 0;
1951 break;
1952 }
1953
1954 if (setToNull)
1955 SetColNull(colNo);
1956} // wxDbTable::ClearMemberVar()
1957
1958
1959/********** wxDbTable::ClearMemberVars() **********/
1960void wxDbTable::ClearMemberVars(bool setToNull)
1961{
1962 int i;
1963
1964 // Loop through the columns setting each member variable to zero
1965 for (i=0; i < noCols; i++)
1966 ClearMemberVar(i,setToNull);
1967
1968} // wxDbTable::ClearMemberVars()
1969
1970
1971/********** wxDbTable::SetQueryTimeout() **********/
1972bool wxDbTable::SetQueryTimeout(UDWORD nSeconds)
1973{
1974 if (SQLSetStmtOption(hstmtInsert, SQL_QUERY_TIMEOUT, nSeconds) != SQL_SUCCESS)
1975 return(pDb->DispAllErrors(henv, hdbc, hstmtInsert));
1976 if (SQLSetStmtOption(hstmtUpdate, SQL_QUERY_TIMEOUT, nSeconds) != SQL_SUCCESS)
1977 return(pDb->DispAllErrors(henv, hdbc, hstmtUpdate));
1978 if (SQLSetStmtOption(hstmtDelete, SQL_QUERY_TIMEOUT, nSeconds) != SQL_SUCCESS)
1979 return(pDb->DispAllErrors(henv, hdbc, hstmtDelete));
1980 if (SQLSetStmtOption(hstmtInternal, SQL_QUERY_TIMEOUT, nSeconds) != SQL_SUCCESS)
1981 return(pDb->DispAllErrors(henv, hdbc, hstmtInternal));
1982
1983 // Completed Successfully
1984 return(TRUE);
1985
1986} // wxDbTable::SetQueryTimeout()
1987
1988
1989/********** wxDbTable::SetColDefs() **********/
1990void wxDbTable::SetColDefs(int index, const char *fieldName, int dataType, void *pData,
1991 int cType, int size, bool keyField, bool upd,
1992 bool insAllow, bool derivedCol)
1993{
1994 if (!colDefs) // May happen if the database connection fails
1995 return;
1996
1997 if (wxStrlen(fieldName) > (unsigned int) DB_MAX_COLUMN_NAME_LEN)
1998 {
1999 wxStrncpy (colDefs[index].ColName, fieldName, DB_MAX_COLUMN_NAME_LEN);
2000 colDefs[index].ColName[DB_MAX_COLUMN_NAME_LEN] = 0;
2001 }
2002 else
2003 wxStrcpy(colDefs[index].ColName, fieldName);
2004
2005 colDefs[index].DbDataType = dataType;
2006 colDefs[index].PtrDataObj = pData;
2007 colDefs[index].SqlCtype = cType;
2008 colDefs[index].SzDataObj = size;
2009 colDefs[index].KeyField = keyField;
2010 colDefs[index].DerivedCol = derivedCol;
2011 // Derived columns by definition would NOT be "Insertable" or "Updateable"
2012 if (derivedCol)
2013 {
2014 colDefs[index].Updateable = FALSE;
2015 colDefs[index].InsertAllowed = FALSE;
2016 }
2017 else
2018 {
2019 colDefs[index].Updateable = upd;
2020 colDefs[index].InsertAllowed = insAllow;
2021 }
2022
2023 colDefs[index].Null = FALSE;
2024
2025} // wxDbTable::SetColDefs()
2026
2027
2028/********** wxDbTable::SetColDefs() **********/
2029wxDbColDataPtr* wxDbTable::SetColDefs(wxDbColInf *pColInfs, ULONG numCols)
2030{
2031 assert(pColInfs);
2032 wxDbColDataPtr *pColDataPtrs = NULL;
2033
2034 if (pColInfs)
2035 {
2036 ULONG index;
2037
2038 pColDataPtrs = new wxDbColDataPtr[numCols+1];
2039
2040 for (index = 0; index < numCols; index++)
2041 {
2042 // Process the fields
2043 switch (pColInfs[index].dbDataType)
2044 {
2045 case DB_DATA_TYPE_VARCHAR:
2046 pColDataPtrs[index].PtrDataObj = new char[pColInfs[index].bufferLength+1];
2047 pColDataPtrs[index].SzDataObj = pColInfs[index].columnSize;
2048 pColDataPtrs[index].SqlCtype = SQL_C_CHAR;
2049 break;
2050 case DB_DATA_TYPE_INTEGER:
2051 // Can be long or short
2052 if (pColInfs[index].bufferLength == sizeof(long))
2053 {
2054 pColDataPtrs[index].PtrDataObj = new long;
2055 pColDataPtrs[index].SzDataObj = sizeof(long);
2056 pColDataPtrs[index].SqlCtype = SQL_C_SLONG;
2057 }
2058 else
2059 {
2060 pColDataPtrs[index].PtrDataObj = new short;
2061 pColDataPtrs[index].SzDataObj = sizeof(short);
2062 pColDataPtrs[index].SqlCtype = SQL_C_SSHORT;
2063 }
2064 break;
2065 case DB_DATA_TYPE_FLOAT:
2066 // Can be float or double
2067 if (pColInfs[index].bufferLength == sizeof(float))
2068 {
2069 pColDataPtrs[index].PtrDataObj = new float;
2070 pColDataPtrs[index].SzDataObj = sizeof(float);
2071 pColDataPtrs[index].SqlCtype = SQL_C_FLOAT;
2072 }
2073 else
2074 {
2075 pColDataPtrs[index].PtrDataObj = new double;
2076 pColDataPtrs[index].SzDataObj = sizeof(double);
2077 pColDataPtrs[index].SqlCtype = SQL_C_DOUBLE;
2078 }
2079 break;
2080 case DB_DATA_TYPE_DATE:
2081 pColDataPtrs[index].PtrDataObj = new TIMESTAMP_STRUCT;
2082 pColDataPtrs[index].SzDataObj = sizeof(TIMESTAMP_STRUCT);
2083 pColDataPtrs[index].SqlCtype = SQL_C_TIMESTAMP;
2084 break;
2085 }
2086 SetColDefs (index,pColInfs[index].colName,pColInfs[index].dbDataType, pColDataPtrs[index].PtrDataObj, pColDataPtrs[index].SqlCtype, pColDataPtrs[index].SzDataObj);
2087 }
2088 }
2089
2090 return (pColDataPtrs);
2091
2092} // wxDbTable::SetColDefs()
2093
2094
2095/********** wxDbTable::SetCursor() **********/
2096void wxDbTable::SetCursor(HSTMT *hstmtActivate)
2097{
2098 if (hstmtActivate == wxDB_DEFAULT_CURSOR)
2099 hstmt = *hstmtDefault;
2100 else
2101 hstmt = *hstmtActivate;
2102
2103} // wxDbTable::SetCursor()
2104
2105
2106/********** wxDbTable::Count(const char *) **********/
2107ULONG wxDbTable::Count(const char *args)
2108{
2109 ULONG count;
2110 wxString sqlStmt;
2111 SDWORD cb;
2112
2113 // Build a "SELECT COUNT(*) FROM queryTableName [WHERE whereClause]" SQL Statement
2114 sqlStmt = "SELECT COUNT(";
2115 sqlStmt += args;
2116 sqlStmt += ") FROM ";
2117 sqlStmt += queryTableName;
2118#if wxODBC_BACKWARD_COMPATABILITY
2119 if (from && wxStrlen(from))
2120#else
2121 if (from.Length())
2122#endif
2123 sqlStmt += from;
2124
2125 // Add the where clause if one is provided
2126#if wxODBC_BACKWARD_COMPATABILITY
2127 if (where && wxStrlen(where))
2128#else
2129 if (where.Length())
2130#endif
2131 {
2132 sqlStmt += " WHERE ";
2133 sqlStmt += where;
2134 }
2135
2136 pDb->WriteSqlLog(sqlStmt.c_str());
2137
2138 // Initialize the Count cursor if it's not already initialized
2139 if (!hstmtCount)
2140 {
2141 hstmtCount = GetNewCursor(FALSE,FALSE);
2142 assert(hstmtCount);
2143 if (!hstmtCount)
2144 return(0);
2145 }
2146
2147 // Execute the SQL statement
2148 if (SQLExecDirect(*hstmtCount, (UCHAR FAR *) sqlStmt.c_str(), SQL_NTS) != SQL_SUCCESS)
2149 {
2150 pDb->DispAllErrors(henv, hdbc, *hstmtCount);
2151 return(0);
2152 }
2153
2154 // Fetch the record
2155 if (SQLFetch(*hstmtCount) != SQL_SUCCESS)
2156 {
2157 pDb->DispAllErrors(henv, hdbc, *hstmtCount);
2158 return(0);
2159 }
2160
2161 // Obtain the result
2162 if (SQLGetData(*hstmtCount, 1, SQL_C_ULONG, &count, sizeof(count), &cb) != SQL_SUCCESS)
2163 {
2164 pDb->DispAllErrors(henv, hdbc, *hstmtCount);
2165 return(0);
2166 }
2167
2168 // Free the cursor
2169 if (SQLFreeStmt(*hstmtCount, SQL_CLOSE) != SQL_SUCCESS)
2170 pDb->DispAllErrors(henv, hdbc, *hstmtCount);
2171
2172 // Return the record count
2173 return(count);
2174
2175} // wxDbTable::Count()
2176
2177
2178/********** wxDbTable::Refresh() **********/
2179bool wxDbTable::Refresh(void)
2180{
2181 bool result = TRUE;
2182
2183 // Switch to the internal cursor so any active cursors are not corrupted
2184 HSTMT currCursor = GetCursor();
2185 hstmt = hstmtInternal;
2186#if wxODBC_BACKWARD_COMPATABILITY
2187 // Save the where and order by clauses
2188 char *saveWhere = where;
2189 char *saveOrderBy = orderBy;
2190#else
2191 wxString saveWhere = where;
2192 wxString saveOrderBy = orderBy;
2193#endif
2194 // Build a where clause to refetch the record with. Try and use the
2195 // ROWID if it's available, ow use the key fields.
2196 char whereClause[DB_MAX_WHERE_CLAUSE_LEN+1];
2197 wxStrcpy(whereClause, "");
2198 if (CanUpdByROWID())
2199 {
2200 SDWORD cb;
2201 char rowid[wxDB_ROWID_LEN+1];
2202
2203 // Get the ROWID value. If not successful retreiving the ROWID,
2204 // simply fall down through the code and build the WHERE clause
2205 // based on the key fields.
2206 if (SQLGetData(hstmt, noCols+1, SQL_C_CHAR, (UCHAR*) rowid, wxDB_ROWID_LEN, &cb) == SQL_SUCCESS)
2207 {
2208 wxStrcat(whereClause, queryTableName);
2209 wxStrcat(whereClause, ".ROWID = '");
2210 wxStrcat(whereClause, rowid);
2211 wxStrcat(whereClause, "'");
2212 }
2213 }
2214
2215 // If unable to use the ROWID, build a where clause from the keyfields
2216 if (wxStrlen(whereClause) == 0)
2217 BuildWhereClause(whereClause, DB_WHERE_KEYFIELDS, queryTableName);
2218
2219 // Requery the record
2220 where = whereClause;
2221 orderBy = "";
2222 if (!Query())
2223 result = FALSE;
2224
2225 if (result && !GetNext())
2226 result = FALSE;
2227
2228 // Switch back to original cursor
2229 SetCursor(&currCursor);
2230
2231 // Free the internal cursor
2232 if (SQLFreeStmt(hstmtInternal, SQL_CLOSE) != SQL_SUCCESS)
2233 pDb->DispAllErrors(henv, hdbc, hstmtInternal);
2234
2235 // Restore the original where and order by clauses
2236 where = saveWhere;
2237 orderBy = saveOrderBy;
2238
2239 return(result);
2240
2241} // wxDbTable::Refresh()
2242
2243
2244/********** wxDbTable::SetColNull(int colNo, bool set) **********/
2245bool wxDbTable::SetColNull(int colNo, bool set)
2246{
2247 if (colNo < noCols)
2248 {
2249 colDefs[colNo].Null = set;
2250 if (set) // Blank out the values in the member variable
2251 ClearMemberVar(colNo,FALSE); // Must call with FALSE, or infinite recursion will happen
2252 return(TRUE);
2253 }
2254 else
2255 return(FALSE);
2256
2257} // wxDbTable::SetColNull(int colNo)
2258
2259
2260/********** wxDbTable::SetColNull(char *colName, bool set) **********/
2261bool wxDbTable::SetColNull(const char *colName, bool set)
2262{
2263 int i;
2264 for (i = 0; i < noCols; i++)
2265 {
2266 if (!wxStricmp(colName, colDefs[i].ColName))
2267 break;
2268 }
2269
2270 if (i < noCols)
2271 {
2272 colDefs[i].Null = set;
2273 if (set) // Blank out the values in the member variable
2274 ClearMemberVar(i,FALSE); // Must call with FALSE, or infinite recursion will happen
2275 return(TRUE);
2276 }
2277 else
2278 return(FALSE);
2279
2280} // wxDbTable::SetColNull(char *colName)
2281
2282
2283/********** wxDbTable::GetNewCursor() **********/
2284HSTMT *wxDbTable::GetNewCursor(bool setCursor, bool bindColumns)
2285{
2286 HSTMT *newHSTMT = new HSTMT;
2287 assert(newHSTMT);
2288 if (!newHSTMT)
2289 return(0);
2290
2291 if (SQLAllocStmt(hdbc, newHSTMT) != SQL_SUCCESS)
2292 {
2293 pDb->DispAllErrors(henv, hdbc);
2294 delete newHSTMT;
2295 return(0);
2296 }
2297
2298 if (SQLSetStmtOption(*newHSTMT, SQL_CURSOR_TYPE, cursorType) != SQL_SUCCESS)
2299 {
2300 pDb->DispAllErrors(henv, hdbc, *newHSTMT);
2301 delete newHSTMT;
2302 return(0);
2303 }
2304
2305 if (bindColumns)
2306 {
2307 if(!bindCols(*newHSTMT))
2308 {
2309 delete newHSTMT;
2310 return(0);
2311 }
2312 }
2313
2314 if (setCursor)
2315 SetCursor(newHSTMT);
2316
2317 return(newHSTMT);
2318
2319} // wxDbTable::GetNewCursor()
2320
2321
2322/********** wxDbTable::DeleteCursor() **********/
2323bool wxDbTable::DeleteCursor(HSTMT *hstmtDel)
2324{
2325 bool result = TRUE;
2326
2327 if (!hstmtDel) // Cursor already deleted
2328 return(result);
2329
2330 if (SQLFreeStmt(*hstmtDel, SQL_DROP) != SQL_SUCCESS)
2331 {
2332 pDb->DispAllErrors(henv, hdbc);
2333 result = FALSE;
2334 }
2335
2336 delete hstmtDel;
2337
2338 return(result);
2339
2340} // wxDbTable::DeleteCursor()
2341
2342#endif // wxUSE_ODBC
2343
wxWidgets (Impactor)