include/wx/db.h

   1 ///////////////////////////////////////////////////////////////////////////////
   2 // Name:        db.h
   3 // Purpose:     Header file wxDB class.  The wxDB class represents a connection
   4 //              to an ODBC data source.  The wxDB class allows operations on the data
   5 //              source such as opening and closing the data source.
   6 // Author:      Doug Card
   7 // Modified by:
   8 // Created:     9.96
   9 // RCS-ID:      $Id$
  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 ///////////////////////////////////////////////////////////////////////////////
  22
  23 /*
  24 // SYNOPSIS START
  25 // SYNOPSIS STOP
  26 */
  27
  28 #ifndef DB_DOT_H
  29 #define DB_DOT_H
  30
  31 #ifdef __GNUG__
  32 #pragma interface "db.h"
  33 #endif
  34
  35 #if defined(wx_msw) || defined(WIN32)
  36 #include <windows.h>
  37 #endif
  38
  39 #define ODBCVER 0x0250
  40 #include <sql.h>
  41 #include <sqlext.h>
  42
  43 enum            enumDummy               {enumDum1};
  44
  45 #define SQL_C_BOOLEAN (sizeof(int) == 2 ? SQL_C_USHORT : SQL_C_ULONG)
  46 #define SQL_C_ENUM (sizeof(enumDummy) == 2 ? SQL_C_USHORT : SQL_C_ULONG)    //glt 2-21-97
  47
  48 /*
  49 #ifndef Bool
  50 #define Bool int
  51 #endif
  52
  53 #ifndef TRUE
  54 #define TRUE 1
  55 #endif
  56
  57 #ifndef FALSE
  58 #define FALSE 0
  59 #endif
  60 */
  61
  62 // Database Globals
  63 const DB_TYPE_NAME_LEN                                          = 40;
  64 const DB_MAX_STATEMENT_LEN                                      = 2048;
  65 const DB_MAX_WHERE_CLAUSE_LEN                           = 1024;
  66 const DB_MAX_ERROR_MSG_LEN                                      = 512;
  67 const DB_MAX_ERROR_HISTORY                                      = 5;
  68 const DB_MAX_TABLE_NAME_LEN                             = 128;
  69 const DB_MAX_COLUMN_NAME_LEN                            = 128;
  70
  71 const DB_DATA_TYPE_VARCHAR                                      = 1;
  72 const DB_DATA_TYPE_INTEGER                                      = 2;
  73 const DB_DATA_TYPE_FLOAT                                        = 3;
  74 const   DB_DATA_TYPE_DATE                                               = 4;
  75
  76 const DB_SELECT_KEYFIELDS                                       = 1;
  77 const DB_SELECT_WHERE                                           = 2;
  78 const DB_SELECT_MATCHING                                        = 3;
  79 const DB_SELECT_STATEMENT                                       = 4;
  80
  81 const DB_UPD_KEYFIELDS                                          = 1;
  82 const DB_UPD_WHERE                                                      = 2;
  83
  84 const DB_DEL_KEYFIELDS                                          = 1;
  85 const DB_DEL_WHERE                                                      = 2;
  86 const DB_DEL_MATCHING                                           = 3;
  87
  88 const DB_WHERE_KEYFIELDS                                        = 1;
  89 const DB_WHERE_MATCHING                                         = 2;
  90
  91 const DB_CURSOR0                                                                = 0;
  92 const DB_CURSOR1                                                                = 1;
  93 const DB_CURSOR2                                                                = 2;
  94 //const DB_CURSOR3                                                      = 3;
  95 //const DB_CURSOR4                                                      = 4;
  96 //const DB_CURSOR5                                                      = 5;
  97
  98 const DB_GRANT_SELECT                                           = 1;
  99 const DB_GRANT_INSERT                                           = 2;
 100 const DB_GRANT_UPDATE                                           = 4;
 101 const DB_GRANT_DELETE                                           = 8;
 102 const DB_GRANT_ALL                                                      = DB_GRANT_SELECT | DB_GRANT_INSERT | DB_GRANT_UPDATE | DB_GRANT_DELETE;
 103
 104 // ODBC Error codes (derived from ODBC SqlState codes)
 105 enum ODBC_ERRORS
 106 {
 107         DB_FAILURE                                                                      = 0,
 108         DB_SUCCESS                                                                      = 1,
 109         DB_ERR_NOT_IN_USE,
 110         DB_ERR_GENERAL_WARNING,                                                                 // SqlState = '01000'
 111         DB_ERR_DISCONNECT_ERROR,                                                                // SqlState = '01002'
 112         DB_ERR_DATA_TRUNCATED,                                                                  // SqlState = '01004'
 113         DB_ERR_PRIV_NOT_REVOKED,                                                                // SqlState = '01006'
 114         DB_ERR_INVALID_CONN_STR_ATTR,                                                   // SqlState = '01S00'
 115         DB_ERR_ERROR_IN_ROW,                                                                            // SqlState = '01S01'
 116         DB_ERR_OPTION_VALUE_CHANGED,                                                    // SqlState = '01S02'
 117         DB_ERR_NO_ROWS_UPD_OR_DEL,                                                              // SqlState = '01S03'
 118         DB_ERR_MULTI_ROWS_UPD_OR_DEL,                                                   // SqlState = '01S04'
 119         DB_ERR_WRONG_NO_OF_PARAMS,                                                              // SqlState = '07001'
 120         DB_ERR_DATA_TYPE_ATTR_VIOL,                                                     // SqlState = '07006'
 121         DB_ERR_UNABLE_TO_CONNECT,                                                               // SqlState = '08001'
 122         DB_ERR_CONNECTION_IN_USE,                                                               // SqlState = '08002'
 123         DB_ERR_CONNECTION_NOT_OPEN,                                                     // SqlState = '08003'
 124         DB_ERR_REJECTED_CONNECTION,                                                     // SqlState = '08004'
 125         DB_ERR_CONN_FAIL_IN_TRANS,                                                              // SqlState = '08007'
 126         DB_ERR_COMM_LINK_FAILURE,                                                               // SqlState = '08S01'
 127         DB_ERR_INSERT_VALUE_LIST_MISMATCH,                                      // SqlState = '21S01'
 128         DB_ERR_DERIVED_TABLE_MISMATCH,                                          // SqlState = '21S02'
 129         DB_ERR_STRING_RIGHT_TRUNC,                                                              // SqlState = '22001'
 130         DB_ERR_NUMERIC_VALUE_OUT_OF_RNG,                                                // SqlState = '22003'
 131         DB_ERR_ERROR_IN_ASSIGNMENT,                                                     // SqlState = '22005'
 132         DB_ERR_DATETIME_FLD_OVERFLOW,                                                   // SqlState = '22008'
 133         DB_ERR_DIVIDE_BY_ZERO,                                                                  // SqlState = '22012'
 134         DB_ERR_STR_DATA_LENGTH_MISMATCH,                                                // SqlState = '22026'
 135         DB_ERR_INTEGRITY_CONSTRAINT_VIOL,                                       // SqlState = '23000'
 136         DB_ERR_INVALID_CURSOR_STATE,                                                    // SqlState = '24000'
 137         DB_ERR_INVALID_TRANS_STATE,                                                     // SqlState = '25000'
 138         DB_ERR_INVALID_AUTH_SPEC,                                                               // SqlState = '28000'
 139         DB_ERR_INVALID_CURSOR_NAME,                                                     // SqlState = '34000'
 140         DB_ERR_SYNTAX_ERROR_OR_ACCESS_VIOL,                                     // SqlState = '37000'
 141         DB_ERR_DUPLICATE_CURSOR_NAME,                                                   // SqlState = '3C000'
 142         DB_ERR_SERIALIZATION_FAILURE,                                                   // SqlState = '40001'
 143         DB_ERR_SYNTAX_ERROR_OR_ACCESS_VIOL2,                            // SqlState = '42000'
 144         DB_ERR_OPERATION_ABORTED,                                                               // SqlState = '70100'
 145         DB_ERR_UNSUPPORTED_FUNCTION,                                                    // SqlState = 'IM001'
 146         DB_ERR_NO_DATA_SOURCE,                                                                  // SqlState = 'IM002'
 147         DB_ERR_DRIVER_LOAD_ERROR,                                                               // SqlState = 'IM003'
 148         DB_ERR_SQLALLOCENV_FAILED,                                                              // SqlState = 'IM004'
 149         DB_ERR_SQLALLOCCONNECT_FAILED,                                          // SqlState = 'IM005'
 150         DB_ERR_SQLSETCONNECTOPTION_FAILED,                                      // SqlState = 'IM006'
 151         DB_ERR_NO_DATA_SOURCE_DLG_PROHIB,                                       // SqlState = 'IM007'
 152         DB_ERR_DIALOG_FAILED,                                                                   // SqlState = 'IM008'
 153         DB_ERR_UNABLE_TO_LOAD_TRANSLATION_DLL,                          // SqlState = 'IM009'
 154         DB_ERR_DATA_SOURCE_NAME_TOO_LONG,                                       // SqlState = 'IM010'
 155         DB_ERR_DRIVER_NAME_TOO_LONG,                                                    // SqlState = 'IM011'
 156         DB_ERR_DRIVER_KEYWORD_SYNTAX_ERROR,                                     // SqlState = 'IM012'
 157         DB_ERR_TRACE_FILE_ERROR,                                                                // SqlState = 'IM013'
 158         DB_ERR_TABLE_OR_VIEW_ALREADY_EXISTS,                            // SqlState = 'S0001'
 159         DB_ERR_TABLE_NOT_FOUND,                                                                 // SqlState = 'S0002'
 160         DB_ERR_INDEX_ALREADY_EXISTS,                                                    // SqlState = 'S0011'
 161         DB_ERR_INDEX_NOT_FOUND,                                                                 // SqlState = 'S0012'
 162         DB_ERR_COLUMN_ALREADY_EXISTS,                                                   // SqlState = 'S0021'
 163         DB_ERR_COLUMN_NOT_FOUND,                                                                // SqlState = 'S0022'
 164         DB_ERR_NO_DEFAULT_FOR_COLUMN,                                                   // SqlState = 'S0023'
 165         DB_ERR_GENERAL_ERROR,                                                                   // SqlState = 'S1000'
 166         DB_ERR_MEMORY_ALLOCATION_FAILURE,                                       // SqlState = 'S1001'
 167         DB_ERR_INVALID_COLUMN_NUMBER,                                                   // SqlState = 'S1002'
 168         DB_ERR_PROGRAM_TYPE_OUT_OF_RANGE,                                       // SqlState = 'S1003'
 169         DB_ERR_SQL_DATA_TYPE_OUT_OF_RANGE,                                      // SqlState = 'S1004'
 170         DB_ERR_OPERATION_CANCELLED,                                                     // SqlState = 'S1008'
 171         DB_ERR_INVALID_ARGUMENT_VALUE,                                          // SqlState = 'S1009'
 172         DB_ERR_FUNCTION_SEQUENCE_ERROR,                                         // SqlState = 'S1010'
 173         DB_ERR_OPERATION_INVALID_AT_THIS_TIME,                          // SqlState = 'S1011'
 174         DB_ERR_INVALID_TRANS_OPERATION_CODE,                            // SqlState = 'S1012'
 175         DB_ERR_NO_CURSOR_NAME_AVAIL,                                                    // SqlState = 'S1015'
 176         DB_ERR_INVALID_STR_OR_BUF_LEN,                                          // SqlState = 'S1090'
 177         DB_ERR_DESCRIPTOR_TYPE_OUT_OF_RANGE,                            // SqlState = 'S1091'
 178         DB_ERR_OPTION_TYPE_OUT_OF_RANGE,                                                // SqlState = 'S1092'
 179         DB_ERR_INVALID_PARAM_NO,                                                                // SqlState = 'S1093'
 180         DB_ERR_INVALID_SCALE_VALUE,                                                     // SqlState = 'S1094'
 181         DB_ERR_FUNCTION_TYPE_OUT_OF_RANGE,                                      // SqlState = 'S1095'
 182         DB_ERR_INF_TYPE_OUT_OF_RANGE,                                                   // SqlState = 'S1096'
 183         DB_ERR_COLUMN_TYPE_OUT_OF_RANGE,                                                // SqlState = 'S1097'
 184         DB_ERR_SCOPE_TYPE_OUT_OF_RANGE,                                         // SqlState = 'S1098'
 185         DB_ERR_NULLABLE_TYPE_OUT_OF_RANGE,                                      // SqlState = 'S1099'
 186         DB_ERR_UNIQUENESS_OPTION_TYPE_OUT_OF_RANGE,             // SqlState = 'S1100'
 187         DB_ERR_ACCURACY_OPTION_TYPE_OUT_OF_RANGE,                       // SqlState = 'S1101'
 188         DB_ERR_DIRECTION_OPTION_OUT_OF_RANGE,                           // SqlState = 'S1103'
 189         DB_ERR_INVALID_PRECISION_VALUE,                                         // SqlState = 'S1104'
 190         DB_ERR_INVALID_PARAM_TYPE,                                                              // SqlState = 'S1105'
 191         DB_ERR_FETCH_TYPE_OUT_OF_RANGE,                                         // SqlState = 'S1106'
 192         DB_ERR_ROW_VALUE_OUT_OF_RANGE,                                          // SqlState = 'S1107'
 193         DB_ERR_CONCURRENCY_OPTION_OUT_OF_RANGE,                 // SqlState = 'S1108'
 194         DB_ERR_INVALID_CURSOR_POSITION,                                         // SqlState = 'S1109'
 195         DB_ERR_INVALID_DRIVER_COMPLETION,                                       // SqlState = 'S1110'
 196         DB_ERR_INVALID_BOOKMARK_VALUE,                                          // SqlState = 'S1111'
 197         DB_ERR_DRIVER_NOT_CAPABLE,                                                              // SqlState = 'S1C00'
 198         DB_ERR_TIMEOUT_EXPIRED                                                                  // SqlState = 'S1T00'
 199 };
 200
 201 struct DbStuff
 202 {
 203         HENV Henv;
 204         char Dsn[SQL_MAX_DSN_LENGTH+1]; // Data Source Name
 205         char Uid[20];                                                   // User ID
 206         char AuthStr[20];                                               // Authorization string (password)
 207 };
 208
 209 typedef struct
 210 {
 211         char    TypeName[DB_TYPE_NAME_LEN];
 212         int     FsqlType;
 213         long    Precision;
 214         short   CaseSensitive;
 215 //      short MinimumScale;
 216         short MaximumScale;
 217 } SqlTypeInfo;
 218
 219 class CcolInf
 220 {
 221 public:
 222         char tableName[DB_MAX_TABLE_NAME_LEN+1];
 223         char colName[DB_MAX_COLUMN_NAME_LEN+1];
 224         int  sqlDataType;
 225 };
 226
 227 class wxDB
 228 {
 229 private:
 230
 231         // Private data
 232         bool dbIsOpen;
 233         char *dsn;                      // Data source name
 234         char *uid;                      // User ID
 235         char *authStr;          // Authorization string (password)
 236
 237         // Private member functions
 238         bool getDbInfo(void);
 239         bool getDataTypeInfo(SWORD fSqlType, SqlTypeInfo &structSQLTypeInfo);
 240         bool setConnectionOptions(void);
 241         void logError(char *errMsg, char *SQLState);
 242
 243 public:
 244
 245         // The following structure contains database information gathered from the
 246         // datasource when the datasource is first opened.
 247         struct
 248         {
 249                 char   dbmsName[40];                                                            // Name of the dbms product
 250                 char   dbmsVer[20];                                                             // Version # of the dbms product
 251                 char   driverName[40];                                                  // Driver name
 252                 char   odbcVer[20];                                                             // ODBC version of the driver
 253                 char   drvMgrOdbcVer[20];                                               // ODBC version of the driver manager
 254                 char   driverVer[40];                                                   // Driver version
 255                 char   serverName[40];                                                  // Server Name, typically a connect string
 256                 char     databaseName[128];                                             // Database filename
 257                 char   outerJoins[2];                                                   // Indicates whether the data source supports outer joins
 258                 char     procedureSupport[2];                                   // Indicates whether the data source supports stored procedures
 259                 UWORD  maxConnections;                                                  // Maximum # of connections the data source supports
 260                 UWORD  maxStmts;                                                                        // Maximum # of HSTMTs per HDBC
 261                 UWORD  apiConfLvl;                                                              // ODBC API conformance level
 262                 UWORD  cliConfLvl;                                                              // Indicates whether the data source is SAG compliant
 263                 UWORD  sqlConfLvl;                                                              // SQL conformance level
 264                 UWORD  cursorCommitBehavior;                                    // Indicates how cursors are affected by a db commit
 265                 UWORD  cursorRollbackBehavior;                          // Indicates how cursors are affected by a db rollback
 266                 UWORD  supportNotNullClause;                                    // Indicates if data source supports NOT NULL clause
 267                 char   supportIEF[2];                                                   // Integrity Enhancement Facility (Referential Integrity)
 268                 UDWORD txnIsolation;                                                            // Default transaction isolation level supported by the driver
 269                 UDWORD txnIsolationOptions;                                     // Transaction isolation level options available
 270                 UDWORD fetchDirections;                                                 // Fetch directions supported
 271                 UDWORD lockTypes;                                                                       // Lock types supported in SQLSetPos
 272                 UDWORD posOperations;                                                   // Position operations supported in SQLSetPos
 273                 UDWORD posStmts;                                                                        // Position statements supported
 274                 UDWORD scrollConcurrency;                                               // Concurrency control options supported for scrollable cursors
 275                 UDWORD scrollOptions;                                                   // Scroll Options supported for scrollable cursors
 276                 UDWORD staticSensitivity;                                               // Indicates if additions, deletions and updates can be detected
 277                 UWORD  txnCapable;                                                              // Indicates if the data source supports transactions
 278                 UDWORD loginTimeout;                                                            // Number seconds to wait for a login request
 279         } dbInf;
 280
 281         // ODBC handles
 282         HENV  henv;             // ODBC Environment handle
 283         HDBC  hdbc;             // ODBC DB Connection handle
 284         HSTMT hstmt;    // ODBC Statement handle
 285
 286         // ODBC Error Inf.
 287         char   sqlState[20];
 288         SDWORD nativeError;
 289         char   errorMsg[SQL_MAX_MESSAGE_LENGTH];
 290         SWORD  cbErrorMsg;
 291         char   errorList[DB_MAX_ERROR_HISTORY][DB_MAX_ERROR_MSG_LEN];
 292         int    DB_STATUS;
 293
 294         //Error reporting mode
 295         bool silent;
 296
 297         // Inf. about logical data types VARCHAR, INTEGER, FLOAT and DATE.
 298         // This inf. is obtained from the ODBC driver by use of the
 299         // SQLGetTypeInfo() function.  The key piece of inf. is the
 300         // type name the data source uses for each logical data type.
 301         // e.g. VARCHAR; Oracle calls it VARCHAR2.
 302         SqlTypeInfo typeInfVarchar, typeInfInteger, typeInfFloat, typeInfDate;
 303
 304         // Public member functions
 305         wxDB(HENV &aHenv);
 306         bool            Open(char *Dsn, char *Uid, char *AuthStr);  // Data Source Name, User ID, Password
 307         void            Close(void);
 308         bool            CommitTrans(void);
 309         bool            RollbackTrans(void);
 310    bool         DispAllErrors(HENV aHenv, HDBC aHdbc = SQL_NULL_HDBC, HSTMT aHstmt = SQL_NULL_HSTMT);
 311    bool         GetNextError(HENV aHenv, HDBC aHdbc = SQL_NULL_HDBC, HSTMT aHstmt = SQL_NULL_HSTMT);
 312    void         DispNextError(void);
 313         bool            CreateView(char *viewName, char *colList, char *pSqlStmt);
 314         bool            ExecSql(char *pSqlStmt);
 315         bool       Grant(int privileges, char *tableName, char *userList = "PUBLIC");
 316         int        TranslateSqlState(char *SQLState);
 317         CcolInf *GetColumns(char *tableName[]);
 318         char      *GetDatabaseName(void)        {return dbInf.dbmsName;}
 319         char      *GetDataSource(void)          {return dsn;}
 320         char      *GetUsername(void)                    {return uid;}
 321         char      *GetPassword(void)                    {return authStr;}
 322         bool            IsOpen(void)                            {return dbIsOpen;}
 323         HENV            GetHENV(void)                           {return henv;}
 324         HDBC            GetHDBC(void)                           {return hdbc;}
 325         HSTMT           GetHSTMT(void)                          {return hstmt;}
 326         bool            TableExists(char *tableName);  // Table name can refer to a table, view, alias or synonym
 327         void            LogError(char *errMsg, char *SQLState = 0) {logError(errMsg, SQLState);}
 328
 329 };  // wxDB
 330
 331 // This structure forms a node in a linked list.  The linked list of "DbList" objects
 332 // keeps track of allocated database connections.  This allows the application to
 333 // open more than one database connection through ODBC for multiple transaction support
 334 // or for multiple database support.
 335
 336 struct DbList
 337 {
 338         DbList *PtrPrev;                                                        // Pointer to previous item in the list
 339         char    Dsn[SQL_MAX_DSN_LENGTH+1];      // Data Source Name
 340         wxDB     *PtrDb;                                                                // Pointer to the wxDB object
 341         bool    Free;                                                           // Is item free or in use?
 342         DbList *PtrNext;                                                        // Pointer to next item in the list
 343 };
 344
 345 // The following routines allow a user to get new database connections, free them
 346 // for other code segments to use, or close all of them when the application has
 347 // completed.
 348
 349 wxDB  *GetDbConnection(DbStuff *pDbStuff);
 350 bool  FreeDbConnection(wxDB *pDb);
 351 void  CloseDbConnections(void);
 352 int     NumberDbConnectionsInUse(void);
 353
 354 // This routine allows you to query a driver manager
 355 // for a list of available datasources.  Call this routine
 356 // the first time using SQL_FETCH_FIRST.  Continue to call it
 357 // using SQL_FETCH_NEXT until you've exhausted the list.
 358 bool GetDataSource(HENV henv, char *Dsn, SWORD DsnMax, char *DsDesc, SWORD DsDescMax,
 359                                                  UWORD direction = SQL_FETCH_NEXT);
 360
 361 #endif
 362