tdb.tex

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

\item Get a datasource connection
\item Create table definition
\item Open the table
\item Use the table
\item Close the table
\item Close the datasource connection
\item Release the ODBC environment handle
\end{itemize}

Following each of these steps is detailed to explain the step, and to 
hopefully mention as many of the pitfalls that beginning users fall in 
to when first starting to use the classes. Throughout the steps, small 
snippets of code are provided to show the syntax of performing the step. A 
complete code snippet is provided at the end of this overview that shows a 
complete working flow of all these steps (see 
\helpref{wxODBC - Sample Code}{wxodbcsamplecode1}).

{\bf Define datasource connection information}

To be able to connect to a datasource through the ODBC driver, a program must 
supply a minimum of three pieces of information: Datasource name, User ID, and 
Authorization string (password). A fourth piece of information, a default 
directory indicating where the data file is stored, is required for Text and 
dBase drivers for ODBC.

The wxWidgets data class wxDbConnectInf exists for holding all of these 
values, plus some others that may be desired.

The 'Henv' member is the environment handle used to access memory for use by the 
ODBC driver. Use of this member is described below in the "Getting a Connection 
to the Datasource" section.

The 'Dsn' must exactly match the datasource name used to configure the ODBC 
datasource (in the ODBC Administrator (MSW only) or in the .odbc.ini file).

The 'Uid' is the User ID that is to be used to log in to the datasource. This 
User ID must already have been created and assigned rights within the 
datasource to which you are connecting. The user that the connection is 
establish by will determine what rights and privileges the datasource 
connection will allow the program to have when using the connection that 
this connection information was used to establish. Some datasources are 
case sensitive for User IDs, and though the wxODBC classes attempt to hide 
this from you by manipulating whatever data you pass in to match the 
datasource's needs, it is always best to pass the 'Uid' in the case that 
the datasource requires.

The 'AuthStr' is the password for the User ID specified in the 'Uid' member. 
As with the 'Uid', some datasources are case sensitive (in fact most are). 
The wxODBC classes do NOT try to manage the case of the 'AuthStr' at all. 
It is passed verbatim to the datasource, so you must use the case that the 
datasource is expecting.

The 'defaultDir' member is used with file based datasources (i.e. dBase, 
FoxPro, text files). It contains a full path to the location where the 
data table or file is located. When setting this value, use forward 
slashes '/' rather than backslashes '\' to avoid compatibility differences 
between ODBC drivers.

The other fields are currently unused. The intent of these fields are that 
they will be used to write our own ODBC Administrator type program that will 
work on both MSW and Un*x systems, regardless of the datasource. Very little 
work has been done on this to date.

{\bf Get a Datasource Connection}

There are two methods of establishing a connection to a datasource. You 
may either manually create your own wxDb instance and open the connection, 
or you may use the caching functions provided with the wxODBC classes to 
create/maintain/delete the connections.

Regardless of which method you use, you must first have a fully populated 
wxDbConnectInf object. In the wxDbConnectInf instance, provide a valid 
Dns, Uid, and AuthStr (along with a 'defaultDir' if necessary). Before 
using this though, you must allocate an environment handle to the 'Henv' 
member.

\begin{verbatim}
    wxDbConnectInf DbConnectInf;
    DbConnectInf.SetDsn("MyDSN");
    DbConnectInf.SetUserID("MyUserName");
    DbConnectInf.SetPassword("MyPassword");
    DbConnectInf.SetDefaultDir("");
\end{verbatim}

To allocate an environment handle for the ODBC connection to use, the 
wxDbConnectInf class has a datasource independent method for creating 
the necessary handle:

\begin{verbatim}
    if (DbConnectInf.AllocHenv())
    {
        wxMessageBox("Unable to allocate an ODBC environment handle",
                     "DB CONNECTION ERROR", wxOK | wxICON_EXCLAMATION);
        return;
    } 
\end{verbatim}

When the wxDbConnectInf::AllocHenv() function is called successfully, a 
value of true will be returned. A value of false means allocation failed, 
and the handle will be undefined.

A shorter form of doing the above steps is encapsulated into the 
long form of the constructor for wxDbConnectInf.

\begin{verbatim}
    wxDbConnectInf *DbConnectInf;

	 DbConnectInf = new wxDbConnectInf(NULL, "MyDSN", "MyUserName",
	                                   "MyPassword", "");
\end{verbatim}

This shorthand form of initializing the constructor passes a NULL for the SQL 
environment handle, telling the constructor to allocate a handle during 
construction. This handle is also managed for the life of wxDbConnectInf 
instance, and is freed automatically upon destruction of the instance.

Once the wxDbConnectInf instance is initialized, you are ready to 
connect to the datasource.

To manually create datasource connections, you must create a wxDb 
instance, and then open it.

\begin{verbatim}
    wxDb *db = new wxDb(DbConnectInf->GetHenv());

    opened = db->Open(DbConnectInf);
\end{verbatim}

The first line does the house keeping needed to initialize all 
the members of the wxDb class. The second line actually sends the request 
to the ODBC driver to open a connection to its associated datasource using 
the parameters supplied in the call to \helpref{wxDb::Open}{wxdbopen}.

A more advanced form of opening a connection is to use the connection 
caching functions that are included with the wxODBC classes. The caching 
mechanisms perform the same functions as the manual approach to opening a 
connection, but they also manage each connection they have created, 
re-using them and cleaning them up when they are closed, without you 
needing to do the coding.

To use the caching function \helpref{wxDbGetConnection}{wxdbfunctions} to get 
a connection to a datasource, simply call it with a single parameter of the 
type wxDbConnectInf:

\begin{verbatim}
    db = wxDbGetConnection(DbConnectInf);
\end{verbatim}

The wxDb pointer that is returned is both initialized and opened. If 
something failed in creating or opening the connection, the return value 
from \helpref{wxDbGetConnection}{wxdbfunctions} will be NULL.

The connection that is returned is either a new connection, or it is a 
"free" connection from the cache of connections that the class maintains 
that was no longer in use. Any wxDb instance created with a call to 
\helpref{wxDbGetConnection}{wxdbfunctions} is recorded in a linked list of established 
connections. When a program is finished with a connection, a call to 
\helpref{wxDbFreeConnection}{wxdbfunctions} is made, and the datasource 
connection will then be tagged as FREE, making it available for the next 
call to \helpref{wxDbGetConnection}{wxdbfunctions} that needs a connection 
using the same connection information (Dsn, Uid, AuthStr). The cached 
connections remain cached until a call to \helpref{wxDbCloseConnections}{wxdbfunctions} is made, 
at which time all cached connections are closed and deleted.

Besides the obvious advantage of using the single command caching routine to 
obtain a datasource connection, using cached connections can be quite a 
performance boost as well. Each time that a new connection is created 
(not retrieved from the cache of free connections), the wxODBC classes 
perform many queries against the datasource to determine the datasource's 
datatypes and other fundamental behaviours. Depending on the hardware, 
network bandwidth, and datasource speed, this can in some cases take a 
few seconds to establish the new connection (with well-balanced systems, 
it should only be a fraction of a second). Re-using already established 
datasource connections rather than creating/deleting, creating/deleting 
connections can be quite a time-saver.

Another time-saver is the "copy connection" features of both 
\helpref{wxDb::Open}{wxdbopen} and \helpref{wxDbGetConnection}{wxdbfunctions}. 
If manually creating a wxDb instance and opening it, you must pass an existing 
connection to the \helpref{wxDb::Open}{wxdbopen} function yourself to gain the performance 
benefit of copying existing connection settings. The 
\helpref{wxDbGetConnection}{wxdbfunctions} function automatically does this 
for you, checking the Dsn, Uid, and AuthStr parameters when you request 
a connection for any existing connections that use those same settings. 
If one is found, \helpref{wxDbGetConnection}{wxdbfunctions} copies the datasource settings for 
datatypes and other datasource specific information that was previously 
queried, rather than re-querying the datasource for all those same settings.

One final note on creating a connection. When a connection is created, it 
will default to only allowing cursor scrolling to be either forward only, 
or both backward and forward scrolling. The default behavior is 
determined by the setting {\tt wxODBC\_FWD\_ONLY\_CURSORS} in setup.h when you 
compile the wxWidgets library. The library default is to only support 
forward scrolling cursors only, though this can be overridden by parameters 
for wxDb() constructor or the \helpref{wxDbGetConnection}{wxdbfunctions} 
function. All datasources and ODBC drivers must support forward scrolling 
cursors. Many datasources support backward scrolling cursors, and many 
ODBC drivers support backward scrolling cursors. Before planning on using 
backward scrolling cursors, you must be certain that both your datasource 
and ODBC driver fully support backward scrolling cursors. See the small 
blurb about "Scrolling cursors" in the definitions at the beginning of 
this overview, or other details of setting the cursor behavior in the wxDb 
class documentation.

{\bf Create Table Definition}

Data can be accessed in a datasource's tables directly through various 
functions of the wxDb class (see \helpref{wxDb::GetData}{wxdbgetdata}). But to make life much 
simpler, the wxDbTable class encapsulates all of the SQL specific API calls 
that would be necessary to do this, wrapping it in an intuitive class of APIs.

The first step in accessing data in a datasource's tables via the wxDbTable 
class is to create a wxDbTable instance.

\begin{verbatim}
    table = new wxDbTable(db, tableName, numTableColumns, "", 
                          !wxDB_QUERY_ONLY, "");
\end{verbatim}

When you create the instance, you indicate the previously established 
datasource connection to be used to access the table, the name of the 
primary table that is to be accessed with the datasource's tables, how many 
columns of each row are going to be returned, the name of the view of the 
table that will actually be used to query against (works with Oracle only 
at this time), whether the data returned is for query purposes only, and 
finally the path to the table, if different than the path specified when 
connecting to the datasource.

Each of the above parameters are described in detail in the wxDbTable 
class' description, but one special note here about the fifth 
parameter - the queryOnly setting. If a wxDbTable instance is created as 
{\tt wxDB\_QUERY\_ONLY}, then no inserts/deletes/updates can be performed 
using this instance of the wxDbTable. Any calls to \helpref{wxDb::CommitTrans}{wxdbcommittrans} 
or \helpref{wxDb::RollbackTrans}{wxdbrollbacktrans} against the datasource 
connection used by this wxDbTable instance are ignored by this instance. If 
the wxDbTable instance is created with {\tt !wxDB\_QUERY\_ONLY} as shown above, 
then all the cursors and other overhead associated with being able to 
insert/update/delete data in the table are created, and thereby those 
operations can then be performed against the associated table with this 
wxDbTable instance.

If a table is to be accessed via a wxDbTable instance, and the table will 
only be read from, not written to, there is a performance benefit (not as 
many cursors need to be maintained/updated, hence speeding up access times), 
as well as a resource savings due to fewer cursors being created for the 
wxDbTable instance. Also, with some datasources, the number of 
simultaneous cursors is limited. 

When defining the columns to be retrievable by the wxDbTable instance, you 
can specify anywhere from one column up to all columns in the table. 

\begin{verbatim}
    table->SetColDefs(0, "FIRST_NAME", DB_DATA_TYPE_VARCHAR, FirstName,
                      SQL_C_WXCHAR, sizeof(FirstName), true, true);
    table->SetColDefs(1, "LAST_NAME", DB_DATA_TYPE_VARCHAR, LastName,
                      SQL_C_WXCHAR, sizeof(LastName), true, true);
\end{verbatim}

Notice that column definitions start at index 0 and go up to one less than 
the number of columns specified when the wxDbTable instance was created 
(in this example, two columns - one with index 0, one with index 1).

The above lines of code "bind" the datasource columns specified to the 
memory variables in the client application. So when the application 
makes a call to \helpref{wxDbTable::GetNext}{wxdbtablegetnext} (or any other function that retrieves 
data from the result set), the variables that are bound to the columns will 
have the column value stored into them. See the 
\helpref{wxDbTable::SetColDefs}{wxdbtablesetcoldefs} 
class documentation for more details on all the parameters for this function.

The bound memory variables have undefined data in them until a call to a 
function that retrieves data from a result set is made 
(e.g. \helpref{wxDbTable::GetNext}{wxdbtablegetnext},
\helpref{wxDbTable::GetPrev}{wxdbtablegetprev}, etc). The variables are not 
initialized to any data by the wxODBC classes, and they still contain 
undefined data after a call to \helpref{wxDbTable::Query}{wxdbtablequery}. Only 
after a successful call to one of the ::GetXxxx() functions is made do the 
variables contain valid data.

It is not necessary to define column definitions for columns whose data is 
not going to be returned to the client. For example, if you want to query 
the datasource for all users with a first name of 'GEORGE', but you only want 
the list of last names associated with those rows (why return the FIRST\_NAME 
column every time when you already know it is 'GEORGE'), you would only have 
needed to define one column above.

You may have as many wxDbTable instances accessing the same table using the 
same wxDb instance as you desire. There is no limit imposed by the classes 
on this. All datasources supported (so far) also have no limitations on this.

{\bf Open the table}

Opening the table is not technically doing anything with the datasource 
itself. Calling \helpref{wxDbTable::Open}{wxdbtableopen} simply does all the 
housekeeping of checking that the specified table exists, that the current 
connected user has at least SELECT privileges for accessing the table, 
setting up the requisite cursors, binding columns and cursors, and 
constructing the default INSERT statement that is used when a new row is 
inserted into the table (non-wxDB\_QUERY\_ONLY tables only).

\begin{verbatim}
    if (!table->Open())
    {
        // An error occurred opening (setting up) the table
    }
\end{verbatim}

The only reason that a call to \helpref{wxDbTable::Open}{wxdbtableopen} is likely to fail is if the