docs/latex/wx/tdb.tex

   1 \section{Database classes overview}\label{odbcoverview}
   2
   3 Classes: \helpref{wxDatabase}{wxdatabase}, \helpref{wxRecordSet}{wxrecordset}, \helpref{wxQueryCol}{wxquerycol},
   4 \rtfsp\helpref{wxQueryField}{wxqueryfield}
   5
   6 \normalboxd{IMPORTANT NOTE: The ODBC classes are a preliminary release and incomplete. Please
   7 take this into account when using them. Feedback and bug fixes are appreciated,
   8 as always. The classes are being developed by Olaf Klein (oklein@smallo.ruhr.de) and
   9 Patrick Halke (patrick@zaphod.ruhr.de).}
  10
  11 wxWindows provides a set of classes for accessing a subset of Microsoft's ODBC (Open Database Connectivity)
  12 product. Currently, this wrapper is available under MS Windows only, although
  13 ODBC may appear on other platforms, and a generic or product-specific SQL emulator for the ODBC
  14 classes may be provided in wxWindows at a later date.
  15
  16 ODBC presents a unified API (Application Programmer's Interface) to a
  17 wide variety of databases, by interfacing indirectly to each database or
  18 file via an ODBC driver. The language for most of the database
  19 operations is SQL, so you need to learn a small amount of SQL as well as
  20 the wxWindows ODBC wrapper API. Even though the databases may not be
  21 SQL-based, the ODBC drivers translate SQL into appropriate operations
  22 for the database or file: even text files have rudimentry ODBC support,
  23 along with dBASE, Access, Excel and other file formats.
  24
  25 The run-time files for ODBC are bundled with many existing database
  26 packages, including MS Office. The required header files, sql.h and
  27 sqlext.h, are bundled with several compilers including MS VC++ and
  28 Watcom C++. The only other way to obtain these header files is from the
  29 ODBC SDK, which is only available with the MS Developer Network CD-ROMs
  30 -- at great expense. If you have odbc.dll, you can make the required
  31 import library odbc.lib using the tool `implib'. You need to have odbc.lib
  32 in your compiler library path.
  33
  34 The minimum you need to distribute with your application is odbc.dll, which must
  35 go in the Windows system directory. For the application to function correctly,
  36 ODBC drivers must be installed on the user's machine. If you do not use the database
  37 classes, odbc.dll will be loaded but not called (so ODBC does not need to be
  38 setup fully if no ODBC calls will be made).
  39
  40 A sample is distributed with wxWindows in {\tt samples/odbc}. You will need to install
  41 the sample dbf file as a data source using the ODBC setup utility, available from
  42 the control panel if ODBC has been fully installed.
  43
  44 \subsection{Procedures for writing an ODBC application}
  45
  46 You first need to create a wxDatabase object. If you want to get information
  47 from the ODBC manager instead of from a particular database (for example
  48 using \helpref{wxRecordSet::GetDataSources}{wxrecordsetgetdatasources}), then you
  49 do not need to call \helpref{wxDatabase::Open}{wxdatabaseopen}.
  50 If you do wish to connect to a datasource, then call wxDatabase::Open.
  51 You can reuse your wxDatabase object, calling wxDatabase::Close and wxDatabase::Open
  52 multiple times.
  53
  54 Then, create a wxRecordSet object for retrieving or sending information.
  55 For ODBC manager information retrieval, you can create it as a dynaset (retrieve the
  56 information as needed) or a snapshot (get all the data at once).
  57 If you are going to call \helpref{wxRecordSet::ExecuteSQL}{wxrecordsetexecutesql}, you need to create it as a snapshot.
  58 Dynaset mode is not yet implemented for user data.
  59
  60 Having called a function such as wxRecordSet::ExecuteSQL or
  61 wxRecordSet::GetDataSources, you may have a number of records
  62 associated with the recordset, if appropriate to the operation. You can
  63 now retrieve information such as the number of records retrieved and the
  64 actual data itself. Use \helpref{wxRecordSet::GetFieldData}{wxrecordsetgetfielddata} or
  65 \helpref{wxRecordSet::GetFieldDataPtr}{wxrecordsetgetfielddataptr} to get the data or a pointer to it, passing
  66 a column index or name. The data returned will be for the current
  67 record. To move around the records, use \helpref{wxRecordSet::MoveNext}{wxrecordsetmovenext},
  68 \rtfsp\helpref{wxRecordSet::MovePrev}{wxrecordsetmoveprev} and associated functions.
  69
  70 You can use the same recordset for multiple operations, or delete
  71 the recordset and create a new one.
  72
  73 Note that when you delete a wxDatabase, any associated recordsets
  74 also get deleted, so beware of holding onto invalid pointers.
  75
  76 \subsection{wxDatabase overview}\label{wxdatabaseoverview}
  77
  78 \overview{Database classes overview}{odbcoverview}
  79
  80 Class: \helpref{wxDatabase}{wxdatabase}
  81
  82 Every database object represents an ODBC connection. To do anything useful
  83 with a database object you need to bind a wxRecordSet object to it. All you
  84 can do with wxDatabase is opening/closing connections and getting some info
  85 about it (users, passwords, and so on).
  86
  87 \subsection{wxQueryCol overview}\label{wxquerycoloverview}
  88
  89 \overview{Database classes overview}{odbcoverview}
  90
  91 Class: \helpref{wxQueryCol}{wxquerycol}
  92
  93 Every data column is represented by an instance of this class.
  94 It contains the name and type of a column and a list of wxQueryFields where
  95 the real data is stored. The links to user-defined variables are stored
  96 here, as well.
  97
  98 \subsection{wxQueryField overview}\label{wxqueryfieldoverview}
  99
 100 \overview{Database classes overview}{odbcoverview}
 101
 102 Class: \helpref{wxQueryField}{wxqueryfield}
 103
 104 As every data column is represented by an instance of the class wxQueryCol,
 105 every data item of a specific column is represented by an instance of
 106 wxQueryField. Each column contains a list of wxQueryFields. If wxRecordSet is
 107 of the type wxOPEN\_TYPE\_DYNASET, there will be only one field for each column,
 108 which will be updated every time you call functions like wxRecordSet::Move
 109 or wxRecordSet::GoTo. If wxRecordSet is of the type wxOPEN\_TYPE\_SNAPSHOT,
 110 all data returned by an ODBC function will be loaded at once and the number
 111 of wxQueryField instances for each column will depend on the number of records.
 112
 113 \subsection{wxRecordSet overview}\label{wxrecordsetoverview}
 114
 115 \overview{Database classes overview}{odbcoverview}
 116
 117 Class: \helpref{wxRecordSet}{wxrecordset}
 118
 119 Each wxRecordSet represents a database query. You can make multiple queries
 120 at a time by using multiple wxRecordSets with a wxDatabase or you can make
 121 your queries in sequential order using the same wxRecordSet.
 122
 123 \subsection{ODBC SQL data types}\label{sqltypes}
 124
 125 \overview{Database classes overview}{odbcoverview}
 126
 127 These are the data types supported in ODBC SQL. Note that there are other, extended level conformance
 128 types, not currently supported in wxWindows.
 129
 130 \begin{twocollist}\itemsep=0pt
 131 \twocolitem{CHAR(n)}{A character string of fixed length {\it n}.}
 132 \twocolitem{VARCHAR(n)}{A varying length character string of maximum length {\it n}.}
 133 \twocolitem{LONG VARCHAR(n)}{A varying length character string: equivalent to VARCHAR for the purposes
 134 of ODBC.}
 135 \twocolitem{DECIMAL(p, s)}{An exact numeric of precision {\it p} and scale {\it s}.}
 136 \twocolitem{NUMERIC(p, s)}{Same as DECIMAL.}
 137 \twocolitem{SMALLINT}{A 2 byte integer.}
 138 \twocolitem{INTEGER}{A 4 byte integer.}
 139 \twocolitem{REAL}{A 4 byte floating point number.}
 140 \twocolitem{FLOAT}{An 8 byte floating point number.}
 141 \twocolitem{DOUBLE PRECISION}{Same as FLOAT.}
 142 \end{twocollist}
 143
 144 These data types correspond to the following ODBC identifiers:
 145
 146 \begin{twocollist}\itemsep=0pt
 147 \twocolitem{SQL\_CHAR}{A character string of fixed length.}
 148 \twocolitem{SQL\_VARCHAR}{A varying length character string.}
 149 \twocolitem{SQL\_DECIMAL}{An exact numeric.}
 150 \twocolitem{SQL\_NUMERIC}{Same as SQL\_DECIMAL.}
 151 \twocolitem{SQL\_SMALLINT}{A 2 byte integer.}
 152 \twocolitem{SQL\_INTEGER}{A 4 byte integer.}
 153 \twocolitem{SQL\_REAL}{A 4 byte floating point number.}
 154 \twocolitem{SQL\_FLOAT}{An 8 byte floating point number.}
 155 \twocolitem{SQL\_DOUBLE}{Same as SQL\_FLOAT.}
 156 \end{twocollist}
 157
 158 \subsection{A selection of SQL commands}\label{sqlcommands}
 159
 160 \overview{Database classes overview}{odbcoverview}
 161
 162 The following is a very brief description of some common SQL commands, with
 163 examples.
 164
 165 \subsubsection{Create}
 166
 167 Creates a table.
 168
 169 Example:
 170
 171 \begin{verbatim}
 172 CREATE TABLE Book
 173  (BookNumber     INTEGER     PRIMARY KEY
 174  , CategoryCode  CHAR(2)     DEFAULT 'RO' NOT NULL
 175  , Title         VARCHAR(100) UNIQUE
 176  , NumberOfPages SMALLINT
 177  , RetailPriceAmount NUMERIC(5,2)
 178  )
 179 \end{verbatim}
 180
 181 \subsubsection{Insert}
 182
 183 Inserts records into a table.
 184
 185 Example:
 186
 187 \begin{verbatim}
 188 INSERT INTO Book
 189   (BookNumber, CategoryCode, Title)
 190   VALUES(5, 'HR', 'The Lark Ascending')
 191 \end{verbatim}
 192
 193 \subsubsection{Select}
 194
 195 The Select operation retrieves rows and columns from a table. The criteria
 196 for selection and the columns returned may be specified.
 197
 198 Examples:
 199
 200 \verb$SELECT * FROM Book$
 201
 202 Selects all rows and columns from table Book.
 203
 204 \verb$SELECT Title, RetailPriceAmount FROM Book WHERE RetailPriceAmount > 20.0$
 205
 206 Selects columns Title and RetailPriceAmount from table Book, returning only
 207 the rows that match the WHERE clause.
 208
 209 \verb$SELECT * FROM Book WHERE CatCode = 'LL' OR CatCode = 'RR'$
 210
 211 Selects all columns from table Book, returning only
 212 the rows that match the WHERE clause.
 213
 214 \verb$SELECT * FROM Book WHERE CatCode IS NULL$
 215
 216 Selects all columns from table Book, returning only rows where the CatCode column
 217 is NULL.
 218
 219 \verb$SELECT * FROM Book ORDER BY Title$
 220
 221 Selects all columns from table Book, ordering by Title, in ascending order. To specify
 222 descending order, add DESC after the ORDER BY Title clause.
 223
 224 \verb$SELECT Title FROM Book WHERE RetailPriceAmount >= 20.0 AND RetailPriceAmount <= 35.0$
 225
 226 Selects records where RetailPriceAmount conforms to the WHERE expression.
 227
 228 \subsubsection{Update}
 229
 230 Updates records in a table.
 231
 232 Example:
 233
 234 \verb$UPDATE Incident SET X = 123 WHERE ASSET = 'BD34'$
 235
 236 This example sets a field in column `X' to the number 123, for the record
 237 where the column ASSET has the value `BD34'.
 238
 239
 240