1 ///////////////////////////////////////////////////////////////////////////////
3 // Purpose: Implementation of the wxDbTable class.
5 // Modified by: George Tasker
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 ///////////////////////////////////////////////////////////////////////////////
28 // Use this line for wxWindows v1.x
30 // Use this line for wxWindows v2.x
31 #include "wx/wxprec.h"
32 #include "wx/version.h"
34 #if wxMAJOR_VERSION == 2
36 #pragma implementation "dbtable.h"
40 #ifdef DBDEBUG_CONSOLE
41 #include "wx/ioswrap.h"
49 #if wxMAJOR_VERSION == 2
51 #include "wx/string.h"
52 #include "wx/object.h"
55 #include "wx/msgdlg.h"
58 #include "wx/filefn.h"
61 #if wxMAJOR_VERSION == 1
62 # if defined(wx_msw) || defined(wx_x)
80 #if wxMAJOR_VERSION == 1
82 #elif wxMAJOR_VERSION == 2
83 #include "wx/dbtable.h"
87 // The HPUX preprocessor lines below were commented out on 8/20/97
88 // because macros.h currently redefines DEBUG and is unneeded.
90 // # include <macros.h>
93 # include <sys/minmax.h>
97 ULONG lastTableID
= 0;
105 /********** wxDbTable::wxDbTable() **********/
106 wxDbTable::wxDbTable(wxDb
*pwxDb
, const wxString
&tblName
, const int nCols
,
107 const wxString
&qryTblName
, bool qryOnly
, const wxString
&tblPath
)
109 if (!initialize(pwxDb
, tblName
, nCols
, qryTblName
, qryOnly
, tblPath
))
111 } // wxDbTable::wxDbTable()
114 /***** DEPRECATED: use wxDbTable::wxDbTable() format above *****/
115 wxDbTable::wxDbTable(wxDb
*pwxDb
, const wxString
&tblName
, const int nCols
,
116 const wxChar
*qryTblName
, bool qryOnly
, const wxString
&tblPath
)
118 wxString tempQryTblName
;
119 tempQryTblName
= qryTblName
;
120 if (!initialize(pwxDb
, tblName
, nCols
, tempQryTblName
, qryOnly
, tblPath
))
122 } // wxDbTable::wxDbTable()
125 /********** wxDbTable::~wxDbTable() **********/
126 wxDbTable::~wxDbTable()
129 } // wxDbTable::~wxDbTable()
132 bool wxDbTable::initialize(wxDb
*pwxDb
, const wxString
&tblName
, const int nCols
,
133 const wxString
&qryTblName
, bool qryOnly
, const wxString
&tblPath
)
135 // Initializing member variables
136 pDb
= pwxDb
; // Pointer to the wxDb object
140 hstmtDefault
= 0; // Initialized below
141 hstmtCount
= 0; // Initialized first time it is needed
148 noCols
= nCols
; // No. of cols in the table
149 where
.Empty(); // Where clause
150 orderBy
.Empty(); // Order By clause
151 from
.Empty(); // From clause
152 selectForUpdate
= FALSE
; // SELECT ... FOR UPDATE; Indicates whether to include the FOR UPDATE phrase
157 queryTableName
.Empty();
159 wxASSERT(tblName
.Length());
165 tableName
= tblName
; // Table Name
166 if (tblPath
.Length())
167 tablePath
= tblPath
; // Table Path - used for dBase files
171 if (qryTblName
.Length()) // Name of the table/view to query
172 queryTableName
= qryTblName
;
174 queryTableName
= tblName
;
176 pDb
->incrementTableCount();
179 tableID
= ++lastTableID
;
180 s
.Printf(wxT("wxDbTable constructor (%-20s) tableID:[%6lu] pDb:[%p]"), tblName
,tableID
,pDb
);
183 wxTablesInUse
*tableInUse
;
184 tableInUse
= new wxTablesInUse();
185 tableInUse
->tableName
= tblName
;
186 tableInUse
->tableID
= tableID
;
187 tableInUse
->pDb
= pDb
;
188 TablesInUse
.Append(tableInUse
);
193 // Grab the HENV and HDBC from the wxDb object
194 henv
= pDb
->GetHENV();
195 hdbc
= pDb
->GetHDBC();
197 // Allocate space for column definitions
199 colDefs
= new wxDbColDef
[noCols
]; // Points to the first column definition
201 // Allocate statement handles for the table
204 // Allocate a separate statement handle for performing inserts
205 if (SQLAllocStmt(hdbc
, &hstmtInsert
) != SQL_SUCCESS
)
206 pDb
->DispAllErrors(henv
, hdbc
);
207 // Allocate a separate statement handle for performing deletes
208 if (SQLAllocStmt(hdbc
, &hstmtDelete
) != SQL_SUCCESS
)
209 pDb
->DispAllErrors(henv
, hdbc
);
210 // Allocate a separate statement handle for performing updates
211 if (SQLAllocStmt(hdbc
, &hstmtUpdate
) != SQL_SUCCESS
)
212 pDb
->DispAllErrors(henv
, hdbc
);
214 // Allocate a separate statement handle for internal use
215 if (SQLAllocStmt(hdbc
, &hstmtInternal
) != SQL_SUCCESS
)
216 pDb
->DispAllErrors(henv
, hdbc
);
218 // Set the cursor type for the statement handles
219 cursorType
= SQL_CURSOR_STATIC
;
221 if (SQLSetStmtOption(hstmtInternal
, SQL_CURSOR_TYPE
, cursorType
) != SQL_SUCCESS
)
223 // Check to see if cursor type is supported
224 pDb
->GetNextError(henv
, hdbc
, hstmtInternal
);
225 if (! wxStrcmp(pDb
->sqlState
, wxT("01S02"))) // Option Value Changed
227 // Datasource does not support static cursors. Driver
228 // will substitute a cursor type. Call SQLGetStmtOption()
229 // to determine which cursor type was selected.
230 if (SQLGetStmtOption(hstmtInternal
, SQL_CURSOR_TYPE
, &cursorType
) != SQL_SUCCESS
)
231 pDb
->DispAllErrors(henv
, hdbc
, hstmtInternal
);
232 #ifdef DBDEBUG_CONSOLE
233 cout
<< wxT("Static cursor changed to: ");
236 case SQL_CURSOR_FORWARD_ONLY
:
237 cout
<< wxT("Forward Only");
239 case SQL_CURSOR_STATIC
:
240 cout
<< wxT("Static");
242 case SQL_CURSOR_KEYSET_DRIVEN
:
243 cout
<< wxT("Keyset Driven");
245 case SQL_CURSOR_DYNAMIC
:
246 cout
<< wxT("Dynamic");
249 cout
<< endl
<< endl
;
252 if (pDb
->FwdOnlyCursors() && cursorType
!= SQL_CURSOR_FORWARD_ONLY
)
254 // Force the use of a forward only cursor...
255 cursorType
= SQL_CURSOR_FORWARD_ONLY
;
256 if (SQLSetStmtOption(hstmtInternal
, SQL_CURSOR_TYPE
, cursorType
) != SQL_SUCCESS
)
258 // Should never happen
259 pDb
->GetNextError(henv
, hdbc
, hstmtInternal
);
266 pDb
->DispNextError();
267 pDb
->DispAllErrors(henv
, hdbc
, hstmtInternal
);
270 #ifdef DBDEBUG_CONSOLE
272 cout
<< wxT("Cursor Type set to STATIC") << endl
<< endl
;
277 // Set the cursor type for the INSERT statement handle
278 if (SQLSetStmtOption(hstmtInsert
, SQL_CURSOR_TYPE
, SQL_CURSOR_FORWARD_ONLY
) != SQL_SUCCESS
)
279 pDb
->DispAllErrors(henv
, hdbc
, hstmtInsert
);
280 // Set the cursor type for the DELETE statement handle
281 if (SQLSetStmtOption(hstmtDelete
, SQL_CURSOR_TYPE
, SQL_CURSOR_FORWARD_ONLY
) != SQL_SUCCESS
)
282 pDb
->DispAllErrors(henv
, hdbc
, hstmtDelete
);
283 // Set the cursor type for the UPDATE statement handle
284 if (SQLSetStmtOption(hstmtUpdate
, SQL_CURSOR_TYPE
, SQL_CURSOR_FORWARD_ONLY
) != SQL_SUCCESS
)
285 pDb
->DispAllErrors(henv
, hdbc
, hstmtUpdate
);
288 // Make the default cursor the active cursor
289 hstmtDefault
= GetNewCursor(FALSE
,FALSE
);
290 wxASSERT(hstmtDefault
);
291 hstmt
= *hstmtDefault
;
295 } // wxDbTable::initialize()
298 void wxDbTable::cleanup()
303 s
.Printf(wxT("wxDbTable destructor (%-20s) tableID:[%6lu] pDb:[%p]"), tableName
,tableID
,pDb
);
310 TablesInUse
.DeleteContents(TRUE
);
314 pNode
= TablesInUse
.First();
315 while (pNode
&& !found
)
317 if (((wxTablesInUse
*)pNode
->Data())->tableID
== tableID
)
320 if (!TablesInUse
.DeleteNode(pNode
))
321 wxLogDebug (s
,wxT("Unable to delete node!"));
324 pNode
= pNode
->Next();
329 msg
.Printf(wxT("Unable to find the tableID in the linked\nlist of tables in use.\n\n%s"),s
);
330 wxLogDebug (msg
,wxT("NOTICE..."));
335 // Decrement the wxDb table count
337 pDb
->decrementTableCount();
339 // Delete memory allocated for column definitions
343 // Free statement handles
347 if (SQLFreeStmt(hstmtInsert
, SQL_DROP
) != SQL_SUCCESS
)
348 pDb
->DispAllErrors(henv
, hdbc
);
351 if (SQLFreeStmt(hstmtDelete
, SQL_DROP
) != SQL_SUCCESS
)
354 if (SQLFreeStmt(hstmtUpdate
, SQL_DROP
) != SQL_SUCCESS
)
355 pDb
->DispAllErrors(henv
, hdbc
);
359 if (SQLFreeStmt(hstmtInternal
, SQL_DROP
) != SQL_SUCCESS
)
360 pDb
->DispAllErrors(henv
, hdbc
);
362 // Delete dynamically allocated cursors
364 DeleteCursor(hstmtDefault
);
367 DeleteCursor(hstmtCount
);
368 } // wxDbTable::cleanup()
371 /***************************** PRIVATE FUNCTIONS *****************************/
374 /********** wxDbTable::bindUpdateParams() **********/
375 bool wxDbTable::bindParams(bool forUpdate
)
377 wxASSERT(!queryOnly
);
382 UDWORD precision
= 0;
385 // Bind each column of the table that should be bound
386 // to a parameter marker
388 for (i
= 0, colNo
= 1; i
< noCols
; i
++)
392 if (! colDefs
[i
].Updateable
)
397 if (! colDefs
[i
].InsertAllowed
)
401 switch(colDefs
[i
].DbDataType
)
403 case DB_DATA_TYPE_VARCHAR
:
404 fSqlType
= pDb
->GetTypeInfVarchar().FsqlType
;
405 precision
= colDefs
[i
].SzDataObj
;
408 colDefs
[i
].CbValue
= SQL_NULL_DATA
;
410 colDefs
[i
].CbValue
= SQL_NTS
;
412 case DB_DATA_TYPE_INTEGER
:
413 fSqlType
= pDb
->GetTypeInfInteger().FsqlType
;
414 precision
= pDb
->GetTypeInfInteger().Precision
;
417 colDefs
[i
].CbValue
= SQL_NULL_DATA
;
419 colDefs
[i
].CbValue
= 0;
421 case DB_DATA_TYPE_FLOAT
:
422 fSqlType
= pDb
->GetTypeInfFloat().FsqlType
;
423 precision
= pDb
->GetTypeInfFloat().Precision
;
424 scale
= pDb
->GetTypeInfFloat().MaximumScale
;
425 // SQL Sybase Anywhere v5.5 returned a negative number for the
426 // MaxScale. This caused ODBC to kick out an error on ibscale.
427 // I check for this here and set the scale = precision.
429 // scale = (short) precision;
431 colDefs
[i
].CbValue
= SQL_NULL_DATA
;
433 colDefs
[i
].CbValue
= 0;
435 case DB_DATA_TYPE_DATE
:
436 fSqlType
= pDb
->GetTypeInfDate().FsqlType
;
437 precision
= pDb
->GetTypeInfDate().Precision
;
440 colDefs
[i
].CbValue
= SQL_NULL_DATA
;
442 colDefs
[i
].CbValue
= 0;
447 if (SQLBindParameter(hstmtUpdate
, colNo
++, SQL_PARAM_INPUT
, colDefs
[i
].SqlCtype
,
448 fSqlType
, precision
, scale
, (UCHAR
*) colDefs
[i
].PtrDataObj
,
449 precision
+1, &colDefs
[i
].CbValue
) != SQL_SUCCESS
)
451 return(pDb
->DispAllErrors(henv
, hdbc
, hstmtUpdate
));
456 if (SQLBindParameter(hstmtInsert
, colNo
++, SQL_PARAM_INPUT
, colDefs
[i
].SqlCtype
,
457 fSqlType
, precision
, scale
, (UCHAR
*) colDefs
[i
].PtrDataObj
,
458 precision
+1,&colDefs
[i
].CbValue
) != SQL_SUCCESS
)
460 return(pDb
->DispAllErrors(henv
, hdbc
, hstmtInsert
));
465 // Completed successfully
468 } // wxDbTable::bindParams()
471 /********** wxDbTable::bindInsertParams() **********/
472 bool wxDbTable::bindInsertParams(void)
474 return bindParams(FALSE
);
475 } // wxDbTable::bindInsertParams()
478 /********** wxDbTable::bindUpdateParams() **********/
479 bool wxDbTable::bindUpdateParams(void)
481 return bindParams(TRUE
);
482 } // wxDbTable::bindUpdateParams()
485 /********** wxDbTable::bindCols() **********/
486 bool wxDbTable::bindCols(HSTMT cursor
)
488 //RG-NULL static SDWORD cb;
490 // Bind each column of the table to a memory address for fetching data
492 for (i
= 0; i
< noCols
; i
++)
494 if (SQLBindCol(cursor
, i
+1, colDefs
[i
].SqlCtype
, (UCHAR
*) colDefs
[i
].PtrDataObj
,
495 //RG-NULL colDefs[i].SzDataObj, &cb) != SQL_SUCCESS)
496 colDefs
[i
].SzDataObj
, &colDefs
[i
].CbValue
) != SQL_SUCCESS
)
498 return (pDb
->DispAllErrors(henv
, hdbc
, cursor
));
502 // Completed successfully
505 } // wxDbTable::bindCols()
508 /********** wxDbTable::getRec() **********/
509 bool wxDbTable::getRec(UWORD fetchType
)
513 if (!pDb
->FwdOnlyCursors())
515 // Fetch the NEXT, PREV, FIRST or LAST record, depending on fetchType
519 retcode
= SQLExtendedFetch(hstmt
, fetchType
, 0, &cRowsFetched
, &rowStatus
);
520 if (retcode
!= SQL_SUCCESS
&& retcode
!= SQL_SUCCESS_WITH_INFO
)
522 if (retcode
== SQL_NO_DATA_FOUND
)
525 return(pDb
->DispAllErrors(henv
, hdbc
, hstmt
));
529 // Set the Null member variable to indicate the Null state
530 // of each column just read in.
532 for (i
= 0; i
< noCols
; i
++)
533 colDefs
[i
].Null
= (colDefs
[i
].CbValue
== SQL_NULL_DATA
);
538 // Fetch the next record from the record set
539 retcode
= SQLFetch(hstmt
);
540 if (retcode
!= SQL_SUCCESS
&& retcode
!= SQL_SUCCESS_WITH_INFO
)
542 if (retcode
== SQL_NO_DATA_FOUND
)
545 return(pDb
->DispAllErrors(henv
, hdbc
, hstmt
));
549 // Set the Null member variable to indicate the Null state
550 // of each column just read in.
552 for (i
= 0; i
< noCols
; i
++)
553 colDefs
[i
].Null
= (colDefs
[i
].CbValue
== SQL_NULL_DATA
);
557 // Completed successfully
560 } // wxDbTable::getRec()
563 /********** wxDbTable::execDelete() **********/
564 bool wxDbTable::execDelete(const wxString
&pSqlStmt
)
566 // Execute the DELETE statement
567 if (SQLExecDirect(hstmtDelete
, (UCHAR FAR
*) pSqlStmt
.c_str(), SQL_NTS
) != SQL_SUCCESS
)
568 return(pDb
->DispAllErrors(henv
, hdbc
, hstmtDelete
));
570 // Record deleted successfully
573 } // wxDbTable::execDelete()
576 /********** wxDbTable::execUpdate() **********/
577 bool wxDbTable::execUpdate(const wxString
&pSqlStmt
)
579 // Execute the UPDATE statement
580 if (SQLExecDirect(hstmtUpdate
, (UCHAR FAR
*) pSqlStmt
.c_str(), SQL_NTS
) != SQL_SUCCESS
)
581 return(pDb
->DispAllErrors(henv
, hdbc
, hstmtUpdate
));
583 // Record deleted successfully
586 } // wxDbTable::execUpdate()
589 /********** wxDbTable::query() **********/
590 bool wxDbTable::query(int queryType
, bool forUpdate
, bool distinct
, const wxString
&pSqlStmt
)
595 // The user may wish to select for update, but the DBMS may not be capable
596 selectForUpdate
= CanSelectForUpdate();
598 selectForUpdate
= FALSE
;
600 // Set the SQL SELECT string
601 if (queryType
!= DB_SELECT_STATEMENT
) // A select statement was not passed in,
602 { // so generate a select statement.
603 BuildSelectStmt(sqlStmt
, queryType
, distinct
);
604 pDb
->WriteSqlLog(sqlStmt
);
607 This is the block of code that got added during the 2.2.1 merge with
608 the 2.2 main branch that somehow got added here when it should not have. - gt
611 wxStrcpy(sqlStmt, pSqlStmt);
613 SQLFreeStmt(hstmt, SQL_CLOSE);
614 if (SQLExecDirect(hstmt, (UCHAR FAR *) sqlStmt, SQL_NTS) == SQL_SUCCESS)
618 pDb->DispAllErrors(henv, hdbc, hstmt);
622 // Make sure the cursor is closed first
623 if (!CloseCursor(hstmt
))
626 // Execute the SQL SELECT statement
628 retcode
= SQLExecDirect(hstmt
, (UCHAR FAR
*) (queryType
== DB_SELECT_STATEMENT
? pSqlStmt
.c_str() : sqlStmt
.c_str()), SQL_NTS
);
629 if (retcode
!= SQL_SUCCESS
&& retcode
!= SQL_SUCCESS_WITH_INFO
)
630 return(pDb
->DispAllErrors(henv
, hdbc
, hstmt
));
632 // Completed successfully
635 } // wxDbTable::query()
638 /***************************** PUBLIC FUNCTIONS *****************************/
641 /********** wxDbTable::Open() **********/
642 bool wxDbTable::Open(bool checkPrivileges
)
652 // Verify that the table exists in the database
653 if (!pDb
->TableExists(tableName
,/*pDb->GetUsername()*/NULL
,tablePath
))
655 s
= wxT("Table/view does not exist in the database");
656 if ( *(pDb
->dbInf
.accessibleTables
) == wxT('Y'))
657 s
+= wxT(", or you have no permissions.\n");
661 else if (checkPrivileges
)
663 // Verify the user has rights to access the table.
664 // Shortcut boolean evaluation to optimize out call to
667 // Unfortunately this optimization doesn't seem to be
669 if (// *(pDb->dbInf.accessibleTables) == 'N' &&
670 !pDb
->TablePrivileges(tableName
,wxT("SELECT"),NULL
,pDb
->GetUsername(),tablePath
))
671 s
= wxT("Current logged in user does not have sufficient privileges to access this table.\n");
678 if (!tablePath
.IsEmpty())
679 p
.Printf(wxT("Error opening '%s/%s'.\n"),tablePath
,tableName
);
681 p
.Printf(wxT("Error opening '%s'.\n"), tableName
);
684 pDb
->LogError(p
.GetData());
689 // Bind the member variables for field exchange between
690 // the wxDbTable object and the ODBC record.
693 if (!bindInsertParams()) // Inserts
696 if (!bindUpdateParams()) // Updates
700 if (!bindCols(*hstmtDefault
)) // Selects
703 if (!bindCols(hstmtInternal
)) // Internal use only
707 * Do NOT bind the hstmtCount cursor!!!
710 // Build an insert statement using parameter markers
711 if (!queryOnly
&& noCols
> 0)
713 bool needComma
= FALSE
;
714 sqlStmt
.Printf(wxT("INSERT INTO %s ("), tableName
);
715 for (i
= 0; i
< noCols
; i
++)
717 if (! colDefs
[i
].InsertAllowed
)
721 sqlStmt
+= colDefs
[i
].ColName
;
725 sqlStmt
+= wxT(") VALUES (");
727 int insertableCount
= 0;
729 for (i
= 0; i
< noCols
; i
++)
731 if (! colDefs
[i
].InsertAllowed
)
741 // Prepare the insert statement for execution
744 if (SQLPrepare(hstmtInsert
, (UCHAR FAR
*) sqlStmt
.c_str(), SQL_NTS
) != SQL_SUCCESS
)
745 return(pDb
->DispAllErrors(henv
, hdbc
, hstmtInsert
));
751 // Completed successfully
754 } // wxDbTable::Open()
757 /********** wxDbTable::Query() **********/
758 bool wxDbTable::Query(bool forUpdate
, bool distinct
)
761 return(query(DB_SELECT_WHERE
, forUpdate
, distinct
));
763 } // wxDbTable::Query()
766 /********** wxDbTable::QueryBySqlStmt() **********/
767 bool wxDbTable::QueryBySqlStmt(const wxString
&pSqlStmt
)
769 pDb
->WriteSqlLog(pSqlStmt
);
771 return(query(DB_SELECT_STATEMENT
, FALSE
, FALSE
, pSqlStmt
));
773 } // wxDbTable::QueryBySqlStmt()
776 /********** wxDbTable::QueryMatching() **********/
777 bool wxDbTable::QueryMatching(bool forUpdate
, bool distinct
)
780 return(query(DB_SELECT_MATCHING
, forUpdate
, distinct
));
782 } // wxDbTable::QueryMatching()
785 /********** wxDbTable::QueryOnKeyFields() **********/
786 bool wxDbTable::QueryOnKeyFields(bool forUpdate
, bool distinct
)
789 return(query(DB_SELECT_KEYFIELDS
, forUpdate
, distinct
));
791 } // wxDbTable::QueryOnKeyFields()
794 /********** wxDbTable::GetPrev() **********/
795 bool wxDbTable::GetPrev(void)
797 if (pDb
->FwdOnlyCursors())
799 wxFAIL_MSG(wxT("GetPrev()::Backward scrolling cursors are not enabled for this instance of wxDbTable"));
803 return(getRec(SQL_FETCH_PRIOR
));
805 } // wxDbTable::GetPrev()
808 /********** wxDbTable::operator-- **********/
809 bool wxDbTable::operator--(int)
811 if (pDb
->FwdOnlyCursors())
813 wxFAIL_MSG(wxT("operator--:Backward scrolling cursors are not enabled for this instance of wxDbTable"));
817 return(getRec(SQL_FETCH_PRIOR
));
819 } // wxDbTable::operator--
822 /********** wxDbTable::GetFirst() **********/
823 bool wxDbTable::GetFirst(void)
825 if (pDb
->FwdOnlyCursors())
827 wxFAIL_MSG(wxT("GetFirst():Backward scrolling cursors are not enabled for this instance of wxDbTable"));
831 return(getRec(SQL_FETCH_FIRST
));
833 } // wxDbTable::GetFirst()
836 /********** wxDbTable::GetLast() **********/
837 bool wxDbTable::GetLast(void)
839 if (pDb
->FwdOnlyCursors())
841 wxFAIL_MSG(wxT("GetLast()::Backward scrolling cursors are not enabled for this instance of wxDbTable"));
845 return(getRec(SQL_FETCH_LAST
));
847 } // wxDbTable::GetLast()
850 /********** wxDbTable::BuildDeleteStmt() **********/
851 void wxDbTable::BuildDeleteStmt(wxString
&pSqlStmt
, int typeOfDel
, const wxString
&pWhereClause
)
853 wxASSERT(!queryOnly
);
857 wxString whereClause
;
861 // Handle the case of DeleteWhere() and the where clause is blank. It should
862 // delete all records from the database in this case.
863 if (typeOfDel
== DB_DEL_WHERE
&& (pWhereClause
.Length() == 0))
865 pSqlStmt
.Printf(wxT("DELETE FROM %s"), tableName
);
869 pSqlStmt
.Printf(wxT("DELETE FROM %s WHERE "), tableName
);
871 // Append the WHERE clause to the SQL DELETE statement
874 case DB_DEL_KEYFIELDS
:
875 // If the datasource supports the ROWID column, build
876 // the where on ROWID for efficiency purposes.
877 // e.g. DELETE FROM PARTS WHERE ROWID = '111.222.333'
881 wxChar rowid
[wxDB_ROWID_LEN
+1];
883 // Get the ROWID value. If not successful retreiving the ROWID,
884 // simply fall down through the code and build the WHERE clause
885 // based on the key fields.
886 if (SQLGetData(hstmt
, noCols
+1, SQL_C_CHAR
, (UCHAR
*) rowid
, wxDB_ROWID_LEN
, &cb
) == SQL_SUCCESS
)
888 pSqlStmt
+= wxT("ROWID = '");
890 pSqlStmt
+= wxT("'");
894 // Unable to delete by ROWID, so build a WHERE
895 // clause based on the keyfields.
896 BuildWhereClause(whereClause
, DB_WHERE_KEYFIELDS
);
897 pSqlStmt
+= whereClause
;
900 pSqlStmt
+= pWhereClause
;
902 case DB_DEL_MATCHING
:
903 BuildWhereClause(whereClause
, DB_WHERE_MATCHING
);
904 pSqlStmt
+= whereClause
;
908 } // BuildDeleteStmt()
911 /***** DEPRECATED: use wxDbTable::BuildDeleteStmt(wxString &....) form *****/
912 void wxDbTable::BuildDeleteStmt(wxChar
*pSqlStmt
, int typeOfDel
, const wxString
&pWhereClause
)
914 wxString tempSqlStmt
;
915 BuildDeleteStmt(tempSqlStmt
, typeOfDel
, pWhereClause
);
916 wxStrcpy(pSqlStmt
, tempSqlStmt
);
917 } // wxDbTable::BuildDeleteStmt()
920 /********** wxDbTable::BuildSelectStmt() **********/
921 void wxDbTable::BuildSelectStmt(wxString
&pSqlStmt
, int typeOfSelect
, bool distinct
)
923 wxString whereClause
;
926 // Build a select statement to query the database
927 pSqlStmt
= wxT("SELECT ");
929 // SELECT DISTINCT values only?
931 pSqlStmt
+= wxT("DISTINCT ");
933 // Was a FROM clause specified to join tables to the base table?
934 // Available for ::Query() only!!!
935 bool appendFromClause
= FALSE
;
936 #if wxODBC_BACKWARD_COMPATABILITY
937 if (typeOfSelect
== DB_SELECT_WHERE
&& from
&& wxStrlen(from
))
938 appendFromClause
= TRUE
;
940 if (typeOfSelect
== DB_SELECT_WHERE
&& from
.Length())
941 appendFromClause
= TRUE
;
944 // Add the column list
946 for (i
= 0; i
< noCols
; i
++)
948 // If joining tables, the base table column names must be qualified to avoid ambiguity
949 if (appendFromClause
)
951 pSqlStmt
+= queryTableName
;
952 pSqlStmt
+= wxT(".");
954 pSqlStmt
+= colDefs
[i
].ColName
;
956 pSqlStmt
+= wxT(",");
959 // If the datasource supports ROWID, get this column as well. Exception: Don't retrieve
960 // the ROWID if querying distinct records. The rowid will always be unique.
961 if (!distinct
&& CanUpdByROWID())
963 // If joining tables, the base table column names must be qualified to avoid ambiguity
964 if (appendFromClause
)
966 pSqlStmt
+= wxT(",");
967 pSqlStmt
+= queryTableName
;
968 pSqlStmt
+= wxT(".ROWID");
971 pSqlStmt
+= wxT(",ROWID");
974 // Append the FROM tablename portion
975 pSqlStmt
+= wxT(" FROM ");
976 pSqlStmt
+= queryTableName
;
978 // Sybase uses the HOLDLOCK keyword to lock a record during query.
979 // The HOLDLOCK keyword follows the table name in the from clause.
980 // Each table in the from clause must specify HOLDLOCK or
981 // NOHOLDLOCK (the default). Note: The "FOR UPDATE" clause
982 // is parsed but ignored in SYBASE Transact-SQL.
983 if (selectForUpdate
&& (pDb
->Dbms() == dbmsSYBASE_ASA
|| pDb
->Dbms() == dbmsSYBASE_ASE
))
984 pSqlStmt
+= wxT(" HOLDLOCK");
986 if (appendFromClause
)
989 // Append the WHERE clause. Either append the where clause for the class
990 // or build a where clause. The typeOfSelect determines this.
993 case DB_SELECT_WHERE
:
994 #if wxODBC_BACKWARD_COMPATABILITY
995 if (where
&& wxStrlen(where
)) // May not want a where clause!!!
997 if (where
.Length()) // May not want a where clause!!!
1000 pSqlStmt
+= wxT(" WHERE ");
1004 case DB_SELECT_KEYFIELDS
:
1005 BuildWhereClause(whereClause
, DB_WHERE_KEYFIELDS
);
1006 if (whereClause
.Length())
1008 pSqlStmt
+= wxT(" WHERE ");
1009 pSqlStmt
+= whereClause
;
1012 case DB_SELECT_MATCHING
:
1013 BuildWhereClause(whereClause
, DB_WHERE_MATCHING
);
1014 if (whereClause
.Length())
1016 pSqlStmt
+= wxT(" WHERE ");
1017 pSqlStmt
+= whereClause
;
1022 // Append the ORDER BY clause
1023 #if wxODBC_BACKWARD_COMPATABILITY
1024 if (orderBy
&& wxStrlen(orderBy
))
1026 if (orderBy
.Length())
1029 pSqlStmt
+= wxT(" ORDER BY ");
1030 pSqlStmt
+= orderBy
;
1033 // SELECT FOR UPDATE if told to do so and the datasource is capable. Sybase
1034 // parses the FOR UPDATE clause but ignores it. See the comment above on the
1035 // HOLDLOCK for Sybase.
1036 if (selectForUpdate
&& CanSelectForUpdate())
1037 pSqlStmt
+= wxT(" FOR UPDATE");
1039 } // wxDbTable::BuildSelectStmt()
1042 /***** DEPRECATED: use wxDbTable::BuildSelectStmt(wxString &....) form *****/
1043 void wxDbTable::BuildSelectStmt(wxChar
*pSqlStmt
, int typeOfSelect
, bool distinct
)
1045 wxString tempSqlStmt
;
1046 BuildSelectStmt(tempSqlStmt
, typeOfSelect
, distinct
);
1047 wxStrcpy(pSqlStmt
, tempSqlStmt
);
1048 } // wxDbTable::BuildSelectStmt()
1051 /********** wxDbTable::BuildUpdateStmt() **********/
1052 void wxDbTable::BuildUpdateStmt(wxString
&pSqlStmt
, int typeOfUpd
, const wxString
&pWhereClause
)
1054 wxASSERT(!queryOnly
);
1058 wxString whereClause
;
1059 whereClause
.Empty();
1061 bool firstColumn
= TRUE
;
1063 pSqlStmt
.Printf(wxT("UPDATE %s SET "), tableName
);
1065 // Append a list of columns to be updated
1067 for (i
= 0; i
< noCols
; i
++)
1069 // Only append Updateable columns
1070 if (colDefs
[i
].Updateable
)
1073 pSqlStmt
+= wxT(",");
1075 firstColumn
= FALSE
;
1076 pSqlStmt
+= colDefs
[i
].ColName
;
1077 pSqlStmt
+= wxT(" = ?");
1081 // Append the WHERE clause to the SQL UPDATE statement
1082 pSqlStmt
+= wxT(" WHERE ");
1085 case DB_UPD_KEYFIELDS
:
1086 // If the datasource supports the ROWID column, build
1087 // the where on ROWID for efficiency purposes.
1088 // e.g. UPDATE PARTS SET Col1 = ?, Col2 = ? WHERE ROWID = '111.222.333'
1089 if (CanUpdByROWID())
1092 wxChar rowid
[wxDB_ROWID_LEN
+1];
1094 // Get the ROWID value. If not successful retreiving the ROWID,
1095 // simply fall down through the code and build the WHERE clause
1096 // based on the key fields.
1097 if (SQLGetData(hstmt
, noCols
+1, SQL_C_CHAR
, (UCHAR
*) rowid
, wxDB_ROWID_LEN
, &cb
) == SQL_SUCCESS
)
1099 pSqlStmt
+= wxT("ROWID = '");
1101 pSqlStmt
+= wxT("'");
1105 // Unable to delete by ROWID, so build a WHERE
1106 // clause based on the keyfields.
1107 BuildWhereClause(whereClause
, DB_WHERE_KEYFIELDS
);
1108 pSqlStmt
+= whereClause
;
1111 pSqlStmt
+= pWhereClause
;
1114 } // BuildUpdateStmt()
1117 /***** DEPRECATED: use wxDbTable::BuildUpdateStmt(wxString &....) form *****/
1118 void wxDbTable::BuildUpdateStmt(wxChar
*pSqlStmt
, int typeOfUpd
, const wxString
&pWhereClause
)
1120 wxString tempSqlStmt
;
1121 BuildUpdateStmt(tempSqlStmt
, typeOfUpd
, pWhereClause
);
1122 wxStrcpy(pSqlStmt
, tempSqlStmt
);
1123 } // BuildUpdateStmt()
1126 /********** wxDbTable::BuildWhereClause() **********/
1127 void wxDbTable::BuildWhereClause(wxString
&pWhereClause
, int typeOfWhere
,
1128 const wxString
&qualTableName
, bool useLikeComparison
)
1130 * Note: BuildWhereClause() currently ignores timestamp columns.
1131 * They are not included as part of the where clause.
1134 bool moreThanOneColumn
= FALSE
;
1137 // Loop through the columns building a where clause as you go
1139 for (i
= 0; i
< noCols
; i
++)
1141 // Determine if this column should be included in the WHERE clause
1142 if ((typeOfWhere
== DB_WHERE_KEYFIELDS
&& colDefs
[i
].KeyField
) ||
1143 (typeOfWhere
== DB_WHERE_MATCHING
&& (!IsColNull(i
))))
1145 // Skip over timestamp columns
1146 if (colDefs
[i
].SqlCtype
== SQL_C_TIMESTAMP
)
1148 // If there is more than 1 column, join them with the keyword "AND"
1149 if (moreThanOneColumn
)
1150 pWhereClause
+= wxT(" AND ");
1152 moreThanOneColumn
= TRUE
;
1153 // Concatenate where phrase for the column
1154 if (qualTableName
.Length())
1156 pWhereClause
+= qualTableName
;
1157 pWhereClause
+= wxT(".");
1159 pWhereClause
+= colDefs
[i
].ColName
;
1160 if (useLikeComparison
&& (colDefs
[i
].SqlCtype
== SQL_C_CHAR
))
1161 pWhereClause
+= wxT(" LIKE ");
1163 pWhereClause
+= wxT(" = ");
1164 switch(colDefs
[i
].SqlCtype
)
1167 colValue
.Printf(wxT("'%s'"), (UCHAR FAR
*) colDefs
[i
].PtrDataObj
);
1170 colValue
.Printf(wxT("%hi"), *((SWORD
*) colDefs
[i
].PtrDataObj
));
1173 colValue
.Printf(wxT("%hu"), *((UWORD
*) colDefs
[i
].PtrDataObj
));
1176 colValue
.Printf(wxT("%li"), *((SDWORD
*) colDefs
[i
].PtrDataObj
));
1179 colValue
.Printf(wxT("%lu"), *((UDWORD
*) colDefs
[i
].PtrDataObj
));
1182 colValue
.Printf(wxT("%.6f"), *((SFLOAT
*) colDefs
[i
].PtrDataObj
));
1185 colValue
.Printf(wxT("%.6f"), *((SDOUBLE
*) colDefs
[i
].PtrDataObj
));
1188 pWhereClause
+= colValue
;
1191 } // wxDbTable::BuildWhereClause()
1194 /***** DEPRECATED: use wxDbTable::BuildWhereClause(wxString &....) form *****/
1195 void wxDbTable::BuildWhereClause(wxChar
*pWhereClause
, int typeOfWhere
,
1196 const wxString
&qualTableName
, bool useLikeComparison
)
1198 wxString tempSqlStmt
;
1199 BuildWhereClause(tempSqlStmt
, typeOfWhere
, qualTableName
, useLikeComparison
);
1200 wxStrcpy(pWhereClause
, tempSqlStmt
);
1201 } // wxDbTable::BuildWhereClause()
1204 /********** wxDbTable::GetRowNum() **********/
1205 UWORD
wxDbTable::GetRowNum(void)
1209 if (SQLGetStmtOption(hstmt
, SQL_ROW_NUMBER
, (UCHAR
*) &rowNum
) != SQL_SUCCESS
)
1211 pDb
->DispAllErrors(henv
, hdbc
, hstmt
);
1215 // Completed successfully
1216 return((UWORD
) rowNum
);
1218 } // wxDbTable::GetRowNum()
1221 /********** wxDbTable::CloseCursor() **********/
1222 bool wxDbTable::CloseCursor(HSTMT cursor
)
1224 if (SQLFreeStmt(cursor
, SQL_CLOSE
) != SQL_SUCCESS
)
1225 return(pDb
->DispAllErrors(henv
, hdbc
, cursor
));
1227 // Completed successfully
1230 } // wxDbTable::CloseCursor()
1233 /********** wxDbTable::CreateTable() **********/
1234 bool wxDbTable::CreateTable(bool attemptDrop
)
1242 #ifdef DBDEBUG_CONSOLE
1243 cout
<< wxT("Creating Table ") << tableName
<< wxT("...") << endl
;
1247 if (attemptDrop
&& !DropTable())
1251 #ifdef DBDEBUG_CONSOLE
1252 for (i
= 0; i
< noCols
; i
++)
1254 // Exclude derived columns since they are NOT part of the base table
1255 if (colDefs
[i
].DerivedCol
)
1257 cout
<< i
+ 1 << wxT(": ") << colDefs
[i
].ColName
<< wxT("; ");
1258 switch(colDefs
[i
].DbDataType
)
1260 case DB_DATA_TYPE_VARCHAR
:
1261 cout
<< pDb
->typeInfVarchar
.TypeName
<< wxT("(") << colDefs
[i
].SzDataObj
<< wxT(")");
1263 case DB_DATA_TYPE_INTEGER
:
1264 cout
<< pDb
->typeInfInteger
.TypeName
;
1266 case DB_DATA_TYPE_FLOAT
:
1267 cout
<< pDb
->typeInfFloat
.TypeName
;
1269 case DB_DATA_TYPE_DATE
:
1270 cout
<< pDb
->typeInfDate
.TypeName
;
1277 // Build a CREATE TABLE string from the colDefs structure.
1278 bool needComma
= FALSE
;
1279 sqlStmt
.Printf(wxT("CREATE TABLE %s ("), tableName
);
1281 for (i
= 0; i
< noCols
; i
++)
1283 // Exclude derived columns since they are NOT part of the base table
1284 if (colDefs
[i
].DerivedCol
)
1288 sqlStmt
+= wxT(",");
1290 sqlStmt
+= colDefs
[i
].ColName
;
1291 sqlStmt
+= wxT(" ");
1293 switch(colDefs
[i
].DbDataType
)
1295 case DB_DATA_TYPE_VARCHAR
:
1296 sqlStmt
+= pDb
->GetTypeInfVarchar().TypeName
;
1298 case DB_DATA_TYPE_INTEGER
:
1299 sqlStmt
+= pDb
->GetTypeInfInteger().TypeName
;
1301 case DB_DATA_TYPE_FLOAT
:
1302 sqlStmt
+= pDb
->GetTypeInfFloat().TypeName
;
1304 case DB_DATA_TYPE_DATE
:
1305 sqlStmt
+= pDb
->GetTypeInfDate().TypeName
;
1308 // For varchars, append the size of the string
1309 if (colDefs
[i
].DbDataType
== DB_DATA_TYPE_VARCHAR
)
1312 s
.Printf(wxT("(%d)"), colDefs
[i
].SzDataObj
);
1316 if (pDb
->Dbms() == dbmsDB2
||
1317 pDb
->Dbms() == dbmsMY_SQL
||
1318 pDb
->Dbms() == dbmsSYBASE_ASE
||
1319 pDb
->Dbms() == dbmsMS_SQL_SERVER
)
1321 if (colDefs
[i
].KeyField
)
1323 sqlStmt
+= wxT(" NOT NULL");
1329 // If there is a primary key defined, include it in the create statement
1330 for (i
= j
= 0; i
< noCols
; i
++)
1332 if (colDefs
[i
].KeyField
)
1338 if (j
&& pDb
->Dbms() != dbmsDBASE
) // Found a keyfield
1340 if (pDb
->Dbms() != dbmsMY_SQL
)
1342 sqlStmt
+= wxT(",CONSTRAINT ");
1343 sqlStmt
+= tableName
;
1344 sqlStmt
+= wxT("_PIDX PRIMARY KEY (");
1348 /* MySQL goes out on this one. We also declare the relevant key NON NULL above */
1349 sqlStmt
+= wxT(", PRIMARY KEY (");
1352 // List column name(s) of column(s) comprising the primary key
1353 for (i
= j
= 0; i
< noCols
; i
++)
1355 if (colDefs
[i
].KeyField
)
1357 if (j
++) // Multi part key, comma separate names
1358 sqlStmt
+= wxT(",");
1359 sqlStmt
+= colDefs
[i
].ColName
;
1362 sqlStmt
+= wxT(")");
1364 // Append the closing parentheses for the create table statement
1365 sqlStmt
+= wxT(")");
1367 pDb
->WriteSqlLog(sqlStmt
);
1369 #ifdef DBDEBUG_CONSOLE
1370 cout
<< endl
<< sqlStmt
.c_str() << endl
;
1373 // Execute the CREATE TABLE statement
1374 RETCODE retcode
= SQLExecDirect(hstmt
, (UCHAR FAR
*) sqlStmt
.c_str(), SQL_NTS
);
1375 if (retcode
!= SQL_SUCCESS
&& retcode
!= SQL_SUCCESS_WITH_INFO
)
1377 pDb
->DispAllErrors(henv
, hdbc
, hstmt
);
1378 pDb
->RollbackTrans();
1383 // Commit the transaction and close the cursor
1384 if (!pDb
->CommitTrans())
1386 if (!CloseCursor(hstmt
))
1389 // Database table created successfully
1392 } // wxDbTable::CreateTable()
1395 /********** wxDbTable::DropTable() **********/
1396 bool wxDbTable::DropTable()
1398 // NOTE: This function returns TRUE if the Table does not exist, but
1399 // only for identified databases. Code will need to be added
1400 // below for any other databases when those databases are defined
1401 // to handle this situation consistently
1405 sqlStmt
.Printf(wxT("DROP TABLE %s"), tableName
);
1407 pDb
->WriteSqlLog(sqlStmt
);
1409 #ifdef DBDEBUG_CONSOLE
1410 cout
<< endl
<< sqlStmt
.c_str() << endl
;
1413 if (SQLExecDirect(hstmt
, (UCHAR FAR
*) sqlStmt
.c_str(), SQL_NTS
) != SQL_SUCCESS
)
1415 // Check for "Base table not found" error and ignore
1416 pDb
->GetNextError(henv
, hdbc
, hstmt
);
1417 if (wxStrcmp(pDb
->sqlState
, wxT("S0002")) &&
1418 wxStrcmp(pDb
->sqlState
, wxT("S1000"))) // "Base table not found"
1420 // Check for product specific error codes
1421 if (!((pDb
->Dbms() == dbmsSYBASE_ASA
&& !wxStrcmp(pDb
->sqlState
,wxT("42000"))) || // 5.x (and lower?)
1422 (pDb
->Dbms() == dbmsSYBASE_ASE
&& !wxStrcmp(pDb
->sqlState
,wxT("37000"))) ||
1423 (pDb
->Dbms() == dbmsPOSTGRES
&& !wxStrcmp(pDb
->sqlState
,wxT("08S01")))))
1425 pDb
->DispNextError();
1426 pDb
->DispAllErrors(henv
, hdbc
, hstmt
);
1427 pDb
->RollbackTrans();
1434 // Commit the transaction and close the cursor
1435 if (! pDb
->CommitTrans())
1437 if (! CloseCursor(hstmt
))
1441 } // wxDbTable::DropTable()
1444 /********** wxDbTable::CreateIndex() **********/
1445 bool wxDbTable::CreateIndex(const wxString
&idxName
, bool unique
, int noIdxCols
, wxDbIdxDef
*pIdxDefs
, bool attemptDrop
)
1449 // Drop the index first
1450 if (attemptDrop
&& !DropIndex(idxName
))
1453 // MySQL (and possibly Sybase ASE?? - gt) require that any columns which are used as portions
1454 // of an index have the columns defined as "NOT NULL". During initial table creation though,
1455 // it may not be known which columns are necessarily going to be part of an index (e.g. the
1456 // table was created, then months later you determine that an additional index while
1457 // give better performance, so you want to add an index).
1459 // The following block of code will modify the column definition to make the column be
1460 // defined with the "NOT NULL" qualifier.
1461 if (pDb
->Dbms() == dbmsMY_SQL
)
1466 for (i
= 0; i
< noIdxCols
&& ok
; i
++)
1470 // Find the column definition that has the ColName that matches the
1471 // index column name. We need to do this to get the DB_DATA_TYPE of
1472 // the index column, as MySQL's syntax for the ALTER column requires
1474 while (!found
&& (j
< this->noCols
))
1476 if (wxStrcmp(colDefs
[j
].ColName
,pIdxDefs
[i
].ColName
) == 0)
1484 ok
= pDb
->ModifyColumn(tableName
, pIdxDefs
[i
].ColName
,
1485 colDefs
[j
].DbDataType
, colDefs
[j
].SzDataObj
,
1490 wxODBC_ERRORS retcode
;
1491 // Oracle returns a DB_ERR_GENERAL_ERROR if the column is already
1492 // defined to be NOT NULL, but reportedly MySQL doesn't mind.
1493 // This line is just here for debug checking of the value
1494 retcode
= (wxODBC_ERRORS
)pDb
->DB_STATUS
;
1504 pDb
->RollbackTrans();
1509 // Build a CREATE INDEX statement
1510 sqlStmt
= wxT("CREATE ");
1512 sqlStmt
+= wxT("UNIQUE ");
1514 sqlStmt
+= wxT("INDEX ");
1516 sqlStmt
+= wxT(" ON ");
1517 sqlStmt
+= tableName
;
1518 sqlStmt
+= wxT(" (");
1520 // Append list of columns making up index
1522 for (i
= 0; i
< noIdxCols
; i
++)
1524 sqlStmt
+= pIdxDefs
[i
].ColName
;
1525 /* Postgres doesn't cope with ASC */
1526 if (pDb
->Dbms() != dbmsPOSTGRES
)
1528 if (pIdxDefs
[i
].Ascending
)
1529 sqlStmt
+= wxT(" ASC");
1531 sqlStmt
+= wxT(" DESC");
1534 if ((i
+ 1) < noIdxCols
)
1535 sqlStmt
+= wxT(",");
1538 // Append closing parentheses
1539 sqlStmt
+= wxT(")");
1541 pDb
->WriteSqlLog(sqlStmt
);
1543 #ifdef DBDEBUG_CONSOLE
1544 cout
<< endl
<< sqlStmt
.c_str() << endl
<< endl
;
1547 // Execute the CREATE INDEX statement
1548 if (SQLExecDirect(hstmt
, (UCHAR FAR
*) sqlStmt
.c_str(), SQL_NTS
) != SQL_SUCCESS
)
1550 pDb
->DispAllErrors(henv
, hdbc
, hstmt
);
1551 pDb
->RollbackTrans();
1556 // Commit the transaction and close the cursor
1557 if (! pDb
->CommitTrans())
1559 if (! CloseCursor(hstmt
))
1562 // Index Created Successfully
1565 } // wxDbTable::CreateIndex()
1568 /********** wxDbTable::DropIndex() **********/
1569 bool wxDbTable::DropIndex(const wxString
&idxName
)
1571 // NOTE: This function returns TRUE if the Index does not exist, but
1572 // only for identified databases. Code will need to be added
1573 // below for any other databases when those databases are defined
1574 // to handle this situation consistently
1578 if (pDb
->Dbms() == dbmsACCESS
|| pDb
->Dbms() == dbmsMY_SQL
)
1579 sqlStmt
.Printf(wxT("DROP INDEX %s ON %s"),idxName
,tableName
);
1580 else if ((pDb
->Dbms() == dbmsMS_SQL_SERVER
) ||
1581 (pDb
->Dbms() == dbmsSYBASE_ASE
))
1582 sqlStmt
.Printf(wxT("DROP INDEX %s.%s"),tableName
,idxName
);
1584 sqlStmt
.Printf(wxT("DROP INDEX %s"),idxName
);
1586 pDb
->WriteSqlLog(sqlStmt
);
1588 #ifdef DBDEBUG_CONSOLE
1589 cout
<< endl
<< sqlStmt
.c_str() << endl
;
1592 if (SQLExecDirect(hstmt
, (UCHAR FAR
*) sqlStmt
.c_str(), SQL_NTS
) != SQL_SUCCESS
)
1594 // Check for "Index not found" error and ignore
1595 pDb
->GetNextError(henv
, hdbc
, hstmt
);
1596 if (wxStrcmp(pDb
->sqlState
,wxT("S0012"))) // "Index not found"
1598 // Check for product specific error codes
1599 if (!((pDb
->Dbms() == dbmsSYBASE_ASA
&& !wxStrcmp(pDb
->sqlState
,wxT("42000"))) || // v5.x (and lower?)
1600 (pDb
->Dbms() == dbmsSYBASE_ASE
&& !wxStrcmp(pDb
->sqlState
,wxT("37000"))) ||
1601 (pDb
->Dbms() == dbmsMS_SQL_SERVER
&& !wxStrcmp(pDb
->sqlState
,wxT("S1000"))) ||
1602 (pDb
->Dbms() == dbmsSYBASE_ASE
&& !wxStrcmp(pDb
->sqlState
,wxT("S0002"))) || // Base table not found
1603 (pDb
->Dbms() == dbmsMY_SQL
&& !wxStrcmp(pDb
->sqlState
,wxT("42S12"))) || // tested by Christopher Ludwik Marino-Cebulski using v3.23.21beta
1604 (pDb
->Dbms() == dbmsPOSTGRES
&& !wxStrcmp(pDb
->sqlState
,wxT("08S01")))
1607 pDb
->DispNextError();
1608 pDb
->DispAllErrors(henv
, hdbc
, hstmt
);
1609 pDb
->RollbackTrans();
1616 // Commit the transaction and close the cursor
1617 if (! pDb
->CommitTrans())
1619 if (! CloseCursor(hstmt
))
1623 } // wxDbTable::DropIndex()
1626 /********** wxDbTable::SetOrderByColNums() **********/
1627 bool wxDbTable::SetOrderByColNums(int first
, ... )
1635 va_start(argptr
, first
); /* Initialize variable arguments. */
1636 while (!abort
&& (colNo
!= wxDB_NO_MORE_COLUMN_NUMBERS
))
1638 // Make sure the passed in column number
1639 // is within the valid range of columns
1641 // Valid columns are 0 thru noCols-1
1642 if (colNo
>= noCols
|| colNo
< 0)
1649 tempStr
+= wxT(",");
1651 tempStr
+= colDefs
[colNo
].ColName
;
1652 colNo
= va_arg (argptr
, int);
1654 va_end (argptr
); /* Reset variable arguments. */
1656 SetOrderByClause(tempStr
);
1659 } // wxDbTable::SetOrderByColNums()
1662 /********** wxDbTable::Insert() **********/
1663 int wxDbTable::Insert(void)
1665 wxASSERT(!queryOnly
);
1666 if (queryOnly
|| !insertable
)
1671 // Insert the record by executing the already prepared insert statement
1673 retcode
=SQLExecute(hstmtInsert
);
1674 if (retcode
!= SQL_SUCCESS
&& retcode
!= SQL_SUCCESS_WITH_INFO
)
1676 // Check to see if integrity constraint was violated
1677 pDb
->GetNextError(henv
, hdbc
, hstmtInsert
);
1678 if (! wxStrcmp(pDb
->sqlState
, wxT("23000"))) // Integrity constraint violated
1679 return(DB_ERR_INTEGRITY_CONSTRAINT_VIOL
);
1682 pDb
->DispNextError();
1683 pDb
->DispAllErrors(henv
, hdbc
, hstmtInsert
);
1688 // Record inserted into the datasource successfully
1691 } // wxDbTable::Insert()
1694 /********** wxDbTable::Update() **********/
1695 bool wxDbTable::Update(void)
1697 wxASSERT(!queryOnly
);
1703 // Build the SQL UPDATE statement
1704 BuildUpdateStmt(sqlStmt
, DB_UPD_KEYFIELDS
);
1706 pDb
->WriteSqlLog(sqlStmt
);
1708 #ifdef DBDEBUG_CONSOLE
1709 cout
<< endl
<< sqlStmt
.c_str() << endl
<< endl
;
1712 // Execute the SQL UPDATE statement
1713 return(execUpdate(sqlStmt
));
1715 } // wxDbTable::Update()
1718 /********** wxDbTable::Update(pSqlStmt) **********/
1719 bool wxDbTable::Update(const wxString
&pSqlStmt
)
1721 wxASSERT(!queryOnly
);
1725 pDb
->WriteSqlLog(pSqlStmt
);
1727 return(execUpdate(pSqlStmt
));
1729 } // wxDbTable::Update(pSqlStmt)
1732 /********** wxDbTable::UpdateWhere() **********/
1733 bool wxDbTable::UpdateWhere(const wxString
&pWhereClause
)
1735 wxASSERT(!queryOnly
);
1741 // Build the SQL UPDATE statement
1742 BuildUpdateStmt(sqlStmt
, DB_UPD_WHERE
, pWhereClause
);
1744 pDb
->WriteSqlLog(sqlStmt
);
1746 #ifdef DBDEBUG_CONSOLE
1747 cout
<< endl
<< sqlStmt
.c_str() << endl
<< endl
;
1750 // Execute the SQL UPDATE statement
1751 return(execUpdate(sqlStmt
));
1753 } // wxDbTable::UpdateWhere()
1756 /********** wxDbTable::Delete() **********/
1757 bool wxDbTable::Delete(void)
1759 wxASSERT(!queryOnly
);
1766 // Build the SQL DELETE statement
1767 BuildDeleteStmt(sqlStmt
, DB_DEL_KEYFIELDS
);
1769 pDb
->WriteSqlLog(sqlStmt
);
1771 // Execute the SQL DELETE statement
1772 return(execDelete(sqlStmt
));
1774 } // wxDbTable::Delete()
1777 /********** wxDbTable::DeleteWhere() **********/
1778 bool wxDbTable::DeleteWhere(const wxString
&pWhereClause
)
1780 wxASSERT(!queryOnly
);
1787 // Build the SQL DELETE statement
1788 BuildDeleteStmt(sqlStmt
, DB_DEL_WHERE
, pWhereClause
);
1790 pDb
->WriteSqlLog(sqlStmt
);
1792 // Execute the SQL DELETE statement
1793 return(execDelete(sqlStmt
));
1795 } // wxDbTable::DeleteWhere()
1798 /********** wxDbTable::DeleteMatching() **********/
1799 bool wxDbTable::DeleteMatching(void)
1801 wxASSERT(!queryOnly
);
1808 // Build the SQL DELETE statement
1809 BuildDeleteStmt(sqlStmt
, DB_DEL_MATCHING
);
1811 pDb
->WriteSqlLog(sqlStmt
);
1813 // Execute the SQL DELETE statement
1814 return(execDelete(sqlStmt
));
1816 } // wxDbTable::DeleteMatching()
1819 /********** wxDbTable::IsColNull() **********/
1820 bool wxDbTable::IsColNull(int colNo
)
1823 This logic is just not right. It would indicate TRUE
1824 if a numeric field were set to a value of 0.
1826 switch(colDefs[colNo].SqlCtype)
1829 return(((UCHAR FAR *) colDefs[colNo].PtrDataObj)[0] == 0);
1831 return(( *((SWORD *) colDefs[colNo].PtrDataObj)) == 0);
1833 return(( *((UWORD*) colDefs[colNo].PtrDataObj)) == 0);
1835 return(( *((SDWORD *) colDefs[colNo].PtrDataObj)) == 0);
1837 return(( *((UDWORD *) colDefs[colNo].PtrDataObj)) == 0);
1839 return(( *((SFLOAT *) colDefs[colNo].PtrDataObj)) == 0);
1841 return((*((SDOUBLE *) colDefs[colNo].PtrDataObj)) == 0);
1842 case SQL_C_TIMESTAMP:
1843 TIMESTAMP_STRUCT *pDt;
1844 pDt = (TIMESTAMP_STRUCT *) colDefs[colNo].PtrDataObj;
1845 if (pDt->year == 0 && pDt->month == 0 && pDt->day == 0)
1853 return (colDefs
[colNo
].Null
);
1854 } // wxDbTable::IsColNull()
1857 /********** wxDbTable::CanSelectForUpdate() **********/
1858 bool wxDbTable::CanSelectForUpdate(void)
1863 if (pDb
->Dbms() == dbmsMY_SQL
)
1866 if ((pDb
->Dbms() == dbmsORACLE
) ||
1867 (pDb
->dbInf
.posStmts
& SQL_PS_SELECT_FOR_UPDATE
))
1872 } // wxDbTable::CanSelectForUpdate()
1875 /********** wxDbTable::CanUpdByROWID() **********/
1876 bool wxDbTable::CanUpdByROWID(void)
1879 * NOTE: Returning FALSE for now until this can be debugged,
1880 * as the ROWID is not getting updated correctly
1884 if (pDb
->Dbms() == dbmsORACLE
)
1889 } // wxDbTable::CanUpdByROWID()
1892 /********** wxDbTable::IsCursorClosedOnCommit() **********/
1893 bool wxDbTable::IsCursorClosedOnCommit(void)
1895 if (pDb
->dbInf
.cursorCommitBehavior
== SQL_CB_PRESERVE
)
1900 } // wxDbTable::IsCursorClosedOnCommit()
1904 /********** wxDbTable::ClearMemberVar() **********/
1905 void wxDbTable::ClearMemberVar(int colNo
, bool setToNull
)
1907 wxASSERT(colNo
< noCols
);
1909 switch(colDefs
[colNo
].SqlCtype
)
1912 ((UCHAR FAR
*) colDefs
[colNo
].PtrDataObj
)[0] = 0;
1915 *((SWORD
*) colDefs
[colNo
].PtrDataObj
) = 0;
1918 *((UWORD
*) colDefs
[colNo
].PtrDataObj
) = 0;
1921 *((SDWORD
*) colDefs
[colNo
].PtrDataObj
) = 0;
1924 *((UDWORD
*) colDefs
[colNo
].PtrDataObj
) = 0;
1927 *((SFLOAT
*) colDefs
[colNo
].PtrDataObj
) = 0.0f
;
1930 *((SDOUBLE
*) colDefs
[colNo
].PtrDataObj
) = 0.0f
;
1932 case SQL_C_TIMESTAMP
:
1933 TIMESTAMP_STRUCT
*pDt
;
1934 pDt
= (TIMESTAMP_STRUCT
*) colDefs
[colNo
].PtrDataObj
;
1947 } // wxDbTable::ClearMemberVar()
1950 /********** wxDbTable::ClearMemberVars() **********/
1951 void wxDbTable::ClearMemberVars(bool setToNull
)
1955 // Loop through the columns setting each member variable to zero
1956 for (i
=0; i
< noCols
; i
++)
1957 ClearMemberVar(i
,setToNull
);
1959 } // wxDbTable::ClearMemberVars()
1962 /********** wxDbTable::SetQueryTimeout() **********/
1963 bool wxDbTable::SetQueryTimeout(UDWORD nSeconds
)
1965 if (SQLSetStmtOption(hstmtInsert
, SQL_QUERY_TIMEOUT
, nSeconds
) != SQL_SUCCESS
)
1966 return(pDb
->DispAllErrors(henv
, hdbc
, hstmtInsert
));
1967 if (SQLSetStmtOption(hstmtUpdate
, SQL_QUERY_TIMEOUT
, nSeconds
) != SQL_SUCCESS
)
1968 return(pDb
->DispAllErrors(henv
, hdbc
, hstmtUpdate
));
1969 if (SQLSetStmtOption(hstmtDelete
, SQL_QUERY_TIMEOUT
, nSeconds
) != SQL_SUCCESS
)
1970 return(pDb
->DispAllErrors(henv
, hdbc
, hstmtDelete
));
1971 if (SQLSetStmtOption(hstmtInternal
, SQL_QUERY_TIMEOUT
, nSeconds
) != SQL_SUCCESS
)
1972 return(pDb
->DispAllErrors(henv
, hdbc
, hstmtInternal
));
1974 // Completed Successfully
1977 } // wxDbTable::SetQueryTimeout()
1980 /********** wxDbTable::SetColDefs() **********/
1981 void wxDbTable::SetColDefs(int index
, const wxString
&fieldName
, int dataType
, void *pData
,
1982 int cType
, int size
, bool keyField
, bool upd
,
1983 bool insAllow
, bool derivedCol
)
1985 if (!colDefs
) // May happen if the database connection fails
1988 if (fieldName
.Length() > (unsigned int) DB_MAX_COLUMN_NAME_LEN
)
1990 wxStrncpy (colDefs
[index
].ColName
, fieldName
, DB_MAX_COLUMN_NAME_LEN
);
1991 colDefs
[index
].ColName
[DB_MAX_COLUMN_NAME_LEN
] = 0;
1994 wxStrcpy(colDefs
[index
].ColName
, fieldName
);
1996 colDefs
[index
].DbDataType
= dataType
;
1997 colDefs
[index
].PtrDataObj
= pData
;
1998 colDefs
[index
].SqlCtype
= cType
;
1999 colDefs
[index
].SzDataObj
= size
;
2000 colDefs
[index
].KeyField
= keyField
;
2001 colDefs
[index
].DerivedCol
= derivedCol
;
2002 // Derived columns by definition would NOT be "Insertable" or "Updateable"
2005 colDefs
[index
].Updateable
= FALSE
;
2006 colDefs
[index
].InsertAllowed
= FALSE
;
2010 colDefs
[index
].Updateable
= upd
;
2011 colDefs
[index
].InsertAllowed
= insAllow
;
2014 colDefs
[index
].Null
= FALSE
;
2016 } // wxDbTable::SetColDefs()
2019 /********** wxDbTable::SetColDefs() **********/
2020 wxDbColDataPtr
* wxDbTable::SetColDefs(wxDbColInf
*pColInfs
, ULONG numCols
)
2023 wxDbColDataPtr
*pColDataPtrs
= NULL
;
2029 pColDataPtrs
= new wxDbColDataPtr
[numCols
+1];
2031 for (index
= 0; index
< numCols
; index
++)
2033 // Process the fields
2034 switch (pColInfs
[index
].dbDataType
)
2036 case DB_DATA_TYPE_VARCHAR
:
2037 pColDataPtrs
[index
].PtrDataObj
= new wxChar
[pColInfs
[index
].bufferLength
+1];
2038 pColDataPtrs
[index
].SzDataObj
= pColInfs
[index
].columnSize
;
2039 pColDataPtrs
[index
].SqlCtype
= SQL_C_CHAR
;
2041 case DB_DATA_TYPE_INTEGER
:
2042 // Can be long or short
2043 if (pColInfs
[index
].bufferLength
== sizeof(long))
2045 pColDataPtrs
[index
].PtrDataObj
= new long;
2046 pColDataPtrs
[index
].SzDataObj
= sizeof(long);
2047 pColDataPtrs
[index
].SqlCtype
= SQL_C_SLONG
;
2051 pColDataPtrs
[index
].PtrDataObj
= new short;
2052 pColDataPtrs
[index
].SzDataObj
= sizeof(short);
2053 pColDataPtrs
[index
].SqlCtype
= SQL_C_SSHORT
;
2056 case DB_DATA_TYPE_FLOAT
:
2057 // Can be float or double
2058 if (pColInfs
[index
].bufferLength
== sizeof(float))
2060 pColDataPtrs
[index
].PtrDataObj
= new float;
2061 pColDataPtrs
[index
].SzDataObj
= sizeof(float);
2062 pColDataPtrs
[index
].SqlCtype
= SQL_C_FLOAT
;
2066 pColDataPtrs
[index
].PtrDataObj
= new double;
2067 pColDataPtrs
[index
].SzDataObj
= sizeof(double);
2068 pColDataPtrs
[index
].SqlCtype
= SQL_C_DOUBLE
;
2071 case DB_DATA_TYPE_DATE
:
2072 pColDataPtrs
[index
].PtrDataObj
= new TIMESTAMP_STRUCT
;
2073 pColDataPtrs
[index
].SzDataObj
= sizeof(TIMESTAMP_STRUCT
);
2074 pColDataPtrs
[index
].SqlCtype
= SQL_C_TIMESTAMP
;
2077 SetColDefs (index
,pColInfs
[index
].colName
,pColInfs
[index
].dbDataType
, pColDataPtrs
[index
].PtrDataObj
, pColDataPtrs
[index
].SqlCtype
, pColDataPtrs
[index
].SzDataObj
);
2081 return (pColDataPtrs
);
2083 } // wxDbTable::SetColDefs()
2086 /********** wxDbTable::SetCursor() **********/
2087 void wxDbTable::SetCursor(HSTMT
*hstmtActivate
)
2089 if (hstmtActivate
== wxDB_DEFAULT_CURSOR
)
2090 hstmt
= *hstmtDefault
;
2092 hstmt
= *hstmtActivate
;
2094 } // wxDbTable::SetCursor()
2097 /********** wxDbTable::Count(const wxString &) **********/
2098 ULONG
wxDbTable::Count(const wxString
&args
)
2104 // Build a "SELECT COUNT(*) FROM queryTableName [WHERE whereClause]" SQL Statement
2105 sqlStmt
= wxT("SELECT COUNT(");
2107 sqlStmt
+= wxT(") FROM ");
2108 sqlStmt
+= queryTableName
;
2109 #if wxODBC_BACKWARD_COMPATABILITY
2110 if (from
&& wxStrlen(from
))
2116 // Add the where clause if one is provided
2117 #if wxODBC_BACKWARD_COMPATABILITY
2118 if (where
&& wxStrlen(where
))
2123 sqlStmt
+= wxT(" WHERE ");
2127 pDb
->WriteSqlLog(sqlStmt
);
2129 // Initialize the Count cursor if it's not already initialized
2132 hstmtCount
= GetNewCursor(FALSE
,FALSE
);
2133 wxASSERT(hstmtCount
);
2138 // Execute the SQL statement
2139 if (SQLExecDirect(*hstmtCount
, (UCHAR FAR
*) sqlStmt
.c_str(), SQL_NTS
) != SQL_SUCCESS
)
2141 pDb
->DispAllErrors(henv
, hdbc
, *hstmtCount
);
2146 if (SQLFetch(*hstmtCount
) != SQL_SUCCESS
)
2148 pDb
->DispAllErrors(henv
, hdbc
, *hstmtCount
);
2152 // Obtain the result
2153 if (SQLGetData(*hstmtCount
, 1, SQL_C_ULONG
, &count
, sizeof(count
), &cb
) != SQL_SUCCESS
)
2155 pDb
->DispAllErrors(henv
, hdbc
, *hstmtCount
);
2160 if (SQLFreeStmt(*hstmtCount
, SQL_CLOSE
) != SQL_SUCCESS
)
2161 pDb
->DispAllErrors(henv
, hdbc
, *hstmtCount
);
2163 // Return the record count
2166 } // wxDbTable::Count()
2169 /********** wxDbTable::Refresh() **********/
2170 bool wxDbTable::Refresh(void)
2174 // Switch to the internal cursor so any active cursors are not corrupted
2175 HSTMT currCursor
= GetCursor();
2176 hstmt
= hstmtInternal
;
2177 #if wxODBC_BACKWARD_COMPATABILITY
2178 // Save the where and order by clauses
2179 char *saveWhere
= where
;
2180 char *saveOrderBy
= orderBy
;
2182 wxString saveWhere
= where
;
2183 wxString saveOrderBy
= orderBy
;
2185 // Build a where clause to refetch the record with. Try and use the
2186 // ROWID if it's available, ow use the key fields.
2187 wxString whereClause
;
2188 whereClause
.Empty();
2190 if (CanUpdByROWID())
2193 wxChar rowid
[wxDB_ROWID_LEN
+1];
2195 // Get the ROWID value. If not successful retreiving the ROWID,
2196 // simply fall down through the code and build the WHERE clause
2197 // based on the key fields.
2198 if (SQLGetData(hstmt
, noCols
+1, SQL_C_CHAR
, (UCHAR
*) rowid
, wxDB_ROWID_LEN
, &cb
) == SQL_SUCCESS
)
2200 whereClause
+= queryTableName
;
2201 whereClause
+= wxT(".ROWID = '");
2202 whereClause
+= rowid
;
2203 whereClause
+= wxT("'");
2207 // If unable to use the ROWID, build a where clause from the keyfields
2208 if (wxStrlen(whereClause
) == 0)
2209 BuildWhereClause(whereClause
, DB_WHERE_KEYFIELDS
, queryTableName
);
2211 // Requery the record
2212 where
= whereClause
;
2217 if (result
&& !GetNext())
2220 // Switch back to original cursor
2221 SetCursor(&currCursor
);
2223 // Free the internal cursor
2224 if (SQLFreeStmt(hstmtInternal
, SQL_CLOSE
) != SQL_SUCCESS
)
2225 pDb
->DispAllErrors(henv
, hdbc
, hstmtInternal
);
2227 // Restore the original where and order by clauses
2229 orderBy
= saveOrderBy
;
2233 } // wxDbTable::Refresh()
2236 /********** wxDbTable::SetColNull(int colNo, bool set) **********/
2237 bool wxDbTable::SetColNull(int colNo
, bool set
)
2241 colDefs
[colNo
].Null
= set
;
2242 if (set
) // Blank out the values in the member variable
2243 ClearMemberVar(colNo
,FALSE
); // Must call with FALSE, or infinite recursion will happen
2249 } // wxDbTable::SetColNull()
2252 /********** wxDbTable::SetColNull(const wxString &colName, bool set) **********/
2253 bool wxDbTable::SetColNull(const wxString
&colName
, bool set
)
2256 for (i
= 0; i
< noCols
; i
++)
2258 if (!wxStricmp(colName
, colDefs
[i
].ColName
))
2264 colDefs
[i
].Null
= set
;
2265 if (set
) // Blank out the values in the member variable
2266 ClearMemberVar(i
,FALSE
); // Must call with FALSE, or infinite recursion will happen
2272 } // wxDbTable::SetColNull()
2275 /********** wxDbTable::GetNewCursor() **********/
2276 HSTMT
*wxDbTable::GetNewCursor(bool setCursor
, bool bindColumns
)
2278 HSTMT
*newHSTMT
= new HSTMT
;
2283 if (SQLAllocStmt(hdbc
, newHSTMT
) != SQL_SUCCESS
)
2285 pDb
->DispAllErrors(henv
, hdbc
);
2290 if (SQLSetStmtOption(*newHSTMT
, SQL_CURSOR_TYPE
, cursorType
) != SQL_SUCCESS
)
2292 pDb
->DispAllErrors(henv
, hdbc
, *newHSTMT
);
2299 if(!bindCols(*newHSTMT
))
2307 SetCursor(newHSTMT
);
2311 } // wxDbTable::GetNewCursor()
2314 /********** wxDbTable::DeleteCursor() **********/
2315 bool wxDbTable::DeleteCursor(HSTMT
*hstmtDel
)
2319 if (!hstmtDel
) // Cursor already deleted
2322 if (SQLFreeStmt(*hstmtDel
, SQL_DROP
) != SQL_SUCCESS
)
2324 pDb
->DispAllErrors(henv
, hdbc
);
2332 } // wxDbTable::DeleteCursor()
2334 #endif // wxUSE_ODBC