Java DB

Apache Derby

Derby Reference Manual

Derby Getting Started
Derby Reference Manual
Derby Developer's Guide
Derby Performance Tuning
Derby Server and Admin Guide
Derby Tools and Utilities
Derby Reference Manual
-SQL language reference
-Capitalization and special characters
-SQL identifiers
-Rules for SQL92 identifiers
-SQL92Identifier
-column-Name
-correlation-Name
-new-table-Name
-schemaName
-Simple-column-Name
-synonym-Name
-table-Name
-view-Name
-index-Name
-constraint-Name
-cursor-Name
-TriggerName
-
-RoleName
-Interaction with the dependency system
-ALTER TABLE
-CALL (PROCEDURE)
-CREATE statements
-DECLARE GLOBAL TEMPORARY TABLE
-DELETE
-DROP statements
-GRANT
-INSERT
-LOCK TABLE
-RENAME statements
-REVOKE
-SET statements
-SELECT
-UPDATE
-SQL clauses
-SQL expressions
-JOIN operations
-SQL queries
-Built-in functions
-Standard built-in functions
-Aggregates (set functions)
-ABS or ABSVAL function
-ACOS function
-ASIN function
-ATAN function
-ATAN2 function
-AVG function
-BIGINT function
-CASE expressions
-CAST function
-CEIL or CEILING function
-CHAR function
-COALESCE function
-Concatenation operator
-COS function
-COSH function
-COT function
-COUNT function
-COUNT(*) function
-CURRENT DATE function
-CURRENT_DATE function
-CURRENT ISOLATION function
-CURRENT_ROLE function
-CURRENT SCHEMA function
-CURRENT TIME function
-CURRENT_TIME function
-CURRENT TIMESTAMP function
-CURRENT_TIMESTAMP function
-CURRENT_USER function
-DATE function
-DAY function
-DEGREES function
-DOUBLE function
-EXP function
-FLOOR function
-HOUR function
-IDENTITY_VAL_LOCAL function
-INTEGER function
-LCASE or LOWER function
-LENGTH function
-LN or LOG function
-LOG10 function
-LOCATE function
-LTRIM function
-MAX function
-MIN function
-MINUTE function
-MOD function
-MONTH function
-NULLIF expressions
-PI function
-RADIANS function
-RANDOM function
-RAND function
-ROW_NUMBER function
-RTRIM function
-SECOND function
-SESSION_USER function
-SIGN function
-SIN function
-SINH function
-SMALLINT function
-SQRT function
-SUBSTR function
-SUM function
-TAN function
-TANH function
-TIME function
-TIMESTAMP function
-TRIM function
-UCASE or UPPER function
-USER function
-VARCHAR function
-XMLEXISTS operator
-XMLPARSE operator
-XMLQUERY operator
-XMLSERIALIZE operator
-YEAR function
-Built-in system functions
-Built-in system procedures
-SYSCS_B ACKUP_D ATABASE
-SYSCS_BAC KUP_DATAB ASE_NOWAIT
-SYSCS_BACKUP_DAT ABASE_AND_ENABLE _LOG_ARCHIVE_MODE
-SYSCS_BACKUP_DATAB ASE_AND_ENABLE_LOG ARCHIVE_MODE_NOWAIT
-SYSCS_EMP TY_STATEM ENT_CACHE
-SYSCS_CH ECKPOINT _DATABASE
-SYSCS_COMPRESS_TABLE
-SYSCS_INP LACE_COMP RESS_TABLE
-SYSCS_DISA BLE_LOG_AR CHIVE_MODE
-SYSCS_EXPORT_TABLE
-SYSCS_EXPOR T_TABLE_LOB S_TO_EXTFILE
-SYSCS_EXPORT_QUERY
-SYSCS_EXPOR T_QUERY_LOB S_TO_EXTFILE
-SYSCS_IMPORT_DATA
-SYSCS_IMPOR T_DATA_LOBS FROM_EXTFILE
-SYSCS_IMPORT_TABLE
-SYSCS_IMPORT _TABLE_LOBS_ FROM_EXTFILE
-SYSCS_F REEZE_D ATABASE
-SYSCS_U NFREEZE DATABASE
-SYSCS_REL OAD_SECUR ITY_POLICY
-SYSCS_SET _DATABASE _PROPERTY
-SYSCS_SET _RUNTIMES TATISTICS
-SYSCS_SET _STATISTI CS_TIMING
-SYSCS_S ET_XPLA N_SCHEMA
-SYSCS_S ET_XPLA IN_MODE
-SYSCS_S ET_USER _ACCESS
-SYSCS_U PDATE_S ATISTICS
-SYSCS_DIAG diagnostic tables and functions
-Data types
-Argument matching
-SQL reserved words
-Derby support for SQL-92 features
-Derby system tables
-XPLAIN style tables
-Derby exception messages and SQL states
-JDBC reference
-java.sql.Driver interface
-java. sql. DriverManager. getConnection method
-java.sql.Connection interface
-java. sql. DatabaseMetaData interface
-java.sql.Statement interface
-java. sql. CallableStatement interface
-java. sql. PreparedStatement interface
-java.sql.ResultSet interface
-java. sql. ResultSetMetaData interface
-java.sql.SQLException class
-java.sql.SQLWarning class
-java.sql.Savepoint interface
-Mapping of java. sql. Types to SQL types
-JDBC Package for Connected Device Configuration/Foundation Profile (JSR 169)
-JDBC 4.0-only features
-Setting attributes for the database connection URL
-bootPassword= key attribute
-collation= collation attribute
-create= true attribute
-createFrom= path attribute
-databaseName= nameofDatabase attribute
-dataEncryption= true attribute
-drop= true attribute
-encryptionKey= key attribute
-encryptionProvider= providerName attribute
-encryptionAlgorithm= algorithm attribute
-failover= true attribute
-logDevice= logDirectoryPath attribute
-newEncryptionKey= key attribute
-newBootPassword= newPassword attribute
-password= userPassword attribute
-restoreFrom= path attribute
-rollForwardRecoveryFrom= path attribute
-securityMechanism= value attribute
-shutdown= true attribute
-slaveHost= hostname attribute
-slavePort= portValue attribute
-startMaster= true attribute
-startSlave= true attribute
-stopMaster= true attribute
-stopSlave= true attribute
-territory= ll_CC attribute
-traceDirectory= path attribute
-traceFile= path attribute
-traceFileAppend= true attribute
-traceLevel= value attribute
-upgrade= true attribute
-user= userName attribute
-ssl= sslMode attribute
-Creating a connection without specifying attributes
-Derby property reference
-Scope of Derby properties
-Dynamic and static properties
-Derby properties
-derby. authentication. builtin. algorithm
-derby. authentication. ldap. searchAuthDN
-derby. authentication. ldap. searchAuthPW
-derby. authentication. ldap. searchBase
-derby. authentication. ldap. searchFilter
-
-
-derby. connection. requireAuthentication
-derby. database. defaultConnectionMode
-derby. database. forceDatabaseLock
-
-
-
-derby. database. readOnlyAccessUsers
-derby. database. sqlAuthorization
-derby.infolog.append
-derby. jdbc. xaTransactionTimeout
-
-derby. language. logStatementText
-
-
-derby. locks. escalationThreshold
-derby.locks.monitor
-
-derby. replication. logBufferSize
-derby. replication. maxLogShippingInterval
-derby. replication. minLogShippingInterval
-
-
-derby. storage. minimumRecordSize
-
-derby. storage. pageReservedSpace
-
-
-
-
-
-
-derby. stream. error. logSeverityLevel
-derby.system.bootAll
-
-derby.system.home
-derby.user.UserName
-J2EE Compliance: Java Transaction API and javax. sql Interfaces
-Derby API
-Supported territories
-Derby limitations

 

Derby support for SQL-92 features

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

Basic data types

The following table shows Derby support for the SQL-92 basic data types.
Table 1. Support for SQL-92 Features: Basic data types
Feature Source Derby
SMALLINT SQL92E Yes
INTEGER SQL92E Yes
DECIMAL(p,s) SQL92E Yes
NUMERIC(p,s) SQL92E Yes
REAL SQL92E Yes
FLOAT(p) SQL92E Yes
DOUBLE PRECISION SQL92E Yes
CHAR(n) SQL92E Yes

Basic math operations

Table 1. Support for SQL-92 Features: Basic math operations
Feature Source Derby
+, *, -, /, unary +, unary - SQL92E Yes

Basic comparisons

Table 1. Support for SQL-92 Features: Basic comparisons
Feature Source Derby
<, >, <= ,>=, <>, = SQL92E Yes

Basic predicates

Table 1. Support for SQL-92 Features: Basic predicates
Feature Source Derby
BETWEEN, LIKE, NULL SQL92E Yes

Quantified predicates

Table 1. Support for SQL-92 Features: Quantified predicates
Feature Source Derby
IN, ALL/SOME, EXISTS SQL92E Yes

Schema definition

Table 1. Support for SQL-92 Features: schema definition
Feature Source Derby
Tables SQL92E Yes
Views SQL92E Yes
Privileges SQL92E Yes

Column attributes

Table 1. Support for SQL-92 Features: column attributes
Feature Source Derby
Default values SQL92E Yes
Nullability SQL92E Yes

Constraints (non-deferrable)

Table 1. Support for SQL-92 Features: constraints (non-deferrable)
Feature Source Derby
NOT NULL SQL92E Yes (not stored in SYSCONSTRAINTS)
UNIQUE/PRIMARY KEY SQL92E Yes
FOREIGN KEY SQL92E Yes
CHECK SQL92E Yes
View WITH CHECK OPTION SQL92E No, views cannot be updated

Cursors

Table 1. Support for SQL-92 Features: Cursors
Feature Source Derby
DECLARE, OPEN, FETCH, CLOSE SQL92E Yes, by using JDBC method calls
UPDATE, DELETE CURRENT SQL92E Yes

Dynamic SQL 1

Table 1. Support for SQL-92 Features: Dynamic SQL 1
Feature Source Derby
ALLOCATE / DEALLOCATE / GET / SET DESCRIPTOR SQL92T Yes, by using JDBC method calls
PREPARE / EXECUTE / EXECUTE IMMEDIATE SQL92T Yes, by using JDBC method calls
DECLARE, OPEN, FETCH, CLOSE, UPDATE, DELETE dynamic cursor SQL92T Yes, by using JDBC method calls
DESCRIBE output SQL92T Yes, by using JDBC method calls

Basic information schema

Table 1. Support for SQL-92 Features: Basic information schema
Feature Source Derby
TABLES SQL92T SYS.SYSTABLES, SYS.SYSVIEWS, SYS.SYSCOLUMNS
VIEWS SQL92T SYS.SYSTABLES, SYS.SYSVIEWS, SYS.SYSCOLUMNS
COLUMNS SQL92T SYS.SYSTABLES, SYS.SYSVIEWS, SYS.SYSCOLUMNS

Basic schema manipulation

Table 1. Support for SQL-92 Features: Basic schema manipulation
Feature Source Derby
CREATE / DROP TABLE SQL92T Yes
CREATE / DROP VIEW SQL92T Yes
GRANT / REVOKE SQL92T Yes
ALTER TABLE ADD COLUMN SQL92T Yes
ALTER TABLE DROP COLUMN SQL92T Yes

Joined table

Table 1. Support for SQL-92 Features: Joined table
Feature Source Derby
INNER JOIN SQL92T Yes
natural join SQL92T No
LEFT, RIGHT OUTER JOIN SQL92T Yes
join condition SQL92T Yes
named columns join SQL92T Yes

Date and time data types

Table 1. Support for SQL-92 Features: Date and time data types
Feature Source Derby
simple DATE, TIME, TIMESTAMP, INTERVAL SQL92T Yes, not INTERVAL
datetime constants SQL92T Yes
datetime math SQL92T Yes, with Java methods
datetime comparisons SQL92T Yes
predicates: OVERLAPS SQL92T Yes, with Java methods

VARCHAR data type

Table 1. Support for SQL-92 Features: VARCHAR
Feature Source Derby
LENGTH SQL92T Yes
concatenation (||) SQL92T Yes

Transaction isolation

Table 1. Support for SQL-92 Features: Transaction isolation
Feature Source Derby
READ WRITE / READ ONLY SQL92T By using JDBC, database properties, and storage media
RU, RC, RR, SER SQL92T Yes

Multiple schemas per user

Table 1. Support for SQL-92 Features: Multiple schemas per user
Feature Source Derby
SCHEMATA view SQL92T SYS.SYSSCHEMAS

Privilege tables

Table 1. Support for SQL-92 Features: Privilege tables
Feature Source Derby
TABLE_PRIVILEGES SQL92T No
COLUMNS_PRIVILEGES SQL92T No
USAGE_PRIVILEGES SQL92T No

Table operations

Table 1. Support for SQL-92 Features: Table operations
Feature Source Derby
UNION relaxations SQL92I Yes
EXCEPT SQL92I Yes
INTERSECT SQL92I Yes
CORRESPONDING SQL92I No

Schema definition statement

Table 1. Support for SQL-92 Features: Schema definition statement
Feature Source Derby
CREATE SCHEMA SQL92I Partial support

User authorization

Table 1. Support for SQL-92 Features: User authorization
Feature Source Derby
SET SESSION AUTHORIZATION SQL92I Use SET SCHEMA
CURRENT_USER SQL92I Yes
SESSION_USER SQL92I Yes
SYSTEM_USER SQL92I No

Constraint tables

Table 1. Support for SQL-92 Features: Constraint tables
Feature Source Derby
TABLE CONSTRAINTS SQL92I SYS.SYSCONSTRAINTS
REFERENTIAL CONSTRAINTS SQL92I SYS.SYSFOREIGNKEYS
CHECK CONSTRAINTS SQL92I SYS.SYSCHECKS

Documentation schema

Table 1. Support for SQL-92 Features: Documentation schema
Feature Source Derby
SQL_FEATURES SQL92I/FIPS 127-2 Use JDBC DatabaseMetaData
SQL_SIZING SQL92I/FIPS 127-2 Use JDBC DatabaseMetaData

Full DATETIME

Table 1. Support for SQL-92 Features: Full DATETIME
Feature Source Derby
precision for TIME and TIMESTAMP SQL92F Yes

Full character functions

Table 1. Support for SQL-92 Features: Full character functions
Feature Source Derby
POSITION expression SQL92F Use Java methods or LOCATE
UPPER/LOWER functions SQL92F Yes

Miscellaneous features

Table 1. Support for SQL-92 Features: Miscellaneous
Feature Source Derby
Delimited identifiers SQL92E Yes
Correlated subqueries SQL92E 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
UNION, INTERSECT, and EXCEPT in views SQL92T Yes
Implicit numeric casting SQL92T Yes
Implicit character casting SQL92T Yes
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 module support SQL92T No (not required and not part of JDBC)
Referential delete actions SQL92T CASCADE, SET NULL, RESTRICT, and NO ACTION
CAST functions SQL92T Yes
INSERT expressions SQL92T Yes
Explicit defaults SQL92T Yes
Keyword relaxations SQL92T Yes
Domain definition SQL92I No
CASE expression SQL92I Partial support
Compound character string constants SQL92I Use concatenation
LIKE enhancements SQL92I Yes
UNIQUE predicate SQL92I No
Usage tables SQL92I SYS.SYSDEPENDS
Intermediate information schema SQL92I Use JDBC DatabaseMetaData and Derby system tables
Subprogram support SQL92I Not relevant to JDBC, which is much richer
Intermediate SQL Flagging SQL92I No
Schema manipulation SQL92I Yes
Long identifiers SQL92I Yes
Full outer join SQL92I No
Time zone specification SQL92I No
Scrolled cursors SQL92I Partial support (scrollable insensitive result sets through JDBC 2.0)
Intermediate set function support SQL92I Partial support
Character set definition SQL92I Support for Java locales
Named character sets SQL92I Support for Java locales
Scalar subquery values SQL92I Yes
Expanded null predicate SQL92I Yes
Constraint management SQL92I Yes (ADD/DROP CONSTRAINT)
FOR BIT DATA types SQL92F Yes
Assertion constraints SQL92F No
Temporary tables SQL92F Partial support, with DECLARE GLOBAL TEMPORARY TABLE
Full dynamic SQL SQL92F No
Full value expressions SQL92F Yes
Truth value tests SQL92F 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 with Java methods
Union join SQL92F No
Collation and translation SQL92F Java locales supported
Referential update actions SQL92F RESTRICT and NO ACTION. Can do others with triggers.
ALTER domain SQL92F nNo
INSERT column privileges SQL92F No
Referential MATCH types SQL92F No
View CHECK enhancements SQL92F No, views cannot be updated
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 Partial support
Catalog flagging SQL92F No
Local table references SQL92F No
Full cursor update SQL92F No
 

javadb@jdbcurl.com