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