Previous Topic: Level 2 APINext Topic: SQL Database Type Mapping Between ODBC and CA IDMS

ODBC Programmer Reference › ODBC Conformance Levels › SQL Conformance Levels

SQL Conformance Levels

ODBC 3.5 defines a minimum SQL grammar, which is a subset of the entry level of the ISO/IEC 9075 (or ANSI X3.135-1992) standard, commonly referred to as SQL-92. ODBC drivers must support at least this minimum grammar. A driver and its underlying DBMS may also implement additional features to comply with conformance levels of the SQL-92 standard itself: entry, intermediate or full. Applications can query a driver's capabilities using the SQLGetInfo function.

CA IDMS Server conforms to the SQL-92 entry level, but also supports some higher-level features as well. For more information, refer to Appendix A of the CA IDMS SQL Reference Guide.

In some instances, CA IDMS SQL syntax differs from the SQL-92 standard, as shown in the following table.

SQL Statement

Comments

CREATE TABLE clauses:
  • DEFAULT—CA IDMS supports WITH DEFAULT, and allows default values of NULL, 0, or blank.
  • UNIQUE—CA IDMS does not support specification of uniqueness constraints at column or table level. A unique index can be defined to provide the same effect.
  • PRIMARY KEY—CA IDMS does not support specification of a primary key at column or table level. A unique index can be defined to provide the same effect.
  • REFERENCES—CA IDMS does not support specification of referential constraints on the CREATE TABLE statement, at column or table level. CREATE CONSTRAINT statement can be used to define referential constraints.
  • CHECK—CA IDMS does not support specification of CHECK constraints at column level. CHECK constraints can be specified at table level.

DROP TABLE

RESTRICT—CA IDMS supports CASCADE, but does not support the RESTRICT keyword. The absence of CASCADE implies RESTRICT.

GRANT UPDATE (column-list) REFERENCES (column-list)

CA IDMS does not support column level security. CA IDMS driver removes the column list and grants UPDATE to all columns of the table.

REVOKE

CASCADE/RESTRICT—CA IDMS does not support the CASCADE and RESTRICT options on REVOKE.

CA IDMS supports:

ISO/IEA/ANSI standard outer join syntax beginning with r17; prior to this outer joins were supported only with the CA IDMS proprietary PRESERVE parameter. If an outer join is coded within an escape sequence, the ODBC driver converts the escape sequence to spaces and pass the statement unchanged to CA IDMS. Scalar functions in escape sequences are supported in the same manner. SQL statements submitted in batch jobs are not supported.

CA IDMS supports data types that map to all ODBC data types.