Chapter 16: SQL API
|
|
Before a single SQL statement can execute, a client application that submits this statement must somehow establish connection with RDBMS. The connection can be established through a variety of mechanisms — ranging from proprietary call-level interface to the buzz-of-the-day like Java Database Connectivity and .NET Providers.
While not being in the domain of SQL proper, application programming interface concepts should be understood by anyone serious enough to go to the trouble of learning RDBMS and their native SQL.
SQL/CLI Standard
The call-level interface (CLI) standard was created by the subcommittee of the SQL Access Group (SAG). SAG was formed in 1989 with its declared purpose "to define and promote standards for database interoperability." The group included Oracle, Informix, and Hewlett-Packard, among others. The list keeps growing and now includes virtually every sizable company exploring the database field.
At about the same time, Microsoft, Lotus, Sybase, and DEC joined the effort in creating the SQL Connectivity specification, which later made its way into SAG base specification.
The CLI standard was published as a work in progress in 1992, and that same year Microsoft shipped the first commercial implementation of the standard CLI specification — Open DataBase Connectivity for Windows version 1.0. In this release, the original specification was extended and divided into three compatibility layers, the core of which corresponded directly to the SAG CLI standard.
In 1994, after several transformations, the original SAG CLI specification (amended and expanded) was dubbed X/Open Preliminary Specification. Microsoft released ODBC for Windows version 2.0 the same year. That year also marks the release of the ODBC Software Development Kit (SDK) for non-Windows platforms by Visigenic Software, under an exclusive source-code license.
In 1993 ODBC was accepted as a base document for the SQL/CLI by the ISO/ANSI SQL committees. In 1995 it was completed as ISO/IEC document 9075-3:1995 Information Technology–Database Languages–SQL–Part 3: Call-Level Interface (SQL/CLI). Later it was expanded to include SQL99 standard extensions. The standard was commercialized as Microsoft ODBC 3.0 in 1996.
In the SQL/CLI standard terminology we are speaking about binding styles — e.g., embedded SQL, module language, and direct invocation — which represent traditional binding mechanisms.
Embedded SQL, discussed in more detail in Chapter 15, received its name because SQL statements were directly embedded into the host language program. Before an application that contained Embedded SQL can be compiled and executed, it had to go through a precompiling process, using a vendor-supplied precompiler.
Module Language refers to the compiled modules stored on the server, utilizing C, PL/1, Cobol, or Java, as well as vendor-proprietary languages like Oracle's PL/SQL or Microsoft/Sybase Transact-SQL.
Direct Invocation defined a set SQL statements that can be executed directly in RDBMS, using some specific vendor-defined mechanism.
The absence of the common standard hindered development of portable applications and reuse of the code, when almost the entire application had to be rewritten to accommodate peculiarities of the RDBMS access and handling of returned data.
The CLI/SQL standard allows for relative independence from a proprietary database interface, through encapsulation of all vendor-specific details into a number of uniform functions. Table 16-1 provides a list of SQL/CLI functions (over 50), as defined by the standard.
| SQL/CLI Function | Conforms to the Standard | Description |
|---|---|---|
| Allocating and De-allocating Resources | ||
| SQLAllocHandle() | SQL/CLI | Allocates environment, connection, statement, or descriptor handle. |
| SQLAllocEnv() | SQL/CLI | Allocates environment resources, returns handle. |
| SQLAllocConnect() | SQL/CLI | Allocates connection resources, returns handle. |
| SQLAllocStmt() | SQL/CLI | Allocates statement resources, returns handle. |
| SQLFreeHandle() | SQL/CLI | Releases allocated environment, connection, etc., by handle. |
| SQLFreeEnv() | SQL/CLI | Releases allocated environment resources. |
| SQLFreeConnect() | SQL/CLI | Releases allocated connection resources. |
| SQLFreeStmt() | SQL/CLI | Stops statement processing and frees all the resources associated with its handle. |
| Opening and Closing Database Connections | ||
| SQLConnect() | SQL/CLI | Connects to a specific driver. |
| SQLDisconnect() | SQL/CLI | Terminates database connection established with SQLConnect. |
| SQLDriverConnect() | ODBC | Connects to a specific driver; if connection parameters are omitted, displays Driver Manager dialog box. |
| SQLBrowseConnect() | ODBC | Returns hierarchy of connection attributes. |
| SQL Statement Execution | ||
| SQLExecDirect() | SQL/CLI | Executes an SQL statement (without preparation). |
| SQLPrepare() | SQL/CLI | Prepares an SQL statement for execution. |
| SQLExecute() | SQL/CLI | Executes a prepared SQL statement. |
| SQLCancel() | SQL/CLI | Cancels SQL statement execution. |
| SQLBindParameter() | ODBC | Allocates storage for parameters in SQL statements. |
| SQLParamData() | SQL/CLI | Supplies parameters value at runtime. |
| SQL/CLI Function | Conforms to the Standard | Description |
| SQLDescribeParam() | ODBC | Describes a specific parameter in the statement. |
| SQLNumParams() | SQL/CLI | Returns a number of parameters for a statement. |
| SQLNativeSql() | ODBC | Returns text of an SQL statement, as it is translated by the ODBC driver. |
| SQLCloseCursor() | SQL/CLI | Closes opened cursor (by handle). |
| SQLSetCursorName() | SQL/CLI | Defines cursor name. |
| SQLGetCursorName() | SQL/CLI | Retrieves cursor name. |
| SQLSetScrollOptions() | ODBC | Sets scroll options for a cursor. |
| SQLPutData() | SQL/CLI | Sends part or all of the data for a parameter. |
| Attributes Management | ||
| SQLSetConnectAttr() | SQL/CLI | Sets connection attribute. |
| SQLGetConnectAttr() | SQL/CLI | Retrieves value of the connection attribute. |
| SQLSetEnvAttr() | SQL/CLI | Sets environment attribute. |
| SQLGetEnvAttr() | SQL/CLI | Retrieves value of the environment attribute. |
| SQLSetStmtAttr() | SQL/CLI | Sets statement attribute. |
| SQLGetStmtAttr() | SQL/CLI | Retrieves value of the statement attribute. |
| Setting and Retrieving Descriptor Fields | ||
| SQLGetDescField() | SQL/CLI | Returns the value of a single descriptor field. |
| SQLGetDescRec() | SQL/CLI | Returns the value of multiple descriptor fields. |
| SQLSetDescField() | SQL/CLI | Sets the value of a single descriptor field. |
| SQLSetDescRec() | SQL/CLI | Sets the value of multiple descriptor fields. |
| Query Results Retrieval | ||
| SQLRowCount() | SQL/CLI | Returns the number of rows affected by SQL statement. |
| SQLDescribeCol() | SQL/CLI | Describes a column in a resultset. |
| SQLColAttribute() | SQL/CLI | Describes the attributes of a column in a resultset. |
| SQLBindCol() | SQL/CLI | Assigns storage of a specific data type for a return value. |
| SQLFetch() | SQL/CLI | Returns a resultset. |
| SQLFetchScroll() | SQL/CLI | Returns scrollable resultset. |
| SQLGetData() | SQL/CLI | Returns part or whole of a column's value for single row in the resultset. |
| SQLSetPos() | ODBC | Positions cursor at specific location within a fetched resultset. |
| SQL/CLI Function | Conforms to the Standard | Description |
| SQLBulkOperations() | ODBC | Performs bulk operations. |
| SQLMoreResults() | ODBC | Returns additional resultsets, if they exist. |
| SQLGetDiagField() | SQL/CLI | Returns additional diagnostic information (single record). |
| SQLGetDiagRec() | SQL/CLI | Returns additional diagnostic information (multiple records). |
| Accessing Systems Catalogs | ||
| SQLColumnPrivileges() | ODBC | Returns list of columns and associated privileges. |
| SQLColumns() | X/Open | Returns list of columns for the specified table. |
| SQLForeignKeys | ODBC | Returns list of all columns that comprise the foreign keys for the table, if any. |
| SQLPrimaryKeys() | ODBC | Returns list of all columns that comprise the primary key for the table, if any. |
| SQLProcedureColumns() | ODBC | Returns list of input/output parameters and columns included into returned resultset. |
| SQLProcedures() | ODBC | Returns list of all stored procedures and functions from a data source. |
| SQLSpecialColumns() | X/Open | Returns information about optimal set of columns that uniquely identifies a row in the table, or a list of the columns updated automatically when any value in a row is updated. |
| SQLStatistics() | SQL/CLI | Returns statistic information for specific table, as well as the list of all indices for the table. |
| SQLTablePrivileges() | ODBC | Returns list of tables and all the privileges for the specific table. |
| SQLTables() | X/Open | Returns list of table names from the data source. |
| Transaction Management | ||
| SQLEndTran() | SQL/CLI | Commits or rolls back a transaction. |
| Drivers and Data Source Information | ||
| SQLDataSources() | SQL/CLI | Returns list of all available data sources. |
| SQLDrivers() | ODBC | Returns list of all installed drivers on the system. |
| SQL/CLI Function | Conforms to the Standard | Description |
| SQLGetInfo() | SQL/CLI | Returns information about a specific driver and the data source. |
| SQLGetFunctions() | SQL/CLI | Returns list of all functions supported by the driver. |
| SQLGetTypeInfo() | SQL/CLI | Returns information about all supported data types. |
| Note | ODBC standard does not address security concerns; in fact, unless encryption is used, all information sent through API calls is up for grabs. Using an ODBC driver with built-in encryption alleviates this potential problem. |
|
|
EAN: 2147483647
Pages: 208