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.
10 \wxheading{Include files
}
15 \wxheading{Helper classes and data structures
}
17 The following classes and structs are defined in db.cpp/.h for use with the wxDb class.
19 \begin{itemize
}\itemsep=
0pt
20 \item \helpref{wxDbColFor
}{wxdbcolfor
}
21 \item \helpref{wxDbColInf
}{wxdbcolinf
}
22 \item \helpref{wxDbTableInf
}{wxdbtableinf
}
23 \item \helpref{wxDbInf
}{wxdbinf
}
29 NOTE: In a future release, all ODBC class constants will be prefaced with 'wx'
33 wxDB_PATH_MAX Maximum path length allowed to be passed to
34 the ODBC driver to indicate where the data
37 DB_MAX_COLUMN_NAME_LEN Maximum supported length for the name of a
40 DB_MAX_ERROR_HISTORY Maximum number of error messages retained in
41 the queue before being overwritten by new
44 DB_MAX_ERROR_MSG_LEN Maximum supported length of an error message
45 returned by the ODBC classes
47 DB_MAX_STATEMENT_LEN Maximum supported length for a complete SQL
48 statement to be passed to the ODBC driver
50 DB_MAX_TABLE_NAME_LEN Maximum supported length for the name of a
53 DB_MAX_WHERE_CLAUSE_LEN Maximum supported WHERE clause length that
54 can be passed to the ODBC driver
56 DB_TYPE_NAME_LEN Maximum length of the name of a column's
61 \subsection{Enumerated types
}\label{wxdbenumeratedtypes
}
62 \wxheading{Enumerated types
}
64 \docparam{enum
{\bf wxDbSqlLogState
}}{sqlLogOFF, sqlLogON
}
66 {\it enum
{\bf wxDBMS
}}
68 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.
73 dbmsSYBASE_ASA // Adaptive Server Anywhere
74 dbmsSYBASE_ASE // Adaptive Server Enterprise
86 See the remarks in
\helpref{wxDb::Dbms
}{wxdbdbms
} for exceptions/issues with each of these database engines.
89 \wxheading{Public member variables
}
91 \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.
}
93 \docparam{int
{\bf wxDb::DB
\_STATUS}}{The last ODBC error/status that occurred on this data connection. Possible codes are:
}
96 DB_ERR_GENERAL_WARNING // SqlState = '
01000'
97 DB_ERR_DISCONNECT_ERROR // SqlState = '
01002'
98 DB_ERR_DATA_TRUNCATED // SqlState = '
01004'
99 DB_ERR_PRIV_NOT_REVOKED // SqlState = '
01006'
100 DB_ERR_INVALID_CONN_STR_ATTR // SqlState = '
01S00'
101 DB_ERR_ERROR_IN_ROW // SqlState = '
01S01'
102 DB_ERR_OPTION_VALUE_CHANGED // SqlState = '
01S02'
103 DB_ERR_NO_ROWS_UPD_OR_DEL // SqlState = '
01S03'
104 DB_ERR_MULTI_ROWS_UPD_OR_DEL // SqlState = '
01S04'
105 DB_ERR_WRONG_NO_OF_PARAMS // SqlState = '
07001'
106 DB_ERR_DATA_TYPE_ATTR_VIOL // SqlState = '
07006'
107 DB_ERR_UNABLE_TO_CONNECT // SqlState = '
08001'
108 DB_ERR_CONNECTION_IN_USE // SqlState = '
08002'
109 DB_ERR_CONNECTION_NOT_OPEN // SqlState = '
08003'
110 DB_ERR_REJECTED_CONNECTION // SqlState = '
08004'
111 DB_ERR_CONN_FAIL_IN_TRANS // SqlState = '
08007'
112 DB_ERR_COMM_LINK_FAILURE // SqlState = '
08S01'
113 DB_ERR_INSERT_VALUE_LIST_MISMATCH // SqlState = '
21S01'
114 DB_ERR_DERIVED_TABLE_MISMATCH // SqlState = '
21S02'
115 DB_ERR_STRING_RIGHT_TRUNC // SqlState = '
22001'
116 DB_ERR_NUMERIC_VALUE_OUT_OF_RNG // SqlState = '
22003'
117 DB_ERR_ERROR_IN_ASSIGNMENT // SqlState = '
22005'
118 DB_ERR_DATETIME_FLD_OVERFLOW // SqlState = '
22008'
119 DB_ERR_DIVIDE_BY_ZERO // SqlState = '
22012'
120 DB_ERR_STR_DATA_LENGTH_MISMATCH // SqlState = '
22026'
121 DB_ERR_INTEGRITY_CONSTRAINT_VIOL // SqlState = '
23000'
122 DB_ERR_INVALID_CURSOR_STATE // SqlState = '
24000'
123 DB_ERR_INVALID_TRANS_STATE // SqlState = '
25000'
124 DB_ERR_INVALID_AUTH_SPEC // SqlState = '
28000'
125 DB_ERR_INVALID_CURSOR_NAME // SqlState = '
34000'
126 DB_ERR_SYNTAX_ERROR_OR_ACCESS_VIOL // SqlState = '
37000'
127 DB_ERR_DUPLICATE_CURSOR_NAME // SqlState = '
3C000'
128 DB_ERR_SERIALIZATION_FAILURE // SqlState = '
40001'
129 DB_ERR_SYNTAX_ERROR_OR_ACCESS_VIOL2 // SqlState = '
42000'
130 DB_ERR_OPERATION_ABORTED // SqlState = '
70100'
131 DB_ERR_UNSUPPORTED_FUNCTION // SqlState = 'IM001'
132 DB_ERR_NO_DATA_SOURCE // SqlState = 'IM002'
133 DB_ERR_DRIVER_LOAD_ERROR // SqlState = 'IM003'
134 DB_ERR_SQLALLOCENV_FAILED // SqlState = 'IM004'
135 DB_ERR_SQLALLOCCONNECT_FAILED // SqlState = 'IM005'
136 DB_ERR_SQLSETCONNECTOPTION_FAILED // SqlState = 'IM006'
137 DB_ERR_NO_DATA_SOURCE_DLG_PROHIB // SqlState = 'IM007'
138 DB_ERR_DIALOG_FAILED // SqlState = 'IM008'
139 DB_ERR_UNABLE_TO_LOAD_TRANSLATION_DLL // SqlState = 'IM009'
140 DB_ERR_DATA_SOURCE_NAME_TOO_LONG // SqlState = 'IM010'
141 DB_ERR_DRIVER_NAME_TOO_LONG // SqlState = 'IM011'
142 DB_ERR_DRIVER_KEYWORD_SYNTAX_ERROR // SqlState = 'IM012'
143 DB_ERR_TRACE_FILE_ERROR // SqlState = 'IM013'
144 DB_ERR_TABLE_OR_VIEW_ALREADY_EXISTS // SqlState = 'S0001'
145 DB_ERR_TABLE_NOT_FOUND // SqlState = 'S0002'
146 DB_ERR_INDEX_ALREADY_EXISTS // SqlState = 'S0011'
147 DB_ERR_INDEX_NOT_FOUND // SqlState = 'S0012'
148 DB_ERR_COLUMN_ALREADY_EXISTS // SqlState = 'S0021'
149 DB_ERR_COLUMN_NOT_FOUND // SqlState = 'S0022'
150 DB_ERR_NO_DEFAULT_FOR_COLUMN // SqlState = 'S0023'
151 DB_ERR_GENERAL_ERROR // SqlState = 'S1000'
152 DB_ERR_MEMORY_ALLOCATION_FAILURE // SqlState = 'S1001'
153 DB_ERR_INVALID_COLUMN_NUMBER // SqlState = 'S1002'
154 DB_ERR_PROGRAM_TYPE_OUT_OF_RANGE // SqlState = 'S1003'
155 DB_ERR_SQL_DATA_TYPE_OUT_OF_RANGE // SqlState = 'S1004'
156 DB_ERR_OPERATION_CANCELLED // SqlState = 'S1008'
157 DB_ERR_INVALID_ARGUMENT_VALUE // SqlState = 'S1009'
158 DB_ERR_FUNCTION_SEQUENCE_ERROR // SqlState = 'S1010'
159 DB_ERR_OPERATION_INVALID_AT_THIS_TIME // SqlState = 'S1011'
160 DB_ERR_INVALID_TRANS_OPERATION_CODE // SqlState = 'S1012'
161 DB_ERR_NO_CURSOR_NAME_AVAIL // SqlState = 'S1015'
162 DB_ERR_INVALID_STR_OR_BUF_LEN // SqlState = 'S1090'
163 DB_ERR_DESCRIPTOR_TYPE_OUT_OF_RANGE // SqlState = 'S1091'
164 DB_ERR_OPTION_TYPE_OUT_OF_RANGE // SqlState = 'S1092'
165 DB_ERR_INVALID_PARAM_NO // SqlState = 'S1093'
166 DB_ERR_INVALID_SCALE_VALUE // SqlState = 'S1094'
167 DB_ERR_FUNCTION_TYPE_OUT_OF_RANGE // SqlState = 'S1095'
168 DB_ERR_INF_TYPE_OUT_OF_RANGE // SqlState = 'S1096'
169 DB_ERR_COLUMN_TYPE_OUT_OF_RANGE // SqlState = 'S1097'
170 DB_ERR_SCOPE_TYPE_OUT_OF_RANGE // SqlState = 'S1098'
171 DB_ERR_NULLABLE_TYPE_OUT_OF_RANGE // SqlState = 'S1099'
172 DB_ERR_UNIQUENESS_OPTION_TYPE_OUT_OF_RANGE // SqlState = 'S1100'
173 DB_ERR_ACCURACY_OPTION_TYPE_OUT_OF_RANGE // SqlState = 'S1101'
174 DB_ERR_DIRECTION_OPTION_OUT_OF_RANGE // SqlState = 'S1103'
175 DB_ERR_INVALID_PRECISION_VALUE // SqlState = 'S1104'
176 DB_ERR_INVALID_PARAM_TYPE // SqlState = 'S1105'
177 DB_ERR_FETCH_TYPE_OUT_OF_RANGE // SqlState = 'S1106'
178 DB_ERR_ROW_VALUE_OUT_OF_RANGE // SqlState = 'S1107'
179 DB_ERR_CONCURRENCY_OPTION_OUT_OF_RANGE // SqlState = 'S1108'
180 DB_ERR_INVALID_CURSOR_POSITION // SqlState = 'S1109'
181 DB_ERR_INVALID_DRIVER_COMPLETION // SqlState = 'S1110'
182 DB_ERR_INVALID_BOOKMARK_VALUE // SqlState = 'S1111'
183 DB_ERR_DRIVER_NOT_CAPABLE // SqlState = 'S1C00'
184 DB_ERR_TIMEOUT_EXPIRED // SqlState = 'S1T00'
187 \docparam{struct
{\bf wxDb::dbInf
}}{This structure is internal to the
188 wxDb class and contains details of the ODBC datasource that the current
189 instance of the wxDb is connected to in its members. When the datasource
190 is opened, all of the information contained in the dbInf structure is
191 queried from the datasource. This information is used almost exclusively
192 within the ODBC class library. Where there may be a need for particular
193 portions of this information outside of the class library, member
195 \helpref{wxDbTable::IsCursorClosedOnCommit
}{wxdbtableiscursorclosedoncommit
})
196 have been added for ease of use.
}
199 char dbmsName
[40] - Name of the dbms product
200 char dbmsVer
[64] - Version # of the dbms product
201 char driverName
[40] - Driver name
202 char odbcVer
[60] - ODBC version of the driver
203 char drvMgrOdbcVer
[60] - ODBC version of the driver manager
204 char driverVer
[60] - Driver version
205 char serverName
[80] - Server Name, typically a connect string
206 char databaseName
[128] - Database filename
207 char outerJoins
[2] - Does datasource support outer joins
208 char procedureSupport
[2] - Does datasource support stored
210 UWORD maxConnections - Maximum # of connections datasource
212 UWORD maxStmts - Maximum # of HSTMTs per HDBC
213 UWORD apiConfLvl - ODBC API conformance level
214 UWORD cliConfLvl - Is datasource SAG compliant
215 UWORD sqlConfLvl - SQL conformance level
216 UWORD cursorCommitBehavior - How cursors are affected on db commit
217 UWORD cursorRollbackBehavior - How cursors are affected on db
219 UWORD supportNotNullClause - Does datasource support NOT NULL
221 char supportIEF
[2] - Integrity Enhancement Facility (Ref.
223 UDWORD txnIsolation - Transaction isolation level supported by
225 UDWORD txnIsolationOptions - Transaction isolation level options
227 UDWORD fetchDirections - Fetch directions supported
228 UDWORD lockTypes - Lock types supported in SQLSetPos
229 UDWORD posOperations - Position operations supported in
231 UDWORD posStmts - Position statements supported
232 UDWORD scrollConcurrency - Scrollable cursor concurrency options
234 UDWORD scrollOptions - Scrollable cursor options supported
235 UDWORD staticSensitivity - Can additions/deletions/updates be
237 UWORD txnCapable - Indicates if datasource supports
239 UDWORD loginTimeout - Number seconds to wait for a login
243 \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.
}
245 \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.
}
247 \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.
}
249 \docparam{wxChar
{\bf wxDb::sqlState
}[20]}{Set by wxDb::TranslateSqlState(). Indicates the error state after a failed ODBC operation. Used for reporting ODBC errors.
}
253 Default cursor scrolling is defined by wxODBC_FWD_ONLY_CURSORS in setup.h
254 when the wxWindows library is built. This behavior can be overridden when
255 an instance of a wxDb is created (see
\helpref{wxDb constructor
}{wxdbconstr
}).
256 Default setting of this value TRUE, as not all databases/drivers support
257 both types of cursors.
261 \helpref{wxDbColFor
}{wxdbcolfor
},
\helpref{wxDbColInf
}{wxdbcolinf
},
\helpref{wxDbTable
}{wxdbtable
},
\helpref{wxDbTableInf
}{wxdbtableinf
},
\helpref{wxDbInf
}{wxdbinf
}
263 \subsection{Associated non-class functions
}\label{wxdbfunctions
}
265 The following functions are used in conjunction with the wxDb class.
267 \func{wxDb *
}{wxDbGetConnection
}{\param{wxDbConnectInf *
}{pDbConfig
},
\param{bool
}{FwdOnlyCursors=(bool)wxODBC_FWD_ONLY_CURSORS
}}
271 This function is used to request a "new" wxDb instance for use by the program.
272 The wxDb instance returned is also opened (see
\helpref{wxDb::Open
}{wxdbopen
}).
274 This function (along with wxDbFreeConnection() and wxDbCloseConnection())
275 maintain a cached of wxDb instances for user/re-use by a program. When a
276 program needs a wxDb instance, it may call this function to obtain a wxDb
277 instance. If there is a wxDb instance in the cache that is currently unused
278 that matches the connection requirements specified in
{\it'pDbConfig'
} then
279 that cached connection is marked as no longer being free, and a pointer to
280 the wxDb instance is returned.
282 If there are no connections available in the cache that meet the requirements
283 given in
{\it'pDbConfig'
}, then a new wxDb instance is created to connect
284 to the datasource specified in
{\it'pDbConfig'
} using the userID and password
285 given in
{\it'pDbConfig'
}.
287 NOTE: The caching routine also uses the
\helpref{wxDb::Open
}{wxdbopen
}
288 connection datatype copying code. If the call to wxDbGetConnection()
289 requests a connection to a datasource, and there is not one available in the
290 cache, a new connection is created. But when the connection is opened,
291 instead of polling the datasource over again for its datatypes, if a
292 connection to the same datasource (using the same userID/password) has already
293 been done previously, the new connection skips querying the datasource for
294 its datatypes, and uses the same datatypes determined previously by the
295 other connection(s) for that same datasource. This cuts down greatly on
296 network traffic, database load, and connection creation time.
298 When the program is done using a connection created through a call to
299 wxDbGetConnection(), the program shoudl call wxDbFreeConnection() to release
300 the wxDb instance back to the cache. DO NOT DELETE THE wxDb INSTANCE!
301 Deleting the wxDb instance returned can cause a crash/memory corruption
302 later in the program when the cache is cleaned up.
304 When exiting the program, call wxDbCloseConnections() to close all the
305 cached connections created by calls to wxDbGetConnection().
308 \func{bool
}{wxDbFreeConnection
}{\param{wxDb *
}{pDb
}}
312 Searches the list of cached database connections connection for one matching
313 the passed in wxDb instance. If found, that cached connection is freed.
315 Freeing a connection means that it is marked as available (free) in the
316 cache of connections, so that a call to
\helpref{wxDbGetConnection
}{wxdbfunctions
}
317 is able to return a pointer to the wxDb instance for use. Freeing a
318 connection does NOT close the connection, it only makes the connection
322 \func{void
}{wxDbCloseConnections
}{\void}
326 Closes all cached connections that have been made through use of the
327 \helpref{wxDbGetConnection
}{wxdbfunctions
} function.
329 NOTE: These connections are closed regardless of whether they are in use
330 or not. This function should only be called after the program has
331 finished using the connections and all wxDbTable instances that use any of
332 the connections have been closed.
334 This function performs a
\helpref{wxDb::CommitTrans
}{wxdbcommittrans
}
335 on the connection before closing it to commit any changes that are still
336 pending, as well as to avoid any function sequence errors upon closing
340 \func{int
}{wxDbConnectionsInUse
}{\void}
344 Returns a count of how many database connections are currently free ( not
345 being used) that have been cached through use of the
\helpref{wxDbGetConnection
}{wxdbfunctions
}
349 \func{bool
}{wxDbSqlLog
}{\param{wxDbSqlLogState
}{state
},
\param{const wxChar *
}{filename = SQL_LOG_FILENAME
}}
353 This function sets the sql log state for all open wxDb objects
356 \func{bool
}{wxDbGetDataSource
}{\param{HENV
}{henv
},
\param{char *
}{Dsn
},
\param{SWORD
}{DsnMax
},
\param{char *
}{DsDesc
},
\param{SWORD
}{DsDescMax
},
\param{UWORD
}{direction = SQL_FETCH_NEXT
}}
360 This routine queries the ODBC driver manager for a list of available
361 datasources. Repeatedly call this function to obtain all the datasources
362 available through the ODBC driver manager on the current workstation.
364 wxStringList strList;
366 while (wxDbGetDataSource(DbConnectInf.Henv, Dsn, SQL_MAX_DSN_LENGTH+
1, DsDesc,
255))
370 \latexignore{\rtfignore{\wxheading{Members
}}}
372 \membersection{wxDb::wxDb
}\label{wxdbconstr
}
378 \func{}{wxDb
}{\param{HENV &
}{aHenv
},
\param{bool
}{FwdOnlyCursors=(bool)wxODBC_FWD_ONLY_CURSORS
}}
380 Constructor, used to create an ODBC connection to a datasource.
382 \wxheading{Parameters
}
384 \docparam{aHenv
}{Environment handle used for this connection.
}
386 \docparam{FwdOnlyCursors
}{Will cursors created for use with this datasource connection only allow forward scrolling cursors.
}
390 This is the constructor for the wxDb class. The wxDb object must
391 be created and opened before any database activity can occur.
396 wxDbConnectInf ConnectInf;
397 ....Set values for member variables of ConnectInf here
399 wxDb sampleDB(ConnectInf.Henv);
400 if (!sampleDB.Open(ConnectInf.Dsn, ConnectInf.Uid, ConnectInf.AuthStr))
402 // Error opening datasource
408 \helpref{wxDbGetConnection
}{wxdbfunctions
},
411 \membersection{wxDb::Catalog
}\label{wxdbcatalog
}
413 \func{bool
}{Catalog
}{\param{char *
}{ userID
},
\param{char *
}{fileName = SQL_CATALOG_FILENAME
}}
415 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.
417 \wxheading{Parameters
}
419 \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.
}
421 \docparam{fileName
}{{\it OPTIONAL
}. Name of the text file to create and write
422 the DB catalog to. Default is SQL_CATALOG_FILENAME.
}
424 \wxheading{Return value
}
426 Returns TRUE if the catalog request was successful, or FALSE if there was some
427 reason that the catalog could not be generated.
432 ============== ============== ================ ========= =======
433 TABLE NAME COLUMN NAME DATA TYPE PRECISION LENGTH
434 ============== ============== ================ ========= =======
435 EMPLOYEE RECID (
0008)NUMBER
15 8
436 EMPLOYEE USER_ID (
0012)VARCHAR2
13 13
437 EMPLOYEE FULL_NAME (
0012)VARCHAR2
26 26
438 EMPLOYEE PASSWORD (
0012)VARCHAR2
26 26
439 EMPLOYEE START_DATE (
0011)DATE
19 16
443 \membersection{wxDb::Close
}\label{wxdbclose
}
445 \func{void
}{Close
}{\void}
447 Closes the database connection.
451 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.
453 Any wxDbTable instances which use this connection must be deleted before closing the database connection.
458 // Commit any open transactions on the datasource
459 sampleDB.CommitTrans();
461 // Delete any remaining wxDbTable objects allocated with new
464 // Close the wxDb connection when finished with it
467 // Free Environment Handle that ODBC uses
469 // Be certain that no wxDb instances still exist
470 // that were created using this handle! If you
471 // use wxDbGetConnection() to get all the wxDb
472 // instances, you can use wxDbConnectionsInUse()
473 // to find out if any connections have not been
474 // closed before calling this
475 if (SQLFreeEnv(Db.Henv) != SQL_SUCCESS)
477 // Error freeing environment handle
482 \membersection{wxDb::CommitTrans
}\label{wxdbcommittrans
}
484 \func{bool
}{CommitTrans
}{\void}
486 Permanently "commits" changes (insertions/deletions/updates) to the database.
488 \wxheading{Return value
}
490 Returns TRUE if the commit was successful, or FALSE if the commit failed.
494 Transactions begin implicitly as soon as you make a change to the database
495 with an insert/update/delete, or any other direct SQL command that performs
496 one of these operations against the datasource.
497 At any time thereafter, to save the changes to disk permanently, "commit"
498 them by calling this function.
500 Calling this member function commits ALL open transactions on this ODBC
501 connection. For example, if three different wxDbTable instances used the
502 same connection to the datasource, commiting changes made on one of those
503 wxDbTable instances commits any pending transactions on all three wxDbTable
506 Until a call to wxDb::CommitTrans() is made, no other user or cursor is able
507 to see any changes made to the row(s) that have been inserted/modified/deleted.
510 \wxheading{Special Note :
{\it Cursors
} }
512 \normalbox{It is important to understand that different database/ODBC driver combinations handle
513 transactions differently. One thing in particular that you must pay attention to is
514 cursors, in regard to transactions. Cursors are what allow you to scroll through
515 records forward and backward and to manipulate records as you scroll through them.
516 When you issue a query, a cursor is created behind the scenes. The cursor keeps track
517 of the query and keeps track of the current record pointer. After you commit or
518 rollback a transaction, the cursor may be closed automatically. This is database
519 dependent, and with some databases this behavior can be controlled through
520 management functions. This means you would need to
521 requery the datasource before you can perform any additional work using this
522 cursor. This is only necessary however if the datasource closes the cursor after a
523 commit or rollback. Use the
\helpref{wxDbTable::IsCursorClosedOnCommit
}{wxdbtableiscursorclosedoncommit
} member function to
524 determine the datasource's transaction behavior. Note, in many situations
525 it is very inefficient to assume the cursor is closed and always requery. This could put
526 a significant, unnecessary load on datasources that leave the cursors open after a
530 \membersection{wxDb::CreateView
}\label{wxdbcreateviews
}
532 \func{bool
}{CreateView
}{\param{char *
}{ viewName
},
\param{char *
}{ colList
},
\param{char *
}{pSqlStmt
}}
534 Creates a SQL VIEW of one or more tables in a single datasource. Note that
535 this function will only work against databases which support views (currently
536 only Oracle as of November
21 2000).
538 \wxheading{Parameters
}
540 \docparam{viewName
}{The name of the view. e.g. PARTS_V
}
542 \docparam{colList
}{{\it OPTIONAL
} Pass in a comma delimited list of column
543 names if you wish to explicitly name each column in the result set. If not
544 desired, pass in an empty string and the column names from the associated
545 table(s) will be used.
}
547 \docparam{pSqlStmt
}{Pointer to the select statement portion of the CREATE VIEW statement. Must be a complete, valid SQL SELECT statement.
}
551 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.
553 NOTE: Views are not available with all datasources. Oracle is one example of a datasource which does support views.
558 // Incomplete code sample
559 db.CreateView("PARTS_SD1", "PN, PD, QTY",
560 "SELECT PART_NO, PART_DESC, QTY_ON_HAND *
1.1 FROM PARTS \
561 WHERE STORAGE_DEVICE =
1");
563 // PARTS_SD1 can now be queried just as if it were a data table.
564 // e.g. SELECT PN, PD, QTY FROM PARTS_SD1
568 \membersection{wxDb::Dbms
}\label{wxdbdbms
}
570 \func{wxDBMS
}{Dbms
}{\void}
574 The return value will be of the enumerated type wxDBMS. This enumerated
575 type contains a list of all the currently tested and supported databases.
577 Additional databases may work with these classes, but the databases
578 returned by this function have been tested and confirmed to work with
581 Possible values returned by this function can be viewed in the
582 \helpref{Enumerated types
}{wxdbenumeratedtypes
} section of wxDb.
584 There are known issues with conformance to the ODBC standards with several
585 datasources supported by the wxWindows ODBC classes. Please see the overview
586 for specific details on which datasource have which issues.
588 \wxheading{Return value
}
590 The return value will indicate which of the supported datasources is
591 currently connected to by this connection. In the event that the
592 datasource is not recognized, a value of 'dbmsUNIDENTIFIED' is returned.
595 \membersection{wxDb::DispAllErrors
}\label{wxdbdispallerrors
}
597 \func{bool
}{DispAllErrors
}{\param{HENV
}{ aHenv
},
{\param}{HDBC
}{ aHdbc = SQL_NULL_HDBC
},
{\param}{HSTMT
}{ aHstmt = SQL_NULL_HSTMT
}}
599 Used to log all database errors that occurred as a result of an executed
600 database command. This logging is automatic and also includes debug logging
601 when compiled in debug mode via
\helpref{wxLogDebug
}{wxlogdebug
}. If logging
602 is turned on via
\helpref{wxDb::SetSqlLogging
}{wxdbsetsqllogging
}, then an
603 entry is also logged to the defined log file.
605 \wxheading{Parameters
}
607 \docparam{aHenv
}{Handle to the ODBC environment.
}
609 \docparam{aHdbc
}{Handle to the ODBC connection. Pass this in if the ODBC function call that erred required a hdbc or hstmt argument.
}
611 \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.
}
615 This member function will log all of the ODBC error messages for the last
616 ODBC function call that was made. This function is normally used internally
617 within the ODBC class library, but can be used programmatically after calling
618 ODBC functions directly (i.e. SQLFreeEnv()).
620 \wxheading{Return value
}
622 The function always returns FALSE, so a call to this function can be made
623 in the return statement of a code block in the event of a failure to
624 perform an action (see the example below).
628 \helpref{wxDb::SetSqlLogging
}{wxdbsetsqllogging
}, wxDbSqlLog
633 if (SQLExecDirect(hstmt, (UCHAR FAR *) pSqlStmt, SQL_NTS) != SQL_SUCCESS)
634 // Display all ODBC errors for this stmt
635 return(db.DispAllErrors(db.henv, db.hdbc, hstmt));
639 \membersection{wxDb::DispNextError
}\label{wxdbdispnexterror
}
641 \func{void
}{DispNextError
}{\void}
645 This function is normally used internally within the ODBC class library.
646 It could be used programmatically after calling ODBC functions directly. This
647 function works in conjunction with
\helpref{wxDb::GetNextError
}{wxdbgetnexterror
} when errors (or
648 sometimes informational messages) returned from ODBC need to be analyzed
649 rather than simply displaying them as an error. GetNextError() retrieves
650 the next ODBC error from the ODBC error queue. The wxDb member variables
651 "sqlState", "nativeError" and "errorMsg" could then be evaluated. To
652 display the error retrieved, DispNextError() could then be called.
653 The combination of GetNextError() and DispNextError() can be used to
654 iteratively step through the errors returned from ODBC evaluating each
655 one in context and displaying the ones you choose.
660 // Drop the table before attempting to create it
661 sprintf(sqlStmt, "DROP TABLE
%s", tableName);
662 // Execute the drop table statement
663 if (SQLExecDirect(hstmt,(UCHAR FAR *)sqlStmt,SQL_NTS) != SQL_SUCCESS)
665 // Check for sqlState = S0002, "Table or view not found".
666 // Ignore this error, bomb out on any other error.
667 pDb->GetNextError(henv, hdbc, hstmt);
668 if (strcmp(pDb->sqlState, "S0002"))
670 pDb->DispNextError(); // Displayed error retrieved
671 pDb->DispAllErrors(henv, hdbc, hstmt); // Display all other errors, if any
672 pDb->RollbackTrans(); // Rollback the transaction
673 CloseCursor(); // Close the cursor
674 return(FALSE); // Return Failure
680 \membersection{wxDb::DropView
}\label{wxdbdropview
}
682 \func{bool
}{DropView
}{\param{const char *
}{viewName
}}
684 Drops the data table view named in 'viewName'.
686 \wxheading{Parameters
}
688 \docparam{viewName
}{Name of the view to be dropped.
}
692 If the view does not exist, this function will return TRUE. Note that views are not supported with all datasources.
694 \membersection{wxDb::ExecSql
}\label{wxdbexecsql
}
696 \func{bool
}{ExecSql
}{\param{char *
}{pSqlStmt
}}
698 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.
700 \wxheading{Parameters
}
702 \docparam{pSqlStmt
}{Pointer to the SQL statement to be executed.
}
706 This member extends the wxDb class and allows you to build and execute ANY VALID
707 SQL statement against the datasource. This allows you to extend the class
708 library by being able to issue any SQL statement that the datasource is capable
713 \helpref{wxDb::GetData
}{wxdbgetdata
},
\helpref{wxDb::GetNext
}{wxdbgetnext
}
716 \membersection{wxDb::GetCatalog
}\label{wxdbgetcatalog
}
718 \func{wxDbInf *
}{GetCatalog
}{\param{char *
}{userID
}}
720 Returns a
\helpref{wxDbInf
}{wxdbinf
} pointer that points to the catalog
721 (datasource) name, schema, number of tables accessible to the current user,
722 and a wxDbTableInf pointer to all data pertaining to all tables in the users
725 \wxheading{Parameters
}
727 \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:
}
730 userID == NULL ... UserID is ignored (DEFAULT)
731 userID == "" ... UserID set equal to 'this->uid'
732 userID != "" ... UserID set equal to 'userID'
737 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.
739 \membersection{wxDb::GetColumnCount
}\label{wxdbgetcolumncount
}
741 \func{int
}{GetColumnCount
}{\param{char *
}{tableName
},
\param{const char *
}{userID
}}
743 \wxheading{Parameters
}
745 \docparam{tableName
}{The table name you wish to obtain column information about.
}
747 \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:
}
750 userID == NULL ... UserID is ignored (DEFAULT)
751 userID == "" ... UserID set equal to 'this->uid'
752 userID != "" ... UserID set equal to 'userID'
755 \wxheading{Return value
}
757 Returns a count of how many columns are in the specified table. If an error
758 occurs retrieving the number of columns, this function will return a -
1.
760 \membersection{wxDb::GetColumns
}\label{wxdbgetcolumns
}
762 \func{wxDbColInf *
}{GetColumns
}{\param{char *
}{tableName
},
\param{int *
}{numCols
},
\param{const char *
}{userID=NULL
}}
764 \func{wxDbColInf *
}{GetColumns
}{\param{char *
}{tableName
[]},
\param{const char *
}{userID
}}
766 \wxheading{Parameters
}
768 \docparam{tableName
}{The table name you wish to obtain column information about.
}
769 \docparam{numCols
}{Pointer to an integer which will hold a count of the number of columns returned by this function
}
770 \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.
}
771 \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:
}
774 userID == NULL ... UserID is ignored (DEFAULT)
775 userID == "" ... UserID set equal to 'this->uid'
776 userID != "" ... UserID set equal to 'userID'
779 \wxheading{Return value
}
781 This function returns a pointer to an array of
\helpref{wxDbColInf
}{wxdbcolinf
}
782 structures, allowing you to obtain information regarding the columns of the
783 named table(s). If no columns were found, or an error occurred, this pointer
786 THE CALLING FUNCTION IS RESPONSIBLE FOR DELETING THE
{\it wxDbColInf
} MEMORY WHEN IT IS
789 \normalbox{ALL column bindings associated with this wxDb instance are unbound
790 by this function, including those used by any wxDbTable instances that use
791 this wxDb instance. This function should use its own wxDb instance
792 to avoid undesired unbinding of columns.
}
796 \helpref{wxDbColInf
}{wxdbcolinf
}
801 char *tableList
[] =
{"PARTS",
0};
802 wxDbColInf *colInf = pDb->GetColumns(tableList);
805 // Use the column inf
807 // Destroy the memory
813 \membersection{wxDb::GetData
}\label{wxdbgetdata
}
815 \func{bool
}{GetData
}{\param{UWORD
}{ colNo
},
\param{SWORD
}{ cType
},
\param{PTR
}{ pData
},
\param{SDWORD
}{ maxLen
},
\param{SDWORD FAR *
}{ cbReturned
} }
817 Used to retrieve result set data without binding column values to memory variables (i.e. not using a wxDbTable instance to access table data).
819 \wxheading{Parameters
}
821 \docparam{colNo
}{Ordinal number of the desired column in the result set to be returned.
}
822 \docparam{cType
}{The C data type that is to be returned. See a partial list in
\helpref{wxDbTable::SetColDefs
}{wxdbtablesetcoldefs
}}
823 \docparam{pData
}{Memory buffer which will hold the data returned by the call to this function.
}
824 \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.
}
825 \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.
}
829 \helpref{wxDb::GetNext
}{wxdbgetnext
},
\helpref{wxDb::ExecSql
}{wxdbexecsql
}
837 sqlStmt = "SELECT SUM(REQUIRED_QTY - PICKED_QTY) FROM ORDER_TABLE WHERE \
838 PART_RECID =
1450 AND REQUIRED_QTY > PICKED_QTY";
841 if (!pDb->ExecSql(sqlStmt.c_str()))
847 // Request the first row of the result set
854 // Read column
#1 of the row returned by the call to ::GetNext()
855 // and return the value in 'reqQty'
856 if (!pDb->GetData(
1, SQL_C_ULONG, &reqQty,
0, &cb))
862 // Check for a NULL result
863 if (cb == SQL_NULL_DATA)
869 When requesting multiple columns to be returned from the result set (for example, the SQL query
870 requested
3 columns be returned), the calls to this function must request the columns in ordinal
871 sequence (
1,
2,
3 or
1,
3 or
2,
3).
873 \membersection{wxDb::GetDatabaseName
}\label{wxdbgetdatabasename
}
875 \func{const char *
}{GetDatabaseName
}{\void}
877 Returns the name of the database engine.
879 \membersection{wxDb::GetDatasourceName
}\label{wxdbgetdatasourcename
}
881 \func{const char *
}{GetDatasourceName
}{\void}
883 Returns the ODBC datasource name.
885 \membersection{wxDb::GetHDBC
}\label{wxdbgethdbc
}
887 \func{HDBC
}{GetHDBC
}{\void}
889 Returns the ODBC handle to the database connection.
891 \membersection{wxDb::GetHENV
}\label{wxdbgethenv
}
893 \func{HENV
}{GetHENV
}{\void}
895 Returns the ODBC environment handle.
897 \membersection{wxDb::GetHSTMT
}\label{wxdbgethstmt
}
899 \func{HSTMT
}{GetHSTMT
}{\void}
901 Returns the ODBC statement handle associated with this database connection.
903 \membersection{wxDb::GetKeyFields
}\label{wxdbgetkeyfields
}
905 \func{int
}{GetKeyFields
}{\param{char *
}{tableName
},
\param{wxDbColInf *
}{colInf
},
\param{int
}{nocols
}}
907 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.
909 This function is primarily for use by the
\helpref{wxDb::GetColumns
}{wxdbgetcolumns
} function, but may be called if desired from the client application.
911 \wxheading{Parameters
}
913 \docparam{tableName
}{Name of the table for which the columns will be evaluated as to their inclusion in any indexes.
}
914 \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.
}
915 \docparam{nocols
}{Number of columns defined in the instance of colInf.
}
917 \wxheading{Return value
}
919 Currently always returns TRUE.
923 \helpref{wxDbColInf
}{wxdbcolinf
},
\helpref{wxDb::GetColumns
}{wxdbgetcolumns
}
925 \membersection{wxDb::GetNext
}\label{wxdbgetnext
}
927 \func{bool
}{GetNext
}{\void}
929 Called after executing a query, this function requests the next row
930 in the result set after the current position of the cursor.
934 \helpref{wxDb::ExecSql
}{wxdbexecsql
},
\helpref{wxDb::GetData
}{wxdbgetdata
}
936 \membersection{wxDb::GetNextError
}\label{wxdbgetnexterror
}
938 \func{bool
}{GetNextError
}{\param{HENV
}{ aHenv
},
\param{HDBC
}{ aHdbc = SQL_NULL_HDBC
},
\param{HSTMT
}{ aHstmt = SQL_NULL_HSTMT
}}
940 \wxheading{Parameters
}
942 \docparam{aHenv
}{A handle to the ODBC environment.
}
943 \docparam{aHdbc
}{{\it OPTIONAL.
} A handle to the ODBC connection. Pass this in if the ODBC function call that
944 erred out required a hdbc or hstmt argument.
}
945 \docparam{AHstmt
}{{\it OPTIONAL.
}A handle to the ODBC statement being executed against. Pass this in if the
946 ODBC function call that erred out requires a hstmt argument.
}
950 \helpref{wxDb::DispNextError
}{wxdbdispnexterror
},
\helpref{wxDb::DispAllErrors
}{wxdbdispallerrors
}
955 if (SQLExecDirect(hstmt, (UCHAR FAR *) pSqlStmt, SQL_NTS) != SQL_SUCCESS)
957 // Display all ODBC errors for this stmt
958 return(db.DispAllErrors(db.henv, db.hdbc, hstmt));
963 \membersection{wxDb::GetPassword
}\label{wxdbgetpassword
}
965 \func{const char *
}{GetPassword
}{\void}
967 Returns the password used to establish this connection to the datasource.
970 \membersection{wxDb::GetTableCount
}\label{wxdbgettablecount
}
972 \func{int
}{GetTableCount
}{\void}
974 Returns the number of wxDbTable() instances currently using this datasource connection.
977 \membersection{wxDb::GetUsername
}\label{wxdbgetusername
}
979 \func{const char *
}{GetUsername
}{\void}
981 Returns the user name (uid) used to establish this connection to the datasource.
984 \membersection{wxDb::Grant
}\label{wxdbgrant
}
986 \func{bool
}{Grant
}{\param{int
}{privileges
},
\param{char *
}{tableName
},
\param{char *
}{userList = "PUBLIC"
}}
988 Use this member function to GRANT privileges to users for accessing tables in the datasource.
990 \wxheading{Parameters
}
992 \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:
}
999 DB_GRANT_ALL = DB_GRANT_SELECT | DB_GRANT_INSERT |
1000 DB_GRANT_UPDATE | DB_GRANT_DELETE
1003 \docparam{tableName
}{The name of the table you wish to grant privileges on.
}
1004 \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.
}
1008 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.
1010 The currently logged in user must have sufficient grantor privileges for this
1011 function to be able to successfully grant the indicated privileges.
1016 db.Grant(DB_GRANT_SELECT | DB_GRANT_INSERT, "PARTS", "mary, sue");
1020 \membersection{wxDb::IsFwdOnlyCursors
}\label{wxdbisfwdonlycursors
}
1022 \func{bool
}{IsFwdOnlyCursors
}{\void}
1024 This setting indicates whether this database connection was created
1025 as being capable of using only forward scrolling cursors.
1027 This function does NOT indicate if the ODBC driver or datasource supports
1028 backward scrolling cursors. There is no standard way of detecting if the
1029 driver or datasource can support backward scrolling cursors.
1031 If a wxDb instance was created as being capable of only forward scrolling
1032 cursors, then even if the datasource and ODBC driver support backward
1033 scrolling cursors, tables using this database connection would only be able
1034 to use forward scrolling cursors.
1036 The default setting of whether a wxDb connection to a database allows
1037 forward-only or also backward scrolling cursors is defined in setup.h by the
1038 value of wxODBC_FWD_ONLY_CURSORS. This default setting can be overridden
1039 when the wxDb connection is initially created (see
1040 \helpref{wxDb constructor
}{wxdbconstr
} and
\helpref{wxDbGetConnection
}{wxdbfunctions
}).
1042 \wxheading{Return value
}
1044 Returns TRUE if this datasource connection is defined as using only forward
1045 scrolling cursors, or FALSE if the connection is defined as being allowed to
1046 use backward scrolling cursors and their associated functions (see note above).
1050 Added as of wxWindows v2.4 release, this function is a renamed version of
1051 wxDb::FwdOnlyCursors() to match the normal wxWindows naming conventions for
1052 class member functions.
1054 This function is not available in versions prior to v2.4. You should
1055 use
\helpref{wxDb::FwdOnlyCursors
}{wxdbfwdonlycursors
} for wxWindows
1056 versions prior to
2.4.
1058 \wxheading{See also
}
1060 \helpref{wxDb constructor
}{wxdbconstr
},
\helpref{wxDbGetConnection
}{wxdbfunctions
}
1063 \membersection{wxDb::IsOpen
}\label{wxdbisopen
}
1065 \func{bool
}{IsOpen
}{\void}
1067 Indicates whether the database connection to the datasource is currently
1072 This function may indicate that the database connection is open, even if
1073 the call to
\helpref{wxDb::Open
}{wxdbopen
} may have failed to fully
1074 initialize the connection correctly. The connection to the database
1075 {\it is
} open and can be used via the direct SQL commands, if this
1076 function returns TRUE. Other functions which depend on the
1077 \helpref{wxDb::Open
}{wxdbopen
} to have completed correctly may not function
1078 as expected. The return result from
\helpref{wxDb::Open
}{wxdbopen
} is the
1079 only way to know if complete initialization of this wxDb connection was
1080 successful or not. See
\helpref{wxDb::Open
}{wxdbopen
} for more details on
1081 partial failures to open a connection instance.
1084 \membersection{wxDb::LogError
}\label{wxdblogerror
}
1086 \func{void
}{LogError
}{\param{const char *
}{errMsg
} \param{const char *
}{SQLState=NULL
}}
1088 \docparam{errMsg
}{Free-form text to display describing the error/text to be logged.
}
1089 \docparam{SQLState
}{{\it OPTIONAL.
} Native SQL state error. Default is
0.
}
1093 Calling this function will enter a log message in the error list maintained
1094 for the database connection. This log message is free form and can be
1095 anything the programmer wants to enter in the error list.
1097 If SQL logging is turned on, the call to this function will also log the
1098 text into the SQL log file.
1100 \wxheading{See also
}
1102 \helpref{wxDb::WriteSqlLog
}{wxdbwritesqllog
}
1105 \membersection{wxDb::Open
}\label{wxdbopen
}
1107 \func{bool
}{Open
}{\param{char *
}{Dsn
},
\param{char *
}{Uid
},
\param{char *
}{AuthStr
}}
1109 \func{bool
}{Open
}{\param{wxDb *
}{copyDb
}}
1111 Opens a connection to the datasource, sets certain behaviors of the datasource
1112 to confirm to the accepted behaviors (e.g. cursor position maintained on
1113 commits), and queries the datasource for its representations of the basic
1114 datatypes to determine the form in which the data going to/from columns in
1115 the data tables are to be handled.
1117 The second form of this function, which accepts a "wxDb *" as a parameter,
1118 can be used to avoid the overhead (execution time, database load, network
1119 traffic) which are needed to determine the data types and representations
1120 of data that are necessary for cross-datasource support by these classes.
1122 Normally the first form of the wxDb::Open() function will open the connection
1123 and then send a series of queries to the datasource asking it for its
1124 representation of data types, and all the features it supports. If one
1125 connection to the datasource has already been made previously, the information
1126 gathered when that connection was created can just be copied to any new
1127 connections to the same datasource by passing a pointer to the first
1128 connection in as a parameter to the wxDb::Open() function. Note that this
1129 new connection created from the first connections information will use the
1130 same Dsn/Uid/AuthStr as the first connection used.
1132 \wxheading{Parameters
}
1134 \docparam{Dsn
}{datasource name. The name of the ODBC datasource as
1135 assigned when the datasource is initially set up through the ODBC data
1137 \docparam{Uid
}{User ID. The name (ID) of the user you wish to connect as
1138 to the datasource. The user name (ID) determines what objects you
1139 have access to in the datasource and what datasource privileges you have.
1140 Privileges include being able to create new objects, update objects, delete
1141 objects and so on. Users and privileges are normally administered by the
1142 database administrator.
}
1143 \docparam{AuthStr
}{The password associated with the Uid.
}
1144 \docparam{copyDb
}{Already completely configured and opened datasource connection
1145 from which all Dsn, Uid, AuthStr, and data typing information is to be copied
1146 from for use by this datasource connection.
}
1150 After a wxDb instance is created, it must then be opened. When opening a
1151 datasource, there must be three pieces of information passed. The data
1152 source name, user name (ID) and the password for the user. No database
1153 activity on the datasource can be performed until the connection is opened.
1154 This is normally done at program startup and the datasource remains
1155 open for the duration of the program/module run.
1157 It is possible to have connections to multiple datasources open at the same
1158 time to support distributed database connections by having separate instances
1159 of wxDb objects that use either the same or different Dsn/Uid/AuthStr settings.
1161 If this function returns a value of FALSE, it does not necessarily mean that
1162 the connection to the datasource was not opened. It may mean that some
1163 portion of the initialization of the connection failed (such as a datatype not
1164 being able to be determined how the datasource represents it). To determine
1165 if the connection to the database failed, use the
\helpref{wxDb::IsOpen
}{wxdbisopen
}
1166 function after receiving a FALSE result back from this function to determine if
1167 the connection was opened or not. If this function returns FALSE, but
\helpref{wxDb::IsOpen
}{wxdbisopen
}
1168 returns TRUE, then direct SQL commands may be passed to the database
1169 connection and can be successfully executed, but use of the datatypes (such as
1170 by a wxDbTable instance) that are normally determined during open will not be
1173 \normalbox{The
{\it Dsn
},
{\it Uid
}, and
{\it AuthStr
} string pointers that are passed in
1174 are copied. NOT the strings themselves, only the pointers. The calling routine
1175 must maintain the memory for these three strings for the life of the wxDb instance.
}
1180 wxDb sampleDB(Db.Henv);
1181 if (!sampleDB.Open("Oracle
7.1 HP/UX", "gtasker", "myPassword"))
1183 if (sampleDb.IsOpen())
1185 // Connection is open, but the initialization of
1186 // datatypes and parameter settings failed
1190 // Error opening datasource
1196 \membersection{wxDb::RollbackTrans
}\label{wxdbrollbacktrans
}
1198 \func{bool
}{RollbackTrans
}{\void}
1200 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.
1204 Transactions begin implicitly as soon as you make a change to the database. The
1205 transaction continues until either a commit or rollback is executed. Calling
1206 wxDb::RollbackTrans() will result in ALL changes done using this database
1207 connection that have not already been committed to be "undone" back to the last
1208 commit/rollback that was successfully executed.
1210 \normalbox{Calling this member function rolls back ALL open (uncommitted)
1211 transactions on this ODBC connection, including all wxDbTable instances that
1212 use this connection.
}
1214 \wxheading{See also
}
1216 \helpref{wxDb::CommitTrans
}{wxdbcommittrans
} for a special note on cursors
1219 \membersection{wxDb::SetDebugErrorMessages
}\label{wxdbsetdebugerrormessages
}
1221 \func{void
}{SetDebugErrorMessages
}{\param{bool
}{state
}}
1223 \docparam{state
}{Either TRUE (debug messages are logged) or FALSE (debug
1224 messages are not logged.
}
1228 Turns on/off debug error messages from the ODBC class library. When
1229 this function is passed TRUE, errors are reported to the user/logged automatically
1230 in a text or pop-up dialog when an ODBC error occurs. When passed FALSE,
1231 errors are silently handled.
1233 When compiled in release mode (FINAL=
1), this setting has no affect.
1235 \wxheading{See also
}
1237 \helpref{wxDb constructor
}{wxdbconstr
}
1240 \membersection{wxDb::SetSqlLogging
}\label{wxdbsetsqllogging
}
1242 \func{bool
}{SetSqlLogging
}{\param{wxDbSqlLogState
}{ state
},
\param{const wxChar *
}{filename = SQL_LOG_FILENAME
},
\param{bool
}{ append = FALSE
}}
1244 \wxheading{Parameters
}
1246 \docparam{state
}{Either sqlLogOFF or sqlLogON (see
\helpref{enum wxDbSqlLogState
}{wxdbcolfor
}). Turns logging of SQL commands sent to the datasource OFF or ON.
}
1247 \docparam{filename
}{{\it OPTIONAL
}. Name of the file to which the log text is to be written. Default is SQL_LOG_FILENAME.
}
1248 \docparam{append
}{{\it OPTIONAL
}. Whether the file is appended to or overwritten. Default is FALSE.
}
1252 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.
1254 When called with
{\it sqlLogOFF
}, the logging file is closed, and any calls to
\helpref{wxDb::WriteSqlLog
}{wxdbwritesqllog
} are ignored.
1257 \membersection{wxDb::TableExists
}\label{wxdbtableexists
}
1259 \func{bool
}{TableExists
}{\param{const char *
}{tableName
},
\param{const char *
}{userID=NULL
},
\param{const char *
}{path=NULL
}}
1261 Checks the ODBC datasource for the existence of a table. If a
{\it userID
}
1262 is specified, then the table must be accessible by that user (user must have
1263 at least minimal privileges to the table).
1265 \wxheading{Parameters
}
1267 \docparam{tableName
}{Name of the table to check for the existence of.
}
1268 \docparam{userID
}{Owner of the table (also referred to as schema). Specify a userID when the datasource you are connected
1269 to allows multiple unique tables with the same name to be owned by different users.
{\it userID
}
1270 is evaluated as follows:
}
1273 userID == NULL ... UserID is ignored (DEFAULT)
1274 userID == "" ... UserID set equal to 'this->uid'
1275 userID != "" ... UserID set equal to 'userID'
1280 {\it tableName
} may refer to a table, view, alias or synonym.
1282 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.
1284 \wxheading{See also
}
1286 \helpref{wxDb::TablePrivileges
}{wxdbtableprivileges
}
1289 \membersection{wxDb::TablePrivileges
}\label{wxdbtableprivileges
}
1291 \func{bool
}{TablePrivileges
}{\param{const char *
}{tableName
},
\param{const char *
}{priv
},
\param{const char *
}{userID=""
},
\param{const char *
}{schema=NULL
},
\param{const char *
}{path=""
}}
1293 Checks the ODBC datasource for the existence of a table. If a
{\it userID
}
1294 is specified, then the table must be accessible by that user (user must have
1295 at least minimal privileges to the table).
1297 \wxheading{Parameters
}
1299 \docparam{tableName
}{Name of the table on which to check privileges.
1300 {\it tableName
} may refer to a table, view, alias or synonym.
}
1301 \docparam{priv
}{The table privilege being evaluated. May be one of the
1302 following (or a datasource specific privilege):
}
1305 SELECT : The connected user is permitted to retrieve data for
1306 one or more columns of the table.
1308 INSERT : The connected user is permitted to insert new rows
1309 containing data for one or more columns into the
1312 UPDATE : The connected user is permitted to update the data in
1313 one or more columns of the table.
1315 DELETE : The connected user is permitted to delete rows of
1316 data from the table.
1318 REFERENCES : Is the connected user permitted to refer to one or
1319 more columns of the table within a constraint (for
1320 example, a unique, referential, or table check
1323 \docparam{userID
}{{\it OPTIONAL.
} User for which to determine if the privilege
1324 specified to be checked is granted or not. Default is "".
1325 {\it userID
} is evaluated as follows:
1328 userID == NULL ... NOT ALLOWED!
1329 userID == "" ... UserID set equal to 'this->uid'
1330 userID != "" ... UserID set equal to 'userID'
1333 \docparam{schema
}{{\it OPTIONAL.
} Owner of the table. Specify a userID when the datasource
1334 you are connected to allows multiple unique tables with the same name to be
1335 owned by different users. Specifying the table owner makes determination of the
1336 users privileges MUCH faster. Default is NULL.
{\it userID
} is evaluated as follows:
1339 schema == NULL ... Any owner (DEFAULT)
1340 schema == "" ... Owned by 'this->uid'
1341 schema != "" ... Owned by userID specified in 'schema'
1344 \docparam{path
}{{\it OPTIONAL.
} Path to the table. Default is "".
1349 The scope of privilege allowed to the connected user by a given table
1350 privilege is datasource dependent.
1352 For example, the privilege UPDATE might allow the connected user to update
1353 all columns in a table on one datasource, but only those columns for
1354 which the grantor (the user that granted the connected user) has the UPDATE
1355 privilege on another datasource.
1357 Looking up a user's privileges to a table can be time consuming depending on the
1358 datasource and ODBC driver. This time can be minimized by passing a
{\it schema
}
1359 as a parameter. With some datasources/drivers, the difference can be several
1360 seconds of time difference.
1363 \membersection{wxDb::TranslateSqlState
}\label{wxdbtranslatesqlstate
}
1365 \func{int
}{TranslateSqlState
}{\param{const wxChar *
}{SQLState
}}
1367 Converts an ODBC sqlstate to an internal error code.
1369 \wxheading{Parameters
}
1371 \docparam{SQLState
}{State to be converted.
}
1373 \wxheading{Return value
}
1375 Returns the internal class DB_ERR code. See
\helpref{wxDb::DB_STATUS
}{wxdb
} definition.
1378 \membersection{wxDb::WriteSqlLog
}\label{wxdbwritesqllog
}
1380 \func{bool
}{WriteSqlLog
}{\param{const wxChar *
}{logMsg
}}
1382 \wxheading{Parameters
}
1384 \docparam{logMsg
}{Free form string to be written to the log file.
}
1388 Very useful debugging tool that may be turned on/off during run time (see
1389 (see
\helpref{wxDb::SetSqlLogging
}{wxdbsetsqllogging
} for details on
1390 turning logging on/off). The passed in string
{\it logMsg
} will be written to
1391 a log file if SQL logging is turned on.
1393 \wxheading{Return value
}
1395 If SQL logging is off when a call to WriteSqlLog() is made, or there is a
1396 failure to write the log message to the log file, the function returns
1397 FALSE without performing the requested log, otherwise TRUE is returned.
1399 \wxheading{See also
}
1401 \helpref{wxDb::SetSqlLogging
}{wxdbsetsqllogging
}
1404 \section{\class{wxDbColDataPtr
}}\label{wxdbcoldataptr
}
1406 Pointer to dynamic column definitions for use with a wxDbTable instance.
1407 Currently there are no member functions for this class.
1416 \section{\class{wxDbColDef
}}\label{wxdbcoldef
}
1418 This class is used to hold information about the columns bound to an
1419 instance of a wxDbTable object.
1421 Each instance of this class describes one column in the wxDbTable
1422 object. When calling the
\helpref{wxDb constructor
}{wxdbconstr
}, a
1423 parameter passed in indicates the number of columns that will be defined for
1424 the wxDbTable object. The constructor uses this information to allocate
1425 adequate memory for all of the column descriptions in your wxDbTable object.
1426 Private member wxDbTable::colDefs is a pointer to this chunk of memory
1427 maintained by the wxDbTable class (and can be retrieved using the
1428 \helpref{wxDbTable::GetColDefs
}{wxdbtablegetcoldefs
} function).
1429 To access the nth column definition of your wxDbTable object, just reference
1430 wxDbColDefs element
[n -
1].
1432 Currently there are no member functions for this class.
1434 Typically,
\helpref{wxDbTable::SetColDefs
}{wxdbtablesetcoldefs
} is used to
1435 populate an array of these data structures for the wxDbTable instance.
1438 char ColName
[DB_MAX_COLUMN_NAME_LEN+
1]; // Column Name
1439 int DbDataType; - Logical Data Type;
1440 e.g. DB_DATA_TYPE_INTEGER
1441 int SqlCtype; - C data type; e.g. SQL_C_LONG
1442 void *PtrDataObj; - Address of the data object
1443 int SzDataObj; - Size, in bytes, of the data object
1444 bool KeyField; - Is column part of the PRIMARY KEY for the
1445 table? -- Date fields should NOT be
1447 bool Updateable; - Column is updateable?
1448 bool InsertAllowed; - Column included in INSERT statements?
1449 bool DerivedCol; - Column is a derived value?
1450 SDWORD CbValue; - !!!Internal use only!!!
1451 bool Null; - NOT FULLY IMPLEMENTED
1452 Allows NULL values in Inserts and Updates
1455 \wxheading{See also
}
1457 \helpref{wxDbTable::GetColDefs
}{wxdbtablegetcoldefs
},
\helpref{wxDb constructor
}{wxdbconstr
}
1460 \section{\class{wxDbColInf
}}\label{wxdbcolinf
}
1462 Used with the
\helpref{wxDb::GetColumns
}{wxdbgetcolumns
} functions for obtaining all retrievable information about a column's definition.
1465 char catalog
[128+
1];
1467 char tableName
[DB_MAX_TABLE_NAME_LEN+
1];
1468 char colName
[DB_MAX_COLUMN_NAME_LEN+
1];
1470 char typeName
[128+
1];
1473 short decimalDigits;
1476 char remarks
[254+
1];
1477 int dbDataType; // conversion of the 'sqlDataType'
1478 // to the generic data type used by
1480 int PkCol; // Primary key column
1483 2 = Second Key, etc...
1484 char PkTableName
[DB_MAX_TABLE_NAME_LEN+
1];
1485 // Tables that use this PKey as a FKey
1486 int FkCol; // Foreign key column
1489 2 = Second Key, etc...
1490 char FkTableName
[DB_MAX_TABLE_NAME_LEN+
1];
1491 // Foreign key table name
1492 wxDbColFor *pColFor; // How should this column be formatted
1495 The constructor for this class initializes all the values to zero or NULL.
1497 The destructor for this class takes care of deleting the pColFor member if
1501 \section{\class{wxDbColFor
}}\label{wxdbcolfor
}
1503 Beginning support for handling international formatting specifically on dates and floats.
1506 wxString s_Field; // Formated String for Output
1507 wxString s_Format
[7]; // Formated Objects - TIMESTAMP has
1509 wxString s_Amount
[7]; // Formated Objects - amount of
1510 things that can be formatted
1511 int i_Amount
[7]; // Formated Objects -
1512 TT MM YYYY HH MM SS m
1513 int i_Nation; //
0 = timestamp
1518 int i_dbDataType; // conversion of the 'sqlDataType'
1519 to the generic data type used by
1521 SWORD i_sqlDataType;
1524 The constructor for this class initializes all the values to zero or NULL.
1526 The destructor does nothing at this time.
1528 Only one function is provided with this class currently:
1530 \membersection{wxDbColFor::Format
}\label{wxdbcolforformat
}
1532 \func{int
}{Format
}{\param{int
}{Nation
},
\param{int
}{dbDataType
},
\param{SWORD
}{sqlDataType
},
\param{short
}{columnSize
},
\param{short
}{decimalDigits
}}\label{wxdbcolforformat
}
1534 Work in progress, and should be inter-related with wxLocale eventually.
1537 \section{\class{wxDbIdxDef
}}\label{wxdbidxdef
}
1539 Used in creation of non-primary indexes. Currently there are no member
1540 functions for this class.
1543 char ColName
[DB_MAX_COLUMN_NAME_LEN+
1]
1545 bool Ascending // Is index maintained in
1549 There are no constructors/destructors as of this time, and no member functions.
1552 \section{\class{wxDbInf
}}\label{wxdbinf
}
1554 Contains information regarding the database connection (datasource name,
1555 number of tables, etc). A pointer to a wxDbTableInf is included in this
1556 class so a program can create a wxDbTableInf array instance to maintain all
1557 information about all tables in the datasource to have all the datasource's
1558 information in one memory structure.
1560 Primarily, this class is used internally by the wxWindows ODBC classes.
1563 char catalog
[128+
1];
1564 char schema
[128+
1]; // typically means owner of table(s)
1565 int numTables; // How many tables does this
1567 wxDbTableInf *pTableInf; // Equals a new
1568 wxDbTableInf
[numTables
];
1571 The constructor for this class initializes all the values to zero or NULL.
1573 The destructor for this class takes care of deleting the pTableInf member if
1577 \section{\class{wxDbTable
}}\label{wxdbtable
}
1579 A wxDbTable instance provides re-usable access to rows of data in
1580 a table contained within the associated ODBC datasource
1582 \wxheading{Include files
}
1587 \latexignore{\rtfignore{\wxheading{Members
}}}
1588 \wxheading{Helper classes and data structures
}
1590 The following classes and structs are defined in dbtable.cpp/.h for use with the wxDbTable class.
1592 \begin{itemize
}\itemsep=
0pt
1593 \item \helpref{wxDbColDef
}{wxdbcoldef
}
1594 \item \helpref{wxDbColDataPtr
}{wxdbcoldataptr
}
1595 \item \helpref{wxDbIdxDef
}{wxdbidxdef
}
1598 \wxheading{Constants
}
1601 wxDB_DEFAULT_CURSOR Primary cursor normally used for cursor based
1604 wxDB_QUERY_ONLY Used to indicate whether a table that is opened
1605 is for query only, or if insert/update/deletes
1606 will be performed on the table. Less overhead
1607 (cursors and memory) are allocated for query
1608 only tables, plus read access times are faster
1609 with some datasources.
1611 wxDB_ROWID_LEN
[Oracle only
] - Used when CanUpdateByRowID()
1612 is true. Optimizes updates so they are faster
1613 by updating on the Oracle-specific ROWID column
1614 rather than some other index.
1617 wxDB_DISABLE_VIEW Use to indicate when a database view should not
1618 be if a table is normally set up to use a view.
1619 [Currently unsupported.
]
1624 \latexignore{\rtfignore{\wxheading{Members
}}}
1626 \membersection{wxDbTable::wxDbTable
}\label{wxdbtableconstr
}
1628 \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
}}
1630 Default constructor.
1632 \wxheading{Parameters
}
1634 \docparam{pwxDb
}{Pointer to the wxDb instance to be used by this wxDbTable
1636 \docparam{tblName
}{The name of the table in the RDBMS.
}
1637 \docparam{nCols
}{The number of columns in the table. (Do NOT include the ROWID
1638 column in the count if using Oracle).
}
1639 \docparam{qryTblName
}{{\it OPTIONAL
}. The name of the table or view to base
1640 your queries on. This argument allows you to specify a table/view other than
1641 the base table for this object to base your queries on. This allows you to
1642 query on a view for example, but all of the INSERT, UPDATE and DELETES will
1643 still be performed on the base table for this wxDbTable object. Basing your
1644 queries on a view can provide a substantial performance increase in cases where
1645 your queries involve many tables with multiple joins. Default is NULL.
}
1646 \docparam{qryOnly
}{{\it OPTIONAL
}. Indicates whether the table will be
1647 accessible for query purposes only, or should the table create the necessary
1648 cursors to be able to insert, update, and delete data from the table.
1649 Default is !wxDB_QUERY_ONLY.
}
1650 \docparam{tblPath
}{{\it OPTIONAL
}. Some datasources (such as dBase)
1651 require a path to where the table is stored on the system. Default is NULL.
}
1653 \membersection{wxDbTable::wxDbTable
}\label{wxdbtabledestr
}
1655 \func{virtual
}{\destruct{wxDbTable
}}{}
1657 Virtual default destructor.
1660 \membersection{wxDbTable::BuildDeleteStmt
}\label{wxdbtablebuilddeletestmt
}
1662 \func{void
}{BuildDeleteStmt
}{\param{char *
}{pSqlStmt
},
\param{int
}{typeOfDel
},
\param{const char *
}{pWhereClause=NULL
}}
1664 Constructs the full SQL statement that can be used to delete all rows matching
1665 the criteria in the pWhereClause.
1667 \wxheading{Parameters
}
1669 \docparam{pSqlStmt
}{Pointer to buffer for the SQL statement retrieved. To be
1670 sure you have adequate space allocated for the SQL statement, allocate
1671 DB_MAX_STATEMENT_LEN bytes.
}
1672 \docparam{typeOfDel
}{The type of delete statement being performed. Can be one
1673 of three values: DB_DEL_KEYFIELDS, DB_DEL_WHERE or DB_DEL_MATCHING
}
1674 \docparam{pWhereClause
}{{\it OPTIONAL
}. If the typeOfDel is DB_DEL_WHERE,
1675 then you must also pass in a SQL WHERE clause in this argument. Default
1680 This member function constructs a SQL DELETE statement. This can be used for
1681 debugging purposes if you are having problems executing your SQL statement.
1683 WHERE and FROM clauses specified using
\helpref{wxDbTable::SetWhereClause
}{wxdbtablesetwhereclause
}
1684 and
\helpref{wxDbTable::SetFromClause
}{wxdbtablesetfromclause
} are ignored by
1688 \membersection{wxDbTable::BuildSelectStmt
}\label{wxdbtablebuildselectstmt
}
1690 \func{void
}{BuildSelectStmt
}{\param{char *
}{pSqlStmt
},
\param{int
}{typeOfSelect
},
\param{bool
}{distinct
}}
1692 Constructs the full SQL statement that can be used to select all rows matching
1693 the criteria in the pWhereClause. This function is called internally in the
1694 wxDbTable class whenever the function
\helpref{wxDbTable::Query
}{wxdbtablequery
}
1697 NOTE: Only the columns specified in
\helpref{wxDbTable::SetColDefs
}{wxdbtablesetcoldefs
}
1698 statements are included in the list of columns returned by the SQL statement
1699 created by a call to this function.
1701 \wxheading{Parameters
}
1703 \docparam{pSqlStmt
}{Pointer to storage for the SQL statement retrieved. To be
1704 sure you have adequate space allocated for the SQL statement, allocate
1705 DB_MAX_STATEMENT_LEN bytes.
}
1706 \docparam{typeOfSelect
}{The type of select statement being performed. Can be
1707 one of four values: DB_SELECT_KEYFIELDS, DB_SELECT_WHERE, DB_SELECT_MATCHING
1708 or DB_SELECT_STATEMENT.
}
1709 \docparam{distinct
}{Whether to select distinct records only.
}
1713 This member function constructs a SQL SELECT statement. This can be used for
1714 debugging purposes if you are having problems executing your SQL statement.
1716 WHERE and FROM clauses specified using
\helpref{wxDbTable::SetWhereClause
}{wxdbtablesetwhereclause
}
1717 and
\helpref{wxDbTable::SetFromClause
}{wxdbtablesetfromclause
} are ignored by
1721 \membersection{wxDbTable::BuildUpdateStmt
}\label{wxdbtablebuildupdatestmt
}
1723 \func{void
}{BuildSelectStmt
}{\param{char *
}{pSqlStmt
},
\param{int
}{typeOfUpd
},
\param{const char *
}{pWhereClause=NULL
}}
1725 Constructs the full SQL statement that can be used to update all rows matching
1726 the criteria in the pWhereClause.
1728 If typeOfUpd is DB_UPD_KEYFIELDS, then the current values in the bound columns
1729 are used to determine which row(s) in the table are to be updated. The
1730 exception to this is when a datasource supports ROW IDs (Oracle). The ROW ID
1731 column is used for efficiency purposes when available.
1733 NOTE: Only the columns specified in
\helpref{wxDbTable::SetColDefs
}{wxdbtablesetcoldefs
}
1734 statements are included in the list of columns updated by the SQL statement
1735 created by a call to this function. Any column definitions that were defined
1736 as being non-updateable will be excluded from the SQL UPDATE statement created
1739 \wxheading{Parameters
}
1741 \docparam{pSqlStmt
}{Pointer to storage for the SQL statement retrieved. To be
1742 sure you have adequate space allocated for the SQL statement, allocate
1743 DB_MAX_STATEMENT_LEN bytes.
}
1744 \docparam{typeOfUpd
}{The type of update statement being performed. Can be one
1745 of two values: DB_UPD_KEYFIELDS or DB_UPD_WHERE.
}
1746 \docparam{pWhereClause
}{{\it OPTIONAL
}. If the typeOfUpd is DB_UPD_WHERE,
1747 then you must also pass in a SQL WHERE clause in this argument. Default
1752 This member function allows you to see what the SQL UPDATE statement looks like
1753 that the ODBC class library builds. This can be used for debugging purposes if
1754 you are having problems executing your SQL statement.
1756 WHERE and FROM clauses specified using
\helpref{wxDbTable::SetWhereClause
}{wxdbtablesetwhereclause
}
1757 and
\helpref{wxDbTable::SetFromClause
}{wxdbtablesetfromclause
} are ignored by
1761 \membersection{wxDbTable::BuildWhereStmt
}\label{wxdbtablebuildwherestmt
}
1763 \func{void
}{BuildSelectStmt
}{\param{char *
}{pWhereClause
},
\param{int
}{typeOfWhere
},
\param{const char *
}{qualTableName=NULL
},
\param{const char *
}{useLikeComparison=FALSE
}}
1765 Constructs the portion of a SQL statement which would follow the word 'WHERE'
1766 in a SQL statement to be passed to the datasource. The returned string
1767 does NOT include the word 'WHERE'.
1769 \wxheading{Parameters
}
1771 \docparam{pWhereClause
}{Pointer to storage for the SQL statement retrieved.
1772 To be sure you have adequate space allocated for the SQL statement, allocate
1773 DB_MAX_STATEMENT_LEN bytes.
}
1774 \docparam{typeOfWhere
}{The type of where clause to generate. Can be one of
1775 two values: DB_WHERE_KEYFIELDS or DB_WHERE_MATCHING.
}
1776 \docparam{qualTableName
}{{\it OPTIONAL
}. Prepended to all base table
1777 column names. For use when a FROM clause has been specified with the
1778 \helpref{wxDbTable::SetFromClause
}{wxdbtablesetfromclause
},
1779 to clarify which table a column name reference belongs to. Default is NULL.
}
1780 \docparam{useLikeComparison
}{{\it OPTIONAL
}. Should the constructed WHERE
1781 clause utilize the LIKE comparison operator. If FALSE, then the '='
1782 operator is used. Default is FALSE.
}
1786 This member function allows you to see what the SQL WHERE clause looks like
1787 that the ODBC class library builds. This can be used for debugging purposes
1788 if you are having problems executing your own SQL statements.
1790 If using 'typeOfWhere' set to DB_WHERE_MATCHING, any bound columns currently
1791 containing a NULL value are not included in the WHERE clause's list of
1792 columns to use in the comparison.
1795 \membersection{wxDbTable::CanSelectForUpdate
}\label{wxdbtablecanselectforupdate
}
1797 \func{bool
}{CanSelectForUpdate
}{\void}
1799 Use this function to determine if the datasource supports SELECT ... FOR UPDATE.
1800 When the keywords "FOR UPDATE" are included as part of your SQL SELECT statement,
1801 all records
{\it retrieved
} (not just queried, but actually retrieved using
1802 \helpref{wxDbTable::GetNext
}{wxdbtablegetnext
}, etc) from the result set are
1807 Not all datasources support the "FOR UPDATE" clause, so you must use this
1808 member function to determine if the datasource currently connected to supports
1809 this behavior or not before trying to select using "FOR UPDATE".
1811 If the wxDbTable instance was created with the parameter wxDB_QUERY_ONLY, then
1812 this function will return FALSE. For all known databases which do not support
1813 the FOR UPDATE clause, this function will return FALSE also.
1816 \membersection{wxDbTable::CanUpdateByROWID
}\label{wxdbtablecanupdatebyrowid
}
1818 \func{bool
}{CanUpdateByROWID
}{\void}
1820 CURRENTLY ONLY POSSIBLE IF USING ORACLE.
1822 --- CURRENTLY DISABLED FOR *ALL* DATASOURCES --- NOV
1 2000 - gt
1824 Every Oracle table has a hidden column named ROWID. This is a pointer to the
1825 physical location of the record in the datasource and allows for very fast
1826 updates and deletes. The key is to retrieve this ROWID during your query so
1827 it is available during an update or delete operation.
1829 Use of the ROWID feature is always handled by the class library except in the
1830 case of
\helpref{wxDbTable::QueryBySqlStmt
}{wxdbtablequerybysqlstmt
}. Since
1831 you are passing in the SQL SELECT statement,
1832 it is up to you to include the ROWID column in your query. If you do not,
1833 the application will still work, but may not be as optimized. The ROWID is
1834 always the last column in the column list in your SQL SELECT statement.
1835 The ROWID is not a column in the normal sense and should not be considered
1836 part of the column definitions for the wxDbTable object.
1840 The decision to include the ROWID in your SQL SELECT statement must be
1841 deferred until runtime since it depends on whether you are connected
1842 to an Oracle datasource or not.
1847 // Incomplete code sample
1850 if (parts.CanUpdByROWID())
1852 // Note that the ROWID column must always be the last column selected
1853 sqlStmt = "SELECT PART_NO, PART_DESC, ROWID" FROM PARTS";
1856 sqlStmt = "SELECT PART_NO, PART_DESC" FROM PARTS";
1860 \membersection{wxDbTable::ClearMemberVar
}\label{wxdbtableclearmembervar
}
1862 \func{void
}{ClearMemberVar
}{\param{int
}{colNo
},
\param{bool
}{setToNull=FALSE
}}
1864 Same as
\helpref{wxDbTable::ClearMemberVars
}{wxdbtableclearmembervars
} except
1865 that this function clears only the specified column of its values, and
1866 optionally sets the column to be a NULL column.
1868 \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.
}
1869 \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.
}
1872 \membersection{wxDbTable::ClearMemberVars
}\label{wxdbtableclearmembervars
}
1874 \func{void
}{ClearMemberVars
}{\param{bool
}{setToNull=FALSE
}}
1876 Initializes all bound columns of the wxDbTable instance to zero. In the case
1877 of a string, zero is copied to the first byte of the string.
1879 \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.
}
1883 This is useful before calling functions such as
\helpref{wxDbTable::QueryMatching
}{wxdbtablequerymatching
} or
1884 \helpref{wxDbTable::DeleteMatching
}{wxdbtabledeletematching
} since these
1885 functions build their WHERE clauses from non-zero columns. To call either
1886 \helpref{wxDbTable::QueryMatching
}{wxdbtablequerymatching
} or
1887 \helpref{wxDbTable::DeleteMatching
}{wxdbtabledeletematching
} use this sequence:
1890 1) ClearMemberVars()
1891 2) Assign columns values you wish to match on
1892 3) Call wxDbTable::QueryMatching() or wxDbTable::DeleteMatching()
1896 \membersection{wxDbTable::CloseCursor
}\label{wxdbtableclosecursor
}
1898 \func{bool
}{CloseCursor
}{\param{HSTMT
}{cursor
}}
1900 Closes the specified cursor associated with the wxDbTable object.
1902 \wxheading{Parameters
}
1904 \docparam{cursor
}{The cursor to be closed.
}
1908 Typically handled internally by the ODBC class library, but may be used by the
1909 programmer if desired.
1912 \normalbox{DO NOT CLOSE THE wxDB_DEFAULT_CURSOR!
}
1915 \membersection{wxDbTable::Count
}\label{wxdbtablecount
}
1917 \func{ULONG
}{Count
}{\param{const char *
}{args="*"
}}
1919 Returns the number of records which would be in the result set using the
1920 current query parameters specified in the WHERE and FROM clauses.
1922 \wxheading{Parameters
}
1924 \docparam{args
}{{\it OPTIONAL
}. This arguement allows the use of the
1925 DISTINCT keyword against a column name to cause the returned count to
1926 only indicate the number of rows in the result set that have a unique
1927 value in the specified column. An example is shown below. Default is "*",
1928 meaning a count of the total number of rows matching is returned, regardless of
1933 This function can be called before or after an actual query to obtain the
1934 count of records in the result set. Count() uses its own cursor, so result
1935 set cursor positioning is not affected by calls to Count().
1937 WHERE and FROM clauses specified using
\helpref{wxDbTable::SetWhereClause
}{wxdbtablesetwhereclause
}
1938 and
\helpref{wxDbTable::SetFromClause
}{wxdbtablesetfromclause
} ARE used by
1947 FIRST_NAME LAST_NAME
1948 ----------- ----------
1954 // Incomplete code sample
1957 users.SetWhereClause("");
1959 // This Count() will return
4, as there are four users listed above
1960 // that match the query parameters
1961 totalNumberOfUsers = users.Count();
1963 // This Count() will return
3, as there are only
3 unique first names
1964 // in the table above - John, Richard, Michael.
1965 totalNumberOfUniqueFirstNames = users.Count("DISTINCT FIRST_NAME");
1969 \membersection{wxDbTable::CreateIndex
}\label{wxdbtablecreateindex
}
1971 \func{bool
}{CreateIndex
}{\param{const char *
}{idxName
},
\param{bool
}{unique
},
\param{int
}{noIdxCols
},
\param{wxDbIdxDef *
}{pIdxDefs
},
\param{bool
}{attemptDrop=TRUE
}}
1973 This member function allows you to create secondary (non primary) indexes on
1974 your tables. You first create your table, normally specifying a primary
1975 index, and then create any secondary indexes on the table. Indexes in
1976 relational model are not required. You do not need indexes to look up records
1977 in a table or to join two tables together. In the relational model, indexes,
1978 if available, provide a quicker means to look up data in a table. To enjoy
1979 the performance benefits of indexes, the indexes must be defined on the
1980 appropriate columns and your SQL code must be written in such a way as to
1981 take advantage of those indexes.
1983 \wxheading{Parameters
}
1985 \docparam{idxName
}{Name of the Index. Name must be unique within the table space of the datasource.
}
1986 \docparam{unique
}{Indicates if this index is unique.
}
1987 \docparam{noIdxCols
}{Number of columns in the index.
}
1988 \docparam{pIdxDefs
}{A pointer to an array wxDbIdxDef structures.
}
1989 \docparam{attemptDrop
}{{\it OPTIONAL
}. Indicates if the function should try
1990 to execute a
\helpref{wxDbTable::DropIndex
}{wxdbtabledropindex
} on the index
1991 name provided before trying to create the index name. Default is TRUE.
}
1995 The first parameter, index name, must be unique and should be given a
1996 meaningful name. Common practice is to include the table name as a prefix
1997 in the index name (e.g. For table PARTS, you might want to call your index
1998 PARTS_IDX1). This will allow you to easily view all
1999 of the indexes defined for a given table grouped together alphabetically.
2001 The second parameter indicates if the index is unique or not. Uniqueness
2002 is enforced at the RDBMS level preventing rows which would have duplicate
2003 indexes from being inserted into the table when violating a unique index's
2006 In the third parameter, specify how many columns are in your index. This
2007 number must match the number of columns defined in the 'pIdxDefs' parameter.
2009 The fourth parameter specifies which columns make up the index using the
2010 wxDbIdxDef structure. For each column in the index, you must specify two
2011 things, the column name and the sort order (ascending / descending). See
2012 the example below to see how to build and pass in the wxDbIdxDef structure.
2014 The fifth parameter is provided to handle the differences in datasources as
2015 to whether they will automatically overwrite existing indexes with the same
2016 name or not. Some datasources require that the existing index must be dropped
2017 first, so this is the default behavior.
2019 Some datasources (MySQL, and possibly others) require columns which are to be
2020 part of an index to be defined as NOT NULL. When this function is called, if
2021 a column is not defined to be NOT NULL, a call to this function will modify
2022 the column definition to change any columns included in the index to be
2023 NOT NULL. In this situation, if a NULL value already exists in one of the
2024 columns that is being modified, creation of the index will fail.
2026 PostGres is unable to handle index definitions which specify whether the index
2027 is ascending or descending, and defaults to the system default when the index
2030 It is not necessary to call
\helpref{wxDb::CommitTrans
}{wxdbcommittrans
}
2031 after executing this function.
2036 // Create a secondary index on the PARTS table
2037 wxDbIdxDef idxDef
[2]; //
2 columns make up the index
2039 strcpy(idxDef
[0].ColName, "PART_DESC"); // Column
1
2040 idxDef
[0].Ascending = TRUE;
2042 strcpy(idxDef
[1].ColName, "SERIAL_NO"); // Column
2
2043 idxDef
[1].Ascending = FALSE;
2045 // Create a name for the index based on the table's name
2047 indexName.sprintf("
%s_IDX1",parts->GetTableName());
2048 parts->CreateIndex(indexName.c_str(), TRUE,
2, idxDef);
2052 \membersection{wxDbTable::CreateTable
}\label{wxdbtablecreatetable
}
2054 \func{bool
}{CreateTable
}{\param{bool
}{attemptDrop=TRUE
}}
2056 Creates a table based on the definitions previously defined for this
2059 \wxheading{Parameters
}
2061 \docparam{attemptDrop
}{{\it OPTIONAL
}. Indicates whether the driver should
2062 attempt to drop the table before trying to create it. Some datasources will
2063 not allow creation of a table if the table already exists in the table space
2064 being used. Default is TRUE.
}
2068 This function creates the table and primary index (if any) in the table space
2069 associated with the connected datasource. The owner of these objects will
2070 be the user id that was given when
\helpref{wxDb::Open
}{wxdbopen
} was called. The objects will
2071 be created in the default schema/table space for that user.
2073 In your derived wxDbTable object constructor, the columns and primary index
2074 of the table are described through the
\helpref{wxDbColDef
}{wxdbcoldef
} structure.
2075 \helpref{wxDbTable::CreateTable
}{wxdbtablecreatetable
} uses this information to create the table and to add
2076 the primary index. See
\helpref{wxDbTable
}{wxdbtable
} ctor and wxDbColDef
2077 description for additional information on describing the columns of the table.
2079 It is not necessary to call
\helpref{wxDb::CommitTrans
}{wxdbcommittrans
}
2080 after executing this function.
2083 \membersection{wxDbTable::DB_STATUS
}\label{wxdbtabledbstatus
}
2085 \func{bool
}{DB_STATUS
}{\void}
2087 Accessor function that returns the wxDb private member variable DB_STATUS for
2088 the database connection used by this instance of wxDbTable.
2091 \membersection{wxDbTable::Delete
}\label{wxdbtabledelete
}
2093 \func{bool
}{Delete
}{\void}
2095 Deletes the row from the table indicated by the current cursor.
2099 Use
\helpref{wxDbTable::GetFirst
}{wxdbtablegetfirst
},
\helpref{wxDbTable::GetLast
}{wxdbtablegetlast
},
2100 \helpref{wxDbTable::GetNext
}{wxdbtablegetnext
} or
2101 \helpref{wxDbTable::GetPrev
}{wxdbtablegetprev
} to position the cursor to
2102 a valid record. Once positioned on a record, call this function to delete
2103 the row from the table.
2105 A
\helpref{wxDb::CommitTrans
}{wxdbcommittrans
} or
2106 \helpref{wxDb::RollbackTrans
}{wxdbrollbacktrans
} must be called after use of
2107 this function to commit or rollback the deletion.
2109 NOTE: Most datasources have a limited size "rollback" segment. This means
2110 that it is only possible to insert/update/delete a finite number of rows
2111 without performing a
\helpref{wxDb::CommitTrans
}{wxdbcommittrans
} or
2112 \helpref{wxDb::RollbackTrans
}{wxdbrollbacktrans
}. Size of the rollback
2113 segment varies from database to database, and is user configurable in
2114 most databases. Therefore it is usually best to try to perform a commit
2115 or rollback at relatively small intervals when processing a larger number
2116 of actions that insert/update/delete rows in a table.
2119 \membersection{wxDbTable::DeleteCursor
}\label{wxdbtabledeletecursor
}
2121 \func{bool
}{DeleteCursor
}{\param{HSTMT *
}{hstmtDel
}}
2123 Allows a program to delete a cursor.
2125 \wxheading{Parameters
}
2127 \docparam{hstmtDel
}{Handle of the cursor to delete.
}
2131 For default cursors associated with the instance of wxDbTable, it is not
2132 necessary to specifically delete the cursors. This is automatically done
2133 in the wxDbTable destructor.
2135 NOTE: If the cursor could not be deleted for some reason, an error is logged
2136 indicating the reason. Even if the cursor could not be deleted, the HSTMT
2137 that is passed in is deleted, and the pointer is set to NULL.
2139 \normalbox{DO NOT DELETE THE wxDB_DEFAULT_CURSOR!
}
2142 \membersection{wxDbTable::DeleteMatching
}\label{wxdbtabledeletematching
}
2144 \func{bool
}{DeleteMatching
}{\void}
2146 This member function allows you to delete records from your wxDbTable object
2147 by specifying the data in the columns to match on.
2151 To delete all users with a first name of "JOHN", do the following:
2154 1) Clear all "columns" using wxDbTable::ClearMemberVars().
2155 2) Set the FIRST_NAME column equal to "JOHN".
2156 3) Call wxDbTable::DeleteMatching().
2159 The WHERE clause is built by the ODBC class library based on all non-NULL
2160 columns. This allows deletion of records
2161 by matching on any column(s) in your wxDbTable instance, without having to
2162 write the SQL WHERE clause.
2164 A
\helpref{wxDb::CommitTrans
}{wxdbcommittrans
} or
2165 \helpref{wxDb::RollbackTrans
}{wxdbrollbacktrans
} must be called after use of
2166 this function to commit or rollback the deletion.
2168 NOTE: Row(s) should be locked before deleting them to make sure they are
2169 not already in use. This can be achieved by calling
\helpref{wxDbTable::QueryMatching
}{wxdbtablequerymatching
},
2170 and then retrieving the records, locking each as you go (assuming FOR UPDATE
2171 is allowed on the datasource). After the row(s) have been successfully locked,
2174 NOTE: Most datasources have a limited "rollback" segment. This means
2175 that it is only possible to insert/update/delete a finite number of rows
2176 without performing a
\helpref{wxDb::CommitTrans
}{wxdbcommittrans
} or
2177 \helpref{wxDb::RollbackTrans
}{wxdbrollbacktrans
}. Size of the rollback
2178 segment varies from database to database, and is user configurable in
2179 most databases. Therefore it is usually best to try to perform a commit
2180 or rollback at relatively small intervals when processing a larger number
2181 of actions that insert/update/delete rows in a table.
2187 // Incomplete code sample to delete all users with a first name
2189 users.ClearMemberVars();
2190 strcpy(users.FirstName,"JOHN");
2191 users.DeleteMatching();
2195 \membersection{wxDbTable::DeleteWhere
}\label{wxdbtabledeletewhere
}
2197 \func{bool
}{DeleteWhere
}{\param{const char *
}{pWhereClause
}}
2199 Deletes all rows from the table which match the criteria specified in the
2200 WHERE clause that is passed in.
2202 \wxheading{Parameters
}
2204 \docparam{pWhereClause
}{SQL WHERE clause. This WHERE clause determines which
2205 records will be deleted from the table interfaced through the wxDbTable
2206 instance. The WHERE clause passed in must be compliant with the SQL
92
2207 grammar. Do not include the keyword 'WHERE'
2212 This is the most powerful form of the wxDbTable delete functions. This
2213 function gives access to the full power of SQL. This function can be used
2214 to delete records by passing a valid SQL WHERE clause. Sophisticated
2215 deletions can be performed based on multiple criteria using the full
2216 functionality of the SQL language.
2218 A
\helpref{wxDb::CommitTrans
}{wxdbcommittrans
} must be called after use of
2219 this function to commit the deletions.
2221 Note: This function is limited to deleting records from the table associated
2222 with this wxDbTable object only. Deletions on joined tables is not possible.
2224 NOTE: Most datasources have a limited size "rollback" segment. This means
2225 that it is only possible to insert/update/delete a finite number of rows
2226 without performing a
\helpref{wxDb::CommitTrans
}{wxdbcommittrans
} or
2227 \helpref{wxDb::RollbackTrans
}{wxdbrollbacktrans
}. Size of the rollback
2228 segment varies from database to database, and is user configurable in
2229 most databases. Therefore it is usually best to try to perform a commit
2230 or rollback at relatively small intervals when processing a larger number
2231 of actions that insert/update/delete rows in a table.
2233 WHERE and FROM clauses specified using
\helpref{wxDbTable::SetWhereClause
}{wxdbtablesetwhereclause
}
2234 and
\helpref{wxDbTable::SetFromClause
}{wxdbtablesetfromclause
} are ignored by
2240 // Delete parts
1 thru
10 from containers 'X', 'Y' and 'Z' that
2241 // are magenta in
color
2242 parts.DeleteWhere("(PART_NUMBER BETWEEN
1 AND
10) AND \
2243 CONTAINER IN ('X', 'Y', 'Z') AND \
2244 UPPER(COLOR) = 'MAGENTA'");
2248 \membersection{wxDbTable::DropIndex
}\label{wxdbtabledropindex
}
2250 \func{bool
}{DropIndex
}{\param{const char *
}{idxName
}}
2252 Allows an index on the associated table to be dropped (deleted) if the user
2253 login has sufficient privileges to do so.
2255 \wxheading{Parameters
}
2257 \docparam{idxName
}{Name of the index to be dropped.
}
2261 If the index specified in the 'idxName' parameter does not exist, an error
2262 will be logged, and the function will return a result of FALSE.
2264 It is not necessary to call
\helpref{wxDb::CommitTrans
}{wxdbcommittrans
}
2265 after executing this function.
2268 \membersection{wxDbTable::DropTable
}\label{wxdbtabledroptable
}
2270 \func{bool
}{DropTable
}{\void}
2272 Deletes the associated table if the user has sufficient privileges to do so.
2276 This function returns TRUE if the table does not exist, but only for
2277 supported databases (see
\helpref{wxDb::Dbms
}{wxdbdbms
}). If a datasource
2278 is not specifically supported, and this function is called, the function
2281 Most datasources/ODBC drivers will delete any indexes associated with the
2282 table automatically, and others may not. Check the documentation for your
2283 database to determine the behavior.
2285 It is not necessary to call
\helpref{wxDb::CommitTrans
}{wxdbcommittrans
}
2286 after executing this function.
2289 \membersection{wxDbTable::From
}\label{wxdbtablefrom
}
2291 \func{const char *
}{From
}{}
2293 \func{void
}{From
}{\param{const wxString &
}{From
}}
2295 Accessor function for the private class member wxDbTable::from. Can be used
2296 as a synonym for
\helpref{wxDbTable::GetFromClause
}{wxdbtablegetfromclause
}
2297 (the first form of this function) or
2298 \helpref{wxDbTable::SetFromClause
}{wxdbtablesetfromclause
} (the second form
2301 \wxheading{Parameters
}
2303 \docparam{From
}{A comma separated list of table names that are to be outer
2304 joined with the base table's columns so that the joined table's columns
2305 may be returned in the result set or used as a portion of a comparison with
2306 the base table's columns. NOTE that the base tables name must NOT be included
2307 in the FROM clause, as it is automatically included by the wxDbTable class
2308 in constructing query statements.
}
2310 \wxheading{Return value
}
2312 The first form of this function returns the current value of the wxDbTable
2313 member variable ::from.
2315 The second form of the function has no return value, as it will always set
2316 the from clause successfully.
2318 \wxheading{See also
}
2320 \helpref{wxDbTable::GetFromClause
}{wxdbtablegetfromclause
},
2321 \helpref{wxDbTable::SetFromClause
}{wxdbtablesetfromclause
}
2324 \membersection{wxDbTable::GetColDefs
}\label{wxdbtablegetcoldefs
}
2326 \func{wxDbColDef *
}{GetColDefs
}{}
2328 Accessor function that returns a pointer to the array of column definitions
2329 that are bound to the columns that this wxDbTable instance is associated
2332 To determine the number of elements pointed to by the returned
2333 \helpref{wxDbColDef
}{wxdbcoldef
} pointer, use the
2334 \helpref{wxDbTable::GetNumberOfColumns
}{wxdbtablegetnumberofcolumns
} function.
2338 These column definitions must not be manually redefined after they have been
2342 \membersection{wxDbTable::GetCursor
}\label{wxdbtablegetcursor
}
2344 \func{HSTMT
}{GetCursor
}{\void}
2346 Returns the HSTMT value of the current cursor for this wxDbTable object.
2350 This function is typically used just before changing to use a different cursor
2351 so that after the program is finished using the other cursor, the current
2352 cursor can be set back to being the cursor in use.
2354 \wxheading{See also
}
2356 \helpref{wxDbTable::SetCursor
}{wxdbtablesetcursor
},
\helpref{wxDbTable::GetNewCursor
}{wxdbtablegetnewcursor
}
2359 \membersection{wxDbTable::GetDb
}\label{wxdbtablegetdb
}
2361 \func{wxDb *
}{GetDb
}{}
2363 Accessor function for the private member variable pDb which is a pointer to
2364 the datasource connection that this wxDbTable instance uses.
2367 \membersection{wxDbTable::GetFirst
}\label{wxdbtablegetfirst
}
2369 \func{bool
}{GetFirst
}{\void}
2371 Retrieves the FIRST row in the record set as defined by the current query.
2372 Before retrieving records, a query must be performed using
2373 \helpref{wxDbTable::Query
}{wxdbtablequery
},
2374 \helpref{wxDbTable::QueryOnKeyFields
}{wxdbtablequeryonkeyfields
},
2375 \helpref{wxDbTable::QueryMatching
}{wxdbtablequerymatching
} or
2376 \helpref{wxDbTable::QueryBySqlStmt
}{wxdbtablequerybysqlstmt
}.
2380 This function can only be used if the datasource connection used by the
2381 wxDbTable instance was created with FwdOnlyCursors set to FALSE. If the
2382 connection does not allow backward scrolling cursors, this function will
2383 return FALSE, and the data contained in the bound columns will be undefined.
2385 \wxheading{See also
}
2387 \helpref{wxDb::FwdOnlyCursors
}{wxdbfwdonlycursors
}
2390 \membersection{wxDbTable::GetFromClause
}\label{wxdbtablegetfromclause
}
2392 \func{const char *
}{GetFromClause
}{}
2394 Accessor function that returns the current FROM setting assigned with the
2395 \helpref{wxDbTable::SetFromClause
}{wxdbtablesetfromclause
}.
2397 \wxheading{See also
}
2399 \helpref{wxDbTable::From
}{wxdbtablefrom
}
2402 \membersection{wxDbTable::GetLast
}\label{wxdbtablegetlast
}
2404 \func{bool
}{GetLast
}{\void}
2406 Retrieves the LAST row in the record set as defined by the current query.
2407 Before retrieving records, a query must be performed using
2408 \helpref{wxDbTable::Query
}{wxdbtablequery
},
2409 \helpref{wxDbTable::QueryOnKeyFields
}{wxdbtablequeryonkeyfields
},
2410 \helpref{wxDbTable::QueryMatching
}{wxdbtablequerymatching
} or
2411 \helpref{wxDbTable::QueryBySqlStmt
}{wxdbtablequerybysqlstmt
}.
2415 This function can only be used if the datasource connection used by the
2416 wxDbTable instance was created with FwdOnlyCursors set to FALSE. If the
2417 connection does not allow backward scrolling cursors, this function will
2418 return FALSE, and the data contained in the bound columns will be undefined.
2420 \wxheading{See also
}
2422 \helpref{wxDb::FwdOnlyCursors
}{wxdbfwdonlycursors
}
2425 \membersection{wxDbTable::GetNewCursor
}\label{wxdbtablegetnewcursor
}
2427 \func{HSTMT *
}{GetNewCursor
}{\param{bool
}{setCursor=FALSE
},
\param{bool
}{bindColumns=TRUE
}}
2429 This function will create a new cursor that can be used to access the table
2430 being referenced by this wxDbTable instance, or to execute direct SQL commands
2431 on without affecting the cursors that are already defined and possibly positioned.
2433 \wxheading{Parameters
}
2435 \docparam{setCursor
}{{\it OPTIONAL
}. Should this new cursor be set to be the current cursor after successfully creating the new cursor. Default is FALSE.
}
2436 \docparam{bindColumns
}{{\it OPTIONAL
}. Should this new cursor be bound to all the memory variables that the default cursor is bound to. Default is TRUE.
}
2440 This new cursor must be closed using
\helpref{wxDbTable::DeleteCursor
}{wxdbtabledeletecursor
}
2441 by the calling program before the wxDbTable instance is deleted, or both memory
2442 and resource leaks will occur.
2445 \membersection{wxDbTable::GetNext
}\label{wxdbtablegetnext
}
2447 \func{bool
}{GetNext
}{\void}
2449 Retrieves the NEXT row in the record set after the current cursor position
2450 as defined by the current query. Before retrieving records, a query must be
2451 performed using
\helpref{wxDbTable::Query
}{wxdbtablequery
},
2452 \helpref{wxDbTable::QueryOnKeyFields
}{wxdbtablequeryonkeyfields
},
2453 \helpref{wxDbTable::QueryMatching
}{wxdbtablequerymatching
} or
2454 \helpref{wxDbTable::QueryBySqlStmt
}{wxdbtablequerybysqlstmt
}.
2456 \wxheading{Return value
}
2458 This function returns FALSE when the current cursor has reached the end of
2459 the result set. When FALSE is returned, data in the bound columns is
2464 This function works with both forward and backward scrolling cursors.
2466 \wxheading{See also
}
2467 \helpref{wxDbTable::++
}{wxdbtableplusplus
}
2470 \membersection{wxDbTable::GetNumberOfColumns
}\label{wxdbtablegetnumberofcolumns
}
2472 \func{bool
}{GetNumberOfColumns
}{}
2474 Accessor function that returns the number of columns that are statically
2475 bound for access by the wxDbTable instance.
2478 \membersection{wxDbTable::GetOrderByClause
}\label{wxdbtablegetorderbyclause
}
2480 \func{const char *
}{GetOrderByClause
}{}
2482 Accessor function that returns the current ORDER BY setting assigned with
2483 the
\helpref{wxDbTable::SetOrderByClause
}{wxdbtablesetorderbyclause
}.
2485 \wxheading{See also
}
2487 \helpref{wxDbTable::OrderBy
}{wxdbtableorderby
}
2490 \membersection{wxDbTable::GetPrev
}\label{wxdbtablegetprev
}
2492 \func{bool
}{GetPrev
}{\void}
2494 Retrieves the PREVIOUS row in the record set before the current cursor
2495 position as defined by the current query. Before retrieving records, a
2496 query must be performed using
\helpref{wxDbTable::Query
}{wxdbtablequery
},
2497 \helpref{wxDbTable::QueryOnKeyFields
}{wxdbtablequeryonkeyfields
},
2498 \helpref{wxDbTable::QueryMatching
}{wxdbtablequerymatching
} or
2499 \helpref{wxDbTable::QueryBySqlStmt
}{wxdbtablequerybysqlstmt
}.
2501 \wxheading{Return value
}
2503 This function returns FALSE when the current cursor has reached the beginning
2504 of the result set and there are now other rows prior to the cursors current
2505 position. When FALSE is returned, data in the bound columns is undefined.
2509 This function can only be used if the datasource connection used by the
2510 wxDbTable instance was created with FwdOnlyCursors set to FALSE. If the
2511 connection does not allow backward scrolling cursors, this function will
2512 return FALSE, and the data contained in the bound columns will be undefined.
2514 \wxheading{See also
}
2516 \helpref{wxDb::FwdOnlyCursors
}{wxdbfwdonlycursors
},
2517 \helpref{wxDbTable::--
}{wxdbtableminusminus
}
2520 \membersection{wxDbTable::GetQueryTableName
}\label{wxdbtablegetquerytablename
}
2522 \func{const char *
}{GetQueryTableName
}{}
2524 Accessor function that returns the name of the table/view that was indicated
2525 as being the table/view to query against when this wxDbTable instance was
2528 \wxheading{See also
}
2530 \helpref{wxDbTable constructor
}{wxdbtableconstr
}
2533 \membersection{wxDbTable::GetRowNum
}\label{wxdbtablegetrownum
}
2535 \func{UWORD
}{GetRowNum
}{\void}
2537 Returns the ODBC row number for performing positioned updates and deletes.
2541 This function is not being used within the ODBC class library and may be a
2542 candidate for removal if no use is found for it.
2544 Row nuumber with some datasources/ODBC drivers is the position in the result set,
2545 while in others it may be a physical position in the database. Check your
2546 database documentation to find out which behavior is supported.
2549 \membersection{wxDbTable::GetTableName
}\label{wxdbtablegettablename
}
2551 \func{const char *
}{GetTableName
}{}
2553 Accessor function that returns the name of the table that was indicated
2554 as being the table that this wxDbTable instance was associated with.
2557 \membersection{wxDbTable::GetTablePath
}\label{wxdbtablegettablepath
}
2559 \func{const char *
}{GetTablePath
}{}
2561 Accessor function that returns the path to the data table that was indicated
2562 during creation of this wxDbTable instance.
2566 Currently only applicable to dBase and MS-Access datasources.
2569 \membersection{wxDbTable::GetWhereClause
}\label{wxdbtablegetwhereclause
}
2571 \func{const char *
}{GetWhereClause
}{}
2573 Accessor function that returns the current WHERE setting assigned with the
2574 \helpref{wxDbTable::SetWhereClause
}{wxdbtablesetwhereclause
}
2576 \wxheading{See also
}
2578 \helpref{wxDbTable::Where
}{wxdbtablewhere
}
2581 \membersection{wxDbTable::Insert
}\label{wxdbtableinsert
}
2583 \func{int
}{Insert
}{\void}
2585 Inserts a new record into the table being referenced by this wxDbTable
2586 instance. The values in the member variables of the wxDbTable instance are
2587 inserted into the columns of the new row in the database.
2589 \wxheading{Return value
}
2592 DB_SUCCESS Record inserted successfully (value =
1)
2594 DB_FAILURE Insert failed (value =
0)
2596 DB_ERR_INTEGRITY_CONSTRAINT_VIOL
2597 The insert failed due to an integrity
2598 constraint violation (duplicate non-unique
2599 index entry) is attempted.
2604 A
\helpref{wxDb::CommitTrans
}{wxdbcommittrans
} or
2605 \helpref{wxDb::RollbackTrans
}{wxdbrollbacktrans
} must be called after use of
2606 this function to commit or rollback the insertion.
2611 // Incomplete code snippet
2612 strcpy(parts->PartName, "
10");
2613 strcpy(parts->PartDesc, "Part
#10");
2615 RETCODE retcode = parts.Insert();
2619 parts->GetDb()->CommitTrans();
2621 case DB_ERR_INTEGRITY_CONSTRAINT_VIOL:
2622 // Current data would result in a duplicate key
2623 // on one or more indexes that do not allow duplicates
2624 parts->GetDb()->RollbackTrans();
2627 // Insert failed for some unexpected reason
2628 parts->GetDb()->RollbackTrans();
2634 \membersection{wxDbTable::IsColNull
}\label{wxdbtableiscolnull
}
2636 \func{bool
}{IsColNull
}{\param{int
}{colNo
}}
2638 Used primarily in the ODBC class library to determine if a column is equal
2639 to "NULL". Works for all data types supported by the ODBC class library.
2641 \wxheading{Parameters
}
2643 \docparam{colNo
}{The column number of the bound column as defined by the
2644 \helpref{wxDbTable::SetColDefs
}{wxdbtablesetcoldefs
}
2645 calls which defined the columns accesible to this wxDbTable instance.
}
2649 NULL column support is currently not fully implemented as of wxWindows
2.4
2652 \membersection{wxDbTable::IsCursorClosedOnCommit
}\label{wxdbtableiscursorclosedoncommit
}
2654 \func{bool
}{IsCursorClosedOnCommit
}{\void}
2656 Accessor function to return information collected during the opening of the
2657 datasource connection that is used by this wxDbTable instance. The result
2658 returned by this function indicates whether an implicit closing of the curosr is
2659 done after a commit on the database connection.
2661 \wxheading{Return value
}
2663 Returns TRUE if the cursor associated with this wxDbTable object is closed
2664 after a commit or rollback operation. Returns FALSE otherwise.
2668 If more than one wxDbTable instance used the same database connection, all cursors
2669 which use the database connection are closed on the commit if this function
2673 \membersection{wxDbTable::IsQueryOnly
}\label{wxdbtableisqueryonly
}
2675 \func{bool
}{IsQueryOnly
}{}
2677 Accessor function that returns a value indicating if this wxDbTable instance
2678 was created to allow only queries to be performed on the bound columns. If
2679 this function returns TRUE, then no actions may be performed using this
2680 wxDbTable instance that would modify (insert/delete/update) the table's data.
2683 \membersection{wxDbTable::Open
}\label{wxdbtableopen
}
2685 \func{bool
}{Open
}{\param{bool
}{checkPrivileges=FALSE
}}
2687 Every wxDbTable instance must be opened before it can be used. This function
2688 checks for the existence of the requested table, binds columns, creates required
2689 cursors, (insert/select and update if connection is not wxDB_QUERY_ONLY) and
2690 constructs the insert statement that is to be used for inserting data as a new
2691 row in the datasource.
2693 \wxheading{Parameters
}
2695 \docparam{checkPrivileges
}{Indicates whether the Open() function should check
2696 whether the current connected user has at least SELECT privileges to access the
2697 table to which they are trying to open. Default is FALSE.
}
2701 If the function returns a FALSE value due to the table not existing, a log
2702 entry is recorded for the datasource connection indicating the problem
2703 that was detected when checking for table existence. Note that it is usually
2704 best for the calling routine to check for the existence of the table and for
2705 sufficent user privileges to access the table in the mode (wxDB_QUERY_ONLY or
2706 !wxDB_QUERY_ONLY) before trying to open the table for the best possible
2707 explanation as to why a table cannot be opened.
2709 Checking the user's privileges on a table can be quite time consuming during
2710 the open phase. With most applications, the programmer already knows that the
2711 user has sufficient privileges to access the table, so this check is normally
2714 For best performance, open the table, and then use the
2715 \helpref{wxDb::TablePrivileges
}{wxdbtableprivileges
} function
2716 to check the users privileges. Passing a schema to the TablePrivileges()
2717 function can significantly speed up the privileges checks.
2719 \wxheading{See also
}
2721 \helpref{wxDb::TableExists
}{wxdbtableexists
},
2722 \helpref{wxDb::TablePrivileges
}{wxdbtableprivileges
}
2725 \membersection{wxDbTable::OrderBy
}\label{wxdbtableorderby
}
2727 \func{const char *
}{OrderBy
}{}
2729 \func{void
}{OrderBy
}{\param{const wxString &
}{OrderBy
}}
2731 Accessor function for the private class member wxDbTable::orderBy. Can be
2732 used as a synonym for
\helpref{wxDbTable::GetOrderByClause
}{wxdbtablegetorderbyclause
}
2733 (the first form of this function) or
\helpref{wxDbTable::SetOrderByClause
}{wxdbtablesetorderbyclause
}
2734 (the second form of this function).
2736 \wxheading{Parameters
}
2738 \docparam{OrderBy
}{A comma separated list of column names that indicate the
2739 alphabetized/numeric sorting sequence that the result set is to be returned
2740 in. If a FROM clause has also been specified, each column name specified in
2741 the ORDER BY clause should be prefaced with the table name to which the column
2742 belongs using DOT notation (TABLE_NAME.COLUMN_NAME).
}
2744 \wxheading{Return value
}
2746 The first form of this function returns the current value of the wxDbTable
2747 member variable ::orderBy.
2749 The second form of the function has no return value.
2751 \wxheading{See also
}
2753 \helpref{wxDbTable::GetOrderByClause
}{wxdbtablegetorderbyclause
},
2754 \helpref{wxDbTable::SetFromClause
}{wxdbtablesetfromclause
}
2757 \membersection{wxDbTable::Query
}\label{wxdbtablequery
}
2759 \func{virtual bool
}{Query
}{\param{bool
}{forUpdate=FALSE
},
\param{bool
}{distinct=FALSE
}}
2761 \wxheading{Parameters
}
2763 \docparam{forUpdate
}{{\it OPTIONAL
}. Gives you the option of locking records
2764 as they are retrieved. If the RDBMS is not capable of the FOR UPDATE clause,
2765 this argument is ignored. See
2766 \helpref{wxDbTable::CanSelectForUpdate
}{wxdbtablecanselectforupdate
} for
2767 additional information regarding this argument. Default is FALSE.
}
2768 \docparam{distinct
}{{\it OPTIONAL
}. Allows selection of only distinct values
2769 from the query (SELECT DISTINCT ... FROM ...). The notion of DISTINCT
2770 applies to all columns returned in the result set, not individual columns.
2775 This function queries records from the datasource based on the three
2776 wxDbTable members: "where", "orderBy", and "from". Use
2777 \helpref{wxDbTable::SetWhereClause
}{wxdbtablesetwhereclause
} to filter on
2778 records to be retrieved (e.g. All users with a first name of "JOHN").
2779 Use
\helpref{wxDbTable::SetOrderByClause
}{wxdbtablesetorderbyclause
} to
2780 change the sequence in which records are returned in the result set from
2781 the datasource (e.g. Ordered by LAST_NAME). Use
2782 \helpref{wxDbTable::SetFromClause
}{wxdbtablesetfromclause
} to allow outer
2783 joining of the base table (the one being associated with this instance of
2784 wxDbTable) with other tables which share a related field.
2786 After each of these clauses are set/cleared, call wxDbTable::Query() to
2787 fetch the result set from the datasource.
2789 This scheme has an advantage if you have to requery your record set
2790 frequently in that you only have to set your WHERE, ORDER BY, and FROM
2791 clauses once. Then to refresh the record set, simply call wxDbTable::Query()
2792 as frequently as needed.
2794 Note that repeated calls to wxDbTable::Query() may tax the database
2795 server and make your application sluggish if done too frequently or
2798 The base table name is automatically prepended to the base column names in
2799 the event that the FROM clause has been set (is non-null) using
2800 \helpref{wxDbTable::SetFromClause
}{wxdbtablesetfromclause
}.
2802 The cursor for the result set is positioned
{\it before
} the first record in
2803 the result set after the query. To retrieve the first record, call either
2804 \helpref{wxDbTable::GetFirst
}{wxdbtablegetfirst
} (only if backward scrolling
2805 cursors are available) or
2806 \helpref{wxDbTable::GetNext
}{wxdbtablegetnext
}. Typically, no data from the
2807 result set is returned to the client driver until a request such as
2808 \helpref{wxDbTable::GetNext
}{wxdbtablegetnext
} is performed, so network
2809 traffic and database load are not overwhelmed transmitting data until the
2810 data is actually requested by the client. This behavior is solely dependent
2811 on the ODBC driver though, so refer to the ODBC driver's reference material
2812 for information on its behaviors.
2814 Values in the bound columns' memory variables are undefined after executing a
2815 call to this function and remain that way until a row in the result set is
2816 requested to be returned.
2818 The wxDbTable::Query() function is defined as "virtual" so that it may be
2819 overridden for application specific purposes.
2821 \normalbox{Be sure to set the wxDbTable's "where", "orderBy", and "from"
2822 member variables to "" if they are not to be used in the query. Otherwise,
2823 the results returned may have unexpected results (or no results) due to
2824 improper or incorrect query parameters constructed from the uninitialized
2830 // Incomplete code sample
2831 parts->SetWhereClause("DESCRIPTION = 'FOOD'");
2832 parts->SetOrderByClause("EXPIRATION_DATE");
2833 parts->SetFromClause("");
2834 // Query the records based on the where, orderBy and from clauses
2837 // Display all records queried
2838 while(parts->GetNext())
2839 dispPart(parts); // user defined function
2843 \membersection{wxDbTable::QueryBySqlStmt
}\label{wxdbtablequerybysqlstmt
}
2845 \func{bool
}{QueryBySqlStmt
}{\param{const char *
}{pSqlStmt
}}
2847 Performs a query against the datasource by accepting and passing verbatim the
2848 SQL SELECT statement passed to the function.
2850 \wxheading{Parameters
}
2852 \docparam{pSqlStmt
}{Pointer to the SQL SELECT statement to be executed.
}
2856 This is the most powerful form of the query functions available. This member
2857 function allows a programmer to write their own custom SQL SELECT statement
2858 for requesting data from the datasource. This gives the programmer access
2859 to the full power of SQL for performing operations such as scalar functions,
2860 aggregate functions, table joins, and sub-queries, as well as datasource
2861 specific function calls.
2863 The requirements of the SELECT statement are the following:
2866 1. Must return the correct number of columns. In the derived
2867 wxDbTable constructor, it is specified how many columns are in
2868 the wxDbTable object. The SELECT statement must return exactly
2871 2. The columns must be returned in the same sequence as specified
2872 when defining the bounds columns using wxDbTable::SetColDefs(),
2873 and the columns returned must be of the proper data type. For
2874 example, if column
3 is defined in the wxDbTable bound column
2875 definitions to be a float, the SELECT statement must return a
2876 float for column
3 (e.g. PRICE *
1.10 to increase the price by
2879 3. The ROWID can be included in your SELECT statement as the
{\bf last
}
2880 column selected, if the datasource supports it. Use
2881 wxDbTable::CanUpdByROWID() to determine if the ROWID can be
2882 selected from the datasource. If it can, much better
2883 performance can be achieved on updates and deletes by including
2884 the ROWID in the SELECT statement.
2887 Even though data can be selected from multiple tables (joins) in your select
2888 statement, only the base table associated with this wxDbTable object
2889 is automatically updated through the ODBC class library. Data from multiple
2890 tables can be selected for display purposes however. Include columns in
2891 the wxDbTable object and mark them as non-updateable (See
2892 \helpref{wxDbColDef
}{wxdbcoldef
} for details). This way columns can be
2893 selected and displayed from other tables, but only the base table will be
2894 updated automatically when performed through the
2895 \helpref{wxDbTable::Update
}{wxdbtableupdate
} function after using this type of
2896 query. To update tables other than the base table, use the
2897 \helpref{wxDbTable::Update
}{wxdbtableupdate
} function passing a SQL statement.
2899 After this function has been called, the cursor is positioned before the
2900 first record in the record set. To retrieve the first record, call
2901 either
\helpref{wxDbTable::GetFirst
}{wxdbtablegetfirst
} or
2902 \helpref{wxDbTable::GetNext
}{wxdbtablegetnext
}.
2907 // Incomplete code samples
2908 strcpy(sqlStmt, "SELECT * FROM PARTS WHERE STORAGE_DEVICE = 'SD98' \
2909 AND CONTAINER =
12");
2910 // Query the records using the SQL SELECT statement above
2911 parts->QueryBySqlStmt(sqlStmt);
2912 // Display all records queried
2913 while(parts->GetNext())
2916 Example SQL statements
2917 ----------------------
2919 // Table Join returning
3 columns
2920 SELECT part_no, part_desc, sd_name
2921 from parts, storage_devices
2922 where parts.storage_device_id =
2923 storage_devices.storage_device_id
2925 // Aggregate function returning total number of
2926 // parts in container
99
2927 SELECT count
(*) from PARTS where container = 99
2929 // Order by clause; ROWID, scalar function
2930 SELECT part_no, substring(part_desc, 1, 10), qty_on_hand + 1, ROWID
2932 where warehouse = 10
2933 order by part_no desc // descending order
2937 where container in (select container
2938 from storage_devices
2939 where device_id = 12)
2943 \membersection{wxDbTable::QueryMatching}\label{wxdbtablequerymatching}
2945 \func{virtual bool}{QueryMatching}{\param{bool }{forUpdate=FALSE}, \param{bool }{distinct=FALSE}}
2947 QueryMatching allows querying of records from the table associated with
2948 the wxDbTable object by matching "columns" to values.
2950 For example: To query the datasource for the row with a PART_NUMBER column
2951 value of "32", clear all column variables of the wxDbTable object, set the
2952 PartNumber variable that is bound to the PART_NUMBER column in the wxDbTable
2953 object to "32", and then call wxDbTable::QueryMatching().
2955 \wxheading{Parameters}
2957 \docparam{forUpdate}{{\it OPTIONAL}. Gives you the option of locking records
2958 as they are queried (SELECT ... FOR UPDATE). If the RDBMS is not capable of
2959 the FOR UPDATE clause, this argument is ignored. See
2960 \helpref{wxDbTable::CanSelectForUpdate}{wxdbtablecanselectforupdate} for
2961 additional information regarding this argument. Default is FALSE.}
2962 \docparam{distinct}{{\it OPTIONAL}. Allows selection of only distinct values
2963 from the query (SELECT DISTINCT ... FROM ...). The notion of DISTINCT
2964 applies to all columns returned in the result set, not individual columns.
2969 The SQL WHERE clause is built by the ODBC class library based on all
2970 non-zero/non-NULL columns in your wxDbTable object. Matches can be on one,
2971 many or all of the wxDbTable's columns. The base table name is prepended
2972 to the column names in the event that the wxDbTable's FROM clause is non-null.
2974 This function cannot be used to perform queries which will check for
2975 columns that are 0 or NULL, as the automatically constructed WHERE clause
2976 only will contain comparisons on column member variables that are
2979 The primary difference between this function and \helpref{wxDbTable::QueryOnKeyFields}{wxdbtablequeryonkeyfields}
2980 is that this function can query on any column(s) in the wxDbTable object.
2981 Note however that this may not always be very efficient. Searching on
2982 non-indexed columns will always require a full table scan.
2984 The cursor is positioned before the first record in the record set after
2985 the query is performed. To retrieve the first record, the program must call
2986 either \helpref{wxDbTable::GetFirst}{wxdbtablegetfirst} or
2987 \helpref{wxDbTable::GetNext}{wxdbtablegetnext}.
2989 WHERE and FROM clauses specified using \helpref{wxDbTable::SetWhereClause}{wxdbtablesetwhereclause}
2990 and \helpref{wxDbTable::SetFromClause}{wxdbtablesetfromclause} are ignored by
2996 // Incomplete code sample
2997 parts->ClearMemberVars(); // Set all columns to zero
2998 strcpy(parts->PartNumber,"32"); // Set columns to query on
2999 parts->OnHold = TRUE;
3000 parts->QueryMatching(); // Query
3001 // Display all records queried
3002 while(parts->GetNext())
3003 dispPart(parts); // Some application defined function
3007 \membersection{wxDbTable::QueryOnKeyFields}\label{wxdbtablequeryonkeyfields}
3009 \func{bool}{QueryOnKeyFields}{\param{bool }{forUpdate=FALSE}, \param{bool }{distinct=FALSE}}
3011 QueryOnKeyFields provides an easy mechanism to query records in the table
3012 associated with the wxDbTable object by the primary index column(s). Simply
3013 assign the primary index column(s) values and then call this member function
3014 to retrieve the record.
3016 Note that since primary indexes are always unique, this function implicitly
3017 always returns a single record from the database. The base table name is
3018 prepended to the column names in the event that the wxDbTable's FROM clause
3021 \wxheading{Parameters}
3023 \docparam{forUpdate}{{\it OPTIONAL}. Gives you the option of locking records
3024 as they are queried (SELECT ... FOR UPDATE). If the RDBMS is not capable of
3025 the FOR UPDATE clause, this argument is ignored. See
3026 \helpref{wxDbTable::CanSelectForUpdate}{wxdbtablecanselectforupdate} for
3027 additional information regarding this argument. Default is FALSE.}
3028 \docparam{distinct}{{\it OPTIONAL}. Allows selection of only distinct values
3029 from the query (SELECT DISTINCT ... FROM ...). The notion of DISTINCT
3030 applies to all columns returned in the result set, not individual columns.
3035 The cursor is positioned before the first record in the record set after
3036 the query is performed. To retrieve the first record, the program must call
3037 either \helpref{wxDbTable::GetFirst}{wxdbtablegetfirst} or
3038 \helpref{wxDbTable::GetNext}{wxdbtablegetnext}.
3040 WHERE and FROM clauses specified using \helpref{wxDbTable::SetWhereClause}{wxdbtablesetwhereclause}
3041 and \helpref{wxDbTable::SetFromClause}{wxdbtablesetfromclause} are ignored by
3047 // Incomplete code sample
3048 strcpy(parts->PartNumber, "32");
3049 parts->QueryOnKeyFields();
3050 // Display all records queried
3051 while(parts->GetNext())
3052 dispPart(parts); // Some application defined function
3056 \membersection{wxDbTable::Refresh}\label{wxdbtablerefresh}
3058 \func{bool}{Refresh}{\void}
3060 This function re-reads the bound columns into the memory variables, setting
3061 them to the current values stored on the disk.
3063 The cursor position and result set are unaffected by calls to this function.
3064 (The one exception is in the case where the record to be refreshed has been
3065 deleted by some other user or transaction since it was originally retrieved
3066 as part of the result set. For most datasources, the default behavior in
3067 this situation is to return the value that was originally queried for the
3068 result set, even though it has been deleted from the database. But this is
3069 datasource dependent, and should be tested before relying on this behavior.)
3073 This routine is only guaranteed to work if the table has a unique primary
3074 index defined for it. Otherwise, more than one record may be fetched and
3075 there is no guarantee that the correct record will be refreshed. The
3076 table's columns are refreshed to reflect the current data in the database.
3079 \membersection{wxDbTable::SetColDefs}\label{wxdbtablesetcoldefs}
3081 \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}}
3083 \func{wxDbColDataPtr *}{SetColDefs}{\param{wxDbColInf *}{colInfs}, \param{ULONG }{numCols}}
3085 \wxheading{Parameters}
3087 \docparam{index}{Column number (0 to n-1, where n is the number of columns
3088 specified as being defined for this wxDbTable instance when the
3089 wxDbTable constructor was called.}
3090 \docparam{fieldName}{Column name from the associated data table.}
3091 \docparam{dataType}{Logical data type. Valid logical types include:}
3094 DB_DATA_TYPE_VARCHAR : strings
3095 DB_DATA_TYPE_INTEGER : non-floating point numbers
3096 DB_DATA_TYPE_FLOAT : floating point numbers
3097 DB_DATA_TYPE_DATE : dates
3100 \docparam{pData}{Pointer to the data object that will hold the column's
3101 value when a row of data is returned from the datasource.}
3102 \docparam{cType}{SQL C Type. This defines the data type that the SQL
3103 representation of the data is converted to to be stored in {\it pData}.
3104 Other valid types are available also, but these are the most common ones:}
3107 SQL_C_CHAR // strings
3117 SQL_C_BOOLEAN // defined in db.h
3118 SQL_C_ENUM // defined in db.h
3121 \docparam{size}{Maximum size in bytes of the {\it pData} object.}
3122 \docparam{keyField}{{\it OPTIONAL}. Indicates if this column is part of the
3123 primary index. Default is FALSE.}
3124 \docparam{upd}{{\it OPTIONAL}. Are updates allowed on this column?
3126 \docparam{insAllow}{{\it OPTIONAL}. Inserts allowed on this column?
3128 \docparam{derivedCol}{{\it OPTIONAL}. Is this a derived column (non-base
3129 table column for query only)? Default is FALSE.}
3131 \docparam{colInfs}{Pointer to an array of wxDbColInf instances which contains
3132 all the information necessary to create {\it numCols} column definitions.}
3133 \docparam{numCols}{Number of elements of wxDbColInf type that are pointed
3134 to by {\it colInfs}, which are to have column definitions created from them.}
3138 If {\it pData} is to hold a string of characters, be sure to include enough
3139 space for the NULL terminator in pData and in the byte count of {\it size}.
3141 Both forms of this function provide a shortcut for defining the columns in
3142 your wxDbTable object. Use this function in any derived wxDbTable
3143 constructor when describing the column/columns in the wxDbTable object.
3145 The second form of this function is primarily used when the
3146 \helpref{wxDb::GetColumns}{wxdbgetcolumns} function was used to query the
3147 datasource for the column definitions, so that the column definitions are
3148 already stored in wxDbColInf form. One example use of using
3149 \helpref{wxDb::GetColumns}{wxdbgetcolumns} then using this function is if
3150 a data table existed in one datasource, and the table's column definitions
3151 were to be copied over to another datasource or table.
3156 // Long way not using this function
3157 strcpy(colDefs[0].ColName, "PART_NO");
3158 colDefs[0].DbDataType = DB_DATA_TYPE_VARCHAR;
3159 colDefs[0].PtrDataObj = PartNumber;
3160 colDefs[0].SqlCtype = SQL_C_CHAR;
3161 colDefs[0].SzDataObj = PART_NUMBER_LEN;
3162 colDefs[0].KeyField = TRUE;
3163 colDefs[0].Updateable = FALSE;
3164 colDefs[0].InsertAllowed= TRUE;
3165 colDefs[0].DerivedCol = FALSE;
3167 // Shortcut using this function
3168 SetColDefs(0, "PART_NUMBER", DB_DATA_TYPE_VARCHAR, PartNumber,
3169 SQL_C_CHAR, PART_NUMBER_LEN, TRUE, FALSE,TRUE,FALSE);
3173 \membersection{wxDbTable::SetCursor}\label{wxdbtablesetcursor}
3175 \func{bool}{SetCursor}{\param{HSTMT *}{hstmtActivate = (void **) wxDB_DEFAULT_CURSOR
}}
3177 \wxheading{Parameters
}
3179 \docparam{hstmtActivate
}{{\it OPTIONAL
}. Pointer to the cursor that is to
3180 become the current cursor. Passing no cursor handle will reset the cursor
3181 back to the wxDbTable's default (original) cursor that was created when the
3182 wxDbTable instance was first created. Default is wxDB_DEFAULT_CURSOR.
}
3186 When swapping between cursors, the member variables of the wxDbTable object
3187 are automatically refreshed with the column values of the row that the
3188 current cursor is positioned at (if any). If the cursor is not positioned,
3189 then the data in member variables is undefined.
3191 The only way to return back to the cursor that was in use before this
3192 function was called is to programmatically determine the current cursor's HSTMT
3193 {\bf BEFORE
} calling this function using
\helpref{wxDbTable::GetCursor
}{wxdbtablegetcursor
}
3194 and saving a pointer to that cursor.
3196 \wxheading{See also
}
3198 \helpref{wxDbTable::GetNewCursor
}{wxdbtablegetnewcursor
},
\helpref{wxDbTable::GetCursor
}{wxdbtablegetcursor
},
3199 \helpref{wxDbTable::SetCursor
}{wxdbtablesetcursor
}
3202 \membersection{wxDbTable::SetFromClause
}\label{wxdbtablesetfromclause
}
3204 \func{void
}{SetFromClause
}{\param{const wxString &
}{From
}}
3206 Accessor function for setting the private class member wxDbTable::from
3207 that indicates what other tables should be outer joined with the wxDbTable's
3208 base table for access to the columns in those other tables.
3210 Synonym to this function is one form of
\helpref{wxDbTable::From
}{wxdbtablefrom
}
3212 \wxheading{Parameters
}
3214 \docparam{From
}{A comma separated list of table names that are to be outer
3215 joined with the base table's columns so that the joined table's columns
3216 may be returned in the result set or used as a portion of a comparison with
3217 the base table's columns. NOTE that the base tables name must NOT be included
3218 in the FROM clause, as it is automatically included by the wxDbTable class
3219 in constructing query statements.
}
3223 Used by the
\helpref{wxDbTable::Query
}{wxdbtablequery
} and
3224 \helpref{wxDbTable::Count
}{wxdbtablecount
} member functions to allow outer
3225 joining of records from multiple tables.
3227 Do
{\bf not
} include the keyword "FROM" when setting the FROM clause.
3229 If using the FROM clause when performing a query, be certain to include in
3230 the corresponding WHERE clause a comparison of a column from either the base
3231 table or one of the other joined tables to each other joined table to ensure
3232 the datasource knows on which column values the tables should be joined on.
3238 // Base table is the "LOCATION" table, and it is being
3239 // outer joined to the "PART" table via the the field "PART_NUMBER"
3240 // that can be related between the two tables.
3241 location->SetWhereClause("LOCATION.PART_NUMBER = PART.PART_NUMBER")
3242 location->SetFromClause("PART");
3246 \wxheading{See also
}
3248 \helpref{wxDbTable::From
}{wxdbtablefrom
},
3249 \helpref{wxDbTable::GetFromClause
}{wxdbtablegetfromclause
}
3252 \membersection{wxDbTable::SetColNull
}\label{wxdbtablesetcolnull
}
3254 \func{bool
}{SetColNull
}{\param{int
}{colNo
},
\param{bool
}{set=TRUE
}}
3256 \func{bool
}{SetColNull
}{\param{const char *
}{colName
},
\param{bool
}{set=TRUE
}}
3258 Both forms of this function allow a member variable representing a column
3259 in the table associated with this wxDbTable object to be set to NULL.
3261 The first form allows the column to be set by the index into the column
3262 definitions used to create the wxDbTable instance, while the second allows
3263 the actual column name to be specified.
3265 \wxheading{Parameters
}
3267 \docparam{colNo
}{Index into the column definitions used when first defining
3268 this wxDbTable object.
}
3269 \docparam{colName
}{Actual data table column name that is to be set to NULL.
}
3270 \docparam{set
}{Whether the column is set to NULL or not. Passing TRUE sets
3271 the column to NULL, passing FALSE sets the column to be non-NULL. Default is
3277 No database updates are done by this function. It only operates on the
3278 member variables in memory. Use and insert or update function to store this
3282 \membersection{wxDbTable::SetOrderByClause
}\label{wxdbtablesetorderbyclause
}
3284 \func{void
}{SetOrderByClause
}{\param{const wxString &
}{OrderBy
}}
3286 Accessor function for setting the private class member wxDbTable::orderBy
3287 which determines sequence/ordering of the rows returned in the result set
3290 A synonym to this function is one form of the function
\helpref{wxDbTable::OrderBy
}{wxdbtableorderby
}
3292 \wxheading{Parameters
}
3294 \docparam{OrderBy
}{A comma separated list of column names that indicate the
3295 alphabetized sorting sequence that the result set is to be returned in. If
3296 a FROM clause has also been specified, each column name specified in the
3297 ORDER BY clause should be prefaced with the table name to which the column
3298 belongs using DOT notation (TABLE_NAME.COLUMN_NAME).
}
3302 Do
{\bf not
} include the keywords "ORDER BY" when setting the ORDER BY clause.
3308 parts->SetOrderByClause("PART_DESCRIP, QUANTITY");
3312 location->SetOrderByClause("LOCATION.POSITION, PART.PART_NUMBER);
3316 \wxheading{See also
}
3318 \helpref{wxDbTable::OrderBy
}{wxdbtableorderby
},
3319 \helpref{wxDbTable::GetOrderByClause
}{wxdbtablegetorderbyclause
}
3322 \membersection{wxDbTable::SetQueryTimeout
}\label{wxdbtablesetquerytimeout
}
3324 \func{bool
}{SetQueryTimeout
}{\param{UDWORD
}{nSeconds
}}
3326 Allows a time period to be set as the timeout period for queries.
3328 \wxheading{Parameters
}
3330 \docparam{nSeconds
}{The number of seconds to wait for the query to complete
3335 Neither Oracle or Access support this function as of yet. Other databases
3336 should be evaluated for support before depending on this function working
3340 \membersection{wxDbTable::SetWhereClause
}\label{wxdbtablesetwhereclause
}
3342 \func{void
}{SetWhereClause
}{\param{const wxString &
}{Where
}}
3344 Accessor function for setting the private class member wxDbTable::where
3345 that determines which rows are returned in the result set by the datasource.
3347 A synonym to this function is one form of the function
\helpref{wxDbTable::Where
}{wxdbtablewhere
}
3349 \wxheading{Parameters
}
3351 \docparam{Where
}{SQL "where" clause. This clause can contain any SQL language
3352 that is legal in standard where clauses. If a FROM clause has also been
3353 specified, each column name specified in the ORDER BY clause should be
3354 prefaced with the table name to which the column belongs using DOT notation
3355 (TABLE_NAME.COLUMN_NAME).
}
3359 Do
{\bf not
} include the keywords "WHERE" when setting the WHERE clause.
3365 // Simple where clause
3366 parts->SetWhereClause("PART_NUMBER = '
32'");
3368 // Any comparison operators
3369 parts->SetWhereClause("PART_DESCRIP LIKE 'HAMMER
%'");
3371 // Multiple comparisons, including a function call
3372 parts->Where("QTY >
0 AND
{fn UCASE(PART_DESCRIP)
} LIKE '
%DRILL%'");
3374 // Using parameters and multiple logical combinations
3375 parts->Where("((QTY >
10) OR (ON_ORDER >
0)) AND ON_HOLD =
0");
3377 // This query uses an outer join (requiring a FROM clause also)
3378 // that joins the PART and LOCATION table on he common field
3380 parts->Where("PART.ON_HOLD =
0 AND \
3381 PART.PART_NUMBER = LOCATION.PART_NUMBER AND \
3382 LOCATION.PART_NUMBER >
0");
3385 \wxheading{See also
}
3387 \helpref{wxDbTable::Where
}{wxdbtablewhere
},
3388 \helpref{wxDbTable::GetWhereClause
}{wxdbtablegetwhereclause
}
3391 \membersection{wxDbTable::Update
}\label{wxdbtableupdate
}
3393 \func{bool
}{Update
}{\void}
3395 \func{bool
}{Update
}{\param{const char *
}{pSqlStmt
}}
3397 The first form of this function will update the row that the current cursor
3398 is currently positioned at with the values in the memory variables that
3399 are bound to the columns. The actual SQL statement to perform the update
3400 is automatically created by the ODBC class, and then executed.
3402 The second form of the function allows full access through SQL statements for
3403 updating records in the database. Write any valid SQL UPDATE statement and
3404 submit it to this function for execution. Sophisticated updates can be
3405 performed using the full power of the SQL dialect. The full SQL statement
3406 must have the exact syntax required by the driver/datasource for performing
3407 the update. This usually is in the form of:
3410 UPDATE tablename SET col1=X, col2=Y, ... where ...
3413 \wxheading{Parameters
}
3415 \docparam{pSqlStmt
}{Pointer to SQL UPDATE statement to be executed.
}
3419 A
\helpref{wxDb::CommitTrans
}{wxdbcommittrans
} or
3420 \helpref{wxDb::RollbackTrans
}{wxdbrollbacktrans
} must be called after use of
3421 this function to commit or rollback the update.
3426 strcpy(sqlStmt, "update PART set QTY =
0 where PART_NUMBER = '
32'");
3430 \membersection{wxDbTable::UpdateWhere
}\label{wxdbtableupdatewhere
}
3432 \func{bool
}{UpdateWhere
}{\param{const char *
}{pWhereClause
}}
3434 Performs updates to the base table of the wxDbTable object, updating only the
3435 rows which match the criteria specified in the
{\it pWhereClause
}.
3437 All columns that are bound to member variables for this wxDbTable instance
3438 that were defined with the "updateable" parameter set to TRUE will be updated
3439 with the information currently held in the memory variable.
3441 \wxheading{Parameters
}
3443 \docparam{pWhereClause
}{Pointer to a valid SQL WHERE clause. Do not
3444 include the keyword 'WHERE'.
}
3448 Care should be used when updating columns that are part of indexes with
3449 this function so as not to violate an unique key constraints.
3451 A
\helpref{wxDb::CommitTrans
}{wxdbcommittrans
} or
3452 \helpref{wxDb::RollbackTrans
}{wxdbrollbacktrans
} must be called after use of
3453 this function to commit or rollback the update(s).
3456 \membersection{wxDbTable::Where
}\label{wxdbtablewhere
}
3458 \func{const char *
}{Where
}{}
3460 \func{void
}{Where
}{\param{const wxString&
}{Where
}}
3462 Accessor function for the private class member wxDbTable::where. Can be used
3463 as a synonym for
\helpref{wxDbTable::GetWhereClause
}{wxdbtablegetwhereclause
}
3464 (the first form of this function) to return the current where clause or
3465 \helpref{wxDbTable::SetWhereClause
}{wxdbtablesetwhereclause
} (the second form
3466 of this function) to set the where clause for this table instance.
3468 \wxheading{Parameters
}
3470 \docparam{Where
}{A valid SQL WHERE clause. Do not include the keyword 'WHERE'.
}
3472 \wxheading{Return value
}
3474 The first form of this function returns the current value of the wxDbTable
3475 member variable ::where.
3477 The second form of the function has no return value, as it will always set
3478 the where clause successfully.
3480 \wxheading{See also
}
3482 \helpref{wxDbTable::GetWhereClause
}{wxdbtablegetwhereclause
},
3483 \helpref{wxDbTable::SetWhereClause
}{wxdbtablesetwhereclause
}
3487 \membersection{wxDbTable::operator $++$
}\label{wxdbtableplusplus
}
3489 \func{bool
}{operator $++$
}{\void}
3491 Synonym for
\helpref{wxDbTable::GetNext
}{wxdbtablegetnext
}
3493 \wxheading{See also
}
3495 \helpref{wxDbTable::GetNext
}{wxdbtablegetnext
}
3498 \membersection{wxDbTable::operator $--$
}\label{wxdbtableminusminus
}
3500 \func{bool
}{operator $--$
}{\void}
3502 Synonym for
\helpref{wxDbTable::GetPrev
}{wxdbtablegetprev
}
3504 \wxheading{See also
}
3506 \helpref{wxDbTable::GetPrev
}{wxdbtablegetprev
}
3509 \section{\class{wxDbTableInf
}}\label{wxdbtableinf
}
3514 tableRemarks
[0] =
0;
3520 Currently only used by
\helpref{wxDb::GetCatalog
}{wxdbgetcatalog
} internally
3521 and
\helpref{wxDbInf
}{wxdbinf
} class, but may be used in future releases for
3522 user functions. Contains information describing the table (Name, type, etc).
3523 A pointer to a wxDbColInf array instance is included so a program can create a
3524 \helpref{wxDbColInf
}{wxdbcolinf
} array instance (using
\helpref{wxDb::GetColumns
}{wxdbgetcolumns
})
3525 to maintain all information about the columns of a table in one memory
projects / wxWidgets.git / blob