1332-1334

Previous Table of Contents Next

Page 1332

NOTE
The application might not have to call SQLTransact when accessing stored procedures and functions. In most cases, it is the stored objects that should handle commits and rollbacks internally.

Currently, support for stored procedures and functions does not extend to packaged objects, even if they are declared publicly . In order to call a packaged procedure or function, an external stub must be created. ODBC can then access the packaged object through an external function, which calls into the package. This solution is clearly less than ideal, and provides somewhat of a deterrent to using packages with ODBC applications. This should not, however, prevent an application from using procedures and functions to handle transaction-based SQL. In terms of reliability and performance, procedures and functions are typically better suited to this task than embedded SQL when using ODBC.

Disconnecting and Freeing Resources

The application should free all ODBC resources and cleanly disconnect before exiting. First, all statement handles should be freed by calling SQLFreeStmt.

Next, the application should call SQLDisconnect, passing the active connection handle. Then, the application should call SQLFreeConnect, passing the connection handle as the argument. Finally, the application should use SQLFreeEnv to free all resources allocated for the environment handle.

The order in which these functions are called is very important. All statement handles for a connection should be freed prior to passing the connection handle to SQLDisconnect. Each connection handle for an environment should be disconnected and freed prior to passing the environment handle to SQLFreeEnv. Freeing allocated memory and disconnecting in the proper order ensures that the connection does not remain active on the server. Listing 57.11 illustrates these steps.

Listing 57.11. This C function disconnects from the data source and frees all resources allocated to the connection and environment handles.

 int Disconnect( HENV hEnv, HDBC hDBc) {     int iRetVal;     iRetVal = SQLDisconnect(hDBc);     if (iRetVal != SQL_SUCCESS)         ReportError(hEnv, hDBc, SQL_NULL_HSTMT); 

Page 1333

 iRetVal = SQLFreeConnect(hDBc);     if (iRetVal != SQL_SUCCESS)         ReportError(hEnv, hDBc, SQL_NULL_HSTMT);     iRetVal = SQLFreeEnv(hEnv);       return(iRetVal); } 

The code example in Listing 57.11 assumes that all statement handles have been freed. SQL_DROP should be passed as the second parameter to SQLFreeStmt when an application is exiting to ensure that all resources are freed.

Debugging ODBC Applications

The ODBC API provides the developer with most of the tools needed to debug an ODBC application. The SQLError function, for example, is arguably more useful in debugging mode than in production code. The application can include debug code to display information from SQLError after every call to the ODBC API that results in a return value other than SQL_SUCCESS. This is especially helpful in the initial phases of development because it assists not only in locating programming bugs but also in determining what errors are likely to occur in a production environment. Steps should be taken to simplify the task of invalidating or removing debug code when it is no longer needed. An easy way to do this is to define a constant in the application and call it DEBUG_MODE, for example. The constant can be used to conditionally branch to debug code or to ignore it, depending on the value of the constant. By simply changing the value of the constant, the developer can then enable or disable debug code. This also simplifies the process of removing this code at a later date by providing a single value to search for that exists wherever debug code exists.

The ODBC API also gives the developer the ability to trace ODBC function calls and parameters. The application can call SQLSetConnectOption to enable tracing and to specify a trace file. Alternatively, the developer can enable tracing for all connections to a data source using the ODBC Administration applet. Refer to the Data Sources dialog box in Figure 57.2. Clicking the Options button on this dialog brings up the ODBC Options dialog box, which lets the user enable tracing for a data source and specify a log file. Tracing is simply logging ODBC function calls and arguments, providing a history of the application's interaction with ODBC.

Debugging time can be minimized by some investigation prior to design and development. Select a driver and determine what functions it supports before writing any code. When developing ODBC applications, never assume that a level 1 or level 2 extension will be supported by the driver. Purchase a utility (or write one yourself) that queries the driver for the functions that it supports. SQLGetFunctions can be used to accomplish this task. It accepts a connection handle, an integer constant specifying the function, and a pointer to storage to receive an integer value of TRUE (a nonzero value) if the function is supported, or FALSE (0) if it is not supported.

Page 1334

This information is critical to the design of most applications. In some cases, however, it is desirable to use SQLGetFunctions at runtime to determine how the application will interact with the database.

Limitations of ODBC

One of the primary goals of the ODBC standard is to provide a DBMS-independent standard interface to relational databases. This, in turn , should enable the development of database- independent client applications. In theory, an application can be developed using embedded SQL that will work regardless of the DBMS being accessed, whether it is Oracle, Sybase, DB2, or another DBMS. The primary limitation to this approach is that the standard ODBC SQL syntax reduces the language to the least common denominator. Outer join syntax, for example, is not currently supported by the ODBC standard.

In many cases, using ODBC precludes the use of the most powerful features of the database. When accessing Oracle through ODBC, packaged constructs are completely unavailable, and support for Oracle functions is awkward , at best. If outer joins are needed, they must be accomplished through the creation of views at design time, which is a hindrance to applications requiring the construction of dynamic SQL.

Using ODBC might also demand a sacrifice in terms of performance. Although performance is not always sacrificed when using ODBC, most high-performance drivers are fairly expensive third-party products that are licensed on a per-workstation basis. Typically, a custom interface to the DBMS-specific network software can provide greater performance and flexibility. A custom interface to SQL*Net, however, would require additional time to design and develop. Because the ODBC standard is so widely supported and its API is relatively easy to use, minor deficiencies in performance can often be accepted in favor of shorter development cycles and more portable code.

Summary

Despite its limitations, ODBC is one of the most popular means of communicating with a database and has become the de facto standard for Microsoft Windows client development. Many of the Windows development tools provide interfaces that completely abstract the underlying API from the developer, making ODBC extremely easy to use. Regardless of the development tools used or the level of abstraction, a thorough understanding of the API is invaluable in making the most of an ODBC application. When greater control is required or when performance is an issue, there is no substitute for using the API directly.

Previous Table of Contents Next


Oracle Unleashed
Oracle Development Unleashed (3rd Edition)
ISBN: 0672315750
EAN: 2147483647
Year: 1997
Pages: 391

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net