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