Derby and standards
to SQL99 standards wherever possible. Below you will find a guide to those
features currently in Derby that
are not standard; these features are currently being evaluated and might be
removed in future releases.
This section describes those parts of Derby that
are non-standard or not typical for a database system.
- ALTER TABLE
Derby uses a slightly different ALTER TABLE syntax for altering column defaults. While SQL99 uses DROP and SET, Derby uses DEFAULT.
- Calling functions and procedures
Derby supports the CALL (procedure) statement for calling external procedures declared by the CREATE PROCEDURE statement. Built-in functions and user-defined functions declared with the CREATE FUNCTION command can be called as part of an SQL select statement or by using either a VALUES clause or VALUES expression.
- CLOB and BLOB
Derby supports the standard CLOB and BLOB data types. BLOB and CLOB values are limited to a maximum of 2,147,483,647 characters.
Derby uses JDBC's result sets, and does not provide SQL for manipulating cursors except for positioned update and delete. Derby's scrollable insensitive cursors are provided through JDBC, not through SQL commands.
- DECIMAL max precision
For Derby, the maximum precision for DECIMAL columns is 31 digits. SQL99 does not require a specific maximum precision for decimals, but most products have a maximum precision of 15-32 digits.
- Dynamic SQL
Derby uses JDBC's Prepared Statement, and does not provide SQL commands for dynamic SQL.
- Expressions on LONGs
Derby permits expressions on LONG VARCHAR; however LONG VARCHAR data types are not allowed in the following clauses, operations, constraints, functions, and predicates.
- Information schema
Derby uses its own system catalog that can be accessed using standard JDBC DatabaseMetadata calls. Derby does not provide the standard Information Schema views.
- NOT NULL characteristic
The SQL standard says NOT NULL is a constraint, and can be named and viewed in the information schema as such. Derby does not provide naming for NOT NULL, nor does it present it as a constraint in the information schema, only as a characteristic of the column.
All operations in Derby are transactional. Derby supports transaction control using JDBC 3.0 Connection methods. This includes support for savepoints and for the four JDBC transaction isolation levels. The only SQL command provided for transaction control is SET TRANSACTION ISOLATION.
- Stored routines and PSM
Derby supports external procedures using the Java programming language. Procedures are managed using the CREATE PROCEDURE and DROP PROCEDURE statements.
- Unique constraints and nulls
The SQL standard defines that unique constraints on nullable columns allow any number of nulls; Derby does not permit unique constraints on nullable columns.
- XML data types and operators
Derby supports the XML data type and a set of operators that work with the XML data type. The XML data type and operators are based on a small subset of the SQL/XML specification.