1 ///////////////////////////////////////////////////////////////////////////////
2 // Name: src/common/dbtable.cpp
3 // Purpose: Implementation of the wxDbTable class.
5 // Modified by: George Tasker
10 // Copyright: (c) 1996 Remstar International, Inc.
11 // Licence: wxWindows licence
12 ///////////////////////////////////////////////////////////////////////////////
14 #include "wx/wxprec.h"
23 #include "wx/object.h"
24 #include "wx/string.h"
30 #ifdef DBDEBUG_CONSOLE
36 #include "wx/ioswrap.h"
39 #include "wx/filefn.h"
45 #include "wx/dbtable.h"
48 // The HPUX preprocessor lines below were commented out on 8/20/97
49 // because macros.h currently redefines DEBUG and is unneeded.
51 // # include <macros.h>
54 # include <sys/minmax.h>
58 ULONG lastTableID
= 0;
66 void csstrncpyt(wxChar
*target
, const wxChar
*source
, int n
)
68 while ( (*target
++ = *source
++) != '\0' && --n
)
76 /********** wxDbColDef::wxDbColDef() Constructor **********/
77 wxDbColDef::wxDbColDef()
83 bool wxDbColDef::Initialize()
86 DbDataType
= DB_DATA_TYPE_INTEGER
;
87 SqlCtype
= SQL_C_LONG
;
92 InsertAllowed
= false;
98 } // wxDbColDef::Initialize()
101 /********** wxDbTable::wxDbTable() Constructor **********/
102 wxDbTable::wxDbTable(wxDb
*pwxDb
, const wxString
&tblName
, const UWORD numColumns
,
103 const wxString
&qryTblName
, bool qryOnly
, const wxString
&tblPath
)
105 if (!initialize(pwxDb
, tblName
, numColumns
, qryTblName
, qryOnly
, tblPath
))
107 } // wxDbTable::wxDbTable()
110 /***** DEPRECATED: use wxDbTable::wxDbTable() format above *****/
111 #if WXWIN_COMPATIBILITY_2_4
112 wxDbTable::wxDbTable(wxDb
*pwxDb
, const wxString
&tblName
, const UWORD numColumns
,
113 const wxChar
*qryTblName
, bool qryOnly
, const wxString
&tblPath
)
115 wxString tempQryTblName
;
116 tempQryTblName
= qryTblName
;
117 if (!initialize(pwxDb
, tblName
, numColumns
, tempQryTblName
, qryOnly
, tblPath
))
119 } // wxDbTable::wxDbTable()
120 #endif // WXWIN_COMPATIBILITY_2_4
123 /********** wxDbTable::~wxDbTable() **********/
124 wxDbTable::~wxDbTable()
127 } // wxDbTable::~wxDbTable()
130 bool wxDbTable::initialize(wxDb
*pwxDb
, const wxString
&tblName
, const UWORD numColumns
,
131 const wxString
&qryTblName
, bool qryOnly
, const wxString
&tblPath
)
133 // Initializing member variables
134 pDb
= pwxDb
; // Pointer to the wxDb object
138 m_hstmtGridQuery
= 0;
139 hstmtDefault
= 0; // Initialized below
140 hstmtCount
= 0; // Initialized first time it is needed
147 m_numCols
= numColumns
; // Number of columns in the table
148 where
.Empty(); // Where clause
149 orderBy
.Empty(); // Order By clause
150 from
.Empty(); // From clause
151 selectForUpdate
= false; // SELECT ... FOR UPDATE; Indicates whether to include the FOR UPDATE phrase
156 queryTableName
.Empty();
158 wxASSERT(tblName
.length());
164 tableName
= tblName
; // Table Name
165 if ((pDb
->Dbms() == dbmsORACLE
) ||
166 (pDb
->Dbms() == dbmsFIREBIRD
) ||
167 (pDb
->Dbms() == dbmsINTERBASE
))
168 tableName
= tableName
.Upper();
170 if (tblPath
.length())
171 tablePath
= tblPath
; // Table Path - used for dBase files
175 if (qryTblName
.length()) // Name of the table/view to query
176 queryTableName
= qryTblName
;
178 queryTableName
= tblName
;
180 if ((pDb
->Dbms() == dbmsORACLE
) ||
181 (pDb
->Dbms() == dbmsFIREBIRD
) ||
182 (pDb
->Dbms() == dbmsINTERBASE
))
183 queryTableName
= queryTableName
.Upper();
185 pDb
->incrementTableCount();
188 tableID
= ++lastTableID
;
189 s
.Printf(wxT("wxDbTable constructor (%-20s) tableID:[%6lu] pDb:[%p]"), tblName
.c_str(), tableID
, pDb
);
192 wxTablesInUse
*tableInUse
;
193 tableInUse
= new wxTablesInUse();
194 tableInUse
->tableName
= tblName
;
195 tableInUse
->tableID
= tableID
;
196 tableInUse
->pDb
= pDb
;
197 TablesInUse
.Append(tableInUse
);
202 // Grab the HENV and HDBC from the wxDb object
203 henv
= pDb
->GetHENV();
204 hdbc
= pDb
->GetHDBC();
206 // Allocate space for column definitions
208 colDefs
= new wxDbColDef
[m_numCols
]; // Points to the first column definition
210 // Allocate statement handles for the table
213 // Allocate a separate statement handle for performing inserts
214 if (SQLAllocStmt(hdbc
, &hstmtInsert
) != SQL_SUCCESS
)
215 pDb
->DispAllErrors(henv
, hdbc
);
216 // Allocate a separate statement handle for performing deletes
217 if (SQLAllocStmt(hdbc
, &hstmtDelete
) != SQL_SUCCESS
)
218 pDb
->DispAllErrors(henv
, hdbc
);
219 // Allocate a separate statement handle for performing updates
220 if (SQLAllocStmt(hdbc
, &hstmtUpdate
) != SQL_SUCCESS
)
221 pDb
->DispAllErrors(henv
, hdbc
);
223 // Allocate a separate statement handle for internal use
224 if (SQLAllocStmt(hdbc
, &hstmtInternal
) != SQL_SUCCESS
)
225 pDb
->DispAllErrors(henv
, hdbc
);
227 // Set the cursor type for the statement handles
228 cursorType
= SQL_CURSOR_STATIC
;
230 if (SQLSetStmtOption(hstmtInternal
, SQL_CURSOR_TYPE
, cursorType
) != SQL_SUCCESS
)
232 // Check to see if cursor type is supported
233 pDb
->GetNextError(henv
, hdbc
, hstmtInternal
);
234 if (! wxStrcmp(pDb
->sqlState
, wxT("01S02"))) // Option Value Changed
236 // Datasource does not support static cursors. Driver
237 // will substitute a cursor type. Call SQLGetStmtOption()
238 // to determine which cursor type was selected.
239 if (SQLGetStmtOption(hstmtInternal
, SQL_CURSOR_TYPE
, &cursorType
) != SQL_SUCCESS
)
240 pDb
->DispAllErrors(henv
, hdbc
, hstmtInternal
);
241 #ifdef DBDEBUG_CONSOLE
242 cout
<< wxT("Static cursor changed to: ");
245 case SQL_CURSOR_FORWARD_ONLY
:
246 cout
<< wxT("Forward Only");
248 case SQL_CURSOR_STATIC
:
249 cout
<< wxT("Static");
251 case SQL_CURSOR_KEYSET_DRIVEN
:
252 cout
<< wxT("Keyset Driven");
254 case SQL_CURSOR_DYNAMIC
:
255 cout
<< wxT("Dynamic");
258 cout
<< endl
<< endl
;
261 if (pDb
->FwdOnlyCursors() && cursorType
!= SQL_CURSOR_FORWARD_ONLY
)
263 // Force the use of a forward only cursor...
264 cursorType
= SQL_CURSOR_FORWARD_ONLY
;
265 if (SQLSetStmtOption(hstmtInternal
, SQL_CURSOR_TYPE
, cursorType
) != SQL_SUCCESS
)
267 // Should never happen
268 pDb
->GetNextError(henv
, hdbc
, hstmtInternal
);
275 pDb
->DispNextError();
276 pDb
->DispAllErrors(henv
, hdbc
, hstmtInternal
);
279 #ifdef DBDEBUG_CONSOLE
281 cout
<< wxT("Cursor Type set to STATIC") << endl
<< endl
;
286 // Set the cursor type for the INSERT statement handle
287 if (SQLSetStmtOption(hstmtInsert
, SQL_CURSOR_TYPE
, SQL_CURSOR_FORWARD_ONLY
) != SQL_SUCCESS
)
288 pDb
->DispAllErrors(henv
, hdbc
, hstmtInsert
);
289 // Set the cursor type for the DELETE statement handle
290 if (SQLSetStmtOption(hstmtDelete
, SQL_CURSOR_TYPE
, SQL_CURSOR_FORWARD_ONLY
) != SQL_SUCCESS
)
291 pDb
->DispAllErrors(henv
, hdbc
, hstmtDelete
);
292 // Set the cursor type for the UPDATE statement handle
293 if (SQLSetStmtOption(hstmtUpdate
, SQL_CURSOR_TYPE
, SQL_CURSOR_FORWARD_ONLY
) != SQL_SUCCESS
)
294 pDb
->DispAllErrors(henv
, hdbc
, hstmtUpdate
);
297 // Make the default cursor the active cursor
298 hstmtDefault
= GetNewCursor(false,false);
299 wxASSERT(hstmtDefault
);
300 hstmt
= *hstmtDefault
;
304 } // wxDbTable::initialize()
307 void wxDbTable::cleanup()
312 s
.Printf(wxT("wxDbTable destructor (%-20s) tableID:[%6lu] pDb:[%p]"), tableName
.c_str(), tableID
, pDb
);
321 wxList::compatibility_iterator pNode
;
322 pNode
= TablesInUse
.GetFirst();
323 while (pNode
&& !found
)
325 if (((wxTablesInUse
*)pNode
->GetData())->tableID
== tableID
)
328 delete (wxTablesInUse
*)pNode
->GetData();
329 TablesInUse
.Erase(pNode
);
332 pNode
= pNode
->GetNext();
337 msg
.Printf(wxT("Unable to find the tableID in the linked\nlist of tables in use.\n\n%s"),s
.c_str());
338 wxLogDebug (msg
,wxT("NOTICE..."));
343 // Decrement the wxDb table count
345 pDb
->decrementTableCount();
347 // Delete memory allocated for column definitions
351 // Free statement handles
357 ODBC 3.0 says to use this form
358 if (SQLFreeHandle(*hstmtDel, SQL_DROP) != SQL_SUCCESS)
360 if (SQLFreeStmt(hstmtInsert
, SQL_DROP
) != SQL_SUCCESS
)
361 pDb
->DispAllErrors(henv
, hdbc
);
367 ODBC 3.0 says to use this form
368 if (SQLFreeHandle(*hstmtDel, SQL_DROP) != SQL_SUCCESS)
370 if (SQLFreeStmt(hstmtDelete
, SQL_DROP
) != SQL_SUCCESS
)
371 pDb
->DispAllErrors(henv
, hdbc
);
377 ODBC 3.0 says to use this form
378 if (SQLFreeHandle(*hstmtDel, SQL_DROP) != SQL_SUCCESS)
380 if (SQLFreeStmt(hstmtUpdate
, SQL_DROP
) != SQL_SUCCESS
)
381 pDb
->DispAllErrors(henv
, hdbc
);
387 if (SQLFreeStmt(hstmtInternal
, SQL_DROP
) != SQL_SUCCESS
)
388 pDb
->DispAllErrors(henv
, hdbc
);
391 // Delete dynamically allocated cursors
393 DeleteCursor(hstmtDefault
);
396 DeleteCursor(hstmtCount
);
398 if (m_hstmtGridQuery
)
399 DeleteCursor(m_hstmtGridQuery
);
401 } // wxDbTable::cleanup()
404 /***************************** PRIVATE FUNCTIONS *****************************/
407 void wxDbTable::setCbValueForColumn(int columnIndex
)
409 switch(colDefs
[columnIndex
].DbDataType
)
411 case DB_DATA_TYPE_VARCHAR
:
412 case DB_DATA_TYPE_MEMO
:
413 if (colDefs
[columnIndex
].Null
)
414 colDefs
[columnIndex
].CbValue
= SQL_NULL_DATA
;
416 colDefs
[columnIndex
].CbValue
= SQL_NTS
;
418 case DB_DATA_TYPE_INTEGER
:
419 if (colDefs
[columnIndex
].Null
)
420 colDefs
[columnIndex
].CbValue
= SQL_NULL_DATA
;
422 colDefs
[columnIndex
].CbValue
= 0;
424 case DB_DATA_TYPE_FLOAT
:
425 if (colDefs
[columnIndex
].Null
)
426 colDefs
[columnIndex
].CbValue
= SQL_NULL_DATA
;
428 colDefs
[columnIndex
].CbValue
= 0;
430 case DB_DATA_TYPE_DATE
:
431 if (colDefs
[columnIndex
].Null
)
432 colDefs
[columnIndex
].CbValue
= SQL_NULL_DATA
;
434 colDefs
[columnIndex
].CbValue
= 0;
436 case DB_DATA_TYPE_BLOB
:
437 if (colDefs
[columnIndex
].Null
)
438 colDefs
[columnIndex
].CbValue
= SQL_NULL_DATA
;
440 if (colDefs
[columnIndex
].SqlCtype
== SQL_C_WXCHAR
)
441 colDefs
[columnIndex
].CbValue
= SQL_NTS
;
443 colDefs
[columnIndex
].CbValue
= SQL_LEN_DATA_AT_EXEC(colDefs
[columnIndex
].SzDataObj
);
448 /********** wxDbTable::bindParams() **********/
449 bool wxDbTable::bindParams(bool forUpdate
)
451 wxASSERT(!queryOnly
);
456 SDWORD precision
= 0;
459 // Bind each column of the table that should be bound
460 // to a parameter marker
464 for (i
=0, colNumber
=1; i
< m_numCols
; i
++)
468 if (!colDefs
[i
].Updateable
)
473 if (!colDefs
[i
].InsertAllowed
)
477 switch(colDefs
[i
].DbDataType
)
479 case DB_DATA_TYPE_VARCHAR
:
480 fSqlType
= pDb
->GetTypeInfVarchar().FsqlType
;
481 precision
= colDefs
[i
].SzDataObj
;
484 case DB_DATA_TYPE_MEMO
:
485 fSqlType
= pDb
->GetTypeInfMemo().FsqlType
;
486 precision
= colDefs
[i
].SzDataObj
;
489 case DB_DATA_TYPE_INTEGER
:
490 fSqlType
= pDb
->GetTypeInfInteger().FsqlType
;
491 precision
= pDb
->GetTypeInfInteger().Precision
;
494 case DB_DATA_TYPE_FLOAT
:
495 fSqlType
= pDb
->GetTypeInfFloat().FsqlType
;
496 precision
= pDb
->GetTypeInfFloat().Precision
;
497 scale
= pDb
->GetTypeInfFloat().MaximumScale
;
498 // SQL Sybase Anywhere v5.5 returned a negative number for the
499 // MaxScale. This caused ODBC to kick out an error on ibscale.
500 // I check for this here and set the scale = precision.
502 // scale = (short) precision;
504 case DB_DATA_TYPE_DATE
:
505 fSqlType
= pDb
->GetTypeInfDate().FsqlType
;
506 precision
= pDb
->GetTypeInfDate().Precision
;
509 case DB_DATA_TYPE_BLOB
:
510 fSqlType
= pDb
->GetTypeInfBlob().FsqlType
;
511 precision
= colDefs
[i
].SzDataObj
;
516 setCbValueForColumn(i
);
520 if (SQLBindParameter(hstmtUpdate
, colNumber
++, SQL_PARAM_INPUT
, colDefs
[i
].SqlCtype
,
521 fSqlType
, precision
, scale
, (UCHAR
*) colDefs
[i
].PtrDataObj
,
522 precision
+1, &colDefs
[i
].CbValue
) != SQL_SUCCESS
)
524 return(pDb
->DispAllErrors(henv
, hdbc
, hstmtUpdate
));
529 if (SQLBindParameter(hstmtInsert
, colNumber
++, SQL_PARAM_INPUT
, colDefs
[i
].SqlCtype
,
530 fSqlType
, precision
, scale
, (UCHAR
*) colDefs
[i
].PtrDataObj
,
531 precision
+1, &colDefs
[i
].CbValue
) != SQL_SUCCESS
)
533 return(pDb
->DispAllErrors(henv
, hdbc
, hstmtInsert
));
538 // Completed successfully
541 } // wxDbTable::bindParams()
544 /********** wxDbTable::bindInsertParams() **********/
545 bool wxDbTable::bindInsertParams(void)
547 return bindParams(false);
548 } // wxDbTable::bindInsertParams()
551 /********** wxDbTable::bindUpdateParams() **********/
552 bool wxDbTable::bindUpdateParams(void)
554 return bindParams(true);
555 } // wxDbTable::bindUpdateParams()
558 /********** wxDbTable::bindCols() **********/
559 bool wxDbTable::bindCols(HSTMT cursor
)
563 // Bind each column of the table to a memory address for fetching data
565 for (i
= 0; i
< m_numCols
; i
++)
567 cb
= colDefs
[i
].CbValue
;
568 if (SQLBindCol(cursor
, (UWORD
)(i
+1), colDefs
[i
].SqlCtype
, (UCHAR
*) colDefs
[i
].PtrDataObj
,
569 colDefs
[i
].SzDataObj
, &cb
) != SQL_SUCCESS
)
570 return (pDb
->DispAllErrors(henv
, hdbc
, cursor
));
573 // Completed successfully
576 } // wxDbTable::bindCols()
579 /********** wxDbTable::getRec() **********/
580 bool wxDbTable::getRec(UWORD fetchType
)
584 if (!pDb
->FwdOnlyCursors())
586 // Fetch the NEXT, PREV, FIRST or LAST record, depending on fetchType
587 SQLULEN cRowsFetched
;
590 retcode
= SQLExtendedFetch(hstmt
, fetchType
, 0, &cRowsFetched
, &rowStatus
);
591 if (retcode
!= SQL_SUCCESS
&& retcode
!= SQL_SUCCESS_WITH_INFO
)
593 if (retcode
== SQL_NO_DATA_FOUND
)
596 return(pDb
->DispAllErrors(henv
, hdbc
, hstmt
));
600 // Set the Null member variable to indicate the Null state
601 // of each column just read in.
603 for (i
= 0; i
< m_numCols
; i
++)
604 colDefs
[i
].Null
= (colDefs
[i
].CbValue
== SQL_NULL_DATA
);
609 // Fetch the next record from the record set
610 retcode
= SQLFetch(hstmt
);
611 if (retcode
!= SQL_SUCCESS
&& retcode
!= SQL_SUCCESS_WITH_INFO
)
613 if (retcode
== SQL_NO_DATA_FOUND
)
616 return(pDb
->DispAllErrors(henv
, hdbc
, hstmt
));
620 // Set the Null member variable to indicate the Null state
621 // of each column just read in.
623 for (i
= 0; i
< m_numCols
; i
++)
624 colDefs
[i
].Null
= (colDefs
[i
].CbValue
== SQL_NULL_DATA
);
628 // Completed successfully
631 } // wxDbTable::getRec()
634 /********** wxDbTable::execDelete() **********/
635 bool wxDbTable::execDelete(const wxString
&pSqlStmt
)
639 // Execute the DELETE statement
640 retcode
= SQLExecDirect(hstmtDelete
, (SQLTCHAR FAR
*) pSqlStmt
.c_str(), SQL_NTS
);
642 if (retcode
== SQL_SUCCESS
||
643 retcode
== SQL_NO_DATA_FOUND
||
644 retcode
== SQL_SUCCESS_WITH_INFO
)
646 // Record deleted successfully
650 // Problem deleting record
651 return(pDb
->DispAllErrors(henv
, hdbc
, hstmtDelete
));
653 } // wxDbTable::execDelete()
656 /********** wxDbTable::execUpdate() **********/
657 bool wxDbTable::execUpdate(const wxString
&pSqlStmt
)
661 // Execute the UPDATE statement
662 retcode
= SQLExecDirect(hstmtUpdate
, (SQLTCHAR FAR
*) pSqlStmt
.c_str(), SQL_NTS
);
664 if (retcode
== SQL_SUCCESS
||
665 retcode
== SQL_NO_DATA_FOUND
||
666 retcode
== SQL_SUCCESS_WITH_INFO
)
668 // Record updated successfully
671 else if (retcode
== SQL_NEED_DATA
)
674 retcode
= SQLParamData(hstmtUpdate
, &pParmID
);
675 while (retcode
== SQL_NEED_DATA
)
677 // Find the parameter
679 for (i
=0; i
< m_numCols
; i
++)
681 if (colDefs
[i
].PtrDataObj
== pParmID
)
683 // We found it. Store the parameter.
684 retcode
= SQLPutData(hstmtUpdate
, pParmID
, colDefs
[i
].SzDataObj
);
685 if (retcode
!= SQL_SUCCESS
)
687 pDb
->DispNextError();
688 return pDb
->DispAllErrors(henv
, hdbc
, hstmtUpdate
);
693 retcode
= SQLParamData(hstmtUpdate
, &pParmID
);
695 if (retcode
== SQL_SUCCESS
||
696 retcode
== SQL_NO_DATA_FOUND
||
697 retcode
== SQL_SUCCESS_WITH_INFO
)
699 // Record updated successfully
704 // Problem updating record
705 return(pDb
->DispAllErrors(henv
, hdbc
, hstmtUpdate
));
707 } // wxDbTable::execUpdate()
710 /********** wxDbTable::query() **********/
711 bool wxDbTable::query(int queryType
, bool forUpdate
, bool distinct
, const wxString
&pSqlStmt
)
716 // The user may wish to select for update, but the DBMS may not be capable
717 selectForUpdate
= CanSelectForUpdate();
719 selectForUpdate
= false;
721 // Set the SQL SELECT string
722 if (queryType
!= DB_SELECT_STATEMENT
) // A select statement was not passed in,
723 { // so generate a select statement.
724 BuildSelectStmt(sqlStmt
, queryType
, distinct
);
725 pDb
->WriteSqlLog(sqlStmt
);
728 // Make sure the cursor is closed first
729 if (!CloseCursor(hstmt
))
732 // Execute the SQL SELECT statement
734 retcode
= SQLExecDirect(hstmt
, (SQLTCHAR FAR
*) (queryType
== DB_SELECT_STATEMENT
? pSqlStmt
.c_str() : sqlStmt
.c_str()), SQL_NTS
);
735 if (retcode
!= SQL_SUCCESS
&& retcode
!= SQL_SUCCESS_WITH_INFO
)
736 return(pDb
->DispAllErrors(henv
, hdbc
, hstmt
));
738 // Completed successfully
741 } // wxDbTable::query()
744 /***************************** PUBLIC FUNCTIONS *****************************/
747 /********** wxDbTable::Open() **********/
748 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
< m_numCols
; i
++)
762 if (colDefs
[i
].KeyField
)
764 m_keysize
+= colDefs
[i
].SzDataObj
;
771 if (checkTableExists
)
773 if (pDb
->Dbms() == dbmsPOSTGRES
)
774 exists
= pDb
->TableExists(tableName
, NULL
, tablePath
);
776 exists
= pDb
->TableExists(tableName
, pDb
->GetUsername(), tablePath
);
779 // Verify that the table exists in the database
782 s
= wxT("Table/view does not exist in the database");
783 if ( *(pDb
->dbInf
.accessibleTables
) == wxT('Y'))
784 s
+= wxT(", or you have no permissions.\n");
788 else if (checkPrivileges
)
790 // Verify the user has rights to access the table.
791 bool hasPrivs
wxDUMMY_INITIALIZE(true);
793 if (pDb
->Dbms() == dbmsPOSTGRES
)
794 hasPrivs
= pDb
->TablePrivileges(tableName
, wxT("SELECT"), pDb
->GetUsername(), NULL
, tablePath
);
796 hasPrivs
= pDb
->TablePrivileges(tableName
, wxT("SELECT"), pDb
->GetUsername(), pDb
->GetUsername(), tablePath
);
799 s
= wxT("Connecting user does not have sufficient privileges to access this table.\n");
806 if (!tablePath
.empty())
807 p
.Printf(wxT("Error opening '%s/%s'.\n"),tablePath
.c_str(),tableName
.c_str());
809 p
.Printf(wxT("Error opening '%s'.\n"), tableName
.c_str());
812 pDb
->LogError(p
.GetData());
817 // Bind the member variables for field exchange between
818 // the wxDbTable object and the ODBC record.
821 if (!bindInsertParams()) // Inserts
824 if (!bindUpdateParams()) // Updates
828 if (!bindCols(*hstmtDefault
)) // Selects
831 if (!bindCols(hstmtInternal
)) // Internal use only
835 * Do NOT bind the hstmtCount cursor!!!
838 // Build an insert statement using parameter markers
839 if (!queryOnly
&& m_numCols
> 0)
841 bool needComma
= false;
842 sqlStmt
.Printf(wxT("INSERT INTO %s ("),
843 pDb
->SQLTableName(tableName
.c_str()).c_str());
844 for (i
= 0; i
< m_numCols
; i
++)
846 if (! colDefs
[i
].InsertAllowed
)
850 sqlStmt
+= pDb
->SQLColumnName(colDefs
[i
].ColName
);
854 sqlStmt
+= wxT(") VALUES (");
856 int insertableCount
= 0;
858 for (i
= 0; i
< m_numCols
; i
++)
860 if (! colDefs
[i
].InsertAllowed
)
870 // Prepare the insert statement for execution
873 if (SQLPrepare(hstmtInsert
, (SQLTCHAR FAR
*) sqlStmt
.c_str(), SQL_NTS
) != SQL_SUCCESS
)
874 return(pDb
->DispAllErrors(henv
, hdbc
, hstmtInsert
));
880 // Completed successfully
883 } // wxDbTable::Open()
886 /********** wxDbTable::Query() **********/
887 bool wxDbTable::Query(bool forUpdate
, bool distinct
)
890 return(query(DB_SELECT_WHERE
, forUpdate
, distinct
));
892 } // wxDbTable::Query()
895 /********** wxDbTable::QueryBySqlStmt() **********/
896 bool wxDbTable::QueryBySqlStmt(const wxString
&pSqlStmt
)
898 pDb
->WriteSqlLog(pSqlStmt
);
900 return(query(DB_SELECT_STATEMENT
, false, false, pSqlStmt
));
902 } // wxDbTable::QueryBySqlStmt()
905 /********** wxDbTable::QueryMatching() **********/
906 bool wxDbTable::QueryMatching(bool forUpdate
, bool distinct
)
909 return(query(DB_SELECT_MATCHING
, forUpdate
, distinct
));
911 } // wxDbTable::QueryMatching()
914 /********** wxDbTable::QueryOnKeyFields() **********/
915 bool wxDbTable::QueryOnKeyFields(bool forUpdate
, bool distinct
)
918 return(query(DB_SELECT_KEYFIELDS
, forUpdate
, distinct
));
920 } // wxDbTable::QueryOnKeyFields()
923 /********** wxDbTable::GetPrev() **********/
924 bool wxDbTable::GetPrev(void)
926 if (pDb
->FwdOnlyCursors())
928 wxFAIL_MSG(wxT("GetPrev()::Backward scrolling cursors are not enabled for this instance of wxDbTable"));
932 return(getRec(SQL_FETCH_PRIOR
));
934 } // wxDbTable::GetPrev()
937 /********** wxDbTable::operator-- **********/
938 bool wxDbTable::operator--(int)
940 if (pDb
->FwdOnlyCursors())
942 wxFAIL_MSG(wxT("operator--:Backward scrolling cursors are not enabled for this instance of wxDbTable"));
946 return(getRec(SQL_FETCH_PRIOR
));
948 } // wxDbTable::operator--
951 /********** wxDbTable::GetFirst() **********/
952 bool wxDbTable::GetFirst(void)
954 if (pDb
->FwdOnlyCursors())
956 wxFAIL_MSG(wxT("GetFirst():Backward scrolling cursors are not enabled for this instance of wxDbTable"));
960 return(getRec(SQL_FETCH_FIRST
));
962 } // wxDbTable::GetFirst()
965 /********** wxDbTable::GetLast() **********/
966 bool wxDbTable::GetLast(void)
968 if (pDb
->FwdOnlyCursors())
970 wxFAIL_MSG(wxT("GetLast()::Backward scrolling cursors are not enabled for this instance of wxDbTable"));
974 return(getRec(SQL_FETCH_LAST
));
976 } // wxDbTable::GetLast()
979 /********** wxDbTable::BuildDeleteStmt() **********/
980 void wxDbTable::BuildDeleteStmt(wxString
&pSqlStmt
, int typeOfDel
, const wxString
&pWhereClause
)
982 wxASSERT(!queryOnly
);
986 wxString whereClause
;
990 // Handle the case of DeleteWhere() and the where clause is blank. It should
991 // delete all records from the database in this case.
992 if (typeOfDel
== DB_DEL_WHERE
&& (pWhereClause
.length() == 0))
994 pSqlStmt
.Printf(wxT("DELETE FROM %s"),
995 pDb
->SQLTableName(tableName
.c_str()).c_str());
999 pSqlStmt
.Printf(wxT("DELETE FROM %s WHERE "),
1000 pDb
->SQLTableName(tableName
.c_str()).c_str());
1002 // Append the WHERE clause to the SQL DELETE statement
1005 case DB_DEL_KEYFIELDS
:
1006 // If the datasource supports the ROWID column, build
1007 // the where on ROWID for efficiency purposes.
1008 // e.g. DELETE FROM PARTS WHERE ROWID = '111.222.333'
1009 if (CanUpdateByROWID())
1012 wxChar rowid
[wxDB_ROWID_LEN
+1];
1014 // Get the ROWID value. If not successful retreiving the ROWID,
1015 // simply fall down through the code and build the WHERE clause
1016 // based on the key fields.
1017 if (SQLGetData(hstmt
, (UWORD
)(m_numCols
+1), SQL_C_WXCHAR
, (UCHAR
*) rowid
, sizeof(rowid
), &cb
) == SQL_SUCCESS
)
1019 pSqlStmt
+= wxT("ROWID = '");
1021 pSqlStmt
+= wxT("'");
1025 // Unable to delete by ROWID, so build a WHERE
1026 // clause based on the keyfields.
1027 BuildWhereClause(whereClause
, DB_WHERE_KEYFIELDS
);
1028 pSqlStmt
+= whereClause
;
1031 pSqlStmt
+= pWhereClause
;
1033 case DB_DEL_MATCHING
:
1034 BuildWhereClause(whereClause
, DB_WHERE_MATCHING
);
1035 pSqlStmt
+= whereClause
;
1039 } // BuildDeleteStmt()
1042 /***** DEPRECATED: use wxDbTable::BuildDeleteStmt(wxString &....) form *****/
1043 void wxDbTable::BuildDeleteStmt(wxChar
*pSqlStmt
, int typeOfDel
, const wxString
&pWhereClause
)
1045 wxString tempSqlStmt
;
1046 BuildDeleteStmt(tempSqlStmt
, typeOfDel
, pWhereClause
);
1047 wxStrcpy(pSqlStmt
, tempSqlStmt
);
1048 } // wxDbTable::BuildDeleteStmt()
1051 /********** wxDbTable::BuildSelectStmt() **********/
1052 void wxDbTable::BuildSelectStmt(wxString
&pSqlStmt
, int typeOfSelect
, bool distinct
)
1054 wxString whereClause
;
1055 whereClause
.Empty();
1057 // Build a select statement to query the database
1058 pSqlStmt
= wxT("SELECT ");
1060 // SELECT DISTINCT values only?
1062 pSqlStmt
+= wxT("DISTINCT ");
1064 // Was a FROM clause specified to join tables to the base table?
1065 // Available for ::Query() only!!!
1066 bool appendFromClause
= false;
1067 #if wxODBC_BACKWARD_COMPATABILITY
1068 if (typeOfSelect
== DB_SELECT_WHERE
&& from
&& wxStrlen(from
))
1069 appendFromClause
= true;
1071 if (typeOfSelect
== DB_SELECT_WHERE
&& from
.length())
1072 appendFromClause
= true;
1075 // Add the column list
1078 for (i
= 0; i
< m_numCols
; i
++)
1080 tStr
= colDefs
[i
].ColName
;
1081 // If joining tables, the base table column names must be qualified to avoid ambiguity
1082 if ((appendFromClause
|| pDb
->Dbms() == dbmsACCESS
) && tStr
.Find(wxT('.')) == wxNOT_FOUND
)
1084 pSqlStmt
+= pDb
->SQLTableName(queryTableName
.c_str());
1085 pSqlStmt
+= wxT(".");
1087 pSqlStmt
+= pDb
->SQLColumnName(colDefs
[i
].ColName
);
1088 if (i
+ 1 < m_numCols
)
1089 pSqlStmt
+= wxT(",");
1092 // If the datasource supports ROWID, get this column as well. Exception: Don't retrieve
1093 // the ROWID if querying distinct records. The rowid will always be unique.
1094 if (!distinct
&& CanUpdateByROWID())
1096 // If joining tables, the base table column names must be qualified to avoid ambiguity
1097 if (appendFromClause
|| pDb
->Dbms() == dbmsACCESS
)
1099 pSqlStmt
+= wxT(",");
1100 pSqlStmt
+= pDb
->SQLTableName(queryTableName
);
1101 pSqlStmt
+= wxT(".ROWID");
1104 pSqlStmt
+= wxT(",ROWID");
1107 // Append the FROM tablename portion
1108 pSqlStmt
+= wxT(" FROM ");
1109 pSqlStmt
+= pDb
->SQLTableName(queryTableName
);
1110 // pSqlStmt += queryTableName;
1112 // Sybase uses the HOLDLOCK keyword to lock a record during query.
1113 // The HOLDLOCK keyword follows the table name in the from clause.
1114 // Each table in the from clause must specify HOLDLOCK or
1115 // NOHOLDLOCK (the default). Note: The "FOR UPDATE" clause
1116 // is parsed but ignored in SYBASE Transact-SQL.
1117 if (selectForUpdate
&& (pDb
->Dbms() == dbmsSYBASE_ASA
|| pDb
->Dbms() == dbmsSYBASE_ASE
))
1118 pSqlStmt
+= wxT(" HOLDLOCK");
1120 if (appendFromClause
)
1123 // Append the WHERE clause. Either append the where clause for the class
1124 // or build a where clause. The typeOfSelect determines this.
1125 switch(typeOfSelect
)
1127 case DB_SELECT_WHERE
:
1128 #if wxODBC_BACKWARD_COMPATABILITY
1129 if (where
&& wxStrlen(where
)) // May not want a where clause!!!
1131 if (where
.length()) // May not want a where clause!!!
1134 pSqlStmt
+= wxT(" WHERE ");
1138 case DB_SELECT_KEYFIELDS
:
1139 BuildWhereClause(whereClause
, DB_WHERE_KEYFIELDS
);
1140 if (whereClause
.length())
1142 pSqlStmt
+= wxT(" WHERE ");
1143 pSqlStmt
+= whereClause
;
1146 case DB_SELECT_MATCHING
:
1147 BuildWhereClause(whereClause
, DB_WHERE_MATCHING
);
1148 if (whereClause
.length())
1150 pSqlStmt
+= wxT(" WHERE ");
1151 pSqlStmt
+= whereClause
;
1156 // Append the ORDER BY clause
1157 #if wxODBC_BACKWARD_COMPATABILITY
1158 if (orderBy
&& wxStrlen(orderBy
))
1160 if (orderBy
.length())
1163 pSqlStmt
+= wxT(" ORDER BY ");
1164 pSqlStmt
+= orderBy
;
1167 // SELECT FOR UPDATE if told to do so and the datasource is capable. Sybase
1168 // parses the FOR UPDATE clause but ignores it. See the comment above on the
1169 // HOLDLOCK for Sybase.
1170 if (selectForUpdate
&& CanSelectForUpdate())
1171 pSqlStmt
+= wxT(" FOR UPDATE");
1173 } // wxDbTable::BuildSelectStmt()
1176 /***** DEPRECATED: use wxDbTable::BuildSelectStmt(wxString &....) form *****/
1177 void wxDbTable::BuildSelectStmt(wxChar
*pSqlStmt
, int typeOfSelect
, bool distinct
)
1179 wxString tempSqlStmt
;
1180 BuildSelectStmt(tempSqlStmt
, typeOfSelect
, distinct
);
1181 wxStrcpy(pSqlStmt
, tempSqlStmt
);
1182 } // wxDbTable::BuildSelectStmt()
1185 /********** wxDbTable::BuildUpdateStmt() **********/
1186 void wxDbTable::BuildUpdateStmt(wxString
&pSqlStmt
, int typeOfUpdate
, const wxString
&pWhereClause
)
1188 wxASSERT(!queryOnly
);
1192 wxString whereClause
;
1193 whereClause
.Empty();
1195 bool firstColumn
= true;
1197 pSqlStmt
.Printf(wxT("UPDATE %s SET "),
1198 pDb
->SQLTableName(tableName
.c_str()).c_str());
1200 // Append a list of columns to be updated
1202 for (i
= 0; i
< m_numCols
; i
++)
1204 // Only append Updateable columns
1205 if (colDefs
[i
].Updateable
)
1208 pSqlStmt
+= wxT(",");
1210 firstColumn
= false;
1212 pSqlStmt
+= pDb
->SQLColumnName(colDefs
[i
].ColName
);
1213 // pSqlStmt += colDefs[i].ColName;
1214 pSqlStmt
+= wxT(" = ?");
1218 // Append the WHERE clause to the SQL UPDATE statement
1219 pSqlStmt
+= wxT(" WHERE ");
1220 switch(typeOfUpdate
)
1222 case DB_UPD_KEYFIELDS
:
1223 // If the datasource supports the ROWID column, build
1224 // the where on ROWID for efficiency purposes.
1225 // e.g. UPDATE PARTS SET Col1 = ?, Col2 = ? WHERE ROWID = '111.222.333'
1226 if (CanUpdateByROWID())
1229 wxChar rowid
[wxDB_ROWID_LEN
+1];
1231 // Get the ROWID value. If not successful retreiving the ROWID,
1232 // simply fall down through the code and build the WHERE clause
1233 // based on the key fields.
1234 if (SQLGetData(hstmt
, (UWORD
)(m_numCols
+1), SQL_C_WXCHAR
, (UCHAR
*) rowid
, sizeof(rowid
), &cb
) == SQL_SUCCESS
)
1236 pSqlStmt
+= wxT("ROWID = '");
1238 pSqlStmt
+= wxT("'");
1242 // Unable to delete by ROWID, so build a WHERE
1243 // clause based on the keyfields.
1244 BuildWhereClause(whereClause
, DB_WHERE_KEYFIELDS
);
1245 pSqlStmt
+= whereClause
;
1248 pSqlStmt
+= pWhereClause
;
1251 } // BuildUpdateStmt()
1254 /***** DEPRECATED: use wxDbTable::BuildUpdateStmt(wxString &....) form *****/
1255 void wxDbTable::BuildUpdateStmt(wxChar
*pSqlStmt
, int typeOfUpdate
, const wxString
&pWhereClause
)
1257 wxString tempSqlStmt
;
1258 BuildUpdateStmt(tempSqlStmt
, typeOfUpdate
, pWhereClause
);
1259 wxStrcpy(pSqlStmt
, tempSqlStmt
);
1260 } // BuildUpdateStmt()
1263 /********** wxDbTable::BuildWhereClause() **********/
1264 void wxDbTable::BuildWhereClause(wxString
&pWhereClause
, int typeOfWhere
,
1265 const wxString
&qualTableName
, bool useLikeComparison
)
1267 * Note: BuildWhereClause() currently ignores timestamp columns.
1268 * They are not included as part of the where clause.
1271 bool moreThanOneColumn
= false;
1274 // Loop through the columns building a where clause as you go
1276 for (colNumber
= 0; colNumber
< m_numCols
; colNumber
++)
1278 // Determine if this column should be included in the WHERE clause
1279 if ((typeOfWhere
== DB_WHERE_KEYFIELDS
&& colDefs
[colNumber
].KeyField
) ||
1280 (typeOfWhere
== DB_WHERE_MATCHING
&& (!IsColNull((UWORD
)colNumber
))))
1282 // Skip over timestamp columns
1283 if (colDefs
[colNumber
].SqlCtype
== SQL_C_TIMESTAMP
)
1285 // If there is more than 1 column, join them with the keyword "AND"
1286 if (moreThanOneColumn
)
1287 pWhereClause
+= wxT(" AND ");
1289 moreThanOneColumn
= true;
1291 // Concatenate where phrase for the column
1292 wxString tStr
= colDefs
[colNumber
].ColName
;
1294 if (qualTableName
.length() && tStr
.Find(wxT('.')) == wxNOT_FOUND
)
1296 pWhereClause
+= pDb
->SQLTableName(qualTableName
);
1297 pWhereClause
+= wxT(".");
1299 pWhereClause
+= pDb
->SQLColumnName(colDefs
[colNumber
].ColName
);
1301 if (useLikeComparison
&& (colDefs
[colNumber
].SqlCtype
== SQL_C_WXCHAR
))
1302 pWhereClause
+= wxT(" LIKE ");
1304 pWhereClause
+= wxT(" = ");
1306 switch(colDefs
[colNumber
].SqlCtype
)
1312 //case SQL_C_WXCHAR: SQL_C_WXCHAR is covered by either SQL_C_CHAR or SQL_C_WCHAR
1313 colValue
.Printf(wxT("'%s'"), (UCHAR FAR
*) colDefs
[colNumber
].PtrDataObj
);
1317 colValue
.Printf(wxT("%hi"), *((SWORD
*) colDefs
[colNumber
].PtrDataObj
));
1320 colValue
.Printf(wxT("%hu"), *((UWORD
*) colDefs
[colNumber
].PtrDataObj
));
1324 colValue
.Printf(wxT("%li"), *((SDWORD
*) colDefs
[colNumber
].PtrDataObj
));
1327 colValue
.Printf(wxT("%lu"), *((UDWORD
*) colDefs
[colNumber
].PtrDataObj
));
1330 colValue
.Printf(wxT("%.6f"), *((SFLOAT
*) colDefs
[colNumber
].PtrDataObj
));
1333 colValue
.Printf(wxT("%.6f"), *((SDOUBLE
*) colDefs
[colNumber
].PtrDataObj
));
1338 strMsg
.Printf(wxT("wxDbTable::bindParams(): Unknown column type for colDefs %d colName %s"),
1339 colNumber
,colDefs
[colNumber
].ColName
);
1340 wxFAIL_MSG(strMsg
.c_str());
1344 pWhereClause
+= colValue
;
1347 } // wxDbTable::BuildWhereClause()
1350 /***** DEPRECATED: use wxDbTable::BuildWhereClause(wxString &....) form *****/
1351 void wxDbTable::BuildWhereClause(wxChar
*pWhereClause
, int typeOfWhere
,
1352 const wxString
&qualTableName
, bool useLikeComparison
)
1354 wxString tempSqlStmt
;
1355 BuildWhereClause(tempSqlStmt
, typeOfWhere
, qualTableName
, useLikeComparison
);
1356 wxStrcpy(pWhereClause
, tempSqlStmt
);
1357 } // wxDbTable::BuildWhereClause()
1360 /********** wxDbTable::GetRowNum() **********/
1361 UWORD
wxDbTable::GetRowNum(void)
1365 if (SQLGetStmtOption(hstmt
, SQL_ROW_NUMBER
, (UCHAR
*) &rowNum
) != SQL_SUCCESS
)
1367 pDb
->DispAllErrors(henv
, hdbc
, hstmt
);
1371 // Completed successfully
1372 return((UWORD
) rowNum
);
1374 } // wxDbTable::GetRowNum()
1377 /********** wxDbTable::CloseCursor() **********/
1378 bool wxDbTable::CloseCursor(HSTMT cursor
)
1380 if (SQLFreeStmt(cursor
, SQL_CLOSE
) != SQL_SUCCESS
)
1381 return(pDb
->DispAllErrors(henv
, hdbc
, cursor
));
1383 // Completed successfully
1386 } // wxDbTable::CloseCursor()
1389 /********** wxDbTable::CreateTable() **********/
1390 bool wxDbTable::CreateTable(bool attemptDrop
)
1398 #ifdef DBDEBUG_CONSOLE
1399 cout
<< wxT("Creating Table ") << tableName
<< wxT("...") << endl
;
1403 if (attemptDrop
&& !DropTable())
1407 #ifdef DBDEBUG_CONSOLE
1408 for (i
= 0; i
< m_numCols
; i
++)
1410 // Exclude derived columns since they are NOT part of the base table
1411 if (colDefs
[i
].DerivedCol
)
1413 cout
<< i
+ 1 << wxT(": ") << colDefs
[i
].ColName
<< wxT("; ");
1414 switch(colDefs
[i
].DbDataType
)
1416 case DB_DATA_TYPE_VARCHAR
:
1417 cout
<< pDb
->GetTypeInfVarchar().TypeName
<< wxT("(") << (int)(colDefs
[i
].SzDataObj
/ sizeof(wxChar
)) << wxT(")");
1419 case DB_DATA_TYPE_MEMO
:
1420 cout
<< pDb
->GetTypeInfMemo().TypeName
;
1422 case DB_DATA_TYPE_INTEGER
:
1423 cout
<< pDb
->GetTypeInfInteger().TypeName
;
1425 case DB_DATA_TYPE_FLOAT
:
1426 cout
<< pDb
->GetTypeInfFloat().TypeName
;
1428 case DB_DATA_TYPE_DATE
:
1429 cout
<< pDb
->GetTypeInfDate().TypeName
;
1431 case DB_DATA_TYPE_BLOB
:
1432 cout
<< pDb
->GetTypeInfBlob().TypeName
;
1439 // Build a CREATE TABLE string from the colDefs structure.
1440 bool needComma
= false;
1442 sqlStmt
.Printf(wxT("CREATE TABLE %s ("),
1443 pDb
->SQLTableName(tableName
.c_str()).c_str());
1445 for (i
= 0; i
< m_numCols
; i
++)
1447 // Exclude derived columns since they are NOT part of the base table
1448 if (colDefs
[i
].DerivedCol
)
1452 sqlStmt
+= wxT(",");
1454 sqlStmt
+= pDb
->SQLColumnName(colDefs
[i
].ColName
);
1455 // sqlStmt += colDefs[i].ColName;
1456 sqlStmt
+= wxT(" ");
1458 switch(colDefs
[i
].DbDataType
)
1460 case DB_DATA_TYPE_VARCHAR
:
1461 sqlStmt
+= pDb
->GetTypeInfVarchar().TypeName
;
1463 case DB_DATA_TYPE_MEMO
:
1464 sqlStmt
+= pDb
->GetTypeInfMemo().TypeName
;
1466 case DB_DATA_TYPE_INTEGER
:
1467 sqlStmt
+= pDb
->GetTypeInfInteger().TypeName
;
1469 case DB_DATA_TYPE_FLOAT
:
1470 sqlStmt
+= pDb
->GetTypeInfFloat().TypeName
;
1472 case DB_DATA_TYPE_DATE
:
1473 sqlStmt
+= pDb
->GetTypeInfDate().TypeName
;
1475 case DB_DATA_TYPE_BLOB
:
1476 sqlStmt
+= pDb
->GetTypeInfBlob().TypeName
;
1479 // For varchars, append the size of the string
1480 if (colDefs
[i
].DbDataType
== DB_DATA_TYPE_VARCHAR
&&
1481 (pDb
->Dbms() != dbmsMY_SQL
|| pDb
->GetTypeInfVarchar().TypeName
!= _T("text")))// ||
1482 // colDefs[i].DbDataType == DB_DATA_TYPE_BLOB)
1485 s
.Printf(wxT("(%d)"), (int)(colDefs
[i
].SzDataObj
/ sizeof(wxChar
)));
1489 if (pDb
->Dbms() == dbmsDB2
||
1490 pDb
->Dbms() == dbmsMY_SQL
||
1491 pDb
->Dbms() == dbmsSYBASE_ASE
||
1492 pDb
->Dbms() == dbmsINTERBASE
||
1493 pDb
->Dbms() == dbmsFIREBIRD
||
1494 pDb
->Dbms() == dbmsMS_SQL_SERVER
)
1496 if (colDefs
[i
].KeyField
)
1498 sqlStmt
+= wxT(" NOT NULL");
1504 // If there is a primary key defined, include it in the create statement
1505 for (i
= j
= 0; i
< m_numCols
; i
++)
1507 if (colDefs
[i
].KeyField
)
1513 if ( j
&& (pDb
->Dbms() != dbmsDBASE
)
1514 && (pDb
->Dbms() != dbmsXBASE_SEQUITER
) ) // Found a keyfield
1516 switch (pDb
->Dbms())
1520 case dbmsSYBASE_ASA
:
1521 case dbmsSYBASE_ASE
:
1525 // MySQL goes out on this one. We also declare the relevant key NON NULL above
1526 sqlStmt
+= wxT(",PRIMARY KEY (");
1531 sqlStmt
+= wxT(",CONSTRAINT ");
1532 // DB2 is limited to 18 characters for index names
1533 if (pDb
->Dbms() == dbmsDB2
)
1535 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."));
1536 sqlStmt
+= pDb
->SQLTableName(tableName
.substr(0, 13).c_str());
1537 // sqlStmt += tableName.substr(0, 13);
1540 sqlStmt
+= pDb
->SQLTableName(tableName
.c_str());
1541 // sqlStmt += tableName;
1543 sqlStmt
+= wxT("_PIDX PRIMARY KEY (");
1548 // List column name(s) of column(s) comprising the primary key
1549 for (i
= j
= 0; i
< m_numCols
; i
++)
1551 if (colDefs
[i
].KeyField
)
1553 if (j
++) // Multi part key, comma separate names
1554 sqlStmt
+= wxT(",");
1555 sqlStmt
+= pDb
->SQLColumnName(colDefs
[i
].ColName
);
1557 if (pDb
->Dbms() == dbmsMY_SQL
&&
1558 colDefs
[i
].DbDataType
== DB_DATA_TYPE_VARCHAR
)
1561 s
.Printf(wxT("(%d)"), (int)(colDefs
[i
].SzDataObj
/ sizeof(wxChar
)));
1566 sqlStmt
+= wxT(")");
1568 if (pDb
->Dbms() == dbmsINFORMIX
||
1569 pDb
->Dbms() == dbmsSYBASE_ASA
||
1570 pDb
->Dbms() == dbmsSYBASE_ASE
)
1572 sqlStmt
+= wxT(" CONSTRAINT ");
1573 sqlStmt
+= pDb
->SQLTableName(tableName
);
1574 // sqlStmt += tableName;
1575 sqlStmt
+= wxT("_PIDX");
1578 // Append the closing parentheses for the create table statement
1579 sqlStmt
+= wxT(")");
1581 pDb
->WriteSqlLog(sqlStmt
);
1583 #ifdef DBDEBUG_CONSOLE
1584 cout
<< endl
<< sqlStmt
.c_str() << endl
;
1587 // Execute the CREATE TABLE statement
1588 RETCODE retcode
= SQLExecDirect(hstmt
, (SQLTCHAR FAR
*) sqlStmt
.c_str(), SQL_NTS
);
1589 if (retcode
!= SQL_SUCCESS
&& retcode
!= SQL_SUCCESS_WITH_INFO
)
1591 pDb
->DispAllErrors(henv
, hdbc
, hstmt
);
1592 pDb
->RollbackTrans();
1597 // Commit the transaction and close the cursor
1598 if (!pDb
->CommitTrans())
1600 if (!CloseCursor(hstmt
))
1603 // Database table created successfully
1606 } // wxDbTable::CreateTable()
1609 /********** wxDbTable::DropTable() **********/
1610 bool wxDbTable::DropTable()
1612 // NOTE: This function returns true if the Table does not exist, but
1613 // only for identified databases. Code will need to be added
1614 // below for any other databases when those databases are defined
1615 // to handle this situation consistently
1619 sqlStmt
.Printf(wxT("DROP TABLE %s"),
1620 pDb
->SQLTableName(tableName
.c_str()).c_str());
1622 pDb
->WriteSqlLog(sqlStmt
);
1624 #ifdef DBDEBUG_CONSOLE
1625 cout
<< endl
<< sqlStmt
.c_str() << endl
;
1628 RETCODE retcode
= SQLExecDirect(hstmt
, (SQLTCHAR FAR
*) sqlStmt
.c_str(), SQL_NTS
);
1629 if (retcode
!= SQL_SUCCESS
)
1631 // Check for "Base table not found" error and ignore
1632 pDb
->GetNextError(henv
, hdbc
, hstmt
);
1633 if (wxStrcmp(pDb
->sqlState
, wxT("S0002")) /*&&
1634 wxStrcmp(pDb->sqlState, wxT("S1000"))*/) // "Base table not found"
1636 // Check for product specific error codes
1637 if (!((pDb
->Dbms() == dbmsSYBASE_ASA
&& !wxStrcmp(pDb
->sqlState
,wxT("42000"))) || // 5.x (and lower?)
1638 (pDb
->Dbms() == dbmsSYBASE_ASE
&& !wxStrcmp(pDb
->sqlState
,wxT("37000"))) ||
1639 (pDb
->Dbms() == dbmsPERVASIVE_SQL
&& !wxStrcmp(pDb
->sqlState
,wxT("S1000"))) || // Returns an S1000 then an S0002
1640 (pDb
->Dbms() == dbmsPOSTGRES
&& !wxStrcmp(pDb
->sqlState
,wxT("08S01")))))
1642 pDb
->DispNextError();
1643 pDb
->DispAllErrors(henv
, hdbc
, hstmt
);
1644 pDb
->RollbackTrans();
1645 // CloseCursor(hstmt);
1651 // Commit the transaction and close the cursor
1652 if (! pDb
->CommitTrans())
1654 if (! CloseCursor(hstmt
))
1658 } // wxDbTable::DropTable()
1661 /********** wxDbTable::CreateIndex() **********/
1662 bool wxDbTable::CreateIndex(const wxString
&indexName
, bool unique
, UWORD numIndexColumns
,
1663 wxDbIdxDef
*pIndexDefs
, bool attemptDrop
)
1667 // Drop the index first
1668 if (attemptDrop
&& !DropIndex(indexName
))
1671 // MySQL (and possibly Sybase ASE?? - gt) require that any columns which are used as portions
1672 // of an index have the columns defined as "NOT NULL". During initial table creation though,
1673 // it may not be known which columns are necessarily going to be part of an index (e.g. the
1674 // table was created, then months later you determine that an additional index while
1675 // give better performance, so you want to add an index).
1677 // The following block of code will modify the column definition to make the column be
1678 // defined with the "NOT NULL" qualifier.
1679 if (pDb
->Dbms() == dbmsMY_SQL
)
1684 for (i
= 0; i
< numIndexColumns
&& ok
; i
++)
1688 // Find the column definition that has the ColName that matches the
1689 // index column name. We need to do this to get the DB_DATA_TYPE of
1690 // the index column, as MySQL's syntax for the ALTER column requires
1692 while (!found
&& (j
< this->m_numCols
))
1694 if (wxStrcmp(colDefs
[j
].ColName
,pIndexDefs
[i
].ColName
) == 0)
1702 ok
= pDb
->ModifyColumn(tableName
, pIndexDefs
[i
].ColName
,
1703 colDefs
[j
].DbDataType
, (int)(colDefs
[j
].SzDataObj
/ sizeof(wxChar
)),
1709 // retcode is not used
1710 wxODBC_ERRORS retcode
;
1711 // Oracle returns a DB_ERR_GENERAL_ERROR if the column is already
1712 // defined to be NOT NULL, but reportedly MySQL doesn't mind.
1713 // This line is just here for debug checking of the value
1714 retcode
= (wxODBC_ERRORS
)pDb
->DB_STATUS
;
1725 pDb
->RollbackTrans();
1730 // Build a CREATE INDEX statement
1731 sqlStmt
= wxT("CREATE ");
1733 sqlStmt
+= wxT("UNIQUE ");
1735 sqlStmt
+= wxT("INDEX ");
1736 sqlStmt
+= pDb
->SQLTableName(indexName
);
1737 sqlStmt
+= wxT(" ON ");
1739 sqlStmt
+= pDb
->SQLTableName(tableName
);
1740 // sqlStmt += tableName;
1741 sqlStmt
+= wxT(" (");
1743 // Append list of columns making up index
1745 for (i
= 0; i
< numIndexColumns
; i
++)
1747 sqlStmt
+= pDb
->SQLColumnName(pIndexDefs
[i
].ColName
);
1748 // sqlStmt += pIndexDefs[i].ColName;
1750 // MySQL requires a key length on VARCHAR keys
1751 if ( pDb
->Dbms() == dbmsMY_SQL
)
1753 // Find the details on this column
1755 for ( j
= 0; j
< m_numCols
; ++j
)
1757 if ( wxStrcmp( pIndexDefs
[i
].ColName
, colDefs
[j
].ColName
) == 0 )
1762 if ( colDefs
[j
].DbDataType
== DB_DATA_TYPE_VARCHAR
)
1765 s
.Printf(wxT("(%d)"), (int)(colDefs
[i
].SzDataObj
/ sizeof(wxChar
)));
1770 // Postgres and SQL Server 7 do not support the ASC/DESC keywords for index columns
1771 if (!((pDb
->Dbms() == dbmsMS_SQL_SERVER
) && (wxStrncmp(pDb
->dbInf
.dbmsVer
,_T("07"),2)==0)) &&
1772 !(pDb
->Dbms() == dbmsFIREBIRD
) &&
1773 !(pDb
->Dbms() == dbmsPOSTGRES
))
1775 if (pIndexDefs
[i
].Ascending
)
1776 sqlStmt
+= wxT(" ASC");
1778 sqlStmt
+= wxT(" DESC");
1781 wxASSERT_MSG(pIndexDefs
[i
].Ascending
, _T("Datasource does not support DESCending index columns"));
1783 if ((i
+ 1) < numIndexColumns
)
1784 sqlStmt
+= wxT(",");
1787 // Append closing parentheses
1788 sqlStmt
+= wxT(")");
1790 pDb
->WriteSqlLog(sqlStmt
);
1792 #ifdef DBDEBUG_CONSOLE
1793 cout
<< endl
<< sqlStmt
.c_str() << endl
<< endl
;
1796 // Execute the CREATE INDEX statement
1797 RETCODE retcode
= SQLExecDirect(hstmt
, (SQLTCHAR FAR
*) sqlStmt
.c_str(), SQL_NTS
);
1798 if (retcode
!= SQL_SUCCESS
)
1800 pDb
->DispAllErrors(henv
, hdbc
, hstmt
);
1801 pDb
->RollbackTrans();
1806 // Commit the transaction and close the cursor
1807 if (! pDb
->CommitTrans())
1809 if (! CloseCursor(hstmt
))
1812 // Index Created Successfully
1815 } // wxDbTable::CreateIndex()
1818 /********** wxDbTable::DropIndex() **********/
1819 bool wxDbTable::DropIndex(const wxString
&indexName
)
1821 // NOTE: This function returns true if the Index does not exist, but
1822 // only for identified databases. Code will need to be added
1823 // below for any other databases when those databases are defined
1824 // to handle this situation consistently
1828 if (pDb
->Dbms() == dbmsACCESS
|| pDb
->Dbms() == dbmsMY_SQL
||
1829 pDb
->Dbms() == dbmsDBASE
/*|| Paradox needs this syntax too when we add support*/)
1830 sqlStmt
.Printf(wxT("DROP INDEX %s ON %s"),
1831 pDb
->SQLTableName(indexName
.c_str()).c_str(),
1832 pDb
->SQLTableName(tableName
.c_str()).c_str());
1833 else if ((pDb
->Dbms() == dbmsMS_SQL_SERVER
) ||
1834 (pDb
->Dbms() == dbmsSYBASE_ASE
) ||
1835 (pDb
->Dbms() == dbmsXBASE_SEQUITER
))
1836 sqlStmt
.Printf(wxT("DROP INDEX %s.%s"),
1837 pDb
->SQLTableName(tableName
.c_str()).c_str(),
1838 pDb
->SQLTableName(indexName
.c_str()).c_str());
1840 sqlStmt
.Printf(wxT("DROP INDEX %s"),
1841 pDb
->SQLTableName(indexName
.c_str()).c_str());
1843 pDb
->WriteSqlLog(sqlStmt
);
1845 #ifdef DBDEBUG_CONSOLE
1846 cout
<< endl
<< sqlStmt
.c_str() << endl
;
1848 RETCODE retcode
= SQLExecDirect(hstmt
, (SQLTCHAR FAR
*) sqlStmt
.c_str(), SQL_NTS
);
1849 if (retcode
!= SQL_SUCCESS
)
1851 // Check for "Index not found" error and ignore
1852 pDb
->GetNextError(henv
, hdbc
, hstmt
);
1853 if (wxStrcmp(pDb
->sqlState
,wxT("S0012"))) // "Index not found"
1855 // Check for product specific error codes
1856 if (!((pDb
->Dbms() == dbmsSYBASE_ASA
&& !wxStrcmp(pDb
->sqlState
,wxT("42000"))) || // v5.x (and lower?)
1857 (pDb
->Dbms() == dbmsSYBASE_ASE
&& !wxStrcmp(pDb
->sqlState
,wxT("37000"))) ||
1858 (pDb
->Dbms() == dbmsMS_SQL_SERVER
&& !wxStrcmp(pDb
->sqlState
,wxT("S1000"))) ||
1859 (pDb
->Dbms() == dbmsINTERBASE
&& !wxStrcmp(pDb
->sqlState
,wxT("S1000"))) ||
1860 (pDb
->Dbms() == dbmsMAXDB
&& !wxStrcmp(pDb
->sqlState
,wxT("S1000"))) ||
1861 (pDb
->Dbms() == dbmsFIREBIRD
&& !wxStrcmp(pDb
->sqlState
,wxT("HY000"))) ||
1862 (pDb
->Dbms() == dbmsSYBASE_ASE
&& !wxStrcmp(pDb
->sqlState
,wxT("S0002"))) || // Base table not found
1863 (pDb
->Dbms() == dbmsMY_SQL
&& !wxStrcmp(pDb
->sqlState
,wxT("42S12"))) || // tested by Christopher Ludwik Marino-Cebulski using v3.23.21beta
1864 (pDb
->Dbms() == dbmsPOSTGRES
&& !wxStrcmp(pDb
->sqlState
,wxT("08S01")))
1867 pDb
->DispNextError();
1868 pDb
->DispAllErrors(henv
, hdbc
, hstmt
);
1869 pDb
->RollbackTrans();
1876 // Commit the transaction and close the cursor
1877 if (! pDb
->CommitTrans())
1879 if (! CloseCursor(hstmt
))
1883 } // wxDbTable::DropIndex()
1886 /********** wxDbTable::SetOrderByColNums() **********/
1887 bool wxDbTable::SetOrderByColNums(UWORD first
, ... )
1889 int colNumber
= first
; // using 'int' to be able to look for wxDB_NO_MORE_COLUN_NUMBERS
1895 va_start(argptr
, first
); /* Initialize variable arguments. */
1896 while (!abort
&& (colNumber
!= wxDB_NO_MORE_COLUMN_NUMBERS
))
1898 // Make sure the passed in column number
1899 // is within the valid range of columns
1901 // Valid columns are 0 thru m_numCols-1
1902 if (colNumber
>= m_numCols
|| colNumber
< 0)
1908 if (colNumber
!= first
)
1909 tempStr
+= wxT(",");
1911 tempStr
+= colDefs
[colNumber
].ColName
;
1912 colNumber
= va_arg (argptr
, int);
1914 va_end (argptr
); /* Reset variable arguments. */
1916 SetOrderByClause(tempStr
);
1919 } // wxDbTable::SetOrderByColNums()
1922 /********** wxDbTable::Insert() **********/
1923 int wxDbTable::Insert(void)
1925 wxASSERT(!queryOnly
);
1926 if (queryOnly
|| !insertable
)
1931 // Insert the record by executing the already prepared insert statement
1933 retcode
= SQLExecute(hstmtInsert
);
1934 if (retcode
!= SQL_SUCCESS
&& retcode
!= SQL_SUCCESS_WITH_INFO
&&
1935 retcode
!= SQL_NEED_DATA
)
1937 // Check to see if integrity constraint was violated
1938 pDb
->GetNextError(henv
, hdbc
, hstmtInsert
);
1939 if (! wxStrcmp(pDb
->sqlState
, wxT("23000"))) // Integrity constraint violated
1940 return(DB_ERR_INTEGRITY_CONSTRAINT_VIOL
);
1943 pDb
->DispNextError();
1944 pDb
->DispAllErrors(henv
, hdbc
, hstmtInsert
);
1948 if (retcode
== SQL_NEED_DATA
)
1951 retcode
= SQLParamData(hstmtInsert
, &pParmID
);
1952 while (retcode
== SQL_NEED_DATA
)
1954 // Find the parameter
1956 for (i
=0; i
< m_numCols
; i
++)
1958 if (colDefs
[i
].PtrDataObj
== pParmID
)
1960 // We found it. Store the parameter.
1961 retcode
= SQLPutData(hstmtInsert
, pParmID
, colDefs
[i
].SzDataObj
);
1962 if (retcode
!= SQL_SUCCESS
)
1964 pDb
->DispNextError();
1965 pDb
->DispAllErrors(henv
, hdbc
, hstmtInsert
);
1971 retcode
= SQLParamData(hstmtInsert
, &pParmID
);
1972 if (retcode
!= SQL_SUCCESS
&&
1973 retcode
!= SQL_SUCCESS_WITH_INFO
)
1975 // record was not inserted
1976 pDb
->DispNextError();
1977 pDb
->DispAllErrors(henv
, hdbc
, hstmtInsert
);
1983 // Record inserted into the datasource successfully
1986 } // wxDbTable::Insert()
1989 /********** wxDbTable::Update() **********/
1990 bool wxDbTable::Update(void)
1992 wxASSERT(!queryOnly
);
1998 // Build the SQL UPDATE statement
1999 BuildUpdateStmt(sqlStmt
, DB_UPD_KEYFIELDS
);
2001 pDb
->WriteSqlLog(sqlStmt
);
2003 #ifdef DBDEBUG_CONSOLE
2004 cout
<< endl
<< sqlStmt
.c_str() << endl
<< endl
;
2007 // Execute the SQL UPDATE statement
2008 return(execUpdate(sqlStmt
));
2010 } // wxDbTable::Update()
2013 /********** wxDbTable::Update(pSqlStmt) **********/
2014 bool wxDbTable::Update(const wxString
&pSqlStmt
)
2016 wxASSERT(!queryOnly
);
2020 pDb
->WriteSqlLog(pSqlStmt
);
2022 return(execUpdate(pSqlStmt
));
2024 } // wxDbTable::Update(pSqlStmt)
2027 /********** wxDbTable::UpdateWhere() **********/
2028 bool wxDbTable::UpdateWhere(const wxString
&pWhereClause
)
2030 wxASSERT(!queryOnly
);
2036 // Build the SQL UPDATE statement
2037 BuildUpdateStmt(sqlStmt
, DB_UPD_WHERE
, pWhereClause
);
2039 pDb
->WriteSqlLog(sqlStmt
);
2041 #ifdef DBDEBUG_CONSOLE
2042 cout
<< endl
<< sqlStmt
.c_str() << endl
<< endl
;
2045 // Execute the SQL UPDATE statement
2046 return(execUpdate(sqlStmt
));
2048 } // wxDbTable::UpdateWhere()
2051 /********** wxDbTable::Delete() **********/
2052 bool wxDbTable::Delete(void)
2054 wxASSERT(!queryOnly
);
2061 // Build the SQL DELETE statement
2062 BuildDeleteStmt(sqlStmt
, DB_DEL_KEYFIELDS
);
2064 pDb
->WriteSqlLog(sqlStmt
);
2066 // Execute the SQL DELETE statement
2067 return(execDelete(sqlStmt
));
2069 } // wxDbTable::Delete()
2072 /********** wxDbTable::DeleteWhere() **********/
2073 bool wxDbTable::DeleteWhere(const wxString
&pWhereClause
)
2075 wxASSERT(!queryOnly
);
2082 // Build the SQL DELETE statement
2083 BuildDeleteStmt(sqlStmt
, DB_DEL_WHERE
, pWhereClause
);
2085 pDb
->WriteSqlLog(sqlStmt
);
2087 // Execute the SQL DELETE statement
2088 return(execDelete(sqlStmt
));
2090 } // wxDbTable::DeleteWhere()
2093 /********** wxDbTable::DeleteMatching() **********/
2094 bool wxDbTable::DeleteMatching(void)
2096 wxASSERT(!queryOnly
);
2103 // Build the SQL DELETE statement
2104 BuildDeleteStmt(sqlStmt
, DB_DEL_MATCHING
);
2106 pDb
->WriteSqlLog(sqlStmt
);
2108 // Execute the SQL DELETE statement
2109 return(execDelete(sqlStmt
));
2111 } // wxDbTable::DeleteMatching()
2114 /********** wxDbTable::IsColNull() **********/
2115 bool wxDbTable::IsColNull(UWORD colNumber
) const
2118 This logic is just not right. It would indicate true
2119 if a numeric field were set to a value of 0.
2121 switch(colDefs[colNumber].SqlCtype)
2125 //case SQL_C_WXCHAR: SQL_C_WXCHAR is covered by either SQL_C_CHAR or SQL_C_WCHAR
2126 return(((UCHAR FAR *) colDefs[colNumber].PtrDataObj)[0] == 0);
2128 return(( *((SWORD *) colDefs[colNumber].PtrDataObj)) == 0);
2130 return(( *((UWORD*) colDefs[colNumber].PtrDataObj)) == 0);
2132 return(( *((SDWORD *) colDefs[colNumber].PtrDataObj)) == 0);
2134 return(( *((UDWORD *) colDefs[colNumber].PtrDataObj)) == 0);
2136 return(( *((SFLOAT *) colDefs[colNumber].PtrDataObj)) == 0);
2138 return((*((SDOUBLE *) colDefs[colNumber].PtrDataObj)) == 0);
2139 case SQL_C_TIMESTAMP:
2140 TIMESTAMP_STRUCT *pDt;
2141 pDt = (TIMESTAMP_STRUCT *) colDefs[colNumber].PtrDataObj;
2142 if (pDt->year == 0 && pDt->month == 0 && pDt->day == 0)
2150 return (colDefs
[colNumber
].Null
);
2151 } // wxDbTable::IsColNull()
2154 /********** wxDbTable::CanSelectForUpdate() **********/
2155 bool wxDbTable::CanSelectForUpdate(void)
2160 if (pDb
->Dbms() == dbmsMY_SQL
)
2163 if ((pDb
->Dbms() == dbmsORACLE
) ||
2164 (pDb
->dbInf
.posStmts
& SQL_PS_SELECT_FOR_UPDATE
))
2169 } // wxDbTable::CanSelectForUpdate()
2172 /********** wxDbTable::CanUpdateByROWID() **********/
2173 bool wxDbTable::CanUpdateByROWID(void)
2176 * NOTE: Returning false for now until this can be debugged,
2177 * as the ROWID is not getting updated correctly
2181 if (pDb->Dbms() == dbmsORACLE)
2186 } // wxDbTable::CanUpdateByROWID()
2189 /********** wxDbTable::IsCursorClosedOnCommit() **********/
2190 bool wxDbTable::IsCursorClosedOnCommit(void)
2192 if (pDb
->dbInf
.cursorCommitBehavior
== SQL_CB_PRESERVE
)
2197 } // wxDbTable::IsCursorClosedOnCommit()
2201 /********** wxDbTable::ClearMemberVar() **********/
2202 void wxDbTable::ClearMemberVar(UWORD colNumber
, bool setToNull
)
2204 wxASSERT(colNumber
< m_numCols
);
2206 switch(colDefs
[colNumber
].SqlCtype
)
2212 //case SQL_C_WXCHAR: SQL_C_WXCHAR is covered by either SQL_C_CHAR or SQL_C_WCHAR
2213 ((UCHAR FAR
*) colDefs
[colNumber
].PtrDataObj
)[0] = 0;
2216 *((SWORD
*) colDefs
[colNumber
].PtrDataObj
) = 0;
2219 *((UWORD
*) colDefs
[colNumber
].PtrDataObj
) = 0;
2223 *((SDWORD
*) colDefs
[colNumber
].PtrDataObj
) = 0;
2226 *((UDWORD
*) colDefs
[colNumber
].PtrDataObj
) = 0;
2229 *((SFLOAT
*) colDefs
[colNumber
].PtrDataObj
) = 0.0f
;
2232 *((SDOUBLE
*) colDefs
[colNumber
].PtrDataObj
) = 0.0f
;
2234 case SQL_C_TIMESTAMP
:
2235 TIMESTAMP_STRUCT
*pDt
;
2236 pDt
= (TIMESTAMP_STRUCT
*) colDefs
[colNumber
].PtrDataObj
;
2248 SetColNull(colNumber
);
2249 } // wxDbTable::ClearMemberVar()
2252 /********** wxDbTable::ClearMemberVars() **********/
2253 void wxDbTable::ClearMemberVars(bool setToNull
)
2257 // Loop through the columns setting each member variable to zero
2258 for (i
=0; i
< m_numCols
; i
++)
2259 ClearMemberVar((UWORD
)i
,setToNull
);
2261 } // wxDbTable::ClearMemberVars()
2264 /********** wxDbTable::SetQueryTimeout() **********/
2265 bool wxDbTable::SetQueryTimeout(UDWORD nSeconds
)
2267 if (SQLSetStmtOption(hstmtInsert
, SQL_QUERY_TIMEOUT
, nSeconds
) != SQL_SUCCESS
)
2268 return(pDb
->DispAllErrors(henv
, hdbc
, hstmtInsert
));
2269 if (SQLSetStmtOption(hstmtUpdate
, SQL_QUERY_TIMEOUT
, nSeconds
) != SQL_SUCCESS
)
2270 return(pDb
->DispAllErrors(henv
, hdbc
, hstmtUpdate
));
2271 if (SQLSetStmtOption(hstmtDelete
, SQL_QUERY_TIMEOUT
, nSeconds
) != SQL_SUCCESS
)
2272 return(pDb
->DispAllErrors(henv
, hdbc
, hstmtDelete
));
2273 if (SQLSetStmtOption(hstmtInternal
, SQL_QUERY_TIMEOUT
, nSeconds
) != SQL_SUCCESS
)
2274 return(pDb
->DispAllErrors(henv
, hdbc
, hstmtInternal
));
2276 // Completed Successfully
2279 } // wxDbTable::SetQueryTimeout()
2282 /********** wxDbTable::SetColDefs() **********/
2283 bool wxDbTable::SetColDefs(UWORD index
, const wxString
&fieldName
, int dataType
, void *pData
,
2284 SWORD cType
, int size
, bool keyField
, bool updateable
,
2285 bool insertAllowed
, bool derivedColumn
)
2289 if (index
>= m_numCols
) // Columns numbers are zero based....
2291 tmpStr
.Printf(wxT("Specified column index (%d) exceeds the maximum number of columns (%d) registered for this table definition. Column definition not added."), index
, m_numCols
);
2297 if (!colDefs
) // May happen if the database connection fails
2300 if (fieldName
.length() > (unsigned int) DB_MAX_COLUMN_NAME_LEN
)
2302 wxStrncpy(colDefs
[index
].ColName
, fieldName
, DB_MAX_COLUMN_NAME_LEN
);
2303 colDefs
[index
].ColName
[DB_MAX_COLUMN_NAME_LEN
] = 0; // Prevent buffer overrun
2305 tmpStr
.Printf(wxT("Column name '%s' is too long. Truncated to '%s'."),
2306 fieldName
.c_str(),colDefs
[index
].ColName
);
2311 wxStrcpy(colDefs
[index
].ColName
, fieldName
);
2313 colDefs
[index
].DbDataType
= dataType
;
2314 colDefs
[index
].PtrDataObj
= pData
;
2315 colDefs
[index
].SqlCtype
= cType
;
2316 colDefs
[index
].SzDataObj
= size
; //TODO: glt ??? * sizeof(wxChar) ???
2317 colDefs
[index
].KeyField
= keyField
;
2318 colDefs
[index
].DerivedCol
= derivedColumn
;
2319 // Derived columns by definition would NOT be "Insertable" or "Updateable"
2322 colDefs
[index
].Updateable
= false;
2323 colDefs
[index
].InsertAllowed
= false;
2327 colDefs
[index
].Updateable
= updateable
;
2328 colDefs
[index
].InsertAllowed
= insertAllowed
;
2331 colDefs
[index
].Null
= false;
2335 } // wxDbTable::SetColDefs()
2338 /********** wxDbTable::SetColDefs() **********/
2339 wxDbColDataPtr
* wxDbTable::SetColDefs(wxDbColInf
*pColInfs
, UWORD numCols
)
2342 wxDbColDataPtr
*pColDataPtrs
= NULL
;
2348 pColDataPtrs
= new wxDbColDataPtr
[numCols
+1];
2350 for (index
= 0; index
< numCols
; index
++)
2352 // Process the fields
2353 switch (pColInfs
[index
].dbDataType
)
2355 case DB_DATA_TYPE_VARCHAR
:
2356 pColDataPtrs
[index
].PtrDataObj
= new wxChar
[pColInfs
[index
].bufferSize
+(1*sizeof(wxChar
))];
2357 pColDataPtrs
[index
].SzDataObj
= pColInfs
[index
].bufferSize
+(1*sizeof(wxChar
));
2358 pColDataPtrs
[index
].SqlCtype
= SQL_C_WXCHAR
;
2360 case DB_DATA_TYPE_MEMO
:
2361 pColDataPtrs
[index
].PtrDataObj
= new wxChar
[pColInfs
[index
].bufferSize
+(1*sizeof(wxChar
))];
2362 pColDataPtrs
[index
].SzDataObj
= pColInfs
[index
].bufferSize
+(1*sizeof(wxChar
));
2363 pColDataPtrs
[index
].SqlCtype
= SQL_C_WXCHAR
;
2365 case DB_DATA_TYPE_INTEGER
:
2366 // Can be long or short
2367 if (pColInfs
[index
].bufferSize
== sizeof(long))
2369 pColDataPtrs
[index
].PtrDataObj
= new long;
2370 pColDataPtrs
[index
].SzDataObj
= sizeof(long);
2371 pColDataPtrs
[index
].SqlCtype
= SQL_C_SLONG
;
2375 pColDataPtrs
[index
].PtrDataObj
= new short;
2376 pColDataPtrs
[index
].SzDataObj
= sizeof(short);
2377 pColDataPtrs
[index
].SqlCtype
= SQL_C_SSHORT
;
2380 case DB_DATA_TYPE_FLOAT
:
2381 // Can be float or double
2382 if (pColInfs
[index
].bufferSize
== sizeof(float))
2384 pColDataPtrs
[index
].PtrDataObj
= new float;
2385 pColDataPtrs
[index
].SzDataObj
= sizeof(float);
2386 pColDataPtrs
[index
].SqlCtype
= SQL_C_FLOAT
;
2390 pColDataPtrs
[index
].PtrDataObj
= new double;
2391 pColDataPtrs
[index
].SzDataObj
= sizeof(double);
2392 pColDataPtrs
[index
].SqlCtype
= SQL_C_DOUBLE
;
2395 case DB_DATA_TYPE_DATE
:
2396 pColDataPtrs
[index
].PtrDataObj
= new TIMESTAMP_STRUCT
;
2397 pColDataPtrs
[index
].SzDataObj
= sizeof(TIMESTAMP_STRUCT
);
2398 pColDataPtrs
[index
].SqlCtype
= SQL_C_TIMESTAMP
;
2400 case DB_DATA_TYPE_BLOB
:
2401 wxFAIL_MSG(wxT("This form of ::SetColDefs() cannot be used with BLOB columns"));
2402 pColDataPtrs
[index
].PtrDataObj
= /*BLOB ADDITION NEEDED*/NULL
;
2403 pColDataPtrs
[index
].SzDataObj
= /*BLOB ADDITION NEEDED*/sizeof(void *);
2404 pColDataPtrs
[index
].SqlCtype
= SQL_VARBINARY
;
2407 if (pColDataPtrs
[index
].PtrDataObj
!= NULL
)
2408 SetColDefs (index
,pColInfs
[index
].colName
,pColInfs
[index
].dbDataType
, pColDataPtrs
[index
].PtrDataObj
, pColDataPtrs
[index
].SqlCtype
, pColDataPtrs
[index
].SzDataObj
);
2411 // Unable to build all the column definitions, as either one of
2412 // the calls to "new" failed above, or there was a BLOB field
2413 // to have a column definition for. If BLOBs are to be used,
2414 // the other form of ::SetColDefs() must be used, as it is impossible
2415 // to know the maximum size to create the PtrDataObj to be.
2416 delete [] pColDataPtrs
;
2422 return (pColDataPtrs
);
2424 } // wxDbTable::SetColDefs()
2427 /********** wxDbTable::SetCursor() **********/
2428 void wxDbTable::SetCursor(HSTMT
*hstmtActivate
)
2430 if (hstmtActivate
== wxDB_DEFAULT_CURSOR
)
2431 hstmt
= *hstmtDefault
;
2433 hstmt
= *hstmtActivate
;
2435 } // wxDbTable::SetCursor()
2438 /********** wxDbTable::Count(const wxString &) **********/
2439 ULONG
wxDbTable::Count(const wxString
&args
)
2445 // Build a "SELECT COUNT(*) FROM queryTableName [WHERE whereClause]" SQL Statement
2446 sqlStmt
= wxT("SELECT COUNT(");
2448 sqlStmt
+= wxT(") FROM ");
2449 sqlStmt
+= pDb
->SQLTableName(queryTableName
);
2450 // sqlStmt += queryTableName;
2451 #if wxODBC_BACKWARD_COMPATABILITY
2452 if (from
&& wxStrlen(from
))
2458 // Add the where clause if one is provided
2459 #if wxODBC_BACKWARD_COMPATABILITY
2460 if (where
&& wxStrlen(where
))
2465 sqlStmt
+= wxT(" WHERE ");
2469 pDb
->WriteSqlLog(sqlStmt
);
2471 // Initialize the Count cursor if it's not already initialized
2474 hstmtCount
= GetNewCursor(false,false);
2475 wxASSERT(hstmtCount
);
2480 // Execute the SQL statement
2481 if (SQLExecDirect(*hstmtCount
, (SQLTCHAR FAR
*) sqlStmt
.c_str(), SQL_NTS
) != SQL_SUCCESS
)
2483 pDb
->DispAllErrors(henv
, hdbc
, *hstmtCount
);
2488 if (SQLFetch(*hstmtCount
) != SQL_SUCCESS
)
2490 pDb
->DispAllErrors(henv
, hdbc
, *hstmtCount
);
2494 // Obtain the result
2495 if (SQLGetData(*hstmtCount
, (UWORD
)1, SQL_C_ULONG
, &count
, sizeof(count
), &cb
) != SQL_SUCCESS
)
2497 pDb
->DispAllErrors(henv
, hdbc
, *hstmtCount
);
2502 if (SQLFreeStmt(*hstmtCount
, SQL_CLOSE
) != SQL_SUCCESS
)
2503 pDb
->DispAllErrors(henv
, hdbc
, *hstmtCount
);
2505 // Return the record count
2508 } // wxDbTable::Count()
2511 /********** wxDbTable::Refresh() **********/
2512 bool wxDbTable::Refresh(void)
2516 // Switch to the internal cursor so any active cursors are not corrupted
2517 HSTMT currCursor
= GetCursor();
2518 hstmt
= hstmtInternal
;
2519 #if wxODBC_BACKWARD_COMPATABILITY
2520 // Save the where and order by clauses
2521 wxChar
*saveWhere
= where
;
2522 wxChar
*saveOrderBy
= orderBy
;
2524 wxString saveWhere
= where
;
2525 wxString saveOrderBy
= orderBy
;
2527 // Build a where clause to refetch the record with. Try and use the
2528 // ROWID if it's available, ow use the key fields.
2529 wxString whereClause
;
2530 whereClause
.Empty();
2532 if (CanUpdateByROWID())
2535 wxChar rowid
[wxDB_ROWID_LEN
+1];
2537 // Get the ROWID value. If not successful retreiving the ROWID,
2538 // simply fall down through the code and build the WHERE clause
2539 // based on the key fields.
2540 if (SQLGetData(hstmt
, (UWORD
)(m_numCols
+1), SQL_C_WXCHAR
, (UCHAR
*) rowid
, sizeof(rowid
), &cb
) == SQL_SUCCESS
)
2542 whereClause
+= pDb
->SQLTableName(queryTableName
);
2543 // whereClause += queryTableName;
2544 whereClause
+= wxT(".ROWID = '");
2545 whereClause
+= rowid
;
2546 whereClause
+= wxT("'");
2550 // If unable to use the ROWID, build a where clause from the keyfields
2551 if (wxStrlen(whereClause
) == 0)
2552 BuildWhereClause(whereClause
, DB_WHERE_KEYFIELDS
, queryTableName
);
2554 // Requery the record
2555 where
= whereClause
;
2560 if (result
&& !GetNext())
2563 // Switch back to original cursor
2564 SetCursor(&currCursor
);
2566 // Free the internal cursor
2567 if (SQLFreeStmt(hstmtInternal
, SQL_CLOSE
) != SQL_SUCCESS
)
2568 pDb
->DispAllErrors(henv
, hdbc
, hstmtInternal
);
2570 // Restore the original where and order by clauses
2572 orderBy
= saveOrderBy
;
2576 } // wxDbTable::Refresh()
2579 /********** wxDbTable::SetColNull() **********/
2580 bool wxDbTable::SetColNull(UWORD colNumber
, bool set
)
2582 if (colNumber
< m_numCols
)
2584 colDefs
[colNumber
].Null
= set
;
2585 if (set
) // Blank out the values in the member variable
2586 ClearMemberVar(colNumber
, false); // Must call with false here, or infinite recursion will happen
2588 setCbValueForColumn(colNumber
);
2595 } // wxDbTable::SetColNull()
2598 /********** wxDbTable::SetColNull() **********/
2599 bool wxDbTable::SetColNull(const wxString
&colName
, bool set
)
2602 for (colNumber
= 0; colNumber
< m_numCols
; colNumber
++)
2604 if (!wxStricmp(colName
, colDefs
[colNumber
].ColName
))
2608 if (colNumber
< m_numCols
)
2610 colDefs
[colNumber
].Null
= set
;
2611 if (set
) // Blank out the values in the member variable
2612 ClearMemberVar((UWORD
)colNumber
,false); // Must call with false here, or infinite recursion will happen
2614 setCbValueForColumn(colNumber
);
2621 } // wxDbTable::SetColNull()
2624 /********** wxDbTable::GetNewCursor() **********/
2625 HSTMT
*wxDbTable::GetNewCursor(bool setCursor
, bool bindColumns
)
2627 HSTMT
*newHSTMT
= new HSTMT
;
2632 if (SQLAllocStmt(hdbc
, newHSTMT
) != SQL_SUCCESS
)
2634 pDb
->DispAllErrors(henv
, hdbc
);
2639 if (SQLSetStmtOption(*newHSTMT
, SQL_CURSOR_TYPE
, cursorType
) != SQL_SUCCESS
)
2641 pDb
->DispAllErrors(henv
, hdbc
, *newHSTMT
);
2648 if (!bindCols(*newHSTMT
))
2656 SetCursor(newHSTMT
);
2660 } // wxDbTable::GetNewCursor()
2663 /********** wxDbTable::DeleteCursor() **********/
2664 bool wxDbTable::DeleteCursor(HSTMT
*hstmtDel
)
2668 if (!hstmtDel
) // Cursor already deleted
2672 ODBC 3.0 says to use this form
2673 if (SQLFreeHandle(*hstmtDel, SQL_DROP) != SQL_SUCCESS)
2676 if (SQLFreeStmt(*hstmtDel
, SQL_DROP
) != SQL_SUCCESS
)
2678 pDb
->DispAllErrors(henv
, hdbc
);
2686 } // wxDbTable::DeleteCursor()
2688 //////////////////////////////////////////////////////////////
2689 // wxDbGrid support functions
2690 //////////////////////////////////////////////////////////////
2692 void wxDbTable::SetRowMode(const rowmode_t rowmode
)
2694 if (!m_hstmtGridQuery
)
2696 m_hstmtGridQuery
= GetNewCursor(false,false);
2697 if (!bindCols(*m_hstmtGridQuery
))
2701 m_rowmode
= rowmode
;
2704 case WX_ROW_MODE_QUERY
:
2705 SetCursor(m_hstmtGridQuery
);
2707 case WX_ROW_MODE_INDIVIDUAL
:
2708 SetCursor(hstmtDefault
);
2713 } // wxDbTable::SetRowMode()
2716 wxVariant
wxDbTable::GetColumn(const int colNumber
) const
2719 if ((colNumber
< m_numCols
) && (!IsColNull((UWORD
)colNumber
)))
2721 switch (colDefs
[colNumber
].SqlCtype
)
2724 #if defined(SQL_WCHAR)
2727 #if defined(SQL_WVARCHAR)
2733 val
= (wxChar
*)(colDefs
[colNumber
].PtrDataObj
);
2737 val
= *(long *)(colDefs
[colNumber
].PtrDataObj
);
2741 val
= (long int )(*(short *)(colDefs
[colNumber
].PtrDataObj
));
2744 val
= (long)(*(unsigned long *)(colDefs
[colNumber
].PtrDataObj
));
2747 val
= (long)(*(wxChar
*)(colDefs
[colNumber
].PtrDataObj
));
2749 case SQL_C_UTINYINT
:
2750 val
= (long)(*(wxChar
*)(colDefs
[colNumber
].PtrDataObj
));
2753 val
= (long)(*(UWORD
*)(colDefs
[colNumber
].PtrDataObj
));
2756 val
= (DATE_STRUCT
*)(colDefs
[colNumber
].PtrDataObj
);
2759 val
= (TIME_STRUCT
*)(colDefs
[colNumber
].PtrDataObj
);
2761 case SQL_C_TIMESTAMP
:
2762 val
= (TIMESTAMP_STRUCT
*)(colDefs
[colNumber
].PtrDataObj
);
2765 val
= *(double *)(colDefs
[colNumber
].PtrDataObj
);
2772 } // wxDbTable::GetCol()
2775 void wxDbTable::SetColumn(const int colNumber
, const wxVariant val
)
2777 //FIXME: Add proper wxDateTime support to wxVariant..
2780 SetColNull((UWORD
)colNumber
, val
.IsNull());
2784 if ((colDefs
[colNumber
].SqlCtype
== SQL_C_DATE
)
2785 || (colDefs
[colNumber
].SqlCtype
== SQL_C_TIME
)
2786 || (colDefs
[colNumber
].SqlCtype
== SQL_C_TIMESTAMP
))
2788 //Returns null if invalid!
2789 if (!dateval
.ParseDate(val
.GetString()))
2790 SetColNull((UWORD
)colNumber
, true);
2793 switch (colDefs
[colNumber
].SqlCtype
)
2796 #if defined(SQL_WCHAR)
2799 #if defined(SQL_WVARCHAR)
2805 csstrncpyt((wxChar
*)(colDefs
[colNumber
].PtrDataObj
),
2806 val
.GetString().c_str(),
2807 colDefs
[colNumber
].SzDataObj
-1); //TODO: glt ??? * sizeof(wxChar) ???
2811 *(long *)(colDefs
[colNumber
].PtrDataObj
) = val
;
2815 *(short *)(colDefs
[colNumber
].PtrDataObj
) = (short)val
.GetLong();
2818 *(unsigned long *)(colDefs
[colNumber
].PtrDataObj
) = val
.GetLong();
2821 *(wxChar
*)(colDefs
[colNumber
].PtrDataObj
) = val
.GetChar();
2823 case SQL_C_UTINYINT
:
2824 *(wxChar
*)(colDefs
[colNumber
].PtrDataObj
) = val
.GetChar();
2827 *(unsigned short *)(colDefs
[colNumber
].PtrDataObj
) = (unsigned short)val
.GetLong();
2829 //FIXME: Add proper wxDateTime support to wxVariant..
2832 DATE_STRUCT
*dataptr
=
2833 (DATE_STRUCT
*)colDefs
[colNumber
].PtrDataObj
;
2835 dataptr
->year
= (SWORD
)dateval
.GetYear();
2836 dataptr
->month
= (UWORD
)(dateval
.GetMonth()+1);
2837 dataptr
->day
= (UWORD
)dateval
.GetDay();
2842 TIME_STRUCT
*dataptr
=
2843 (TIME_STRUCT
*)colDefs
[colNumber
].PtrDataObj
;
2845 dataptr
->hour
= dateval
.GetHour();
2846 dataptr
->minute
= dateval
.GetMinute();
2847 dataptr
->second
= dateval
.GetSecond();
2850 case SQL_C_TIMESTAMP
:
2852 TIMESTAMP_STRUCT
*dataptr
=
2853 (TIMESTAMP_STRUCT
*)colDefs
[colNumber
].PtrDataObj
;
2854 dataptr
->year
= (SWORD
)dateval
.GetYear();
2855 dataptr
->month
= (UWORD
)(dateval
.GetMonth()+1);
2856 dataptr
->day
= (UWORD
)dateval
.GetDay();
2858 dataptr
->hour
= dateval
.GetHour();
2859 dataptr
->minute
= dateval
.GetMinute();
2860 dataptr
->second
= dateval
.GetSecond();
2864 *(double *)(colDefs
[colNumber
].PtrDataObj
) = val
;
2869 } // if (!val.IsNull())
2870 } // wxDbTable::SetCol()
2873 GenericKey
wxDbTable::GetKey()
2878 blk
= malloc(m_keysize
);
2879 blkptr
= (wxChar
*) blk
;
2882 for (i
=0; i
< m_numCols
; i
++)
2884 if (colDefs
[i
].KeyField
)
2886 memcpy(blkptr
,colDefs
[i
].PtrDataObj
, colDefs
[i
].SzDataObj
);
2887 blkptr
+= colDefs
[i
].SzDataObj
;
2891 GenericKey k
= GenericKey(blk
, m_keysize
);
2895 } // wxDbTable::GetKey()
2898 void wxDbTable::SetKey(const GenericKey
& k
)
2904 blkptr
= (wxChar
*)blk
;
2907 for (i
=0; i
< m_numCols
; i
++)
2909 if (colDefs
[i
].KeyField
)
2911 SetColNull((UWORD
)i
, false);
2912 memcpy(colDefs
[i
].PtrDataObj
, blkptr
, colDefs
[i
].SzDataObj
);
2913 blkptr
+= colDefs
[i
].SzDataObj
;
2916 } // wxDbTable::SetKey()
2919 #endif // wxUSE_ODBC