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 // wxWidgets-based application
16 // 2) All enhancements and bug fixes are to be submitted back to the wxWidgets
17 // user groups free of all charges for use with the wxWidgets 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 wxWidgets GUI development toolkit.
21 ///////////////////////////////////////////////////////////////////////////////
28 #if defined(__GNUG__) && !defined(NO_GCC_PRAGMA)
29 #pragma implementation "dbtable.h"
32 #include "wx/wxprec.h"
38 #ifdef DBDEBUG_CONSOLE
44 #include "wx/ioswrap.h"
48 #include "wx/string.h"
49 #include "wx/object.h"
54 #include "wx/filefn.h"
62 #include "wx/dbtable.h"
65 // The HPUX preprocessor lines below were commented out on 8/20/97
66 // because macros.h currently redefines DEBUG and is unneeded.
68 // # include <macros.h>
71 # include <sys/minmax.h>
75 ULONG lastTableID
= 0;
83 void csstrncpyt(wxChar
*target
, const wxChar
*source
, int n
)
85 while ( (*target
++ = *source
++) != '\0' && --n
)
93 /********** wxDbColDef::wxDbColDef() Constructor **********/
94 wxDbColDef::wxDbColDef()
100 bool wxDbColDef::Initialize()
103 DbDataType
= DB_DATA_TYPE_INTEGER
;
104 SqlCtype
= SQL_C_LONG
;
109 InsertAllowed
= FALSE
;
115 } // wxDbColDef::Initialize()
118 /********** wxDbTable::wxDbTable() Constructor **********/
119 wxDbTable::wxDbTable(wxDb
*pwxDb
, const wxString
&tblName
, const UWORD numColumns
,
120 const wxString
&qryTblName
, bool qryOnly
, const wxString
&tblPath
)
122 if (!initialize(pwxDb
, tblName
, numColumns
, qryTblName
, qryOnly
, tblPath
))
124 } // wxDbTable::wxDbTable()
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
)
131 wxString tempQryTblName
;
132 tempQryTblName
= qryTblName
;
133 if (!initialize(pwxDb
, tblName
, numColumns
, tempQryTblName
, qryOnly
, tblPath
))
135 } // wxDbTable::wxDbTable()
138 /********** wxDbTable::~wxDbTable() **********/
139 wxDbTable::~wxDbTable()
142 } // wxDbTable::~wxDbTable()
145 bool wxDbTable::initialize(wxDb
*pwxDb
, const wxString
&tblName
, const UWORD numColumns
,
146 const wxString
&qryTblName
, bool qryOnly
, const wxString
&tblPath
)
148 // Initializing member variables
149 pDb
= pwxDb
; // Pointer to the wxDb object
153 m_hstmtGridQuery
= 0;
154 hstmtDefault
= 0; // Initialized below
155 hstmtCount
= 0; // Initialized first time it is needed
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
171 queryTableName
.Empty();
173 wxASSERT(tblName
.Length());
179 tableName
= tblName
; // Table Name
180 if (tblPath
.Length())
181 tablePath
= tblPath
; // Table Path - used for dBase files
185 if (qryTblName
.Length()) // Name of the table/view to query
186 queryTableName
= qryTblName
;
188 queryTableName
= tblName
;
190 pDb
->incrementTableCount();
193 tableID
= ++lastTableID
;
194 s
.Printf(wxT("wxDbTable constructor (%-20s) tableID:[%6lu] pDb:[%p]"), tblName
.c_str(), tableID
, pDb
);
197 wxTablesInUse
*tableInUse
;
198 tableInUse
= new wxTablesInUse();
199 tableInUse
->tableName
= tblName
;
200 tableInUse
->tableID
= tableID
;
201 tableInUse
->pDb
= pDb
;
202 TablesInUse
.Append(tableInUse
);
207 // Grab the HENV and HDBC from the wxDb object
208 henv
= pDb
->GetHENV();
209 hdbc
= pDb
->GetHDBC();
211 // Allocate space for column definitions
213 colDefs
= new wxDbColDef
[noCols
]; // Points to the first column definition
215 // Allocate statement handles for the table
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
);
228 // Allocate a separate statement handle for internal use
229 if (SQLAllocStmt(hdbc
, &hstmtInternal
) != SQL_SUCCESS
)
230 pDb
->DispAllErrors(henv
, hdbc
);
232 // Set the cursor type for the statement handles
233 cursorType
= SQL_CURSOR_STATIC
;
235 if (SQLSetStmtOption(hstmtInternal
, SQL_CURSOR_TYPE
, cursorType
) != SQL_SUCCESS
)
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
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: ");
250 case SQL_CURSOR_FORWARD_ONLY
:
251 cout
<< wxT("Forward Only");
253 case SQL_CURSOR_STATIC
:
254 cout
<< wxT("Static");
256 case SQL_CURSOR_KEYSET_DRIVEN
:
257 cout
<< wxT("Keyset Driven");
259 case SQL_CURSOR_DYNAMIC
:
260 cout
<< wxT("Dynamic");
263 cout
<< endl
<< endl
;
266 if (pDb
->FwdOnlyCursors() && cursorType
!= SQL_CURSOR_FORWARD_ONLY
)
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
)
272 // Should never happen
273 pDb
->GetNextError(henv
, hdbc
, hstmtInternal
);
280 pDb
->DispNextError();
281 pDb
->DispAllErrors(henv
, hdbc
, hstmtInternal
);
284 #ifdef DBDEBUG_CONSOLE
286 cout
<< wxT("Cursor Type set to STATIC") << endl
<< endl
;
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
);
302 // Make the default cursor the active cursor
303 hstmtDefault
= GetNewCursor(FALSE
,FALSE
);
304 wxASSERT(hstmtDefault
);
305 hstmt
= *hstmtDefault
;
309 } // wxDbTable::initialize()
312 void wxDbTable::cleanup()
317 s
.Printf(wxT("wxDbTable destructor (%-20s) tableID:[%6lu] pDb:[%p]"), tableName
.c_str(), tableID
, pDb
);
326 wxList::compatibility_iterator pNode
;
327 pNode
= TablesInUse
.GetFirst();
328 while (pNode
&& !found
)
330 if (((wxTablesInUse
*)pNode
->GetData())->tableID
== tableID
)
333 delete (wxTablesInUse
*)pNode
->GetData();
334 TablesInUse
.Erase(pNode
);
337 pNode
= pNode
->GetNext();
342 msg
.Printf(wxT("Unable to find the tableID in the linked\nlist of tables in use.\n\n%s"),s
.c_str());
343 wxLogDebug (msg
,wxT("NOTICE..."));
348 // Decrement the wxDb table count
350 pDb
->decrementTableCount();
352 // Delete memory allocated for column definitions
356 // Free statement handles
362 ODBC 3.0 says to use this form
363 if (SQLFreeHandle(*hstmtDel, SQL_DROP) != SQL_SUCCESS)
365 if (SQLFreeStmt(hstmtInsert
, SQL_DROP
) != SQL_SUCCESS
)
366 pDb
->DispAllErrors(henv
, hdbc
);
372 ODBC 3.0 says to use this form
373 if (SQLFreeHandle(*hstmtDel, SQL_DROP) != SQL_SUCCESS)
375 if (SQLFreeStmt(hstmtDelete
, SQL_DROP
) != SQL_SUCCESS
)
376 pDb
->DispAllErrors(henv
, hdbc
);
382 ODBC 3.0 says to use this form
383 if (SQLFreeHandle(*hstmtDel, SQL_DROP) != SQL_SUCCESS)
385 if (SQLFreeStmt(hstmtUpdate
, SQL_DROP
) != SQL_SUCCESS
)
386 pDb
->DispAllErrors(henv
, hdbc
);
392 if (SQLFreeStmt(hstmtInternal
, SQL_DROP
) != SQL_SUCCESS
)
393 pDb
->DispAllErrors(henv
, hdbc
);
396 // Delete dynamically allocated cursors
398 DeleteCursor(hstmtDefault
);
401 DeleteCursor(hstmtCount
);
403 if (m_hstmtGridQuery
)
404 DeleteCursor(m_hstmtGridQuery
);
406 } // wxDbTable::cleanup()
409 /***************************** PRIVATE FUNCTIONS *****************************/
412 void wxDbTable::setCbValueForColumn(int columnIndex
)
414 switch(colDefs
[columnIndex
].DbDataType
)
416 case DB_DATA_TYPE_VARCHAR
:
417 if (colDefs
[columnIndex
].Null
)
418 colDefs
[columnIndex
].CbValue
= SQL_NULL_DATA
;
420 colDefs
[columnIndex
].CbValue
= SQL_NTS
;
422 case DB_DATA_TYPE_INTEGER
:
423 if (colDefs
[columnIndex
].Null
)
424 colDefs
[columnIndex
].CbValue
= SQL_NULL_DATA
;
426 colDefs
[columnIndex
].CbValue
= 0;
428 case DB_DATA_TYPE_FLOAT
:
429 if (colDefs
[columnIndex
].Null
)
430 colDefs
[columnIndex
].CbValue
= SQL_NULL_DATA
;
432 colDefs
[columnIndex
].CbValue
= 0;
434 case DB_DATA_TYPE_DATE
:
435 if (colDefs
[columnIndex
].Null
)
436 colDefs
[columnIndex
].CbValue
= SQL_NULL_DATA
;
438 colDefs
[columnIndex
].CbValue
= 0;
440 case DB_DATA_TYPE_BLOB
:
441 if (colDefs
[columnIndex
].Null
)
442 colDefs
[columnIndex
].CbValue
= SQL_NULL_DATA
;
444 if (colDefs
[columnIndex
].SqlCtype
== SQL_C_CHAR
)
445 colDefs
[columnIndex
].CbValue
= SQL_NTS
;
447 colDefs
[columnIndex
].CbValue
= SQL_LEN_DATA_AT_EXEC(colDefs
[columnIndex
].SzDataObj
);
452 /********** wxDbTable::bindParams() **********/
453 bool wxDbTable::bindParams(bool forUpdate
)
455 wxASSERT(!queryOnly
);
460 SDWORD precision
= 0;
463 // Bind each column of the table that should be bound
464 // to a parameter marker
468 for (i
=0, colNo
=1; i
< noCols
; i
++)
472 if (!colDefs
[i
].Updateable
)
477 if (!colDefs
[i
].InsertAllowed
)
481 switch(colDefs
[i
].DbDataType
)
483 case DB_DATA_TYPE_VARCHAR
:
484 fSqlType
= pDb
->GetTypeInfVarchar().FsqlType
;
485 precision
= colDefs
[i
].SzDataObj
;
488 case DB_DATA_TYPE_INTEGER
:
489 fSqlType
= pDb
->GetTypeInfInteger().FsqlType
;
490 precision
= pDb
->GetTypeInfInteger().Precision
;
493 case DB_DATA_TYPE_FLOAT
:
494 fSqlType
= pDb
->GetTypeInfFloat().FsqlType
;
495 precision
= pDb
->GetTypeInfFloat().Precision
;
496 scale
= pDb
->GetTypeInfFloat().MaximumScale
;
497 // SQL Sybase Anywhere v5.5 returned a negative number for the
498 // MaxScale. This caused ODBC to kick out an error on ibscale.
499 // I check for this here and set the scale = precision.
501 // scale = (short) precision;
503 case DB_DATA_TYPE_DATE
:
504 fSqlType
= pDb
->GetTypeInfDate().FsqlType
;
505 precision
= pDb
->GetTypeInfDate().Precision
;
508 case DB_DATA_TYPE_BLOB
:
509 fSqlType
= pDb
->GetTypeInfBlob().FsqlType
;
510 precision
= colDefs
[i
].SzDataObj
;
515 setCbValueForColumn(i
);
519 if (SQLBindParameter(hstmtUpdate
, colNo
++, SQL_PARAM_INPUT
, colDefs
[i
].SqlCtype
,
520 fSqlType
, precision
, scale
, (UCHAR
*) colDefs
[i
].PtrDataObj
,
521 precision
+1, &colDefs
[i
].CbValue
) != SQL_SUCCESS
)
523 return(pDb
->DispAllErrors(henv
, hdbc
, hstmtUpdate
));
528 if (SQLBindParameter(hstmtInsert
, colNo
++, SQL_PARAM_INPUT
, colDefs
[i
].SqlCtype
,
529 fSqlType
, precision
, scale
, (UCHAR
*) colDefs
[i
].PtrDataObj
,
530 precision
+1, &colDefs
[i
].CbValue
) != SQL_SUCCESS
)
532 return(pDb
->DispAllErrors(henv
, hdbc
, hstmtInsert
));
537 // Completed successfully
540 } // wxDbTable::bindParams()
543 /********** wxDbTable::bindInsertParams() **********/
544 bool wxDbTable::bindInsertParams(void)
546 return bindParams(FALSE
);
547 } // wxDbTable::bindInsertParams()
550 /********** wxDbTable::bindUpdateParams() **********/
551 bool wxDbTable::bindUpdateParams(void)
553 return bindParams(TRUE
);
554 } // wxDbTable::bindUpdateParams()
557 /********** wxDbTable::bindCols() **********/
558 bool wxDbTable::bindCols(HSTMT cursor
)
562 // Bind each column of the table to a memory address for fetching data
564 for (i
= 0; i
< noCols
; i
++)
566 cb
= colDefs
[i
].CbValue
;
567 if (SQLBindCol(cursor
, (UWORD
)(i
+1), colDefs
[i
].SqlCtype
, (UCHAR
*) colDefs
[i
].PtrDataObj
,
568 colDefs
[i
].SzDataObj
, &cb
) != SQL_SUCCESS
)
569 return (pDb
->DispAllErrors(henv
, hdbc
, cursor
));
572 // Completed successfully
575 } // wxDbTable::bindCols()
578 /********** wxDbTable::getRec() **********/
579 bool wxDbTable::getRec(UWORD fetchType
)
583 if (!pDb
->FwdOnlyCursors())
585 // Fetch the NEXT, PREV, FIRST or LAST record, depending on fetchType
589 retcode
= SQLExtendedFetch(hstmt
, fetchType
, 0, &cRowsFetched
, &rowStatus
);
590 if (retcode
!= SQL_SUCCESS
&& retcode
!= SQL_SUCCESS_WITH_INFO
)
592 if (retcode
== SQL_NO_DATA_FOUND
)
595 return(pDb
->DispAllErrors(henv
, hdbc
, hstmt
));
599 // Set the Null member variable to indicate the Null state
600 // of each column just read in.
602 for (i
= 0; i
< noCols
; i
++)
603 colDefs
[i
].Null
= (colDefs
[i
].CbValue
== SQL_NULL_DATA
);
608 // Fetch the next record from the record set
609 retcode
= SQLFetch(hstmt
);
610 if (retcode
!= SQL_SUCCESS
&& retcode
!= SQL_SUCCESS_WITH_INFO
)
612 if (retcode
== SQL_NO_DATA_FOUND
)
615 return(pDb
->DispAllErrors(henv
, hdbc
, hstmt
));
619 // Set the Null member variable to indicate the Null state
620 // of each column just read in.
622 for (i
= 0; i
< noCols
; i
++)
623 colDefs
[i
].Null
= (colDefs
[i
].CbValue
== SQL_NULL_DATA
);
627 // Completed successfully
630 } // wxDbTable::getRec()
633 /********** wxDbTable::execDelete() **********/
634 bool wxDbTable::execDelete(const wxString
&pSqlStmt
)
638 // Execute the DELETE statement
639 retcode
= SQLExecDirect(hstmtDelete
, (SQLTCHAR FAR
*) pSqlStmt
.c_str(), SQL_NTS
);
641 if (retcode
== SQL_SUCCESS
||
642 retcode
== SQL_NO_DATA_FOUND
||
643 retcode
== SQL_SUCCESS_WITH_INFO
)
645 // Record deleted successfully
649 // Problem deleting record
650 return(pDb
->DispAllErrors(henv
, hdbc
, hstmtDelete
));
652 } // wxDbTable::execDelete()
655 /********** wxDbTable::execUpdate() **********/
656 bool wxDbTable::execUpdate(const wxString
&pSqlStmt
)
660 // Execute the UPDATE statement
661 retcode
= SQLExecDirect(hstmtUpdate
, (SQLTCHAR FAR
*) pSqlStmt
.c_str(), SQL_NTS
);
663 if (retcode
== SQL_SUCCESS
||
664 retcode
== SQL_NO_DATA_FOUND
||
665 retcode
== SQL_SUCCESS_WITH_INFO
)
667 // Record updated successfully
670 else if (retcode
== SQL_NEED_DATA
)
673 retcode
= SQLParamData(hstmtUpdate
, &pParmID
);
674 while (retcode
== SQL_NEED_DATA
)
676 // Find the parameter
678 for (i
=0; i
< noCols
; i
++)
680 if (colDefs
[i
].PtrDataObj
== pParmID
)
682 // We found it. Store the parameter.
683 retcode
= SQLPutData(hstmtUpdate
, pParmID
, colDefs
[i
].SzDataObj
);
684 if (retcode
!= SQL_SUCCESS
)
686 pDb
->DispNextError();
687 return pDb
->DispAllErrors(henv
, hdbc
, hstmtUpdate
);
692 retcode
= SQLParamData(hstmtUpdate
, &pParmID
);
694 if (retcode
== SQL_SUCCESS
||
695 retcode
== SQL_NO_DATA_FOUND
||
696 retcode
== SQL_SUCCESS_WITH_INFO
)
698 // Record updated successfully
703 // Problem updating record
704 return(pDb
->DispAllErrors(henv
, hdbc
, hstmtUpdate
));
706 } // wxDbTable::execUpdate()
709 /********** wxDbTable::query() **********/
710 bool wxDbTable::query(int queryType
, bool forUpdate
, bool distinct
, const wxString
&pSqlStmt
)
715 // The user may wish to select for update, but the DBMS may not be capable
716 selectForUpdate
= CanSelectForUpdate();
718 selectForUpdate
= FALSE
;
720 // Set the SQL SELECT string
721 if (queryType
!= DB_SELECT_STATEMENT
) // A select statement was not passed in,
722 { // so generate a select statement.
723 BuildSelectStmt(sqlStmt
, queryType
, distinct
);
724 pDb
->WriteSqlLog(sqlStmt
);
727 // Make sure the cursor is closed first
728 if (!CloseCursor(hstmt
))
731 // Execute the SQL SELECT statement
733 retcode
= SQLExecDirect(hstmt
, (SQLTCHAR FAR
*) (queryType
== DB_SELECT_STATEMENT
? pSqlStmt
.c_str() : sqlStmt
.c_str()), SQL_NTS
);
734 if (retcode
!= SQL_SUCCESS
&& retcode
!= SQL_SUCCESS_WITH_INFO
)
735 return(pDb
->DispAllErrors(henv
, hdbc
, hstmt
));
737 // Completed successfully
740 } // wxDbTable::query()
743 /***************************** PUBLIC FUNCTIONS *****************************/
746 /********** wxDbTable::Open() **********/
747 bool wxDbTable::Open(bool checkPrivileges
, bool checkTableExists
)
757 // Calculate the maximum size of the concatenated
758 // keys for use with wxDbGrid
760 for (i
=0; i
< noCols
; i
++)
762 if (colDefs
[i
].KeyField
)
765 m_keysize
+= colDefs
[i
].SzDataObj
;
770 // Verify that the table exists in the database
771 if (checkTableExists
&& !pDb
->TableExists(tableName
, pDb
->GetUsername(), tablePath
))
773 s
= wxT("Table/view does not exist in the database");
774 if ( *(pDb
->dbInf
.accessibleTables
) == wxT('Y'))
775 s
+= wxT(", or you have no permissions.\n");
779 else if (checkPrivileges
)
781 // Verify the user has rights to access the table.
782 // Shortcut boolean evaluation to optimize out call to
785 // Unfortunately this optimization doesn't seem to be
787 if (// *(pDb->dbInf.accessibleTables) == 'N' &&
788 !pDb
->TablePrivileges(tableName
,wxT("SELECT"), pDb
->GetUsername(), pDb
->GetUsername(), tablePath
))
789 s
= wxT("Current logged in user does not have sufficient privileges to access this table.\n");
796 if (!tablePath
.IsEmpty())
797 p
.Printf(wxT("Error opening '%s/%s'.\n"),tablePath
.c_str(),tableName
.c_str());
799 p
.Printf(wxT("Error opening '%s'.\n"), tableName
.c_str());
802 pDb
->LogError(p
.GetData());
807 // Bind the member variables for field exchange between
808 // the wxDbTable object and the ODBC record.
811 if (!bindInsertParams()) // Inserts
814 if (!bindUpdateParams()) // Updates
818 if (!bindCols(*hstmtDefault
)) // Selects
821 if (!bindCols(hstmtInternal
)) // Internal use only
825 * Do NOT bind the hstmtCount cursor!!!
828 // Build an insert statement using parameter markers
829 if (!queryOnly
&& noCols
> 0)
831 bool needComma
= FALSE
;
832 sqlStmt
.Printf(wxT("INSERT INTO %s ("),
833 pDb
->SQLTableName(tableName
.c_str()).c_str());
834 for (i
= 0; i
< noCols
; i
++)
836 if (! colDefs
[i
].InsertAllowed
)
840 sqlStmt
+= pDb
->SQLColumnName(colDefs
[i
].ColName
);
841 // sqlStmt += colDefs[i].ColName;
845 sqlStmt
+= wxT(") VALUES (");
847 int insertableCount
= 0;
849 for (i
= 0; i
< noCols
; i
++)
851 if (! colDefs
[i
].InsertAllowed
)
861 // Prepare the insert statement for execution
864 if (SQLPrepare(hstmtInsert
, (SQLTCHAR FAR
*) sqlStmt
.c_str(), SQL_NTS
) != SQL_SUCCESS
)
865 return(pDb
->DispAllErrors(henv
, hdbc
, hstmtInsert
));
871 // Completed successfully
874 } // wxDbTable::Open()
877 /********** wxDbTable::Query() **********/
878 bool wxDbTable::Query(bool forUpdate
, bool distinct
)
881 return(query(DB_SELECT_WHERE
, forUpdate
, distinct
));
883 } // wxDbTable::Query()
886 /********** wxDbTable::QueryBySqlStmt() **********/
887 bool wxDbTable::QueryBySqlStmt(const wxString
&pSqlStmt
)
889 pDb
->WriteSqlLog(pSqlStmt
);
891 return(query(DB_SELECT_STATEMENT
, FALSE
, FALSE
, pSqlStmt
));
893 } // wxDbTable::QueryBySqlStmt()
896 /********** wxDbTable::QueryMatching() **********/
897 bool wxDbTable::QueryMatching(bool forUpdate
, bool distinct
)
900 return(query(DB_SELECT_MATCHING
, forUpdate
, distinct
));
902 } // wxDbTable::QueryMatching()
905 /********** wxDbTable::QueryOnKeyFields() **********/
906 bool wxDbTable::QueryOnKeyFields(bool forUpdate
, bool distinct
)
909 return(query(DB_SELECT_KEYFIELDS
, forUpdate
, distinct
));
911 } // wxDbTable::QueryOnKeyFields()
914 /********** wxDbTable::GetPrev() **********/
915 bool wxDbTable::GetPrev(void)
917 if (pDb
->FwdOnlyCursors())
919 wxFAIL_MSG(wxT("GetPrev()::Backward scrolling cursors are not enabled for this instance of wxDbTable"));
923 return(getRec(SQL_FETCH_PRIOR
));
925 } // wxDbTable::GetPrev()
928 /********** wxDbTable::operator-- **********/
929 bool wxDbTable::operator--(int)
931 if (pDb
->FwdOnlyCursors())
933 wxFAIL_MSG(wxT("operator--:Backward scrolling cursors are not enabled for this instance of wxDbTable"));
937 return(getRec(SQL_FETCH_PRIOR
));
939 } // wxDbTable::operator--
942 /********** wxDbTable::GetFirst() **********/
943 bool wxDbTable::GetFirst(void)
945 if (pDb
->FwdOnlyCursors())
947 wxFAIL_MSG(wxT("GetFirst():Backward scrolling cursors are not enabled for this instance of wxDbTable"));
951 return(getRec(SQL_FETCH_FIRST
));
953 } // wxDbTable::GetFirst()
956 /********** wxDbTable::GetLast() **********/
957 bool wxDbTable::GetLast(void)
959 if (pDb
->FwdOnlyCursors())
961 wxFAIL_MSG(wxT("GetLast()::Backward scrolling cursors are not enabled for this instance of wxDbTable"));
965 return(getRec(SQL_FETCH_LAST
));
967 } // wxDbTable::GetLast()
970 /********** wxDbTable::BuildDeleteStmt() **********/
971 void wxDbTable::BuildDeleteStmt(wxString
&pSqlStmt
, int typeOfDel
, const wxString
&pWhereClause
)
973 wxASSERT(!queryOnly
);
977 wxString whereClause
;
981 // Handle the case of DeleteWhere() and the where clause is blank. It should
982 // delete all records from the database in this case.
983 if (typeOfDel
== DB_DEL_WHERE
&& (pWhereClause
.Length() == 0))
985 pSqlStmt
.Printf(wxT("DELETE FROM %s"),
986 pDb
->SQLTableName(tableName
.c_str()).c_str());
990 pSqlStmt
.Printf(wxT("DELETE FROM %s WHERE "),
991 pDb
->SQLTableName(tableName
.c_str()).c_str());
993 // Append the WHERE clause to the SQL DELETE statement
996 case DB_DEL_KEYFIELDS
:
997 // If the datasource supports the ROWID column, build
998 // the where on ROWID for efficiency purposes.
999 // e.g. DELETE FROM PARTS WHERE ROWID = '111.222.333'
1000 if (CanUpdByROWID())
1003 wxChar rowid
[wxDB_ROWID_LEN
+1];
1005 // Get the ROWID value. If not successful retreiving the ROWID,
1006 // simply fall down through the code and build the WHERE clause
1007 // based on the key fields.
1008 if (SQLGetData(hstmt
, (UWORD
)(noCols
+1), SQL_C_CHAR
, (UCHAR
*) rowid
, wxDB_ROWID_LEN
, &cb
) == SQL_SUCCESS
)
1010 pSqlStmt
+= wxT("ROWID = '");
1012 pSqlStmt
+= wxT("'");
1016 // Unable to delete by ROWID, so build a WHERE
1017 // clause based on the keyfields.
1018 BuildWhereClause(whereClause
, DB_WHERE_KEYFIELDS
);
1019 pSqlStmt
+= whereClause
;
1022 pSqlStmt
+= pWhereClause
;
1024 case DB_DEL_MATCHING
:
1025 BuildWhereClause(whereClause
, DB_WHERE_MATCHING
);
1026 pSqlStmt
+= whereClause
;
1030 } // BuildDeleteStmt()
1033 /***** DEPRECATED: use wxDbTable::BuildDeleteStmt(wxString &....) form *****/
1034 void wxDbTable::BuildDeleteStmt(wxChar
*pSqlStmt
, int typeOfDel
, const wxString
&pWhereClause
)
1036 wxString tempSqlStmt
;
1037 BuildDeleteStmt(tempSqlStmt
, typeOfDel
, pWhereClause
);
1038 wxStrcpy(pSqlStmt
, tempSqlStmt
);
1039 } // wxDbTable::BuildDeleteStmt()
1042 /********** wxDbTable::BuildSelectStmt() **********/
1043 void wxDbTable::BuildSelectStmt(wxString
&pSqlStmt
, int typeOfSelect
, bool distinct
)
1045 wxString whereClause
;
1046 whereClause
.Empty();
1048 // Build a select statement to query the database
1049 pSqlStmt
= wxT("SELECT ");
1051 // SELECT DISTINCT values only?
1053 pSqlStmt
+= wxT("DISTINCT ");
1055 // Was a FROM clause specified to join tables to the base table?
1056 // Available for ::Query() only!!!
1057 bool appendFromClause
= FALSE
;
1058 #if wxODBC_BACKWARD_COMPATABILITY
1059 if (typeOfSelect
== DB_SELECT_WHERE
&& from
&& wxStrlen(from
))
1060 appendFromClause
= TRUE
;
1062 if (typeOfSelect
== DB_SELECT_WHERE
&& from
.Length())
1063 appendFromClause
= TRUE
;
1066 // Add the column list
1069 for (i
= 0; i
< noCols
; i
++)
1071 tStr
= colDefs
[i
].ColName
;
1072 // If joining tables, the base table column names must be qualified to avoid ambiguity
1073 if ((appendFromClause
|| pDb
->Dbms() == dbmsACCESS
) && !tStr
.Find(wxT('.')))
1075 pSqlStmt
+= pDb
->SQLTableName(queryTableName
.c_str());
1076 pSqlStmt
+= wxT(".");
1078 pSqlStmt
+= pDb
->SQLColumnName(colDefs
[i
].ColName
);
1080 pSqlStmt
+= wxT(",");
1083 // If the datasource supports ROWID, get this column as well. Exception: Don't retrieve
1084 // the ROWID if querying distinct records. The rowid will always be unique.
1085 if (!distinct
&& CanUpdByROWID())
1087 // If joining tables, the base table column names must be qualified to avoid ambiguity
1088 if (appendFromClause
|| pDb
->Dbms() == dbmsACCESS
)
1090 pSqlStmt
+= wxT(",");
1091 pSqlStmt
+= pDb
->SQLTableName(queryTableName
);
1092 // pSqlStmt += queryTableName;
1093 pSqlStmt
+= wxT(".ROWID");
1096 pSqlStmt
+= wxT(",ROWID");
1099 // Append the FROM tablename portion
1100 pSqlStmt
+= wxT(" FROM ");
1101 pSqlStmt
+= pDb
->SQLTableName(queryTableName
);
1102 // pSqlStmt += queryTableName;
1104 // Sybase uses the HOLDLOCK keyword to lock a record during query.
1105 // The HOLDLOCK keyword follows the table name in the from clause.
1106 // Each table in the from clause must specify HOLDLOCK or
1107 // NOHOLDLOCK (the default). Note: The "FOR UPDATE" clause
1108 // is parsed but ignored in SYBASE Transact-SQL.
1109 if (selectForUpdate
&& (pDb
->Dbms() == dbmsSYBASE_ASA
|| pDb
->Dbms() == dbmsSYBASE_ASE
))
1110 pSqlStmt
+= wxT(" HOLDLOCK");
1112 if (appendFromClause
)
1115 // Append the WHERE clause. Either append the where clause for the class
1116 // or build a where clause. The typeOfSelect determines this.
1117 switch(typeOfSelect
)
1119 case DB_SELECT_WHERE
:
1120 #if wxODBC_BACKWARD_COMPATABILITY
1121 if (where
&& wxStrlen(where
)) // May not want a where clause!!!
1123 if (where
.Length()) // May not want a where clause!!!
1126 pSqlStmt
+= wxT(" WHERE ");
1130 case DB_SELECT_KEYFIELDS
:
1131 BuildWhereClause(whereClause
, DB_WHERE_KEYFIELDS
);
1132 if (whereClause
.Length())
1134 pSqlStmt
+= wxT(" WHERE ");
1135 pSqlStmt
+= whereClause
;
1138 case DB_SELECT_MATCHING
:
1139 BuildWhereClause(whereClause
, DB_WHERE_MATCHING
);
1140 if (whereClause
.Length())
1142 pSqlStmt
+= wxT(" WHERE ");
1143 pSqlStmt
+= whereClause
;
1148 // Append the ORDER BY clause
1149 #if wxODBC_BACKWARD_COMPATABILITY
1150 if (orderBy
&& wxStrlen(orderBy
))
1152 if (orderBy
.Length())
1155 pSqlStmt
+= wxT(" ORDER BY ");
1156 pSqlStmt
+= orderBy
;
1159 // SELECT FOR UPDATE if told to do so and the datasource is capable. Sybase
1160 // parses the FOR UPDATE clause but ignores it. See the comment above on the
1161 // HOLDLOCK for Sybase.
1162 if (selectForUpdate
&& CanSelectForUpdate())
1163 pSqlStmt
+= wxT(" FOR UPDATE");
1165 } // wxDbTable::BuildSelectStmt()
1168 /***** DEPRECATED: use wxDbTable::BuildSelectStmt(wxString &....) form *****/
1169 void wxDbTable::BuildSelectStmt(wxChar
*pSqlStmt
, int typeOfSelect
, bool distinct
)
1171 wxString tempSqlStmt
;
1172 BuildSelectStmt(tempSqlStmt
, typeOfSelect
, distinct
);
1173 wxStrcpy(pSqlStmt
, tempSqlStmt
);
1174 } // wxDbTable::BuildSelectStmt()
1177 /********** wxDbTable::BuildUpdateStmt() **********/
1178 void wxDbTable::BuildUpdateStmt(wxString
&pSqlStmt
, int typeOfUpd
, const wxString
&pWhereClause
)
1180 wxASSERT(!queryOnly
);
1184 wxString whereClause
;
1185 whereClause
.Empty();
1187 bool firstColumn
= TRUE
;
1189 pSqlStmt
.Printf(wxT("UPDATE %s SET "),
1190 pDb
->SQLTableName(tableName
.c_str()).c_str());
1192 // Append a list of columns to be updated
1194 for (i
= 0; i
< noCols
; i
++)
1196 // Only append Updateable columns
1197 if (colDefs
[i
].Updateable
)
1200 pSqlStmt
+= wxT(",");
1202 firstColumn
= FALSE
;
1204 pSqlStmt
+= pDb
->SQLColumnName(colDefs
[i
].ColName
);
1205 // pSqlStmt += colDefs[i].ColName;
1206 pSqlStmt
+= wxT(" = ?");
1210 // Append the WHERE clause to the SQL UPDATE statement
1211 pSqlStmt
+= wxT(" WHERE ");
1214 case DB_UPD_KEYFIELDS
:
1215 // If the datasource supports the ROWID column, build
1216 // the where on ROWID for efficiency purposes.
1217 // e.g. UPDATE PARTS SET Col1 = ?, Col2 = ? WHERE ROWID = '111.222.333'
1218 if (CanUpdByROWID())
1221 wxChar rowid
[wxDB_ROWID_LEN
+1];
1223 // Get the ROWID value. If not successful retreiving the ROWID,
1224 // simply fall down through the code and build the WHERE clause
1225 // based on the key fields.
1226 if (SQLGetData(hstmt
, (UWORD
)(noCols
+1), SQL_C_CHAR
, (UCHAR
*) rowid
, wxDB_ROWID_LEN
, &cb
) == SQL_SUCCESS
)
1228 pSqlStmt
+= wxT("ROWID = '");
1230 pSqlStmt
+= wxT("'");
1234 // Unable to delete by ROWID, so build a WHERE
1235 // clause based on the keyfields.
1236 BuildWhereClause(whereClause
, DB_WHERE_KEYFIELDS
);
1237 pSqlStmt
+= whereClause
;
1240 pSqlStmt
+= pWhereClause
;
1243 } // BuildUpdateStmt()
1246 /***** DEPRECATED: use wxDbTable::BuildUpdateStmt(wxString &....) form *****/
1247 void wxDbTable::BuildUpdateStmt(wxChar
*pSqlStmt
, int typeOfUpd
, const wxString
&pWhereClause
)
1249 wxString tempSqlStmt
;
1250 BuildUpdateStmt(tempSqlStmt
, typeOfUpd
, pWhereClause
);
1251 wxStrcpy(pSqlStmt
, tempSqlStmt
);
1252 } // BuildUpdateStmt()
1255 /********** wxDbTable::BuildWhereClause() **********/
1256 void wxDbTable::BuildWhereClause(wxString
&pWhereClause
, int typeOfWhere
,
1257 const wxString
&qualTableName
, bool useLikeComparison
)
1259 * Note: BuildWhereClause() currently ignores timestamp columns.
1260 * They are not included as part of the where clause.
1263 bool moreThanOneColumn
= FALSE
;
1266 // Loop through the columns building a where clause as you go
1268 for (colNo
= 0; colNo
< noCols
; colNo
++)
1270 // Determine if this column should be included in the WHERE clause
1271 if ((typeOfWhere
== DB_WHERE_KEYFIELDS
&& colDefs
[colNo
].KeyField
) ||
1272 (typeOfWhere
== DB_WHERE_MATCHING
&& (!IsColNull(colNo
))))
1274 // Skip over timestamp columns
1275 if (colDefs
[colNo
].SqlCtype
== SQL_C_TIMESTAMP
)
1277 // If there is more than 1 column, join them with the keyword "AND"
1278 if (moreThanOneColumn
)
1279 pWhereClause
+= wxT(" AND ");
1281 moreThanOneColumn
= TRUE
;
1283 // Concatenate where phrase for the column
1284 wxString tStr
= colDefs
[colNo
].ColName
;
1286 if (qualTableName
.Length() && !tStr
.Find(wxT('.')))
1288 pWhereClause
+= pDb
->SQLTableName(qualTableName
);
1289 pWhereClause
+= wxT(".");
1291 pWhereClause
+= pDb
->SQLColumnName(colDefs
[colNo
].ColName
);
1293 if (useLikeComparison
&& (colDefs
[colNo
].SqlCtype
== SQL_C_CHAR
))
1294 pWhereClause
+= wxT(" LIKE ");
1296 pWhereClause
+= wxT(" = ");
1298 switch(colDefs
[colNo
].SqlCtype
)
1301 colValue
.Printf(wxT("'%s'"), (UCHAR FAR
*) colDefs
[colNo
].PtrDataObj
);
1305 colValue
.Printf(wxT("%hi"), *((SWORD
*) colDefs
[colNo
].PtrDataObj
));
1308 colValue
.Printf(wxT("%hu"), *((UWORD
*) colDefs
[colNo
].PtrDataObj
));
1312 colValue
.Printf(wxT("%li"), *((SDWORD
*) colDefs
[colNo
].PtrDataObj
));
1315 colValue
.Printf(wxT("%lu"), *((UDWORD
*) colDefs
[colNo
].PtrDataObj
));
1318 colValue
.Printf(wxT("%.6f"), *((SFLOAT
*) colDefs
[colNo
].PtrDataObj
));
1321 colValue
.Printf(wxT("%.6f"), *((SDOUBLE
*) colDefs
[colNo
].PtrDataObj
));
1326 strMsg
.Printf(wxT("wxDbTable::bindParams(): Unknown column type for colDefs %d colName %s"),
1327 colNo
,colDefs
[colNo
].ColName
);
1328 wxFAIL_MSG(strMsg
.c_str());
1332 pWhereClause
+= colValue
;
1335 } // wxDbTable::BuildWhereClause()
1338 /***** DEPRECATED: use wxDbTable::BuildWhereClause(wxString &....) form *****/
1339 void wxDbTable::BuildWhereClause(wxChar
*pWhereClause
, int typeOfWhere
,
1340 const wxString
&qualTableName
, bool useLikeComparison
)
1342 wxString tempSqlStmt
;
1343 BuildWhereClause(tempSqlStmt
, typeOfWhere
, qualTableName
, useLikeComparison
);
1344 wxStrcpy(pWhereClause
, tempSqlStmt
);
1345 } // wxDbTable::BuildWhereClause()
1348 /********** wxDbTable::GetRowNum() **********/
1349 UWORD
wxDbTable::GetRowNum(void)
1353 if (SQLGetStmtOption(hstmt
, SQL_ROW_NUMBER
, (UCHAR
*) &rowNum
) != SQL_SUCCESS
)
1355 pDb
->DispAllErrors(henv
, hdbc
, hstmt
);
1359 // Completed successfully
1360 return((UWORD
) rowNum
);
1362 } // wxDbTable::GetRowNum()
1365 /********** wxDbTable::CloseCursor() **********/
1366 bool wxDbTable::CloseCursor(HSTMT cursor
)
1368 if (SQLFreeStmt(cursor
, SQL_CLOSE
) != SQL_SUCCESS
)
1369 return(pDb
->DispAllErrors(henv
, hdbc
, cursor
));
1371 // Completed successfully
1374 } // wxDbTable::CloseCursor()
1377 /********** wxDbTable::CreateTable() **********/
1378 bool wxDbTable::CreateTable(bool attemptDrop
)
1386 #ifdef DBDEBUG_CONSOLE
1387 cout
<< wxT("Creating Table ") << tableName
<< wxT("...") << endl
;
1391 if (attemptDrop
&& !DropTable())
1395 #ifdef DBDEBUG_CONSOLE
1396 for (i
= 0; i
< noCols
; i
++)
1398 // Exclude derived columns since they are NOT part of the base table
1399 if (colDefs
[i
].DerivedCol
)
1401 cout
<< i
+ 1 << wxT(": ") << colDefs
[i
].ColName
<< wxT("; ");
1402 switch(colDefs
[i
].DbDataType
)
1404 case DB_DATA_TYPE_VARCHAR
:
1405 cout
<< pDb
->GetTypeInfVarchar().TypeName
<< wxT("(") << colDefs
[i
].SzDataObj
<< wxT(")");
1407 case DB_DATA_TYPE_INTEGER
:
1408 cout
<< pDb
->GetTypeInfInteger().TypeName
;
1410 case DB_DATA_TYPE_FLOAT
:
1411 cout
<< pDb
->GetTypeInfFloat().TypeName
;
1413 case DB_DATA_TYPE_DATE
:
1414 cout
<< pDb
->GetTypeInfDate().TypeName
;
1416 case DB_DATA_TYPE_BLOB
:
1417 cout
<< pDb
->GetTypeInfBlob().TypeName
;
1424 // Build a CREATE TABLE string from the colDefs structure.
1425 bool needComma
= FALSE
;
1427 sqlStmt
.Printf(wxT("CREATE TABLE %s ("),
1428 pDb
->SQLTableName(tableName
.c_str()).c_str());
1430 for (i
= 0; i
< noCols
; i
++)
1432 // Exclude derived columns since they are NOT part of the base table
1433 if (colDefs
[i
].DerivedCol
)
1437 sqlStmt
+= wxT(",");
1439 sqlStmt
+= pDb
->SQLColumnName(colDefs
[i
].ColName
);
1440 // sqlStmt += colDefs[i].ColName;
1441 sqlStmt
+= wxT(" ");
1443 switch(colDefs
[i
].DbDataType
)
1445 case DB_DATA_TYPE_VARCHAR
:
1446 sqlStmt
+= pDb
->GetTypeInfVarchar().TypeName
;
1448 case DB_DATA_TYPE_INTEGER
:
1449 sqlStmt
+= pDb
->GetTypeInfInteger().TypeName
;
1451 case DB_DATA_TYPE_FLOAT
:
1452 sqlStmt
+= pDb
->GetTypeInfFloat().TypeName
;
1454 case DB_DATA_TYPE_DATE
:
1455 sqlStmt
+= pDb
->GetTypeInfDate().TypeName
;
1457 case DB_DATA_TYPE_BLOB
:
1458 sqlStmt
+= pDb
->GetTypeInfBlob().TypeName
;
1461 // For varchars, append the size of the string
1462 if (colDefs
[i
].DbDataType
== DB_DATA_TYPE_VARCHAR
&&
1463 (pDb
->Dbms() != dbmsMY_SQL
|| pDb
->GetTypeInfVarchar().TypeName
!= _T("text")))// ||
1464 // colDefs[i].DbDataType == DB_DATA_TYPE_BLOB)
1467 s
.Printf(wxT("(%d)"), colDefs
[i
].SzDataObj
);
1471 if (pDb
->Dbms() == dbmsDB2
||
1472 pDb
->Dbms() == dbmsMY_SQL
||
1473 pDb
->Dbms() == dbmsSYBASE_ASE
||
1474 pDb
->Dbms() == dbmsINTERBASE
||
1475 pDb
->Dbms() == dbmsMS_SQL_SERVER
)
1477 if (colDefs
[i
].KeyField
)
1479 sqlStmt
+= wxT(" NOT NULL");
1485 // If there is a primary key defined, include it in the create statement
1486 for (i
= j
= 0; i
< noCols
; i
++)
1488 if (colDefs
[i
].KeyField
)
1494 if ( j
&& (pDb
->Dbms() != dbmsDBASE
)
1495 && (pDb
->Dbms() != dbmsXBASE_SEQUITER
) ) // Found a keyfield
1497 switch (pDb
->Dbms())
1501 case dbmsSYBASE_ASA
:
1502 case dbmsSYBASE_ASE
:
1505 // MySQL goes out on this one. We also declare the relevant key NON NULL above
1506 sqlStmt
+= wxT(",PRIMARY KEY (");
1511 sqlStmt
+= wxT(",CONSTRAINT ");
1512 // DB2 is limited to 18 characters for index names
1513 if (pDb
->Dbms() == dbmsDB2
)
1515 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."));
1516 sqlStmt
+= pDb
->SQLTableName(tableName
.substr(0, 13).c_str());
1517 // sqlStmt += tableName.substr(0, 13);
1520 sqlStmt
+= pDb
->SQLTableName(tableName
.c_str());
1521 // sqlStmt += tableName;
1523 sqlStmt
+= wxT("_PIDX PRIMARY KEY (");
1528 // List column name(s) of column(s) comprising the primary key
1529 for (i
= j
= 0; i
< noCols
; i
++)
1531 if (colDefs
[i
].KeyField
)
1533 if (j
++) // Multi part key, comma separate names
1534 sqlStmt
+= wxT(",");
1535 sqlStmt
+= pDb
->SQLColumnName(colDefs
[i
].ColName
);
1537 if (pDb
->Dbms() == dbmsMY_SQL
&&
1538 colDefs
[i
].DbDataType
== DB_DATA_TYPE_VARCHAR
)
1541 s
.Printf(wxT("(%d)"), colDefs
[i
].SzDataObj
);
1546 sqlStmt
+= wxT(")");
1548 if (pDb
->Dbms() == dbmsINFORMIX
||
1549 pDb
->Dbms() == dbmsSYBASE_ASA
||
1550 pDb
->Dbms() == dbmsSYBASE_ASE
)
1552 sqlStmt
+= wxT(" CONSTRAINT ");
1553 sqlStmt
+= pDb
->SQLTableName(tableName
);
1554 // sqlStmt += tableName;
1555 sqlStmt
+= wxT("_PIDX");
1558 // Append the closing parentheses for the create table statement
1559 sqlStmt
+= wxT(")");
1561 pDb
->WriteSqlLog(sqlStmt
);
1563 #ifdef DBDEBUG_CONSOLE
1564 cout
<< endl
<< sqlStmt
.c_str() << endl
;
1567 // Execute the CREATE TABLE statement
1568 RETCODE retcode
= SQLExecDirect(hstmt
, (SQLTCHAR FAR
*) sqlStmt
.c_str(), SQL_NTS
);
1569 if (retcode
!= SQL_SUCCESS
&& retcode
!= SQL_SUCCESS_WITH_INFO
)
1571 pDb
->DispAllErrors(henv
, hdbc
, hstmt
);
1572 pDb
->RollbackTrans();
1577 // Commit the transaction and close the cursor
1578 if (!pDb
->CommitTrans())
1580 if (!CloseCursor(hstmt
))
1583 // Database table created successfully
1586 } // wxDbTable::CreateTable()
1589 /********** wxDbTable::DropTable() **********/
1590 bool wxDbTable::DropTable()
1592 // NOTE: This function returns TRUE if the Table does not exist, but
1593 // only for identified databases. Code will need to be added
1594 // below for any other databases when those databases are defined
1595 // to handle this situation consistently
1599 sqlStmt
.Printf(wxT("DROP TABLE %s"),
1600 pDb
->SQLTableName(tableName
.c_str()).c_str());
1602 pDb
->WriteSqlLog(sqlStmt
);
1604 #ifdef DBDEBUG_CONSOLE
1605 cout
<< endl
<< sqlStmt
.c_str() << endl
;
1608 RETCODE retcode
= SQLExecDirect(hstmt
, (SQLTCHAR FAR
*) sqlStmt
.c_str(), SQL_NTS
);
1609 if (retcode
!= SQL_SUCCESS
)
1611 // Check for "Base table not found" error and ignore
1612 pDb
->GetNextError(henv
, hdbc
, hstmt
);
1613 if (wxStrcmp(pDb
->sqlState
, wxT("S0002")) /*&&
1614 wxStrcmp(pDb->sqlState, wxT("S1000"))*/) // "Base table not found"
1616 // Check for product specific error codes
1617 if (!((pDb
->Dbms() == dbmsSYBASE_ASA
&& !wxStrcmp(pDb
->sqlState
,wxT("42000"))) || // 5.x (and lower?)
1618 (pDb
->Dbms() == dbmsSYBASE_ASE
&& !wxStrcmp(pDb
->sqlState
,wxT("37000"))) ||
1619 (pDb
->Dbms() == dbmsPERVASIVE_SQL
&& !wxStrcmp(pDb
->sqlState
,wxT("S1000"))) || // Returns an S1000 then an S0002
1620 (pDb
->Dbms() == dbmsPOSTGRES
&& !wxStrcmp(pDb
->sqlState
,wxT("08S01")))))
1622 pDb
->DispNextError();
1623 pDb
->DispAllErrors(henv
, hdbc
, hstmt
);
1624 pDb
->RollbackTrans();
1625 // CloseCursor(hstmt);
1631 // Commit the transaction and close the cursor
1632 if (! pDb
->CommitTrans())
1634 if (! CloseCursor(hstmt
))
1638 } // wxDbTable::DropTable()
1641 /********** wxDbTable::CreateIndex() **********/
1642 bool wxDbTable::CreateIndex(const wxString
&idxName
, bool unique
, UWORD noIdxCols
,
1643 wxDbIdxDef
*pIdxDefs
, bool attemptDrop
)
1647 // Drop the index first
1648 if (attemptDrop
&& !DropIndex(idxName
))
1651 // MySQL (and possibly Sybase ASE?? - gt) require that any columns which are used as portions
1652 // of an index have the columns defined as "NOT NULL". During initial table creation though,
1653 // it may not be known which columns are necessarily going to be part of an index (e.g. the
1654 // table was created, then months later you determine that an additional index while
1655 // give better performance, so you want to add an index).
1657 // The following block of code will modify the column definition to make the column be
1658 // defined with the "NOT NULL" qualifier.
1659 if (pDb
->Dbms() == dbmsMY_SQL
)
1664 for (i
= 0; i
< noIdxCols
&& ok
; i
++)
1668 // Find the column definition that has the ColName that matches the
1669 // index column name. We need to do this to get the DB_DATA_TYPE of
1670 // the index column, as MySQL's syntax for the ALTER column requires
1672 while (!found
&& (j
< this->noCols
))
1674 if (wxStrcmp(colDefs
[j
].ColName
,pIdxDefs
[i
].ColName
) == 0)
1682 ok
= pDb
->ModifyColumn(tableName
, pIdxDefs
[i
].ColName
,
1683 colDefs
[j
].DbDataType
, colDefs
[j
].SzDataObj
,
1689 // retcode is not used
1690 wxODBC_ERRORS retcode
;
1691 // Oracle returns a DB_ERR_GENERAL_ERROR if the column is already
1692 // defined to be NOT NULL, but reportedly MySQL doesn't mind.
1693 // This line is just here for debug checking of the value
1694 retcode
= (wxODBC_ERRORS
)pDb
->DB_STATUS
;
1705 pDb
->RollbackTrans();
1710 // Build a CREATE INDEX statement
1711 sqlStmt
= wxT("CREATE ");
1713 sqlStmt
+= wxT("UNIQUE ");
1715 sqlStmt
+= wxT("INDEX ");
1716 sqlStmt
+= pDb
->SQLTableName(idxName
);
1717 sqlStmt
+= wxT(" ON ");
1719 sqlStmt
+= pDb
->SQLTableName(tableName
);
1720 // sqlStmt += tableName;
1721 sqlStmt
+= wxT(" (");
1723 // Append list of columns making up index
1725 for (i
= 0; i
< noIdxCols
; i
++)
1727 sqlStmt
+= pDb
->SQLColumnName(pIdxDefs
[i
].ColName
);
1728 // sqlStmt += pIdxDefs[i].ColName;
1730 // MySQL requires a key length on VARCHAR keys
1731 if ( pDb
->Dbms() == dbmsMY_SQL
)
1733 // Find the details on this column
1735 for ( j
= 0; j
< noCols
; ++j
)
1737 if ( wxStrcmp( pIdxDefs
[i
].ColName
, colDefs
[j
].ColName
) == 0 )
1742 if ( colDefs
[j
].DbDataType
== DB_DATA_TYPE_VARCHAR
)
1745 s
.Printf(wxT("(%d)"), colDefs
[i
].SzDataObj
);
1750 // Postgres and SQL Server 7 do not support the ASC/DESC keywords for index columns
1751 if (!((pDb
->Dbms() == dbmsMS_SQL_SERVER
) && (wxStrncmp(pDb
->dbInf
.dbmsVer
,_T("07"),2)==0)) &&
1752 !(pDb
->Dbms() == dbmsPOSTGRES
))
1754 if (pIdxDefs
[i
].Ascending
)
1755 sqlStmt
+= wxT(" ASC");
1757 sqlStmt
+= wxT(" DESC");
1760 wxASSERT_MSG(pIdxDefs
[i
].Ascending
, _T("Datasource does not support DESCending index columns"));
1762 if ((i
+ 1) < noIdxCols
)
1763 sqlStmt
+= wxT(",");
1766 // Append closing parentheses
1767 sqlStmt
+= wxT(")");
1769 pDb
->WriteSqlLog(sqlStmt
);
1771 #ifdef DBDEBUG_CONSOLE
1772 cout
<< endl
<< sqlStmt
.c_str() << endl
<< endl
;
1775 // Execute the CREATE INDEX statement
1776 if (SQLExecDirect(hstmt
, (SQLTCHAR FAR
*) sqlStmt
.c_str(), SQL_NTS
) != SQL_SUCCESS
)
1778 pDb
->DispAllErrors(henv
, hdbc
, hstmt
);
1779 pDb
->RollbackTrans();
1784 // Commit the transaction and close the cursor
1785 if (! pDb
->CommitTrans())
1787 if (! CloseCursor(hstmt
))
1790 // Index Created Successfully
1793 } // wxDbTable::CreateIndex()
1796 /********** wxDbTable::DropIndex() **********/
1797 bool wxDbTable::DropIndex(const wxString
&idxName
)
1799 // NOTE: This function returns TRUE if the Index does not exist, but
1800 // only for identified databases. Code will need to be added
1801 // below for any other databases when those databases are defined
1802 // to handle this situation consistently
1806 if (pDb
->Dbms() == dbmsACCESS
|| pDb
->Dbms() == dbmsMY_SQL
||
1807 pDb
->Dbms() == dbmsDBASE
/*|| Paradox needs this syntax too when we add support*/)
1808 sqlStmt
.Printf(wxT("DROP INDEX %s ON %s"),
1809 pDb
->SQLTableName(idxName
.c_str()).c_str(),
1810 pDb
->SQLTableName(tableName
.c_str()).c_str());
1811 else if ((pDb
->Dbms() == dbmsMS_SQL_SERVER
) ||
1812 (pDb
->Dbms() == dbmsSYBASE_ASE
) ||
1813 (pDb
->Dbms() == dbmsXBASE_SEQUITER
))
1814 sqlStmt
.Printf(wxT("DROP INDEX %s.%s"),
1815 pDb
->SQLTableName(tableName
.c_str()).c_str(),
1816 pDb
->SQLTableName(idxName
.c_str()).c_str());
1818 sqlStmt
.Printf(wxT("DROP INDEX %s"),
1819 pDb
->SQLTableName(idxName
.c_str()).c_str());
1821 pDb
->WriteSqlLog(sqlStmt
);
1823 #ifdef DBDEBUG_CONSOLE
1824 cout
<< endl
<< sqlStmt
.c_str() << endl
;
1827 if (SQLExecDirect(hstmt
, (SQLTCHAR FAR
*) sqlStmt
.c_str(), SQL_NTS
) != SQL_SUCCESS
)
1829 // Check for "Index not found" error and ignore
1830 pDb
->GetNextError(henv
, hdbc
, hstmt
);
1831 if (wxStrcmp(pDb
->sqlState
,wxT("S0012"))) // "Index not found"
1833 // Check for product specific error codes
1834 if (!((pDb
->Dbms() == dbmsSYBASE_ASA
&& !wxStrcmp(pDb
->sqlState
,wxT("42000"))) || // v5.x (and lower?)
1835 (pDb
->Dbms() == dbmsSYBASE_ASE
&& !wxStrcmp(pDb
->sqlState
,wxT("37000"))) ||
1836 (pDb
->Dbms() == dbmsMS_SQL_SERVER
&& !wxStrcmp(pDb
->sqlState
,wxT("S1000"))) ||
1837 (pDb
->Dbms() == dbmsINTERBASE
&& !wxStrcmp(pDb
->sqlState
,wxT("S1000"))) ||
1838 (pDb
->Dbms() == dbmsSYBASE_ASE
&& !wxStrcmp(pDb
->sqlState
,wxT("S0002"))) || // Base table not found
1839 (pDb
->Dbms() == dbmsMY_SQL
&& !wxStrcmp(pDb
->sqlState
,wxT("42S12"))) || // tested by Christopher Ludwik Marino-Cebulski using v3.23.21beta
1840 (pDb
->Dbms() == dbmsPOSTGRES
&& !wxStrcmp(pDb
->sqlState
,wxT("08S01")))
1843 pDb
->DispNextError();
1844 pDb
->DispAllErrors(henv
, hdbc
, hstmt
);
1845 pDb
->RollbackTrans();
1852 // Commit the transaction and close the cursor
1853 if (! pDb
->CommitTrans())
1855 if (! CloseCursor(hstmt
))
1859 } // wxDbTable::DropIndex()
1862 /********** wxDbTable::SetOrderByColNums() **********/
1863 bool wxDbTable::SetOrderByColNums(UWORD first
, ... )
1865 int colNo
= first
; // using 'int' to be able to look for wxDB_NO_MORE_COLUN_NUMBERS
1871 va_start(argptr
, first
); /* Initialize variable arguments. */
1872 while (!abort
&& (colNo
!= wxDB_NO_MORE_COLUMN_NUMBERS
))
1874 // Make sure the passed in column number
1875 // is within the valid range of columns
1877 // Valid columns are 0 thru noCols-1
1878 if (colNo
>= noCols
|| colNo
< 0)
1885 tempStr
+= wxT(",");
1887 tempStr
+= colDefs
[colNo
].ColName
;
1888 colNo
= va_arg (argptr
, int);
1890 va_end (argptr
); /* Reset variable arguments. */
1892 SetOrderByClause(tempStr
);
1895 } // wxDbTable::SetOrderByColNums()
1898 /********** wxDbTable::Insert() **********/
1899 int wxDbTable::Insert(void)
1901 wxASSERT(!queryOnly
);
1902 if (queryOnly
|| !insertable
)
1907 // Insert the record by executing the already prepared insert statement
1909 retcode
=SQLExecute(hstmtInsert
);
1910 if (retcode
!= SQL_SUCCESS
&& retcode
!= SQL_SUCCESS_WITH_INFO
&&
1911 retcode
!= SQL_NEED_DATA
)
1913 // Check to see if integrity constraint was violated
1914 pDb
->GetNextError(henv
, hdbc
, hstmtInsert
);
1915 if (! wxStrcmp(pDb
->sqlState
, wxT("23000"))) // Integrity constraint violated
1916 return(DB_ERR_INTEGRITY_CONSTRAINT_VIOL
);
1919 pDb
->DispNextError();
1920 pDb
->DispAllErrors(henv
, hdbc
, hstmtInsert
);
1924 if (retcode
== SQL_NEED_DATA
)
1927 retcode
= SQLParamData(hstmtInsert
, &pParmID
);
1928 while (retcode
== SQL_NEED_DATA
)
1930 // Find the parameter
1932 for (i
=0; i
< noCols
; i
++)
1934 if (colDefs
[i
].PtrDataObj
== pParmID
)
1936 // We found it. Store the parameter.
1937 retcode
= SQLPutData(hstmtInsert
, pParmID
, colDefs
[i
].SzDataObj
);
1938 if (retcode
!= SQL_SUCCESS
)
1940 pDb
->DispNextError();
1941 pDb
->DispAllErrors(henv
, hdbc
, hstmtInsert
);
1947 retcode
= SQLParamData(hstmtInsert
, &pParmID
);
1948 if (retcode
!= SQL_SUCCESS
&&
1949 retcode
!= SQL_SUCCESS_WITH_INFO
)
1951 // record was not inserted
1952 pDb
->DispNextError();
1953 pDb
->DispAllErrors(henv
, hdbc
, hstmtInsert
);
1959 // Record inserted into the datasource successfully
1962 } // wxDbTable::Insert()
1965 /********** wxDbTable::Update() **********/
1966 bool wxDbTable::Update(void)
1968 wxASSERT(!queryOnly
);
1974 // Build the SQL UPDATE statement
1975 BuildUpdateStmt(sqlStmt
, DB_UPD_KEYFIELDS
);
1977 pDb
->WriteSqlLog(sqlStmt
);
1979 #ifdef DBDEBUG_CONSOLE
1980 cout
<< endl
<< sqlStmt
.c_str() << endl
<< endl
;
1983 // Execute the SQL UPDATE statement
1984 return(execUpdate(sqlStmt
));
1986 } // wxDbTable::Update()
1989 /********** wxDbTable::Update(pSqlStmt) **********/
1990 bool wxDbTable::Update(const wxString
&pSqlStmt
)
1992 wxASSERT(!queryOnly
);
1996 pDb
->WriteSqlLog(pSqlStmt
);
1998 return(execUpdate(pSqlStmt
));
2000 } // wxDbTable::Update(pSqlStmt)
2003 /********** wxDbTable::UpdateWhere() **********/
2004 bool wxDbTable::UpdateWhere(const wxString
&pWhereClause
)
2006 wxASSERT(!queryOnly
);
2012 // Build the SQL UPDATE statement
2013 BuildUpdateStmt(sqlStmt
, DB_UPD_WHERE
, pWhereClause
);
2015 pDb
->WriteSqlLog(sqlStmt
);
2017 #ifdef DBDEBUG_CONSOLE
2018 cout
<< endl
<< sqlStmt
.c_str() << endl
<< endl
;
2021 // Execute the SQL UPDATE statement
2022 return(execUpdate(sqlStmt
));
2024 } // wxDbTable::UpdateWhere()
2027 /********** wxDbTable::Delete() **********/
2028 bool wxDbTable::Delete(void)
2030 wxASSERT(!queryOnly
);
2037 // Build the SQL DELETE statement
2038 BuildDeleteStmt(sqlStmt
, DB_DEL_KEYFIELDS
);
2040 pDb
->WriteSqlLog(sqlStmt
);
2042 // Execute the SQL DELETE statement
2043 return(execDelete(sqlStmt
));
2045 } // wxDbTable::Delete()
2048 /********** wxDbTable::DeleteWhere() **********/
2049 bool wxDbTable::DeleteWhere(const wxString
&pWhereClause
)
2051 wxASSERT(!queryOnly
);
2058 // Build the SQL DELETE statement
2059 BuildDeleteStmt(sqlStmt
, DB_DEL_WHERE
, pWhereClause
);
2061 pDb
->WriteSqlLog(sqlStmt
);
2063 // Execute the SQL DELETE statement
2064 return(execDelete(sqlStmt
));
2066 } // wxDbTable::DeleteWhere()
2069 /********** wxDbTable::DeleteMatching() **********/
2070 bool wxDbTable::DeleteMatching(void)
2072 wxASSERT(!queryOnly
);
2079 // Build the SQL DELETE statement
2080 BuildDeleteStmt(sqlStmt
, DB_DEL_MATCHING
);
2082 pDb
->WriteSqlLog(sqlStmt
);
2084 // Execute the SQL DELETE statement
2085 return(execDelete(sqlStmt
));
2087 } // wxDbTable::DeleteMatching()
2090 /********** wxDbTable::IsColNull() **********/
2091 bool wxDbTable::IsColNull(UWORD colNo
) const
2094 This logic is just not right. It would indicate TRUE
2095 if a numeric field were set to a value of 0.
2097 switch(colDefs[colNo].SqlCtype)
2100 return(((UCHAR FAR *) colDefs[colNo].PtrDataObj)[0] == 0);
2102 return(( *((SWORD *) colDefs[colNo].PtrDataObj)) == 0);
2104 return(( *((UWORD*) colDefs[colNo].PtrDataObj)) == 0);
2106 return(( *((SDWORD *) colDefs[colNo].PtrDataObj)) == 0);
2108 return(( *((UDWORD *) colDefs[colNo].PtrDataObj)) == 0);
2110 return(( *((SFLOAT *) colDefs[colNo].PtrDataObj)) == 0);
2112 return((*((SDOUBLE *) colDefs[colNo].PtrDataObj)) == 0);
2113 case SQL_C_TIMESTAMP:
2114 TIMESTAMP_STRUCT *pDt;
2115 pDt = (TIMESTAMP_STRUCT *) colDefs[colNo].PtrDataObj;
2116 if (pDt->year == 0 && pDt->month == 0 && pDt->day == 0)
2124 return (colDefs
[colNo
].Null
);
2125 } // wxDbTable::IsColNull()
2128 /********** wxDbTable::CanSelectForUpdate() **********/
2129 bool wxDbTable::CanSelectForUpdate(void)
2134 if (pDb
->Dbms() == dbmsMY_SQL
)
2137 if ((pDb
->Dbms() == dbmsORACLE
) ||
2138 (pDb
->dbInf
.posStmts
& SQL_PS_SELECT_FOR_UPDATE
))
2143 } // wxDbTable::CanSelectForUpdate()
2146 /********** wxDbTable::CanUpdByROWID() **********/
2147 bool wxDbTable::CanUpdByROWID(void)
2150 * NOTE: Returning FALSE for now until this can be debugged,
2151 * as the ROWID is not getting updated correctly
2155 if (pDb->Dbms() == dbmsORACLE)
2160 } // wxDbTable::CanUpdByROWID()
2163 /********** wxDbTable::IsCursorClosedOnCommit() **********/
2164 bool wxDbTable::IsCursorClosedOnCommit(void)
2166 if (pDb
->dbInf
.cursorCommitBehavior
== SQL_CB_PRESERVE
)
2171 } // wxDbTable::IsCursorClosedOnCommit()
2175 /********** wxDbTable::ClearMemberVar() **********/
2176 void wxDbTable::ClearMemberVar(UWORD colNo
, bool setToNull
)
2178 wxASSERT(colNo
< noCols
);
2180 switch(colDefs
[colNo
].SqlCtype
)
2183 ((UCHAR FAR
*) colDefs
[colNo
].PtrDataObj
)[0] = 0;
2186 *((SWORD
*) colDefs
[colNo
].PtrDataObj
) = 0;
2189 *((UWORD
*) colDefs
[colNo
].PtrDataObj
) = 0;
2192 *((SDWORD
*) colDefs
[colNo
].PtrDataObj
) = 0;
2195 *((UDWORD
*) colDefs
[colNo
].PtrDataObj
) = 0;
2198 *((SFLOAT
*) colDefs
[colNo
].PtrDataObj
) = 0.0f
;
2201 *((SDOUBLE
*) colDefs
[colNo
].PtrDataObj
) = 0.0f
;
2203 case SQL_C_TIMESTAMP
:
2204 TIMESTAMP_STRUCT
*pDt
;
2205 pDt
= (TIMESTAMP_STRUCT
*) colDefs
[colNo
].PtrDataObj
;
2218 } // wxDbTable::ClearMemberVar()
2221 /********** wxDbTable::ClearMemberVars() **********/
2222 void wxDbTable::ClearMemberVars(bool setToNull
)
2226 // Loop through the columns setting each member variable to zero
2227 for (i
=0; i
< noCols
; i
++)
2228 ClearMemberVar(i
,setToNull
);
2230 } // wxDbTable::ClearMemberVars()
2233 /********** wxDbTable::SetQueryTimeout() **********/
2234 bool wxDbTable::SetQueryTimeout(UDWORD nSeconds
)
2236 if (SQLSetStmtOption(hstmtInsert
, SQL_QUERY_TIMEOUT
, nSeconds
) != SQL_SUCCESS
)
2237 return(pDb
->DispAllErrors(henv
, hdbc
, hstmtInsert
));
2238 if (SQLSetStmtOption(hstmtUpdate
, SQL_QUERY_TIMEOUT
, nSeconds
) != SQL_SUCCESS
)
2239 return(pDb
->DispAllErrors(henv
, hdbc
, hstmtUpdate
));
2240 if (SQLSetStmtOption(hstmtDelete
, SQL_QUERY_TIMEOUT
, nSeconds
) != SQL_SUCCESS
)
2241 return(pDb
->DispAllErrors(henv
, hdbc
, hstmtDelete
));
2242 if (SQLSetStmtOption(hstmtInternal
, SQL_QUERY_TIMEOUT
, nSeconds
) != SQL_SUCCESS
)
2243 return(pDb
->DispAllErrors(henv
, hdbc
, hstmtInternal
));
2245 // Completed Successfully
2248 } // wxDbTable::SetQueryTimeout()
2251 /********** wxDbTable::SetColDefs() **********/
2252 void wxDbTable::SetColDefs(UWORD index
, const wxString
&fieldName
, int dataType
, void *pData
,
2253 SWORD cType
, int size
, bool keyField
, bool upd
,
2254 bool insAllow
, bool derivedCol
)
2256 wxASSERT_MSG( index
< noCols
,
2257 _T("Specified column index exceeds the maximum number of columns for this table.") );
2259 if (!colDefs
) // May happen if the database connection fails
2262 if (fieldName
.Length() > (unsigned int) DB_MAX_COLUMN_NAME_LEN
)
2264 wxStrncpy(colDefs
[index
].ColName
, fieldName
, DB_MAX_COLUMN_NAME_LEN
);
2265 colDefs
[index
].ColName
[DB_MAX_COLUMN_NAME_LEN
] = 0;
2269 tmpMsg
.Printf(_T("Column name '%s' is too long. Truncated to '%s'."),
2270 fieldName
.c_str(),colDefs
[index
].ColName
);
2272 #endif // __WXDEBUG__
2275 wxStrcpy(colDefs
[index
].ColName
, fieldName
);
2277 colDefs
[index
].DbDataType
= dataType
;
2278 colDefs
[index
].PtrDataObj
= pData
;
2279 colDefs
[index
].SqlCtype
= cType
;
2280 colDefs
[index
].SzDataObj
= size
;
2281 colDefs
[index
].KeyField
= keyField
;
2282 colDefs
[index
].DerivedCol
= derivedCol
;
2283 // Derived columns by definition would NOT be "Insertable" or "Updateable"
2286 colDefs
[index
].Updateable
= FALSE
;
2287 colDefs
[index
].InsertAllowed
= FALSE
;
2291 colDefs
[index
].Updateable
= upd
;
2292 colDefs
[index
].InsertAllowed
= insAllow
;
2295 colDefs
[index
].Null
= FALSE
;
2297 } // wxDbTable::SetColDefs()
2300 /********** wxDbTable::SetColDefs() **********/
2301 wxDbColDataPtr
* wxDbTable::SetColDefs(wxDbColInf
*pColInfs
, UWORD numCols
)
2304 wxDbColDataPtr
*pColDataPtrs
= NULL
;
2310 pColDataPtrs
= new wxDbColDataPtr
[numCols
+1];
2312 for (index
= 0; index
< numCols
; index
++)
2314 // Process the fields
2315 switch (pColInfs
[index
].dbDataType
)
2317 case DB_DATA_TYPE_VARCHAR
:
2318 pColDataPtrs
[index
].PtrDataObj
= new wxChar
[pColInfs
[index
].bufferLength
+1];
2319 pColDataPtrs
[index
].SzDataObj
= pColInfs
[index
].columnSize
;
2320 pColDataPtrs
[index
].SqlCtype
= SQL_C_CHAR
;
2322 case DB_DATA_TYPE_INTEGER
:
2323 // Can be long or short
2324 if (pColInfs
[index
].bufferLength
== sizeof(long))
2326 pColDataPtrs
[index
].PtrDataObj
= new long;
2327 pColDataPtrs
[index
].SzDataObj
= sizeof(long);
2328 pColDataPtrs
[index
].SqlCtype
= SQL_C_SLONG
;
2332 pColDataPtrs
[index
].PtrDataObj
= new short;
2333 pColDataPtrs
[index
].SzDataObj
= sizeof(short);
2334 pColDataPtrs
[index
].SqlCtype
= SQL_C_SSHORT
;
2337 case DB_DATA_TYPE_FLOAT
:
2338 // Can be float or double
2339 if (pColInfs
[index
].bufferLength
== sizeof(float))
2341 pColDataPtrs
[index
].PtrDataObj
= new float;
2342 pColDataPtrs
[index
].SzDataObj
= sizeof(float);
2343 pColDataPtrs
[index
].SqlCtype
= SQL_C_FLOAT
;
2347 pColDataPtrs
[index
].PtrDataObj
= new double;
2348 pColDataPtrs
[index
].SzDataObj
= sizeof(double);
2349 pColDataPtrs
[index
].SqlCtype
= SQL_C_DOUBLE
;
2352 case DB_DATA_TYPE_DATE
:
2353 pColDataPtrs
[index
].PtrDataObj
= new TIMESTAMP_STRUCT
;
2354 pColDataPtrs
[index
].SzDataObj
= sizeof(TIMESTAMP_STRUCT
);
2355 pColDataPtrs
[index
].SqlCtype
= SQL_C_TIMESTAMP
;
2357 case DB_DATA_TYPE_BLOB
:
2358 wxFAIL_MSG(wxT("This form of ::SetColDefs() cannot be used with BLOB columns"));
2359 pColDataPtrs
[index
].PtrDataObj
= /*BLOB ADDITION NEEDED*/NULL
;
2360 pColDataPtrs
[index
].SzDataObj
= /*BLOB ADDITION NEEDED*/sizeof(void *);
2361 pColDataPtrs
[index
].SqlCtype
= SQL_VARBINARY
;
2364 if (pColDataPtrs
[index
].PtrDataObj
!= NULL
)
2365 SetColDefs (index
,pColInfs
[index
].colName
,pColInfs
[index
].dbDataType
, pColDataPtrs
[index
].PtrDataObj
, pColDataPtrs
[index
].SqlCtype
, pColDataPtrs
[index
].SzDataObj
);
2368 // Unable to build all the column definitions, as either one of
2369 // the calls to "new" failed above, or there was a BLOB field
2370 // to have a column definition for. If BLOBs are to be used,
2371 // the other form of ::SetColDefs() must be used, as it is impossible
2372 // to know the maximum size to create the PtrDataObj to be.
2373 delete [] pColDataPtrs
;
2379 return (pColDataPtrs
);
2381 } // wxDbTable::SetColDefs()
2384 /********** wxDbTable::SetCursor() **********/
2385 void wxDbTable::SetCursor(HSTMT
*hstmtActivate
)
2387 if (hstmtActivate
== wxDB_DEFAULT_CURSOR
)
2388 hstmt
= *hstmtDefault
;
2390 hstmt
= *hstmtActivate
;
2392 } // wxDbTable::SetCursor()
2395 /********** wxDbTable::Count(const wxString &) **********/
2396 ULONG
wxDbTable::Count(const wxString
&args
)
2402 // Build a "SELECT COUNT(*) FROM queryTableName [WHERE whereClause]" SQL Statement
2403 sqlStmt
= wxT("SELECT COUNT(");
2405 sqlStmt
+= wxT(") FROM ");
2406 sqlStmt
+= pDb
->SQLTableName(queryTableName
);
2407 // sqlStmt += queryTableName;
2408 #if wxODBC_BACKWARD_COMPATABILITY
2409 if (from
&& wxStrlen(from
))
2415 // Add the where clause if one is provided
2416 #if wxODBC_BACKWARD_COMPATABILITY
2417 if (where
&& wxStrlen(where
))
2422 sqlStmt
+= wxT(" WHERE ");
2426 pDb
->WriteSqlLog(sqlStmt
);
2428 // Initialize the Count cursor if it's not already initialized
2431 hstmtCount
= GetNewCursor(FALSE
,FALSE
);
2432 wxASSERT(hstmtCount
);
2437 // Execute the SQL statement
2438 if (SQLExecDirect(*hstmtCount
, (SQLTCHAR FAR
*) sqlStmt
.c_str(), SQL_NTS
) != SQL_SUCCESS
)
2440 pDb
->DispAllErrors(henv
, hdbc
, *hstmtCount
);
2445 if (SQLFetch(*hstmtCount
) != SQL_SUCCESS
)
2447 pDb
->DispAllErrors(henv
, hdbc
, *hstmtCount
);
2451 // Obtain the result
2452 if (SQLGetData(*hstmtCount
, (UWORD
)1, SQL_C_ULONG
, &count
, sizeof(count
), &cb
) != SQL_SUCCESS
)
2454 pDb
->DispAllErrors(henv
, hdbc
, *hstmtCount
);
2459 if (SQLFreeStmt(*hstmtCount
, SQL_CLOSE
) != SQL_SUCCESS
)
2460 pDb
->DispAllErrors(henv
, hdbc
, *hstmtCount
);
2462 // Return the record count
2465 } // wxDbTable::Count()
2468 /********** wxDbTable::Refresh() **********/
2469 bool wxDbTable::Refresh(void)
2473 // Switch to the internal cursor so any active cursors are not corrupted
2474 HSTMT currCursor
= GetCursor();
2475 hstmt
= hstmtInternal
;
2476 #if wxODBC_BACKWARD_COMPATABILITY
2477 // Save the where and order by clauses
2478 wxChar
*saveWhere
= where
;
2479 wxChar
*saveOrderBy
= orderBy
;
2481 wxString saveWhere
= where
;
2482 wxString saveOrderBy
= orderBy
;
2484 // Build a where clause to refetch the record with. Try and use the
2485 // ROWID if it's available, ow use the key fields.
2486 wxString whereClause
;
2487 whereClause
.Empty();
2489 if (CanUpdByROWID())
2492 wxChar rowid
[wxDB_ROWID_LEN
+1];
2494 // Get the ROWID value. If not successful retreiving the ROWID,
2495 // simply fall down through the code and build the WHERE clause
2496 // based on the key fields.
2497 if (SQLGetData(hstmt
, (UWORD
)(noCols
+1), SQL_C_CHAR
, (UCHAR
*) rowid
, wxDB_ROWID_LEN
, &cb
) == SQL_SUCCESS
)
2499 whereClause
+= pDb
->SQLTableName(queryTableName
);
2500 // whereClause += queryTableName;
2501 whereClause
+= wxT(".ROWID = '");
2502 whereClause
+= rowid
;
2503 whereClause
+= wxT("'");
2507 // If unable to use the ROWID, build a where clause from the keyfields
2508 if (wxStrlen(whereClause
) == 0)
2509 BuildWhereClause(whereClause
, DB_WHERE_KEYFIELDS
, queryTableName
);
2511 // Requery the record
2512 where
= whereClause
;
2517 if (result
&& !GetNext())
2520 // Switch back to original cursor
2521 SetCursor(&currCursor
);
2523 // Free the internal cursor
2524 if (SQLFreeStmt(hstmtInternal
, SQL_CLOSE
) != SQL_SUCCESS
)
2525 pDb
->DispAllErrors(henv
, hdbc
, hstmtInternal
);
2527 // Restore the original where and order by clauses
2529 orderBy
= saveOrderBy
;
2533 } // wxDbTable::Refresh()
2536 /********** wxDbTable::SetColNull() **********/
2537 bool wxDbTable::SetColNull(UWORD colNo
, bool set
)
2541 colDefs
[colNo
].Null
= set
;
2542 if (set
) // Blank out the values in the member variable
2543 ClearMemberVar(colNo
, FALSE
); // Must call with FALSE here, or infinite recursion will happen
2545 setCbValueForColumn(colNo
);
2552 } // wxDbTable::SetColNull()
2555 /********** wxDbTable::SetColNull() **********/
2556 bool wxDbTable::SetColNull(const wxString
&colName
, bool set
)
2559 for (colNo
= 0; colNo
< noCols
; colNo
++)
2561 if (!wxStricmp(colName
, colDefs
[colNo
].ColName
))
2567 colDefs
[colNo
].Null
= set
;
2568 if (set
) // Blank out the values in the member variable
2569 ClearMemberVar(colNo
,FALSE
); // Must call with FALSE here, or infinite recursion will happen
2571 setCbValueForColumn(colNo
);
2578 } // wxDbTable::SetColNull()
2581 /********** wxDbTable::GetNewCursor() **********/
2582 HSTMT
*wxDbTable::GetNewCursor(bool setCursor
, bool bindColumns
)
2584 HSTMT
*newHSTMT
= new HSTMT
;
2589 if (SQLAllocStmt(hdbc
, newHSTMT
) != SQL_SUCCESS
)
2591 pDb
->DispAllErrors(henv
, hdbc
);
2596 if (SQLSetStmtOption(*newHSTMT
, SQL_CURSOR_TYPE
, cursorType
) != SQL_SUCCESS
)
2598 pDb
->DispAllErrors(henv
, hdbc
, *newHSTMT
);
2605 if (!bindCols(*newHSTMT
))
2613 SetCursor(newHSTMT
);
2617 } // wxDbTable::GetNewCursor()
2620 /********** wxDbTable::DeleteCursor() **********/
2621 bool wxDbTable::DeleteCursor(HSTMT
*hstmtDel
)
2625 if (!hstmtDel
) // Cursor already deleted
2629 ODBC 3.0 says to use this form
2630 if (SQLFreeHandle(*hstmtDel, SQL_DROP) != SQL_SUCCESS)
2633 if (SQLFreeStmt(*hstmtDel
, SQL_DROP
) != SQL_SUCCESS
)
2635 pDb
->DispAllErrors(henv
, hdbc
);
2643 } // wxDbTable::DeleteCursor()
2645 //////////////////////////////////////////////////////////////
2646 // wxDbGrid support functions
2647 //////////////////////////////////////////////////////////////
2649 void wxDbTable::SetRowMode(const rowmode_t rowmode
)
2651 if (!m_hstmtGridQuery
)
2653 m_hstmtGridQuery
= GetNewCursor(FALSE
,FALSE
);
2654 if (!bindCols(*m_hstmtGridQuery
))
2658 m_rowmode
= rowmode
;
2661 case WX_ROW_MODE_QUERY
:
2662 SetCursor(m_hstmtGridQuery
);
2664 case WX_ROW_MODE_INDIVIDUAL
:
2665 SetCursor(hstmtDefault
);
2670 } // wxDbTable::SetRowMode()
2673 wxVariant
wxDbTable::GetCol(const int colNo
) const
2676 if ((colNo
< noCols
) && (!IsColNull(colNo
)))
2678 switch (colDefs
[colNo
].SqlCtype
)
2682 val
= (wxChar
*)(colDefs
[colNo
].PtrDataObj
);
2686 val
= *(long *)(colDefs
[colNo
].PtrDataObj
);
2690 val
= (long int )(*(short *)(colDefs
[colNo
].PtrDataObj
));
2693 val
= (long)(*(unsigned long *)(colDefs
[colNo
].PtrDataObj
));
2696 val
= (long)(*(wxChar
*)(colDefs
[colNo
].PtrDataObj
));
2698 case SQL_C_UTINYINT
:
2699 val
= (long)(*(wxChar
*)(colDefs
[colNo
].PtrDataObj
));
2702 val
= (long)(*(UWORD
*)(colDefs
[colNo
].PtrDataObj
));
2705 val
= (DATE_STRUCT
*)(colDefs
[colNo
].PtrDataObj
);
2708 val
= (TIME_STRUCT
*)(colDefs
[colNo
].PtrDataObj
);
2710 case SQL_C_TIMESTAMP
:
2711 val
= (TIMESTAMP_STRUCT
*)(colDefs
[colNo
].PtrDataObj
);
2714 val
= *(double *)(colDefs
[colNo
].PtrDataObj
);
2721 } // wxDbTable::GetCol()
2724 void wxDbTable::SetCol(const int colNo
, const wxVariant val
)
2726 //FIXME: Add proper wxDateTime support to wxVariant..
2729 SetColNull(colNo
, val
.IsNull());
2733 if ((colDefs
[colNo
].SqlCtype
== SQL_C_DATE
)
2734 || (colDefs
[colNo
].SqlCtype
== SQL_C_TIME
)
2735 || (colDefs
[colNo
].SqlCtype
== SQL_C_TIMESTAMP
))
2737 //Returns null if invalid!
2738 if (!dateval
.ParseDate(val
.GetString()))
2739 SetColNull(colNo
, TRUE
);
2742 switch (colDefs
[colNo
].SqlCtype
)
2746 csstrncpyt((wxChar
*)(colDefs
[colNo
].PtrDataObj
),
2747 val
.GetString().c_str(),
2748 colDefs
[colNo
].SzDataObj
-1);
2752 *(long *)(colDefs
[colNo
].PtrDataObj
) = val
;
2756 *(short *)(colDefs
[colNo
].PtrDataObj
) = val
.GetLong();
2759 *(unsigned long *)(colDefs
[colNo
].PtrDataObj
) = val
.GetLong();
2762 *(wxChar
*)(colDefs
[colNo
].PtrDataObj
) = val
.GetChar();
2764 case SQL_C_UTINYINT
:
2765 *(wxChar
*)(colDefs
[colNo
].PtrDataObj
) = val
.GetChar();
2768 *(unsigned short *)(colDefs
[colNo
].PtrDataObj
) = val
.GetLong();
2770 //FIXME: Add proper wxDateTime support to wxVariant..
2773 DATE_STRUCT
*dataptr
=
2774 (DATE_STRUCT
*)colDefs
[colNo
].PtrDataObj
;
2776 dataptr
->year
= dateval
.GetYear();
2777 dataptr
->month
= dateval
.GetMonth()+1;
2778 dataptr
->day
= dateval
.GetDay();
2783 TIME_STRUCT
*dataptr
=
2784 (TIME_STRUCT
*)colDefs
[colNo
].PtrDataObj
;
2786 dataptr
->hour
= dateval
.GetHour();
2787 dataptr
->minute
= dateval
.GetMinute();
2788 dataptr
->second
= dateval
.GetSecond();
2791 case SQL_C_TIMESTAMP
:
2793 TIMESTAMP_STRUCT
*dataptr
=
2794 (TIMESTAMP_STRUCT
*)colDefs
[colNo
].PtrDataObj
;
2795 dataptr
->year
= dateval
.GetYear();
2796 dataptr
->month
= dateval
.GetMonth()+1;
2797 dataptr
->day
= dateval
.GetDay();
2799 dataptr
->hour
= dateval
.GetHour();
2800 dataptr
->minute
= dateval
.GetMinute();
2801 dataptr
->second
= dateval
.GetSecond();
2805 *(double *)(colDefs
[colNo
].PtrDataObj
) = val
;
2810 } // if (!val.IsNull())
2811 } // wxDbTable::SetCol()
2814 GenericKey
wxDbTable::GetKey()
2819 blk
= malloc(m_keysize
);
2820 blkptr
= (wxChar
*) blk
;
2823 for (i
=0; i
< noCols
; i
++)
2825 if (colDefs
[i
].KeyField
)
2827 memcpy(blkptr
,colDefs
[i
].PtrDataObj
, colDefs
[i
].SzDataObj
);
2828 blkptr
+= colDefs
[i
].SzDataObj
;
2832 GenericKey k
= GenericKey(blk
, m_keysize
);
2836 } // wxDbTable::GetKey()
2839 void wxDbTable::SetKey(const GenericKey
& k
)
2845 blkptr
= (wxChar
*)blk
;
2848 for (i
=0; i
< noCols
; i
++)
2850 if (colDefs
[i
].KeyField
)
2852 SetColNull(i
, FALSE
);
2853 memcpy(colDefs
[i
].PtrDataObj
, blkptr
, colDefs
[i
].SzDataObj
);
2854 blkptr
+= colDefs
[i
].SzDataObj
;
2857 } // wxDbTable::SetKey()
2860 #endif // wxUSE_ODBC