لطفا منتظر باشید ...
Chapter 16: SQL API
Before asingle 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. |
