ODBC Conformance Levels

ODBC defines two different conformance standards for drivers: the API conformance standard and the SQL conformance standard. Each conformance standard is made up of three levels. These levels help application and driver developers establish standard sets of functionality. See Appendix C, “ODBC API and Scalar Functions” in the DataDirect Connect ODBC Reference for more information on ODBC conformance levels.

API Conformance Levels

The API conformance standard is made up of three levels:

• Core API: A set of core functions that correspond to the functions in the X/Open SQL Access Group Call Level Interface specification.
• Level 1 API: Core API functionality plus all Level 1 functionality.
• Level 2 API: Core and Level 1 API functionality plus all Level 2 functionality.

ODBC API Functions

The Connect ODBC drivers support all Core and Level 1 functions. In addition, each driver supports a key set of the Level 2 functions. For a list of supported Level 2 functions by driver, refer to the “ODBC Conformance Levels” section for the database you are connecting to in the Connect ODBC Reference.

SQL Conformance Levels

SQL conformance is made up of three levels: Minimum, Core, and Extended. The Minimum level is designed to meet the basic level of ODBC conformance. The Core level roughly corresponds to the X/Open SQL Access Group SQL CAE specification (1995) and the Extended level provides common DBMS extensions to SQL. Most of the Connect ODBC drivers support all Minimum and Core SQL grammar. In addition, each driver supports a number of extended SQL statements, expressions, and data types. For a list of supported Extended SQL grammar by driver, refer to the appropriate “ODBC Conformance Levels” section in each chapter.

Minimum SQL Grammar

The Minimum level of SQL grammar consists of the following statements, expressions, and data types:

• 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

Core SQL Grammar

The Core level of SQL grammar consists of the following statements, expressions, and data types:

• Minimum SQL grammar and data types
• Data Definition Language (DDL): ALTER TABLE, CREATE INDEX, DROP INDEX, CREATE VIEW, DROP VIEW, GRANT, and REVOKE
• Data Manipulation Language (DML): full SELECT
• Expressions: subquery, set functions such as SUM and MIN
• Data Types: DECIMAL, NUMERIC, SMALLINT, INTEGER, REAL, FLOAT, DOUBLE PRECISION

Extended SQL Grammar

The Extended level of SQL grammar consists of the following statements, expressions, and data types:

• Minimum and Core SQL grammar and data types
• Data Manipulation Language (DML): outer joins, positioned UPDATE, positioned DELETE, SELECT FOR UPDATE, and unions
• Expressions: scalar functions such as SUBSTRING, ABS, date, time, and timestamp literals
• Data types: BIT, TINYINT, BIGINT, BINARY, VARBINARY, LONG VARBINARY, DATE, TIME, TIMESTAMP
• Batch SQL statements
• Procedure calls