Roger is chairman of the SQL Access Group and founder of Visigenic Software. He can be contacted at Visigenic Software, 951 Mariner's Island Blvd., Suite 460, San Mateo, CA 94404, (415) 286-1900.
The SQL Access Group (SAG) was formed in 1989 to define and promote standards for database portability and interoperability. Initially, the membership roster included database heavyweights Oracle, Informix, and Ingres (now Computer Associates), as well as hardware vendors Digital, Hewlett-Packard, Tandem, and Sun. Table 1 lists the current membership roster. The group's initial projects involved developing the draft ISO/ANSI standard for SQL (including the embedded-SQL interface specifications) and a specification for remote data access (RDA).
In 1990, SAG took the lead in developing an SQL-based Call Level Interface (CLI). The CLI SAG is an API for database access, offering an alternative invocation technique to embedded SQL that provides essentially equivalent operations. SAG envisioned an interface that would enable client/server applications to access data stored in heterogeneous relational and nonrelational databases. The interface would be platform, vendor, database, and language neutral. SAG and X/Open published the CLI Snapshot Specification in 1992 as a "work in progress," and it was adopted for use in commercial software products.
Microsoft helped define the X/Open CLI specification and became the first company to commercialize the CLI specification by shipping Open Database Connectivity (ODBC) 1.0 for Windows in 1992. To create ODBC, Microsoft extended the CLI specification and created a three-layer specification in which the "core" layer corresponds to the SAG CLI. Over the next two years, the CLI specification underwent several transformations, reemerging in 1994 as an X/Open Preliminary Specification. Also in 1994, Microsoft released ODBC 2.0, whose core functionality was still aligned with the SAG CLI. Earlier this year, Microsoft announced that ODBC 3.0 (to be released in 1996) will be fully aligned with both ISO's CLI standard and SAG's CLI Specification.
In March 1995, the CLI was finalized and published as an X/Open Common Application Environment (CAE) specification. CAE specifications are adopted by consensus and are the basis against which suppliers brand their products. The adoption and publication of the CLI as an X/Open CAE specification represents the culmination of five years of cooperative development work within the SQL Access Group.
In the meantime, ODBC has gained broad support in the database industry. All major software vendors--Oracle, Sybase, Informix, Computer Associates, IBM, Gupta, Powersoft, and Borland, as well as more than 140 application-software developers and VARs--have added ODBC support. Many of these vendors also offer proprietary APIs; nonetheless, they see the X/Open CLI specification and ODBC as important to their strategy. In 1994, Microsoft granted an exclusive source-code license to Visigenic Software for porting and licensing the ODBC SDK to non-Windows platforms. As a result, the ODBC SDK is now also available on all major UNIX platforms, as well as OS/2 and Macintosh.
A Closer Look at the SAG CLI
The X/Open CLI specification is a standard API for database access that is vendor, platform, and database neutral. It defines a set of functions that a program can call directly using normal function-call facilities. The specification is language independent and includes header files for both C and Cobol.
The CLI specification defines 57 functions that support a rich set of database-access operations sufficient for creating robust database applications, including:
- Allocating and deallocating handles (eight calls).
- Getting and setting attributes (ten calls).
- Opening and closing database connections (two calls).
- Accessing descriptors (six calls).
- Executing SQL statements (nine calls).
- Retrieving results (eight calls).
- Accessing schema metadata (four calls).
- Performing introspection (four calls).
- Controlling transactions (two calls).
- Accessing diagnostic information (three calls).
- Canceling functions (one call).
The X/Open CLI specification was developed with client/server architectures in mind. In fact, the CLI is ideal for this environment, in which the developer often knows little (if anything) about the database at the time the application is written. The X/Open CLI specification defines a set of introspection functions that enable an application to discover the characteristics and capabilities of a particular CLI implementation and of any database server accessed through that implementation. For example, SQLGetTypeInfo lets you find out what data types are supported by a particular server, and SQLDataSources returns a list of available database servers and descriptions. Introspection functions facilitate a technique known as "adaptive programming," whereby an application adapts its behavior at run time to take advantage of the capabilities of a particular database environment.
Processing SQL Statements
The X/Open CLI specification, including sample programs and header files, is available directly from X/Open. Listings One, Two, and Three illustrate how the CLI works. Listing One is from a typical database application. Listings Two and Three are the significant portions of functions called by Listing One.
The application allocates memory for an environment handle and a connection handle; both are required to establish a database connection. The SQLConnect call establishes the database connection, specifying the server name (server_name), user id (uid), and password (pwd). The application then allocates memory for a statement handle and calls SQLExecDirect, which both prepares and executes an SQL statement. The SQLGetDiagField call takes advantage of the CLI's ability to interrogate the database server. In this case, it returns a code describing the nature of the SQL statement just executed. With this information in hand, the application calls the user-defined DoSelect() function to process the results of the SQL statement. Finally, the program frees the statement handle, disconnects from the database, and frees the memory previously allocated for the connection and environment handles.
The body of the DoStatement() function in Listing Two is built around a switch statement that processes the results of an SQL statement based on the return value from the SQLGetDiagField call in Listing One. In the case of a SELECT statement, which requires its own complex processing, the function calls another user-defined function, DoSelect(); see Listing Three. In the case of an UPDATE, DELETE, or INSERT statement, the DoStatement() function calls the CLI diagnostic function SQLGetDiagField to find out how many rows were affected by the statement, then calls SQLEndTran to commit the transaction. The function prints one message indicating whether the commit was successful and another giving the number of affected rows.
In the case of any Data Definition Language (DDL) statement, the DoStatement() function first calls the CLI introspection function SQLGetInfo to find out if the CLI implementation being used supports transaction processing for DDL statements. If so, the function calls SQLEndTran to commit the transaction, then prints a message indicating whether the commit was successful.
The DoSelect() function in Listing Three processes the results of an SQL SELECT statement. It is called by the DoStatement() function. First, DoSelect() calls SQLNumResultCols to determine how many columns are in the result set. Then, for each column in the result set, the function calls SQLDescribeCol to get descriptive information about the column (that is, its length, scale, and data type), prints an appropriate row of column headings to the standard output, allocates memory to bind the results, and calls SQLBindCol to establish the bindings. Next, DoSelect() calls SQLFetch to fetch rows from the result set until none are left. For each row, the DoSelect() function prints the column values, followed by a new line. The CLI can provide various types of diagnostic information, such as whether a value is truncated or null; DoSelect() tests for these two conditions and, when they occur, calls the user-defined BuildMessage() function (not shown) to generate appropriate error messages. At the end, the function prints a list of any such error messages. Finally, the application closes the cursor for the statement handle and frees the data buffers.
What's Next?
The SQL Access Group formally merged with X/Open in 1995. The charters of SAG and X/Open's Data Management Working Group were essentially the same, and X/Open had always edited and published the work of the SQL Access Group. The merger made it possible to eliminate duplicate efforts, reduce costs, and unify development efforts. The X/Open Data Management Technical Committee disbanded, and the X/Open SQL Access Group, now functioning within the X/Open Technical Program, has assumed all of its responsibilities.
SAG is working in close cooperation with ISO on its upcoming SQL CLI specification, which is intended to mirror the X/Open specification. SAG is also actively pursuing the next logical step for the CLI--the development of an X/Open test suite for CLI conformance. Such a test suite will enable developers to verify conformance to the CLI Specification. Development of the test suite should be well underway by the time this article is published.
SAG has already begun work on the next version of the CLI specification. Our mission is to extend and refine the CLI for even more successful interoperability and to define standards that incorporate newer database technologies, including XA transaction processing, stored procedures, BLOBs, triggers, and asynchronous calls.
The efforts of the major standards organizations, including ISO, ANSI, and X/Open, as well as the strategies of all the major players in the database industry, now incorporate the X/Open CLI Specification. Vendors and standards organizations are moving rapidly in the same direction, a direction the marketplace has already validated.
Figure 1: Basic CLI control flow.
Figure 2: CLI control flow for processing SQL statements.
Table 1: SQL Access Group members.
AT&T Borland International Computer Associates Fulcrum Technologies Hitachi IBM Information Builders Informix Software INTERSOLV Microsoft Oracle Progress Software Sybase Visigenic Software
Listing One
/* allocate an environment handle */
SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
/* allocate a connection handle */
SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
/* connect to database */
if (SQLConnect(hdbc, server_name, SQL_NTS, uid, SQL_NTS, pwd, SQL_NTS) !=
SQL_SUCCESS)
return(PrintErr(SQL_HANDLE_DBC, hdbc));
/* allocate a statement handle */
SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
/* execute the SQL statement */
if (SQLExecDirect(hstmt, sqlstr, SQL_NTS) != SQL_SUCCESS)
return(PrintErr(SQL_HANDLE_STMT, hstmt));
/* see what kind of statement it was */
SQLGetDiagField(SQL_HANDLE_STMT, hstmt, 0, SQL_DIAG_DYNAMIC_FUNCTION_CODE,
(SQLPOINTER)&stmttype, 0, (SQLSMALLINT *)NULL);
/* process the SQL statement */
DoStatement(stmttype);
/* free statement handle */
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
/* disconnect from database */
SQLDisconnect(hdbc);
/* free connection handle */
SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
/* free environment handle */
SQLFreeHandle(SQL_HANDLE_ENV, henv);
Listing Two
switch(stmttype) {
/* SELECT statement */
case SQL_DIAG_SELECT_CURSOR:
DoSelect();
break;
/* searched UPDATE, searched DELETE, or INSERT statement */
case SQL_DIAG_UPDATE_WHERE:
case SQL_DIAG_DELETE_WHERE:
case SQL_DIAG_INSERT:
/* get row count */
SQLGetDiagField(SQL_HANDLE_STMT, hstmt, 0, SQL_DIAG_ROW_COUNT,
(SQL_POINTER)&rowcount, 0, (SQLSMALLINT *)NULL);
if (SQLEndTran(SQL_HANDLE_ENV, henv, SQL_COMMIT) == SQL_SUCCESS)
printf("Operation successful\n");
else
printf("Operation failed\n");
printf("%ld rows affected\n", rowcount);
break;
/* other statements */
case SQL_DIAG_ALTER_TABLE:
case SQL_DIAG_CREATE_INDEX:
case SQL_DIAG_CREATE_TABLE:
case SQL_DIAG_CREATE_VIEW:
case SQL_DIAG_DROP_INDEX:
case SQL_DIAG_DROP_TABLE:
case SQL_DIAG_DROP_VIEW:
case SQL_DIAG_GRANT:
case SQL_DIAG_REVOKE:
SQLGetInfo(hdbc, SQL_TXN_CAPABLE, &txn_type, 0, 0);
if(txn_type == SQL_TC_ALL) {
if (SQLEndTran(SQL_HANDLE_ENV, henv, SQL_COMMIT) ==
SQL_SUCCESS)
printf("Operation successful\n");
else
printf("Operation failed\n");
}
break;
/* other implementation-defined statements */
default:
printf("Statement type=%ld\n", stmttype);
break;
}
Listing Three
/* determine number of result columns */
SQLNumResultCols(hstmt, &nresultcols);
/* display column names */
for (i=0; i<nresultcols; i++) {
SQLDescribeCol(hstmt, i+1, colname, sizeof(colname), &colnamelen,
&coltype, collen[i], &scale, &nullable);
/* user-defined function to get the display length for the data type */
collen[i] = DisplayLength(coltype, collen[i], colname);
printf("%*.*s", collen[i], collen[i], colname);
/* allocate memory to bind column */
data[i] = (SQLCHAR *) malloc(collen[i]+1);
/* bind columns to program vars, converting all types to CHAR */
SQLBindCol(hstmt, i+1, SQL_CHAR, data[i], collen[i]+1, &outlen[i]);
}
printf("\n");
/*display result rows */
while (SQL_SUCCEEDED(rc=SQLFetch(hstmt))) {
errmsg[0] = '\0';
for (i=0; i<nresultcols; i++) {
if (outlen[i] == SQL_NULL_DATA || outlen[i] >= collen[i])
/* set data text to "NULL" or add to errmsg */
BuildMessage(errmsg, (SQLPOINTER *)&data[i], collen[i],
&outlen[i], i);
printf("%*,*s ", outlen[i], outlen[i], data[i]);
} /* for all columns in this row */
/* print any accumulated error messages and new line */
printf("%s\n", errmsg);
} /* while rows to fetch */
SQLCloseCursor(hstmt);
/* free data buffers */
for (i=0; i<nresultcols; i++) {
free(data[i]);
}
End Listings
