1 ///////////////////////////////////////////////////////////////////////////////
3 // Purpose: Declaration of the wxDbTable class.
5 // Modified by: George Tasker
10 // Copyright: (c) 1996 Remstar International, Inc.
11 // Licence: wxWindows licence, plus:
12 // Notice: This class library and its intellectual design are free of charge for use,
13 // modification, enhancement, debugging under the following conditions:
14 // 1) These classes may only be used as part of the implementation of a
15 // wxWindows-based application
16 // 2) All enhancements and bug fixes are to be submitted back to the wxWindows
17 // user groups free of all charges for use with the wxWindows library.
18 // 3) These classes may not be distributed as part of any other class library,
19 // DLL, text (written or electronic), other than a complete distribution of
20 // the wxWindows GUI development toolkit.
21 ///////////////////////////////////////////////////////////////////////////////
31 #include "wx/version.h"
34 #pragma interface "dbtable.h"
39 #include "wx/variant.h"
40 #include "wx/dbkeyg.h"
42 const int wxDB_ROWID_LEN
= 24; // 18 is the max, 24 is in case it gets larger
43 const int wxDB_DEFAULT_CURSOR
= 0;
44 const bool wxDB_QUERY_ONLY
= TRUE
;
45 const bool wxDB_DISABLE_VIEW
= TRUE
;
47 // Used to indicate end of a variable length list of
48 // column numbers passed to member functions
49 const int wxDB_NO_MORE_COLUMN_NUMBERS
= -1;
51 // The following class is used to define a column of a table.
52 // The wxDbTable constructor will dynamically allocate as many of
53 // these as there are columns in the table. The class derived
54 // from wxDbTable must initialize these column definitions in it's
55 // constructor. These column definitions provide inf. to the
56 // wxDbTable class which allows it to create a table in the data
57 // source, exchange data between the data source and the C++
59 class WXDLLEXPORT wxDbColDef
62 wxChar ColName
[DB_MAX_COLUMN_NAME_LEN
+1]; // Column Name
63 int DbDataType
; // Logical Data Type; e.g. DB_DATA_TYPE_INTEGER
64 SWORD SqlCtype
; // C data type; e.g. SQL_C_LONG
65 void *PtrDataObj
; // Address of the data object
66 int SzDataObj
; // Size, in bytes, of the data object
67 bool KeyField
; // TRUE if this column is part of the PRIMARY KEY to the table; Date fields should NOT be KeyFields.
68 bool Updateable
; // Specifies whether this column is updateable
69 bool InsertAllowed
; // Specifies whether this column should be included in an INSERT statement
70 bool DerivedCol
; // Specifies whether this column is a derived value
71 SDWORD CbValue
; // Internal use only!!!
72 bool Null
; // NOT FULLY IMPLEMENTED - Allows NULL values in Inserts and Updates
80 class WXDLLEXPORT wxDbColDataPtr
89 // This structure is used when creating secondary indexes.
90 class WXDLLEXPORT wxDbIdxDef
93 wxChar ColName
[DB_MAX_COLUMN_NAME_LEN
+1];
98 class WXDLLEXPORT wxDbTable
101 ULONG tableID
; // Used for debugging. This can help to match up mismatched constructors/destructors
103 // Private member variables
107 // Private member functions
108 bool initialize(wxDb
*pwxDb
, const wxString
&tblName
, const UWORD numColumns
,
109 const wxString
&qryTblName
, bool qryOnly
, const wxString
&tblPath
);
112 bool bindParams(bool forUpdate
); // called by the other 'bind' functions
113 bool bindInsertParams(void);
114 bool bindUpdateParams(void);
116 bool bindCols(HSTMT cursor
);
117 bool getRec(UWORD fetchType
);
118 bool execDelete(const wxString
&pSqlStmt
);
119 bool execUpdate(const wxString
&pSqlStmt
);
120 bool query(int queryType
, bool forUpdate
, bool distinct
, const wxString
&pSqlStmt
=wxEmptyString
);
122 #if !wxODBC_BACKWARD_COMPATABILITY
124 // Where, Order By and From clauses
125 wxString where
; // Standard SQL where clause, minus the word WHERE
126 wxString orderBy
; // Standard SQL order by clause, minus the ORDER BY
127 wxString from
; // Allows for joins in a wxDbTable::Query(). Format: ",tbl,tbl..."
130 HENV henv
; // ODBC Environment handle
131 HDBC hdbc
; // ODBC DB Connection handle
132 HSTMT hstmt
; // ODBC Statement handle
133 HSTMT
*hstmtDefault
; // Default cursor
134 HSTMT hstmtInsert
; // ODBC Statement handle used specifically for inserts
135 HSTMT hstmtDelete
; // ODBC Statement handle used specifically for deletes
136 HSTMT hstmtUpdate
; // ODBC Statement handle used specifically for updates
137 HSTMT hstmtInternal
; // ODBC Statement handle used internally only
138 HSTMT
*hstmtCount
; // ODBC Statement handle used by Count() function (No binding of columns)
141 bool selectForUpdate
;
143 // Pointer to the database object this table belongs to
147 wxString tablePath
; // needed for dBase tables
148 wxString tableName
; // Table name
149 wxString queryTableName
; // Query Table Name
150 UWORD noCols
; // # of columns in the table
151 bool queryOnly
; // Query Only, no inserts, updates or deletes
153 // Column Definitions
154 wxDbColDef
*colDefs
; // Array of wxDbColDef structures
157 #if wxODBC_BACKWARD_COMPATABILITY
158 // Where, Order By and From clauses
159 char *where
; // Standard SQL where clause, minus the word WHERE
160 char *orderBy
; // Standard SQL order by clause, minus the ORDER BY
161 char *from
; // Allows for joins in a wxDbTable::Query(). Format: ",tbl,tbl..."
164 HENV henv
; // ODBC Environment handle
165 HDBC hdbc
; // ODBC DB Connection handle
166 HSTMT hstmt
; // ODBC Statement handle
167 HSTMT
*hstmtDefault
; // Default cursor
168 HSTMT hstmtInsert
; // ODBC Statement handle used specifically for inserts
169 HSTMT hstmtDelete
; // ODBC Statement handle used specifically for deletes
170 HSTMT hstmtUpdate
; // ODBC Statement handle used specifically for updates
171 HSTMT hstmtInternal
; // ODBC Statement handle used internally only
172 HSTMT
*hstmtCount
; // ODBC Statement handle used by Count() function (No binding of columns)
175 bool selectForUpdate
;
177 // Pointer to the database object this table belongs to
181 char tablePath
[wxDB_PATH_MAX
]; // needed for dBase tables
182 char tableName
[DB_MAX_TABLE_NAME_LEN
+1]; // Table name
183 char queryTableName
[DB_MAX_TABLE_NAME_LEN
+1]; // Query Table Name
184 UWORD noCols
; // # of columns in the table
185 bool queryOnly
; // Query Only, no inserts, updates or deletes
187 // Column Definitions
188 wxDbColDef
*colDefs
; // Array of wxDbColDef structures
190 // Public member functions
191 wxDbTable(wxDb
*pwxDb
, const wxString
&tblName
, const UWORD numColumns
,
192 const wxString
&qryTblName
="", bool qryOnly
= !wxDB_QUERY_ONLY
,
193 const wxString
&tblPath
="");
196 wxDbTable(wxDb
*pwxDb
, const wxString
&tblName
, const UWORD numColumns
,
197 const wxChar
*qryTblName
="", bool qryOnly
= !wxDB_QUERY_ONLY
,
198 const wxString
&tblPath
="");
200 virtual ~wxDbTable();
202 bool Open(bool checkPrivileges
=FALSE
, bool checkTableExists
=TRUE
);
203 bool CreateTable(bool attemptDrop
=TRUE
);
204 bool DropTable(void);
205 bool CreateIndex(const wxString
&idxName
, bool unique
, UWORD noIdxCols
,
206 wxDbIdxDef
*pIdxDefs
, bool attemptDrop
=TRUE
);
207 bool DropIndex(const wxString
&idxName
);
211 // The member variables returned by these accessors are all
212 // set when the wxDbTable instance is created and cannot be
213 // changed, hence there is no corresponding SetXxxx function
214 wxDb
*GetDb() { return pDb
; }
215 const wxString
&GetTableName() { return tableName
; }
216 const wxString
&GetQueryTableName() { return queryTableName
; }
217 const wxString
&GetTablePath() { return tablePath
; }
219 UWORD
GetNumberOfColumns() { return noCols
; } // number of "defined" columns for this wxDbTable instance
221 const wxString
&GetFromClause() { return from
; }
222 const wxString
&GetOrderByClause() { return orderBy
; }
223 const wxString
&GetWhereClause() { return where
; }
225 bool IsQueryOnly() { return queryOnly
; }
226 #if wxODBC_BACKWARD_COMPATABILITY
227 void SetFromClause(const char *From
) { from
= (char *)From
; }
228 void SetOrderByClause(const char *OrderBy
) { orderBy
= (char *)OrderBy
; }
229 void SetWhereClause(const char *Where
) { where
= (char *)Where
; }
231 void SetFromClause(const wxString
&From
) { from
= From
; }
232 void SetOrderByClause(const wxString
&OrderBy
) { orderBy
= OrderBy
; }
233 bool SetOrderByColNums(UWORD first
, ...);
234 void SetWhereClause(const wxString
&Where
) { where
= Where
; }
235 void From(const wxString
&From
) { from
= From
; }
236 void OrderBy(const wxString
&OrderBy
) { orderBy
= OrderBy
; }
237 void Where(const wxString
&Where
) { where
= Where
; }
238 const wxString
&Where() { return where
; }
239 const wxString
&OrderBy() { return orderBy
; }
240 const wxString
&From() { return from
; }
244 bool Update(const wxString
&pSqlStmt
);
245 bool UpdateWhere(const wxString
&pWhereClause
);
247 bool DeleteWhere(const wxString
&pWhereClause
);
248 bool DeleteMatching(void);
249 virtual bool Query(bool forUpdate
= FALSE
, bool distinct
= FALSE
);
250 bool QueryBySqlStmt(const wxString
&pSqlStmt
);
251 bool QueryMatching(bool forUpdate
= FALSE
, bool distinct
= FALSE
);
252 bool QueryOnKeyFields(bool forUpdate
= FALSE
, bool distinct
= FALSE
);
254 bool GetNext(void) { return(getRec(SQL_FETCH_NEXT
)); }
255 bool operator++(int) { return(getRec(SQL_FETCH_NEXT
)); }
257 /***** These four functions only work with wxDb instances that are defined *****
258 ***** as not being FwdOnlyCursors *****/
260 bool operator--(int);
264 bool IsCursorClosedOnCommit(void);
265 UWORD
GetRowNum(void);
267 void BuildSelectStmt(wxString
&pSqlStmt
, int typeOfSelect
, bool distinct
);
268 void BuildSelectStmt(wxChar
*pSqlStmt
, int typeOfSelect
, bool distinct
);
270 void BuildDeleteStmt(wxString
&pSqlStmt
, int typeOfDel
, const wxString
&pWhereClause
="");
271 void BuildDeleteStmt(wxChar
*pSqlStmt
, int typeOfDel
, const wxString
&pWhereClause
="");
273 void BuildUpdateStmt(wxString
&pSqlStmt
, int typeOfUpd
, const wxString
&pWhereClause
="");
274 void BuildUpdateStmt(wxChar
*pSqlStmt
, int typeOfUpd
, const wxString
&pWhereClause
="");
276 void BuildWhereClause(wxString
&pWhereClause
, int typeOfWhere
, const wxString
&qualTableName
="", bool useLikeComparison
=FALSE
);
277 void BuildWhereClause(wxChar
*pWhereClause
, int typeOfWhere
, const wxString
&qualTableName
="", bool useLikeComparison
=FALSE
);
279 #if wxODBC_BACKWARD_COMPATABILITY
280 // The following member functions are deprecated. You should use the BuildXxxxxStmt functions (above)
281 void GetSelectStmt(char *pSqlStmt
, int typeOfSelect
, bool distinct
)
282 { BuildSelectStmt(pSqlStmt
,typeOfSelect
,distinct
); }
283 void GetDeleteStmt(char *pSqlStmt
, int typeOfDel
, const char *pWhereClause
= NULL
)
284 { BuildDeleteStmt(pSqlStmt
,typeOfDel
,pWhereClause
); }
285 void GetUpdateStmt(char *pSqlStmt
, int typeOfUpd
, const char *pWhereClause
= NULL
)
286 { BuildUpdateStmt(pSqlStmt
,typeOfUpd
,pWhereClause
); }
287 void GetWhereClause(char *pWhereClause
, int typeOfWhere
,
288 const char *qualTableName
= NULL
, bool useLikeComparison
=FALSE
)
289 { BuildWhereClause(pWhereClause
,typeOfWhere
,qualTableName
,useLikeComparison
); }
291 bool CanSelectForUpdate(void);
292 bool CanUpdByROWID(void);
293 void ClearMemberVar(UWORD colNo
, bool setToNull
=FALSE
);
294 void ClearMemberVars(bool setToNull
=FALSE
);
295 bool SetQueryTimeout(UDWORD nSeconds
);
297 wxDbColDef
*GetColDefs() { return colDefs
; }
298 void SetColDefs(UWORD index
, const wxString
&fieldName
, int dataType
,
299 void *pData
, SWORD cType
,
300 int size
, bool keyField
= FALSE
, bool upd
= TRUE
,
301 bool insAllow
= TRUE
, bool derivedCol
= FALSE
);
302 wxDbColDataPtr
*SetColDefs(wxDbColInf
*colInfs
, UWORD numCols
);
304 bool CloseCursor(HSTMT cursor
);
305 bool DeleteCursor(HSTMT
*hstmtDel
);
306 void SetCursor(HSTMT
*hstmtActivate
= (void **) wxDB_DEFAULT_CURSOR
);
307 HSTMT
GetCursor(void) { return(hstmt
); }
308 HSTMT
*GetNewCursor(bool setCursor
= FALSE
, bool bindColumns
= TRUE
);
309 #if wxODBC_BACKWARD_COMPATABILITY
310 // The following member function is deprecated. You should use the GetNewCursor
311 HSTMT
*NewCursor(bool setCursor
= FALSE
, bool bindColumns
= TRUE
) { return GetNewCursor(setCursor
,bindColumns
); }
314 ULONG
Count(const wxString
&args
="*");
315 int DB_STATUS(void) { return(pDb
->DB_STATUS
); }
317 bool IsColNull(UWORD colNo
) const;
318 bool SetColNull(UWORD colNo
, bool set
=TRUE
);
319 bool SetColNull(const wxString
&colName
, bool set
=TRUE
);
320 #if wxODBC_BACKWARD_COMPATABILITY
321 // The following member functions are deprecated. You should use the SetColNull()
322 bool SetNull(int colNo
, bool set
=TRUE
) { return (SetNull(colNo
,set
)); }
323 bool SetNull(const char *colName
, bool set
=TRUE
) { return (SetNull(colName
,set
)); }
326 ULONG
GetTableID() { return tableID
; }
329 //TODO: Need to Document
330 typedef enum { WX_ROW_MODE_QUERY
, WX_ROW_MODE_INDIVIDUAL
} rowmode_t
;
331 virtual void SetRowMode(const rowmode_t rowmode
);
332 virtual wxVariant
GetCol(const int Colno
) const ;
333 virtual void SetCol(const int ColNo
, const wxVariant value
);
334 virtual GenericKey
GetKey(void);
335 virtual void SetKey(const GenericKey
&key
);
338 HSTMT
*m_hstmtGridQuery
;
342 // typedef enum {unmodified=0, UpdatePending, InsertPending } recStatus;
344 // recStatus get_ModifiedStatus() { return m_recstatus; }
347 // if (m_recstatus==unmodified)
348 // m_recstatus=UpdatePending;
351 // void insertify() {m_recstatus=InsertPending; }
352 // void unmodify() {m_recstatus=unmodified; }
353 // recStatus m_recstatus;
354 //TODO: Need to Document
358 // Change this to 0 to remove use of all deprecated functions
359 #if wxODBC_BACKWARD_COMPATABILITY
360 //#################################################################################
361 //############### DEPRECATED functions for backward compatability #################
362 //#################################################################################
364 // Backward compability. These will eventually go away
365 typedef wxDbTable wxTable
;
366 typedef wxDbIdxDef wxIdxDef
;
367 typedef wxDbIdxDef CidxDef
;
368 typedef wxDbColDef wxColDef
;
369 typedef wxDbColDef CcolDef
;
370 typedef wxDbColDataPtr wxColDataPtr
;
371 typedef wxDbColDataPtr CcolDataPtr
;
373 const int ROWID
= wxDB_ROWID_LEN
;
374 const int DEFAULT_CURSOR
= wxDB_DEFAULT_CURSOR
;
375 const bool QUERY_ONLY
= wxDB_QUERY_ONLY
;
376 const bool DISABLE_VIEW
= wxDB_DISABLE_VIEW
;