SOLID SQL API is the native call level interface of SOLID DBMS. SOLID SQL API is based on the SQL Access Group's CLI specification, a standard dynamic call level interface. The SQL syntax used in SOLID Server is based on the ANSI X3.135-1989 level 2 standard including important ANSI X3.135-1992 (SQL2) extensions. Developers have also the option of accessing SQL SQL API through ODBC API in the Windows (3.x, NT and 95) environments. ODBC provides a single interface for SQL queries to access a variety of relational and non-relational databases.
SAG CLI History
The SQL Access Group (SAG) was formed in 1989 to define and promote standards for database portability and interoperability. 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 SAG CLI is and 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 to define the X/Open CLI specification and became the first company to commercialize the CLI specification by shipping 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. In 1996, Microsoft announced that ODBC 3.0 will be fully aligned with both ISO's CLI standard and SAG's CLI Specification.
SOLID SQL API is a DLL for Windows and OS/2, and a library for other environents. SOLID SQL API has functions that support a rich set of database access operations sufficient to creating robust database applications, including:
- Allocating and deallocating handles
- Getting and setting attributes
- Opening and closing database connections
- Accessing descriptors
- Executing SQL statements
- Accessing schema metadata
- Controlling transactions
- Accessing diagnostic information
- Cancelling functions
A database application calls these functions for all interactions with a database. SOLID SQL API enables applications to establish multiple database connections simultaneously and to process multiple statements simultaneously.
Using SOLID SQL API
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, user id, and password. The application then allocates memory for a statement handle and calls either SQLExecDirect, which both prepares and executes an SQL statement, or SQLPrepare and SQLExecute, which allows statements to be executed multiple times. If the statement was a SELECT statement, the resulting columns need to be bound to variables in the application. This is done by using SQLBindCol. The rows can be then fetched using repeatedly SQLFetch.If the statement was a UPDATE, DELETE or INSERT, the application needs to check if the execution succeeded and call SQLTransact to commit the transaction. Finally the application closes the connection.
The Open Database Connectivity (ODBC) interface allows applications to access data in database management systems (DBMS) using Structured Query Language (SQL) as a standard for accessing data.
The interface permits maximum interoperability — a single application can access different database management systems. This allows an application developer to develop, compile, and ship an application without targeting a specific DBMS. Users can then add modules called database drivers that link the application to their choice of database management systems.
Accessing SOLID SQL API through ODBC API
Native 16 and 32 bit ODBC drivers are available for maximum power and functionality. When SOLID SQL API is accessed through ODBC API developers can use also those ODBC functions that are implemented in the ODBC Driver Manager.
The ODBC interface can be used in Windows 3.x, Windows 95 and Windows NT clients.
In the traditional database world, application has usually meant a program that performed a specific database task with a specific DBMS in mind such as payroll, financial analysis, or inventory management. Such applications have typically been written using embedded SQL. While embedded SQL is efficient and is portable across different hardware and operating system environments, the source code must be recompiled for each new environment.
Embedded SQL is not optimal for applications that need to analyze data stored in databases such as DB2® and Oracle®, and prefer to do so from within a familiar application interface, such as a Microsoft Excel® spreadsheet. Under the traditional approach to database access, one version of Microsoft Excel would have to be precompiled with the IBM® precompiler and another with the Oracle precompiler, clearly a radical departure from simply buying a single packaged product.
ODBC offers a new approach: provide a separate program to extract the database information, and then have a way for applications to import the data. Since there are and probably always will be many viable communication methods, data protocols, and DBMS capabilities, the ODBC solution is to allow different technologies to be used by defining a standard interface. This solution leads to the idea of database drivers — dynamic-link libraries that an application can invoke on demand to gain access to a particular data source through a particular communications method, much like a printer driver running under Windows. ODBC provides the standard interface that allows both application writers and providers of libraries to shuttle data between applications and data sources.
- A library of ODBC function calls that allow an application to connect to a DBMS, execute SQL statements, and retrieve results.
- SQL syntax based on the X/Open and SQL Access Group (SAG) SQL CAE specification (1992).
- A standard set of error codes.
- A standard way to connect and log on to a DBMS.
- A standard representation for data types.
- Strings containing SQL statements can be explicitly included in source code or constructed on the fly at run time.
- The same object code can be used to access different DBMS products.
- An application can ignore underlying data communications protocols between it and a DBMS product.
- Data values can be sent and retrieved in a format convenient to the application.
- The ODBC interface provides two types of function calls:
- Core functions are based on the X/Open and SQL Access Group Call Level Interface specification.
- Extended functions support additional functionality, including scrollable cursors and asynchronous processing.
To send an SQL statement, include the statement as an argument in an ODBC function call. The statement need not be customized for a specific DBMS.
- Application Performs processing and calls ODBC functions to submit SQL statements and retrieve results.
- Driver Manager Loads drivers on behalf of an application.
- Driver Processes ODBC function calls, submits SQL requests to a specific data source, and returns results to the application. If necessary, the driver modifies an application’s request so that the request conforms to syntax supported by the associated DBMS.
- Data source Consists of the data the user wants to access and its associated operating system, DBMS, and network platform (if any) used to access the DBMS.
The Driver Manager and driver appear to an application as one unit that processes ODBC function calls. The following paragraphs describe each component in more detail.
An application using the ODBC interface performs the following tasks:
- Requests a connection, or session, with a data source.
- Sends SQL requests to the data source.
- Defines storage areas and data formats for the results of SQL requests.
- Requests results.
- Processes errors.
- Reports results back to a user, if necessary.
- Requests commit or rollback operations for transaction control.
- Terminates the connection to the data source.
An application can provide a variety of features external to the ODBC interface, including mail, spreadsheet capabilities, online transaction processing, and report generation; the application may or may not interact with users.
The Driver Manager, provided by Microsoft, is a dynamic-link library (DLL) with an import library. The primary purpose of the Driver Manager is to load drivers. The Driver Manager also performs the following:
- Uses the ODBC.INI file or registry to map a data source name to a specific driver dynamic-link library (DLL).
- Processes several ODBC initialization calls.
- Provides entry points to ODBC functions for each driver.
- Provides parameter validation and sequence validation for ODBC calls.
A driver is a DLL that implements ODBC function calls and interacts with a data source.
The Driver Manager loads a driver when the application calls the SQLBrowseConnect, SQLConnect, or SQLDriverConnect function.
A driver performs the following tasks in response to ODBC function calls from an application:
- Establishes a connection to a data source.
- Submits requests to the data source.
- Translates data to or from other formats, if requested by the application.
- Returns results to the application.
- Formats errors into standard error codes and returns them to the application.
- Declares and manipulates cursors if necessary. (This operation is invisible to the application unless there is a request for access to a cursor name.)
- Initiates transactions if the data source requires explicit transaction initiation. (This operation is invisible to the application.)
In this manual, DBMS refers to the general features and functionality provided by an SQL database management system. A data source is a specific instance of a combination of a DBMS product and any remote operating system and network necessary to access it.
An application establishes a connection with a particular vendor’s DBMS product on a particular operating system, accessible by a particular network. For example, the application might establish connections to:
- An Oracle DBMS running on an OS/2® operating system, accessed by Novell® netware.
- A local Xbase file, in which case the network and remote operating system are not part of the communication path.
- A Tandem NonStop™ SQL DBMS running on the Guardian 90 operating system, accessed via a gateway.
One of the strengths of the ODBC interface is interoperability; a programmer can create an ODBC application without targeting a specific data source. Users can add drivers to the application after it is compiled and shipped.
From an application standpoint, it would be ideal if every driver and data source supported the same set of ODBC function calls and SQL statements. However, data sources and their associated drivers provide a varying range of functionality. Therefore, the ODBC interface defines conformance levels, which determine the ODBC procedures and SQL statements supported by a driver.
ODBC defines conformance levels for drivers in two areas: the ODBC API and the ODBC SQL grammar (which includes the ODBC SQL data types). Conformance levels help both application and driver developers by establishing standard sets of functionality. Applications can easily determine if a driver provides the functionality they need. Drivers can be developed to support a broad selection of applications without being concerned about the specific requirements of each application.
To claim that it conforms to a given API or SQL conformance level, a driver must support all the functionality in that conformance level, regardless of whether that functionality is supported by the DBMS associated with the driver. However, conformance levels do not restrict drivers to the functionality in the levels to which they conform. Driver developers are encouraged to support as much functionality as they can; applications can determine the functionality supported by a driver by calling SQLGetInfo, SQLGetFunctions, and SQLGetTypeInfo.
API Conformance Levels
The ODBC API defines a set of core functions that correspond to the functions in the X/Open and SQL Access Group Call Level Interface specification. ODBC also defines two extended sets of functionality, Level 1 and Level 2. The following list summarizes the functionality included in each conformance level.
- Allocate and free environment, connection, and statement handles.
- Connect to data sources. Use multiple statements on a connection.
- Prepare and execute SQL statements. Execute SQL statements immediately.
- Assign storage for parameters in an SQL statement and result columns.
- Retrieve data from a result set. Retrieve information about a result set.
- Commit or roll back transactions.
- Retrieve error information.
- Core API functionality.
- Connect to data sources with driver-specific dialog boxes.
- Set and inquire values of statement and connection options.
- Send part or all of a parameter value (useful for long data).
- Retrieve part or all of a result column value (useful for long data).
- Retrieve catalog information (columns, special columns, statistics, and tables).
- Retrieve information about driver and data source capabilities, such as supported data types, scalar functions, and ODBC functions.
- Core and Level 1 API functionality.
- Browse connection information and list available data sources.
- Send arrays of parameter values. Retrieve arrays of result column values.
- Retrieve the number of parameters and describe individual parameters.
- Use a scrollable cursor.
- Retrieve the native form of an SQL statement.
- Retrieve catalog information (privileges, keys, and procedures).
- Call a translation DLL.
- For a list of functions and their conformance levels, see Chapter 12, "Function Summary."
Note Each function description in this manual indicates whether the function is a core function or a level 1 or level 2 extension function.
SQL Conformance Levels
ODBC defines a core grammar that roughly corresponds to the X/Open and SQL Access Group SQL CAE specification (1992). ODBC also defines a minimum grammar, to meet a basic level of ODBC conformance, and an extended grammar, to provide for common DBMS extensions to SQL. The following list summarizes the grammar included in each conformance level.
- Data Definition Language (DDL): CREATE TABLE and DROP TABLE.
- Data Manipulation Language (DML): simple SELECT, INSERT, UPDATE SEARCHED, and DELETE SEARCHED.
- Expressions: simple (such as A > B + C).
- Data types: CHAR, VARCHAR, or LONG VARCHAR.
- Minimum SQL grammar and data types.
- ALTER TABLE, CREATE INDEX, DROP INDEX, CREATE VIEW, DROP VIEW, GRANT, and REVOKE.
- full SELECT.
- Expressions: subquery, set functions such as SUM and MIN.
- Data types: DECIMAL, NUMERIC, SMALLINT, INTEGER, REAL, FLOAT, DOUBLE PRECISION.
- Minimum and Core SQL grammar and data types.
- outer joins, positioned UPDATE, positioned DELETE, SELECT FOR UPDATE, and unions.
Note In ODBC 1.0, positioned update, positioned delete, and SELECT FOR UPDATE statements and the UNION clause were part of the core SQL grammar; in ODBC 2.0, they are part of the extended grammar. Applications that use the SQL conformance level to determine whether these statements are supported also need to check the version number of the driver to correctly interpret the information. In particular, applications that use these features with ODBC 1.0 drivers need to explicitly check for these capabilities in ODBC 2.0 drivers.
- Expressions: scalar functions such as SUBSTRING and ABS, date, time, and timestamp literals.
- Data types: BIT, TINYINT, BIGINT, BINARY, VARBINARY, LONG VARBINARY, DATE, TIME, TIMESTAMP
- Batch SQL statements.
- Procedure calls.
For more information about data types, see Appendix D, "Data Types."
The ODBC functions and SQL statements that a driver supports usually depend on the capabilities of its associated data source. Driver developers are encouraged, however, to implement as many ODBC functions as possible to ensure the widest possible use by applications.
The ODBC functions and SQL statements that an application uses depend on:
- The functionality needed by the application.
- The performance needed by the application.
- The data sources to be accessed by the application and the extent to which the application must be interoperable among these data sources.
- The functionality available in the drivers used by the application.
Because drivers support different levels of functionality, application developers may have to make trade-offs among the factors listed above. For example, an application might display the data in a table. It uses SQLColumnPrivileges to determine which columns a user can update and dims those columns the user cannot update. If some of the drivers available to the developer of this application do not support SQLColumnPrivileges, the developer can decide to:
- Use all the drivers and not dim any columns. The application behaves the same for all data sources, but has reduced functionality: the user might attempt to update data in a column for which they do not have update privileges. The application returns an error message only when the driver attempts to update the data in the data source.
- Use only those drivers that support SQLColumnPrivileges. The application behaves the same for all supported data sources, but has reduced functionality: the application does not support all the drivers.
- Use all the drivers and, for drivers that support SQLColumnPrivileges, dim columns the user cannot update. Otherwise, warn the user that they might not have update privileges on all columns. The application behaves differently for different data sources but has increased functionality: the application supports all drivers and sometimes dims columns the user cannot update.
- Use all the drivers and always dim columns the user cannot update; the application locally implements SQLColumnPrivileges for those drivers that do not support it. The application behaves the same for all data sources and has maximum functionality. However, the developer must know how to retrieve column privileges from some of the data sources, the application contains data source–specific code, and developement time is longer.
Developers of specialized applications may make different trade-offs than developers of generalized applications. For example, the developer of an application that only transfers data between two DBMS’s (each from a different vendor) can safely exploit the full functionality of each of the drivers.
Before an application can use ODBC, it must initialize ODBC and request an environment handle (henv). To communicate with a data source, the application must request a connection handle (hdbc) and connect to the data source. The application uses the environment and connection handles in subsequent ODBC calls to refer to the environment and specific connection.
An application may request multiple connections for one or more data sources. Each connection is considered a separate transaction space.
An active connection can have one or more statement processing streams.
A driver maintains a transaction for each active connection. The application can request that each SQL statement be automatically committed on completion; otherwise, the driver waits for an explicit commit or rollback request from the application. When the driver performs a commit or rollback operation, the driver resets all statement requests associated with the connection.
The Driver Manager manages the work of allowing an application to switch connections while transactions are in progress on the current connection.
Copyright © 1992-1997 Solid Information Technology Ltd All rights reserved.