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