ODBC and the SQL/CLI Standard

Open Database Connectivity (ODBC) is a database-independent callable API suite originally developed by Microsoft. Although Microsoft plays an important role as a database software vendor, its development of ODBC was motivated even more by its role as a major operating system developer. Microsoft wanted to make it easier for developers of Windows applications to incorporate database access. But the large differences between the various database systems and their APIs made this very difficult. If an application developer wanted a program to work with several different DBMS brands, it had to provide a separate, specially written database interface module (usually called a driver) for each one. Each application program that wanted to provide access to multiple databases had to provide a set of drivers.

Microsoft’s solution to this problem was to create ODBC as a uniform, standardized database access interface, and incorporate it into the Windows operating system. For application developers, ODBC eliminated the need to write custom database drivers. For database vendors, ODBC provided a way to gain support from a broader range of application programs.

1. The Call-Level Interface Standardization

ODBC would have been important even as a Microsoft-only standard. However, Microsoft worked to make it a vendor-independent standard as well. A database vendor association called the SQL Access Group was working on standardizing client/server protocols for remote database access at about the same time as Microsoft’s original development of ODBC. Microsoft persuaded the SQL Access Group to expand their focus and adopt ODBC as their standard for vendor-independent database access. Management of the SQL Access Group standard was eventually turned over to the European X/Open consortium, another standards organization, as part of its overall standards for a Common Application Environment (CAE).

With the growing popularity of call-level APIs for database access, the official SQL standards groups eventually turned their attention to standardization of this aspect of SQL. The X/Open standard (based on Microsoft’s earlier ODBC work) was taken as a starting point and slightly modified to create an official ANSI/ISO standard.

The resulting SQL/Call-Level Interface (SQL/CLI) standard was published in 1995 as ANSI/ISO/IEC 9075-3-1995. With a few modifications, SQL/CLI became Part 3 of the SQL:1999 standard.

Microsoft has evolved ODBC to conform to the official SQL/CLI standard. The CLI standard roughly forms the core level of Microsoft’s ODBC 3 revision. Other, higher- level capabilities of ODBC 3 go beyond the CLI specification to provide more API functionality and to deal with the specific problems of managing ODBC as part of the Windows operating system. In practice, the core-level ODBC capabilities and the SQL/CLI specification form the effective callable API standard.

Because of its substantial advantages for both application developers and database vendors, ODBC/CLI has become a very widely supported standard. Virtually all SQL-based database systems provide an ODBC/CLI interface as one of their supported interfaces. Some DBMS brands have even adopted ODBC/CLI as their standard database API. Thousands of application programs support ODBC/CLI, including all of the leading programming tools packages, query- and forms-processing tools and report writers, and popular productivity software such as spreadsheets and graphics programs.

The SQL/CLI standard includes about 40 different API calls, summarized in Table 19-2. The calls provide a comprehensive facility for establishing connections to a database server, executing SQL statements, retrieving and processing query results, and handling errors in database processing. They provide all of the capabilities available through the standard’s embedded SQL interface, including both static SQL and dynamic SQL capabilities.

The simple CLI program in Figure 19-11 repeats the program in Figure 19-3 and 17-14, but it uses the CLI functions. It follows the sequence of steps used by most CLI-based applications:

  1. The program connects to the CLI and allocates data structures for its use.
  2. It connects to a specific database server.
  3. The program builds SQL statements in its memory buffers.
  4. It makes CLI calls to request statement execution and check status.
  5. Upon successful completion, it makes a CLI call to commit the database transaction.
  6. It disconnects from the database and releases its data structures.

All of the CLI routines return a status code indicating either successful completion of the routine or some type of error or warning about its execution. The values for the CLI return status codes are summarized in Table 19-3. Some of the program examples in this book omit the checking of return status codes to shorten the example and focus on the specific features being illustrated. However, production programs that call CLI functions should always check the return value to ensure that the function was completed successfully. Symbolic constant names for the return status codes as well as many other values, such as data type codes and statement-id codes, are typically defined in a header file that is included at the beginning of a program that uses the CLI.

2. CLI Structures

The CLI manages interactions between an application program and a supported database through a hierarchy of concepts, reflected in a hierarchy of CLI data structures:

  • SQL -environment. The highest-level environment within which database access takes place. The CLI uses the data structure associated with a SQL-environment to keep track of the various application programs that are using it.
  • SQL -connection. A logical connection to a specific database server. Conceptually, the CLI allows a single application program to connect to several different database servers concurrently. Each connection has its own data structure, which the CLI uses to track connection status.
  • SQL -statement. An individual SQL statement to be processed by a database server. A statement may move through several stages of processing, as the DBMS prepares (compiles) it, executes it, processes any errors, and in the case of queries, returns the results to the application program. Conceptually, an application program may have multiple SQL statements moving through these processing stages in parallel. Each statement has its own data structure, which the CLI uses to track its progress.

The CLI uses a technique commonly used by modern operating systems and library packages to manage these conceptual entities. A symbolic pointer called a handle is associated with the overall SQL environment, with a SQL connection to a specific database server, and with the execution of a SQL statement. The handle identifies an area of storage managed by the CLI itself. Some type of handle is passed as one of the parameters in every CLI call. The CLI routines that manage handles are shown in Figure 19-12.

A handle is created (allocated) using the CLI SQLAllocHandle() routine. One of the parameters of the routine tells the CLI which type of handle is to be allocated. Another parameter returns the handle value to the application program. Once allocated, a handle is passed to subsequent CLI routines to maintain a context for the CLI calls. In this way, different threads of execution within a program or different concurrently running programs (processes) can each establish their own connection to the CLI and can maintain their own contexts, independent of one another. Handles also allow a single program to have multiple CLI connections to different database servers, and to process more than one SQL statement in parallel. When a handle is no longer needed, the application calls SQLFreeHandle() to tell the CLI.

In addition to the general-purpose handle management routines, SQLAllocHandle () and SQLFreeHandle(), the CLI specification includes separate routines to create and free an environment, connection, or statement handle. These routines (SQLAllocEnv(), SQLAllocStmt(), and so on) were a part of the original ODBC API and are still supported in current ODBC implementations for backward compatibility. However, Microsoft has indicated that the general handle-management routines are now the preferred ODBC functions, and the specific routines may be dropped in future ODBC releases. For maximum cross-platform portability, it’s best to use the general-purpose routines.

2.1. SQL-Environment

The SQL-environment is the highest-level context used by an application program in its calls to the CLI. In a single-threaded application, there will typically be one SQL-environment for the entire program. In a multithreaded application, there may be one SQL-environment per thread or one overall SQL-environment, depending on the architecture of the program. The CLI conceptually permits multiple connections, possibly to several different database servers, from within one SQL-environment.

A specific CLI implementation for a specific DBMS may or may not actually support multiple connections.

2.2. SQL-Connections

Within a SQL-environment, an application program may establish one or more SQL- connections. A SQL-connection is a linkage between the program and a specific SQL server (database server) over which SQL statements are processed. In practice, a SQL-connection often is actually a virtual network connection to a database server located on another computer system. However, a SQL-connection may also be a logical connection between a program and a DBMS located on the same computer system.

Figure 19-13 shows the CLI routines that are used to manage SQL-connections. To establish a connection, an application program first allocates a connection handle by calling SQLAllocHandle() with the appropriate handle type. It then attempts to connect to the target SQL server with a SQLConnect() call. SQL statements can subsequently be processed over the connection. The connection handle is passed as a parameter to all of the statement-processing calls to indicate which connection is being used. When the connection is no longer needed, a call to SQLDisconnect() terminates it, and a call to SQLFreeHandle() releases the associated connection handle in the CLI.

Normally, an application program knows the name of the specific database server (in terms of the standard, the “SQL server”) that it wants to access. In certain applications (such as general-purpose query or data entry tools), it may be desirable to let the user choose which database server is to be used. The CLI SQLDataSources() call returns the names of the SQL servers that are known to the CLI—that is, the data sources that can be legally specified as server names in SQLConnect() calls. To obtain the list of server names, the application repeatedly calls SQLDataSources(). Each call returns a single server description, until the call returns an error indicating no more data. A parameter to the call can be optionally used to alter this sequential retrieval of server names.

3. CLI Statement Processing

The CLI processes SQL statements using a technique very similar to that described for dynamic embedded SQL in Chapter 18. The SQL statement is passed to the CLI in text form, as a character string. It can be executed in a one- or two-step process.

Figure 19-14 shows the basic SQL statement-processing calls. The application program must first call SQLAllocHandle() to obtain a statement handle, which identifies the statement to the program and the CLI. All subsequent SQLExecDirect (), SQLPrepare (), and SQLExecute() calls reference this statement handle. When the handle is no longer needed, it is freed with a SQLFreeHandle() call.

For one-step execution, the application program calls SQL SQLExecDirect(), passing the SQL statement text as one of the parameters to the call. The DBMS processes the statement as a result of the call and returns the completion status of the statement. This one-step process was used in the simple example program in Figure 19-11. It corresponds to the one-step EXECUTE IMMEDIATE statement in embedded dynamic SQL, described in Chapter 18.

For two-step execution, the application program calls SQLPrepare(), passing the SQL statement text as one of the parameters to the call. The DBMS analyzes the statement, determines how to carry it out, and retains this information. It does not immediately carry out the statement. Instead, subsequent calls to the SQLExecute() routine cause the statement to actually be executed. This two-step process corresponds exactly to the PREPARE and EXECUTE embedded dynamic SQL statements described in Chapter 18. You should always use it for any SQL operations that will be carried out repeatedly, because it causes the DBMS to go through the overhead of statement analysis and optimization only once, in response to the SQLPrepare() call. Parameters can be passed through the CLI to tailor the operation of the multiple SQLExecute() calls that follow.

3.1. Statement Execution with Parameters

In many cases, a SQL statement must be repeatedly executed with only changes in some of the values that it specifies. For example, an INSERT statement to add an order to the sample database is identical for every order except for the specific information about the customer number, product and manufacturer, and quantity ordered.

As described in Chapter 18, for dynamic embedded SQL, such statements can be processed efficiently by specifying the variable parts of the statement as input parameters. The statement text passed to the SQLPrepare() call has a parameter marker—a question mark (?)—in its text at each position where a parameter value is to be inserted. When the statement is later executed, values must be supplied for each of its input parameters.

The most straightforward way to supply input parameter values is with the SQLBindParam() call. Each call to SQLBindParam() establishes a linkage between one of the parameter markers in the SQL statement (identified by number) and a variable in the application program (identified by its memory address). In addition, an association is optionally established with a second application program variable (an integer) that provides the length of variable-length input parameters. If the parameter is a NULL-terminated string like those used in C programs, a special negative code value, defined in the header file as the symbolic constant SQL_NTS, can be passed, indicating that the string length can be obtained from the data itself by the CLI routines. Similarly, a negative code is used to indicate a NULL value for an input parameter. If there are three input parameter markers in the statement, there will be three calls to SQLBindParam(), one for each input parameter.

Once the association between application program variables (more accurately, program storage locations) and the statement parameters is established, the statement can be executed with a call to SQLExecute(). To change the parameter values for subsequent statements, it is only necessary to place new values in the application program buffer areas before the next call to SQLExecute (). Alternatively, the parameters can be rebound to different data areas within the application program by subsequent calls to SQLBindParam(). Figure 19-15 shows a program that includes a SQL statement with two input parameters. The program repeatedly prompts the user for a customer number and a new credit limit for the customer. The values provided by the user become the input parameters to an UPDATE statement for the CUSTOMERS table.

The SQLParamData() and SQLPutData() calls in Figure 19-15 provide an alternative method of passing parameter data at runtime, called deferred parameter passing. The selection of this technique for a particular statement parameter is indicated in the corresponding call to SQLBindParam(). Instead of actually supplying a program data location to which the parameter is bound, the SQLBindParam() call indicates that deferred parameter passing will be used and provides a value that will later be used to identify the particular parameter being processed in this way.

After statement execution is requested (by a SQLExecute() or SQLExecDirect() call), the program calls SQLParamData() to determine whether deferred parameter data is required by the statement. If so, the CLI returns a status code (SQL_NEED_DATA) along with an indicator of which parameter needs a value. The program then calls SQLPutData() to actually provide the value for the parameter. Typically, the program then calls SQLParamData() again to determine if another parameter requires dynamic data. The cycle repeats until all required dynamic data has been supplied, and SQL statement execution then continues normally.

This alternative parameter-passing method is considerably more complex than the straightforward process of binding parameters to application program locations. It has two advantages. The first is that the actual passing of data values (and the allocation of storage to contain those values) can be delayed until the last possible moment when the data is actually needed. The second advantage is that the technique can be used to pass very long parameter values piece by piece. For selected long data types, the CLI allows repeated calls to SQLPutData() for the same parameter, with each call passing the next part of the data. For example, the text of a document that is supplied as a parameter for the VALUES clause of an INSERT statement might be passed in 1000-character pieces through repeated SQLPutData() calls until all of the document has been passed. This avoids the need to allocate a single very large memory buffer within the application program to hold the entire parameter value.

3.2. CLI Transaction Management

The COMMIT and ROLLBACK functions for SQL transaction processing also apply to SQL operation via the CLI. However, because the CLI itself must be aware that a transaction is being completed, the COMMIT and ROLLBACK SQL statements are replaced by the CLI SQLEndTran() call, shown in Figure 19-16. This call was used to commit the transactions in the program examples of Figures 19-11 and 19-15. The same CLI routine is used to execute either a COMMIT or a ROLLBACK operation; the particular operation to be performed is specified by the completion type parameter to the call.

The CLI SQLCancel() call, also shown in Figure 19-16, does not actually provide a transaction management function, but in practice, it is almost always used in conjunction with a ROLLBACK operation. It is used to cancel the execution of a SQL statement that was previously initiated by a SQLExecDirect() or SQLExecute() call. This would be appropriate in a program that is using deferred parameter processing, as described in the previous section. If the program determines that it should cancel the statement execution instead of supplying a value for a deferred parameter, the program can call SQLCancel() to achieve this result.

The SQLCancel() call can also be used in a multithreaded application to cancel the effect of a SQLExecute() or SQLExecDirect() call that has not yet completed. In this situation, the thread making the original execute call will still be waiting for the call to complete, but another concurrently executing thread may call SQLCancel() using the same statement handle. The specifics of this technique, and how interruptible a CLI call is, tend to be very implementation dependent.

3.3. Processing Query Results with CLI

The CLI routines described thus far can be used to process SQL data definition statements or SQL data manipulation statements other than queries (that is, UPDATE, DELETE, and INSERT statements). For query processing, some additional CLI calls, shown in Figure 19-17, are required. The simplest way to process query results is with the SQLBindCol() and SQLFetch() calls. To carry out a query using these calls, the application program goes through the following steps (assuming a connection has already been established):

  1. The program allocates a statement handle using SQLAllocHandle().
  2. The program calls SQLPrepare(), passing the text of the SQL SELECT statement for the query.
  3. The program calls SQLExecute() to carry out the query.
  4. The program calls SQLBindCol() once for each column of query results that will be returned. Each call associates a program buffer area with a returned data column.
  5. The program calls SQLFetch() to fetch a row of query results. The data value for each row in the newly fetched row is placed into the appropriate program buffer as indicated in the previous SQLBindCol() calls.
  6. If the query produces multiple rows, the program repeats Step 5 until the SQLFetch() call returns a value indicating that there are no more rows.
  7. When all query results have been processed, the program calls SQLCloseCursor() to end access to the query results.

The program excerpt in Figure 19-18 shows a simple query carried out using this technique. The program is identical in function to the dblib-based program example in Figure 19-7. It’s instructive to compare the two programs. The specifics of the calls and their parameters are quite different, but the flow of the programs and the logical sequence of calls that they make are the same.

Each call to SQLBindCol() establishes an association between one column of query results (identified by column number) and an application program buffer (identified by its address). With each call to SQLFetch(), the CLI uses this binding to copy the appropriate data value for the column into the program’s buffer area. When appropriate, a second program data area is specified as the indicator-variable buffer for the column. Each call to SQLFetch() sets this program variable to indicate the actual length of the returned data value (for variable-length data) and to indicate when a NULL value is returned. When the program has finished processing all of the query results, it calls SQLCloseCursor().

The CLI routines in Figure 19-17 can also be used to implement an alternative method of processing query results. In this technique, the columns of query results are not bound to locations in the application program in advance. Instead, each call to SQLFetch() only advances the cursor to the next row of query results. It does not actually cause retrieval of data into host program data areas. Instead, a call to SQLGetData() is made to actually retrieve the data. One of the parameters of SQLGetData() specifies which column of query results is to be retrieved. The other parameters specify the data type to be returned and the location of the buffer to receive the data and an associated indicator variable value.

At the basic level, the SQLGetData() call is simply an alternative to the host-variable binding approach provided by SQLBindCol(), but SQLGetData() provides an important advantage when processing very large data items. Some databases support long binary or character-valued columns that can contain thousands or millions of bytes of data. It’s usually impractical to allocate a program buffer to hold all of the data in such a column. Using SQLGetData(), the program can allocate a buffer of reasonable size and work its way through the data a few thousand bytes at a time.

It’s possible to intermix the SQLBindCol() and SQLGetData() styles to process the query results of a single statement. In this case, the SQLFetch() call actually retrieves the data values for the bound columns (those for which a SQLBindCol() call has been made), but the program must explicitly call SQLGetData() to process the other columns. This technique may be especially appropriate if a query retrieves several columns of typical SQL data (names, dates, money amounts) and a column or two of long data, such as the text of a contract. Note that some CLI implementations severely restrict the ability to intermix the two styles of processing. In particular, some implementations require that all of the bound columns appear first in the left-to-right order of query results, before any columns retrieved using SQLGetData().

3.4. Scrolling Cursors

The SQL/CLI standard specifies CLI support for scrolling cursors that parallels the scrolling-cursor support originally included in the SQL2 standard for embedded SQL. The SQLFetchScroll() call, shown in Figure 19-17, provides the extended FETCH functions needed for forward/backward and random retrieval of query results. One of its parameters specifies the statement handle for the query, just as for the simple SQLFetch() call. The other two parameters specify the direction of FETCH motion (PREVIOUS, NEXT, and so on) and the offset for FETCH motions that require it (absolute and relative random row retrieval). The operation of SQLBindCol() and SQLGetData() for processing returned values is identical to that described for the SQLFetch() call.

3.5. Named Cursors

Note that the CLI doesn’t include an explicit cursor declaration call to parallel the embedded SQL DECLARE CURSOR statement. Instead, SQL query text (that is, a SELECT statement) is passed to the CLI for execution in the same manner as any other SQL statement, using either a SQLExecDirect() call or SQLPrepare() / SQLExecute() call sequence. The results of the query are identified by the statement handle in subsequent SQLFetch(), SQLBindCol(), and similar calls. For these purposes, the statement handle takes the place of the cursor name used in embedded SQL.

A problem with this scheme arises in the case of positioned (cursor-based) updates and positioned deletes. As described in Chapter 17, a positioned database UPDATE or DELETE statement (UPDATE… WHERE CURRENT OF or DELETE… WHERE CURRENT OF) can be used to modify or delete the current (that is, just fetched) row of query results. These embedded SQL statements use the cursor name to identify the particular row to be processed, since an application program may have more than one cursor open at a time to process more than one set of query results.

To support positioned updates, the CLI provides the SQLSetCursorName() call shown in Figure 19-17. The call is used to assign a cursor name, specified as one of its parameters, to a set of query results, identified by the statement handle that produced them. Once the call has been made, the cursor name can be used in subsequent positioned UPDATE or DELETE statements, which can be passed to the CLI for execution. A companion call, SQLGetCursorName(), can be used to obtain a previously assigned cursor name, given its statement handle.

3.6. Dynamic Query Processing with CLI

If the columns to be retrieved by a SQL query are not known in advance when a program is developed, the program can use the query-processing calls in Figure 19-19 to determine the characteristics of the query results at runtime. These calls implement the same type of dynamic SQL query-processing capability that was described for dynamic embedded SQL in Chapter 18. Here are the steps for dynamic query processing using CLI:

  1. The program allocates a statement handle using SQLAllocHandle().
  2. The program calls Prepare(), passing the text of the SQL SELECT statement for the query.
  3. The program calls SQLExecute() to carry out the query.
  4. The program calls SQLNumResultCols() to determine the number of columns of query results.
  5. The program calls SQLDescribeCol() once for each column of returned query results to determine its data type, size, whether it may contain NULL values, and so on.
  6. The program allocates memory to receive the returned query results and binds these memory locations to the columns by calling SQLBindCol() once for each column.
  7. The program calls SQLFetch() to fetch a row of query results. The SQLFetch() call advances the cursor to the next row of query results and returns each column of results into the appropriate area in the application program, as specified in the SQLBindCol() calls.
  8. If the query produces multiple rows, the program repeats Step 7 until the SQLFetch() call returns a value indicating that there are no more rows.
  9. When all query results have been processed, the program calls SQLCloseCursor() to end access to the query results.

Figure 19-20 shows a program that uses these techniques to process a dynamic query. The program is identical in its concept and purpose to the embedded dynamic SQL query program shown in Figure 18-9 and the dblib-based dynamic SQL query program shown in Figure 19-10. Once again, it’s instructive to compare the program examples to enhance your understanding of dynamic query processing. The API calls have quite different names, but the sequence of functions calls for the dblib program (Figure 19-10) and the CLI program (Figure 19-20) are nearly identical. The dbcmd() / dbsqlexec() / dbresults() call sequence is replaced by SQLExecDirect() .

(In this case, the query will be executed only once, so there’s no advantage to using SQLPrepare() and SQLExecute() separately.) The dbnumcols() call becomes SQLNumResultCols(). The calls to obtain column information (dbcolname(), dbcoltype(), dbcollen()) become a single call to SQLDescribeCol(). The dbnextrow() call becomes SQLFetch(). All of the other changes in the program are made to support these changes in the API functions.

If you compare the program in Figure 19-20 with the corresponding embedded dynamic SQL program in Figure 18-9, one of the major differences is embedded SQL’s use of the special SQL Data Area (SQLDA) for column binding and column description. The CLI splits these functions between the SQLNumResultCol s(), SQLDescribeCol (), and SQLBindCol() functions, and most programmers find the CLI structure easier to use and understand. However, the CLI provides an alternative, lower-level method that offers capabilities like those provided by the embedded SQLDA.

The alternative CLI method for dynamic query processing involves CLI descriptors. A CLI descriptor contains low-level information about a statement parameter (a parameter descriptor) or a column of query results (a row descriptor). The information in the descriptor is like that contained in the variable area of the SQLDA—the column or parameter’s name, data type and subtype, length, data buffer location, NULL indicator location, and so on. The parameter descriptors and row descriptors thus correspond to the input and output SQLDAs provided by some DBMS brands in their embedded dynamic SQL implementations.

CLI descriptors are identified by descriptor handles. The CLI provides a default set of descriptors for parameters and query results columns when a statement is prepared. Alternatively, the program can allocate its own descriptors and use them. The handles of the descriptors for a statement are considered statement attributes, and they are associated with a particular statement handle. The descriptor handle values can be retrieved and set by the application program using the attribute management routines, described later in the section “CLI Attributes.”

Two calls are used to retrieve information from a descriptor, given its handle. The SQLGetDescField() call retrieves a particular field of a descriptor, which is identified by a code value. It is typically used to obtain the data type or length of a query results column, for example. The SQLGetDescRec() call retrieves many pieces of information in one call, including the column or parameter name, data type and subtype, length, precision and scale, and whether it may contain NULL values. A corresponding set of calls is used to place information into a descriptor. The SQLSetDescField() call sets the value of a single piece of information within a descriptor. The SQLSetDescRec() sets multiple values in a single call, including the data type and subtype, length, precision and scale, and nullability. For convenience, the CLI provides a SQLCopyDesc() call that copies all of the values from one descriptor to another.

4. CLI Errors and Diagnostic Information

Each CLI function returns a short integer value that indicates its completion status.

If the completion status indicates an error, the error-handling CLI calls shown in Figure 19-21 can be used to obtain more information about the error and diagnose it. The most basic error-handling call is SQLError(). The application program passes the environment, connection, and statement handles and is returned the SQL2 SQLSTATE result code, the native error code of the subsystem producing the error, and an error message in text form.

The SQLError() routine actually retrieves specific, frequently used information from the CLI diagnostics area. The other error-handling routines provide more complete information through direct access to the diagnostic records created and maintained by the CLI. In general, a CLI call can produce multiple errors, which result in multiple diagnostic records. The SQLGetDiagRec() call retrieves an individual diagnostic record, by record number. Through repeated calls, the application program can retrieve complete information about all error records produced by a CLI call. Even more complete information can be obtained by interrogating individual diagnostic fields within the record. This capability is provided by the SQLGetDiagField() call.

Although not strictly an error-processing function, the SQLRowCount() function, like the error-handling functions, is called after a previous CLI SQLExecute() call. It is used to determine the impact of the previous statement when it was successful.

A returned value indicates the number of rows of data affected by the previously executed statement. (For example, the value 4 would be returned for a searched UPDATE statement that updates four rows.)

5. CLI Attributes

The CLI provides a number of options that control some of the details of its processing. Some of these control relatively minor but critical details, such as whether the CLI should automatically assume that parameters passed as string values are NULL-terminated. Others control broader aspects of CLI operation, such as the scrollability of cursors.

The CLI gives application programs the capability to control these processing options through a set of CLI attributes. The attributes are structured in a hierarchy, paralleling the environment/connection/statement hierarchy of the CLI handle structure. Environment attributes control overall operational options. Connection options apply to a particular connection created by the SQLConnect() call but may vary from one connection to another. Statement attributes apply to the processing of an individual statement, identified by a CLI statement handle.

A set of CLI calls, shown in Figure 19-22, is used by an application program to control attributes. The get calls (SQLGetEnvAttr(), SQLGetConnectAttr(), and SQLGetStmtAttr()) obtain current attribute values. The set calls (SQLSetEnvAttr(), SQLSetConnectAttr(), and SQLSetStmtAttr()) modify the current attribute values. In all of the calls, the particular attribute being processed is indicated by a code value.

Although the CLI standard provides this elaborate attribute structure, it actually specifies relatively few attributes. The single environment attribute specified is NULL TERMINATION; it controls null-terminated strings. The single connection attribute specified controls whether the CLI automatically populates a parameter descriptor when a statement is prepared or executed. Statement-level attributes control the scrollability and sensitivity of cursors. Perhaps the most important of the CLI-specified attributes are the handles of the four CLI descriptors that may be associated with a statement (two parameter descriptors and two row descriptors). The calls in Figure 19-22 are used to obtain and set these descriptor handles when using descriptor-based statement processing.

The ODBC API, on which the SQL/CLI standard was originally based, includes many more attributes. For example, ODBC connection attributes can be used to specify a read-only connection, to enable asynchronous statement processing, to specify the timeout for a connection request, and so on. ODBC environment attributes control automatic translation of ODBC calls from earlier versions of the ODBC standard.

ODBC statement attributes control transaction isolation levels, specify whether a cursor is scrollable, and limit the number of rows of query results that might be generated by a runaway query.

6. CLI Information Calls

The CLI includes three specific calls that can be used to obtain information about the particular CLI implementation. In general, these calls will not be used by an application program written for a specific purpose. They are needed by general-purpose programs (such as a query or report writing program) that need to determine the specific characteristics of the CLI they are using. The calls are shown in Figure 19-23.

The SQLGetFunctions() call is used to determine whether a specific implemen­tation supports a particular CLI function call. It is called with a function code value corresponding to one of the CLI functions, and returns a parameter indicating whether the function is supported. The SQLGetInfo() call is used to obtain much more detailed information about a CLI implementation, such as the maximum lengths of table and user names, whether the DBMS supports outer joins or transactions, and whether SQL identifiers are case-sensitive.

The SQLGetTypeInfo() call is used to obtain information about a particular supported data type or about all types supported via the CLI interface. The call actually behaves as if it were a query against a system catalog of data type information. It produces a set of query results rows, each row containing information about one specific supported type. The supplied information indicates the name of the type, its size, whether it is nullable, whether it is searchable, and so on.

Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.

Leave a Reply

Your email address will not be published. Required fields are marked *