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