Copyright
License
About this guide
Purpose of this document
Audience
How this guide is organized
SQL syntax used in this manual
SQL language reference
Capitalization and special characters
SQL identifiers
Rules for SQL identifiers
SQLIdentifier
Qualifying dictionary objects
aggregateName
authorizationIdentifier
columnName
constraintName
correlationName
cursorName
functionName
indexName
newTableName
procedureName
roleName
schemaName
sequenceName
simpleColumnName
synonymName
tableName
triggerName
typeName
viewName
Statements
Interaction with the dependency system
ALTER TABLE statement
CALL (PROCEDURE) statement
CREATE statements
CREATE DERBY AGGREGATE statement
CREATE FUNCTION statement
CREATE INDEX statement
CREATE PROCEDURE statement
CREATE ROLE statement
CREATE SCHEMA statement
CREATE SEQUENCE statement
CREATE SYNONYM statement
CREATE TABLE statement
columnDefinition
generatedColumnSpec
generationClause
CREATE TRIGGER statement
referencingClause
WHEN clause
CREATE TYPE statement
CREATE VIEW statement
DECLARE GLOBAL TEMPORARY TABLE statement
DELETE statement
DROP statements
DROP DERBY AGGREGATE statement
DROP FUNCTION statement
DROP INDEX statement
DROP PROCEDURE statement
DROP ROLE statement
DROP SCHEMA statement
DROP SEQUENCE statement
DROP SYNONYM statement
DROP TABLE statement
DROP TRIGGER statement
DROP TYPE statement
DROP VIEW statement
GRANT statement
INSERT statement
LOCK TABLE statement
MERGE statement
RENAME statements
RENAME COLUMN statement
RENAME INDEX statement
RENAME TABLE statement
REVOKE statement
SELECT statement
SET statements
SET CONSTRAINTS statement
SET ISOLATION statement
SET ROLE statement
SET SCHEMA statement
TRUNCATE TABLE statement
UPDATE statement
SQL clauses
CONSTRAINT clause
columnLevelConstraint
tableLevelConstraint
REFERENCES clause
constraintCharacteristics
EXTERNAL NAME clause
FOR UPDATE clause
FROM clause
GROUP BY clause
HAVING clause
WINDOW clause
ORDER BY clause
The result offset and fetch first clauses
USING clause
WHERE clause
WHERE CURRENT OF clause
SQL expressions
selectExpression
tableExpression
tableViewOrFunctionExpression
tableFunctionInvocation
NEXT VALUE FOR expression
VALUES expression
Expression precedence
Boolean expressions
CASE expression
Dynamic parameters
Dynamic parameters example
Where dynamic parameters are allowed
JOIN operations
INNER JOIN operation
LEFT OUTER JOIN operation
RIGHT OUTER JOIN operation
CROSS JOIN operation
NATURAL JOIN operation
SQL queries
query
scalarSubquery
tableSubquery
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
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 function
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
SYSCS_UTIL.SYSCS_CHECK_TABLE system function
SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY system function
SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS system function
SYSCS_UTIL.SYSCS_GET_USER_ACCESS system function
SYSCS_UTIL.SYSCS_GET_XPLAIN_MODE system function
SYSCS_UTIL.SYSCS_GET_XPLAIN_SCHEMA system function
SYSCS_UTIL.SYSCS_PEEK_AT_IDENTITY system function
SYSCS_UTIL.SYSCS_PEEK_AT_SEQUENCE system function
Built-in system procedures
SYSCS_UTIL.SYSCS_BACKUP_DATABASE system procedure
SYSCS_UTIL.SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE system procedure
SYSCS_UTIL.SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE_NOWAIT system procedure
SYSCS_UTIL.SYSCS_BACKUP_DATABASE_NOWAIT system procedure
SYSCS_UTIL.SYSCS_CHECKPOINT_DATABASE system procedure
SYSCS_UTIL.SYSCS_COMPRESS_TABLE system procedure
SYSCS_UTIL.SYSCS_CREATE_USER system procedure
SYSCS_UTIL.SYSCS_DISABLE_LOG_ARCHIVE_MODE system procedure
SYSCS_UTIL.SYSCS_DROP_STATISTICS system procedure
SYSCS_UTIL.SYSCS_DROP_USER system procedure
SYSCS_UTIL.SYSCS_EMPTY_STATEMENT_CACHE system procedure
SYSCS_UTIL.SYSCS_EXPORT_QUERY system procedure
SYSCS_UTIL.SYSCS_EXPORT_QUERY_LOBS_TO_EXTFILE system procedure
SYSCS_UTIL.SYSCS_EXPORT_TABLE system procedure
SYSCS_UTIL.SYSCS_EXPORT_TABLE_LOBS_TO_EXTFILE system procedure
SYSCS_UTIL.SYSCS_FREEZE_DATABASE system procedure
SYSCS_UTIL.SYSCS_IMPORT_DATA system procedure
SYSCS_UTIL.SYSCS_IMPORT_DATA_LOBS_FROM_EXTFILE system procedure
SYSCS_UTIL.SYSCS_IMPORT_TABLE system procedure
SYSCS_UTIL.SYSCS_IMPORT_TABLE_LOBS_FROM_EXTFILE system procedure
SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE system procedure
SYSCS_UTIL.SYSCS_INVALIDATE_STORED_STATEMENTS system procedure
SYSCS_UTIL.SYSCS_MODIFY_PASSWORD system procedure
SYSCS_UTIL.SYSCS_REGISTER_TOOL system procedure
SYSCS_UTIL.SYSCS_RELOAD_SECURITY_POLICY system procedure
SYSCS_UTIL.SYSCS_RESET_PASSWORD system procedure
SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY system procedure
SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS system procedure
SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING system procedure
SYSCS_UTIL.SYSCS_SET_USER_ACCESS system procedure
SYSCS_UTIL.SYSCS_SET_XPLAIN_MODE system procedure
SYSCS_UTIL.SYSCS_SET_XPLAIN_SCHEMA system procedure
SYSCS_UTIL.SYSCS_UNFREEZE_DATABASE system procedure
SYSCS_UTIL.SYSCS_UPDATE_STATISTICS system procedure
System procedures for storing jar files in a database
SQLJ.INSTALL_JAR system procedure
SQLJ.REMOVE_JAR system procedure
SQLJ.REPLACE_JAR system procedure
SYSCS_DIAG diagnostic tables and functions
SYSCS_DIAG.CONTAINED_ROLES diagnostic table function
SYSCS_DIAG.ERROR_LOG_READER diagnostic table function
SYSCS_DIAG.ERROR_MESSAGES diagnostic table
SYSCS_DIAG.LOCK_TABLE diagnostic table
SYSCS_DIAG.SPACE_TABLE diagnostic table function
SYSCS_DIAG.STATEMENT_CACHE diagnostic table
SYSCS_DIAG.STATEMENT_DURATION diagnostic table function
SYSCS_DIAG.TRANSACTION_TABLE diagnostic table
Data types
Built-in type overview
Numeric types
Numeric type overview
Numeric type promotion in expressions
Storing values of one numeric data type in columns of another numeric data type
Scale for decimal arithmetic
Data type assignments and comparison, sorting, and ordering
BIGINT data type
BLOB data type
BOOLEAN data type
CHAR data type
CHAR FOR BIT DATA data type
CLOB data type
DATE data type
DECIMAL data type
DOUBLE data type
DOUBLE PRECISION data type
FLOAT data type
INTEGER data type
LONG VARCHAR data type
LONG VARCHAR FOR BIT DATA data type
NUMERIC data type
REAL data type
SMALLINT data type
TIME data type
TIMESTAMP data type
User-defined types
VARCHAR data type
VARCHAR FOR BIT DATA data type
XML data type
Argument matching
SQL reserved words
Derby support for SQL:2011 features
SQL:2011 features not supported by Derby
Derby system tables
SYSALIASES system table
SYSCHECKS system table
SYSCOLPERMS system table
SYSCOLUMNS system table
SYSCONGLOMERATES system table
SYSCONSTRAINTS system table
SYSDEPENDS system table
SYSFILES system table
SYSFOREIGNKEYS system table
SYSKEYS system table
SYSPERMS system table
SYSROLES system table
SYSROUTINEPERMS system table
SYSSCHEMAS system table
SYSSEQUENCES system table
SYSSTATEMENTS system table
SYSSTATISTICS system table
SYSTABLEPERMS system table
SYSTABLES system table
SYSTRIGGERS system table
SYSUSERS system table
SYSVIEWS system table
XPLAIN style tables
SYSXPLAIN_STATEMENTS system table
SYSXPLAIN_STATEMENT_TIMINGS system table
SYSXPLAIN_RESULTSETS system table
SYSXPLAIN_RESULTSET_TIMINGS system table
SYSXPLAIN_SCAN_PROPS system table
SYSXPLAIN_SORT_PROPS system table
Derby exception messages and SQL states
SQL error messages and exceptions
JDBC reference
java.sql.Driver interface
java.sql.Driver.getPropertyInfo method
java.sql.DriverManager.getConnection method
Derby database connection URL syntax
Syntax of database connection URLs for applications with embedded databases
Additional SQL syntax
Attributes of the Derby database connection URL
java.sql.Connection interface
java.sql.Connection.setTransactionIsolation method
java.sql.Connection.setReadOnly method
java.sql.Connection.isReadOnly method
Connection functionality not supported
java.sql.DatabaseMetaData interface
DatabaseMetaData result sets
Columns in the ResultSets returned by getFunctionColumns and getProcedureColumns
java.sql.DatabaseMetaData.getBestRowIdentifier method
java.sql.Statement interface
ResultSet objects
Autogenerated keys
java.sql.CallableStatement interface
CallableStatements and OUT Parameters
CallableStatements and INOUT parameters
java.sql.PreparedStatement interface
Prepared statements and streaming columns
java.sql.ResultSet interface
ResultSets and streaming columns
java.sql.ResultSetMetaData interface
java.sql.SQLException class
java.sql.SQLWarning class
java.sql.SQLXML interface
java.sql.Savepoint interface
Mapping of java.sql.Types to SQL types
Mapping of java.sql.Blob and java.sql.Clob interfaces
Notes on mapping of java.sql.Blob and java.sql.Clob interfaces
Features supported on JDBC 4.1 and above
java.sql.Connection interface: JDBC 4.1 features
JDBC 4.2-only features
JDBC support for Java SE 8 Compact Profiles
java.sql.DatabaseMetaData interface: JDBC 4.2 features
java.sql.SQLType interface
JDBC escape syntax
JDBC escape keyword for call statements
JDBC escape syntax for LIKE clauses
JDBC escape syntax for limit/offset clauses
JDBC escape syntax for fn keyword
JDBC escape syntax for outer joins
JDBC escape syntax for time formats
JDBC escape syntax for date formats
JDBC escape syntax for timestamp formats
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
decryptDatabase=true attribute
deregister=false attribute
drop=true attribute
encryptionKey=key attribute
encryptionKeyLength=length attribute
encryptionProvider=providerName attribute
encryptionAlgorithm=algorithm attribute
failover=true attribute
logDevice=logDirectoryPath attribute
newBootPassword=newPassword attribute
newEncryptionKey=key attribute
password=userPassword attribute
restoreFrom=path attribute
retrieveMessageText=false attribute
rollForwardRecoveryFrom=path attribute
securityMechanism=value attribute
shutdown=true attribute
slaveHost=hostname attribute
slavePort=portValue attribute
ssl=sslMode 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
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.builtin.iterations
derby.authentication.builtin.saltLength
derby.authentication.ldap.searchAuthDN
derby.authentication.ldap.searchAuthPW
derby.authentication.ldap.searchBase
derby.authentication.ldap.searchFilter
derby.authentication.native.passwordLifetimeMillis
derby.authentication.native.passwordLifetimeThreshold
derby.authentication.provider
derby.authentication.server
derby.connection.requireAuthentication
derby.database.classpath
derby.database.defaultConnectionMode
derby.database.forceDatabaseLock
derby.database.fullAccessUsers
derby.database.noAutoBoot
derby.database.propertiesOnly
derby.database.readOnlyAccessUsers
derby.database.sqlAuthorization
derby.infolog.append
derby.jdbc.xaTransactionTimeout
derby.language.logQueryPlan
derby.language.logStatementText
derby.language.sequence.preallocator
derby.language.statementCacheSize
derby.locks.deadlockTimeout
derby.locks.deadlockTrace
derby.locks.escalationThreshold
derby.locks.monitor
derby.locks.waitTimeout
derby.replication.logBufferSize
derby.replication.maxLogShippingInterval
derby.replication.minLogShippingInterval
derby.replication.verbose
derby.storage.indexStats.auto
derby.storage.indexStats.log
derby.storage.indexStats.trace
derby.storage.initialPages
derby.storage.minimumRecordSize
derby.storage.pageCacheSize
derby.storage.pageReservedSpace
derby.storage.pageSize
derby.storage.rowLocking
derby.storage.tempDirectory
derby.storage.useDefaultFilePermissions
derby.stream.error.extendedDiagSeverityLevel
derby.stream.error.field
derby.stream.error.file
derby.stream.error.logBootTrace
derby.stream.error.logSeverityLevel
derby.stream.error.method
derby.stream.error.rollingFile.count
derby.stream.error.rollingFile.limit
derby.stream.error.rollingFile.pattern
derby.stream.error.style
derby.system.bootAll
derby.system.durability
derby.system.home
derby.user.UserName
DataDictionaryVersion
Java EE compliance: Java Transaction API and javax.sql interfaces
The JTA API
Recovered global transactions
XAConnections, user names and passwords
XA transactions and deferred constraints
javax.sql: JDBC interfaces
Derby API
Stand-alone tools and utilities
JDBC implementation classes
JDBC drivers
DataSource classes
Miscellaneous utilities and interfaces
Supported locales
Derby limitations
Limitations for database values
DATE, TIME, and TIMESTAMP limitations
Limitations on identifier length
Numeric limitations
String limitations
XML limitations
Trademarks