tdb.tex

来自「Wxpython Implemented on Windows CE, Sou」· TEX 代码 · 共 1,230 行 · 第 1/4 页

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

Following is a detailed overview of how to use the wxWidgets ODBC classes - \helpref{wxDb}{wxdb} 
and \helpref{wxDbTable}{wxdbtable} and their associated functions. These are 
the ODBC classes donated by Remstar International, and are collectively 
referred to herein as the wxODBC classes.

\subsection{wxDb/wxDbTable wxODBC Overview}\label{wxodbcoverview}

Classes: \helpref{wxDb}{wxdb}, \helpref{wxDbTable}{wxdbtable}

The wxODBC classes were designed for database independence. Although SQL and 
ODBC both have standards which define the minimum requirements they must 
support to be in compliance with specifications, different database vendors 
may implement things slightly differently. One example of this is that Oracle 
requires all user names for the datasources to be supplied in uppercase 
characters. In situations like this, the wxODBC classes have been written 
to make this transparent to the programmer when using functions that require 
database-specific syntax.

Currently several major databases, along with other widely used databases, 
have been tested and supported through the wxODBC classes. The list of 
supported databases is certain to grow as more users start implementing 
software with these classes, but at the time of the writing of this document, 
users have successfully used the classes with the following datasources:

\begin{itemize}\itemsep=0pt
\item DB2
\item DBase (IV, V)**
\item Firebird
\item INFORMIX
\item Interbase
\item MS SQL Server (v7 - minimal testing)
\item MS Access (97, 2000, 2002, and 2003)
\item MySQL (2.x and 3.5 - use the 2.5x drivers though)
\item Oracle (v7, v8, v8i)
\item Pervasive SQL
\item PostgreSQL
\item Sybase (ASA and ASE)
\item XBase Sequiter
\item VIRTUOSO
\end{itemize}

An up-to-date list can be obtained by looking in the comments of the function 
\helpref{wxDb::Dbms}{wxdbdbms} in db.cpp, or in the enumerated type 
\helpref{wxDBMS}{wxdbenumeratedtypes} in db.h.

**dBase is not truly an ODBC datasource, but there are drivers which can 
emulate much of the functionality of an ODBC connection to a dBase table. 
See the \helpref{wxODBC Known Issues}{wxodbcknownissues} section of this 
overview for details.


\subsection{wxODBC Where To Start}\label{wxodbcwheretostart}

First, if you are not familiar with SQL and ODBC, go to your local bookstore 
and pick up a good book on each. This documentation is not meant to teach 
you many details about SQL or ODBC, though you may learn some just from 
immersion in the subject.

If you have worked with non-SQL/ODBC datasources before, there are some 
things you will need to un-learn. First some terminology as these phrases will 
be used heavily in this section of the manual.

\begin{twocollist}\itemsep=0pt
\twocolitem{Datasource}{(usually a database) that contains the data that will be 
accessed by the wxODBC classes.}
\twocolitem{Data table}{The section of the datasource that contains the rows and 
columns of data.}
\twocolitem{ODBC driver}{The middle-ware software that interprets the ODBC 
commands sent by your application and converts them to the SQL format expected 
by the target datasource.}
\twocolitem{Datasource connection}{An open pipe between your application and 
the ODBC driver which in turn has a connection to the target datasource. 
Datasource connections can have a virtually unlimited number of wxDbTable 
instances using the same connect (dependent on the ODBC driver). A separate 
connection is not needed for each table (the exception is for isolating 
commits/rollbacks on different tables from affecting more than the desired 
table. See the class documentation on 
\helpref{wxDb::CommitTrans}{wxdbcommittrans} and 
\helpref{wxDb::RollbackTrans}{wxdbrollbacktrans}.)}
\twocolitem{Rows}{Similar to records in old relational databases, a row is a 
collection of one instance of each column of the data table that are all 
associated with each other.}
\twocolitem{Columns}{Individual fields associated with each row of a data 
table.}
\twocolitem{Query}{Request from the client to the datasource asking for 
the data that matches the requirements specified in the users request. When 
a query is performed, the datasource performs the lookup of the rows with 
satisfy the query, and creates a result set.}
\twocolitem{Result set}{The data which matches the requirements specified 
in a query sent to the datasource. Dependent on drivers, a result set 
typically remains at the datasource (no data is transmitted to the ODBC driver) 
until the client actually instructs the ODBC driver to retrieve it.}
\twocolitem{Cursor}{A logical pointer into the result set that a query 
generates, indicating the next record that will be returned to the client 
when a request for the next record is made.}
\twocolitem{Scrolling cursors}{Scrolling refers to the movement of cursors 
through the result set. Cursors can always scroll forward sequentially in 
the result set (FORWARD ONLY scrolling cursors). With Forward only scrolling 
cursors, once a row in the result set has been returned to the ODBC driver 
and on to the client, there is no way to have the cursor move backward in 
the result set to look at the row that is previous to the current row in 
the result set. If BACKWARD scrolling cursors are supported by both the 
ODBC driver and the datasource that are being used, then backward 
scrolling cursor functions may be used (
\helpref{wxDbTable::GetPrev}{wxdbtablegetprev}, 
\helpref{wxDbTable::GetFirst}{wxdbtablegetfirst}, and 
\helpref{wxDbTable::GetLast}{wxdbtablegetlast}). If the datasource or the 
ODBC driver only support forward scrolling cursors, your program and logic 
must take this in to account.}
\twocolitem{Commit/Rollback}{Commit will physically save 
insertions/deletions/updates, while rollback basically does an undo of 
everything done against the datasource connection that has not been 
previously committed. Note that Commit and Rollbacks are done on a 
connection, not on individual tables. All tables which use a shared 
connection to the datasource are all committed/rolled back at the same 
time when a call to 
\helpref{wxDb::CommitTrans}{wxdbcommittrans} or 
\helpref{wxDb::RollbackTrans}{wxdbrollbacktrans} is made.}
\twocolitem{Index}{Indexes are datasource-maintained lookup structures 
that allow the datasource to quickly locate data rows based on the values 
of certain columns. Without indexes, the datasource would need to do a 
sequential search of a table every time a query request is made. Proper 
unique key index construction can make datasource queries nearly instantaneous.}
\end{twocollist}

Before you are able to read data from a data table in a datasource, you must 
have a connection to the datasource. Each datasource connection may be used 
to open multiple tables all on the same connection (number of tables open are 
dependent on the driver, datasource configuration and the amount of memory on 
the client workstation). Multiple connections can be opened to the same 
datasource by the same client (number of concurrent connections is dependent 
on the driver and datasource configuration).

When a query is performed, the client passes the query to the ODBC driver, 
and the driver then translates it and passes it along to the datasource. The 
database engine (in most cases - exceptions are text and dBase files) running 
on the machine hosting the database does all the work of performing the search 
for the requested data. The client simply waits for a status to come back 
through the ODBC driver from the datasource. 

Depending on the ODBC driver, the result set either remains "queued" on the 
database server side, or is transferred to the machine that the driver is 
queued on. The client does not receive this data. The client must request 
some or all of the result set to be returned before any data rows are 
returned to the client application.

Result sets do not need to include all columns of every row matching the 
query. In fact, result sets can actually be joinings of columns from two 
or more data tables, may have derived column values, or calculated values 
returned.

For each result set, a cursor is maintained (typically by the database) 
which keeps track of where in the result set the user currently is. 
Depending on the database, ODBC driver, and how you configured the 
wxWidgets ODBC settings in setup.h (see \helpref{wxODBC - Compiling}{wxodbccompiling}), cursors can be 
either forward or backward scrolling. At a minimum, cursors must scroll 
forward. For example, if a query resulted in a result set with 100 rows, 
as the data is read by the client application, it will read row 1, then 2, 
then 3, etc. With forward only cursors, once the cursor has moved to 
the next row, the previous row cannot be accessed again without re-querying 
the datasource for the result set over again. Backward scrolling cursors 
allow you to request the previous row from the result set, actually 
scrolling the cursor backward.

Backward scrolling cursors are not supported on all database/driver 
combinations. For this reason, forward-only cursors are the default in 
the wxODBC classes. If your datasource does support backward scrolling 
cursors and you wish to use them, make the appropriate changes in setup.h 
to enable them (see \helpref{wxODBC - Compiling}{wxodbccompiling}). For greatest portability between 
datasources, writing your program in such a way that it only requires 
forward scrolling cursors is your best bet. On the other hand, if you are 
focusing on using only datasources that support backward scrolling cursors, 
potentially large performance benefits can be gained from using them.

There is a limit to the number of cursors that can be open on each connection 
to the datasource, and usually a maximum number of cursors for the datasource 
itself. This is all dependent on the database. Each connection that is 
opened (each instance of a wxDb) opens a minimum of 5 cursors on creation 
that are required for things such as updates/deletions/rollbacks/queries. 
Cursors are a limited resource, so use care in creating large numbers of 
cursors.

Additional cursors can be created if necessary with the 
\helpref{wxDbTable::GetNewCursor}{wxdbtablegetnewcursor} function. One example 
use for additional cursors is to track multiple scroll points in result 
sets. By creating a new cursor, a program could request a second result set 
from the datasource while still maintaining the original cursor position in 
the first result set.

Different than non-SQL/ODBC datasources, when a program performs an 
insertion, deletion, or update (or other SQL functions like altering 
tables, etc) through ODBC, the program must issue a "commit" to the 
datasource to tell the datasource that the action(s) it has been told to 
perform are to be recorded as permanent. Until a commit is performed, 
any other programs that query the datasource will not see the changes that 
have been made (although there are databases that can be configured to 
auto-commit). NOTE: With most datasources, until the commit is 
performed, any cursor that is open on that same datasource connection 
will be able to see the changes that are uncommitted. Check your 
database's documentation/configuration to verify this before relying on it 
though.

A rollback is basically an UNDO command on the datasource connection. When 
a rollback is issued, the datasource will flush all commands it has been told 
to do since the last commit that was performed.

NOTE: Commits/Rollbacks are done on datasource connections (wxDb instances) 
not on the wxDbTable instances. This means that if more than one table 
shares the same connection, and a commit or rollback is done on that 
connection, all pending changes for ALL tables using that connection are 
committed/rolled back.

\subsection{wxODBC - Configuring your system for ODBC use}\label{wxodbcconfiguringyoursystem}

Before you are able to access a datasource, you must have installed and 
configured an ODBC driver. Doing this is system specific, so it will not be 
covered in detail here. But here are a few details to get you started.

Most database vendors provide at least a minimal ODBC driver with their 
database product. In practice, many of these drivers have proven to be slow 
and/or incomplete. Rumour has it that this is because the vendors do not want 
you using the ODBC interface to their products; they want you to use their 
applications to access the data. 

Whatever the reason, for database-intensive applications, you may want to 
consider using a third-party ODBC driver for your needs. One example of a 
third-party set of ODBC drivers that has been heavily tested and used is 
Rogue Wave's drivers. Rogue Wave has drivers available for many different 
platforms and databases.
 
Under Microsoft Windows, install the ODBC driver you are planning to use. You 
will then use the ODBC Administrator in the Control Panel to configure an 
instance of the driver for your intended datasource. Note that with all 
flavors of NT, this configuration can be set up as a System or User DSN 
(datasource name). Configuring it as a system resource will make it 
available to all users (if you are logged in as 'administrator'), otherwise 
the datasource will only be available to the user who configured the DSN.

Under Unix, iODBC is used for implementation of the ODBC API. To compile the 
wxODBC classes, you must first obtain iODBC from \urlref{http://www.iodbc.org}{www.iodbc.org} and install it. 
(Note: wxWidgets currently includes a version of iODBC.) Then you must create the file "~/.odbc.ini" (or optionally create 
"/etc/odbc.ini" for access for all users on the system). This file contains 
the settings for your system/datasource. Below is an example section of a 
odbc.ini file for use with the "samples/db" sample program using MySQL:

\begin{verbatim}
        [contacts]
        Trace    = Off
        TraceFile= stderr
        Driver   = /usr/local/lib/libmyodbc.so
        DSN      = contacts
        SERVER   = 192.168.1.13
        USER     = qet
        PASSWORD = 
        PORT     = 3306
\end{verbatim}

\subsection{wxODBC - Compiling}\label{wxodbccompiling}

The wxWidgets setup.h file has several settings in it pertaining to compiling 
the wxODBC classes.

\begin{twocollist}\itemsep=0pt
\twocolitem{wxUSE\_ODBC}{This must be set to 1 in order for the compiler to 
compile the wxODBC classes. Without setting this to 1, there will be no 
access to any of the wxODBC classes. The default is 0.}
\twocolitem{wxODBC\_FWD\_ONLY\_CURSORS}{When a new database connection is 
requested, this setting controls the default of whether the connection allows 
only forward scrolling cursors, or forward and backward scrolling cursors 
(see the section in "WHERE TO START" on cursors for more information on 
cursors). This default can be overridden by passing a second parameter to 
either the \helpref{wxDbGetConnection}{wxdbfunctions} or 
\helpref{wxDb constructor}{wxdbctor}. The default is 1.}
\twocolitem{wxODBC\_BACKWARD\_COMPATABILITY}{Between v2.0 and 2.2, massive 
renaming efforts were done to the ODBC classes to get naming conventions 
similar to those used throughout wxWidgets, as well as to preface all wxODBC 
classes names and functions with a wxDb preface. Because this renaming would 
affect applications written using the v2.0 names, this compile-time directive 
was added to allow those programs written for v2.0 to still compile using the 
old naming conventions.  These deprecated names are all {\tt\#}define'd to their 
corresponding new function names at the end of the db.cpp/dbtable.cpp source 
files. These deprecated class/function names should not be used in future 
development, as at some point in the future they will be removed. The default 
is 0.}
\end{twocollist}

{\it Under MS Windows}

You are required to include the "odbc32.lib" provided by your compiler vendor 
in the list of external libraries to be linked in. If using the makefiles 
supplied with wxWidgets, this library should already be included for use with 
makefile.b32, makefile.vc, and makefile.g95. 

\normalbox{MORE TO COME}

{\it Under Unix}
--with-odbc flag for configure

\normalbox{MORE TO COME}

\subsection{wxODBC - Basic Step-By-Step Guide}\label{wxodbcstepbystep}

To use the classes in an application, there are eight basic steps:

\begin{itemize}\itemsep=0pt
\item Define datasource connection information