v2.2.1 TAGGED copy ported over to main trunk of CVS

[wxWidgets.git] / docs / latex / wx / tdb.tex

\section{Database classes overview}\label{odbcoverview}

Classes: \helpref{wxDatabase}{wxdatabase}, \helpref{wxRecordSet}{wxrecordset}, \helpref{wxQueryCol}{wxquerycol},
\rtfsp\helpref{wxQueryField}{wxqueryfield}

\normalboxd{Note that more sophisticated ODBC classes are provided by the Remstar
database classes: please see the separate HTML and Word documentation.}

wxWindows provides a set of classes for accessing a subset of Microsoft's ODBC (Open Database Connectivity)
product. Currently, this wrapper is available under MS Windows only, although
ODBC may appear on other platforms, and a generic or product-specific SQL emulator for the ODBC
classes may be provided in wxWindows at a later date.

ODBC presents a unified API (Application Programmer's Interface) to a
wide variety of databases, by interfacing indirectly to each database or
file via an ODBC driver. The language for most of the database
operations is SQL, so you need to learn a small amount of SQL as well as
the wxWindows ODBC wrapper API. Even though the databases may not be
SQL-based, the ODBC drivers translate SQL into appropriate operations
for the database or file: even text files have rudimentary ODBC support,
along with dBASE, Access, Excel and other file formats.

The run-time files for ODBC are bundled with many existing database
packages, including MS Office. The required header files, sql.h and
sqlext.h, are bundled with several compilers including MS VC++ and
Watcom C++. The only other way to obtain these header files is from the
ODBC SDK, which is only available with the MS Developer Network CD-ROMs
-- at great expense. If you have odbc.dll, you can make the required
import library odbc.lib using the tool `implib'. You need to have odbc.lib
in your compiler library path.

The minimum you need to distribute with your application is odbc.dll, which must
go in the Windows system directory. For the application to function correctly,
ODBC drivers must be installed on the user's machine. If you do not use the database
classes, odbc.dll will be loaded but not called (so ODBC does not need to be
setup fully if no ODBC calls will be made).

A sample is distributed with wxWindows in {\tt samples/odbc}. You will need to install
the sample dbf file as a data source using the ODBC setup utility, available from
the control panel if ODBC has been fully installed.

\subsection{Procedures for writing an ODBC application}

You first need to create a wxDatabase object. If you want to get information
from the ODBC manager instead of from a particular database (for example
using \helpref{wxRecordSet::GetDataSources}{wxrecordsetgetdatasources}), then you
do not need to call \helpref{wxDatabase::Open}{wxdatabaseopen}.
If you do wish to connect to a datasource, then call wxDatabase::Open.
You can reuse your wxDatabase object, calling wxDatabase::Close and wxDatabase::Open
multiple times.

Then, create a wxRecordSet object for retrieving or sending information.
For ODBC manager information retrieval, you can create it as a dynaset (retrieve the
information as needed) or a snapshot (get all the data at once).
If you are going to call \helpref{wxRecordSet::ExecuteSQL}{wxrecordsetexecutesql}, you need to create it as a snapshot.
Dynaset mode is not yet implemented for user data.

Having called a function such as wxRecordSet::ExecuteSQL or
wxRecordSet::GetDataSources, you may have a number of records
associated with the recordset, if appropriate to the operation. You can
now retrieve information such as the number of records retrieved and the
actual data itself. Use \helpref{wxRecordSet::GetFieldData}{wxrecordsetgetfielddata} or
\helpref{wxRecordSet::GetFieldDataPtr}{wxrecordsetgetfielddataptr} to get the data or a pointer to it, passing
a column index or name. The data returned will be for the current
record. To move around the records, use \helpref{wxRecordSet::MoveNext}{wxrecordsetmovenext},
\rtfsp\helpref{wxRecordSet::MovePrev}{wxrecordsetmoveprev} and associated functions.

You can use the same recordset for multiple operations, or delete
the recordset and create a new one.

Note that when you delete a wxDatabase, any associated recordsets
also get deleted, so beware of holding onto invalid pointers.

\subsection{wxDatabase overview}\label{wxdatabaseoverview}

Class: \helpref{wxDatabase}{wxdatabase}

Every database object represents an ODBC connection. To do anything useful
with a database object you need to bind a wxRecordSet object to it. All you
can do with wxDatabase is opening/closing connections and getting some info
about it (users, passwords, and so on).

\wxheading{See also}

\helpref{Database classes overview}{odbcoverview}

\subsection{wxQueryCol overview}\label{wxquerycoloverview}

Class: \helpref{wxQueryCol}{wxquerycol}

Every data column is represented by an instance of this class.
It contains the name and type of a column and a list of wxQueryFields where
the real data is stored. The links to user-defined variables are stored
here, as well.

\wxheading{See also}

\helpref{Database classes overview}{odbcoverview}

\subsection{wxQueryField overview}\label{wxqueryfieldoverview}

Class: \helpref{wxQueryField}{wxqueryfield}

As every data column is represented by an instance of the class wxQueryCol,
every data item of a specific column is represented by an instance of
wxQueryField. Each column contains a list of wxQueryFields. If wxRecordSet is
of the type wxOPEN\_TYPE\_DYNASET, there will be only one field for each column,
which will be updated every time you call functions like wxRecordSet::Move
or wxRecordSet::GoTo. If wxRecordSet is of the type wxOPEN\_TYPE\_SNAPSHOT,
all data returned by an ODBC function will be loaded at once and the number
of wxQueryField instances for each column will depend on the number of records.

\wxheading{See also}

\helpref{Database classes overview}{odbcoverview}

\subsection{wxRecordSet overview}\label{wxrecordsetoverview}

Class: \helpref{wxRecordSet}{wxrecordset}

Each wxRecordSet represents a database query. You can make multiple queries
at a time by using multiple wxRecordSets with a wxDatabase or you can make
your queries in sequential order using the same wxRecordSet.

\wxheading{See also}

\helpref{Database classes overview}{odbcoverview}

\subsection{ODBC SQL data types}\label{sqltypes}

These are the data types supported in ODBC SQL. Note that there are other, extended level conformance
types, not currently supported in wxWindows.

\begin{twocollist}\itemsep=0pt
\twocolitem{CHAR(n)}{A character string of fixed length {\it n}.}
\twocolitem{VARCHAR(n)}{A varying length character string of maximum length {\it n}.}
\twocolitem{LONG VARCHAR(n)}{A varying length character string: equivalent to VARCHAR for the purposes
of ODBC.}
\twocolitem{DECIMAL(p, s)}{An exact numeric of precision {\it p} and scale {\it s}.}
\twocolitem{NUMERIC(p, s)}{Same as DECIMAL.}
\twocolitem{SMALLINT}{A 2 byte integer.}
\twocolitem{INTEGER}{A 4 byte integer.}
\twocolitem{REAL}{A 4 byte floating point number.}
\twocolitem{FLOAT}{An 8 byte floating point number.}
\twocolitem{DOUBLE PRECISION}{Same as FLOAT.}
\end{twocollist}

These data types correspond to the following ODBC identifiers:

\begin{twocollist}\itemsep=0pt
\twocolitem{SQL\_CHAR}{A character string of fixed length.}
\twocolitem{SQL\_VARCHAR}{A varying length character string.}
\twocolitem{SQL\_DECIMAL}{An exact numeric.}
\twocolitem{SQL\_NUMERIC}{Same as SQL\_DECIMAL.}
\twocolitem{SQL\_SMALLINT}{A 2 byte integer.}
\twocolitem{SQL\_INTEGER}{A 4 byte integer.}
\twocolitem{SQL\_REAL}{A 4 byte floating point number.}
\twocolitem{SQL\_FLOAT}{An 8 byte floating point number.}
\twocolitem{SQL\_DOUBLE}{Same as SQL\_FLOAT.}
\end{twocollist}

\wxheading{See also}

\helpref{Database classes overview}{odbcoverview}

\subsection{A selection of SQL commands}\label{sqlcommands}

The following is a very brief description of some common SQL commands, with
examples.

\wxheading{See also}

\helpref{Database classes overview}{odbcoverview}

\subsubsection{Create}

Creates a table.

Example:

\begin{verbatim}
CREATE TABLE Book
 (BookNumber     INTEGER     PRIMARY KEY
 , CategoryCode  CHAR(2)     DEFAULT 'RO' NOT NULL
 , Title         VARCHAR(100) UNIQUE
 , NumberOfPages SMALLINT
 , RetailPriceAmount NUMERIC(5,2)
 )
\end{verbatim}

\subsubsection{Insert}

Inserts records into a table.

Example:

\begin{verbatim}
INSERT INTO Book
  (BookNumber, CategoryCode, Title)
  VALUES(5, 'HR', 'The Lark Ascending')
\end{verbatim}

\subsubsection{Select}

The Select operation retrieves rows and columns from a table. The criteria
for selection and the columns returned may be specified.

Examples:

\verb$SELECT * FROM Book$

Selects all rows and columns from table Book.

\verb$SELECT Title, RetailPriceAmount FROM Book WHERE RetailPriceAmount > 20.0$

Selects columns Title and RetailPriceAmount from table Book, returning only
the rows that match the WHERE clause.

\verb$SELECT * FROM Book WHERE CatCode = 'LL' OR CatCode = 'RR'$

Selects all columns from table Book, returning only
the rows that match the WHERE clause.

\verb$SELECT * FROM Book WHERE CatCode IS NULL$

Selects all columns from table Book, returning only rows where the CatCode column
is NULL.

\verb$SELECT * FROM Book ORDER BY Title$

Selects all columns from table Book, ordering by Title, in ascending order. To specify
descending order, add DESC after the ORDER BY Title clause.

\verb$SELECT Title FROM Book WHERE RetailPriceAmount >= 20.0 AND RetailPriceAmount <= 35.0$

Selects records where RetailPriceAmount conforms to the WHERE expression.

\subsubsection{Update}

Updates records in a table.

Example:

\verb$UPDATE Incident SET X = 123 WHERE ASSET = 'BD34'$

This example sets a field in column `X' to the number 123, for the record
where the column ASSET has the value `BD34'.