1 \section{\class{wxDb
}}\label{wxdb
}
3 A wxDb instance is a connection to an ODBC datasource which may
4 be opened, closed, and re-opened an unlimited number of times. A
5 database connection allows function to be performed directly on the
6 datasource, as well as allowing access to any tables/views defined in
7 the datasource to which the user has sufficient privileges.
9 \wxheading{Include files
}
13 \wxheading{Helper classes and data structures
}
15 The following classes and structs are defined in db.cpp/.h for use with the wxDb class.
17 \begin{itemize
}\itemsep=
0pt
18 \item \helpref{wxDbColFor
}{wxdbcolfor
}
19 \item \helpref{wxDbColInf
}{wxdbcolinf
}
20 \item \helpref{wxDbTableInf
}{wxdbtableinf
}
21 \item \helpref{wxDbInf
}{wxdbinf
}
26 NOTE: In a future release, all ODBC class constants will be prefaced with 'wx'
30 wxDB_PATH_MAX Maximum path length allowed to be passed to
31 the ODBC driver to indicate where the data
34 DB_MAX_COLUMN_NAME_LEN Maximum supported length for the name of a
37 DB_MAX_ERROR_HISTORY Maximum number of error messages retained in
38 the queue before being overwritten by new
41 DB_MAX_ERROR_MSG_LEN Maximum supported length of an error message
42 returned by the ODBC classes
44 DB_MAX_STATEMENT_LEN Maximum supported length for a complete SQL
45 statement to be passed to the ODBC driver
47 DB_MAX_TABLE_NAME_LEN Maximum supported length for the name of a
50 DB_MAX_WHERE_CLAUSE_LEN Maximum supported WHERE clause length that
51 can be passed to the ODBC driver
53 DB_TYPE_NAME_LEN Maximum length of the name of a column's
58 \subsection{Enumerated types
}\label{wxdbenumeratedtypes
}
60 \docparam{enum
{\bf wxDbSqlLogState
}}{sqlLogOFF, sqlLogON
}
62 {\it enum
{\bf wxDBMS
}}
64 These are the databases currently tested and working with the ODBC classes. A call to
\helpref{wxDb::Dbms
}{wxdbdbms
} will return one of these enumerated values listed below.
69 dbmsSYBASE_ASA // Adaptive Server Anywhere
70 dbmsSYBASE_ASE // Adaptive Server Enterprise
82 See the remarks in
\helpref{wxDb::Dbms
}{wxdbdbms
} for exceptions/issues with each of these database engines.
84 \wxheading{Public member variables
}
86 \docparam{SWORD
{\bf wxDb::cbErrorMsg
}}{This member variable is populated as a result of calling
\helpref{wxDb::GetNextError
}{wxdbgetnexterror
}. Contains the count of bytes in the wxDb::errorMsg string.
}
88 \docparam{int
{\bf wxDb::DB
\_STATUS}}{The last ODBC error/status that occurred on this data connection. Possible codes are:
}
91 DB_ERR_GENERAL_WARNING // SqlState = '
01000'
92 DB_ERR_DISCONNECT_ERROR // SqlState = '
01002'
93 DB_ERR_DATA_TRUNCATED // SqlState = '
01004'
94 DB_ERR_PRIV_NOT_REVOKED // SqlState = '
01006'
95 DB_ERR_INVALID_CONN_STR_ATTR // SqlState = '
01S00'
96 DB_ERR_ERROR_IN_ROW // SqlState = '
01S01'
97 DB_ERR_OPTION_VALUE_CHANGED // SqlState = '
01S02'
98 DB_ERR_NO_ROWS_UPD_OR_DEL // SqlState = '
01S03'
99 DB_ERR_MULTI_ROWS_UPD_OR_DEL // SqlState = '
01S04'
100 DB_ERR_WRONG_NO_OF_PARAMS // SqlState = '
07001'
101 DB_ERR_DATA_TYPE_ATTR_VIOL // SqlState = '
07006'
102 DB_ERR_UNABLE_TO_CONNECT // SqlState = '
08001'
103 DB_ERR_CONNECTION_IN_USE // SqlState = '
08002'
104 DB_ERR_CONNECTION_NOT_OPEN // SqlState = '
08003'
105 DB_ERR_REJECTED_CONNECTION // SqlState = '
08004'
106 DB_ERR_CONN_FAIL_IN_TRANS // SqlState = '
08007'
107 DB_ERR_COMM_LINK_FAILURE // SqlState = '
08S01'
108 DB_ERR_INSERT_VALUE_LIST_MISMATCH // SqlState = '
21S01'
109 DB_ERR_DERIVED_TABLE_MISMATCH // SqlState = '
21S02'
110 DB_ERR_STRING_RIGHT_TRUNC // SqlState = '
22001'
111 DB_ERR_NUMERIC_VALUE_OUT_OF_RNG // SqlState = '
22003'
112 DB_ERR_ERROR_IN_ASSIGNMENT // SqlState = '
22005'
113 DB_ERR_DATETIME_FLD_OVERFLOW // SqlState = '
22008'
114 DB_ERR_DIVIDE_BY_ZERO // SqlState = '
22012'
115 DB_ERR_STR_DATA_LENGTH_MISMATCH // SqlState = '
22026'
116 DB_ERR_INTEGRITY_CONSTRAINT_VIOL // SqlState = '
23000'
117 DB_ERR_INVALID_CURSOR_STATE // SqlState = '
24000'
118 DB_ERR_INVALID_TRANS_STATE // SqlState = '
25000'
119 DB_ERR_INVALID_AUTH_SPEC // SqlState = '
28000'
120 DB_ERR_INVALID_CURSOR_NAME // SqlState = '
34000'
121 DB_ERR_SYNTAX_ERROR_OR_ACCESS_VIOL // SqlState = '
37000'
122 DB_ERR_DUPLICATE_CURSOR_NAME // SqlState = '
3C000'
123 DB_ERR_SERIALIZATION_FAILURE // SqlState = '
40001'
124 DB_ERR_SYNTAX_ERROR_OR_ACCESS_VIOL2 // SqlState = '
42000'
125 DB_ERR_OPERATION_ABORTED // SqlState = '
70100'
126 DB_ERR_UNSUPPORTED_FUNCTION // SqlState = 'IM001'
127 DB_ERR_NO_DATA_SOURCE // SqlState = 'IM002'
128 DB_ERR_DRIVER_LOAD_ERROR // SqlState = 'IM003'
129 DB_ERR_SQLALLOCENV_FAILED // SqlState = 'IM004'
130 DB_ERR_SQLALLOCCONNECT_FAILED // SqlState = 'IM005'
131 DB_ERR_SQLSETCONNECTOPTION_FAILED // SqlState = 'IM006'
132 DB_ERR_NO_DATA_SOURCE_DLG_PROHIB // SqlState = 'IM007'
133 DB_ERR_DIALOG_FAILED // SqlState = 'IM008'
134 DB_ERR_UNABLE_TO_LOAD_TRANSLATION_DLL // SqlState = 'IM009'
135 DB_ERR_DATA_SOURCE_NAME_TOO_LONG // SqlState = 'IM010'
136 DB_ERR_DRIVER_NAME_TOO_LONG // SqlState = 'IM011'
137 DB_ERR_DRIVER_KEYWORD_SYNTAX_ERROR // SqlState = 'IM012'
138 DB_ERR_TRACE_FILE_ERROR // SqlState = 'IM013'
139 DB_ERR_TABLE_OR_VIEW_ALREADY_EXISTS // SqlState = 'S0001'
140 DB_ERR_TABLE_NOT_FOUND // SqlState = 'S0002'
141 DB_ERR_INDEX_ALREADY_EXISTS // SqlState = 'S0011'
142 DB_ERR_INDEX_NOT_FOUND // SqlState = 'S0012'
143 DB_ERR_COLUMN_ALREADY_EXISTS // SqlState = 'S0021'
144 DB_ERR_COLUMN_NOT_FOUND // SqlState = 'S0022'
145 DB_ERR_NO_DEFAULT_FOR_COLUMN // SqlState = 'S0023'
146 DB_ERR_GENERAL_ERROR // SqlState = 'S1000'
147 DB_ERR_MEMORY_ALLOCATION_FAILURE // SqlState = 'S1001'
148 DB_ERR_INVALID_COLUMN_NUMBER // SqlState = 'S1002'
149 DB_ERR_PROGRAM_TYPE_OUT_OF_RANGE // SqlState = 'S1003'
150 DB_ERR_SQL_DATA_TYPE_OUT_OF_RANGE // SqlState = 'S1004'
151 DB_ERR_OPERATION_CANCELLED // SqlState = 'S1008'
152 DB_ERR_INVALID_ARGUMENT_VALUE // SqlState = 'S1009'
153 DB_ERR_FUNCTION_SEQUENCE_ERROR // SqlState = 'S1010'
154 DB_ERR_OPERATION_INVALID_AT_THIS_TIME // SqlState = 'S1011'
155 DB_ERR_INVALID_TRANS_OPERATION_CODE // SqlState = 'S1012'
156 DB_ERR_NO_CURSOR_NAME_AVAIL // SqlState = 'S1015'
157 DB_ERR_INVALID_STR_OR_BUF_LEN // SqlState = 'S1090'
158 DB_ERR_DESCRIPTOR_TYPE_OUT_OF_RANGE // SqlState = 'S1091'
159 DB_ERR_OPTION_TYPE_OUT_OF_RANGE // SqlState = 'S1092'
160 DB_ERR_INVALID_PARAM_NO // SqlState = 'S1093'
161 DB_ERR_INVALID_SCALE_VALUE // SqlState = 'S1094'
162 DB_ERR_FUNCTION_TYPE_OUT_OF_RANGE // SqlState = 'S1095'
163 DB_ERR_INF_TYPE_OUT_OF_RANGE // SqlState = 'S1096'
164 DB_ERR_COLUMN_TYPE_OUT_OF_RANGE // SqlState = 'S1097'
165 DB_ERR_SCOPE_TYPE_OUT_OF_RANGE // SqlState = 'S1098'
166 DB_ERR_NULLABLE_TYPE_OUT_OF_RANGE // SqlState = 'S1099'
167 DB_ERR_UNIQUENESS_OPTION_TYPE_OUT_OF_RANGE // SqlState = 'S1100'
168 DB_ERR_ACCURACY_OPTION_TYPE_OUT_OF_RANGE // SqlState = 'S1101'
169 DB_ERR_DIRECTION_OPTION_OUT_OF_RANGE // SqlState = 'S1103'
170 DB_ERR_INVALID_PRECISION_VALUE // SqlState = 'S1104'
171 DB_ERR_INVALID_PARAM_TYPE // SqlState = 'S1105'
172 DB_ERR_FETCH_TYPE_OUT_OF_RANGE // SqlState = 'S1106'
173 DB_ERR_ROW_VALUE_OUT_OF_RANGE // SqlState = 'S1107'
174 DB_ERR_CONCURRENCY_OPTION_OUT_OF_RANGE // SqlState = 'S1108'
175 DB_ERR_INVALID_CURSOR_POSITION // SqlState = 'S1109'
176 DB_ERR_INVALID_DRIVER_COMPLETION // SqlState = 'S1110'
177 DB_ERR_INVALID_BOOKMARK_VALUE // SqlState = 'S1111'
178 DB_ERR_DRIVER_NOT_CAPABLE // SqlState = 'S1C00'
179 DB_ERR_TIMEOUT_EXPIRED // SqlState = 'S1T00'
182 \docparam{struct
{\bf wxDb::dbInf
}}{This structure is internal to the wxDb class and contains details of the ODBC datasource that the current instance of the wxDb is connected to in its members. When the datasource is opened, all of the information contained in the dbInf structure is queried from the datasource. This information is used almost exclusively within the ODBC class library. Where there may be a need for particular portions of this information outside of the class library, member functions (e.g. wxDbTable::IsCursorClosedOnCommit()) have been added for ease of use.
}
185 char dbmsName
[40] - Name of the dbms product
186 char dbmsVer
[64] - Version # of the dbms product
187 char driverName
[40] - Driver name
188 char odbcVer
[60] - ODBC version of the driver
189 char drvMgrOdbcVer
[60] - ODBC version of the driver manager
190 char driverVer
[60] - Driver version
191 char serverName
[80] - Server Name, typically a connect string
192 char databaseName
[128] - Database filename
193 char outerJoins
[2] - Does datasource support outer joins
194 char procedureSupport
[2] - Does datasource support stored
196 UWORD maxConnections - Maximum # of connections datasource
198 UWORD maxStmts - Maximum # of HSTMTs per HDBC
199 UWORD apiConfLvl - ODBC API conformance level
200 UWORD cliConfLvl - Is datasource SAG compliant
201 UWORD sqlConfLvl - SQL conformance level
202 UWORD cursorCommitBehavior - How cursors are affected on db commit
203 UWORD cursorRollbackBehavior - How cursors are affected on db
205 UWORD supportNotNullClause - Does datasource support NOT NULL
207 char supportIEF
[2] - Integrity Enhancement Facility (Ref.
209 UDWORD txnIsolation - Transaction isolation level supported by
211 UDWORD txnIsolationOptions - Transaction isolation level options
213 UDWORD fetchDirections - Fetch directions supported
214 UDWORD lockTypes - Lock types supported in SQLSetPos
215 UDWORD posOperations - Position operations supported in
217 UDWORD posStmts - Position statements supported
218 UDWORD scrollConcurrency - Scrollable cursor concurrency options
220 UDWORD scrollOptions - Scrollable cursor options supported
221 UDWORD staticSensitivity - Can additions/deletions/updates be
223 UWORD txnCapable - Indicates if datasource supports
225 UDWORD loginTimeout - Number seconds to wait for a login
229 \docparam{char
{\bf wxDb::errorList
}[DB_MAX_ERROR_HISTORY
][DB_MAX_ERROR_MSG_LEN
]}{The last n ODBC errors that have occurred on this database connection.
}
231 \docparam{char
{\bf wxDb::errorMsg
}[SQL_MAX_MESSAGE_LENGTH
]}{This member variable is populated as a result of calling
\helpref{wxDb::GetNextError
}{wxdbgetnexterror
}. It contains the ODBC error message text.
}
233 \docparam{SDWORD
{\bf wxDb::nativeError
}}{Set by wxDb::DispAllErrors, wxDb::GetNextError, and wxDb::DispNextError. It contains the datasource-specific error code returned by the datasource to the ODBC driver. Used for reporting ODBC errors.
}
235 \docparam{wxChar
{\bf wxDb::sqlState
}[20]}{Set by wxDb::TranslateSqlState(). Indicates the error state after a failed ODBC operation. Used for reporting ODBC errors.
}
239 Default cursor scrolling is defined by wxODBC_FWD_ONLY_CURSORS in setup.h
240 when the wxWindows library is built. This behavior can be overridden when
241 an instance of a wxDb is created (see
\helpref{wxDb constructor
}{wxdbconstr
}).
242 Default setting of this value TRUE, as not all databases/drivers support
243 both types of cursors.
247 \helpref{wxDbColFor
}{wxdbcolfor
},
\helpref{wxDbColInf
}{wxdbcolinf
},
\helpref{wxDbTable
}{wxdbtable
},
\helpref{wxDbTableInf
}{wxdbtableinf
},
\helpref{wxDbInf
}{wxdbinf
}
249 \subsection{Associated non-class functions
}\label{wxdbfunctions
}
251 The following functions are used in conjunction with the wxDb class.
253 \func{wxDb *
}{wxDbGetConnection
}{\param{wxDbConnectInf *
}{pDbConfig
},
\param{bool
}{FwdOnlyCursors=(bool)wxODBC_FWD_ONLY_CURSORS
}}
257 This function is used to request a "new" wxDb instance for use by the program.
258 The wxDb instance returned is also opened (see
\helpref{wxDb::Open
}{wxdbopen
}).
260 This function (along with wxDbFreeConnection() and wxDbCloseConnection())
261 maintain a cached of wxDb instances for user/re-use by a program. When a
262 program needs a wxDb instance, it may call this function to obtain a wxDb
263 instance. If there is a wxDb instance in the cache that is currently unused
264 that matches the connection requirements specified in
{\it'pDbConfig'
} then
265 that cached connection is marked as no longer being free, and a pointer to
266 the wxDb instance is returned.
268 If there are no connections available in the cache that meet the requirements
269 given in
{\it'pDbConfig'
}, then a new wxDb instance is created to connect
270 to the datasource specified in
{\it'pDbConfig'
} using the userID and password
271 given in
{\it'pDbConfig'
}.
273 NOTE: The caching routine also uses the
\helpref{wxDb::Open
}{wxdbopen
}
274 connection datatype copying code. If the call to wxDbGetConnection()
275 requests a connection to a datasource, and there is not one available in the
276 cache, a new connection is created. But when the connection is opened,
277 instead of polling the datasource over again for its datatypes, if a
278 connection to the same datasource (using the same userID/password) has already
279 been done previously, the new connection skips querying the datasource for
280 its datatypes, and uses the same datatypes determined previously by the
281 other connection(s) for that same datasource. This cuts down greatly on
282 network traffic, database load, and connection creation time.
284 When the program is done using a connection created through a call to
285 wxDbGetConnection(), the program shoudl call wxDbFreeConnection() to release
286 the wxDb instance back to the cache. DO NOT DELETE THE wxDb INSTANCE!
287 Deleting the wxDb instance returned can cause a crash/memory corruption
288 later in the program when the cache is cleaned up.
290 When exiting the program, call wxDbCloseConnections() to close all the
291 cached connections created by calls to wxDbGetConnection().
294 \func{bool
}{wxDbFreeConnection
}{\param{wxDb *
}{pDb
}}
298 Searches the list of cached database connections connection for one matching
299 the passed in wxDb instance. If found, that cached connection is freed.
301 Freeing a connection means that it is marked as available (free) in the
302 cahce of connections, so that a call to
\helpref{wxDbGetConnection
}{wxdbfunctions
}
303 is able to return a pointer to the wxDb instance for use. Freeing a
304 connection does NOT close the connection, it only makes the connection
308 \func{void
}{wxDbCloseConnections
}{\void}
312 Closes all cached connections that have been made through use of the
313 \helpref{wxDbGetConnection
}{wxdbfunctions
} function.
315 NOTE: These connections are closed regardless of whether they are in use
316 or not. This function should only be called after the program has
317 finished using the connections and all wxDbTable instances that use any of
318 the connections have been closed.
320 This function performs a
\helpref{wxDb::CommitTrans
}{wxdbcommittrans
}
321 on the connection before closing it to commit any changes tht are still
322 pending, as well as to avoid any function sequence errors upon closing
326 \func{int
}{wxDbConnectionsInUse
}{\void}
330 Returns a count of how many database connections are currently free ( not
331 being used) that have been cached through use of the
\helpref{wxDbGetConnection
}{wxdbfunctions
}
335 \func{bool
}{wxDbSqlLog
}{\param{wxDbSqlLogState
}{state
},
\param{const wxChar *
}{filename = SQL_LOG_FILENAME
}}
339 This function sets the sql log state for all open wxDb objects
342 \func{bool
}{wxDbGetDataSource
}{\param{HENV
}{henv
},
\param{char *
}{Dsn
},
\param{SWORD
}{DsnMax
},
\param{char *
}{DsDesc
},
\param{SWORD
}{DsDescMax
},
\param{UWORD
}{direction = SQL_FETCH_NEXT
}}
346 This routine queries the ODBC driver manager for a list of available
347 datasources. Repeatedly call this function to obtain all the datasources
348 available through the ODBC driver manager on the current workstation.
350 wxStringList strList;
352 while (wxDbGetDataSource(DbConnectInf.Henv, Dsn, SQL_MAX_DSN_LENGTH+
1, DsDesc,
255))
356 \latexignore{\rtfignore{\wxheading{Members
}}}
358 \membersection{wxDb::wxDb
}\label{wxdbconstr
}
364 \func{}{wxDb
}{\param{HENV &
}{aHenv
},
\param{bool
}{FwdOnlyCursors=(bool)wxODBC_FWD_ONLY_CURSORS
}}
366 Constructor, used to create an ODBC connection to a datasource.
368 \wxheading{Parameters
}
370 \docparam{aHenv
}{Environment handle used for this connection.
}
372 \docparam{FwdOnlyCursors
}{Will cursors created for use with this datasource connection only allow forward scrolling cursors.
}
376 This is the constructor for the wxDb class. The wxDb object must
377 be created and opened before any database activity can occur.
382 wxDbConnectInf ConnectInf;
383 ....Set values for member variables of ConnectInf here
385 wxDb sampleDB(ConnectInf.Henv);
386 if (!sampleDB.Open(ConnectInf.Dsn, ConnectInf.Uid, ConnectInf.AuthStr))
388 // Error opening datasource
394 \helpref{wxDbGetConnection
}{wxdbfunctions
},
397 \membersection{wxDb::Catalog
}\label{wxdbcatalog
}
399 \func{bool
}{Catalog
}{\param{char *
}{ userID
},
\param{char *
}{fileName = SQL_CATALOG_FILENAME
}}
401 Allows a data "dictionary" of the datasource to be created, dumping pertinent information about all data tables to which the user specified in userID has access.
403 \wxheading{Parameters
}
405 \docparam{userID
}{Database user name to use in accessing the database. All tables to which this user has rights will be evaluated in the catalog.
}
407 \docparam{fileName
}{{\it OPTIONAL
}. Name of the text file to create and write
408 the DB catalog to. Default is SQL_CATALOG_FILENAME.
}
410 \wxheading{Return value
}
412 Returns TRUE if the catalog request was successful, or FALSE if there was some
413 reason that the catalog could not be generated.
418 ============== ============== ================ ========= =======
419 TABLE NAME COLUMN NAME DATA TYPE PRECISION LENGTH
420 ============== ============== ================ ========= =======
421 EMPLOYEE RECID (
0008)NUMBER
15 8
422 EMPLOYEE USER_ID (
0012)VARCHAR2
13 13
423 EMPLOYEE FULL_NAME (
0012)VARCHAR2
26 26
424 EMPLOYEE PASSWORD (
0012)VARCHAR2
26 26
425 EMPLOYEE START_DATE (
0011)DATE
19 16
429 \membersection{wxDb::Close
}\label{wxdbclose
}
431 \func{void
}{Close
}{\void}
433 Closes the database connection.
437 At the end of your program, when you have finished all of your database work, you must close the ODBC connection to the datasource. There are actually four steps involved in doing this as illustrated in the example.
439 Any wxDbTable instances which use this connection must be deleted before closing the database connection.
444 // Commit any open transactions on the datasource
445 sampleDB.CommitTrans();
447 // Delete any remaining wxDbTable objects allocated with new
450 // Close the wxDb connection when finished with it
453 // Free Environment Handle that ODBC uses
455 // Be certain that no wxDb instances still exist
456 // that were created using this handle! If you
457 // use wxDbGetConnection() to get all the wxDb
458 // instances, you can use wxDbConnectionsInUse()
459 // to find out if any connections have not been
460 // closed before calling this
461 if (SQLFreeEnv(Db.Henv) != SQL_SUCCESS)
463 // Error freeing environment handle
468 \membersection{wxDb::CommitTrans
}\label{wxdbcommittrans
}
470 \func{bool
}{CommitTrans
}{\void}
472 Permanently "commits" changes (insertions/deletions/updates) to the database.
474 \wxheading{Return value
}
476 Returns TRUE if the commit was successful, or FALSE if the commit failed.
480 Transactions begin implicitly as soon as you make a change to the database
481 with an insert/update/delete, or any other direct SQL command that performs
482 one of these operations against the datasource.
483 At any time thereafter, to save the changes to disk permanently, "commit"
484 them by calling this function.
486 Calling this member function commits ALL open transactions on this ODBC
487 connection. For example, if three different wxDbTable instances used the
488 same connection to the datasource, commiting changes made on one of those
489 wxDbTable instances commits any pending transactions on all three wxDbTable
492 Until a call to wxDb::CommitTrans() is made, no other user or cursor is able
493 to see any changes made to the row(s) that have been inserted/modifed/deleted.
496 \wxheading{Special Note :
{\it Cursors
} }
498 \normalbox{It is important to understand that different database/ODBC driver combinations handle
499 transactions differently. One thing in particular that you must pay attention to is
500 cursors, in regard to transactions. Cursors are what allow you to scroll through
501 records forward and backward and to manipulate records as you scroll through them.
502 When you issue a query, a cursor is created behind the scenes. The cursor keeps track
503 of the query and keeps track of the current record pointer. After you commit or
504 rollback a transaction, the cursor may be closed automatically. This is database
505 dependent, and with some databases this behavior can be controlled through
506 management functions. This means you would need to
507 requery the datasource before you can perform any additional work using this
508 cursor. This is only necessary however if the datasource closes the cursor after a
509 commit or rollback. Use the wxDbTable::IsCursorClosedOnCommit() member function to
510 determine the datasource's transaction behavior. Note, in many situations
511 it is very inefficient to assume the cursor is closed and always requery. This could put
512 a significant, unnecessary load on datasources that leave the cursors open after a
516 \membersection{wxDb::CreateView
}\label{wxdbcreateviews
}
518 \func{bool
}{CreateView
}{\param{char *
}{ viewName
},
\param{char *
}{ colList
},
\param{char *
}{pSqlStmt
}}
520 Creates a SQL VIEW of one or more tables in a single datasource. Note that
521 this function will only work against databases which support views (currently
522 only Oracle as of November
21 2000).
524 \wxheading{Parameters
}
526 \docparam{viewName
}{The name of the view. e.g. PARTS_V
}
528 \docparam{colList
}{{\it OPTIONAL
} Pass in a comma delimited list of column
529 names if you wish to explicitly name each column in the result set. If not
530 desired, pass in an empty string and the column names from the associated
531 table(s) will be used.
}
533 \docparam{pSqlStmt
}{Pointer to the select statement portion of the CREATE VIEW statement. Must be a complete, valid SQL SELECT statement.
}
537 A 'view' is a logical table that derives columns from one or more other tables or views. Once the view is created, it can be queried exactly like any other table in the database.
539 NOTE: Views are not available with all datasources. Oracle is one example of a datasouce which does support views.
544 // Incomplete code sample
545 db.CreateView("PARTS_SD1", "PN, PD, QTY",
546 "SELECT PART_NO, PART_DESC, QTY_ON_HAND *
1.1 FROM PARTS \
547 WHERE STORAGE_DEVICE =
1");
549 // PARTS_SD1 can now be queried just as if it were a data table.
550 // e.g. SELECT PN, PD, QTY FROM PARTS_SD1
554 \membersection{wxDb::Dbms
}\label{wxdbdbms
}
556 \func{wxDBMS
}{Dbms
}{\void}
560 The return value will be of the enumerated type wxDBMS. This enumerated
561 type contains a list of all the currently tested and supported databases.
563 Additional databases may work with these classes, but the databases
564 returned by this function have been tested and confirmed to work with
567 Possible values returned by this function can be viewed in the
568 \helpref{Enumerated types
}{wxdbenumeratedtypes
} section of wxDb.
570 There are known issues with conformance to the ODBC standards with several
571 datasources supported by the wxWindows ODBC classes. Please see the overview
572 for specific details on which datasource have which issues.
574 \wxheading{Return value
}
576 The return value will indicate which of the supported datasources is
577 currently connected to by this connection. In the event that the
578 datasource is not recognized, a value of 'dbmsUNIDENTIFIED' is returned.
581 \membersection{wxDb::DispAllErrors
}\label{wxdbdispallerrors
}
583 \func{bool
}{DispAllErrors
}{\param{HENV
}{ aHenv
},
{\param}{HDBC
}{ aHdbc = SQL_NULL_HDBC
},
{\param}{HSTMT
}{ aHstmt = SQL_NULL_HSTMT
}}
585 Used to log all database errors that occurred as a result of an executed
586 database command. This logging is automatic and also includes debug logging
587 when compiled in debug mode via
\helpref{wxLogDebug
}{wxlogdebug
}. If logging
588 is turned on via
\helpref{wxDb::SetSqlLogging
}{wxdbsetsqllogging
}, then an
589 entry is also logged to the defined log file.
591 \wxheading{Parameters
}
593 \docparam{aHenv
}{Handle to the ODBC environment.
}
595 \docparam{aHdbc
}{Handle to the ODBC connection. Pass this in if the ODBC function call that erred required a hdbc or hstmt argument.
}
597 \docparam{AHstmt
}{Handle to the ODBC statement being executed against. Pass this in if the ODBC function call that erred out required a hstmt argument.
}
601 This member function will log all of the ODBC error messages for the last
602 ODBC function call that was made. This function is normally used internally
603 within the ODBC class library, but can be used programmatically after calling
604 ODBC functions directly (i.e. SQLFreeEnv()).
606 \wxheading{Return value
}
608 The function always returns FALSE, so a call to this function can be made
609 in the return statement of a code block in the event of a failure to
610 perform an action (see the example below).
614 \helpref{wxDb::SetSqlLogging
}{wxdbsetsqllogging
}, wxDbSqlLog
619 if (SQLExecDirect(hstmt, (UCHAR FAR *) pSqlStmt, SQL_NTS) != SQL_SUCCESS)
620 // Display all ODBC errors for this stmt
621 return(db.DispAllErrors(db.henv, db.hdbc, hstmt));
625 \membersection{wxDb::DispNextError
}\label{wxdbdispnexterror
}
627 \func{void
}{DispNextError
}{\void}
631 This function is normally used internally within the ODBC class library.
632 It could be used programmatically after calling ODBC functions directly. This
633 function works in conjunction with
\helpref{wxDb::GetNextError
}{wxdbgetnexterror
} when errors (or
634 sometimes informational messages) returned from ODBC need to be analyzed
635 rather than simply displaying them as an error. GetNextError() retrieves
636 the next ODBC error from the ODBC error queue. The wxDb member variables
637 "sqlState", "nativeError" and "errorMsg" could then be evaluated. To
638 display the error retrieved, DispNextError() could then be called.
639 The combination of GetNextError() and DispNextError() can be used to
640 iteratively step through the errors returned from ODBC evaluating each
641 one in context and displaying the ones you choose.
646 // Drop the table before attempting to create it
647 sprintf(sqlStmt, "DROP TABLE
%s", tableName);
648 // Execute the drop table statement
649 if (SQLExecDirect(hstmt,(UCHAR FAR *)sqlStmt,SQL_NTS) != SQL_SUCCESS)
651 // Check for sqlState = S0002, "Table or view not found".
652 // Ignore this error, bomb out on any other error.
653 pDb->GetNextError(henv, hdbc, hstmt);
654 if (strcmp(pDb->sqlState, "S0002"))
656 pDb->DispNextError(); // Displayed error retrieved
657 pDb->DispAllErrors(henv, hdbc, hstmt); // Display all other errors, if any
658 pDb->RollbackTrans(); // Rollback the transaction
659 CloseCursor(); // Close the cursor
660 return(FALSE); // Return Failure
666 \membersection{wxDb::DropView
}\label{wxdbdropview
}
668 \func{bool
}{DropView
}{\param{const char *
}{viewName
}}
670 Drops the data table view named in 'viewName'.
672 \wxheading{Parameters
}
674 \docparam{viewName
}{Name of the view to be dropped.
}
678 If the view does not exist, this function will return TRUE. Note that views are not supported with all datasources.
680 \membersection{wxDb::ExecSql
}\label{wxdbexecsql
}
682 \func{bool
}{ExecSql
}{\param{char *
}{pSqlStmt
}}
684 Allows a native SQL command to be executed directly against the datasource. In addition to being able to run any standard SQL command, use of this function allows a user to (potentially) utilize features specific to the datasource they are connected to that may not be available through ODBC. The ODBC driver will pass the specified command directly to the datasource.
686 \wxheading{Parameters
}
688 \docparam{pSqlStmt
}{Pointer to the SQL statement to be executed.
}
692 This member extends the wxDb class and allows you to build and execute ANY VALID
693 SQL statement against the datasource. This allows you to extend the class
694 library by being able to issue any SQL statement that the datasource is capable
699 \helpref{wxDb::GetData
}{wxdbgetdata
},
\helpref{wxDb::GetNext
}{wxdbgetnext
}
702 \membersection{wxDb::GetCatalog
}\label{wxdbgetcatalog
}
704 \func{wxDbInf *
}{GetCatalog
}{\param{char *
}{userID
}}
706 Returns a
\helpref{wxDbInf
}{wxdbinf
} pointer that points to the catalog
707 (datasource) name, schema, number of tables accessible to the current user,
708 and a wxDbTableInf pointer to all data pertaining to all tables in the users
711 \wxheading{Parameters
}
713 \docparam{userID
}{Owner/Schema of the table. Specify a userID when the datasource you are connected to allows multiple unique tables with the same name to be owned by different users.
{\it userID
} is evaluated as follows:
}
716 userID == NULL ... UserID is ignored (DEFAULT)
717 userID == "" ... UserID set equal to 'this->uid'
718 userID != "" ... UserID set equal to 'userID'
723 The returned catalog will only contain catalog entries for tables to which the user specified in 'userID' has sufficient privileges. If no user is specified (NULL passed in), a catalog pertaining to all tables in the datasource accessible to the connected user (permissions apply) via this connection will be returned.
725 \membersection{wxDb::GetColumnCount
}\label{wxdbgetcolumncount
}
727 \func{int
}{GetColumnCount
}{\param{char *
}{tableName
},
\param{const char *
}{userID
}}
729 \wxheading{Parameters
}
731 \docparam{tableName
}{The table name you wish to obtain column information about.
}
733 \docparam{userID
}{Name of the user that owns the table(s) (also referred to as schema). Required for some datasources for situations where there may be multiple tables with the same name in the datasource, but owned by different users.
{\it userID
} is evaluated in the following manner:
}
736 userID == NULL ... UserID is ignored (DEFAULT)
737 userID == "" ... UserID set equal to 'this->uid'
738 userID != "" ... UserID set equal to 'userID'
741 \wxheading{Return value
}
743 Returns a count of how many columns are in the specified table. If an error
744 occurs retrieving the number of columns, this function will return a -
1.
746 \membersection{wxDb::GetColumns
}\label{wxdbgetcolumns
}
748 \func{wxDbColInf *
}{GetColumns
}{\param{char *
}{tableName
},
\param{int *
}{numCols
},
\param{const char *
}{userID=NULL
}}
750 \func{wxDbColInf *
}{GetColumns
}{\param{char *
}{tableName
[]},
\param{const char *
}{userID
}}
752 \wxheading{Parameters
}
754 \docparam{tableName
}{The table name you wish to obtain column information about.
}
755 \docparam{numCols
}{Pointer to an integer which will hold a count of the number of columns returned by this function
}
756 \docparam{tableName
[]}{An array of pointers to table names you wish to obtain column information about. The last element of this array must be a NULL string.
}
757 \docparam{userID
}{Name of the user that owns the table(s) (also referred to as schema). Required for some datasources for situations where there may be multiple tables with the same name in the datasource, but owned by different users.
{\it userID
} is evaluated in the following manner:
}
760 userID == NULL ... UserID is ignored (DEFAULT)
761 userID == "" ... UserID set equal to 'this->uid'
762 userID != "" ... UserID set equal to 'userID'
765 \wxheading{Return value
}
767 This function returns a pointer to an array of
\helpref{wxDbColInf
}{wxdbcolinf
}
768 structures, allowing you to obtain information regarding the columns of the
769 named table(s). If no columns were found, or an error occurred, this pointer
772 THE CALLING FUNCTION IS RESPONSIBLE FOR DELETING THE
{\it wxDbColInf
} MEMORY WHEN IT IS
775 \normalbox{ALL column bindings associated with this wxDb instance are unbound
776 by this function, including those used by any wxDbTable instances that use
777 this wxDb instance. This function should use its own wxDb instance
778 to avoid undesired unbinding of columns.
}
782 \helpref{wxDbColInf
}{wxdbcolinf
}
787 char *tableList
[] =
{"PARTS",
0};
788 wxDbColInf *colInf = pDb->GetColumns(tableList);
791 // Use the column inf
793 // Destroy the memory
799 \membersection{wxDb::GetData
}\label{wxdbgetdata
}
801 \func{bool
}{GetData
}{\param{UWORD
}{ colNo
},
\param{SWORD
}{ cType
},
\param{PTR
}{ pData
},
\param{SDWORD
}{ maxLen
},
\param{SDWORD FAR *
}{ cbReturned
} }
803 Used to retrieve result set data without binding column values to memory variables (i.e. not using a wxDbTable instance to access table data).
805 \wxheading{Parameters
}
807 \docparam{colNo
}{Ordinal number of the desired column in the result set to be returned.
}
808 \docparam{cType
}{The C data type that is to be returned. See a partial list in
\helpref{wxDbTable::SetColDefs
}{wxdbtablesetcoldefs
}}
809 \docparam{pData
}{Memory buffer which will hold the data returned by the call to this function.
}
810 \docparam{maxLen
}{Maximum size of the buffer
{\it 'pData'
} in characters. NOTE: Not UNICODE safe. If this is a numeric field, a value of
0 may be passed for this parameter, as the API knows the size of the expected return value.
}
811 \docparam{cbReturned
}{Pointer to the buffer containing the length of the actual data returned. If this value comes back as SQL_NULL_DATA, then the wxDb::GetData() call has failed.
}
815 \helpref{wxDb::GetNext
}{wxdbgetnext
},
\helpref{wxDb::ExecSql
}{wxdbexecsql
}
823 sqlStmt = "SELECT SUM(REQUIRED_QTY - PICKED_QTY) FROM ORDER_TABLE WHERE \
824 PART_RECID =
1450 AND REQUIRED_QTY > PICKED_QTY";
827 if (!pDb->ExecSql(sqlStmt.c_str()))
833 // Request the first row of the result set
840 // Read column
#1 of the row returned by the call to ::GetNext()
841 // and return the value in 'reqQty'
842 if (!pDb->GetData(
1, SQL_C_ULONG, &reqQty,
0, &cb))
848 // Check for a NULL result
849 if (cb == SQL_NULL_DATA)
855 When requesting multiple columns to be returned from the result set (for example, the SQL query
856 requested
3 columns be returned), the calls to this function must request the columns in ordinal
857 sequence (
1,
2,
3 or
1,
3 or
2,
3).
859 \membersection{wxDb::GetDatabaseName
}\label{wxdbgetdatabasename
}
861 \func{const char *
}{GetDatabaseName
}{\void}
863 Returns the name of the database engine.
865 \membersection{wxDb::GetDatasourceName
}\label{wxdbgetdatasourcename
}
867 \func{const char *
}{GetDatasourceName
}{\void}
869 Returns the ODBC datasource name.
871 \membersection{wxDb::GetHDBC
}\label{wxdbgethdbc
}
873 \func{HDBC
}{GetHDBC
}{\void}
875 Returns the ODBC handle to the database connection.
877 \membersection{wxDb::GetHENV
}\label{wxdbgethenv
}
879 \func{HENV
}{GetHENV
}{\void}
881 Returns the ODBC environment handle.
883 \membersection{wxDb::GetHSTMT
}\label{wxdbgethstmt
}
885 \func{HSTMT
}{GetHSTMT
}{\void}
887 Returns the ODBC statement handle associated with this database connection.
889 \membersection{wxDb::GetKeyFields
}\label{wxdbgetkeyfields
}
891 \func{int
}{GetKeyFields
}{\param{char *
}{tableName
},
\param{wxDbColInf *
}{colInf
},
\param{int
}{nocols
}}
893 Used to determine which columns are members of primary or non-primary indexes on the specified table. If a column is a member of a foreign key for some other table, that information is detected also.
895 This function is primarily for use by the
\helpref{wxDb::GetColumns
}{wxdbgetcolumns
} function, but may be called if desired from the client application.
897 \wxheading{Parameters
}
899 \docparam{tableName
}{Name of the table for which the columns will be evaluated as to their inclusion in any indexes.
}
900 \docparam{colInf
}{Data structure containing the column definitions (obtained with
\helpref{wxDb::GetColumns
}{wxdbgetcolumns
}). This function populates the PkCol, PkTableName, and FkTableName members of the colInf structure.
}
901 \docparam{nocols
}{Number of columns defined in the instance of colInf.
}
903 \wxheading{Return value
}
905 Currently always returns TRUE.
909 \helpref{wxDbColInf
}{wxdbcolinf
},
\helpref{wxDb::GetColumns
}{wxdbgetcolumns
}
911 \membersection{wxDb::GetNext
}\label{wxdbgetnext
}
913 \func{bool
}{GetNext
}{\void}
915 Called after executing a query, this function requests the next row
916 in the result set after the current position of the cursor.
920 \helpref{wxDb::ExecSql
}{wxdbexecsql
},
\helpref{wxDb::GetData
}{wxdbgetdata
}
922 \membersection{wxDb::GetNextError
}\label{wxdbgetnexterror
}
924 \func{bool
}{GetNextError
}{\param{HENV
}{ aHenv
},
\param{HDBC
}{ aHdbc = SQL_NULL_HDBC
},
\param{HSTMT
}{ aHstmt = SQL_NULL_HSTMT
}}
926 \wxheading{Parameters
}
928 \docparam{aHenv
}{A handle to the ODBC environment.
}
929 \docparam{aHdbc
}{{\it OPTIONAL.
} A handle to the ODBC connection. Pass this in if the ODBC function call that
930 erred out required a hdbc or hstmt argument.
}
931 \docparam{AHstmt
}{{\it OPTIONAL.
}A handle to the ODBC statement being executed against. Pass this in if the
932 ODBC function call that erred out requires a hstmt argument.
}
936 \helpref{wxDb::DispNextError
}{wxdbdispnexterror
},
\helpref{wxDb::DispAllErrors
}{wxdbdispallerrors
}
941 if (SQLExecDirect(hstmt, (UCHAR FAR *) pSqlStmt, SQL_NTS) != SQL_SUCCESS)
943 // Display all ODBC errors for this stmt
944 return(db.DispAllErrors(db.henv, db.hdbc, hstmt));
949 \membersection{wxDb::GetPassword
}\label{wxdbgetpassword
}
951 \func{const char *
}{GetPassword
}{\void}
953 Returns the password used to establish this connection to the datasource.
956 \membersection{wxDb::GetTableCount
}\label{wxdbgettablecount
}
958 \func{int
}{GetTableCount
}{\void}
960 Returns the number of wxDbTable() instances currently using this datasource connection.
963 \membersection{wxDb::GetUsername
}\label{wxdbgetusername
}
965 \func{const char *
}{GetUsername
}{\void}
967 Returns the user name (uid) used to establish this connection to the datasource.
970 \membersection{wxDb::Grant
}\label{wxdbgrant
}
972 \func{bool
}{Grant
}{\param{int
}{privileges
},
\param{char *
}{tableName
},
\param{char *
}{userList = "PUBLIC"
}}
974 Use this member function to GRANT privileges to users for accessing tables in the datasource.
976 \wxheading{Parameters
}
978 \docparam{privileges
}{Use this argument to select which privileges you want to grant. Pass DB_GRANT_ALL to grant all privileges. To grant individual privileges pass one or more of the following OR'd together:
}
985 DB_GRANT_ALL = DB_GRANT_SELECT | DB_GRANT_INSERT |
986 DB_GRANT_UPDATE | DB_GRANT_DELETE
989 \docparam{tableName
}{The name of the table you wish to grant privileges on.
}
990 \docparam{userList
}{{\it OPTIONAL.
} A comma delimited list of users to grant the privileges to. If this argument is not passed in, the privileges will be given to the general PUBLIC.
}
994 Some databases require user names to be specified in all capital letters (i.e. Oracle). This function does not automatically capitalize the user names passed in the comma-separated list. This is the responsibility of the calling routine.
996 The currently logged in user must have sufficient grantor privileges for this
997 function to be able to successfully grant the indicated privileges.
1002 db.Grant(DB_GRANT_SELECT | DB_GRANT_INSERT, "PARTS", "mary, sue");
1006 \membersection{wxDb::IsFwdOnlyCursors
}\label{wxdbisfwdonlycursors
}
1008 \func{bool
}{IsFwdOnlyCursors
}{\void}
1010 This setting indicates whether this database connection was created
1011 as being capable of using only forward scrolling cursors.
1013 This function does NOT indicate if the ODBC driver or datasource supports
1014 backward scrolling cursors. There is no standard way of detecting if the
1015 driver or datasource can support backward scrolling cursors.
1017 If a wxDb instance was created as being capable of only forward scrolling
1018 cursors, then even if the datasource and ODBC driver support backward
1019 scrolling cursors, tables using this database connection would only be able
1020 to use forward scrolling cursors.
1022 The default setting of whether a wxDb connection to a database allows
1023 forward-only or also backward scrolling cursors is defined in setup.h by the
1024 value of wxODBC_FWD_ONLY_CURSORS. This default setting can be overridden
1025 when the wxDb connection is initially created (see
1026 \helpref{wxDb constructor
}{wxdbconstr
} and
\helpref{wxDbGetConnection
}{wxdbfunctions
}).
1028 \wxheading{Return value
}
1030 Returns TRUE if this datasource connection is defined as using only forward
1031 scrolling cursors, or FALSE if the connection is defined as being allowed to
1032 use backward scrolling cursors and their associated functions (see note above).
1036 Added as of wxWindows v2.4 release, this function is a renamed version of
1037 wxDb::FwdOnlyCursors() to match the normal wxWindows naming conventions for
1038 class member functions.
1040 This function is not available in versions prior to v2.4. You should
1041 use
\helpref{wxDb::FwdOnlyCursors
}{wxdbfwdonlycursors
} for wxWindows
1042 versions prior to
2.4.
1044 \wxheading{See also
}
1046 \helpref{wxDb constructor
}{wxdbconstr
},
\helpref{wxDbGetConnection
}{wxdbfunctions
}
1049 \membersection{wxDb::IsOpen
}\label{wxdbisopen
}
1051 \func{bool
}{IsOpen
}{\void}
1053 Indicates whether the database connection to the datasource is currently
1058 This function may indicate that the database connection is open, even if
1059 the call to
\helpref{wxDb::Open
}{wxdbopen
} may have failed to fully
1060 initialize the connection correctly. The connection to the database
1061 {\it is
} open and can be used via the direct SQL commands, if this
1062 function returns TRUE. Other functions which depend on the
1063 \helpref{wxDb::Open
}{wxdbopen
} to have completed correctly may not function
1064 as expected. The return result from
\helpref{wxDb::Open
}{wxdbopen
} is the
1065 only way to know if complete initialization of this wxDb connection was
1066 successful or not. See
\helpref{wxDb::Open
}{wxdbopen
} for more details on
1067 partial failures to open a connection instance.
1070 \membersection{wxDb::LogError
}\label{wxdblogerror
}
1072 \func{void
}{LogError
}{\param{const char *
}{errMsg
} \param{const char *
}{SQLState=NULL
}}
1074 \docparam{errMsg
}{Free-form text to display describing the error/text to be logged.
}
1075 \docparam{SQLState
}{{\it OPTIONAL.
} Native SQL state error. Default is
0.
}
1079 Calling this function will enter a log message in the error list maintained
1080 for the database connection. This log message is free form and can be
1081 anything the programmer wants to enter in the error list.
1083 If SQL logging is turned on, the call to this function will also log the
1084 text into the SQL log file.
1086 \wxheading{See also
}
1088 \helpref{wxDb::WriteSqlLog
}{wxdbwritesqllog
}
1091 \membersection{wxDb::Open
}\label{wxdbopen
}
1093 \func{bool
}{Open
}{\param{char *
}{Dsn
},
\param{char *
}{Uid
},
\param{char *
}{AuthStr
}}
1095 \func{bool
}{Open
}{\param{wxDb *
}{copyDb
}}
1097 Opens a connection to the datasource, sets certain behaviors of the datasource
1098 to confirm to the accepted behaviors (e.g. cursor position maintained on
1099 commits), and queries the datasource for its representations of the basic
1100 datatypes to determine the form in which the data going to/from columns in
1101 the data tables are to be handled.
1103 The second form of this function, which accepts a "wxDb *" as a parameter,
1104 can be used to avoid the overhead (execution time, database load, network
1105 traffic) which are needed to determine the data types and representations
1106 of data that are necessary for cross-datasource support by these classes.
1108 Normally the first form of the wxDb::Open() function will open the connection
1109 and then send a series of queries to the datasource asking it for its
1110 representation of data types, and all the features it supports. If one
1111 connection to the datasource has already been made previously, the information
1112 gathered when that connection was created can just be copied to any new
1113 connections to the same datasource by passing a pointer to the first
1114 connection in as a parameter to the wxDb::Open() function. Note that this
1115 new connection created from the first connections information will use the
1116 same Dsn/Uid/AuthStr as the first connection used.
1118 \wxheading{Parameters
}
1120 \docparam{Dsn
}{datasource name. The name of the ODBC datasource as
1121 assigned when the datasource is initially set up through the ODBC data
1123 \docparam{Uid
}{User ID. The name (ID) of the user you wish to connect as
1124 to the datasource. The user name (ID) determines what objects you
1125 have access to in the datasource and what datasource privileges you have.
1126 Privileges include being able to create new objects, update objects, delete
1127 objects and so on. Users and privileges are normally administered by the
1128 database administrator.
}
1129 \docparam{AuthStr
}{The password associated with the Uid.
}
1130 \docparam{copyDb
}{Already completely configured and opened datasource connection
1131 from which all Dsn, Uid, AuthStr, and data typing information is to be copied
1132 from for use by this datasource connection.
}
1136 After a wxDb instance is created, it must then be opened. When opening a
1137 datasource, there must be three pieces of information passed. The data
1138 source name, user name (ID) and the password for the user. No database
1139 activity on the datasource can be performed until the connection is opened.
1140 This is normally done at program startup and the datasource remains
1141 open for the duration of the program/module run.
1143 It is possible to have connections to multiple datasources open at the same
1144 time to support distributed database connections by having separate instances
1145 of wxDb objects that use either the same or different Dsn/Uid/AuthStr settings.
1147 If this function returns a value of FALSE, it does not necessarily mean that
1148 the connection to the datasource was not opened. It may mean that some
1149 portion of the initialization of the connection failed (such as a datatype not
1150 being able to be determined how the datasource represents it). To determine
1151 if the connection to the database failed, use the
\helpref{wxDb::IsOpen
}{wxdbisopen
}
1152 function after receiving a FALSE result back from this function to determine if
1153 the connection was opened or not. If this function returns FALSE, but
\helpref{wxDb::IsOpen
}{wxdbisopen
}
1154 returns TRUE, then direct SQL commands may be passed to the database
1155 connection and can be successfully executed, but use of the datatypes (such as
1156 by a wxDbTable instance) that are normally determined during open will not be
1159 \normalbox{The
{\it Dsn
},
{\it Uid
}, and
{\it AuthStr
} string pointers that are passed in
1160 are copied. NOT the strings themselves, only the pointers. The calling routine
1161 must maintain the memory for these three strings for the life of the wxDb instance.
}
1166 wxDb sampleDB(Db.Henv);
1167 if (!sampleDB.Open("Oracle
7.1 HP/UX", "gtasker", "myPassword"))
1169 if (sampleDb.IsOpen())
1171 // Connection is open, but the initialization of
1172 // datatypes and parameter settings failed
1176 // Error opening datasource
1182 \membersection{wxDb::RollbackTrans
}\label{wxdbrollbacktrans
}
1184 \func{bool
}{RollbackTrans
}{\void}
1186 Function to "undo" changes made to the database. After an insert/update/delete, the operation may be "undone" by issuing this command any time before a
\helpref{wxDb::CommitTrans
}{wxdbcommittrans
} is called on the database connection.
1190 Transactions begin implicitly as soon as you make a change to the database. The
1191 transaction continues until either a commit or rollback is executed. Calling
1192 wxDb::RollbackTrans() will result in ALL changes done using this database
1193 connection that have not already been committed to be "undone" back to the last
1194 commit/rollback that was successfully executed.
1196 \normalbox{Calling this member function rolls back ALL open (uncommitted)
1197 transactions on this ODBC connection, including all wxDbTable instances that
1198 use this connection.
}
1200 \wxheading{See also
}
1202 \helpref{wxDb::CommitTrans
}{wxdbcommittrans
} for a special note on cursors
1205 \membersection{wxDb::SetDebugErrorMessages
}\label{wxdbsetdebugerrormessages
}
1207 \func{void
}{SetDebugErrorMessages
}{\param{bool
}{state
}}
1209 \docparam{state
}{Either TRUE (debug messages are logged) or FALSE (debug
1210 messages are not logged.
}
1214 Turns on/off debug error messages from the ODBC class library. When
1215 this function is passed TRUE, errors are reported to the user/logged automatically
1216 in a text or pop-up dialog when an ODBC error occurs. When passed FALSE,
1217 errors are silently handled.
1219 When compiled in release mode (FINAL=
1), this setting has no affect.
1221 \wxheading{See also
}
1223 \helpref{wxDb constructor
}{wxdbconstr
}
1226 \membersection{wxDb::SetSqlLogging
}\label{wxdbsetsqllogging
}
1228 \func{bool
}{SetSqlLogging
}{\param{wxDbSqlLogState
}{ state
},
\param{const wxChar *
}{filename = SQL_LOG_FILENAME
},
\param{bool
}{ append = FALSE
}}
1230 \wxheading{Parameters
}
1232 \docparam{state
}{Either sqlLogOFF or sqlLogON (see
\helpref{enum wxDbSqlLogState
}{wxdbcolfor
}). Turns logging of SQL commands sent to the datasource OFF or ON.
}
1233 \docparam{filename
}{{\it OPTIONAL
}. Name of the file to which the log text is to be written. Default is SQL_LOG_FILENAME.
}
1234 \docparam{append
}{{\it OPTIONAL
}. Whether the file is appended to or overwritten. Default is FALSE.
}
1238 When called with
{\it sqlLogON
}, all commands sent to the datasource engine are logged to the file specified by
{\it filename
}. Logging is done by embedded riteSqlLog() calls in the database member functions, or may be manually logged by adding calls to
\helpref{wxDb::WriteSqlLog
}{wxdbwritesqllog
} in your own source code.
1240 When called with
{\it sqlLogOFF
}, the logging file is closed, and any calls to
\helpref{wxDb::WriteSqlLog
}{wxdbwritesqllog
} are ignored.
1243 \membersection{wxDb::TableExists
}\label{wxdbtablexists
}
1245 \func{bool
}{TableExists
}{\param{const char *
}{tableName
},
\param{const char *
}{userID=NULL
},
\param{const char *
}{path=NULL
}}
1247 Checks the ODBC datasource for the existence of a table. If a
{\it userID
}
1248 is specified, then the table must be accessible by that user (user must have
1249 at least minimal privileges to the table).
1251 \wxheading{Parameters
}
1253 \docparam{tableName
}{Name of the table to check for the existence of.
}
1254 \docparam{userID
}{Owner of the table (also referred to as schema). Specify a userID when the datasource you are connected
1255 to allows multiple unique tables with the same name to be owned by different users.
{\it userID
}
1256 is evaluated as follows:
}
1259 userID == NULL ... UserID is ignored (DEFAULT)
1260 userID == "" ... UserID set equal to 'this->uid'
1261 userID != "" ... UserID set equal to 'userID'
1266 {\it tableName
} may refer to a table, view, alias or synonym.
1268 This function does not indicate whether or not the user has privileges to query or perform other functions on the table. Use the
\helpref{wxDb::TablePrivileges
}{wxdbtableprivileges
} to determine if the user has sufficient privileges or not.
1270 \wxheading{See also
}
1272 \helpref{wxDb::TablePrivileges
}{wxdbtableprivileges
}
1275 \membersection{wxDb::TablePrivileges
}\label{wxdbtableprivileges
}
1277 \func{bool
}{TablePrivileges
}{\param{const char *
}{tableName
},
\param{const char *
}{priv
},
\param{const char *
}{userID=""
},
\param{const char *
}{schema=NULL
},
\param{const char *
}{path=""
}}
1279 Checks the ODBC datasource for the existence of a table. If a
{\it userID
}
1280 is specified, then the table must be accessible by that user (user must have
1281 at least minimal privileges to the table).
1283 \wxheading{Parameters
}
1285 \docparam{tableName
}{Name of the table on which to check privileges.
1286 {\it tableName
} may refer to a table, view, alias or synonym.
}
1287 \docparam{priv
}{The table privilege being evaluated. May be one of the
1288 following (or a datasource specific privilege):
}
1291 SELECT : The connected user is permitted to retrieve data for
1292 one or more columns of the table.
1294 INSERT : The connected user is permitted to insert new rows
1295 containing data for one or more columns into the
1298 UPDATE : The connected user is permitted to update the data in
1299 one or more columns of the table.
1301 DELETE : The connected user is permitted to delete rows of
1302 data from the table.
1304 REFERENCES : Is the connected user permitted to refer to one or
1305 more columns of the table within a constraint (for
1306 example, a unique, referential, or table check
1309 \docparam{userID
}{{\it OPTIONAL.
} User for which to determine if the privilege
1310 specified to be checked is granted or not. Default is "".
1311 {\it userID
} is evaluated as follows:
1314 userID == NULL ... NOT ALLOWED!
1315 userID == "" ... UserID set equal to 'this->uid'
1316 userID != "" ... UserID set equal to 'userID'
1319 \docparam{schema
}{{\it OPTIONAL.
} Owner of the table. Specify a userID when the datasource
1320 you are connected to allows multiple unique tables with the same name to be
1321 owned by different users. Specifying the table owner makes determination of the
1322 users privileges MUCH faster. Default is NULL.
{\it userID
} is evaluated as follows:
1325 schema == NULL ... Any owner (DEFAULT)
1326 schema == "" ... Owned by 'this->uid'
1327 schema != "" ... Owned by userID specified in 'schema'
1330 \docparam{path
}{{\it OPTIONAL.
} Path to the table. Default is "".
1335 The scope of privilege allowed to the connected user by a given table
1336 privilege is datasource dependent.
1338 For example, the privilege UPDATE might allow the connected user to update
1339 all columns in a table on one datasource, but only those columns for
1340 which the grantor (the user that granted the connected user) has the UPDATE
1341 privilege on another datasource.
1344 \membersection{wxDb::TranslateSqlState
}\label{wxdbtranslatesqlstate
}
1346 \func{int
}{TranslateSqlState
}{\param{const wxChar *
}{SQLState
}}
1348 Converts an ODBC sqlstate to an internal error code.
1350 \wxheading{Parameters
}
1352 \docparam{SQLState
}{State to be converted.
}
1354 \wxheading{Return value
}
1356 Returns the internal class DB_ERR code. See
\helpref{wxDb::DB_STATUS
}{wxdb
} definition.
1359 \membersection{wxDb::WriteSqlLog
}\label{wxdbwritesqllog
}
1361 \func{bool
}{WriteSqlLog
}{\param{const wxChar *
}{logMsg
}}
1363 \wxheading{Parameters
}
1365 \docparam{logMsg
}{Free form string to be written to the log file.
}
1369 Very useful debugging tool that may be turned on/off during run time (see
1370 (see
\helpref{wxDb::SetSqlLogging
}{wxdbsetsqllogging
} for details on
1371 turning logging on/off). The passed in string
{\it logMsg
} will be written to
1372 a log file if SQL logging is turned on.
1374 \wxheading{Return value
}
1376 If SQL logging is off when a call to WriteSqlLog() is made, or there is a
1377 failure to write the log message to the log file, the function returns
1378 FALSE without performing the requested log, otherwise TRUE is returned.
1380 \wxheading{See also
}
1382 \helpref{wxDb::SetSqlLogging
}{wxdbsetsqllogging
}
1385 \section{\class{wxDbColDataPtr
}}\label{wxdbcoldataptr
}
1387 Pointer to dynamic column definitions for use with a wxDbTable instance.
1388 Currently there are no member functions for this class.
1397 \section{\class{wxDbColDef
}}\label{wxdbcoldef
}
1399 This class is used to hold information about the columns bound to an
1400 instance of a wxDbTable object.
1402 Each instance of this class describes one column in the wxDbTable
1403 object. When calling the
\helpref{wxDb constructor
}{wxdbconstr
}, a
1404 parameter passed in indicates the number of columns that will be defined for
1405 the wxDbTable object. The constructor uses this information to allocate
1406 adequate memory for all of the column descriptions in your wxDbTable object.
1407 Private member wxDbTable::colDefs is a pointer to this chunk of memory
1408 maintained by the wxDbTable class (and can be retrieved using the
1409 \helpref{wxDbTable::GetColDefs
}{wxdbtablegetcoldefs
} function).
1410 To access the nth column definition of your wxDbTable object, just reference
1411 wxDbColDefs element
[n -
1].
1413 Currently there are no member functions for this class.
1415 Typically,
\helpref{wxDbTable::SetColDefs
}{wxdbtablesetcoldefs
} is used to
1416 populate an array of these data structures for the wxDbTable instance.
1419 char ColName
[DB_MAX_COLUMN_NAME_LEN+
1]; // Column Name
1420 int DbDataType; - Logical Data Type;
1421 e.g. DB_DATA_TYPE_INTEGER
1422 int SqlCtype; - C data type; e.g. SQL_C_LONG
1423 void *PtrDataObj; - Address of the data object
1424 int SzDataObj; - Size, in bytes, of the data object
1425 bool KeyField; - Is column part of the PRIMARY KEY for the
1426 table? -- Date fields should NOT be
1428 bool Updateable; - Column is updateable?
1429 bool InsertAllowed; - Column included in INSERT statements?
1430 bool DerivedCol; - Column is a derived value?
1431 SDWORD CbValue; - !!!Internal use only!!!
1432 bool Null; - NOT FULLY IMPLEMENTED
1433 Allows NULL values in Inserts and Updates
1436 \wxheading{See also
}
1438 \helpref{wxDbTable::GetColDefs
}{wxdbtablegetcoldefs
},
\helpref{wxDb constructor
}{wxdbconstr
}
1441 \section{\class{wxDbColInf
}}\label{wxdbcolinf
}
1443 Used with the
\helpref{wxDb::GetColumns
}{wxdbgetcolumns
} functions for obtaining all retrievable information about a column's definition.
1446 char catalog
[128+
1];
1448 char tableName
[DB_MAX_TABLE_NAME_LEN+
1];
1449 char colName
[DB_MAX_COLUMN_NAME_LEN+
1];
1451 char typeName
[128+
1];
1454 short decimalDigits;
1457 char remarks
[254+
1];
1458 int dbDataType; // conversion of the 'sqlDataType'
1459 // to the generic data type used by
1461 int PkCol; // Primary key column
1464 2 = Second Key, etc...
1465 char PkTableName
[DB_MAX_TABLE_NAME_LEN+
1];
1466 // Tables that use this PKey as a FKey
1467 int FkCol; // Foreign key column
1470 2 = Second Key, etc...
1471 char FkTableName
[DB_MAX_TABLE_NAME_LEN+
1];
1472 // Foreign key table name
1473 wxDbColFor *pColFor; // How should this column be formatted
1476 The constructor for this class initializes all the values to zero or NULL.
1478 The destructor for this class takes care of deleting the pColFor member if
1482 \section{\class{wxDbColFor
}}\label{wxdbcolfor
}
1484 Beginning support for handling international formatting specifically on dates and floats.
1487 wxString s_Field; // Formated String for Output
1488 wxString s_Format
[7]; // Formated Objects - TIMESTAMP has
1490 wxString s_Amount
[7]; // Formated Objects - amount of
1491 things that can be formatted
1492 int i_Amount
[7]; // Formated Objects -
1493 TT MM YYYY HH MM SS m
1494 int i_Nation; //
0 = timestamp
1499 int i_dbDataType; // conversion of the 'sqlDataType'
1500 to the generic data type used by
1502 SWORD i_sqlDataType;
1505 The constructor for this class initializes all the values to zero or NULL.
1507 The destructor does nothing at this time.
1509 Only one function is provided with this class currently:
1511 \membersection{wxDbColFor::Format
}\label{wxdbcolforformat
}
1513 \func{int
}{Format
}{\param{int
}{Nation
},
\param{int
}{dbDataType
},
\param{SWORD
}{sqlDataType
},
\param{short
}{columnSize
},
\param{short
}{decimalDigits
}}\label{wxdbcolforformat
}
1515 Work in progress, and should be inter-related with wxLocale eventually.
1518 \section{\class{wxDbIdxDef
}}\label{wxdbidxdef
}
1520 Used in creation of non-primary indexes. Currently there are no member
1521 functions for this class.
1524 char ColName
[DB_MAX_COLUMN_NAME_LEN+
1]
1526 bool Ascending // Is index maintained in
1530 There are no constructors/destructors as of this time, and no member functions.
1533 \section{\class{wxDbInf
}}\label{wxdbinf
}
1535 Contains information regarding the database connection (datasource name,
1536 number of tables, etc). A pointer to a wxDbTableInf is included in this
1537 class so a program can create a wxDbTableInf array instance to maintain all
1538 information about all tables in the datasource to have all the datasource's
1539 information in one memory structure.
1541 Primarily, this class is used internally by the wxWindows ODBC classes.
1544 char catalog
[128+
1];
1545 char schema
[128+
1]; // typically means owner of table(s)
1546 int numTables; // How many tables does this
1548 wxDbTableInf *pTableInf; // Equals a new
1549 wxDbTableInf
[numTables
];
1552 The constructor for this class initializes all the values to zero or NULL.
1554 The destructor for this class takes care of deleting the pTableInf member if
1558 \section{\class{wxDbTable
}}\label{wxdbtable
}
1560 A wxDbTable instance provides re-usable access to rows of data in
1561 a table contained within the associated ODBC datasource
1563 \wxheading{Include files
}
1568 \latexignore{\rtfignore{\wxheading{Members
}}}
1569 \wxheading{Helper classes and data structures
}
1571 The following classes and structs are defined in dbtable.cpp/.h for use with the wxDbTable class.
1573 \begin{itemize
}\itemsep=
0pt
1574 \item \helpref{wxDbColDef
}{wxdbcoldef
}
1575 \item \helpref{wxDbColDataPtr
}{wxdbcoldataptr
}
1576 \item \helpref{wxDbIdxDef
}{wxdbidxdef
}
1579 \wxheading{Constants
}
1582 wxDB_DEFAULT_CURSOR Primary cursor normally used for cursor based
1585 wxDB_QUERY_ONLY Used to indicate whether a table that is opened
1586 is for query only, or if insert/update/deletes
1587 will be performed on the table. Less overhead
1588 (cursors and memory) are allocated for query
1589 only tables, plus read access times are faster
1590 with some datasources.
1592 wxDB_ROWID_LEN
[Oracle only
] - Used when CanUpdateByRowID()
1593 is true. Optimizes updates so they are faster
1594 by updating on the Oracle-specific ROWID column
1595 rather than some other index.
1598 wxDB_DISABLE_VIEW Use to indicate when a database view should not
1599 be if a table is normally set up to use a view.
1600 [Currently unsupported.
]
1605 \latexignore{\rtfignore{\wxheading{Members
}}}
1607 \membersection{wxDbTable::wxDbTable
}\label{wxdbtableconstr
}
1609 \func{}{wxDbTable
}{\param{wxDb *
}{pwxDb
},
\param{const char *
}{tblName
},
\param{const int
}{nCols
},
\param{const char *
}{qryTblName = NULL
},
\param{bool
}{qryOnly = !wxDB_QUERY_ONLY
},
\param{const char *
}{tblPath=NULL
}}
1611 Default constructor.
1613 \wxheading{Parameters
}
1615 \docparam{pwxDb
}{Pointer to the wxDb instance to be used by this wxDbTable
1617 \docparam{tblName
}{The name of the table in the RDBMS.
}
1618 \docparam{nCols
}{The number of columns in the table. (Do NOT include the ROWID
1619 column in the count if using Oracle).
}
1620 \docparam{qryTblName
}{{\it OPTIONAL
}. The name of the table or view to base
1621 your queries on. This argument allows you to specify a table/view other than
1622 the base table for this object to base your queries on. This allows you to
1623 query on a view for example, but all of the INSERT, UPDATE and DELETES will
1624 still be performed on the base table for this wxDbTable object. Basing your
1625 queries on a view can provide a substantial performance increase in cases where
1626 your queries involve many tables with multiple joins. Default is NULL.
}
1627 \docparam{qryOnly
}{{\it OPTIONAL
}. Indicates whether the table will be
1628 accessible for query purposes only, or should the table create the necessary
1629 cursors to be able to insert, update, and delete data from the table.
1630 Default is !wxDB_QUERY_ONLY.
}
1631 \docparam{tblPath
}{{\it OPTIONAL
}. Some datasources (such as dBase)
1632 require a path to where the table is stored on the system. Default is NULL.
}
1634 \membersection{wxDbTable::wxDbTable
}\label{wxdbtabledestr
}
1636 \func{virtual
}{\destruct{wxDbTable
}}{}
1638 Virtual default destructor.
1641 \membersection{wxDbTable::BuildDeleteStmt
}\label{wxdbtablebuilddeletestmt
}
1643 \func{void
}{BuildDeleteStmt
}{\param{char *
}{pSqlStmt
},
\param{int
}{typeOfDel
},
\param{const char *
}{pWhereClause=NULL
}}
1645 Constructs the full SQL statement that can be used to delete all rows matching
1646 the criteria in the pWhereClause.
1648 \wxheading{Parameters
}
1650 \docparam{pSqlStmt
}{Pointer to buffer for the SQL statement retrieved. To be
1651 sure you have adequate space allocated for the SQL statement, allocate
1652 DB_MAX_STATEMENT_LEN bytes.
}
1653 \docparam{typeOfDel
}{The type of delete statement being performed. Can be one
1654 of three values: DB_DEL_KEYFIELDS, DB_DEL_WHERE or DB_DEL_MATCHING
}
1655 \docparam{pWhereClause
}{{\it OPTIONAL
}. If the typeOfDel is DB_DEL_WHERE,
1656 then you must also pass in a SQL WHERE clause in this argument. Default
1661 This member function constructs a SQL DELETE statement. This can be used for
1662 debugging purposes if you are having problems executing your SQL statement.
1664 WHERE and FROM clauses specified using
\helpref{wxDbTable::SetWhereClause
}{wxdbtablesetwhereclause
}
1665 and
\helpref{wxDbTable::SetFromClause
}{wxdbtablesetfromclause
} are ignored by
1669 \membersection{wxDbTable::BuildSelectStmt
}\label{wxdbtablebuildselectstmt
}
1671 \func{void
}{BuildSelectStmt
}{\param{char *
}{pSqlStmt
},
\param{int
}{typeOfSelect
},
\param{bool
}{distinct
}}
1673 Constructs the full SQL statement that can be used to select all rows matching
1674 the criteria in the pWhereClause. This function is called internally in the
1675 wxDbTable class whenever the function
\helpref{wxDbTable::Query
}{wxdbtablequery
}
1678 NOTE: Only the columns specified in
\helpref{wxDbTable::SetColDefs
}{wxdbtablesetcoldefs
}
1679 statements are included in the list of columns returned by the SQL statement
1680 created by a call to this function.
1682 \wxheading{Parameters
}
1684 \docparam{pSqlStmt
}{Pointer to storage for the SQL statement retrieved. To be
1685 sure you have adequate space allocated for the SQL statement, allocate
1686 DB_MAX_STATEMENT_LEN bytes.
}
1687 \docparam{typeOfSelect
}{The type of select statement being performed. Can be
1688 one of four values: DB_SELECT_KEYFIELDS, DB_SELECT_WHERE, DB_SELECT_MATCHING
1689 or DB_SELECT_STATEMENT.
}
1690 \docparam{distinct
}{Whether to select distinct records only.
}
1694 This member function constructs a SQL SELECT statement. This can be used for
1695 debugging purposes if you are having problems executing your SQL statement.
1697 WHERE and FROM clauses specified using
\helpref{wxDbTable::SetWhereClause
}{wxdbtablesetwhereclause
}
1698 and
\helpref{wxDbTable::SetFromClause
}{wxdbtablesetfromclause
} are ignored by
1702 \membersection{wxDbTable::BuildUpdateStmt
}\label{wxdbtablebuildupdatestmt
}
1704 \func{void
}{BuildSelectStmt
}{\param{char *
}{pSqlStmt
},
\param{int
}{typeOfUpd
},
\param{const char *
}{pWhereClause=NULL
}}
1706 Constructs the full SQL statement that can be used to update all rows matching
1707 the criteria in the pWhereClause.
1709 If typeOfUpd is DB_UPD_KEYFIELDS, then the current values in the bound columns
1710 are used to determine which row(s) in the table are to be updated. The
1711 exception to this is when a datasource supports ROW IDs (Oracle). The ROW ID
1712 column is used for efficiency purposes when available.
1714 NOTE: Only the columns specified in
\helpref{wxDbTable::SetColDefs
}{wxdbtablesetcoldefs
}
1715 statements are included in the list of columns updated by the SQL statement
1716 created by a call to this function. Any column definitions that were defined
1717 as being non-updateable will be excluded from the SQL UPDATE statement created
1720 \wxheading{Parameters
}
1722 \docparam{pSqlStmt
}{Pointer to storage for the SQL statement retrieved. To be
1723 sure you have adequate space allocated for the SQL statement, allocate
1724 DB_MAX_STATEMENT_LEN bytes.
}
1725 \docparam{typeOfUpd
}{The type of update statement being performed. Can be one
1726 of two values: DB_UPD_KEYFIELDS or DB_UPD_WHERE.
}
1727 \docparam{pWhereClause
}{{\it OPTIONAL
}. If the typeOfUpd is DB_UPD_WHERE,
1728 then you must also pass in a SQL WHERE clause in this argument. Default
1733 This member function allows you to see what the SQL UPDATE statement looks like
1734 that the ODBC class library builds. This can be used for debugging purposes if
1735 you are having problems executing your SQL statement.
1737 WHERE and FROM clauses specified using
\helpref{wxDbTable::SetWhereClause
}{wxdbtablesetwhereclause
}
1738 and
\helpref{wxDbTable::SetFromClause
}{wxdbtablesetfromclause
} are ignored by
1742 \membersection{wxDbTable::BuildWhereStmt
}\label{wxdbtablebuildwherestmt
}
1744 \func{void
}{BuildSelectStmt
}{\param{char *
}{pWhereClause
},
\param{int
}{typeOfWhere
},
\param{const char *
}{qualTableName=NULL
},
\param{const char *
}{useLikeComparison=FALSE
}}
1746 Constructs the portion of a SQL statement which would follow the word 'WHERE'
1747 in a SQL statement to be passed to the datasource. The returned string
1748 does NOT include the word 'WHERE'.
1750 \wxheading{Parameters
}
1752 \docparam{pWhereClause
}{Pointer to storage for the SQL statement retrieved.
1753 To be sure you have adequate space allocated for the SQL statement, allocate
1754 DB_MAX_STATEMENT_LEN bytes.
}
1755 \docparam{typeOfWhere
}{The type of where clause to generate. Can be one of
1756 two values: DB_WHERE_KEYFIELDS or DB_WHERE_MATCHING.
}
1757 \docparam{qualTableName
}{{\it OPTIONAL
}. Prepended to all base table
1758 column names. For use when a FROM clause has been specified with the
1759 \helpref{wxDbTable::SetFromClause
}{wxdbtablesetfromclause
},
1760 to clarify which table a column name reference belongs to. Default is NULL.
}
1761 \docparam{useLikeComparison
}{{\it OPTIONAL
}. Should the constructed WHERE
1762 clause utilize the LIKE comparison operator. If FALSE, then the '='
1763 operator is used. Default is FALSE.
}
1767 This member function allows you to see what the SQL WHERE clause looks like
1768 that the ODBC class library builds. This can be used for debugging purposes
1769 if you are having problems executing your own SQL statements.
1771 If using 'typeOfWhere' set to DB_WHERE_MATCHING, any bound columns currently
1772 containing a NULL value are not included in the WHERE clause's list of
1773 columns to use in the comparison.
1776 \membersection{wxDbTable::CanSelectForUpdate
}\label{wxdbtablecanselectforupdate
}
1778 \func{bool
}{CanSelectForUpdate
}{\void}
1780 Use this function to determine if the datasource supports SELECT ... FOR UPDATE.
1781 When the keywords "FOR UPDATE" are included as part of your SQL SELECT statement,
1782 all records
{\it retrieved
} (not just queried, but actually retrieved using
1783 \helpref{wxDbTable::GetNext
}{wxdbtablegetnext
}, etc) from the result set are
1788 Not all datasources support the "FOR UPDATE" clause, so you must use this
1789 member function to determine if the datasource currently connected to supports
1790 this behavior or not before trying to select using "FOR UPDATE".
1793 \membersection{wxDbTable::CanUpdateByROWID
}\label{wxdbtablecanupdatebyrowid
}
1795 \func{bool
}{CanUpdateByROWID
}{\void}
1797 CURRENTLY ONLY POSSIBLE IF USING ORACLE.
1799 --- CURRENTLY DISABLED FOR *ALL* DATASOURCES --- NOV
1 2000 - gt
1801 Every Oracle table has a hidden column named ROWID. This is a pointer to the
1802 physical location of the record in the datasource and allows for very fast
1803 updates and deletes. The key is to retrieve this ROWID during your query so
1804 it is available during an update or delete operation.
1806 Use of the ROWID feature is always handled by the class library except in the
1807 case of
\helpref{wxDbTable::QueryBySqlStmt
}{wxdbtablequerybysqlstmt
}. Since
1808 you are passing in the SQL SELECT statement,
1809 it is up to you to include the ROWID column in your query. If you do not,
1810 the application will still work, but may not be as optimized. The ROWID is
1811 always the last column in the column list in your SQL SELECT statement.
1812 The ROWID is not a column in the normal sense and should not be considered
1813 part of the column definitions for the wxDbTable object.
1817 The decision to include the ROWID in your SQL SELECT statement must be
1818 deferred until runtime since it depends on whether you are connected
1819 to an Oracle datasource or not.
1824 // Incomplete code sample
1827 if (parts.CanUpdByROWID())
1829 // Note that the ROWID column must always be the last column selected
1830 sqlStmt = "SELECT PART_NO, PART_DESC, ROWID" FROM PARTS";
1833 sqlStmt = "SELECT PART_NO, PART_DESC" FROM PARTS";
1837 \membersection{wxDbTable::ClearMemberVar
}\label{wxdbtableclearmembervar
}
1839 \func{void
}{ClearMemberVar
}{\param{int
}{colNo
},
\param{bool
}{setToNull=FALSE
}}
1841 Same as
\helpref{wxDbTable::ClearMemberVars
}{wxdbtableclearmembervars
} except
1842 that it clears only the specified column of its values, and optionally sets
1843 the column to be a NULL column.
1845 \docparam{colNo
}{Column number that is to be cleared. This number (between
0 and (noCols-
1)) is the index of the column definition created using the
\helpref{wxDbTable::SetColDefs
}{wxdbtablesetcoldefs
} function.
}
1846 \docparam{setToNull
}{{\it OPTIONAL
}. Indicates whether the column should be flagged as being a NULL value stored in the bound memory variable. If TRUE, then any value stored in the bound member variable is cleared. Default is FALSE.
}
1849 \membersection{wxDbTable::ClearMemberVars
}\label{wxdbtableclearmembervars
}
1851 \func{void
}{ClearMemberVars
}{\param{bool
}{setToNull=FALSE
}}
1853 Initializes all bound columns of the wxDbTable instance to zero. In the case
1854 of a string, zero is copied to the first byte of the string.
1856 \docparam{setToNull
}{{\it OPTIONAL
}. Indicates whether all columns should be flagged as having a NULL value stored in the bound memory variable. If TRUE, then any value stored in the bound member variable is cleared. Default is FALSE.
}
1860 This is useful before calling functions such as
\helpref{wxDbTable::QueryMatching
}{wxdbtablequerymatching
} or
1861 \helpref{wxDbTable::DeleteMatching
}{wxdbtabledeletematching
} since these
1862 functions build their WHERE clauses from non-zero columns. To call either
1863 \helpref{wxDbTable::QueryMatching
}{wxdbtablequerymatching
} or
1864 \helpref{wxDbTable::DeleteMatching
}{wxdbtabledeletematching
} use this sequence:
1867 1) ClearMemberVars()
1868 2) Assign columns values you wish to match on
1869 3) Call wxDbTable::QueryMatching() or wxDbTable::DeleteMatching()
1873 \membersection{wxDbTable::CloseCursor
}\label{wxdbtableclosecursor
}
1875 \func{bool
}{CloseCursor
}{\param{HSTMT
}{cursor
}}
1877 Closes the specified cursor associated with the wxDbTable object.
1879 \wxheading{Parameters
}
1881 \docparam{cursor
}{The cursor to be closed.
}
1885 Typically handled internally by the ODBC class library, but may be used by the
1886 programmer if desired.
1889 \normalbox{DO NOT CLOSE THE wxDB_DEFAULT_CURSOR!
}
1892 \membersection{wxDbTable::Count
}\label{wxdbtablecount
}
1894 \func{ULONG
}{Count
}{\param{const char *
}{args="*"
}}
1896 Returns the number of records which would be in the result set using the
1897 current query parameters specified in the WHERE and FROM clauses.
1899 \wxheading{Parameters
}
1901 \docparam{args
}{{\it OPTIONAL
}. This arguement allows the use of the
1902 DISTINCT keyword against a column name to cause the returned count to
1903 only indicate the number of rows in the result set that have a unique
1904 value in the specified column. An example is shown below. Default is "*",
1905 meaning a count of the total number of rows matching is returned, regardless of
1910 This function can be called before or after an actual query to obtain the
1911 count of records in the result set. Count() uses its own cursor, so result
1912 set cursor positioning is not affected by calls to Count().
1914 WHERE and FROM clauses specified using
\helpref{wxDbTable::SetWhereClause
}{wxdbtablesetwhereclause
}
1915 and
\helpref{wxDbTable::SetFromClause
}{wxdbtablesetfromclause
} ARE used by
1924 FIRST_NAME LAST_NAME
1925 ----------- ----------
1931 // Incomplete code sample
1934 users.SetWhereClause("");
1936 // This Count() will return
4, as there are four users listed above
1937 // that match the query parameters
1938 totalNumberOfUsers = users.Count();
1940 // This Count() will return
3, as there are only
3 unique first names
1941 // in the table above - John, Richard, Michael.
1942 totalNumberOfUniqueFirstNames = users.Count("DISTINCT FIRST_NAME");
1946 \membersection{wxDbTable::CreateIndex
}\label{wxdbtablecreateindex
}
1948 \func{bool
}{CreateIndex
}{\param{const char *
}{idxName
},
\param{bool
}{unique
},
\param{int
}{noIdxCols
},
\param{wxDbIdxDef *
}{pIdxDefs
},
\param{bool
}{attemptDrop=TRUE
}}
1950 This member function allows you to create secondary (non primary) indexes on
1951 your tables. You first create your table, normally specifying a primary
1952 index, and then create any secondary indexes on the table. Indexes in
1953 relational model are not required. You do not need indexes to look up records
1954 in a table or to join two tables together. In the relational model, indexes,
1955 if available, provide a quicker means to look up data in a table. To enjoy
1956 the performance benefits of indexes, the indexes must be defined on the
1957 appropriate columns and your SQL code must be written in such a way as to
1958 take advantage of those indexes.
1960 \wxheading{Parameters
}
1962 \docparam{idxName
}{Name of the Index. Name must be unique within the table space of the datasource.
}
1963 \docparam{unique
}{Indicates if this index is unique.
}
1964 \docparam{noIdxCols
}{Number of columns in the index.
}
1965 \docparam{pIdxDefs
}{A pointer to an array wxDbIdxDef structures.
}
1966 \docparam{attemptDrop
}{{\it OPTIONAL
}. Indicates if the function should try
1967 to execute a
\helpref{wxDbTable::DropIndex
}{wxdbtabledropindex
} on the index
1968 name provided before trying to create the index name. Default is TRUE.
}
1972 The first parameter, index name, must be unique and should be given a
1973 meaningful name. Common practice is to include the table name as a prefix
1974 in the index name (e.g. For table PARTS, you might want to call your index
1975 PARTS_IDX1). This will allow you to easily view all
1976 of the indexes defined for a given table grouped together alphabetically.
1978 The second parameter indicates if the index is unique or not. Uniqueness
1979 is enforced at the RDBMS level preventing rows which would have duplicate
1980 indexes from being inserted into the table when violating a unique index's
1983 In the third parameter, specify how many columns are in your index. This
1984 number must match the number of columns defined in the 'pIdxDefs' parameter.
1986 The fourth parameter specifies which columns make up the index using the
1987 wxDbIdxDef structure. For each column in the index, you must specify two
1988 things, the column name and the sort order (ascending / descending). See
1989 the example below to see how to build and pass in the wxDbIdxDef structure.
1991 The fifth parameter is provided to handle the differences in datasources as
1992 to whether they will automatically overwrite existing indexes with the same
1993 name or not. Some datasources require that the existing index must be dropped
1994 first, so this is the default behavior.
1996 Some datasources (MySQL, and possibly others) require columns which are to be
1997 part of an index to be defined as NOT NULL. When this function is called, if
1998 a column is not defined to be NOT NULL, a call to this function will modify
1999 the column definition to change any columns included in the index to be
2000 NOT NULL. In this situation, if a NULL value already exists in one of the
2001 columns that is being modified, creation of the index will fail.
2003 PostGres is unable to handle index definitions which specify whether the index
2004 is ascending or descending, and defaults to the system default when the index
2007 It is not necessary to call
\helpref{wxDb::CommitTrans
}{wxdbcommittrans
}
2008 after executing this function.
2013 // Create a secondary index on the PARTS table
2014 wxDbIdxDef idxDef
[2]; //
2 columns make up the index
2016 strcpy(idxDef
[0].ColName, "PART_DESC"); // Column
1
2017 idxDef
[0].Ascending = TRUE;
2019 strcpy(idxDef
[1].ColName, "SERIAL_NO"); // Column
2
2020 idxDef
[1].Ascending = FALSE;
2022 // Create a name for the index based on the table's name
2024 indexName.sprintf("
%s_IDX1",parts->GetTableName());
2025 parts->CreateIndex(indexName.c_str(), TRUE,
2, idxDef);
2029 \membersection{wxDbTable::CreateTable
}\label{wxdbtablecreatetable
}
2031 \func{bool
}{CreateTable
}{\param{bool
}{attemptDrop=TRUE
}}
2033 Creates a table based on the definitions previously defined for this
2036 \wxheading{Parameters
}
2038 \docparam{attemptDrop
}{{\it OPTIONAL
}. Indicates whether the driver should
2039 attempt to drop the table before trying to create it. Some datasources will
2040 not allow creation of a table if the table already exists in the table space
2041 being used. Default is TRUE.
}
2045 This function creates the table and primary index (if any) in the table space
2046 associated with the connected datasource. The owner of these objects will
2047 be the user id that was given when
\helpref{wxDb::Open
}{wxdbopen
} was called. The objects will
2048 be created in the default schema/table space for that user.
2050 In your derived wxDbTable object constructor, the columns and primary index
2051 of the table are described through the
\helpref{wxDbColDef
}{wxdbcoldef
} structure.
2052 \helpref{wxDbTable::CreateTable
}{wxdbtablecreatetable
} uses this information to create the table and to add
2053 the primary index. See
\helpref{wxDbTable
}{wxdbtable
} ctor and wxDbColDef
2054 description for additional information on describing the columns of the table.
2056 It is not necessary to call
\helpref{wxDb::CommitTrans
}{wxdbcommittrans
}
2057 after executing this function.
2060 \membersection{wxDbTable::DB_STATUS
}\label{wxdbtabledbstatus
}
2062 \func{bool
}{DB_STATUS
}{\void}
2064 Accessor function that returns the wxDb private member variable DB_STATUS for
2065 the database connection used by this instance of wxDbTable.
2068 \membersection{wxDbTable::Delete
}\label{wxdbtabledelete
}
2070 \func{bool
}{Delete
}{\void}
2072 Deletes the row from the table indicated by the current cursor.
2076 Use
\helpref{wxDbTable::GetFirst
}{wxdbtablegetfirst
},
\helpref{wxDbTable::GetLast
}{wxdbtablegetlast
},
2077 \helpref{wxDbTable::GetNext
}{wxdbtablegetnext
} or
2078 \helpref{wxDbTable::GetPrev
}{wxdbtablegetprev
} to position the cursor to
2079 a valid record. Once positioned on a record, call this function to delete
2080 the row from the table.
2082 A
\helpref{wxDb::CommitTrans
}{wxdbcommittrans
} or
2083 \helpref{wxDb::RollbackTrans
}{wxdbrollbacktrans
} must be called after use of
2084 this function to commit or rollback the deletion.
2086 NOTE: Most datasources have a limited size "rollback" segment. This means
2087 that it is only possible to insert/update/delete a finite number of rows
2088 without performing a
\helpref{wxDb::CommitTrans
}{wxdbcommittrans
} or
2089 \helpref{wxDb::RollbackTrans
}{wxdbrollbacktrans
}. Size of the rollback
2090 segment varies from database to database, and is user configurable in
2091 most databases. Therefore it is usually best to try to perform a commit
2092 or rollback at relatively small intervals when processing a larger number
2093 of actions that insert/update/delete rows in a table.
2096 \membersection{wxDbTable::DeleteCursor
}\label{wxdbtabledeletecursor
}
2098 \func{bool
}{DeleteCursor
}{\param{HSTMT *
}{hstmtDel
}}
2100 Allows a program to delete a cursor.
2102 \wxheading{Parameters
}
2104 \docparam{hstmtDel
}{Handle of the cursor to delete.
}
2108 For default cursors associated with the instance of wxDbTable, it is not
2109 necessary to specifically delete the cursors. This is automatically done
2110 in the wxDbTable destructor.
2112 NOTE: If the cursor could not be deleted for some reason, an error is logged
2113 indicating the reason. Even if the cursor could not be deleted, the HSTMT
2114 that is passed in is deleted, and the pointer is set to NULL.
2116 \normalbox{DO NOT DELETE THE wxDB_DEFAULT_CURSOR!
}
2119 \membersection{wxDbTable::DeleteMatching
}\label{wxdbtabledeletematching
}
2121 \func{bool
}{DeleteMatching
}{\void}
2123 This member function allows you to delete records from your wxDbTable object
2124 by specifying the data in the columns to match on.
2128 To delete all users with a first name of "JOHN", do the following:
2131 1) Clear all "columns" using wxDbTable::ClearMemberVars().
2132 2) Set the FIRST_NAME column equal to "JOHN".
2133 3) Call wxDbTable::DeleteMatching().
2136 The WHERE clause is built by the ODBC class library based on all non-NULL
2137 columns. This allows deletion of records
2138 by matching on any column(s) in your wxDbTable instance, without having to
2139 write the SQL WHERE clause.
2141 A
\helpref{wxDb::CommitTrans
}{wxdbcommittrans
} or
2142 \helpref{wxDb::RollbackTrans
}{wxdbrollbacktrans
} must be called after use of
2143 this function to commit or rollback the deletion.
2145 NOTE: Row(s) should be locked before deleting them to make sure they are
2146 not already in use. This can be achieved by calling
\helpref{wxDbTable::QueryMatching
}{wxdbtablequerymatching
},
2147 and then retrieving the records, locking each as you go (assuming FOR UPDATE
2148 is allowed on the datasource). After the row(s) have been successfully locked,
2151 NOTE: Most datasources have a limited "rollback" segment. This means
2152 that it is only possible to insert/update/delete a finite number of rows
2153 without performing a
\helpref{wxDb::CommitTrans
}{wxdbcommittrans
} or
2154 \helpref{wxDb::RollbackTrans
}{wxdbrollbacktrans
}. Size of the rollback
2155 segment varies from database to database, and is user configurable in
2156 most databases. Therefore it is usually best to try to perform a commit
2157 or rollback at relatively small intervals when processing a larger number
2158 of actions that insert/update/delete rows in a table.
2164 // Incomplete code sample to delete all users with a first name
2166 users.ClearMemberVars();
2167 strcpy(users.FirstName,"JOHN");
2168 users.DeleteMatching();
2172 \membersection{wxDbTable::DeleteWhere
}\label{wxdbtabledeletewhere
}
2174 \func{bool
}{DeleteWhere
}{\param{const char *
}{pWhereClause
}}
2176 Deletes all rows from the table which match the criteria specified in the
2177 WHERE clause that is passed in as the sole parameter.
2179 \wxheading{Parameters
}
2181 \docparam{pWhereClause
}{SQL WHERE clause. This WHERE clause determines which
2182 records will be deleted from the table interfaced through the wxDbTable
2183 instance. The WHERE clause passed in must be compliant with the SQL
92
2184 grammar. Do not include the keyword 'WHERE'
2189 This is the most powerful form of the wxDbTable delete functions. This
2190 function gives access to the full power of SQL. This function can be used
2191 to delete records by passing a valid SQL WHERE clause. Sophisticated
2192 deletions can be performed based on multiple criteria using the full
2193 functionality of the SQL language.
2195 A
\helpref{wxDb::CommitTrans
}{wxdbcommittrans
} must be called after use of
2196 this function to commit the deletions.
2198 Note: This function is limited to deleting records from the table associated
2199 with this wxDbTable object only. Deletions on joined tables is not possible.
2201 NOTE: Most datasources have a limited size "rollback" segment. This means
2202 that it is only possible to insert/update/delete a finite number of rows
2203 without performing a
\helpref{wxDb::CommitTrans
}{wxdbcommittrans
} or
2204 \helpref{wxDb::RollbackTrans
}{wxdbrollbacktrans
}. Size of the rollback
2205 segment varies from database to database, and is user configurable in
2206 most databases. Therefore it is usually best to try to perform a commit
2207 or rollback at relatively small intervals when processing a larger number
2208 of actions that insert/update/delete rows in a table.
2213 // Delete parts
1 thru
10 from containers 'X', 'Y' and 'Z' that
2214 // are magenta in
color
2215 parts.DeleteWhere("(PART_NUMBER BETWEEN
1 AND
10) AND \
2216 CONTAINER IN ('X', 'Y', 'Z') AND \
2217 UPPER(COLOR) = 'MAGENTA'");
2221 \membersection{wxDbTable::DropIndex
}\label{wxdbtabledropindex
}
2223 \func{bool
}{DropIndex
}{\param{const char *
}{idxName
}}
2225 Allows an index on the associated table to be dropped (deleted) if the user
2226 login has sufficient privileges to do so.
2228 \wxheading{Parameters
}
2230 \docparam{idxName
}{Name of the index to be dropped.
}
2234 If the index specified in the 'idxName' parameter does not exist, an error
2235 will be logged, and the function will return a result of FALSE.
2237 It is not necessary to call
\helpref{wxDb::CommitTrans
}{wxdbcommittrans
}
2238 after executing this function.
2241 \membersection{wxDbTable::DropTable
}\label{wxdbtabledroptable
}
2243 \func{bool
}{DropTable
}{\void}
2245 Deletes the associated table if the user has sufficient privileges to do so.
2249 This function returns TRUE if the table does not exist, but only for
2250 supported databases (see
\helpref{wxDb::Dbms
}{wxdbdbms
}). If a datasource
2251 is not specifically supported, and this function is called, the function
2254 Some datasources/ODBC drivers will delete any indexes associated with the
2255 table automatically, and others may not. Check the documentation for your
2256 database to determine the behavior.
2258 It is not necessary to call
\helpref{wxDb::CommitTrans
}{wxdbcommittrans
}
2259 after executing this function.
2262 \membersection{wxDbTable::From
}\label{wxdbtablefrom
}
2264 \func{const char *
}{From
}{}
2265 \func{void
}{From
}{\param{const wxString &
}{From
}}
2267 Accessor function for the private class member wxDbTable::from. Can be used
2268 as a synonym for
\helpref{wxDbTable::GetFromClause
}{wxdbtablegetfromclause
}
2269 (the first form of this function) or
2270 \helpref{wxDbTable::SetFromClause
}{wxdbtablesetfromclause
} (the second form
2273 \wxheading{Parameters
}
2275 \docparam{From
}{A comma separated list of table names that are to be outer
2276 joined with the base table's columns so that the joined table's columns
2277 may be returned in the result set or used as a portion of a comparison with
2278 the base table's columns. NOTE that the base tables name must NOT be included
2279 in the FROM clause, as it is automatically included by the wxDbTable class
2280 in constructing query statements.
}
2282 \wxheading{Return value
}
2284 The first form of this function returns the current value of the wxDbTable
2285 member variable ::from.
2287 The second form of the function has no return value.
2289 \wxheading{See also
}
2291 \helpref{wxDbTable::GetFromClause
}{wxdbtablegetfromclause
},
2292 \helpref{wxDbTable::SetFromClause
}{wxdbtablesetfromclause
}
2295 \membersection{wxDbTable::GetColDefs
}\label{wxdbtablegetcoldefs
}
2297 \func{wxDbColDef *
}{GetColDefs
}{}
2299 Accessor function that returns a pointer to the array of column definitions
2300 that are bound to the columns that this wxDbTable instance is associated
2303 To determine the number of elements pointed to by the returned
2304 \helpref{wxDbColDef
}{wxdbcoldef
} pointer, use the
2305 \helpref{wxDbTable::GetNumberOfColumns
}{wxdbtablegetnumberofcolumns
} function.
2309 These column definitions must not be manually redefined after they have been
2313 \membersection{wxDbTable::GetCursor
}\label{wxdbtablegetcursor
}
2315 \func{HSTMT
}{GetCursor
}{\void}
2317 Returns the HSTMT value of the current cursor for this wxDbTable object.
2321 This function is typically used just before changing to use a different cursor
2322 so that after the program is finished using the other cursor, the current
2323 cursor can be set back to being the cursor in use.
2325 \wxheading{See also
}
2327 \helpref{wxDbTable::SetCursor
}{wxdbtablesetcursor
},
\helpref{wxDbTable::GetNewCursor
}{wxdbtablegetnewcursor
}
2330 \membersection{wxDbTable::GetDb
}\label{wxdbtablegetdb
}
2332 \func{wxDb *
}{GetDb
}{}
2334 Accessor function for the private member variable pDb which is a pointer to
2335 the datasource connection that this wxDbTable instance uses.
2338 \membersection{wxDbTable::GetFirst
}\label{wxdbtablegetfirst
}
2340 \func{bool
}{GetFirst
}{\void}
2342 Retrieves the FIRST row in the record set as defined by the current query.
2343 Before retrieving records, a query must be performed using
2344 \helpref{wxDbTable::Query
}{wxdbtablequery
},
2345 \helpref{wxDbTable::QueryOnKeyFields
}{wxdbtablequeryonkeyfields
},
2346 \helpref{wxDbTable::QueryMatching
}{wxdbtablequerymatching
} or
2347 \helpref{wxDbTable::QueryBySqlStmt
}{wxdbtablequerybysqlstmt
}.
2351 This function can only be used if the datasource connection used by the
2352 wxDbTable instance was created with FwdOnlyCursors set to FALSE. If the
2353 connection does not allow backward scrolling cursors, this function will
2354 return FALSE, and the data contained in the bound columns will be undefined.
2356 \wxheading{See also
}
2358 \helpref{wxDb::FwdOnlyCursors
}{wxdbfwdonlycursors
}
2361 \membersection{wxDbTable::GetFromClause
}\label{wxdbtablegetfromclause
}
2363 \func{const char *
}{GetFromClause
}{}
2365 Accessor function that returns the current FROM setting assigned with the
2366 \helpref{wxDbTable::SetFromClause
}{wxdbtablesetfromclause
}.
2368 \wxheading{See also
}
2370 \helpref{wxDbTable::From
}{wxdbtablefrom
}
2373 \membersection{wxDbTable::GetLast
}\label{wxdbtablegetlast
}
2375 \func{bool
}{GetLast
}{\void}
2377 Retrieves the LAST row in the record set as defined by the current query.
2378 Before retrieving records, a query must be performed using
2379 \helpref{wxDbTable::Query
}{wxdbtablequery
},
2380 \helpref{wxDbTable::QueryOnKeyFields
}{wxdbtablequeryonkeyfields
},
2381 \helpref{wxDbTable::QueryMatching
}{wxdbtablequerymatching
} or
2382 \helpref{wxDbTable::QueryBySqlStmt
}{wxdbtablequerybysqlstmt
}.
2386 This function can only be used if the datasource connection used by the
2387 wxDbTable instance was created with FwdOnlyCursors set to FALSE. If the
2388 connection does not allow backward scrolling cursors, this function will
2389 return FALSE, and the data contained in the bound columns will be undefined.
2391 \wxheading{See also
}
2393 \helpref{wxDb::FwdOnlyCursors
}{wxdbfwdonlycursors
}
2396 \membersection{wxDbTable::GetNewCursor
}\label{wxdbtablegetnewcursor
}
2398 \func{HSTMT *
}{GetNewCursor
}{\param{bool
}{setCursor=FALSE
},
\param{bool
}{bindColumns=TRUE
}}
2401 % George: @@@@@@@@STILL TO DO
2404 \wxheading{Parameters
}
2406 \docparam{setCursor
}{{\it OPTIONAL
}. Default is FALSE.
}
2407 \docparam{bindColumns
}{{\it OPTIONAL
}. Default is TRUE.
}
2412 \membersection{wxDbTable::GetNext
}\label{wxdbtablegetnext
}
2414 \func{bool
}{GetNext
}{\void}
2416 Retrieves the NEXT row in the record set after the current cursor position
2417 as defined by the current query. Before retrieving records, a query must be
2418 performed using
\helpref{wxDbTable::Query
}{wxdbtablequery
},
2419 \helpref{wxDbTable::QueryOnKeyFields
}{wxdbtablequeryonkeyfields
},
2420 \helpref{wxDbTable::QueryMatching
}{wxdbtablequerymatching
} or
2421 \helpref{wxDbTable::QueryBySqlStmt
}{wxdbtablequerybysqlstmt
}.
2423 \wxheading{Return value
}
2425 This function returns FALSE when the current cursor has reached the end of
2426 the result set. When FALSE is returned, data in the bound columns is
2429 \wxheading{See also
}
2430 \helpref{wxDbTable::++
}{wxdbtableplusplus
}
2433 \membersection{wxDbTable::GetNumberOfColumns
}\label{wxdbtablegetnumberofcolumns
}
2435 \func{bool
}{GetNumberOfColumns
}{}
2437 Accessor function that returns the number of columns that are statically
2438 bound for access by the wxDbTable instance.
2441 \membersection{wxDbTable::GetOrderByClause
}\label{wxdbtablegetorderbyclause
}
2443 \func{const char *
}{GetOrderByClause
}{}
2445 Accessor function that returns the current ORDER BY setting assigned with
2446 the
\helpref{wxDbTable::SetOrderByClause
}{wxdbtablesetorderbyclause
}.
2448 \wxheading{See also
}
2450 \helpref{wxDbTable::OrderBy
}{wxdbtableorderby
}
2453 \membersection{wxDbTable::GetPrev
}\label{wxdbtablegetprev
}
2455 \func{bool
}{GetPrev
}{\void}
2457 Retrieves the PREVIOUS row in the record set before the current cursor
2458 position as defined by the current query. Before retrieving records, a
2459 query must be performed using
\helpref{wxDbTable::Query
}{wxdbtablequery
},
2460 \helpref{wxDbTable::QueryOnKeyFields
}{wxdbtablequeryonkeyfields
},
2461 \helpref{wxDbTable::QueryMatching
}{wxdbtablequerymatching
} or
2462 \helpref{wxDbTable::QueryBySqlStmt
}{wxdbtablequerybysqlstmt
}.
2464 \wxheading{Return value
}
2466 This function returns FALSE when the current cursor has reached the beginning
2467 of the result set and there are now other rows prior to the cursors current
2468 position. When FALSE is returned, data in the bound columns is undefined.
2472 This function can only be used if the datasource connection used by the
2473 wxDbTable instance was created with FwdOnlyCursors set to FALSE. If the
2474 connection does not allow backward scrolling cursors, this function will
2475 return FALSE, and the data contained in the bound columns will be undefined.
2477 \wxheading{See also
}
2479 \helpref{wxDb::FwdOnlyCursors
}{wxdbfwdonlycursors
},
2480 \helpref{wxDbTable::--
}{wxdbtableminusminus
}
2483 \membersection{wxDbTable::GetQueryTableName
}\label{wxdbtablegetquerytablename
}
2485 \func{const char *
}{GetQueryTableName
}{}
2487 Accessor function that returns the name of the table/view that was indicated
2488 as being the table/view to query against when this wxDbTable instance was
2491 \wxheading{See also
}
2493 \helpref{wxDbTable constructor
}{wxdbtableconstr
}
2496 \membersection{wxDbTable::GetRowNum
}\label{wxdbtablegetrownum
}
2498 \func{UWORD
}{GetRowNum
}{\void}
2500 Returns the ODBC row number for performing positioned updates and deletes.
2504 This function is not being used within the ODBC class library and may be a
2505 candidate for removal if no use is found for it.
2508 \membersection{wxDbTable::GetTableName
}\label{wxdbtablegettablename
}
2510 \func{const char *
}{GetTableName
}{}
2512 Accessor function that returns the name of the table that was indicated
2513 as being the table that this wxDbTable instance was associated with.
2516 \membersection{wxDbTable::GetTablePath
}\label{wxdbtablegettablepath
}
2518 \func{const char *
}{GetTablePath
}{}
2520 Accessor function that returns the path to the data table that was indicated
2521 during creation of this wxDbTable instance.
2525 Currently only applicable to dBase and MS-Access datasources.
2528 \membersection{wxDbTable::GetWhereClause
}\label{wxdbtablegetwhereclause
}
2530 \func{const char *
}{GetWhereClause
}{}
2532 Accessor function that returns the current WHERE setting assigned with the
2533 \helpref{wxDbTable::SetWhereClause
}{wxdbtablesetwhereclause
}
2535 \wxheading{See also
}
2537 \helpref{wxDbTable::Where
}{wxdbtablewhere
}
2540 \membersection{wxDbTable::Insert
}\label{wxdbtableinsert
}
2542 \func{int
}{Insert
}{\void}
2544 Inserts a new record into the table being referenced by this wxDbTable
2545 instance. The values in the member variables of the wxDbTable instance are
2546 inserted into the columns of the new row in the database
2548 \wxheading{Return value
}
2551 DB_SUCCESS Record inserted successfully (value =
1)
2553 DB_FAILURE Insert failed (value =
0)
2555 DB_ERR_INTEGRITY_CONSTRAINT_VIOL
2556 The insert failed due to an integrity
2557 constraint violation (duplicate non-unique
2558 index entry) is attempted.
2563 A
\helpref{wxDb::CommitTrans
}{wxdbcommittrans
} or
2564 \helpref{wxDb::RollbackTrans
}{wxdbrollbacktrans
} must be called after use of
2565 this function to commit or rollback the insertion.
2570 // Incomplete code snippet
2571 strcpy(parts->PartName, "
10");
2572 strcpy(parts->PartDesc, "Part
#10");
2574 RETCODE retcode = parts.Insert();
2578 parts->GetDb()->CommitTrans();
2580 case DB_ERR_INTEGRITY_CONSTRAINT_VIOL:
2581 // Current data would result in a duplicate key
2582 // on one or more indexes that do not allow duplicates
2583 parts->GetDb()->RollbackTrans();
2586 // Insert failed for some unexpected reason
2587 parts->GetDb()->RollbackTrans();
2593 \membersection{wxDbTable::IsColNull
}\label{wxdbtableiscolnull
}
2595 \func{bool
}{IsColNull
}{\param{int
}{colNo
}}
2597 Used primarily in the ODBC class library to determine if a column is equal
2598 to "NULL". Works for all data types supported by the ODBC class library.
2600 \wxheading{Parameters
}
2602 \docparam{colNo
}{The column number of the bound column as defined by the
2603 \helpref{wxDbTable::SetColDefs
}{wxdbtablesetcoldefs
}
2604 calls which defined the columns accesible to this wxDbTable instance.
}
2608 NULL column support is currently not fully implemented as of wxWindows
2.4
2611 \membersection{wxDbTable::IsCursorClosedOnCommit
}\label{wxdbtableiscursorclosedoncommit
}
2613 \func{bool
}{IsCursorClosedOnCommit
}{\void}
2615 Accessor function to return information collected during the opening of the
2616 datasource connection that is used by this wxDbTable instance.
2618 \wxheading{Return value
}
2620 Returns TRUE if the cursor associated with this wxDbTable object is closed
2621 after a commit or rollback operation. Returns FALSE otherwise.
2624 \membersection{wxDbTable::IsQueryOnly
}\label{wxdbtableisqueryonly
}
2626 \func{bool
}{IsQueryOnly
}{}
2628 Accessor function that returns a value indicating if this wxDbTable instance
2629 was created to allow only queries to be performed on the bound columns. If
2630 this function returns TRUE, then no actions may be performed using this
2631 wxDbTable instance that would modify (insert/delete/update) the table's data.
2634 \membersection{wxDbTable::Open
}\label{wxdbtableopen
}
2636 \func{bool
}{Open
}{\void}
2638 Every wxDbTable instance must be opened before it can be used. This function
2639 checks for the existence of the requested table, binds columns to the
2640 internal cursors needed (insert/update/select) and constructs the insert
2641 statement that is used for inserting data to the columns of a new row in
2646 If the function returns a FALSE value due to the table not existing, a log
2647 entry is recorded for the datasource connection indicating the problem
2648 that was detected when checking for table existence. Note that it is usually
2649 best for the calling routine to check for the existence of the table and for
2650 sufficent user privileges to access the table in the mode (wxDB_QUERY_ONLY or
2651 !wxDB_QUERY_ONLY) before trying to open the table for the best possible
2652 return results as to why a table cannot be opened.
2654 \wxheading{See also
}
2656 \helpref{wxDb::TableExists
}{wxdbtableexists
},
\helpref{wxDb::TablePrivileges
}{wxdbtableprivileges
}
2659 \membersection{wxDbTable::OrderBy
}\label{wxdbtableorderby
}
2661 \func{const char *
}{OrderBy
}{}
2662 \func{void
}{OrderBy
}{\param{const wxString &
}{OrderBy
}}
2664 Accessor function for the private class member wxDbTable::orderBy. Can be
2665 used as a synonym for
\helpref{wxDbTable::GetOrderByClause
}{wxdbtablegetorderbyclause
}
2666 (the first form of this function) or
\helpref{wxDbTable::SetOrderByClause
}{wxdbtablesetorderbyclause
}
2667 (the second form of this function).
2669 \wxheading{Parameters
}
2671 \docparam{OrderBy
}{A comma separated list of column names that indicate the
2672 alphabetized sorting sequence that the result set is to be returned in. If
2673 a FROM clause has also been specified, each column name specified in the
2674 ORDER BY clause should be prefaced with the table name to which the column
2675 belongs using DOT notation (TABLE.COLUMN).
}
2677 \wxheading{Return value
}
2679 The first form of this function returns the current value of the wxDbTable
2680 member variable ::orderBy.
2682 The second form of the function has no return value.
2684 \wxheading{See also
}
2686 \helpref{wxDbTable::GetOrderByClause
}{wxdbtablegetorderbyclause
},
2687 \helpref{wxDbTable::SetFromClause
}{wxdbtablesetfromclause
}
2690 \membersection{wxDbTable::Query
}\label{wxdbtablequery
}
2692 \func{virtual bool
}{Query
}{\param{bool
}{forUpdate=FALSE
},
\param{bool
}{distinct=FALSE
}}
2694 \wxheading{Parameters
}
2696 \docparam{forUpdate
}{{\it OPTIONAL
}. Gives you the option of locking records
2697 as they are queried (SELECT ... FOR UPDATE). If the RDBMS is not capable of
2698 the FOR UPDATE clause, this argument is ignored. See
2699 \helpref{wxDbTable::CanSelectForUpdate
}{wxdbtablecanselectforupdate
} for
2700 additional information regarding this argument. Default is FALSE.
}
2701 \docparam{distinct
}{{\it OPTIONAL
}. Allows selection of only distinct values
2702 from the query (SELECT DISTINCT ... FROM ...). The notion of DISTINCT
2703 applies to all columns returned in the result set, not individual columns.
2708 This function queries records from the datasource based on the three
2709 wxDbTable members: "where", "orderBy", and "from". Use
2710 \helpref{wxDbTable::SetWhereClause
}{wxdbtablesetwhereclause
} to filter on
2711 records to be retrieved (e.g. All users with a first name of "JOHN").
2712 Use
\helpref{wxDbTable::SetOrderByClause
}{wxdbtablesetorderbyclause
} to
2713 change the sequence in which records are returned in the result set from
2714 the datasource (e.g. Ordered by LAST_NAME). Use
2715 \helpref{wxDbTable::SetFromClause
}{wxdbtablesetfromclause
} to allow outer
2716 joining of the base table (the one being associated with this instance of
2717 wxDbTable) with other tables which share a related field.
2719 After each of these clauses are set/cleared, call wxDbTable::Query() to
2720 fetch the result set from the datasource.
2722 This scheme has an advantage if you have to requery your record set
2723 frequently in that you only have to set your WHERE, ORDER BY, and FROM
2724 clauses once. Then to refresh the record set, simply call wxDbTable::Query()
2725 as frequently as needed.
2727 Note that repeated calls to wxDbTable::Query() may tax the database
2728 server and make your application sluggish if done too frequently or
2731 The base table name is automatically prepended to the base column names in
2732 the event that the FROM clause has been set (is non-null) using
2733 \helpref{wxDbTable::SetFromClause
}{wxdbtablesetfromclause
}.
2735 The cursor for the result set is positioned
{\it before
} the first record in
2736 the record set after the query. To retrieve the first record, call
2737 \helpref{wxDbTable::GetFirst
}{wxdbtablegetfirst
} or
2738 \helpref{wxDbTable::GetNext
}{wxdbtablegetnext
}. Typically, no data from the
2739 result set is returned to the client driver until a request to such as
2740 \helpref{wxDbTable::GetNext
}{wxdbtablegetnext
} is performed, so network
2741 traffic and database load are not overwhelmed transmitting data until the
2742 data is actually requested by the client. This behavior is solely dependent
2743 on the ODBC driver though, so refer to the ODBC driver's reference material
2744 for information on its behaviors.
2746 The wxDbTable::Query() function is defined as "virtual" so that it may be
2747 overridden for application specific purposes.
2749 \normalbox{Be sure and set the wxDbTable's "where", "orderBy", and "from"
2750 member variables to "" if they are not being used in the query. Otherwise,
2751 the results returned may have unexpected results (or no results) due to
2752 improper or incorrect query parameters constructed from the uninitialized
2758 // Incomplete code sample
2759 parts->SetWhereClause("DESCRIPTION = 'FOOD'");
2760 parts->SetOrderByClause("EXPIRATION_DATE");
2761 parts->SetFromClause("");
2762 // Query the records based on the where, orderBy and from clauses
2765 // Display all records queried
2766 while(parts->GetNext())
2767 dispPart(parts); // user defined function
2771 \membersection{wxDbTable::QueryBySqlStmt
}\label{wxdbtablequerybysqlstmt
}
2773 \func{bool
}{QueryBySqlStmt
}{\param{const char *
}{pSqlStmt
}}
2775 Performs a query against the datasource by accepting and passing verbatim the
2776 SQL SELECT statement passed to the function.
2778 \wxheading{Parameters
}
2780 \docparam{pSqlStmt
}{A pointer to the SQL SELECT statement to be executed.
}
2784 This is the most powerful form of the query functions available. This member
2785 function allows a programmer to write their own custom SQL SELECT statement
2786 for requesting data from the datasource. This gives the programmer access
2787 to the full power of SQL for performing operations such as scalar functions,
2788 aggregate functions, table joins, and sub-queries, as well as datasource
2789 specific function calls.
2791 The requirements of the SELECT statement are the following:
2794 1. Must return the correct number of columns. In the derived wxDbTable
2795 constructor, it is specified how many columns are in the wxDbTable
2796 object. The SELECT statement must return exactly that many columns.
2798 2. The columns must be returned in the same sequence as specified
2799 when defining the bounds columns using wxDbTable::SetColDefs(), and
2800 the columns returned must be of the proper data type. For example,
2801 if column
3 is defined in the wxDbTable bound column definitions to be a float,
2802 the SELECT statement must return a float for column
3 (e.g.
2803 PRICE *
1.10 to increase the price by
10%).
2805 3. The ROWID can be included in your SELECT statement as the last column
2806 selected, if the datasource supports it. Use wxDbTable::CanUpdByROWID()
2807 to determine if the ROWID can be selected from the datasource. If it
2808 can, much better performance can be achieved on updates and deletes
2809 by including the ROWID in the SELECT statement.
2812 Even though data can be selected from multiple tables in your select
2813 statement (joins), only the base table associated with this wxDbTable object
2814 is automatically updated through the ODBC class library. Data from multiple
2815 tables can be selected for display purposes however. Include columns in
2816 the wxDbTable object and mark them as non-updateable (See
2817 \helpref{wxDbColDef
}{wxdbcoldef
} for details). This way columns can be
2818 selected and displayed from other tables, but only the base table will be
2819 updated automatically through the
\helpref{wxDbTable::Update
}{wxdbtableupdate
}
2820 function. To update tables other than the base table, use the
2821 \helpref{wxDbTable::Update
}{wxdbtableupdate
} function passing a SQL statement.
2823 After this function has been called, the cursor is positioned before the
2824 first record in the record set. To retrieve the first record, call
2825 either
\helpref{wxDbTable::GetFirst
}{wxdbtablegetfirst
} or
2826 \helpref{wxDbTable::GetNext
}{wxdbtablegetnext
}.
2831 // Incomplete code samples
2832 strcpy(sqlStmt, "SELECT * FROM PARTS WHERE STORAGE_DEVICE = 'SD98' \
2833 AND CONTAINER =
12");
2834 // Query the records using the SQL SELECT statement above
2835 parts->QueryBySqlStmt(sqlStmt);
2836 // Display all records queried
2837 while(parts->GetNext())
2840 Example SQL statements
2841 ----------------------
2843 // Table Join returning
3 columns
2844 SELECT part_no, part_desc, sd_name
2845 from parts, storage_devices
2846 where parts.storage_device_id = storage_devices.storage_device_id
2848 // Aggregate function returning total number of parts in container
99
2849 SELECT count
(*) from PARTS where container = 99
2851 // Order by clause; ROWID, scalar function
2852 SELECT part_no, substring(part_desc, 1, 10), qty_on_hand + 1, ROWID
2854 where warehouse = 10
2855 order by part_no desc // descending order
2859 where container in (select container
2860 from storage_devices
2861 where device_id = 12)
2865 \membersection{wxDbTable::QueryMatching}\label{wxdbtablequerymatching}
2867 \func{virtual bool}{QueryMatching}{\param{bool }{forUpdate=FALSE}, \param{bool }{distinct=FALSE}}
2869 QueryMatching allows querying of records from the table associated with
2870 the wxDbTable object by matching "columns" to values.
2872 For example: To query the datasource for the row with a PART_NUMBER column
2873 value of "32", clear all column variables of the wxDbTable object, set the
2874 PartNumber variable that is bound to the PART_NUMBER column in the wxDbTable
2875 object to "32", and then call wxDbTable::QueryMatching().
2877 \wxheading{Parameters}
2879 \docparam{forUpdate}{{\it OPTIONAL}. Gives you the option of locking records
2880 as they are queried (SELECT ... FOR UPDATE). If the RDBMS is not capable of
2881 the FOR UPDATE clause, this argument is ignored. See
2882 \helpref{wxDbTable::CanSelectForUpdate}{wxdbtablecanselectforupdate} for
2883 additional information regarding this argument. Default is FALSE.}
2884 \docparam{distinct}{{\it OPTIONAL}. Allows selection of only distinct values
2885 from the query (SELECT DISTINCT ... FROM ...). The notion of DISTINCT
2886 applies to all columns returned in the result set, not individual columns.
2891 The SQL WHERE clause is built by the ODBC class library based on all
2892 non-zero/non-NULL columns in your wxDbTable object. Matches can be on one,
2893 many or all of the wxDbTable's columns. The base table name is prepended
2894 to the column names in the event that the wxDbTable's FROM clause is non-null.
2896 This function cannot be used to perform queries which will check for
2897 columns that are 0 or NULL, as the automatically constructed WHERE clause
2898 only will contain comparisons on column member variables that are
2901 The primary difference between this function and \helpref{wxDbTable::QueryOnKeyFields}{wxdbtablequeryonkeyfields}
2902 is that this function can query on any column(s) in the wxDbTable object.
2903 Note however that this may not always be very efficient. Searching on
2904 non-indexed columns will always require a full table scan.
2906 The cursor is positioned before the first record in the record set after
2907 the query is performed. To retrieve the first record, the program must call
2908 either \helpref{wxDbTable::GetFirst}{wxdbtablegetfirst} or
2909 \helpref{wxDbTable::GetNext}{wxdbtablegetnext}.
2914 // Incomplete code sample
2915 parts->ClearMemberVars(); // Set all columns to zero
2916 strcpy(parts->PartNumber,"32"); // Set columns to query on
2917 parts->OnHold = TRUE;
2918 parts->QueryMatching(); // Query
2919 // Display all records queried
2920 while(parts->GetNext())
2921 dispPart(parts); // Some application defined function
2925 \membersection{wxDbTable::QueryOnKeyFields}\label{wxdbtablequeryonkeyfields}
2927 \func{bool}{QueryOnKeyFields}{\param{bool }{forUpdate=FALSE}, \param{bool }{distinct=FALSE}}
2929 QueryOnKeyFields provides an easy mechanism to query records in the table
2930 associated with the wxDbTable object by the primary index column(s). Simply
2931 assign the primary index column(s) values and then call this member function
2932 to retrieve the record.
2934 Note that since primary indexes are always unique, this function implicitly
2935 always returns a single record from the database. The base table name is
2936 prepended to the column names in the event that the wxDbTable's FROM clause
2939 \wxheading{Parameters}
2941 \docparam{forUpdate}{{\it OPTIONAL}. Gives you the option of locking records
2942 as they are queried (SELECT ... FOR UPDATE). If the RDBMS is not capable of
2943 the FOR UPDATE clause, this argument is ignored. See
2944 \helpref{wxDbTable::CanSelectForUpdate}{wxdbtablecanselectforupdate} for
2945 additional information regarding this argument. Default is FALSE.}
2946 \docparam{distinct}{{\it OPTIONAL}. Allows selection of only distinct values
2947 from the query (SELECT DISTINCT ... FROM ...). The notion of DISTINCT
2948 applies to all columns returned in the result set, not individual columns.
2953 The cursor is positioned before the first record in the record set after
2954 the query is performed. To retrieve the first record, the program must call
2955 either \helpref{wxDbTable::GetFirst}{wxdbtablegetfirst} or
2956 \helpref{wxDbTable::GetNext}{wxdbtablegetnext}.
2961 // Incomplete code sample
2962 strcpy(parts->PartNumber, "32");
2963 parts->QueryOnKeyFields();
2964 // Display all records queried
2965 while(parts->GetNext())
2966 dispPart(parts); // Some application defined function
2970 \membersection{wxDbTable::Refresh}\label{wxdbtablerefresh}
2972 \func{bool}{Refresh}{\void}
2974 Refreshes (re-reads into the bound columns) the current record for the
2975 current active cursor of the wxDbTable object.
2977 The cursor position and result set are unaffected by calls to this function.
2978 (The one exception is in the case where the record to be refreshed has been
2979 deleted by some other user or transaction since it was originally retrieved
2980 as part of the result set. For most datasources, the default behavior in
2981 this situation is to return the value that was originally queried for the
2982 result set, even though it has been deleted from the database. But this is
2983 datasource dependent, and should be tested before relying on this behavior.)
2987 This routine is only guaranteed to work if the table has a unique primary
2988 index defined for it. Otherwise, more than one record may be fetched and
2989 there is no guarantee that the correct record will be refreshed. The
2990 table's columns are refreshed to reflect the current data in the database.
2993 \membersection{wxDbTable::SetColDefs}\label{wxdbtablesetcoldefs}
2995 \func{void}{SetColDefs}{\param{int }{index}, \param{const char *}{fieldName}, \param{int }{dataType}, \param{void *}{pData}, \param{int }{cType}, \param{int }{size}, \param{bool }{keyField = FALSE}, \param{bool }{upd = TRUE}, \param{bool }{insAllow = TRUE}, \param{bool }{derivedCol = FALSE}}
2997 \func{wxDbColDataPtr *}{SetColDefs}{\param{wxDbColInf *}{colInfs}, \param{ULONG }{numCols}}
2999 \wxheading{Parameters}
3001 \docparam{index}{Column number (0 to n-1, where n is the number of columns
3002 specified as being defined for this wxDbTable instance when the
3003 wxDbTable constructor was called.}
3004 \docparam{fieldName}{Column name from the associated data table.}
3005 \docparam{dataType}{Logical data type. Valid logical types include:}
3008 DB_DATA_TYPE_VARCHAR : strings
3009 DB_DATA_TYPE_INTEGER : non-floating point numbers
3010 DB_DATA_TYPE_FLOAT : floating point numbers
3011 DB_DATA_TYPE_DATE : dates
3014 \docparam{pData}{Pointer to the data object that will hold the column's
3015 value when a row of data is returned from the datasource.}
3016 \docparam{cType}{SQL C Type. This defines the data type that the SQL
3017 representation of the data is converted to to be stored in {\it pData}.
3018 Other valid types are available also, but these are the most common ones:}
3021 SQL_C_CHAR // strings
3031 SQL_C_BOOLEAN // defined in db.h
3032 SQL_C_ENUM // defined in db.h
3035 \docparam{size}{Maximum size in bytes of the {\it pData} object.}
3036 \docparam{keyField}{{\it OPTIONAL}. Indicates if this column is part of the
3037 primary index. Default is FALSE.}
3038 \docparam{upd}{{\it OPTIONAL}. Are updates allowed on this column?
3040 \docparam{insAllow}{{\it OPTIONAL}. Inserts allowed on this column?
3042 \docparam{derivedCol}{{\it OPTIONAL}. Is this a derived column (non base
3043 table column for query only)? Default is FALSE.}
3045 \docparam{colInfs}{Pointer to an array of wxDbColInf instances which contains
3046 all the information necessary to create {\it numCols} column definitions.}
3047 \docparam{numCols}{Number of elements of wxDbColInf type that are pointed
3048 to by {\it colInfs}, which are to have column definitions created from them.}
3052 If {\it pData} is to hold a string of characters, be sure to include enough
3053 space for the NULL terminator in pData and in the byte count of {\it size}.
3055 Both forms of this function provide a shortcut for defining the columns in
3056 your wxDbTable object. Use this function in any derived wxDbTable
3057 constructor when describing the column/columns in the wxDbTable object.
3059 The second form of this function is primarily used when the
3060 \helpref{wxDb::GetColumns}{wxdbgetcolumns} function was used to query the
3061 datasource for the column definitions, so that the column definitions are
3062 already stored in wxDbColInf form. One example use of using
3063 \helpref{wxDb::GetColumns}{wxdbgetcolumns} then using this function is if
3064 a data table existed in one datasource, and the table's column definitions
3065 were to be copied over to another datasource or table.
3070 // Long way not using this function
3071 strcpy(colDefs[0].ColName, "PART_NO");
3072 colDefs[0].DbDataType = DB_DATA_TYPE_VARCHAR;
3073 colDefs[0].PtrDataObj = PartNumber;
3074 colDefs[0].SqlCtype = SQL_C_CHAR;
3075 colDefs[0].SzDataObj = PART_NUMBER_LEN;
3076 colDefs[0].KeyField = TRUE;
3077 colDefs[0].Updateable = FALSE;
3078 colDefs[0].InsertAllowed= TRUE;
3079 colDefs[0].DerivedCol = FALSE;
3081 // Shortcut using this function
3082 SetColDefs(0, "PART_NUMBER", DB_DATA_TYPE_VARCHAR, PartNumber,
3083 SQL_C_CHAR, PART_NUMBER_LEN, TRUE, FALSE,TRUE,FALSE);
3087 \membersection{wxDbTable::SetCursor}\label{wxdbtablesetcursor}
3089 \func{bool}{Open}{\param{HSTMT *}{hstmtActivate = (void **) wxDB_DEFAULT_CURSOR
}}
3091 \wxheading{Parameters
}
3093 \docparam{hstmtActivate
}{{\it OPTIONAL
}. Pointer to the cursor that is to
3094 become the current cursor. Passing no cursor handle will reset the cursor
3095 back to the wxDbTable's default (original) cursor that was created when the
3096 wxDbTable instance was first created. Default is wxDB_DEFAULT_CURSOR.
}
3100 When swapping between cursors, the member variables of the wxDbTable object
3101 are automatically refreshed with the column values of the row that the
3102 current cursor is positioned at (if any). If the cursor is not positioned,
3103 then the data in member variables is undefined.
3105 The only way to return back to the cursor that was in use before this
3106 function was called is to programmatically determine the current cursor
3107 BEFORE calling this function using
\helpref{wxDbTable::GetCursor
}{wxdbtablegetcursor
}
3108 and saving a pointer to that cursor.
3110 \wxheading{See also
}
3112 \helpref{wxDbTable::GetNewCursor
}{wxdbtablegetnewcursor
},
\helpref{wxDbTable::GetCursor
}{wxdbtablegetcursor
},
3113 \helpref{wxDbTable::SetCursor
}{wxdbtablesetcursor
}
3116 \membersection{wxDbTable::SetFromClause
}\label{wxdbtablesetfromclause
}
3118 \func{void
}{SetFromClause
}{\param{const wxString &
}{From
}}
3120 Accessor function for setting the private class member wxDbTable::from
3121 that indicates what other tables should be outer joined with the wxDbTable's
3122 base table for access to the columns in those other tables.
3124 Synonym to this function is one form of
\helpref{wxDbTable::From
}{wxdbtablefrom
}
3126 \wxheading{Parameters
}
3128 \docparam{From
}{A comma separated list of table names that are to be outer
3129 joined with the base table's columns so that the joined table's columns
3130 may be returned in the result set or used as a portion of a comparison with
3131 the base table's columns. NOTE that the base tables name must NOT be included
3132 in the FROM clause, as it is automatically included by the wxDbTable class
3133 in constructing query statements.
}
3137 Used by the
\helpref{wxDbTable::Query
}{wxdbtablequery
} and
3138 \helpref{wxDbTable::Count
}{wxdbtablecount
} member functions to allow outer
3139 joining of records from multiple tables.
3141 Do
{\bf not
} include the keyword "FROM" when setting the FROM clause.
3143 If using the FROM clause when performing a query, be certain to include in
3144 the corresponding WHERE clause a comparison of a column from either the base
3145 table or one of the other joined tables to each other joined table to ensure
3146 the datasource knows on which column values the tables should be joined on.
3152 // Base table is the "LOCATION" table, and it is being
3153 // outer joined to the "PART" table via the the field "PART_NUMBER"
3154 // that can be related between the two tables.
3155 location->SetWhereClause("LOCATION.PART_NUMBER = PART.PART_NUMBER")
3156 location->SetFromClause("PART");
3160 \wxheading{See also
}
3162 \helpref{wxDbTable::From
}{wxdbtablefrom
},
3163 \helpref{wxDbTable::SetWhereClause
}{wxdbtablesetwhereclause
}
3166 \membersection{wxDbTable::SetNull
}\label{wxdbtablesetnull
}
3168 \func{bool
}{SetNull
}{\param{int
}{colNo
}}
3170 \func{bool
}{SetNull
}{\param{const char *
}{colName
}}
3172 Both forms of this function allow a member variable representing a column
3173 in the table associated with this wxDbTable object to be set to NULL.
3175 The first form allows the column to be set by the index into the column
3176 definitions used to create the wxDbTable instance, while the second allows
3177 the actual column name to be specified.
3179 \wxheading{Parameters
}
3181 \docparam{colNo
}{Index into the column definitions used when first defining
3182 this wxDbTable object.
}
3183 \docparam{colName
}{Actual data table column name that is to be set to NULL.
}
3187 No database updates are done by this function. It only operates on the
3188 member variables in memory. Use and insert or update function to store this
3192 \membersection{wxDbTable::SetOrderByClause
}\label{wxdbtablesetorderbyclause
}
3194 \func{void
}{SetOrderByClause
}{\param{const wxString &
}{OrderBy
}}
3196 Accessor function for setting the private class member wxDbTable::orderBy
3197 which determines sequence/ordering of the rows returned in the result set
3200 Synonym to this function is one form of
\helpref{wxDbTable::OrderBy
}{wxdbtableorderby
}
3202 \wxheading{Parameters
}
3204 \docparam{OrderBy
}{A comma separated list of column names that indicate the
3205 alphabetized sorting sequence that the result set is to be returned in. If
3206 a FROM clause has also been specified, each column name specified in the
3207 ORDER BY clause should be prefaced with the table name to which the column
3208 belongs using DOT notation (TABLE.COLUMN).
}
3212 Do
{\bf not
} include the keywords "ORDER BY" when setting the ORDER BY clause.
3218 parts->SetOrderByClause("PART_DESCRIP, QUANTITY");
3222 location->SetOrderByClause("LOCATION.POSITION, PART.PART_NUMBER);
3226 \wxheading{See also
}
3228 \helpref{wxDbTable::OrderBy
}{wxdbtableorderby
},
3229 \helpref{wxDbTable::SetFromClause
}{wxdbtablesetfromclause
}
3232 \membersection{wxDbTable::SetQueryTimeout
}\label{wxdbtablesetquerytimeout
}
3234 \func{bool
}{SetQueryTimeout
}{\param{UDWORD
}{nSeconds
}}
3236 Allows a time period to be set as the timeout period for queries.
3238 \wxheading{Parameters
}
3240 \docparam{nSeconds
}{The number of seconds to wait for the query to complete
3245 Neither Oracle or Access support this function as of yet. Other databases
3246 should be evaluated for support before depending on this function working
3250 \membersection{wxDbTable::SetWhereClause
}\label{wxdbtablesetwhereclause
}
3252 \func{void
}{SetWhereClause
}{\param{const wxString &
}{Where
}}
3254 Accessor function for setting the private class member wxDbTable::where
3255 that determines which rows are returned in the result set by the datasource.
3257 Synonym to this function is one form of
\helpref{wxDbTable::Where
}{wxdbtablewhere
}
3259 \wxheading{Parameters
}
3261 \docparam{Where
}{SQL "where" clause. This clause can contain any SQL language
3262 that is legal in standard where clauses. If a FROM clause has also been
3263 specified, each column name specified in the ORDER BY clause should be
3264 prefaced with the table name to which the column belongs using DOT notation
3269 Do
{\bf not
} include the keywords "WHERE" when setting the WHERE clause.
3275 // Simple where clause
3276 parts->SetWhereClause("PART_NUMBER = '
32'");
3278 // Any comparison operators
3279 parts->SetWhereClause("PART_DESCRIP LIKE 'HAMMER
%'");
3281 // Multiple comparisons, including a function call
3282 parts->Where("QTY >
0 AND
{fn UCASE(PART_DESCRIP)
} LIKE '
%DRILL%'");
3284 // Using parameters and multiple logical combinations
3285 parts->Where("((QTY >
10) OR (ON_ORDER >
0)) AND ON_HOLD =
0");
3287 // This query uses an outer join (requiring a FROM clause also)
3288 // that joins the PART and LOCATION table on he common field
3290 parts->Where("PART.ON_HOLD =
0 AND \
3291 PART.PART_NUMBER = LOCATION.PART_NUMBER AND \
3292 LOCATION.PART_NUMBER >
0");
3296 \membersection{wxDbTable::Update
}\label{wxdbtableupdate
}
3298 \func{bool
}{Update
}{\void}
3300 \func{bool
}{Update
}{\param{const char *
}{pSqlStmt
}}
3302 This member function
3304 The first form of this function will update the row that the current cursor
3305 is currently positioned at with the values in the memory variables that
3306 are bound to the columns. The actual SQL statement to perform the update
3307 is automatically created by the ODBC class, and then executed.
3309 The second form of the function allows full access through SQL statements for
3310 updating records in the database. Write any valid SQL UPDATE statement and
3311 submit it to this function for execution. Sophisticated updates can be
3312 performed using the full power of the SQL dialect. The full SQL statement
3313 must have the exact syntax required by the driver/datasource for performing
3314 the update. This usually is in the form of:
3317 UPDATE tablename SET col1=X, col2=Y, ... where ...
3320 \wxheading{Parameters
}
3322 \docparam{pSqlStmt
}{Pointer to SQL UPDATE statement to be executed.
}
3326 Note that using this function when it is associated with the table that the
3327 wxDbTable instance is associated with does not prevent updating
3328 columns in any other table in the database for which the connected user
3329 has update privileges on. Constructing the appropriate full SQL statement,
3330 columns in other tables can also be updated as well.
3332 A
\helpref{wxDb::CommitTrans
}{wxdbcommittrans
} or
3333 \helpref{wxDb::RollbackTrans
}{wxdbrollbacktrans
} must be called after use of
3334 this function to commit or rollback the update.
3339 strcpy(sqlStmt, "update PART set QTY =
0 where PART_NUMBER = '
32'");
3343 \membersection{wxDbTable::UpdateWhere
}\label{wxdbtableupdatewhere
}
3345 \func{bool
}{UpdateWhere
}{\param{const char *
}{pWhereClause
}}
3347 Performs updates to the base table of the wxDbTable object, updating only the
3348 rows which match the criteria specified in the
{\it pWhereClause
}.
3350 All columns that are bound to member variables for this wxDbTable instance
3351 that were defined with the "updateable" parameter set to TRUE will be updated
3352 with the information currently held in the memory variable.
3354 \wxheading{Parameters
}
3356 \docparam{pWhereClause
}{Pointer to a valid SQL WHERE clause. Do not
3357 include the keyword 'WHERE'.
}
3361 Care should be used when updating columns that are part of indexes with
3362 this function so as not to violate an unique key constraints.
3364 A
\helpref{wxDb::CommitTrans
}{wxdbcommittrans
} or
3365 \helpref{wxDb::RollbackTrans
}{wxdbrollbacktrans
} must be called after use of
3366 this function to commit or rollback the update(s).
3369 \membersection{wxDbTable::operator $++$
}\label{wxdbtableplusplus
}
3371 \func{bool
}{operator $++$
}{\void}
3373 Synonym for
\helpref{wxDbTable::GetNext
}{wxdbtablegetnext
}
3375 \wxheading{See also
}
3377 \helpref{wxDbTable::GetNext
}{wxdbtablegetnext
}
3380 \membersection{wxDbTable::operator $--$
}\label{wxdbtableminusminus
}
3382 \func{bool
}{operator $--$
}{\void}
3384 Synonym for
\helpref{wxDbTable::GetPrev
}{wxdbtablegetprev
}
3386 \wxheading{See also
}
3388 \helpref{wxDbTable::GetPrev
}{wxdbtablegetprev
}
3391 \section{\class{wxDbTableInf
}}\label{wxdbtableinf
}
3393 Currently only used by wxDb::GetCatalog() internally and wxDbInf class,
3394 but may be used in future releases for user functions. Contains information
3395 describing the table (Name, type, etc). A pointer to a wxDbColInf array
3396 instance is included so a program can create a wxDbColInf array instance
3397 (using
\helpref{wxDb::GetColumns
}{wxdbgetcolumns
}) to maintain all information about the columns
3398 of a table in one memory structure.
projects / wxWidgets.git / blob