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"
25 #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
!= 0 )
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 /********** wxDbTable::~wxDbTable() **********/
111 wxDbTable::~wxDbTable()
114 } // wxDbTable::~wxDbTable()
117 bool wxDbTable::initialize(wxDb
*pwxDb
, const wxString
&tblName
, const UWORD numColumns
,
118 const wxString
&qryTblName
, bool qryOnly
, const wxString
&tblPath
)
120 // Initializing member variables
121 pDb
= pwxDb
; // Pointer to the wxDb object
125 m_hstmtGridQuery
= 0;
126 hstmtDefault
= 0; // Initialized below
127 hstmtCount
= 0; // Initialized first time it is needed
134 m_numCols
= numColumns
; // Number of columns in the table
135 where
.Empty(); // Where clause
136 orderBy
.Empty(); // Order By clause
137 from
.Empty(); // From clause
138 selectForUpdate
= false; // SELECT ... FOR UPDATE; Indicates whether to include the FOR UPDATE phrase
143 queryTableName
.Empty();
145 wxASSERT(tblName
.length());
151 tableName
= tblName
; // Table Name
152 if ((pDb
->Dbms() == dbmsORACLE
) ||
153 (pDb
->Dbms() == dbmsFIREBIRD
) ||
154 (pDb
->Dbms() == dbmsINTERBASE
))
155 tableName
= tableName
.Upper();
157 if (tblPath
.length())
158 tablePath
= tblPath
; // Table Path - used for dBase files
162 if (qryTblName
.length()) // Name of the table/view to query
163 queryTableName
= qryTblName
;
165 queryTableName
= tblName
;
167 if ((pDb
->Dbms() == dbmsORACLE
) ||
168 (pDb
->Dbms() == dbmsFIREBIRD
) ||
169 (pDb
->Dbms() == dbmsINTERBASE
))
170 queryTableName
= queryTableName
.Upper();
172 pDb
->incrementTableCount();
175 tableID
= ++lastTableID
;
176 s
.Printf(wxT("wxDbTable constructor (%-20s) tableID:[%6lu] pDb:[%p]"),
177 tblName
.c_str(), tableID
, wx_static_cast(void*, pDb
));
180 wxTablesInUse
*tableInUse
;
181 tableInUse
= new wxTablesInUse();
182 tableInUse
->tableName
= tblName
;
183 tableInUse
->tableID
= tableID
;
184 tableInUse
->pDb
= pDb
;
185 TablesInUse
.Append(tableInUse
);
190 // Grab the HENV and HDBC from the wxDb object
191 henv
= pDb
->GetHENV();
192 hdbc
= pDb
->GetHDBC();
194 // Allocate space for column definitions
196 colDefs
= new wxDbColDef
[m_numCols
]; // Points to the first column definition
198 // Allocate statement handles for the table
201 // Allocate a separate statement handle for performing inserts
202 if (SQLAllocStmt(hdbc
, &hstmtInsert
) != SQL_SUCCESS
)
203 pDb
->DispAllErrors(henv
, hdbc
);
204 // Allocate a separate statement handle for performing deletes
205 if (SQLAllocStmt(hdbc
, &hstmtDelete
) != SQL_SUCCESS
)
206 pDb
->DispAllErrors(henv
, hdbc
);
207 // Allocate a separate statement handle for performing updates
208 if (SQLAllocStmt(hdbc
, &hstmtUpdate
) != SQL_SUCCESS
)
209 pDb
->DispAllErrors(henv
, hdbc
);
211 // Allocate a separate statement handle for internal use
212 if (SQLAllocStmt(hdbc
, &hstmtInternal
) != SQL_SUCCESS
)
213 pDb
->DispAllErrors(henv
, hdbc
);
215 // Set the cursor type for the statement handles
216 cursorType
= SQL_CURSOR_STATIC
;
218 if (SQLSetStmtOption(hstmtInternal
, SQL_CURSOR_TYPE
, cursorType
) != SQL_SUCCESS
)
220 // Check to see if cursor type is supported
221 pDb
->GetNextError(henv
, hdbc
, hstmtInternal
);
222 if (! wxStrcmp(pDb
->sqlState
, wxT("01S02"))) // Option Value Changed
224 // Datasource does not support static cursors. Driver
225 // will substitute a cursor type. Call SQLGetStmtOption()
226 // to determine which cursor type was selected.
227 if (SQLGetStmtOption(hstmtInternal
, SQL_CURSOR_TYPE
, &cursorType
) != SQL_SUCCESS
)
228 pDb
->DispAllErrors(henv
, hdbc
, hstmtInternal
);
229 #ifdef DBDEBUG_CONSOLE
230 cout
<< wxT("Static cursor changed to: ");
233 case SQL_CURSOR_FORWARD_ONLY
:
234 cout
<< wxT("Forward Only");
236 case SQL_CURSOR_STATIC
:
237 cout
<< wxT("Static");
239 case SQL_CURSOR_KEYSET_DRIVEN
:
240 cout
<< wxT("Keyset Driven");
242 case SQL_CURSOR_DYNAMIC
:
243 cout
<< wxT("Dynamic");
246 cout
<< endl
<< endl
;
249 if (pDb
->FwdOnlyCursors() && cursorType
!= SQL_CURSOR_FORWARD_ONLY
)
251 // Force the use of a forward only cursor...
252 cursorType
= SQL_CURSOR_FORWARD_ONLY
;
253 if (SQLSetStmtOption(hstmtInternal
, SQL_CURSOR_TYPE
, cursorType
) != SQL_SUCCESS
)
255 // Should never happen
256 pDb
->GetNextError(henv
, hdbc
, hstmtInternal
);
263 pDb
->DispNextError();
264 pDb
->DispAllErrors(henv
, hdbc
, hstmtInternal
);
267 #ifdef DBDEBUG_CONSOLE
269 cout
<< wxT("Cursor Type set to STATIC") << endl
<< endl
;
274 // Set the cursor type for the INSERT statement handle
275 if (SQLSetStmtOption(hstmtInsert
, SQL_CURSOR_TYPE
, SQL_CURSOR_FORWARD_ONLY
) != SQL_SUCCESS
)
276 pDb
->DispAllErrors(henv
, hdbc
, hstmtInsert
);
277 // Set the cursor type for the DELETE statement handle
278 if (SQLSetStmtOption(hstmtDelete
, SQL_CURSOR_TYPE
, SQL_CURSOR_FORWARD_ONLY
) != SQL_SUCCESS
)
279 pDb
->DispAllErrors(henv
, hdbc
, hstmtDelete
);
280 // Set the cursor type for the UPDATE statement handle
281 if (SQLSetStmtOption(hstmtUpdate
, SQL_CURSOR_TYPE
, SQL_CURSOR_FORWARD_ONLY
) != SQL_SUCCESS
)
282 pDb
->DispAllErrors(henv
, hdbc
, hstmtUpdate
);
285 // Make the default cursor the active cursor
286 hstmtDefault
= GetNewCursor(false,false);
287 wxASSERT(hstmtDefault
);
288 hstmt
= *hstmtDefault
;
292 } // wxDbTable::initialize()
295 void wxDbTable::cleanup()
300 s
.Printf(wxT("wxDbTable destructor (%-20s) tableID:[%6lu] pDb:[%p]"),
301 tableName
.c_str(), tableID
, wx_static_cast(void*, pDb
));
310 wxList::compatibility_iterator pNode
;
311 pNode
= TablesInUse
.GetFirst();
312 while (pNode
&& !found
)
314 if (((wxTablesInUse
*)pNode
->GetData())->tableID
== tableID
)
317 delete (wxTablesInUse
*)pNode
->GetData();
318 TablesInUse
.Erase(pNode
);
321 pNode
= pNode
->GetNext();
326 msg
.Printf(wxT("Unable to find the tableID in the linked\nlist of tables in use.\n\n%s"),s
.c_str());
327 wxLogDebug (msg
,wxT("NOTICE..."));
332 // Decrement the wxDb table count
334 pDb
->decrementTableCount();
336 // Delete memory allocated for column definitions
340 // Free statement handles
346 ODBC 3.0 says to use this form
347 if (SQLFreeHandle(*hstmtDel, SQL_DROP) != SQL_SUCCESS)
349 if (SQLFreeStmt(hstmtInsert
, SQL_DROP
) != SQL_SUCCESS
)
350 pDb
->DispAllErrors(henv
, hdbc
);
356 ODBC 3.0 says to use this form
357 if (SQLFreeHandle(*hstmtDel, SQL_DROP) != SQL_SUCCESS)
359 if (SQLFreeStmt(hstmtDelete
, SQL_DROP
) != SQL_SUCCESS
)
360 pDb
->DispAllErrors(henv
, hdbc
);
366 ODBC 3.0 says to use this form
367 if (SQLFreeHandle(*hstmtDel, SQL_DROP) != SQL_SUCCESS)
369 if (SQLFreeStmt(hstmtUpdate
, SQL_DROP
) != SQL_SUCCESS
)
370 pDb
->DispAllErrors(henv
, hdbc
);
376 if (SQLFreeStmt(hstmtInternal
, SQL_DROP
) != SQL_SUCCESS
)
377 pDb
->DispAllErrors(henv
, hdbc
);
380 // Delete dynamically allocated cursors
382 DeleteCursor(hstmtDefault
);
385 DeleteCursor(hstmtCount
);
387 if (m_hstmtGridQuery
)
388 DeleteCursor(m_hstmtGridQuery
);
390 } // wxDbTable::cleanup()
393 /***************************** PRIVATE FUNCTIONS *****************************/
396 void wxDbTable::setCbValueForColumn(int columnIndex
)
398 switch(colDefs
[columnIndex
].DbDataType
)
400 case DB_DATA_TYPE_VARCHAR
:
401 case DB_DATA_TYPE_MEMO
:
402 if (colDefs
[columnIndex
].Null
)
403 colDefs
[columnIndex
].CbValue
= SQL_NULL_DATA
;
405 colDefs
[columnIndex
].CbValue
= SQL_NTS
;
407 case DB_DATA_TYPE_INTEGER
:
408 if (colDefs
[columnIndex
].Null
)
409 colDefs
[columnIndex
].CbValue
= SQL_NULL_DATA
;
411 colDefs
[columnIndex
].CbValue
= 0;
413 case DB_DATA_TYPE_FLOAT
:
414 if (colDefs
[columnIndex
].Null
)
415 colDefs
[columnIndex
].CbValue
= SQL_NULL_DATA
;
417 colDefs
[columnIndex
].CbValue
= 0;
419 case DB_DATA_TYPE_DATE
:
420 if (colDefs
[columnIndex
].Null
)
421 colDefs
[columnIndex
].CbValue
= SQL_NULL_DATA
;
423 colDefs
[columnIndex
].CbValue
= 0;
425 case DB_DATA_TYPE_BLOB
:
426 if (colDefs
[columnIndex
].Null
)
427 colDefs
[columnIndex
].CbValue
= SQL_NULL_DATA
;
429 if (colDefs
[columnIndex
].SqlCtype
== SQL_C_WXCHAR
)
430 colDefs
[columnIndex
].CbValue
= SQL_NTS
;
432 colDefs
[columnIndex
].CbValue
= SQL_LEN_DATA_AT_EXEC(colDefs
[columnIndex
].SzDataObj
);
437 /********** wxDbTable::bindParams() **********/
438 bool wxDbTable::bindParams(bool forUpdate
)
440 wxASSERT(!queryOnly
);
445 SDWORD precision
= 0;
448 // Bind each column of the table that should be bound
449 // to a parameter marker
453 for (i
=0, colNumber
=1; i
< m_numCols
; i
++)
457 if (!colDefs
[i
].Updateable
)
462 if (!colDefs
[i
].InsertAllowed
)
466 switch(colDefs
[i
].DbDataType
)
468 case DB_DATA_TYPE_VARCHAR
:
469 fSqlType
= pDb
->GetTypeInfVarchar().FsqlType
;
470 precision
= colDefs
[i
].SzDataObj
;
473 case DB_DATA_TYPE_MEMO
:
474 fSqlType
= pDb
->GetTypeInfMemo().FsqlType
;
475 precision
= colDefs
[i
].SzDataObj
;
478 case DB_DATA_TYPE_INTEGER
:
479 fSqlType
= pDb
->GetTypeInfInteger().FsqlType
;
480 precision
= pDb
->GetTypeInfInteger().Precision
;
483 case DB_DATA_TYPE_FLOAT
:
484 fSqlType
= pDb
->GetTypeInfFloat().FsqlType
;
485 precision
= pDb
->GetTypeInfFloat().Precision
;
486 scale
= pDb
->GetTypeInfFloat().MaximumScale
;
487 // SQL Sybase Anywhere v5.5 returned a negative number for the
488 // MaxScale. This caused ODBC to kick out an error on ibscale.
489 // I check for this here and set the scale = precision.
491 // scale = (short) precision;
493 case DB_DATA_TYPE_DATE
:
494 fSqlType
= pDb
->GetTypeInfDate().FsqlType
;
495 precision
= pDb
->GetTypeInfDate().Precision
;
498 case DB_DATA_TYPE_BLOB
:
499 fSqlType
= pDb
->GetTypeInfBlob().FsqlType
;
500 precision
= colDefs
[i
].SzDataObj
;
505 setCbValueForColumn(i
);
509 if (SQLBindParameter(hstmtUpdate
, colNumber
++, SQL_PARAM_INPUT
, colDefs
[i
].SqlCtype
,
510 fSqlType
, precision
, scale
, (UCHAR
*) colDefs
[i
].PtrDataObj
,
511 precision
+1, &colDefs
[i
].CbValue
) != SQL_SUCCESS
)
513 return(pDb
->DispAllErrors(henv
, hdbc
, hstmtUpdate
));
518 if (SQLBindParameter(hstmtInsert
, colNumber
++, SQL_PARAM_INPUT
, colDefs
[i
].SqlCtype
,
519 fSqlType
, precision
, scale
, (UCHAR
*) colDefs
[i
].PtrDataObj
,
520 precision
+1, &colDefs
[i
].CbValue
) != SQL_SUCCESS
)
522 return(pDb
->DispAllErrors(henv
, hdbc
, hstmtInsert
));
527 // Completed successfully
530 } // wxDbTable::bindParams()
533 /********** wxDbTable::bindInsertParams() **********/
534 bool wxDbTable::bindInsertParams(void)
536 return bindParams(false);
537 } // wxDbTable::bindInsertParams()
540 /********** wxDbTable::bindUpdateParams() **********/
541 bool wxDbTable::bindUpdateParams(void)
543 return bindParams(true);
544 } // wxDbTable::bindUpdateParams()
547 /********** wxDbTable::bindCols() **********/
548 bool wxDbTable::bindCols(HSTMT cursor
)
550 // Bind each column of the table to a memory address for fetching data
552 for (i
= 0; i
< m_numCols
; i
++)
554 if (SQLBindCol(cursor
, (UWORD
)(i
+1), colDefs
[i
].SqlCtype
, (UCHAR
*) colDefs
[i
].PtrDataObj
,
555 colDefs
[i
].SzDataObj
, &colDefs
[i
].CbValue
) != SQL_SUCCESS
)
556 return (pDb
->DispAllErrors(henv
, hdbc
, cursor
));
559 // Completed successfully
561 } // wxDbTable::bindCols()
564 /********** wxDbTable::getRec() **********/
565 bool wxDbTable::getRec(UWORD fetchType
)
569 if (!pDb
->FwdOnlyCursors())
571 // Fetch the NEXT, PREV, FIRST or LAST record, depending on fetchType
572 SQLULEN cRowsFetched
;
575 retcode
= SQLExtendedFetch(hstmt
, fetchType
, 0, &cRowsFetched
, &rowStatus
);
576 if (retcode
!= SQL_SUCCESS
&& retcode
!= SQL_SUCCESS_WITH_INFO
)
578 if (retcode
== SQL_NO_DATA_FOUND
)
581 return(pDb
->DispAllErrors(henv
, hdbc
, hstmt
));
585 // Set the Null member variable to indicate the Null state
586 // of each column just read in.
588 for (i
= 0; i
< m_numCols
; i
++)
589 colDefs
[i
].Null
= (colDefs
[i
].CbValue
== SQL_NULL_DATA
);
594 // Fetch the next record from the record set
595 retcode
= SQLFetch(hstmt
);
596 if (retcode
!= SQL_SUCCESS
&& retcode
!= SQL_SUCCESS_WITH_INFO
)
598 if (retcode
== SQL_NO_DATA_FOUND
)
601 return(pDb
->DispAllErrors(henv
, hdbc
, hstmt
));
605 // Set the Null member variable to indicate the Null state
606 // of each column just read in.
608 for (i
= 0; i
< m_numCols
; i
++)
609 colDefs
[i
].Null
= (colDefs
[i
].CbValue
== SQL_NULL_DATA
);
613 // Completed successfully
616 } // wxDbTable::getRec()
619 /********** wxDbTable::execDelete() **********/
620 bool wxDbTable::execDelete(const wxString
&pSqlStmt
)
624 // Execute the DELETE statement
625 retcode
= SQLExecDirect(hstmtDelete
, (SQLTCHAR FAR
*) pSqlStmt
.c_str(), SQL_NTS
);
627 if (retcode
== SQL_SUCCESS
||
628 retcode
== SQL_NO_DATA_FOUND
||
629 retcode
== SQL_SUCCESS_WITH_INFO
)
631 // Record deleted successfully
635 // Problem deleting record
636 return(pDb
->DispAllErrors(henv
, hdbc
, hstmtDelete
));
638 } // wxDbTable::execDelete()
641 /********** wxDbTable::execUpdate() **********/
642 bool wxDbTable::execUpdate(const wxString
&pSqlStmt
)
646 // Execute the UPDATE statement
647 retcode
= SQLExecDirect(hstmtUpdate
, (SQLTCHAR FAR
*) pSqlStmt
.c_str(), SQL_NTS
);
649 if (retcode
== SQL_SUCCESS
||
650 retcode
== SQL_NO_DATA_FOUND
||
651 retcode
== SQL_SUCCESS_WITH_INFO
)
653 // Record updated successfully
656 else if (retcode
== SQL_NEED_DATA
)
659 retcode
= SQLParamData(hstmtUpdate
, &pParmID
);
660 while (retcode
== SQL_NEED_DATA
)
662 // Find the parameter
664 for (i
=0; i
< m_numCols
; i
++)
666 if (colDefs
[i
].PtrDataObj
== pParmID
)
668 // We found it. Store the parameter.
669 retcode
= SQLPutData(hstmtUpdate
, pParmID
, colDefs
[i
].SzDataObj
);
670 if (retcode
!= SQL_SUCCESS
)
672 pDb
->DispNextError();
673 return pDb
->DispAllErrors(henv
, hdbc
, hstmtUpdate
);
678 retcode
= SQLParamData(hstmtUpdate
, &pParmID
);
680 if (retcode
== SQL_SUCCESS
||
681 retcode
== SQL_NO_DATA_FOUND
||
682 retcode
== SQL_SUCCESS_WITH_INFO
)
684 // Record updated successfully
689 // Problem updating record
690 return(pDb
->DispAllErrors(henv
, hdbc
, hstmtUpdate
));
692 } // wxDbTable::execUpdate()
695 /********** wxDbTable::query() **********/
696 bool wxDbTable::query(int queryType
, bool forUpdate
, bool distinct
, const wxString
&pSqlStmt
)
701 // The user may wish to select for update, but the DBMS may not be capable
702 selectForUpdate
= CanSelectForUpdate();
704 selectForUpdate
= false;
706 // Set the SQL SELECT string
707 if (queryType
!= DB_SELECT_STATEMENT
) // A select statement was not passed in,
708 { // so generate a select statement.
709 BuildSelectStmt(sqlStmt
, queryType
, distinct
);
710 pDb
->WriteSqlLog(sqlStmt
);
713 // Make sure the cursor is closed first
714 if (!CloseCursor(hstmt
))
717 // Execute the SQL SELECT statement
719 retcode
= SQLExecDirect(hstmt
, (SQLTCHAR FAR
*) (queryType
== DB_SELECT_STATEMENT
? pSqlStmt
.c_str() : sqlStmt
.c_str()), SQL_NTS
);
720 if (retcode
!= SQL_SUCCESS
&& retcode
!= SQL_SUCCESS_WITH_INFO
)
721 return(pDb
->DispAllErrors(henv
, hdbc
, hstmt
));
723 // Completed successfully
726 } // wxDbTable::query()
729 /***************************** PUBLIC FUNCTIONS *****************************/
732 /********** wxDbTable::Open() **********/
733 bool wxDbTable::Open(bool checkPrivileges
, bool checkTableExists
)
742 // Calculate the maximum size of the concatenated
743 // keys for use with wxDbGrid
745 for (i
=0; i
< m_numCols
; i
++)
747 if (colDefs
[i
].KeyField
)
749 m_keysize
+= colDefs
[i
].SzDataObj
;
756 if (checkTableExists
)
758 if (pDb
->Dbms() == dbmsPOSTGRES
)
759 exists
= pDb
->TableExists(tableName
, NULL
, tablePath
);
761 exists
= pDb
->TableExists(tableName
, pDb
->GetUsername(), tablePath
);
764 // Verify that the table exists in the database
767 s
= wxT("Table/view does not exist in the database");
768 if ( *(pDb
->dbInf
.accessibleTables
) == wxT('Y'))
769 s
+= wxT(", or you have no permissions.\n");
773 else if (checkPrivileges
)
775 // Verify the user has rights to access the table.
776 bool hasPrivs
wxDUMMY_INITIALIZE(true);
778 if (pDb
->Dbms() == dbmsPOSTGRES
)
779 hasPrivs
= pDb
->TablePrivileges(tableName
, wxT("SELECT"), pDb
->GetUsername(), NULL
, tablePath
);
781 hasPrivs
= pDb
->TablePrivileges(tableName
, wxT("SELECT"), pDb
->GetUsername(), pDb
->GetUsername(), tablePath
);
784 s
= wxT("Connecting user does not have sufficient privileges to access this table.\n");
791 if (!tablePath
.empty())
792 p
.Printf(wxT("Error opening '%s/%s'.\n"),tablePath
.c_str(),tableName
.c_str());
794 p
.Printf(wxT("Error opening '%s'.\n"), tableName
.c_str());
797 pDb
->LogError(p
.GetData());
802 // Bind the member variables for field exchange between
803 // the wxDbTable object and the ODBC record.
806 if (!bindInsertParams()) // Inserts
809 if (!bindUpdateParams()) // Updates
813 if (!bindCols(*hstmtDefault
)) // Selects
816 if (!bindCols(hstmtInternal
)) // Internal use only
820 * Do NOT bind the hstmtCount cursor!!!
823 // Build an insert statement using parameter markers
824 if (!queryOnly
&& m_numCols
> 0)
826 bool needComma
= false;
827 sqlStmt
.Printf(wxT("INSERT INTO %s ("),
828 pDb
->SQLTableName(tableName
.c_str()).c_str());
829 for (i
= 0; i
< m_numCols
; i
++)
831 if (! colDefs
[i
].InsertAllowed
)
835 sqlStmt
+= pDb
->SQLColumnName(colDefs
[i
].ColName
);
839 sqlStmt
+= wxT(") VALUES (");
841 int insertableCount
= 0;
843 for (i
= 0; i
< m_numCols
; i
++)
845 if (! colDefs
[i
].InsertAllowed
)
855 // Prepare the insert statement for execution
858 if (SQLPrepare(hstmtInsert
, (SQLTCHAR FAR
*) sqlStmt
.c_str(), SQL_NTS
) != SQL_SUCCESS
)
859 return(pDb
->DispAllErrors(henv
, hdbc
, hstmtInsert
));
865 // Completed successfully
868 } // wxDbTable::Open()
871 /********** wxDbTable::Query() **********/
872 bool wxDbTable::Query(bool forUpdate
, bool distinct
)
875 return(query(DB_SELECT_WHERE
, forUpdate
, distinct
));
877 } // wxDbTable::Query()
880 /********** wxDbTable::QueryBySqlStmt() **********/
881 bool wxDbTable::QueryBySqlStmt(const wxString
&pSqlStmt
)
883 pDb
->WriteSqlLog(pSqlStmt
);
885 return(query(DB_SELECT_STATEMENT
, false, false, pSqlStmt
));
887 } // wxDbTable::QueryBySqlStmt()
890 /********** wxDbTable::QueryMatching() **********/
891 bool wxDbTable::QueryMatching(bool forUpdate
, bool distinct
)
894 return(query(DB_SELECT_MATCHING
, forUpdate
, distinct
));
896 } // wxDbTable::QueryMatching()
899 /********** wxDbTable::QueryOnKeyFields() **********/
900 bool wxDbTable::QueryOnKeyFields(bool forUpdate
, bool distinct
)
903 return(query(DB_SELECT_KEYFIELDS
, forUpdate
, distinct
));
905 } // wxDbTable::QueryOnKeyFields()
908 /********** wxDbTable::GetPrev() **********/
909 bool wxDbTable::GetPrev(void)
911 if (pDb
->FwdOnlyCursors())
913 wxFAIL_MSG(wxT("GetPrev()::Backward scrolling cursors are not enabled for this instance of wxDbTable"));
917 return(getRec(SQL_FETCH_PRIOR
));
919 } // wxDbTable::GetPrev()
922 /********** wxDbTable::operator-- **********/
923 bool wxDbTable::operator--(int)
925 if (pDb
->FwdOnlyCursors())
927 wxFAIL_MSG(wxT("operator--:Backward scrolling cursors are not enabled for this instance of wxDbTable"));
931 return(getRec(SQL_FETCH_PRIOR
));
933 } // wxDbTable::operator--
936 /********** wxDbTable::GetFirst() **********/
937 bool wxDbTable::GetFirst(void)
939 if (pDb
->FwdOnlyCursors())
941 wxFAIL_MSG(wxT("GetFirst():Backward scrolling cursors are not enabled for this instance of wxDbTable"));
945 return(getRec(SQL_FETCH_FIRST
));
947 } // wxDbTable::GetFirst()
950 /********** wxDbTable::GetLast() **********/
951 bool wxDbTable::GetLast(void)
953 if (pDb
->FwdOnlyCursors())
955 wxFAIL_MSG(wxT("GetLast()::Backward scrolling cursors are not enabled for this instance of wxDbTable"));
959 return(getRec(SQL_FETCH_LAST
));
961 } // wxDbTable::GetLast()
964 /********** wxDbTable::BuildDeleteStmt() **********/
965 void wxDbTable::BuildDeleteStmt(wxString
&pSqlStmt
, int typeOfDel
, const wxString
&pWhereClause
)
967 wxASSERT(!queryOnly
);
971 wxString whereClause
;
975 // Handle the case of DeleteWhere() and the where clause is blank. It should
976 // delete all records from the database in this case.
977 if (typeOfDel
== DB_DEL_WHERE
&& (pWhereClause
.length() == 0))
979 pSqlStmt
.Printf(wxT("DELETE FROM %s"),
980 pDb
->SQLTableName(tableName
.c_str()).c_str());
984 pSqlStmt
.Printf(wxT("DELETE FROM %s WHERE "),
985 pDb
->SQLTableName(tableName
.c_str()).c_str());
987 // Append the WHERE clause to the SQL DELETE statement
990 case DB_DEL_KEYFIELDS
:
991 // If the datasource supports the ROWID column, build
992 // the where on ROWID for efficiency purposes.
993 // e.g. DELETE FROM PARTS WHERE ROWID = '111.222.333'
994 if (CanUpdateByROWID())
997 wxChar rowid
[wxDB_ROWID_LEN
+1];
999 // Get the ROWID value. If not successful retreiving the ROWID,
1000 // simply fall down through the code and build the WHERE clause
1001 // based on the key fields.
1002 if (SQLGetData(hstmt
, (UWORD
)(m_numCols
+1), SQL_C_WXCHAR
, (UCHAR
*) rowid
, sizeof(rowid
), &cb
) == SQL_SUCCESS
)
1004 pSqlStmt
+= wxT("ROWID = '");
1006 pSqlStmt
+= wxT("'");
1010 // Unable to delete by ROWID, so build a WHERE
1011 // clause based on the keyfields.
1012 BuildWhereClause(whereClause
, DB_WHERE_KEYFIELDS
);
1013 pSqlStmt
+= whereClause
;
1016 pSqlStmt
+= pWhereClause
;
1018 case DB_DEL_MATCHING
:
1019 BuildWhereClause(whereClause
, DB_WHERE_MATCHING
);
1020 pSqlStmt
+= whereClause
;
1024 } // BuildDeleteStmt()
1027 /***** DEPRECATED: use wxDbTable::BuildDeleteStmt(wxString &....) form *****/
1028 void wxDbTable::BuildDeleteStmt(wxChar
*pSqlStmt
, int typeOfDel
, const wxString
&pWhereClause
)
1030 wxString tempSqlStmt
;
1031 BuildDeleteStmt(tempSqlStmt
, typeOfDel
, pWhereClause
);
1032 wxStrcpy(pSqlStmt
, tempSqlStmt
);
1033 } // wxDbTable::BuildDeleteStmt()
1036 /********** wxDbTable::BuildSelectStmt() **********/
1037 void wxDbTable::BuildSelectStmt(wxString
&pSqlStmt
, int typeOfSelect
, bool distinct
)
1039 wxString whereClause
;
1040 whereClause
.Empty();
1042 // Build a select statement to query the database
1043 pSqlStmt
= wxT("SELECT ");
1045 // SELECT DISTINCT values only?
1047 pSqlStmt
+= wxT("DISTINCT ");
1049 // Was a FROM clause specified to join tables to the base table?
1050 // Available for ::Query() only!!!
1051 bool appendFromClause
= false;
1052 #if wxODBC_BACKWARD_COMPATABILITY
1053 if (typeOfSelect
== DB_SELECT_WHERE
&& from
&& wxStrlen(from
))
1054 appendFromClause
= true;
1056 if (typeOfSelect
== DB_SELECT_WHERE
&& from
.length())
1057 appendFromClause
= true;
1060 // Add the column list
1063 for (i
= 0; i
< m_numCols
; i
++)
1065 tStr
= colDefs
[i
].ColName
;
1066 // If joining tables, the base table column names must be qualified to avoid ambiguity
1067 if ((appendFromClause
|| pDb
->Dbms() == dbmsACCESS
) && tStr
.Find(wxT('.')) == wxNOT_FOUND
)
1069 pSqlStmt
+= pDb
->SQLTableName(queryTableName
.c_str());
1070 pSqlStmt
+= wxT(".");
1072 pSqlStmt
+= pDb
->SQLColumnName(colDefs
[i
].ColName
);
1073 if (i
+ 1 < m_numCols
)
1074 pSqlStmt
+= wxT(",");
1077 // If the datasource supports ROWID, get this column as well. Exception: Don't retrieve
1078 // the ROWID if querying distinct records. The rowid will always be unique.
1079 if (!distinct
&& CanUpdateByROWID())
1081 // If joining tables, the base table column names must be qualified to avoid ambiguity
1082 if (appendFromClause
|| pDb
->Dbms() == dbmsACCESS
)
1084 pSqlStmt
+= wxT(",");
1085 pSqlStmt
+= pDb
->SQLTableName(queryTableName
);
1086 pSqlStmt
+= wxT(".ROWID");
1089 pSqlStmt
+= wxT(",ROWID");
1092 // Append the FROM tablename portion
1093 pSqlStmt
+= wxT(" FROM ");
1094 pSqlStmt
+= pDb
->SQLTableName(queryTableName
);
1095 // pSqlStmt += queryTableName;
1097 // Sybase uses the HOLDLOCK keyword to lock a record during query.
1098 // The HOLDLOCK keyword follows the table name in the from clause.
1099 // Each table in the from clause must specify HOLDLOCK or
1100 // NOHOLDLOCK (the default). Note: The "FOR UPDATE" clause
1101 // is parsed but ignored in SYBASE Transact-SQL.
1102 if (selectForUpdate
&& (pDb
->Dbms() == dbmsSYBASE_ASA
|| pDb
->Dbms() == dbmsSYBASE_ASE
))
1103 pSqlStmt
+= wxT(" HOLDLOCK");
1105 if (appendFromClause
)
1108 // Append the WHERE clause. Either append the where clause for the class
1109 // or build a where clause. The typeOfSelect determines this.
1110 switch(typeOfSelect
)
1112 case DB_SELECT_WHERE
:
1113 #if wxODBC_BACKWARD_COMPATABILITY
1114 if (where
&& wxStrlen(where
)) // May not want a where clause!!!
1116 if (where
.length()) // May not want a where clause!!!
1119 pSqlStmt
+= wxT(" WHERE ");
1123 case DB_SELECT_KEYFIELDS
:
1124 BuildWhereClause(whereClause
, DB_WHERE_KEYFIELDS
);
1125 if (whereClause
.length())
1127 pSqlStmt
+= wxT(" WHERE ");
1128 pSqlStmt
+= whereClause
;
1131 case DB_SELECT_MATCHING
:
1132 BuildWhereClause(whereClause
, DB_WHERE_MATCHING
);
1133 if (whereClause
.length())
1135 pSqlStmt
+= wxT(" WHERE ");
1136 pSqlStmt
+= whereClause
;
1141 // Append the ORDER BY clause
1142 #if wxODBC_BACKWARD_COMPATABILITY
1143 if (orderBy
&& wxStrlen(orderBy
))
1145 if (orderBy
.length())
1148 pSqlStmt
+= wxT(" ORDER BY ");
1149 pSqlStmt
+= orderBy
;
1152 // SELECT FOR UPDATE if told to do so and the datasource is capable. Sybase
1153 // parses the FOR UPDATE clause but ignores it. See the comment above on the
1154 // HOLDLOCK for Sybase.
1155 if (selectForUpdate
&& CanSelectForUpdate())
1156 pSqlStmt
+= wxT(" FOR UPDATE");
1158 } // wxDbTable::BuildSelectStmt()
1161 /***** DEPRECATED: use wxDbTable::BuildSelectStmt(wxString &....) form *****/
1162 void wxDbTable::BuildSelectStmt(wxChar
*pSqlStmt
, int typeOfSelect
, bool distinct
)
1164 wxString tempSqlStmt
;
1165 BuildSelectStmt(tempSqlStmt
, typeOfSelect
, distinct
);
1166 wxStrcpy(pSqlStmt
, tempSqlStmt
);
1167 } // wxDbTable::BuildSelectStmt()
1170 /********** wxDbTable::BuildUpdateStmt() **********/
1171 void wxDbTable::BuildUpdateStmt(wxString
&pSqlStmt
, int typeOfUpdate
, const wxString
&pWhereClause
)
1173 wxASSERT(!queryOnly
);
1177 wxString whereClause
;
1178 whereClause
.Empty();
1180 bool firstColumn
= true;
1182 pSqlStmt
.Printf(wxT("UPDATE %s SET "),
1183 pDb
->SQLTableName(tableName
.c_str()).c_str());
1185 // Append a list of columns to be updated
1187 for (i
= 0; i
< m_numCols
; i
++)
1189 // Only append Updateable columns
1190 if (colDefs
[i
].Updateable
)
1193 pSqlStmt
+= wxT(",");
1195 firstColumn
= false;
1197 pSqlStmt
+= pDb
->SQLColumnName(colDefs
[i
].ColName
);
1198 // pSqlStmt += colDefs[i].ColName;
1199 pSqlStmt
+= wxT(" = ?");
1203 // Append the WHERE clause to the SQL UPDATE statement
1204 pSqlStmt
+= wxT(" WHERE ");
1205 switch(typeOfUpdate
)
1207 case DB_UPD_KEYFIELDS
:
1208 // If the datasource supports the ROWID column, build
1209 // the where on ROWID for efficiency purposes.
1210 // e.g. UPDATE PARTS SET Col1 = ?, Col2 = ? WHERE ROWID = '111.222.333'
1211 if (CanUpdateByROWID())
1214 wxChar rowid
[wxDB_ROWID_LEN
+1];
1216 // Get the ROWID value. If not successful retreiving the ROWID,
1217 // simply fall down through the code and build the WHERE clause
1218 // based on the key fields.
1219 if (SQLGetData(hstmt
, (UWORD
)(m_numCols
+1), SQL_C_WXCHAR
, (UCHAR
*) rowid
, sizeof(rowid
), &cb
) == SQL_SUCCESS
)
1221 pSqlStmt
+= wxT("ROWID = '");
1223 pSqlStmt
+= wxT("'");
1227 // Unable to delete by ROWID, so build a WHERE
1228 // clause based on the keyfields.
1229 BuildWhereClause(whereClause
, DB_WHERE_KEYFIELDS
);
1230 pSqlStmt
+= whereClause
;
1233 pSqlStmt
+= pWhereClause
;
1236 } // BuildUpdateStmt()
1239 /***** DEPRECATED: use wxDbTable::BuildUpdateStmt(wxString &....) form *****/
1240 void wxDbTable::BuildUpdateStmt(wxChar
*pSqlStmt
, int typeOfUpdate
, const wxString
&pWhereClause
)
1242 wxString tempSqlStmt
;
1243 BuildUpdateStmt(tempSqlStmt
, typeOfUpdate
, pWhereClause
);
1244 wxStrcpy(pSqlStmt
, tempSqlStmt
);
1245 } // BuildUpdateStmt()
1248 /********** wxDbTable::BuildWhereClause() **********/
1249 void wxDbTable::BuildWhereClause(wxString
&pWhereClause
, int typeOfWhere
,
1250 const wxString
&qualTableName
, bool useLikeComparison
)
1252 * Note: BuildWhereClause() currently ignores timestamp columns.
1253 * They are not included as part of the where clause.
1256 bool moreThanOneColumn
= false;
1259 // Loop through the columns building a where clause as you go
1261 for (colNumber
= 0; colNumber
< m_numCols
; colNumber
++)
1263 // Determine if this column should be included in the WHERE clause
1264 if ((typeOfWhere
== DB_WHERE_KEYFIELDS
&& colDefs
[colNumber
].KeyField
) ||
1265 (typeOfWhere
== DB_WHERE_MATCHING
&& (!IsColNull((UWORD
)colNumber
))))
1267 // Skip over timestamp columns
1268 if (colDefs
[colNumber
].SqlCtype
== SQL_C_TIMESTAMP
)
1270 // If there is more than 1 column, join them with the keyword "AND"
1271 if (moreThanOneColumn
)
1272 pWhereClause
+= wxT(" AND ");
1274 moreThanOneColumn
= true;
1276 // Concatenate where phrase for the column
1277 wxString tStr
= colDefs
[colNumber
].ColName
;
1279 if (qualTableName
.length() && tStr
.Find(wxT('.')) == wxNOT_FOUND
)
1281 pWhereClause
+= pDb
->SQLTableName(qualTableName
);
1282 pWhereClause
+= wxT(".");
1284 pWhereClause
+= pDb
->SQLColumnName(colDefs
[colNumber
].ColName
);
1286 if (useLikeComparison
&& (colDefs
[colNumber
].SqlCtype
== SQL_C_WXCHAR
))
1287 pWhereClause
+= wxT(" LIKE ");
1289 pWhereClause
+= wxT(" = ");
1291 switch(colDefs
[colNumber
].SqlCtype
)
1297 //case SQL_C_WXCHAR: SQL_C_WXCHAR is covered by either SQL_C_CHAR or SQL_C_WCHAR
1298 colValue
.Printf(wxT("'%s'"), GetDb()->EscapeSqlChars((wxChar
*)colDefs
[colNumber
].PtrDataObj
).c_str());
1302 colValue
.Printf(wxT("%hi"), *((SWORD
*) colDefs
[colNumber
].PtrDataObj
));
1305 colValue
.Printf(wxT("%hu"), *((UWORD
*) colDefs
[colNumber
].PtrDataObj
));
1309 colValue
.Printf(wxT("%li"), *((SDWORD
*) colDefs
[colNumber
].PtrDataObj
));
1312 colValue
.Printf(wxT("%lu"), *((UDWORD
*) colDefs
[colNumber
].PtrDataObj
));
1315 colValue
.Printf(wxT("%.6f"), *((SFLOAT
*) colDefs
[colNumber
].PtrDataObj
));
1318 colValue
.Printf(wxT("%.6f"), *((SDOUBLE
*) colDefs
[colNumber
].PtrDataObj
));
1323 strMsg
.Printf(wxT("wxDbTable::bindParams(): Unknown column type for colDefs %d colName %s"),
1324 colNumber
,colDefs
[colNumber
].ColName
);
1325 wxFAIL_MSG(strMsg
.c_str());
1329 pWhereClause
+= colValue
;
1332 } // wxDbTable::BuildWhereClause()
1335 /***** DEPRECATED: use wxDbTable::BuildWhereClause(wxString &....) form *****/
1336 void wxDbTable::BuildWhereClause(wxChar
*pWhereClause
, int typeOfWhere
,
1337 const wxString
&qualTableName
, bool useLikeComparison
)
1339 wxString tempSqlStmt
;
1340 BuildWhereClause(tempSqlStmt
, typeOfWhere
, qualTableName
, useLikeComparison
);
1341 wxStrcpy(pWhereClause
, tempSqlStmt
);
1342 } // wxDbTable::BuildWhereClause()
1345 /********** wxDbTable::GetRowNum() **********/
1346 UWORD
wxDbTable::GetRowNum(void)
1350 if (SQLGetStmtOption(hstmt
, SQL_ROW_NUMBER
, (UCHAR
*) &rowNum
) != SQL_SUCCESS
)
1352 pDb
->DispAllErrors(henv
, hdbc
, hstmt
);
1356 // Completed successfully
1357 return((UWORD
) rowNum
);
1359 } // wxDbTable::GetRowNum()
1362 /********** wxDbTable::CloseCursor() **********/
1363 bool wxDbTable::CloseCursor(HSTMT cursor
)
1365 if (SQLFreeStmt(cursor
, SQL_CLOSE
) != SQL_SUCCESS
)
1366 return(pDb
->DispAllErrors(henv
, hdbc
, cursor
));
1368 // Completed successfully
1371 } // wxDbTable::CloseCursor()
1374 /********** wxDbTable::CreateTable() **********/
1375 bool wxDbTable::CreateTable(bool attemptDrop
)
1383 #ifdef DBDEBUG_CONSOLE
1384 cout
<< wxT("Creating Table ") << tableName
<< wxT("...") << endl
;
1388 if (attemptDrop
&& !DropTable())
1392 #ifdef DBDEBUG_CONSOLE
1393 for (i
= 0; i
< m_numCols
; i
++)
1395 // Exclude derived columns since they are NOT part of the base table
1396 if (colDefs
[i
].DerivedCol
)
1398 cout
<< i
+ 1 << wxT(": ") << colDefs
[i
].ColName
<< wxT("; ");
1399 switch(colDefs
[i
].DbDataType
)
1401 case DB_DATA_TYPE_VARCHAR
:
1402 cout
<< pDb
->GetTypeInfVarchar().TypeName
<< wxT("(") << (int)(colDefs
[i
].SzDataObj
/ sizeof(wxChar
)) << wxT(")");
1404 case DB_DATA_TYPE_MEMO
:
1405 cout
<< pDb
->GetTypeInfMemo().TypeName
;
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
< m_numCols
; 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_MEMO
:
1449 sqlStmt
+= pDb
->GetTypeInfMemo().TypeName
;
1451 case DB_DATA_TYPE_INTEGER
:
1452 sqlStmt
+= pDb
->GetTypeInfInteger().TypeName
;
1454 case DB_DATA_TYPE_FLOAT
:
1455 sqlStmt
+= pDb
->GetTypeInfFloat().TypeName
;
1457 case DB_DATA_TYPE_DATE
:
1458 sqlStmt
+= pDb
->GetTypeInfDate().TypeName
;
1460 case DB_DATA_TYPE_BLOB
:
1461 sqlStmt
+= pDb
->GetTypeInfBlob().TypeName
;
1464 // For varchars, append the size of the string
1465 if (colDefs
[i
].DbDataType
== DB_DATA_TYPE_VARCHAR
&&
1466 (pDb
->Dbms() != dbmsMY_SQL
|| pDb
->GetTypeInfVarchar().TypeName
!= _T("text")))// ||
1467 // colDefs[i].DbDataType == DB_DATA_TYPE_BLOB)
1470 s
.Printf(wxT("(%d)"), (int)(colDefs
[i
].SzDataObj
/ sizeof(wxChar
)));
1474 if (pDb
->Dbms() == dbmsDB2
||
1475 pDb
->Dbms() == dbmsMY_SQL
||
1476 pDb
->Dbms() == dbmsSYBASE_ASE
||
1477 pDb
->Dbms() == dbmsINTERBASE
||
1478 pDb
->Dbms() == dbmsFIREBIRD
||
1479 pDb
->Dbms() == dbmsMS_SQL_SERVER
)
1481 if (colDefs
[i
].KeyField
)
1483 sqlStmt
+= wxT(" NOT NULL");
1489 // If there is a primary key defined, include it in the create statement
1490 for (i
= j
= 0; i
< m_numCols
; i
++)
1492 if (colDefs
[i
].KeyField
)
1498 if ( j
&& (pDb
->Dbms() != dbmsDBASE
)
1499 && (pDb
->Dbms() != dbmsXBASE_SEQUITER
) ) // Found a keyfield
1501 switch (pDb
->Dbms())
1505 case dbmsSYBASE_ASA
:
1506 case dbmsSYBASE_ASE
:
1510 // MySQL goes out on this one. We also declare the relevant key NON NULL above
1511 sqlStmt
+= wxT(",PRIMARY KEY (");
1516 sqlStmt
+= wxT(",CONSTRAINT ");
1517 // DB2 is limited to 18 characters for index names
1518 if (pDb
->Dbms() == dbmsDB2
)
1520 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."));
1521 sqlStmt
+= pDb
->SQLTableName(tableName
.substr(0, 13).c_str());
1522 // sqlStmt += tableName.substr(0, 13);
1525 sqlStmt
+= pDb
->SQLTableName(tableName
.c_str());
1526 // sqlStmt += tableName;
1528 sqlStmt
+= wxT("_PIDX PRIMARY KEY (");
1533 // List column name(s) of column(s) comprising the primary key
1534 for (i
= j
= 0; i
< m_numCols
; i
++)
1536 if (colDefs
[i
].KeyField
)
1538 if (j
++) // Multi part key, comma separate names
1539 sqlStmt
+= wxT(",");
1540 sqlStmt
+= pDb
->SQLColumnName(colDefs
[i
].ColName
);
1542 if (pDb
->Dbms() == dbmsMY_SQL
&&
1543 colDefs
[i
].DbDataType
== DB_DATA_TYPE_VARCHAR
)
1546 s
.Printf(wxT("(%d)"), (int)(colDefs
[i
].SzDataObj
/ sizeof(wxChar
)));
1551 sqlStmt
+= wxT(")");
1553 if (pDb
->Dbms() == dbmsINFORMIX
||
1554 pDb
->Dbms() == dbmsSYBASE_ASA
||
1555 pDb
->Dbms() == dbmsSYBASE_ASE
)
1557 sqlStmt
+= wxT(" CONSTRAINT ");
1558 sqlStmt
+= pDb
->SQLTableName(tableName
);
1559 // sqlStmt += tableName;
1560 sqlStmt
+= wxT("_PIDX");
1563 // Append the closing parentheses for the create table statement
1564 sqlStmt
+= wxT(")");
1566 pDb
->WriteSqlLog(sqlStmt
);
1568 #ifdef DBDEBUG_CONSOLE
1569 cout
<< endl
<< sqlStmt
.c_str() << endl
;
1572 // Execute the CREATE TABLE statement
1573 RETCODE retcode
= SQLExecDirect(hstmt
, (SQLTCHAR FAR
*) sqlStmt
.c_str(), SQL_NTS
);
1574 if (retcode
!= SQL_SUCCESS
&& retcode
!= SQL_SUCCESS_WITH_INFO
)
1576 pDb
->DispAllErrors(henv
, hdbc
, hstmt
);
1577 pDb
->RollbackTrans();
1582 // Commit the transaction and close the cursor
1583 if (!pDb
->CommitTrans())
1585 if (!CloseCursor(hstmt
))
1588 // Database table created successfully
1591 } // wxDbTable::CreateTable()
1594 /********** wxDbTable::DropTable() **********/
1595 bool wxDbTable::DropTable()
1597 // NOTE: This function returns true if the Table does not exist, but
1598 // only for identified databases. Code will need to be added
1599 // below for any other databases when those databases are defined
1600 // to handle this situation consistently
1604 sqlStmt
.Printf(wxT("DROP TABLE %s"),
1605 pDb
->SQLTableName(tableName
.c_str()).c_str());
1607 pDb
->WriteSqlLog(sqlStmt
);
1609 #ifdef DBDEBUG_CONSOLE
1610 cout
<< endl
<< sqlStmt
.c_str() << endl
;
1613 RETCODE retcode
= SQLExecDirect(hstmt
, (SQLTCHAR FAR
*) sqlStmt
.c_str(), SQL_NTS
);
1614 if (retcode
!= SQL_SUCCESS
)
1616 // Check for "Base table not found" error and ignore
1617 pDb
->GetNextError(henv
, hdbc
, hstmt
);
1618 if (wxStrcmp(pDb
->sqlState
, wxT("S0002")) /*&&
1619 wxStrcmp(pDb->sqlState, wxT("S1000"))*/) // "Base table not found"
1621 // Check for product specific error codes
1622 if (!((pDb
->Dbms() == dbmsSYBASE_ASA
&& !wxStrcmp(pDb
->sqlState
,wxT("42000"))) || // 5.x (and lower?)
1623 (pDb
->Dbms() == dbmsSYBASE_ASE
&& !wxStrcmp(pDb
->sqlState
,wxT("37000"))) ||
1624 (pDb
->Dbms() == dbmsPERVASIVE_SQL
&& !wxStrcmp(pDb
->sqlState
,wxT("S1000"))) || // Returns an S1000 then an S0002
1625 (pDb
->Dbms() == dbmsPOSTGRES
&& !wxStrcmp(pDb
->sqlState
,wxT("08S01")))))
1627 pDb
->DispNextError();
1628 pDb
->DispAllErrors(henv
, hdbc
, hstmt
);
1629 pDb
->RollbackTrans();
1630 // CloseCursor(hstmt);
1636 // Commit the transaction and close the cursor
1637 if (! pDb
->CommitTrans())
1639 if (! CloseCursor(hstmt
))
1643 } // wxDbTable::DropTable()
1646 /********** wxDbTable::CreateIndex() **********/
1647 bool wxDbTable::CreateIndex(const wxString
&indexName
, bool unique
, UWORD numIndexColumns
,
1648 wxDbIdxDef
*pIndexDefs
, bool attemptDrop
)
1652 // Drop the index first
1653 if (attemptDrop
&& !DropIndex(indexName
))
1656 // MySQL (and possibly Sybase ASE?? - gt) require that any columns which are used as portions
1657 // of an index have the columns defined as "NOT NULL". During initial table creation though,
1658 // it may not be known which columns are necessarily going to be part of an index (e.g. the
1659 // table was created, then months later you determine that an additional index while
1660 // give better performance, so you want to add an index).
1662 // The following block of code will modify the column definition to make the column be
1663 // defined with the "NOT NULL" qualifier.
1664 if (pDb
->Dbms() == dbmsMY_SQL
)
1669 for (i
= 0; i
< numIndexColumns
&& ok
; i
++)
1673 // Find the column definition that has the ColName that matches the
1674 // index column name. We need to do this to get the DB_DATA_TYPE of
1675 // the index column, as MySQL's syntax for the ALTER column requires
1677 while (!found
&& (j
< this->m_numCols
))
1679 if (wxStrcmp(colDefs
[j
].ColName
,pIndexDefs
[i
].ColName
) == 0)
1687 ok
= pDb
->ModifyColumn(tableName
, pIndexDefs
[i
].ColName
,
1688 colDefs
[j
].DbDataType
, (int)(colDefs
[j
].SzDataObj
/ sizeof(wxChar
)),
1694 // retcode is not used
1695 wxODBC_ERRORS retcode
;
1696 // Oracle returns a DB_ERR_GENERAL_ERROR if the column is already
1697 // defined to be NOT NULL, but reportedly MySQL doesn't mind.
1698 // This line is just here for debug checking of the value
1699 retcode
= (wxODBC_ERRORS
)pDb
->DB_STATUS
;
1710 pDb
->RollbackTrans();
1715 // Build a CREATE INDEX statement
1716 sqlStmt
= wxT("CREATE ");
1718 sqlStmt
+= wxT("UNIQUE ");
1720 sqlStmt
+= wxT("INDEX ");
1721 sqlStmt
+= pDb
->SQLTableName(indexName
);
1722 sqlStmt
+= wxT(" ON ");
1724 sqlStmt
+= pDb
->SQLTableName(tableName
);
1725 // sqlStmt += tableName;
1726 sqlStmt
+= wxT(" (");
1728 // Append list of columns making up index
1730 for (i
= 0; i
< numIndexColumns
; i
++)
1732 sqlStmt
+= pDb
->SQLColumnName(pIndexDefs
[i
].ColName
);
1733 // sqlStmt += pIndexDefs[i].ColName;
1735 // MySQL requires a key length on VARCHAR keys
1736 if ( pDb
->Dbms() == dbmsMY_SQL
)
1738 // Find the details on this column
1740 for ( j
= 0; j
< m_numCols
; ++j
)
1742 if ( wxStrcmp( pIndexDefs
[i
].ColName
, colDefs
[j
].ColName
) == 0 )
1747 if ( colDefs
[j
].DbDataType
== DB_DATA_TYPE_VARCHAR
)
1750 s
.Printf(wxT("(%d)"), (int)(colDefs
[i
].SzDataObj
/ sizeof(wxChar
)));
1755 // Postgres and SQL Server 7 do not support the ASC/DESC keywords for index columns
1756 if (!((pDb
->Dbms() == dbmsMS_SQL_SERVER
) && (wxStrncmp(pDb
->dbInf
.dbmsVer
,_T("07"),2)==0)) &&
1757 !(pDb
->Dbms() == dbmsFIREBIRD
) &&
1758 !(pDb
->Dbms() == dbmsPOSTGRES
))
1760 if (pIndexDefs
[i
].Ascending
)
1761 sqlStmt
+= wxT(" ASC");
1763 sqlStmt
+= wxT(" DESC");
1766 wxASSERT_MSG(pIndexDefs
[i
].Ascending
, _T("Datasource does not support DESCending index columns"));
1768 if ((i
+ 1) < numIndexColumns
)
1769 sqlStmt
+= wxT(",");
1772 // Append closing parentheses
1773 sqlStmt
+= wxT(")");
1775 pDb
->WriteSqlLog(sqlStmt
);
1777 #ifdef DBDEBUG_CONSOLE
1778 cout
<< endl
<< sqlStmt
.c_str() << endl
<< endl
;
1781 // Execute the CREATE INDEX statement
1782 RETCODE retcode
= SQLExecDirect(hstmt
, (SQLTCHAR FAR
*) sqlStmt
.c_str(), SQL_NTS
);
1783 if (retcode
!= SQL_SUCCESS
)
1785 pDb
->DispAllErrors(henv
, hdbc
, hstmt
);
1786 pDb
->RollbackTrans();
1791 // Commit the transaction and close the cursor
1792 if (! pDb
->CommitTrans())
1794 if (! CloseCursor(hstmt
))
1797 // Index Created Successfully
1800 } // wxDbTable::CreateIndex()
1803 /********** wxDbTable::DropIndex() **********/
1804 bool wxDbTable::DropIndex(const wxString
&indexName
)
1806 // NOTE: This function returns true if the Index does not exist, but
1807 // only for identified databases. Code will need to be added
1808 // below for any other databases when those databases are defined
1809 // to handle this situation consistently
1813 if (pDb
->Dbms() == dbmsACCESS
|| pDb
->Dbms() == dbmsMY_SQL
||
1814 pDb
->Dbms() == dbmsDBASE
/*|| Paradox needs this syntax too when we add support*/)
1815 sqlStmt
.Printf(wxT("DROP INDEX %s ON %s"),
1816 pDb
->SQLTableName(indexName
.c_str()).c_str(),
1817 pDb
->SQLTableName(tableName
.c_str()).c_str());
1818 else if ((pDb
->Dbms() == dbmsMS_SQL_SERVER
) ||
1819 (pDb
->Dbms() == dbmsSYBASE_ASE
) ||
1820 (pDb
->Dbms() == dbmsXBASE_SEQUITER
))
1821 sqlStmt
.Printf(wxT("DROP INDEX %s.%s"),
1822 pDb
->SQLTableName(tableName
.c_str()).c_str(),
1823 pDb
->SQLTableName(indexName
.c_str()).c_str());
1825 sqlStmt
.Printf(wxT("DROP INDEX %s"),
1826 pDb
->SQLTableName(indexName
.c_str()).c_str());
1828 pDb
->WriteSqlLog(sqlStmt
);
1830 #ifdef DBDEBUG_CONSOLE
1831 cout
<< endl
<< sqlStmt
.c_str() << endl
;
1833 RETCODE retcode
= SQLExecDirect(hstmt
, (SQLTCHAR FAR
*) sqlStmt
.c_str(), SQL_NTS
);
1834 if (retcode
!= SQL_SUCCESS
)
1836 // Check for "Index not found" error and ignore
1837 pDb
->GetNextError(henv
, hdbc
, hstmt
);
1838 if (wxStrcmp(pDb
->sqlState
,wxT("S0012"))) // "Index not found"
1840 // Check for product specific error codes
1841 if (!((pDb
->Dbms() == dbmsSYBASE_ASA
&& !wxStrcmp(pDb
->sqlState
,wxT("42000"))) || // v5.x (and lower?)
1842 (pDb
->Dbms() == dbmsSYBASE_ASE
&& !wxStrcmp(pDb
->sqlState
,wxT("37000"))) ||
1843 (pDb
->Dbms() == dbmsMS_SQL_SERVER
&& !wxStrcmp(pDb
->sqlState
,wxT("S1000"))) ||
1844 (pDb
->Dbms() == dbmsINTERBASE
&& !wxStrcmp(pDb
->sqlState
,wxT("S1000"))) ||
1845 (pDb
->Dbms() == dbmsMAXDB
&& !wxStrcmp(pDb
->sqlState
,wxT("S1000"))) ||
1846 (pDb
->Dbms() == dbmsFIREBIRD
&& !wxStrcmp(pDb
->sqlState
,wxT("HY000"))) ||
1847 (pDb
->Dbms() == dbmsSYBASE_ASE
&& !wxStrcmp(pDb
->sqlState
,wxT("S0002"))) || // Base table not found
1848 (pDb
->Dbms() == dbmsMY_SQL
&& !wxStrcmp(pDb
->sqlState
,wxT("42S12"))) || // tested by Christopher Ludwik Marino-Cebulski using v3.23.21beta
1849 (pDb
->Dbms() == dbmsPOSTGRES
&& !wxStrcmp(pDb
->sqlState
,wxT("08S01")))
1852 pDb
->DispNextError();
1853 pDb
->DispAllErrors(henv
, hdbc
, hstmt
);
1854 pDb
->RollbackTrans();
1861 // Commit the transaction and close the cursor
1862 if (! pDb
->CommitTrans())
1864 if (! CloseCursor(hstmt
))
1868 } // wxDbTable::DropIndex()
1871 /********** wxDbTable::SetOrderByColNums() **********/
1872 bool wxDbTable::SetOrderByColNums(UWORD first
, ... )
1874 int colNumber
= first
; // using 'int' to be able to look for wxDB_NO_MORE_COLUN_NUMBERS
1880 va_start(argptr
, first
); /* Initialize variable arguments. */
1881 while (!abort
&& (colNumber
!= wxDB_NO_MORE_COLUMN_NUMBERS
))
1883 // Make sure the passed in column number
1884 // is within the valid range of columns
1886 // Valid columns are 0 thru m_numCols-1
1887 if (colNumber
>= m_numCols
|| colNumber
< 0)
1893 if (colNumber
!= first
)
1894 tempStr
+= wxT(",");
1896 tempStr
+= colDefs
[colNumber
].ColName
;
1897 colNumber
= va_arg (argptr
, int);
1899 va_end (argptr
); /* Reset variable arguments. */
1901 SetOrderByClause(tempStr
);
1904 } // wxDbTable::SetOrderByColNums()
1907 /********** wxDbTable::Insert() **********/
1908 int wxDbTable::Insert(void)
1910 wxASSERT(!queryOnly
);
1911 if (queryOnly
|| !insertable
)
1916 // Insert the record by executing the already prepared insert statement
1918 retcode
= SQLExecute(hstmtInsert
);
1919 if (retcode
!= SQL_SUCCESS
&& retcode
!= SQL_SUCCESS_WITH_INFO
&&
1920 retcode
!= SQL_NEED_DATA
)
1922 // Check to see if integrity constraint was violated
1923 pDb
->GetNextError(henv
, hdbc
, hstmtInsert
);
1924 if (! wxStrcmp(pDb
->sqlState
, wxT("23000"))) // Integrity constraint violated
1925 return(DB_ERR_INTEGRITY_CONSTRAINT_VIOL
);
1928 pDb
->DispNextError();
1929 pDb
->DispAllErrors(henv
, hdbc
, hstmtInsert
);
1933 if (retcode
== SQL_NEED_DATA
)
1936 retcode
= SQLParamData(hstmtInsert
, &pParmID
);
1937 while (retcode
== SQL_NEED_DATA
)
1939 // Find the parameter
1941 for (i
=0; i
< m_numCols
; i
++)
1943 if (colDefs
[i
].PtrDataObj
== pParmID
)
1945 // We found it. Store the parameter.
1946 retcode
= SQLPutData(hstmtInsert
, pParmID
, colDefs
[i
].SzDataObj
);
1947 if (retcode
!= SQL_SUCCESS
)
1949 pDb
->DispNextError();
1950 pDb
->DispAllErrors(henv
, hdbc
, hstmtInsert
);
1956 retcode
= SQLParamData(hstmtInsert
, &pParmID
);
1957 if (retcode
!= SQL_SUCCESS
&&
1958 retcode
!= SQL_SUCCESS_WITH_INFO
)
1960 // record was not inserted
1961 pDb
->DispNextError();
1962 pDb
->DispAllErrors(henv
, hdbc
, hstmtInsert
);
1968 // Record inserted into the datasource successfully
1971 } // wxDbTable::Insert()
1974 /********** wxDbTable::Update() **********/
1975 bool wxDbTable::Update(void)
1977 wxASSERT(!queryOnly
);
1983 // Build the SQL UPDATE statement
1984 BuildUpdateStmt(sqlStmt
, DB_UPD_KEYFIELDS
);
1986 pDb
->WriteSqlLog(sqlStmt
);
1988 #ifdef DBDEBUG_CONSOLE
1989 cout
<< endl
<< sqlStmt
.c_str() << endl
<< endl
;
1992 // Execute the SQL UPDATE statement
1993 return(execUpdate(sqlStmt
));
1995 } // wxDbTable::Update()
1998 /********** wxDbTable::Update(pSqlStmt) **********/
1999 bool wxDbTable::Update(const wxString
&pSqlStmt
)
2001 wxASSERT(!queryOnly
);
2005 pDb
->WriteSqlLog(pSqlStmt
);
2007 return(execUpdate(pSqlStmt
));
2009 } // wxDbTable::Update(pSqlStmt)
2012 /********** wxDbTable::UpdateWhere() **********/
2013 bool wxDbTable::UpdateWhere(const wxString
&pWhereClause
)
2015 wxASSERT(!queryOnly
);
2021 // Build the SQL UPDATE statement
2022 BuildUpdateStmt(sqlStmt
, DB_UPD_WHERE
, pWhereClause
);
2024 pDb
->WriteSqlLog(sqlStmt
);
2026 #ifdef DBDEBUG_CONSOLE
2027 cout
<< endl
<< sqlStmt
.c_str() << endl
<< endl
;
2030 // Execute the SQL UPDATE statement
2031 return(execUpdate(sqlStmt
));
2033 } // wxDbTable::UpdateWhere()
2036 /********** wxDbTable::Delete() **********/
2037 bool wxDbTable::Delete(void)
2039 wxASSERT(!queryOnly
);
2046 // Build the SQL DELETE statement
2047 BuildDeleteStmt(sqlStmt
, DB_DEL_KEYFIELDS
);
2049 pDb
->WriteSqlLog(sqlStmt
);
2051 // Execute the SQL DELETE statement
2052 return(execDelete(sqlStmt
));
2054 } // wxDbTable::Delete()
2057 /********** wxDbTable::DeleteWhere() **********/
2058 bool wxDbTable::DeleteWhere(const wxString
&pWhereClause
)
2060 wxASSERT(!queryOnly
);
2067 // Build the SQL DELETE statement
2068 BuildDeleteStmt(sqlStmt
, DB_DEL_WHERE
, pWhereClause
);
2070 pDb
->WriteSqlLog(sqlStmt
);
2072 // Execute the SQL DELETE statement
2073 return(execDelete(sqlStmt
));
2075 } // wxDbTable::DeleteWhere()
2078 /********** wxDbTable::DeleteMatching() **********/
2079 bool wxDbTable::DeleteMatching(void)
2081 wxASSERT(!queryOnly
);
2088 // Build the SQL DELETE statement
2089 BuildDeleteStmt(sqlStmt
, DB_DEL_MATCHING
);
2091 pDb
->WriteSqlLog(sqlStmt
);
2093 // Execute the SQL DELETE statement
2094 return(execDelete(sqlStmt
));
2096 } // wxDbTable::DeleteMatching()
2099 /********** wxDbTable::IsColNull() **********/
2100 bool wxDbTable::IsColNull(UWORD colNumber
) const
2103 This logic is just not right. It would indicate true
2104 if a numeric field were set to a value of 0.
2106 switch(colDefs[colNumber].SqlCtype)
2110 //case SQL_C_WXCHAR: SQL_C_WXCHAR is covered by either SQL_C_CHAR or SQL_C_WCHAR
2111 return(((UCHAR FAR *) colDefs[colNumber].PtrDataObj)[0] == 0);
2113 return(( *((SWORD *) colDefs[colNumber].PtrDataObj)) == 0);
2115 return(( *((UWORD*) colDefs[colNumber].PtrDataObj)) == 0);
2117 return(( *((SDWORD *) colDefs[colNumber].PtrDataObj)) == 0);
2119 return(( *((UDWORD *) colDefs[colNumber].PtrDataObj)) == 0);
2121 return(( *((SFLOAT *) colDefs[colNumber].PtrDataObj)) == 0);
2123 return((*((SDOUBLE *) colDefs[colNumber].PtrDataObj)) == 0);
2124 case SQL_C_TIMESTAMP:
2125 TIMESTAMP_STRUCT *pDt;
2126 pDt = (TIMESTAMP_STRUCT *) colDefs[colNumber].PtrDataObj;
2127 if (pDt->year == 0 && pDt->month == 0 && pDt->day == 0)
2135 return (colDefs
[colNumber
].Null
);
2136 } // wxDbTable::IsColNull()
2139 /********** wxDbTable::CanSelectForUpdate() **********/
2140 bool wxDbTable::CanSelectForUpdate(void)
2145 if (pDb
->Dbms() == dbmsMY_SQL
)
2148 if ((pDb
->Dbms() == dbmsORACLE
) ||
2149 (pDb
->dbInf
.posStmts
& SQL_PS_SELECT_FOR_UPDATE
))
2154 } // wxDbTable::CanSelectForUpdate()
2157 /********** wxDbTable::CanUpdateByROWID() **********/
2158 bool wxDbTable::CanUpdateByROWID(void)
2161 * NOTE: Returning false for now until this can be debugged,
2162 * as the ROWID is not getting updated correctly
2166 if (pDb->Dbms() == dbmsORACLE)
2171 } // wxDbTable::CanUpdateByROWID()
2174 /********** wxDbTable::IsCursorClosedOnCommit() **********/
2175 bool wxDbTable::IsCursorClosedOnCommit(void)
2177 if (pDb
->dbInf
.cursorCommitBehavior
== SQL_CB_PRESERVE
)
2182 } // wxDbTable::IsCursorClosedOnCommit()
2186 /********** wxDbTable::ClearMemberVar() **********/
2187 void wxDbTable::ClearMemberVar(UWORD colNumber
, bool setToNull
)
2189 wxASSERT(colNumber
< m_numCols
);
2191 switch(colDefs
[colNumber
].SqlCtype
)
2197 //case SQL_C_WXCHAR: SQL_C_WXCHAR is covered by either SQL_C_CHAR or SQL_C_WCHAR
2198 ((UCHAR FAR
*) colDefs
[colNumber
].PtrDataObj
)[0] = 0;
2201 *((SWORD
*) colDefs
[colNumber
].PtrDataObj
) = 0;
2204 *((UWORD
*) colDefs
[colNumber
].PtrDataObj
) = 0;
2208 *((SDWORD
*) colDefs
[colNumber
].PtrDataObj
) = 0;
2211 *((UDWORD
*) colDefs
[colNumber
].PtrDataObj
) = 0;
2214 *((SFLOAT
*) colDefs
[colNumber
].PtrDataObj
) = 0.0f
;
2217 *((SDOUBLE
*) colDefs
[colNumber
].PtrDataObj
) = 0.0f
;
2219 case SQL_C_TIMESTAMP
:
2220 TIMESTAMP_STRUCT
*pDt
;
2221 pDt
= (TIMESTAMP_STRUCT
*) colDefs
[colNumber
].PtrDataObj
;
2232 pDtd
= (DATE_STRUCT
*) colDefs
[colNumber
].PtrDataObj
;
2239 pDtt
= (TIME_STRUCT
*) colDefs
[colNumber
].PtrDataObj
;
2247 SetColNull(colNumber
);
2248 } // wxDbTable::ClearMemberVar()
2251 /********** wxDbTable::ClearMemberVars() **********/
2252 void wxDbTable::ClearMemberVars(bool setToNull
)
2256 // Loop through the columns setting each member variable to zero
2257 for (i
=0; i
< m_numCols
; i
++)
2258 ClearMemberVar((UWORD
)i
,setToNull
);
2260 } // wxDbTable::ClearMemberVars()
2263 /********** wxDbTable::SetQueryTimeout() **********/
2264 bool wxDbTable::SetQueryTimeout(UDWORD nSeconds
)
2266 if (SQLSetStmtOption(hstmtInsert
, SQL_QUERY_TIMEOUT
, nSeconds
) != SQL_SUCCESS
)
2267 return(pDb
->DispAllErrors(henv
, hdbc
, hstmtInsert
));
2268 if (SQLSetStmtOption(hstmtUpdate
, SQL_QUERY_TIMEOUT
, nSeconds
) != SQL_SUCCESS
)
2269 return(pDb
->DispAllErrors(henv
, hdbc
, hstmtUpdate
));
2270 if (SQLSetStmtOption(hstmtDelete
, SQL_QUERY_TIMEOUT
, nSeconds
) != SQL_SUCCESS
)
2271 return(pDb
->DispAllErrors(henv
, hdbc
, hstmtDelete
));
2272 if (SQLSetStmtOption(hstmtInternal
, SQL_QUERY_TIMEOUT
, nSeconds
) != SQL_SUCCESS
)
2273 return(pDb
->DispAllErrors(henv
, hdbc
, hstmtInternal
));
2275 // Completed Successfully
2278 } // wxDbTable::SetQueryTimeout()
2281 /********** wxDbTable::SetColDefs() **********/
2282 bool wxDbTable::SetColDefs(UWORD index
, const wxString
&fieldName
, int dataType
, void *pData
,
2283 SWORD cType
, int size
, bool keyField
, bool updateable
,
2284 bool insertAllowed
, bool derivedColumn
)
2288 if (index
>= m_numCols
) // Columns numbers are zero based....
2290 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
);
2296 if (!colDefs
) // May happen if the database connection fails
2299 if (fieldName
.length() > (unsigned int) DB_MAX_COLUMN_NAME_LEN
)
2301 wxStrncpy(colDefs
[index
].ColName
, fieldName
, DB_MAX_COLUMN_NAME_LEN
);
2302 colDefs
[index
].ColName
[DB_MAX_COLUMN_NAME_LEN
] = 0; // Prevent buffer overrun
2304 tmpStr
.Printf(wxT("Column name '%s' is too long. Truncated to '%s'."),
2305 fieldName
.c_str(),colDefs
[index
].ColName
);
2310 wxStrcpy(colDefs
[index
].ColName
, fieldName
);
2312 colDefs
[index
].DbDataType
= dataType
;
2313 colDefs
[index
].PtrDataObj
= pData
;
2314 colDefs
[index
].SqlCtype
= cType
;
2315 colDefs
[index
].SzDataObj
= size
; //TODO: glt ??? * sizeof(wxChar) ???
2316 colDefs
[index
].KeyField
= keyField
;
2317 colDefs
[index
].DerivedCol
= derivedColumn
;
2318 // Derived columns by definition would NOT be "Insertable" or "Updateable"
2321 colDefs
[index
].Updateable
= false;
2322 colDefs
[index
].InsertAllowed
= false;
2326 colDefs
[index
].Updateable
= updateable
;
2327 colDefs
[index
].InsertAllowed
= insertAllowed
;
2330 colDefs
[index
].Null
= false;
2334 } // wxDbTable::SetColDefs()
2337 /********** wxDbTable::SetColDefs() **********/
2338 wxDbColDataPtr
* wxDbTable::SetColDefs(wxDbColInf
*pColInfs
, UWORD numCols
)
2341 wxDbColDataPtr
*pColDataPtrs
= NULL
;
2347 pColDataPtrs
= new wxDbColDataPtr
[numCols
+1];
2349 for (index
= 0; index
< numCols
; index
++)
2351 // Process the fields
2352 switch (pColInfs
[index
].dbDataType
)
2354 case DB_DATA_TYPE_VARCHAR
:
2355 pColDataPtrs
[index
].PtrDataObj
= new wxChar
[pColInfs
[index
].bufferSize
+(1*sizeof(wxChar
))];
2356 pColDataPtrs
[index
].SzDataObj
= pColInfs
[index
].bufferSize
+(1*sizeof(wxChar
));
2357 pColDataPtrs
[index
].SqlCtype
= SQL_C_WXCHAR
;
2359 case DB_DATA_TYPE_MEMO
:
2360 pColDataPtrs
[index
].PtrDataObj
= new wxChar
[pColInfs
[index
].bufferSize
+(1*sizeof(wxChar
))];
2361 pColDataPtrs
[index
].SzDataObj
= pColInfs
[index
].bufferSize
+(1*sizeof(wxChar
));
2362 pColDataPtrs
[index
].SqlCtype
= SQL_C_WXCHAR
;
2364 case DB_DATA_TYPE_INTEGER
:
2365 // Can be long or short
2366 if (pColInfs
[index
].bufferSize
== sizeof(long))
2368 pColDataPtrs
[index
].PtrDataObj
= new long;
2369 pColDataPtrs
[index
].SzDataObj
= sizeof(long);
2370 pColDataPtrs
[index
].SqlCtype
= SQL_C_SLONG
;
2374 pColDataPtrs
[index
].PtrDataObj
= new short;
2375 pColDataPtrs
[index
].SzDataObj
= sizeof(short);
2376 pColDataPtrs
[index
].SqlCtype
= SQL_C_SSHORT
;
2379 case DB_DATA_TYPE_FLOAT
:
2380 // Can be float or double
2381 if (pColInfs
[index
].bufferSize
== sizeof(float))
2383 pColDataPtrs
[index
].PtrDataObj
= new float;
2384 pColDataPtrs
[index
].SzDataObj
= sizeof(float);
2385 pColDataPtrs
[index
].SqlCtype
= SQL_C_FLOAT
;
2389 pColDataPtrs
[index
].PtrDataObj
= new double;
2390 pColDataPtrs
[index
].SzDataObj
= sizeof(double);
2391 pColDataPtrs
[index
].SqlCtype
= SQL_C_DOUBLE
;
2394 case DB_DATA_TYPE_DATE
:
2395 pColDataPtrs
[index
].PtrDataObj
= new TIMESTAMP_STRUCT
;
2396 pColDataPtrs
[index
].SzDataObj
= sizeof(TIMESTAMP_STRUCT
);
2397 pColDataPtrs
[index
].SqlCtype
= SQL_C_TIMESTAMP
;
2399 case DB_DATA_TYPE_BLOB
:
2400 wxFAIL_MSG(wxT("This form of ::SetColDefs() cannot be used with BLOB columns"));
2401 pColDataPtrs
[index
].PtrDataObj
= /*BLOB ADDITION NEEDED*/NULL
;
2402 pColDataPtrs
[index
].SzDataObj
= /*BLOB ADDITION NEEDED*/sizeof(void *);
2403 pColDataPtrs
[index
].SqlCtype
= SQL_VARBINARY
;
2406 if (pColDataPtrs
[index
].PtrDataObj
!= NULL
)
2407 SetColDefs (index
,pColInfs
[index
].colName
,pColInfs
[index
].dbDataType
, pColDataPtrs
[index
].PtrDataObj
, pColDataPtrs
[index
].SqlCtype
, pColDataPtrs
[index
].SzDataObj
);
2410 // Unable to build all the column definitions, as either one of
2411 // the calls to "new" failed above, or there was a BLOB field
2412 // to have a column definition for. If BLOBs are to be used,
2413 // the other form of ::SetColDefs() must be used, as it is impossible
2414 // to know the maximum size to create the PtrDataObj to be.
2415 delete [] pColDataPtrs
;
2421 return (pColDataPtrs
);
2423 } // wxDbTable::SetColDefs()
2426 /********** wxDbTable::SetCursor() **********/
2427 void wxDbTable::SetCursor(HSTMT
*hstmtActivate
)
2429 if (hstmtActivate
== wxDB_DEFAULT_CURSOR
)
2430 hstmt
= *hstmtDefault
;
2432 hstmt
= *hstmtActivate
;
2434 } // wxDbTable::SetCursor()
2437 /********** wxDbTable::Count(const wxString &) **********/
2438 ULONG
wxDbTable::Count(const wxString
&args
)
2444 // Build a "SELECT COUNT(*) FROM queryTableName [WHERE whereClause]" SQL Statement
2445 sqlStmt
= wxT("SELECT COUNT(");
2447 sqlStmt
+= wxT(") FROM ");
2448 sqlStmt
+= pDb
->SQLTableName(queryTableName
);
2449 // sqlStmt += queryTableName;
2450 #if wxODBC_BACKWARD_COMPATABILITY
2451 if (from
&& wxStrlen(from
))
2457 // Add the where clause if one is provided
2458 #if wxODBC_BACKWARD_COMPATABILITY
2459 if (where
&& wxStrlen(where
))
2464 sqlStmt
+= wxT(" WHERE ");
2468 pDb
->WriteSqlLog(sqlStmt
);
2470 // Initialize the Count cursor if it's not already initialized
2473 hstmtCount
= GetNewCursor(false,false);
2474 wxASSERT(hstmtCount
);
2479 // Execute the SQL statement
2480 if (SQLExecDirect(*hstmtCount
, (SQLTCHAR FAR
*) sqlStmt
.c_str(), SQL_NTS
) != SQL_SUCCESS
)
2482 pDb
->DispAllErrors(henv
, hdbc
, *hstmtCount
);
2487 if (SQLFetch(*hstmtCount
) != SQL_SUCCESS
)
2489 pDb
->DispAllErrors(henv
, hdbc
, *hstmtCount
);
2493 // Obtain the result
2494 if (SQLGetData(*hstmtCount
, (UWORD
)1, SQL_C_ULONG
, &count
, sizeof(count
), &cb
) != SQL_SUCCESS
)
2496 pDb
->DispAllErrors(henv
, hdbc
, *hstmtCount
);
2501 if (SQLFreeStmt(*hstmtCount
, SQL_CLOSE
) != SQL_SUCCESS
)
2502 pDb
->DispAllErrors(henv
, hdbc
, *hstmtCount
);
2504 // Return the record count
2507 } // wxDbTable::Count()
2510 /********** wxDbTable::Refresh() **********/
2511 bool wxDbTable::Refresh(void)
2515 // Switch to the internal cursor so any active cursors are not corrupted
2516 HSTMT currCursor
= GetCursor();
2517 hstmt
= hstmtInternal
;
2518 #if wxODBC_BACKWARD_COMPATABILITY
2519 // Save the where and order by clauses
2520 wxChar
*saveWhere
= where
;
2521 wxChar
*saveOrderBy
= orderBy
;
2523 wxString saveWhere
= where
;
2524 wxString saveOrderBy
= orderBy
;
2526 // Build a where clause to refetch the record with. Try and use the
2527 // ROWID if it's available, ow use the key fields.
2528 wxString whereClause
;
2529 whereClause
.Empty();
2531 if (CanUpdateByROWID())
2534 wxChar rowid
[wxDB_ROWID_LEN
+1];
2536 // Get the ROWID value. If not successful retreiving the ROWID,
2537 // simply fall down through the code and build the WHERE clause
2538 // based on the key fields.
2539 if (SQLGetData(hstmt
, (UWORD
)(m_numCols
+1), SQL_C_WXCHAR
, (UCHAR
*) rowid
, sizeof(rowid
), &cb
) == SQL_SUCCESS
)
2541 whereClause
+= pDb
->SQLTableName(queryTableName
);
2542 // whereClause += queryTableName;
2543 whereClause
+= wxT(".ROWID = '");
2544 whereClause
+= rowid
;
2545 whereClause
+= wxT("'");
2549 // If unable to use the ROWID, build a where clause from the keyfields
2550 if (wxStrlen(whereClause
) == 0)
2551 BuildWhereClause(whereClause
, DB_WHERE_KEYFIELDS
, queryTableName
);
2553 // Requery the record
2554 where
= whereClause
;
2559 if (result
&& !GetNext())
2562 // Switch back to original cursor
2563 SetCursor(&currCursor
);
2565 // Free the internal cursor
2566 if (SQLFreeStmt(hstmtInternal
, SQL_CLOSE
) != SQL_SUCCESS
)
2567 pDb
->DispAllErrors(henv
, hdbc
, hstmtInternal
);
2569 // Restore the original where and order by clauses
2571 orderBy
= saveOrderBy
;
2575 } // wxDbTable::Refresh()
2578 /********** wxDbTable::SetColNull() **********/
2579 bool wxDbTable::SetColNull(UWORD colNumber
, bool set
)
2581 if (colNumber
< m_numCols
)
2583 colDefs
[colNumber
].Null
= set
;
2584 if (set
) // Blank out the values in the member variable
2585 ClearMemberVar(colNumber
, false); // Must call with false here, or infinite recursion will happen
2587 setCbValueForColumn(colNumber
);
2594 } // wxDbTable::SetColNull()
2597 /********** wxDbTable::SetColNull() **********/
2598 bool wxDbTable::SetColNull(const wxString
&colName
, bool set
)
2601 for (colNumber
= 0; colNumber
< m_numCols
; colNumber
++)
2603 if (!wxStricmp(colName
, colDefs
[colNumber
].ColName
))
2607 if (colNumber
< m_numCols
)
2609 colDefs
[colNumber
].Null
= set
;
2610 if (set
) // Blank out the values in the member variable
2611 ClearMemberVar((UWORD
)colNumber
,false); // Must call with false here, or infinite recursion will happen
2613 setCbValueForColumn(colNumber
);
2620 } // wxDbTable::SetColNull()
2623 /********** wxDbTable::GetNewCursor() **********/
2624 HSTMT
*wxDbTable::GetNewCursor(bool setCursor
, bool bindColumns
)
2626 HSTMT
*newHSTMT
= new HSTMT
;
2631 if (SQLAllocStmt(hdbc
, newHSTMT
) != SQL_SUCCESS
)
2633 pDb
->DispAllErrors(henv
, hdbc
);
2638 if (SQLSetStmtOption(*newHSTMT
, SQL_CURSOR_TYPE
, cursorType
) != SQL_SUCCESS
)
2640 pDb
->DispAllErrors(henv
, hdbc
, *newHSTMT
);
2647 if (!bindCols(*newHSTMT
))
2655 SetCursor(newHSTMT
);
2659 } // wxDbTable::GetNewCursor()
2662 /********** wxDbTable::DeleteCursor() **********/
2663 bool wxDbTable::DeleteCursor(HSTMT
*hstmtDel
)
2667 if (!hstmtDel
) // Cursor already deleted
2671 ODBC 3.0 says to use this form
2672 if (SQLFreeHandle(*hstmtDel, SQL_DROP) != SQL_SUCCESS)
2675 if (SQLFreeStmt(*hstmtDel
, SQL_DROP
) != SQL_SUCCESS
)
2677 pDb
->DispAllErrors(henv
, hdbc
);
2685 } // wxDbTable::DeleteCursor()
2687 //////////////////////////////////////////////////////////////
2688 // wxDbGrid support functions
2689 //////////////////////////////////////////////////////////////
2691 void wxDbTable::SetRowMode(const rowmode_t rowmode
)
2693 if (!m_hstmtGridQuery
)
2695 m_hstmtGridQuery
= GetNewCursor(false,false);
2696 if (!bindCols(*m_hstmtGridQuery
))
2700 m_rowmode
= rowmode
;
2703 case WX_ROW_MODE_QUERY
:
2704 SetCursor(m_hstmtGridQuery
);
2706 case WX_ROW_MODE_INDIVIDUAL
:
2707 SetCursor(hstmtDefault
);
2712 } // wxDbTable::SetRowMode()
2715 wxVariant
wxDbTable::GetColumn(const int colNumber
) const
2718 if ((colNumber
< m_numCols
) && (!IsColNull((UWORD
)colNumber
)))
2720 switch (colDefs
[colNumber
].SqlCtype
)
2723 #if defined(SQL_WCHAR)
2726 #if defined(SQL_WVARCHAR)
2732 val
= (wxChar
*)(colDefs
[colNumber
].PtrDataObj
);
2736 val
= *(long *)(colDefs
[colNumber
].PtrDataObj
);
2740 val
= (long int )(*(short *)(colDefs
[colNumber
].PtrDataObj
));
2743 val
= (long)(*(unsigned long *)(colDefs
[colNumber
].PtrDataObj
));
2746 val
= (long)(*(wxChar
*)(colDefs
[colNumber
].PtrDataObj
));
2748 case SQL_C_UTINYINT
:
2749 val
= (long)(*(wxChar
*)(colDefs
[colNumber
].PtrDataObj
));
2752 val
= (long)(*(UWORD
*)(colDefs
[colNumber
].PtrDataObj
));
2755 val
= (DATE_STRUCT
*)(colDefs
[colNumber
].PtrDataObj
);
2758 val
= (TIME_STRUCT
*)(colDefs
[colNumber
].PtrDataObj
);
2760 case SQL_C_TIMESTAMP
:
2761 val
= (TIMESTAMP_STRUCT
*)(colDefs
[colNumber
].PtrDataObj
);
2764 val
= *(double *)(colDefs
[colNumber
].PtrDataObj
);
2771 } // wxDbTable::GetCol()
2774 void wxDbTable::SetColumn(const int colNumber
, const wxVariant val
)
2776 //FIXME: Add proper wxDateTime support to wxVariant..
2779 SetColNull((UWORD
)colNumber
, val
.IsNull());
2783 if ((colDefs
[colNumber
].SqlCtype
== SQL_C_DATE
)
2784 || (colDefs
[colNumber
].SqlCtype
== SQL_C_TIME
)
2785 || (colDefs
[colNumber
].SqlCtype
== SQL_C_TIMESTAMP
))
2787 //Returns null if invalid!
2788 if (!dateval
.ParseDate(val
.GetString()))
2789 SetColNull((UWORD
)colNumber
, true);
2792 switch (colDefs
[colNumber
].SqlCtype
)
2795 #if defined(SQL_WCHAR)
2798 #if defined(SQL_WVARCHAR)
2804 csstrncpyt((wxChar
*)(colDefs
[colNumber
].PtrDataObj
),
2805 val
.GetString().c_str(),
2806 colDefs
[colNumber
].SzDataObj
-1); //TODO: glt ??? * sizeof(wxChar) ???
2810 *(long *)(colDefs
[colNumber
].PtrDataObj
) = val
;
2814 *(short *)(colDefs
[colNumber
].PtrDataObj
) = (short)val
.GetLong();
2817 *(unsigned long *)(colDefs
[colNumber
].PtrDataObj
) = val
.GetLong();
2820 *(wxChar
*)(colDefs
[colNumber
].PtrDataObj
) = val
.GetChar();
2822 case SQL_C_UTINYINT
:
2823 *(wxChar
*)(colDefs
[colNumber
].PtrDataObj
) = val
.GetChar();
2826 *(unsigned short *)(colDefs
[colNumber
].PtrDataObj
) = (unsigned short)val
.GetLong();
2828 //FIXME: Add proper wxDateTime support to wxVariant..
2831 DATE_STRUCT
*dataptr
=
2832 (DATE_STRUCT
*)colDefs
[colNumber
].PtrDataObj
;
2834 dataptr
->year
= (SWORD
)dateval
.GetYear();
2835 dataptr
->month
= (UWORD
)(dateval
.GetMonth()+1);
2836 dataptr
->day
= (UWORD
)dateval
.GetDay();
2841 TIME_STRUCT
*dataptr
=
2842 (TIME_STRUCT
*)colDefs
[colNumber
].PtrDataObj
;
2844 dataptr
->hour
= dateval
.GetHour();
2845 dataptr
->minute
= dateval
.GetMinute();
2846 dataptr
->second
= dateval
.GetSecond();
2849 case SQL_C_TIMESTAMP
:
2851 TIMESTAMP_STRUCT
*dataptr
=
2852 (TIMESTAMP_STRUCT
*)colDefs
[colNumber
].PtrDataObj
;
2853 dataptr
->year
= (SWORD
)dateval
.GetYear();
2854 dataptr
->month
= (UWORD
)(dateval
.GetMonth()+1);
2855 dataptr
->day
= (UWORD
)dateval
.GetDay();
2857 dataptr
->hour
= dateval
.GetHour();
2858 dataptr
->minute
= dateval
.GetMinute();
2859 dataptr
->second
= dateval
.GetSecond();
2863 *(double *)(colDefs
[colNumber
].PtrDataObj
) = val
;
2868 } // if (!val.IsNull())
2869 } // wxDbTable::SetCol()
2872 GenericKey
wxDbTable::GetKey()
2877 blk
= malloc(m_keysize
);
2878 blkptr
= (wxChar
*) blk
;
2881 for (i
=0; i
< m_numCols
; i
++)
2883 if (colDefs
[i
].KeyField
)
2885 memcpy(blkptr
,colDefs
[i
].PtrDataObj
, colDefs
[i
].SzDataObj
);
2886 blkptr
+= colDefs
[i
].SzDataObj
;
2890 GenericKey k
= GenericKey(blk
, m_keysize
);
2894 } // wxDbTable::GetKey()
2897 void wxDbTable::SetKey(const GenericKey
& k
)
2903 blkptr
= (wxChar
*)blk
;
2906 for (i
=0; i
< m_numCols
; i
++)
2908 if (colDefs
[i
].KeyField
)
2910 SetColNull((UWORD
)i
, false);
2911 memcpy(colDefs
[i
].PtrDataObj
, blkptr
, colDefs
[i
].SzDataObj
);
2912 blkptr
+= colDefs
[i
].SzDataObj
;
2915 } // wxDbTable::SetKey()
2918 #endif // wxUSE_ODBC