tdb.tex

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

\subsection{wxODBC - Sample Code}\label{wxodbcsamplecode1}

Simplest example of establishing/opening a connection to an ODBC datasource, 
binding variables to the columns for read/write usage, opening an 
existing table in the datasource, inserting a record, setting query parameters 
(where/orderBy/from), querying the datasource, reading each row of the 
result set, deleting a record, releasing the connection, then cleaning up.

NOTE: Very basic error handling is shown here, to reduce the size of the 
code and to make it more easily readable.  The HandleError() function uses the wxDbLogExtendedErrorMsg() function for retrieving database error messages.

\begin{verbatim}
// ----------------------------------------------------------------------------
// HEADERS
// ----------------------------------------------------------------------------
#include "wx/log.h"         // #included to enable output of messages only
#include "wx/dbtable.h"

// ----------------------------------------------------------------------------
// FUNCTION USED FOR HANDLING/DISPLAYING ERRORS
// ----------------------------------------------------------------------------
// Very generic error handling function.  
// If a connection to the database is passed in, then we retrieve all the 
// database errors for the connection and add them to the displayed message
int HandleError(wxString errmsg, wxDb *pDb=NULL)
{
    // Retrieve all the error message for the errors that occurred
    wxString allErrors;
    if (!pDb == NULL)
        // Get the database errors and append them to the error message
        allErrors = wxDbLogExtendedErrorMsg(errmsg.c_str(), pDb, 0, 0);
    else
        allErrors = errmsg;

    // Do whatever you wish with the error message here
    // wxLogDebug() is called inside wxDbLogExtendedErrorMsg() so this
    // console program will show the errors in the console window,
    // but these lines will show the errors in RELEASE builds also
    wxFprintf(stderr, wxT("\n%s\n"), allErrors.c_str());
    fflush(stderr);

    return 1;
}


// ----------------------------------------------------------------------------
// entry point
// ----------------------------------------------------------------------------
int main(int argc, char **argv)
{
wxDbConnectInf  *DbConnectInf    = NULL;    // DB connection information

wxDb            *db              = NULL;    // Database connection

wxDbTable       *table           = NULL;    // Data table to access
const wxChar     tableName[]     = wxT("USERS"); // Name of database table
const UWORD      numTableColumns = 2;       // Number table columns
wxChar           FirstName[50+1];           // column data: "FIRST_NAME"
wxChar           LastName[50+1];            // column data: "LAST_NAME"

wxString         msg;                       // Used for display messages

// -----------------------------------------------------------------------
// DEFINE THE CONNECTION HANDLE FOR THE DATABASE
// -----------------------------------------------------------------------
DbConnectInf = new wxDbConnectInf(NULL, 
                                    wxT("CONTACTS-SqlServer"),
                                    wxT("sa"),
                                    wxT("abk"));

// Error checking....
if (!DbConnectInf || !DbConnectInf->GetHenv())
{
    return HandleError(wxT("DB ENV ERROR: Cannot allocate ODBC env handle"));
}


// -----------------------------------------------------------------------
// GET A DATABASE CONNECTION
// -----------------------------------------------------------------------
db = wxDbGetConnection(DbConnectInf);

if (!db)
{
    return HandleError(wxT("CONNECTION ERROR - Cannot get DB connection"));
}


// -----------------------------------------------------------------------
// DEFINE THE TABLE, AND THE COLUMNS THAT WILL BE ACCESSED
// -----------------------------------------------------------------------
table = new wxDbTable(db, tableName, numTableColumns, wxT(""), 
                    !wxDB_QUERY_ONLY, wxT(""));
//
// Bind the columns that you wish to retrieve. Note that there must be
// 'numTableColumns' calls to SetColDefs(), to match the wxDbTable def
//
// Not all columns need to be bound, only columns whose values are to be 
// returned back to the client.
//
table->SetColDefs(0, wxT("FIRST_NAME"), DB_DATA_TYPE_VARCHAR, FirstName,
                SQL_C_WXCHAR, sizeof(FirstName), true, true);
table->SetColDefs(1, wxT("LAST_NAME"), DB_DATA_TYPE_VARCHAR, LastName,
                SQL_C_WXCHAR, sizeof(LastName), true, true);


// -----------------------------------------------------------------------
// CREATE (or RECREATE) THE TABLE IN THE DATABASE
// -----------------------------------------------------------------------
if (!table->CreateTable(true))  //NOTE: No CommitTrans is required
{
    return HandleError(wxT("TABLE CREATION ERROR: "), table->GetDb());
}


// -----------------------------------------------------------------------
// OPEN THE TABLE FOR ACCESS
// -----------------------------------------------------------------------
if (!table->Open())
{
    return HandleError(wxT("TABLE OPEN ERROR: "), table->GetDb());
}


// -----------------------------------------------------------------------
// INSERT A NEW ROW INTO THE TABLE
// -----------------------------------------------------------------------
wxStrcpy(FirstName, wxT("JULIAN"));
wxStrcpy(LastName, wxT("SMART"));
if (!table->Insert())
{
    return HandleError(wxT("INSERTION ERROR: "), table->GetDb());
}

// Must commit the insert to write the data to the DB
table->GetDb()->CommitTrans();


// -----------------------------------------------------------------------
// RETRIEVE ROWS FROM THE TABLE BASED ON SUPPLIED CRITERIA
// -----------------------------------------------------------------------
// Set the WHERE clause to limit the result set to return
// all rows that have a value of 'JULIAN' in the FIRST_NAME
// column of the table.
table->SetWhereClause(wxT("FIRST_NAME = 'JULIAN'"));

// Result set will be sorted in ascending alphabetical 
// order on the data in the 'LAST_NAME' column of each row
table->SetOrderByClause(wxT("LAST_NAME"));

// No other tables (joins) are used for this query
table->SetFromClause(wxT(""));

// Instruct the datasource to perform a query based on the 
// criteria specified above in the where/orderBy/from clauses.
if (!table->Query())
{
    return HandleError(wxT("QUERY ERROR: "), table->GetDb());
}

// Loop through all rows matching the query criteria until
// there are no more records to read
while (table->GetNext())
{
    msg.Printf(wxT("Row #%lu -- First Name : %s  Last Name is %s"),
            table->GetRowNum(), FirstName, LastName);

    // Code to display 'msg' here
    wxLogMessage(wxT("\n%s\n"), msg.c_str());
}


// -----------------------------------------------------------------------
// DELETE A ROW FROM THE TABLE
// -----------------------------------------------------------------------
// Select the row which has FIRST_NAME of 'JULIAN' and LAST_NAME
// of 'SMART', then delete the retrieved row
//
if (!table->DeleteWhere(wxT("FIRST_NAME = 'JULIAN' and LAST_NAME = 'SMART'")))
{
    return HandleError(wxT("DELETION ERROR: "), table->GetDb());
}
    
// Must commit the deletion to the database
table->GetDb()->CommitTrans();


// -----------------------------------------------------------------------
// TAKE CARE OF THE ODBC CLASS INSTANCES THAT WERE BEING USED
// -----------------------------------------------------------------------
// If the wxDbTable instance was successfully created
// then delete it as we are done with it now.
wxDELETE(table);

// Free the cached connection
// (meaning release it back in to the cache of datasource
// connections) for the next time a call to wxDbGetConnection()
// is made.
wxDbFreeConnection(db);
db = NULL;


// -----------------------------------------------------------------------
// CLEANUP BEFORE EXITING APP
// -----------------------------------------------------------------------
// The program is now ending, so we need to close
// any cached connections that are still being 
// maintained.
wxDbCloseConnections();

// Release the environment handle that was created
// for use with the ODBC datasource connections
wxDELETE(DbConnectInf);

wxUnusedVar(argc);  // Here just to prevent compiler warnings
wxUnusedVar(argv);  // Here just to prevent compiler warnings

return 0;
}
\end{verbatim}

\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}\label{odbccreateexample}

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}\label{odbcinsertexample}

Inserts records into a table.

Example:

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

\subsubsection{Select}\label{odbcselectexample}

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

Examples:

{\tt SELECT * FROM Book}

Selects all rows and columns from table Book.

{\tt 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.

{\tt SELECT * FROM Book WHERE CatCode = 'LL' OR CatCode = 'RR'}

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

{\tt SELECT * FROM Book WHERE CatCode IS NULL}

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

{\tt 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.

{\tt SELECT Title FROM Book WHERE RetailPriceAmount >= 20.0 AND RetailPriceAmount <= 35.0}

Selects records where RetailPriceAmount conforms to the WHERE expression.

\subsubsection{Update}\label{odbcupdateexample}

Updates records in a table.

Example:

{\tt 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'.