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


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