Functionality
The functionality available, as well as the missing functionality, is
listed here. See also the SQL Grammar and JDBC API documentation for
details.
Quoted names
Not yet supported
Quoted names are currently not supported. SQL allows quoted names,
however some vendors (Microsoft SQL Server) do not support it by
default. However, internally quoted names are used to reduce the number
of keywords.
Autoincrement
Supported
There is no standard for autoincrement, but many vendors support it in
some way. LDBC supports autoincrement in a vendor independant way.
Please note that manually inserted values for autoincrement column are allowed,
and updates on the autoincrement columns are also allowed.
Here an example on how to use the Autoincrement feature. For details,
see the the SQL grammar.
Creating a table with an autoincrement column:
CREATE TABLE TEST(ID INT AUTOINCREMENT PRIMARY KEY,VALUE VARCHAR(255))
Inserting a row:
INSERT INTO TEST VALUES('Hello')
INSERT INTO TEST(NAME) VALUES('World')
INSERT INTO TEST(ID, NAME) VALUES(3,'How are you')
Selecting all rows:
SELECT * FROM TEST
Updating autoincrement columns:
There is no restriction on updating the values.
UPDATE TEST SET ID=100 WHERE ID=1
Data definition
Supported:
- Creating and dropping columns
- Primary keys
- Foreign keys / Referential integrity
Not yet supported:
- Adding columns to existing tables
- Renaming tables
- Default values
- Index dropping
- Foreign keys / Referential integrity with cascade, restrict and
so on
Subqueries
Partially supported
Subqueries are not fully supported yet. Now even MySQL supports this
feature, so it will be implemented. Currently, only EXISTS and NOT EXISTS are supported.
Access rights
Not yet supported
If possible define the subset supported by many databases. If not
supported, the commands are ignored.
Namespaces
Not yet supported
SQL does not support namespaces, however JDBC defines 'catalogs' and
'schemas'. A common subset needs to be defined, or completely
excluded.
Null handling
Supported
However it may be different across databases.
NULL handling in SQL is 'problematic': NULL is not equal to NULL. A
NULL value in indices is a separate story. The behavior and special
cases need to be documented.
Joins
Supported:
Inner and outer joins are supported. For inner joins, both a list of
tables can be used, as well as the ANSI inner join syntax.
Not yet supported:
Outer joins for Oracle 8i because the ANSI outer join syntax is not
supported by this database.
Full outer joins and cross joins are not supported.
Union
Not yet supported
UNION ALL and UNION DISTINCT are SQL standard, where UNION DISTINCT
is the default.
Managing databases
Not yet supported
An example is a way to create a new database from the application.
SQL does not define a standard way to manage databases. Most
databases do support managing databases in some way, but using very
different syntax and sometimes APIs outside of JDBC. It should be
possible to define a small subset of database management operations.
Logging
Supported
Some JDBC driver support logging, but there is no standard. Logging is
supported, but not finished. There are currently 3 levels of logging:
off, on, detailed. See also JDBC documentation. However, logging is not
done over the DriverManager logging facility, as the underlying JDBC
drivers also use the same mechanism.
Stored procedures and trigger
Not yet supported
Both are very vendor specific.
Standardized error messages / codes
Supported, not finished
Error codes are largely vendor specific. Some applications have to
check for error codes (for example, to re-try after a deadlock).
The goal is to define standard codes for all common errors wherever possible.