tdb.tex

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

user has insufficient privileges to even SELECT the table. Other problems 
could occur, such as being unable to bind columns, but these other reason 
point to some lack of resource (like memory). Any errors generated 
internally in the \helpref{wxDbTable::Open}{wxdbtableopen} function are logged to the error log 
if SQL logging is turned on for the classes.

{\bf Use the table}

To use the table and the definitions that are now set up, we must first 
define what data we want the datasource to collect in to a result set, tell 
it where to get the data from, and in which sequence we want the data returned.

\begin{verbatim}
    // the WHERE clause limits/specifies which rows in the table
    // are to be returned in the result set
    table->SetWhereClause("FIRST_NAME = 'GEORGE'");

    // Result set will be sorted in ascending alphabetical 
    // order on the data in the 'LAST_NAME' column of each row
    // If the same last name is in the table for two rows, 
    // sub-sort on the 'AGE' column
    table->SetOrderByClause("LAST_NAME, AGE");

    // No other tables (joins) are used for this query
    table->SetFromClause("");
\end{verbatim}

The above lines will be used to tell the datasource to return in the result 
all the rows in the table whose column "FIRST\_NAME" contains the name 
'GEORGE' (note the required use of the single quote around the string 
literal) and that the result set will return the rows sorted by ascending 
last names (ascending is the default, and can be overridden with the 
"DESC" keyword for datasources that support it - "LAST\_NAME DESC").

Specifying a blank WHERE clause will result in the result set containing 
all rows in the datasource.

Specifying a blank ORDERBY clause means that the datasource will return 
the result set in whatever sequence it encounters rows which match the 
selection criteria. What this sequence is can be hard to determine. 
Typically it depends on the index that the datasource used to find the 
rows which match the WHERE criteria. BEWARE - relying on the datasource 
to return data in a certain sequence when you have not provided an ORDERBY 
clause will eventually cause a problem for your program. Databases can be 
tuned to be COST-based, SPEED-based, or some other basis for how it gets 
your result set. In short, if you need your result set returned in a 
specific sequence, ask for it that way by providing an ORDERBY clause.

Using an ORDERBY clause can be a performance hit, as the database must 
sort the items before making the result set available to the client. 
Creating efficient indexes that cause the data to be "found" in the correct 
ORDERBY sequence can be a big performance benefit. Also, in the large 
majority of cases, the database will be able to sort the records faster 
than your application can read all the records in (unsorted) and then sort 
them. Let the database do the work for you!

Notice in the example above, a column that is not included in the bound 
data columns ('AGE') will be used to sub-sort the result set. 

The FROM clause in this example is blanked, as we are not going to be 
performing any table joins with this simple query. When the FROM clause 
is blank, it is assumed that all columns referenced are coming from 
the default table for the wxDbTable instance.

After the selection criteria have been specified, the program can now 
ask the datasource to perform the search and create a result set that 
can be retrieved:

\begin{verbatim}
    // Instruct the datasource to perform a query based on the 
    // criteria specified above in the where/orderBy/from clauses.
    if (!table->Query())
    {
        // An error occurred performing the query
    }
\end{verbatim}

Typically, when an error occurs when calling \helpref{wxDbTable::Query}{wxdbtablequery}, it is a 
syntax problem in the WHERE clause that was specified. The exact SQL 
(datasource-specific) reason for what caused the failure of \helpref{wxDbTable::Query}{wxdbtablequery} 
(and all other operations against the datasource can be found by 
parsing the table's database connection's "errorList[]" array member for 
the stored text of the error.

When the \helpref{wxDbTable::Query}{wxdbtablequery} returns true, the 
database was able to successfully complete the requested query using the 
provided criteria. This does not mean that there are any rows in the 
result set, it just mean that the query was successful.

\normalbox{IMPORTANT: The result created by the call to 
\helpref{wxDbTable::Query}{wxdbtablequery} can take one of two forms. It is 
either a snapshot of the data at the exact moment that the database 
determined the record matched the search criteria, or it is a pointer to 
the row that matched the selection criteria. Which form of behavior is 
datasource dependent. If it is a snapshot, the data may have changed 
since the result set was constructed, so beware if your datasource 
uses snapshots and call \helpref{wxDbTable::Refresh}{wxdbtablerefresh}. Most larger brand databases 
do not use snapshots, but it is important to mention so that your application 
can handle it properly if your datasource does.}

To retrieve the data, one of the data fetching routines must be used to 
request a row from the result set, and to store the data from the result 
set into the bound memory variables. After \helpref{wxDbTable::Query}{wxdbtablequery} 
has completed successfully, the default/current cursor is placed so it 
is pointing just before the first record in the result set. If the 
result set is empty (no rows matched the criteria), then any calls to 
retrieve data from the result set will return false.

\begin{verbatim}
    wxString msg;

    while (table->GetNext())
    {
        msg.Printf("Row #%lu -- First Name : %s  Last Name is %s",
      	           table->GetRowNum(), FirstName, LastName);
        wxMessageBox(msg, "Data", wxOK | wxICON_INFORMATION, NULL);
    }
\end{verbatim}

The sample code above will read the next record in the result set repeatedly 
until the end of the result set has been reached. The first time that 
\helpref{wxDbTable::GetNext}{wxdbtablegetnext} is called right after the successful 
call to \helpref{wxDbTable::Query}{wxdbtablequery}, it actually returns the first record 
in the result set. 

When \helpref{wxDbTable::GetNext}{wxdbtablegetnext} is called and there are 
no rows remaining in the result set after the current cursor position, 
\helpref{wxDbTable::GetNext}{wxdbtablegetnext} (as well as all the other 
wxDbTable::GetXxxxx() functions) will return false.

{\bf Close the table}

When the program is done using a wxDbTable instance, it is as simple as 
deleting the table pointer (or if declared statically, letting the 
variable go out of scope). Typically the default destructor will take 
care of all that is required for cleaning up the wxDbTable instance.

\begin{verbatim}
    if (table)
    {
        delete table;
        table = NULL;
    }
\end{verbatim}

Deleting a wxDbTable instance releases all of its cursors, deletes the 
column definitions and frees the SQL environment handles used by the 
table (but not the environment handle used by the datasource connection 
that the wxDbTable instance was using).

{\bf Close the datasource connection}

After all tables that have been using a datasource connection have been 
closed (this can be verified by calling \helpref{wxDb::GetTableCount}{wxdbgettablecount} 
and checking that it returns 0), then you may close the datasource 
connection. The method of doing this is dependent on whether the 
non-caching or caching method was used to obtain the datasource connection.

If the datasource connection was created manually (non-cached), closing the 
connection is done like this:

\begin{verbatim}
    if (db)
    {
        db->Close();
        delete db;
        db = NULL;
    }
\end{verbatim}

If the program used the \helpref{wxDbGetConnection}{wxdbfunctions} function to get a datasource 
connection, the following is the code that should be used to free the 
connection(s):

\begin{verbatim}
    if (db)
    {
        wxDbFreeConnection(db);
        db = NULL;
    }
\end{verbatim}

Note that the above code just frees the connection so that it can be 
re-used on the next call the \helpref{wxDbGetConnection}{wxdbfunctions}. To actually dispose 
of the connection, releasing all of its resources (other than the 
environment handle), do the following:

\begin{verbatim}
    wxDbCloseConnections();
\end{verbatim}

{\bf Release the ODBC environment handle}

Once all of the connections that used the ODBC environment handle (in 
this example it was stored in "DbConnectInf.Henv") have been closed, then 
it is safe to release the environment handle:

\begin{verbatim}
    DbConnectInf->FreeHenv();
\end{verbatim}

Or, if the long form of the constructor was used and the constructor was allowed 
to allocate its own SQL environment handle, leaving scope or destruction of the 
wxDbConnectInf will free the handle automatically.

\begin{verbatim}
    delete DbConnectInf;
\end{verbatim}

\normalbox{Remember to never release this environment handle if there are any 
connections still using the handle.}

\subsection{wxODBC - Known Issues}\label{wxodbcknownissues}

As with creating wxWidgets, writing the wxODBC classes was not the simple 
task of writing an application to run on a single type of computer system. 
The classes need to be cross-platform for different operating systems, and 
they also needed to take in to account different database manufacturers and 
different ODBC driver manufacturers. Because of all the possible combinations 
of OS/database/drivers, it is impossible to say that these classes will work 
perfectly with datasource ABC, ODBC driver XYZ, on platform LMN. You may run 
into some incompatibilities or unsupported features when moving your 
application from one environment to another. But that is what makes 
cross-platform programming fun. It also pinpoints one of the great 
things about open source software. It can evolve!

The most common difference between different database/ODBC driver 
manufacturers in regards to these wxODBC classes is the lack of 
standard error codes being returned to the calling program. Sometimes 
manufacturers have even changed the error codes between versions of 
their databases/drivers. 

In all the tested databases, every effort has been made to determine 
the correct error codes and handle them in the class members that need 
to check for specific error codes (such as TABLE DOES NOT EXIST when 
you try to open a table that has not been created yet). Adding support 
for additional databases in the future requires adding an entry for the 
database in the \helpref{wxDb::Dbms}{wxdbdbms} function, and then handling any error codes 
returned by the datasource that do not match the expected values.

{\bf Databases}

Following is a list of known issues and incompatibilities that the 
wxODBC classes have between different datasources. An up to date 
listing of known issues can be seen in the comments of the source 
for \helpref{wxDb::Dbms}{wxdbdbms}.

{\it ORACLE}
\begin{itemize}\itemsep=0pt
\item Currently the only database supported by the wxODBC classes to support VIEWS
\end{itemize}

{\it DBASE}

NOTE: dBase is not a true ODBC datasource. You only have access to as much 
functionality as the driver can emulate.

\begin{itemize}\itemsep=0pt
\item Does not support the SQL\_TIMESTAMP structure
\item Supports only one cursor and one connect (apparently? with Microsoft driver only?)
\item Does not automatically create the primary index if the 'keyField' param of SetColDef is true. The user must create ALL indexes from their program with calls to \helpref{wxDbTable::CreateIndex}{wxdbtablecreateindex}
\item Table names can only be 8 characters long
\item Column names can only be 10 characters long
\item Currently cannot CREATE a dBase table - bug or limitation of the drivers used??
\item Currently cannot insert rows that have integer columns - bug??
\end{itemize}

{\it SYBASE (all)}
\begin{itemize}\itemsep=0pt
\item To lock a record during QUERY functions, the reserved word 'HOLDLOCK' must be added after every table name involved in the query/join if that table's matching record(s) are to be locked
\item Ignores the keywords 'FOR UPDATE'. Use the HOLDLOCK functionality described above
\end{itemize}

{\it SYBASE (Enterprise)}
\begin{itemize}\itemsep=0pt
\item If a column is part of the Primary Key, the column cannot be NULL
\item Maximum row size is somewhere in the neighborhood of 1920 bytes
\end{itemize}

{\it mySQL}
\begin{itemize}\itemsep=0pt
\item If a column is part of the Primary Key, the column cannot be NULL.
\item Cannot support selecting for update [\helpref{wxDbTable::CanSelectForUpdate}{wxdbtablecanselectforupdate}]. Always returns false.
\item Columns that are part of primary or secondary keys must be defined as being NOT NULL when they are created. Some code is added in \helpref{wxDbTable::CreateIndex}{wxdbtablecreateindex} to try to adjust the column definition if it is not defined correctly, but it is experimental (as of wxWidgets v2.2.1)
\item Does not support sub-queries in SQL statements
\end{itemize}

{\it POSTGRES}
\begin{itemize}\itemsep=0pt
\item Does not support the keywords 'ASC' or 'DESC' as of release v6.5.0
\item Does not support sub-queries in SQL statements
\end{itemize}

{\it DB2}
\begin{itemize}\itemsep=0pt
\item Columns which are part of a primary key must be declared as NOT NULL
\end{itemize}

{\bf UNICODE with wxODBC classes}

As of v2.6 of wxWidgets, the wxODBC classes now fully support the compilation 
and use of the classes in a Unicode build of wxWidgets, assuming the compiler 
and OS on which the program will be compiled/run is Unicode capable.

The one major difference in writing code that can be compiled in either 
unicode or non-unicode builds that is specific to the wxODBC classes is to 
use the SQL\_C\_WXCHAR datatype for string columns rather than SQL\_C\_CHAR or 
SQL\_C\_WCHAR.