Technology and Community

Jim O'Neil

Subscribe to Jim O'Neil: eMailAlertsEmail Alerts
Get Jim O'Neil: homepageHomepage mobileMobile rssRSS facebookFacebook twitterTwitter linkedinLinkedIn

Related Topics: Open Web Magazine

Open Web: Article

Open Database Connectivity

The possibilities are limitless

This article offers a brief look into accessing the ODBC API from a PowerBuilder client application that views the tables and columns within any ODBC data source. The techniques shown here can be leveraged to provide capabilities such as ad-hoc reporting within your PowerBuilder applications.

A Brief Overview of ODBC
In 1989, the SQL Access Group (SAG), including representation from Oracle, Informix, Ingres, and Sun, was launched with the goal of defining standards for database interoperability. SAG took the lead in developing a SQL Call Level Interface (CLI) specification to define an application programming interface (API) that was vendor neutral, but provided essentially the same functionality as the proprietary embedded-SQL techniques that had been widely used to that point. The API was to be used by database vendors and other software providers in developing drivers that implemented a common interface and so could be invoked by client applications without requiring relinking or recompilation.

Microsoft, one of the SAG members, released the Open Database Connectivity (ODBC) standard in 1992 as the first commercial implementation of the SQL CLI specification. This initial release of ODBC supported three progressive conformance levels:

  • Core: Corresponding to requirements of the SQL CLI specification
  • Level 1: Adding support for transactions, scrollable cursors, and metadata methods for primary keys and stored procedures
  • Level 2: Supporting dynamic parameters, output and return values from stored procedures, bookmarks, and the modification of isolation levels
Each of the 60 or so ODBC API functions maps to one of these three levels, and an ODBC driver vendor must implement all functionality of a given level in order to claim its driver complies with a specific conformance level.

Since its introduction, ODBC has undergone two major revisions. ODBC 2.0 introduced 32-bit support and maintained compliance with the SAG specification, which by now had evolved into the X/Open CAE Specification. In 1996, ODBC 3.0 was released and realigned the implementation to provide full compliance with both the X/Open CAE Specification, Structured Query Language (SQL), C201, and the ISO/IEC 9075-3:1995 (E) Call-Level Interface (SQL/CLI).

As shown in Figure 1, the ODBC architecture consists of four layers: the client application, the ODBC driver manager, the ODBC driver, and the data source. For the purposes of this article, the client application is PowerBuilder or an application you write in PowerBuilder. The driver manager on the Windows operating systems is provided by Microsoft and implemented by several DLLs, most notably ODBC32.DLL. The driver manager loads a vendor-specific ODBC driver and dispatches the ODBC API calls invoked by the client application. It's the ODBC driver's responsibility to translate the ODBC API calls to the native syntax or APIs expected by the data source, which could range from an enterprise relational database management system such as Sybase ASE to a simple text file. ODBC drivers can be obtained directly from database vendors or from companies such as DataDirect Technologies, whose drivers Sybase has bundled with their products for many years.

Using the ODBC API with PowerBuilder
While it should be obvious that PowerBuilder, a C/C++ application, can and does use the ODBC API to implement its ODBC driver interface (PBODB90.DLL), what you may not have realized is that through external function calls you too can access the ODBC API within your PowerBuilder applications to provide additional functionality in your end-user applications. The specific example we will look at here displays the list of tables and columns within any of the ODBC data sources configured on the client machine. An end-user application that includes a requirement for building ad-hoc queries, for instance, could make use of this functionality without resorting to implementing vendor-specific access routines for accessing table and column metadata.

PowerBuilder and ODBC Versions and Conformance Levels
PowerBuilder identifies itself to the ODBC Driver Manager as an ODBC 2.0-level application. Although this may seem outdated, it allows PowerBuilder to communicate with older ODBC drivers that have not yet implemented (or perhaps never will) the ODBC 3.0 API. The ODBC driver manager is responsible for managing communications between client applications and down-level ODBC drivers; therefore, even though PowerBuilder will issue ODBC 2.0-level API calls, they will be translated automatically to ODBC 3.0 calls when the driver manager determines that the targeted ODBC driver supports that level.

As we develop the application, we'll simplify our implementation by confining it to ODBC 2.0 calls. In addition, we shall restrict our utilization of the ODBC API to those methods implementing core-level functionality. Since every ODBC driver must implement all core-level functions, this restriction eliminates having to inquire whether or not the driver implements a specific Level 1 or 2 API before invoking it. As we'll see, that's more than sufficient for our small sample, but more robust applications like PowerBuilder generally include such checks. Documentation for the individual API calls is available within the ODBC SDK help files and on the Microsoft Web site and indicates, method by method, both the ODBC version supported and the conformance level.

The main functionality of our application is provided by three ODBC functions: SQLDataSources, SQLTables, and SQLColumns; however, there are other ancillary constructs and methods required to establish an ODBC context and the data source connection in which to execute these methods. To isolate the ODBC API access in our application, the object n_tr extends the standard class user object transaction and encapsulates the required ODBC API methods as local external functions. n_tr is also the object type for the standard SQLCA global variable, as defined on the Variable Types tab of the Additional Properties dialog accessible from the General Properties of the application object.

User Interface for Our Sample Application
Our user interface is the simple window shown in Figure 2 and is composed of three main sections: a tree view listing the data sources on the machine and allowing connectivity via double-click, a second tree view that organizes the table objects within the data source by type and schema (or owner), and a list view specifying the names and types of the columns in the selected table.

ODBC Handles
We'll soon see how each of these three user interface widgets is populated through use of the ODBC API, but first we'll need a little background on one of those ancillary concepts mentioned earlier, namely handles. ODBC defines four types of handles, three of which are used frequently within applications such as ours:

  • Environment handles acquire an ODBC context, and their allocation is generally the first step of any ODBC application. The environment handle provides a context for loading the ODBC driver manager but not necessarily a specific ODBC driver. From this handle, you can access information about the ODBC driver manager as well as query the list of ODBC drivers and data sources available on the machine.
  • A connection handle represents the physical connection of a client application to an ODBC DSN via the associated ODBC driver. Each connection handle is tied to an environment handle, but there can be multiple connections active within a given environment.
  • Statement handles are created within a connection to issue SQL statements or other directives such as metadata queries against the data source. The general flow for executing commands against the back-end database is to allocate a statement handle, execute the statement, fetch results from the statement within a loop, and then close the statement handle.
  • Descriptor handles describe the parameters or results of a SQL statement. They are automatically allocated and less frequently used but correspond to the DynamicDescriptionArea class (e.g., SQLDA) used in some of the more advanced formats of dynamic SQL in PowerScript.
In ODBC 3.0, the SQLAllocHandle API call is used to allocate any one of these handle types, whereas in ODBC 2.0 there are separate calls - SQLAllocEnv, SQLAllocConnect, SQLAllocStmt - to allocate each handle type and three methods - SQLFreeEnv, SQLFreeConnect, and SQLFreeStmt - to deallocate those same handles. We'll use four of these methods explicitly and rely on PowerScript's CONNECT statement to provide for the other two.

Step 1: Populating the List of DSNs
The SQLDataSources API is the crux of this part of the implementation. This method retrieves the DSNs defined on a particular system one by one. Since this API does not deal with a specific database connection, only an environment handle is required, and that is obtained by a call to SQLAllocEnv. In addition to the environment handle, SQLDataSources accepts a second argument, a constant, specifying whether we want the first DSN or a subsequent DSN, the latter option being used to cycle through all available data sources. Like all constant values defined for use within ODBC calls, specific values can be found in the C header files that are part of the ODBC SDK available from Microsoft.

The actual ODBC DSN and driver information are returned in the subsequent six arguments provided to SQLDataSources. As you'll see with most ODBC APIs that return string data, three arguments are needed for each string returned from the method:

  • A buffer to store the string, declared as REF String in PowerBuilder.
  • An integer input value indicating the length of the previous buffer and thus the amount of memory available in which to store the returned value. In PowerBuilder you must allocate this buffer, typically via the Space() PowerScript method, before calling the ODBC method.
  • An integer output value providing the length of the data available to be returned in the buffer. This parameter contains the full length of the string, even if only a portion of the string could be returned because the allocated buffer was too small. In such a case, the API call would return the SQL_SUCCESS_WITH_INFO value to indicate the data truncation.

More Stories By Jim O'Neil

Jim is a Technology Evangelist for Microsoft who covers the Northeast District, namely, New England and upstate New York. He is focused on engaging with the development community in the area through user groups, code camps, BarCamps, Microsoft-sponsored events, etc., and just in general serve as ambassador for Microsoft. Since 2009, Jim has been focusing on software development scenarios using cloud computing and Windows Azure. You can follow Jim on Twitter at @jimoneil

Comments (0)

Share your thoughts on this story.

Add your comment
You must be signed in to add a comment. Sign-in | Register

In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.