1 /////////////////////////////////////////////////////////////////////////////// 2 // Name: dbtable.h 3 // Purpose: Declaration of the wxDbTable class. 4 // Author: Doug Card 5 // Modified by: George Tasker 6 // Bart Jourquin 7 // Mark Johnson 8 // Created: 9.96 9 // RCS-ID: $Id: dbtable.h 61872 2009-09-09 22:37:05Z VZ $ 10 // Copyright: (c) 1996 Remstar International, Inc. 11 // Licence: wxWindows licence 12 /////////////////////////////////////////////////////////////////////////////// 13 14 /* 15 // SYNOPSIS START 16 // SYNOPSIS STOP 17 */ 18 19 #ifndef DBTABLE_DOT_H 20 #define DBTABLE_DOT_H 21 22 #include "wx/defs.h" 23 24 #include "wx/db.h" 25 26 #include "wx/variant.h" 27 #include "wx/dbkeyg.h" 28 29 const int wxDB_ROWID_LEN = 24; // 18 is the max, 24 is in case it gets larger 30 const int wxDB_DEFAULT_CURSOR = 0; 31 const bool wxDB_QUERY_ONLY = true; 32 const bool wxDB_DISABLE_VIEW = true; 33 34 // Used to indicate end of a variable length list of 35 // column numbers passed to member functions 36 const int wxDB_NO_MORE_COLUMN_NUMBERS = -1; 37 38 // The following class is used to define a column of a table. 39 // The wxDbTable constructor will dynamically allocate as many of 40 // these as there are columns in the table. The class derived 41 // from wxDbTable must initialize these column definitions in it's 42 // constructor. These column definitions provide inf. to the 43 // wxDbTable class which allows it to create a table in the data 44 // source, exchange data between the data source and the C++ 45 // object, and so on. 46 class WXDLLIMPEXP_ODBC wxDbColDef 47 { 48 public: 49 wxChar ColName[DB_MAX_COLUMN_NAME_LEN+1]; // Column Name 50 int DbDataType; // Logical Data Type; e.g. DB_DATA_TYPE_INTEGER 51 SWORD SqlCtype; // C data type; e.g. SQL_C_LONG 52 void *PtrDataObj; // Address of the data object 53 int SzDataObj; // Size, in bytes, of the data object 54 bool KeyField; // true if this column is part of the PRIMARY KEY to the table; Date fields should NOT be KeyFields. 55 bool Updateable; // Specifies whether this column is updateable 56 bool InsertAllowed; // Specifies whether this column should be included in an INSERT statement 57 bool DerivedCol; // Specifies whether this column is a derived value 58 SQLLEN CbValue; // Internal use only!!! 59 bool Null; // NOT FULLY IMPLEMENTED - Allows NULL values in Inserts and Updates 60 61 wxDbColDef(); 62 63 bool Initialize(); 64 }; // wxDbColDef 65 66 67 class WXDLLIMPEXP_ODBC wxDbColDataPtr 68 { 69 public: 70 void *PtrDataObj; 71 int SzDataObj; 72 SWORD SqlCtype; 73 }; // wxDbColDataPtr 74 75 76 // This structure is used when creating secondary indexes. 77 class WXDLLIMPEXP_ODBC wxDbIdxDef 78 { 79 public: 80 wxChar ColName[DB_MAX_COLUMN_NAME_LEN+1]; 81 bool Ascending; 82 }; // wxDbIdxDef 83 84 85 class WXDLLIMPEXP_ODBC wxDbTable 86 { 87 private: 88 ULONG tableID; // Used for debugging. This can help to match up mismatched constructors/destructors 89 90 // Private member variables 91 UDWORD cursorType; 92 bool insertable; 93 94 // Private member functions 95 bool initialize(wxDb *pwxDb, const wxString &tblName, const UWORD numColumns, 96 const wxString &qryTblName, bool qryOnly, const wxString &tblPath); 97 void cleanup(); 98 99 void setCbValueForColumn(int columnIndex); 100 bool bindParams(bool forUpdate); // called by the other 'bind' functions 101 bool bindInsertParams(void); 102 bool bindUpdateParams(void); 103 104 bool bindCols(HSTMT cursor); 105 bool getRec(UWORD fetchType); 106 bool execDelete(const wxString &pSqlStmt); 107 bool execUpdate(const wxString &pSqlStmt); 108 bool query(int queryType, bool forUpdate, bool distinct, const wxString &pSqlStmt=wxEmptyString); 109 110 #if !wxODBC_BACKWARD_COMPATABILITY 111 // these were public 112 // Where, Order By and From clauses 113 wxString where; // Standard SQL where clause, minus the word WHERE 114 wxString orderBy; // Standard SQL order by clause, minus the ORDER BY 115 wxString from; // Allows for joins in a wxDbTable::Query(). Format: ",tbl,tbl..." 116 117 // ODBC Handles 118 HENV henv; // ODBC Environment handle 119 HDBC hdbc; // ODBC DB Connection handle 120 HSTMT hstmt; // ODBC Statement handle 121 HSTMT *hstmtDefault; // Default cursor 122 HSTMT hstmtInsert; // ODBC Statement handle used specifically for inserts 123 HSTMT hstmtDelete; // ODBC Statement handle used specifically for deletes 124 HSTMT hstmtUpdate; // ODBC Statement handle used specifically for updates 125 HSTMT hstmtInternal; // ODBC Statement handle used internally only 126 HSTMT *hstmtCount; // ODBC Statement handle used by Count() function (No binding of columns) 127 128 // Flags 129 bool selectForUpdate; 130 131 // Pointer to the database object this table belongs to 132 wxDb *pDb; 133 134 // Table Inf. 135 wxString tablePath; // needed for dBase tables 136 wxString tableName; // Table name 137 wxString queryTableName; // Query Table Name 138 UWORD m_numCols; // # of columns in the table 139 bool queryOnly; // Query Only, no inserts, updates or deletes 140 141 // Column Definitions 142 wxDbColDef *colDefs; // Array of wxDbColDef structures 143 #endif 144 public: 145 #if wxODBC_BACKWARD_COMPATABILITY 146 // Where, Order By and From clauses 147 char *where; // Standard SQL where clause, minus the word WHERE 148 char *orderBy; // Standard SQL order by clause, minus the ORDER BY 149 char *from; // Allows for joins in a wxDbTable::Query(). Format: ",tbl,tbl..." 150 151 // ODBC Handles 152 HENV henv; // ODBC Environment handle 153 HDBC hdbc; // ODBC DB Connection handle 154 HSTMT hstmt; // ODBC Statement handle 155 HSTMT *hstmtDefault; // Default cursor 156 HSTMT hstmtInsert; // ODBC Statement handle used specifically for inserts 157 HSTMT hstmtDelete; // ODBC Statement handle used specifically for deletes 158 HSTMT hstmtUpdate; // ODBC Statement handle used specifically for updates 159 HSTMT hstmtInternal; // ODBC Statement handle used internally only 160 HSTMT *hstmtCount; // ODBC Statement handle used by Count() function (No binding of columns) 161 162 // Flags 163 bool selectForUpdate; 164 165 // Pointer to the database object this table belongs to 166 wxDb *pDb; 167 168 // Table Inf. 169 char tablePath[wxDB_PATH_MAX]; // needed for dBase tables 170 char tableName[DB_MAX_TABLE_NAME_LEN+1]; // Table name 171 char queryTableName[DB_MAX_TABLE_NAME_LEN+1]; // Query Table Name 172 UWORD m_numCols; // # of columns in the table 173 bool queryOnly; // Query Only, no inserts, updates or deletes 174 175 // Column Definitions 176 wxDbColDef *colDefs; // Array of wxDbColDef structures 177 #endif 178 // Public member functions 179 wxDbTable(wxDb *pwxDb, const wxString &tblName, const UWORD numColumns, 180 const wxString &qryTblName=wxEmptyString, bool qryOnly = !wxDB_QUERY_ONLY, 181 const wxString &tblPath=wxEmptyString); 182 183 #if WXWIN_COMPATIBILITY_2_4 184 wxDEPRECATED( 185 wxDbTable(wxDb *pwxDb, const wxString &tblName, const UWORD numColumns, 186 const wxChar *qryTblName, bool qryOnly, 187 const wxString &tblPath) 188 ); 189 #endif // WXWIN_COMPATIBILITY_2_4 190 191 virtual ~wxDbTable(); 192 193 bool Open(bool checkPrivileges=false, bool checkTableExists=true); 194 bool CreateTable(bool attemptDrop=true); 195 bool DropTable(void); 196 bool CreateIndex(const wxString &indexName, bool unique, UWORD numIndexColumns, 197 wxDbIdxDef *pIndexDefs, bool attemptDrop=true); 198 bool DropIndex(const wxString &indexName); 199 200 // Accessors 201 202 // The member variables returned by these accessors are all 203 // set when the wxDbTable instance is created and cannot be 204 // changed, hence there is no corresponding SetXxxx function GetDb()205 wxDb *GetDb() { return pDb; } GetTableName()206 const wxString &GetTableName() { return tableName; } GetQueryTableName()207 const wxString &GetQueryTableName() { return queryTableName; } GetTablePath()208 const wxString &GetTablePath() { return tablePath; } 209 GetNumberOfColumns()210 UWORD GetNumberOfColumns() { return m_numCols; } // number of "defined" columns for this wxDbTable instance 211 GetFromClause()212 const wxString &GetFromClause() { return from; } GetOrderByClause()213 const wxString &GetOrderByClause() { return orderBy; } GetWhereClause()214 const wxString &GetWhereClause() { return where; } 215 IsQueryOnly()216 bool IsQueryOnly() { return queryOnly; } 217 #if wxODBC_BACKWARD_COMPATABILITY SetFromClause(const char * From)218 void SetFromClause(const char *From) { from = (char *)From; } SetOrderByClause(const char * OrderBy)219 void SetOrderByClause(const char *OrderBy) { orderBy = (char *)OrderBy; } SetWhereClause(const char * Where)220 void SetWhereClause(const char *Where) { where = (char *)Where; } 221 #else SetFromClause(const wxString & From)222 void SetFromClause(const wxString &From) { from = From; } SetOrderByClause(const wxString & OrderBy)223 void SetOrderByClause(const wxString &OrderBy) { orderBy = OrderBy; } 224 bool SetOrderByColNums(UWORD first, ...); SetWhereClause(const wxString & Where)225 void SetWhereClause(const wxString &Where) { where = Where; } From(const wxString & From)226 void From(const wxString &From) { from = From; } OrderBy(const wxString & OrderBy)227 void OrderBy(const wxString &OrderBy) { orderBy = OrderBy; } Where(const wxString & Where)228 void Where(const wxString &Where) { where = Where; } Where()229 const wxString &Where() { return where; } OrderBy()230 const wxString &OrderBy() { return orderBy; } From()231 const wxString &From() { return from; } 232 #endif 233 int Insert(void); 234 bool Update(void); 235 bool Update(const wxString &pSqlStmt); 236 bool UpdateWhere(const wxString &pWhereClause); 237 bool Delete(void); 238 bool DeleteWhere(const wxString &pWhereClause); 239 bool DeleteMatching(void); 240 virtual bool Query(bool forUpdate = false, bool distinct = false); 241 bool QueryBySqlStmt(const wxString &pSqlStmt); 242 bool QueryMatching(bool forUpdate = false, bool distinct = false); 243 bool QueryOnKeyFields(bool forUpdate = false, bool distinct = false); 244 bool Refresh(void); GetNext(void)245 bool GetNext(void) { return(getRec(SQL_FETCH_NEXT)); } 246 bool operator++(int) { return(getRec(SQL_FETCH_NEXT)); } 247 248 /***** These four functions only work with wxDb instances that are defined ***** 249 ***** as not being FwdOnlyCursors *****/ 250 bool GetPrev(void); 251 bool operator--(int); 252 bool GetFirst(void); 253 bool GetLast(void); 254 255 bool IsCursorClosedOnCommit(void); 256 UWORD GetRowNum(void); 257 258 void BuildSelectStmt(wxString &pSqlStmt, int typeOfSelect, bool distinct); 259 void BuildSelectStmt(wxChar *pSqlStmt, int typeOfSelect, bool distinct); 260 261 void BuildDeleteStmt(wxString &pSqlStmt, int typeOfDel, const wxString &pWhereClause=wxEmptyString); 262 void BuildDeleteStmt(wxChar *pSqlStmt, int typeOfDel, const wxString &pWhereClause=wxEmptyString); 263 264 void BuildUpdateStmt(wxString &pSqlStmt, int typeOfUpdate, const wxString &pWhereClause=wxEmptyString); 265 void BuildUpdateStmt(wxChar *pSqlStmt, int typeOfUpdate, const wxString &pWhereClause=wxEmptyString); 266 267 void BuildWhereClause(wxString &pWhereClause, int typeOfWhere, const wxString &qualTableName=wxEmptyString, bool useLikeComparison=false); 268 void BuildWhereClause(wxChar *pWhereClause, int typeOfWhere, const wxString &qualTableName=wxEmptyString, bool useLikeComparison=false); 269 270 #if wxODBC_BACKWARD_COMPATABILITY 271 // The following member functions are deprecated. You should use the BuildXxxxxStmt functions (above) GetSelectStmt(char * pSqlStmt,int typeOfSelect,bool distinct)272 void GetSelectStmt(char *pSqlStmt, int typeOfSelect, bool distinct) 273 { BuildSelectStmt(pSqlStmt,typeOfSelect,distinct); } 274 void GetDeleteStmt(char *pSqlStmt, int typeOfDel, const char *pWhereClause = NULL) 275 { BuildDeleteStmt(pSqlStmt,typeOfDel,pWhereClause); } 276 void GetUpdateStmt(char *pSqlStmt, int typeOfUpdate, const char *pWhereClause = NULL) 277 { BuildUpdateStmt(pSqlStmt,typeOfUpdate,pWhereClause); } 278 void GetWhereClause(char *pWhereClause, int typeOfWhere, 279 const char *qualTableName = NULL, bool useLikeComparison=false) 280 { BuildWhereClause(pWhereClause,typeOfWhere,qualTableName,useLikeComparison); } 281 #endif 282 bool CanSelectForUpdate(void); 283 #if wxODBC_BACKWARD_COMPATABILITY CanUpdByROWID(void)284 bool CanUpdByROWID(void) { return CanUpdateByRowID(); }; 285 #endif 286 bool CanUpdateByROWID(void); 287 void ClearMemberVar(UWORD colNumber, bool setToNull=false); 288 void ClearMemberVars(bool setToNull=false); 289 bool SetQueryTimeout(UDWORD nSeconds); 290 GetColDefs()291 wxDbColDef *GetColDefs() { return colDefs; } 292 bool SetColDefs(UWORD index, const wxString &fieldName, int dataType, 293 void *pData, SWORD cType, 294 int size, bool keyField = false, bool updateable = true, 295 bool insertAllowed = true, bool derivedColumn = false); 296 wxDbColDataPtr *SetColDefs(wxDbColInf *colInfs, UWORD numCols); 297 298 bool CloseCursor(HSTMT cursor); 299 bool DeleteCursor(HSTMT *hstmtDel); 300 void SetCursor(HSTMT *hstmtActivate = (void **) wxDB_DEFAULT_CURSOR); GetCursor(void)301 HSTMT GetCursor(void) { return(hstmt); } 302 HSTMT *GetNewCursor(bool setCursor = false, bool bindColumns = true); 303 #if wxODBC_BACKWARD_COMPATABILITY 304 // The following member function is deprecated. You should use the GetNewCursor 305 HSTMT *NewCursor(bool setCursor = false, bool bindColumns = true) { return GetNewCursor(setCursor,bindColumns); } 306 #endif 307 308 ULONG Count(const wxString &args=wxT("*")); DB_STATUS(void)309 int DB_STATUS(void) { return(pDb->DB_STATUS); } 310 311 bool IsColNull(UWORD colNumber) const; 312 bool SetColNull(UWORD colNumber, bool set=true); 313 bool SetColNull(const wxString &colName, bool set=true); 314 #if wxODBC_BACKWARD_COMPATABILITY 315 // The following member functions are deprecated. You should use the SetColNull() 316 bool SetNull(int colNumber, bool set=true) { return (SetNull(colNumber,set)); } 317 bool SetNull(const char *colName, bool set=true) { return (SetNull(colName,set)); } 318 #endif 319 #ifdef __WXDEBUG__ GetTableID()320 ULONG GetTableID() { return tableID; } 321 #endif 322 323 //TODO: Need to Document 324 typedef enum { WX_ROW_MODE_QUERY , WX_ROW_MODE_INDIVIDUAL } rowmode_t; 325 virtual void SetRowMode(const rowmode_t rowmode); 326 #if wxODBC_BACKWARD_COMPATABILITY GetCol(const int colNumber)327 virtual wxVariant GetCol(const int colNumber) const { return GetColumn(colNumber); }; SetCol(const int colNumber,const wxVariant value)328 virtual void SetCol(const int colNumber, const wxVariant value) { return SetColumn(colNumber, value); }; 329 #endif 330 virtual wxVariant GetColumn(const int colNumber) const ; 331 virtual void SetColumn(const int colNumber, const wxVariant value); 332 virtual GenericKey GetKey(void); 333 virtual void SetKey(const GenericKey &key); 334 335 private: 336 HSTMT *m_hstmtGridQuery; 337 rowmode_t m_rowmode; 338 size_t m_keysize; 339 340 // typedef enum {unmodified=0, UpdatePending, InsertPending } recStatus; 341 342 // recStatus get_ModifiedStatus() { return m_recstatus; } 343 344 // void modify() { 345 // if (m_recstatus==unmodified) 346 // m_recstatus=UpdatePending; 347 // } 348 // protected: 349 // void insertify() {m_recstatus=InsertPending; } 350 // void unmodify() {m_recstatus=unmodified; } 351 // recStatus m_recstatus; 352 //TODO: Need to Document 353 }; // wxDbTable 354 355 356 // Change this to 0 to remove use of all deprecated functions 357 #if wxODBC_BACKWARD_COMPATABILITY 358 //################################################################################# 359 //############### DEPRECATED functions for backward compatibility ################# 360 //################################################################################# 361 362 // Backward compability. These will eventually go away 363 typedef wxDbTable wxTable; 364 typedef wxDbIdxDef wxIdxDef; 365 typedef wxDbIdxDef CidxDef; 366 typedef wxDbColDef wxColDef; 367 typedef wxDbColDef CcolDef; 368 typedef wxDbColDataPtr wxColDataPtr; 369 typedef wxDbColDataPtr CcolDataPtr; 370 371 const int ROWID = wxDB_ROWID_LEN; 372 const int DEFAULT_CURSOR = wxDB_DEFAULT_CURSOR; 373 const bool QUERY_ONLY = wxDB_QUERY_ONLY; 374 const bool DISABLE_VIEW = wxDB_DISABLE_VIEW; 375 #endif 376 377 #endif 378