]> git.saurik.com Git - wxWidgets.git/blob - src/common/dbtable.cpp
projects / wxWidgets.git / blob
? search:


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