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 rudimentary 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 Class: \helpref{wxDatabase}{wxdatabase}
  77
  78 Every database object represents an ODBC connection. To do anything useful
  79 with a database object you need to bind a wxRecordSet object to it. All you
  80 can do with wxDatabase is opening/closing connections and getting some info
  81 about it (users, passwords, and so on).
  82
  83 \wxheading{See also}
  84
  85 \helpref{Database classes overview}{odbcoverview}
  86
  87 \subsection{wxQueryCol overview}\label{wxquerycoloverview}
  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 \wxheading{See also}
  97
  98 \helpref{Database classes overview}{odbcoverview}
  99
 100 \subsection{wxQueryField overview}\label{wxqueryfieldoverview}
 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 \wxheading{See also}
 114
 115 \helpref{Database classes overview}{odbcoverview}
 116
 117 \subsection{wxRecordSet overview}\label{wxrecordsetoverview}
 118
 119 Class: \helpref{wxRecordSet}{wxrecordset}
 120
 121 Each wxRecordSet represents a database query. You can make multiple queries
 122 at a time by using multiple wxRecordSets with a wxDatabase or you can make
 123 your queries in sequential order using the same wxRecordSet.
 124
 125 \wxheading{See also}
 126
 127 \helpref{Database classes overview}{odbcoverview}
 128
 129 \subsection{ODBC SQL data types}\label{sqltypes}
 130
 131 These are the data types supported in ODBC SQL. Note that there are other, extended level conformance
 132 types, not currently supported in wxWindows.
 133
 134 \begin{twocollist}\itemsep=0pt
 135 \twocolitem{CHAR(n)}{A character string of fixed length {\it n}.}
 136 \twocolitem{VARCHAR(n)}{A varying length character string of maximum length {\it n}.}
 137 \twocolitem{LONG VARCHAR(n)}{A varying length character string: equivalent to VARCHAR for the purposes
 138 of ODBC.}
 139 \twocolitem{DECIMAL(p, s)}{An exact numeric of precision {\it p} and scale {\it s}.}
 140 \twocolitem{NUMERIC(p, s)}{Same as DECIMAL.}
 141 \twocolitem{SMALLINT}{A 2 byte integer.}
 142 \twocolitem{INTEGER}{A 4 byte integer.}
 143 \twocolitem{REAL}{A 4 byte floating point number.}
 144 \twocolitem{FLOAT}{An 8 byte floating point number.}
 145 \twocolitem{DOUBLE PRECISION}{Same as FLOAT.}
 146 \end{twocollist}
 147
 148 These data types correspond to the following ODBC identifiers:
 149
 150 \begin{twocollist}\itemsep=0pt
 151 \twocolitem{SQL\_CHAR}{A character string of fixed length.}
 152 \twocolitem{SQL\_VARCHAR}{A varying length character string.}
 153 \twocolitem{SQL\_DECIMAL}{An exact numeric.}
 154 \twocolitem{SQL\_NUMERIC}{Same as SQL\_DECIMAL.}
 155 \twocolitem{SQL\_SMALLINT}{A 2 byte integer.}
 156 \twocolitem{SQL\_INTEGER}{A 4 byte integer.}
 157 \twocolitem{SQL\_REAL}{A 4 byte floating point number.}
 158 \twocolitem{SQL\_FLOAT}{An 8 byte floating point number.}
 159 \twocolitem{SQL\_DOUBLE}{Same as SQL\_FLOAT.}
 160 \end{twocollist}
 161
 162 \wxheading{See also}
 163
 164 \helpref{Database classes overview}{odbcoverview}
 165
 166 \subsection{A selection of SQL commands}\label{sqlcommands}
 167
 168 The following is a very brief description of some common SQL commands, with
 169 examples.
 170
 171 \wxheading{See also}
 172
 173 \helpref{Database classes overview}{odbcoverview}
 174
 175 \subsubsection{Create}
 176
 177 Creates a table.
 178
 179 Example:
 180
 181 \begin{verbatim}
 182 CREATE TABLE Book
 183  (BookNumber     INTEGER     PRIMARY KEY
 184  , CategoryCode  CHAR(2)     DEFAULT 'RO' NOT NULL
 185  , Title         VARCHAR(100) UNIQUE
 186  , NumberOfPages SMALLINT
 187  , RetailPriceAmount NUMERIC(5,2)
 188  )
 189 \end{verbatim}
 190
 191 \subsubsection{Insert}
 192
 193 Inserts records into a table.
 194
 195 Example:
 196
 197 \begin{verbatim}
 198 INSERT INTO Book
 199   (BookNumber, CategoryCode, Title)
 200   VALUES(5, 'HR', 'The Lark Ascending')
 201 \end{verbatim}
 202
 203 \subsubsection{Select}
 204
 205 The Select operation retrieves rows and columns from a table. The criteria
 206 for selection and the columns returned may be specified.
 207
 208 Examples:
 209
 210 \verb$SELECT * FROM Book$
 211
 212 Selects all rows and columns from table Book.
 213
 214 \verb$SELECT Title, RetailPriceAmount FROM Book WHERE RetailPriceAmount > 20.0$
 215
 216 Selects columns Title and RetailPriceAmount from table Book, returning only
 217 the rows that match the WHERE clause.
 218
 219 \verb$SELECT * FROM Book WHERE CatCode = 'LL' OR CatCode = 'RR'$
 220
 221 Selects all columns from table Book, returning only
 222 the rows that match the WHERE clause.
 223
 224 \verb$SELECT * FROM Book WHERE CatCode IS NULL$
 225
 226 Selects all columns from table Book, returning only rows where the CatCode column
 227 is NULL.
 228
 229 \verb$SELECT * FROM Book ORDER BY Title$
 230
 231 Selects all columns from table Book, ordering by Title, in ascending order. To specify
 232 descending order, add DESC after the ORDER BY Title clause.
 233
 234 \verb$SELECT Title FROM Book WHERE RetailPriceAmount >= 20.0 AND RetailPriceAmount <= 35.0$
 235
 236 Selects records where RetailPriceAmount conforms to the WHERE expression.
 237
 238 \subsubsection{Update}
 239
 240 Updates records in a table.
 241
 242 Example:
 243
 244 \verb$UPDATE Incident SET X = 123 WHERE ASSET = 'BD34'$
 245
 246 This example sets a field in column `X' to the number 123, for the record
 247 where the column ASSET has the value `BD34'.
 248