Cloudscape 3.6 Support for SQL-92 Features
Page 1 of 1

3
Cloudscape 3.6 Support for SQL-92 Features

Table 3-1 shows the SQL-92 features that Cloudscape 3.6 supports. There are four levels of SQL-92 support:

  • SQL92E

    Entry

  • SQL92T

    Transitional, a level defined by NIST in a publication called FIPS 127-2

  • SQL92I

    Intermediate

  • SQL92F

    Full

    Table 3-1 Support for SQL-92 Features 

    Feature

    Source

    Cloudscape 3.6

    Basic types

    SQL92E


    SMALLINT

    yes

    INTEGER

    yes

    DECIMAL(p,s)

    yes

    NUMERIC(p,s)

    yes

    REAL

    yes

    FLOAT(p)

    yes

    DOUBLE PRECISION

    yes

    CHAR(n)

    yes

    Basic math operations

    SQL92E


    +, *, -, /, unary +, unary -

    yes

    Basic comparisons

    SQL92E


    <, >, <= ,>=, <>, =

    yes

    Basic predicates

    SQL92E


    BETWEEN, LIKE, NULL

    yes (except ESCAPE clause on LIKE)

    Quantified predicates

    SQL92E


    IN, ALL/SOME, EXISTS

    yes

    schema definition

    SQL92E


    tables

    yes

    views

    yes (not updatable)

    privileges

    no

    column attributes

    SQL92E


    default values

    yes

    nullability

    yes

    constraints (non-deferrable)

    SQL92E


    NOT NULL

    yes (not stored in SYSCONSTRAINTS)

    UNIQUE/PRIMARY KEY

    yes

    FOREIGN KEY

    yes

    CHECK

    yes

    View WITH CHECK OPTION

    no, since views are not updatable

    Delimited identifiers

    SQL92E

    yes

    Correlated subqueries

    SQL92E

    yes

    Cursors

    SQL92E


    DECLARE, OPEN, FETCH, CLOSE

    done through JDBC

    UPDATE, DELETE CURRENT

    yes

    Insert, Update, Delete statements

    SQL92E

    yes

    Joins

    SQL92E

    yes

    Where qualifications

    SQL92E

    yes

    Group by

    SQL92E

    yes

    Having

    SQL92E

    yes

    Aggregate functions

    SQL92E

    yes

    Order by

    SQL92E

    yes

    Select expressions

    SQL92E

    yes

    Select *

    SQL92E

    yes

    SQLCODE

    SQL92E

    no, deprecated in SQL-92

    SQLSTATE

    SQL92E

    yes

    Dynamic SQL 1

    SQL92T

    done through JDBC

    ALLOCATE/DEALLOCATE/GET/SET DESCRIPTOR

    PREPARE/EXECUTE/EXECUTE IMMEDIATE

    DECLARE, OPEN, FETCH, CLOSE, UPDATE, DELETE dynamic cursor

    DESCRIBE output

    Basic information schema

    SQL92T

    SYS.SYSTABLES, SYS.SYSVIEWS, SYS.SYSCOLUMNS

    TABLES

    VIEWS

    COLUMNS

    Basic schema manipulation

    SQL92T


    CREATE/DROP TABLE

    yes

    CREATE/DROP VIEW

    yes

    GRANT/REVOKE

    no

    ALTER TABLE ADD COLUMN

    yes

    ALTER TABLE DROP COLUMN

    no

    Joined table

    SQL92T


    INNER JOIN

    yes

    natural join

    no

    LEFT, RIGHT OUTER JOIN

    yes

    join condition

    yes

    named columns join

    yes

    DATETIME data types 1

    SQL92T


    simple DATE, TIME, TIMESTAMP, INTERVAL

    yes, not INTERVAL

    datetime literals

    yes

    datetime math

    can do with Java methods

    datetime comparisons

    yes

    predicates: OVERLAPS

    can do with Java methods

    VARCHAR

    SQL92T

    yes

    LENGTH


    yes

    SUBSTRING

    yes

    concatenation (||)

    yes

    TRIM function

    SQL92T

    yes

    UNION in views

    SQL92T

    yes

    Implicit numeric casting

    SQL92T

    yes

    Implicit character casting

    SQL92T

    yes

    Transaction isolation

    SQL92T


    READ WRITE/READ ONLY

    through JDBC, database properties, and storage media.

    RU, RC, RR, SER

    RC, SER, and RR (maps to SER)

    Get diagnostics

    SQL92T

    use JDBC SQLExceptions

    Grouped operations

    SQL92T

    yes

    Qualified * in select list

    SQL92T

    yes

    Lowercase identifiers

    SQL92T

    yes

    nullable PRIMARY KEYs

    SQL92T

    no

    Multiple schemas per user

    SQL92T

    yes

    SCHEMATA view

    SYS.SYSSCHEMAS

    Multiple module support

    SQL92T

    no (not required and not part of JDBC)

    Referential delete actions

    SQL92T

    only RESTRICT, or use triggers

    CAST functions

    SQL92T

    yes

    INSERT expressions

    SQL92T

    yes

    Explicit defaults

    SQL92T

    yes

    Privilege tables

    SQL92T

    no

    TABLE_PRIVILEGES


    COLUMNS_PRIVILEGES


    USAGE_PRIVILEGES


    Keyword relaxations

    SQL92T

    yes

    Domain definition

    SQL92I

    no

    CASE expression

    SQL92I

    use conditional expression

    Compound character literals

    SQL92I

    use concatenation

    LIKE enhancements

    SQL92I

    no

    UNIQUE predicate

    SQL92I

    no

    Table operations

    SQL92I


    UNION relaxations

    yes

    EXCEPT

    no

    INTERSECT

    no

    CORRESPONDING

    no

    Schema definition statement

    SQL92I


    CREATE SCHEMA

    yes, partially

    User authorization

    SQL92I


    SET SESSION AUTHORIZATION

    use set schema

    CURRENT_USER

    yes

    SESSION_USER

    yes

    SYSTEM_USER

    no

    Constraint tables

    SQL92I


    TABLE_CONSTRAINTS

    SYS.SYSCONSTRAINTS

    REFERENTIAL_CONSTRAINTS

    SYS.SYSFOREIGNKEYS

    CHECK_CONSTRAINTS

    SYS.SYSCHECKS

    Usage tables

    SQL92I

    SYS.SYSDEPENDS

    Intermediate information schema

    SQL92I

    use JDBC DatabaseMetaData and Cloudscape system tables

    Subprogram support

    SQL92I

    not relevant to JDBC, which is much richer

    Intermediate SQL Flagging

    SQL92I

    no

    Schema manipulation

    SQL92I

    ADD and DROP CONSTRAINT, MODIFY DEFAULT

    Long identifiers

    SQL92I

    yes

    Full outer join

    SQL92I

    no

    Time zone specification

    SQL92I

    no

    National character

    SQL92I

    yes

    Scrolled cursors

    SQL92I

    partial (scrolling insensitive result sets through JDBC 2.0)

    Intermediate set function support

    SQL92I

    partial

    Character set definition

    SQL92I

    supports Java locales

    Named character sets

    SQL92I

    supports Java locales

    Scalar subquery values

    SQL92I

    yes

    Expanded null predicate

    SQL92I

    yes

    Constraint management

    SQL92I

    yes (ADD/DROP CONSTRAINT)

    Documentation schema

    SQL92I/FIPS 127-2


    SQL_FEATURES

    use JDBC DatabaseMetaData

    SQL_SIZING

    use JDBC DatabaseMetaData

    BIT data types

    SQL92F

    yes

    Assertion constraints

    SQL92F

    no

    Temporary tables

    SQL92F

    no

    Full dynamic SQL

    SQL92F

    no

    Full DATETIME

    SQL92F


    precision for TIME and TIMESTAMP

    no

    Full value expressions

    SQL92F

    yes

    Truth value tests

    SQL92F

    yes

    Full character functions

    SQL92F


    POSITION expression

    use Java methods

    UPPER/LOWER functions

    yes

    Derived tables in FROM

    SQL92F

    yes

    Trailing underscore

    SQL92F

    yes

    Indicator data types

    SQL92F

    not relevant to JDBC

    Referential name order

    SQL92F

    no

    Full SQL Flagging

    SQL92F

    no

    Row and table constructors

    SQL92F

    yes

    Catalog name qualifiers

    SQL92F

    no

    Simple tables

    SQL92F

    no

    Subqueries in CHECK

    SQL92F

    no, but can do with Java methods

    Union join

    SQL92F

    no

    Cross join

    SQL92F

    yes

    Collation and translation

    SQL92F

    Java locales supported

    Referential update actions

    SQL92F

    can do with triggers

    ALTER domain

    SQL92F

    no

    Deferrable constraints

    SQL92F

    SET CONSTRAINTS

    INSERT column privileges

    SQL92F

    no

    Referential MATCH types

    SQL92F

    no

    View CHECK enhancements

    SQL92F

    no, views not updateable

    Session management

    SQL92F

    use JDBC

    Connection management

    SQL92F

    use JDBC

    Self-referencing operations

    SQL92F

    yes

    Insensitive cursors

    SQL92F

    Yes through JDBC 2.0

    Full set function

    SQL92F

    partially

    Catalog flagging

    SQL92F

    no

    Local table references

    SQL92F

    no

    Full cursor update

    SQL92F

    no