• Copyright
  • License
  • Relationship between Java DB and Derby
  • About this guide
    • Purpose of this document
    • Audience
    • How this guide is organized
  • SQL language reference
    • Capitalization and special characters
    • SQL identifiers
      • Rules for SQL92 identifiers
      • SQL92Identifier
        • Qualifying dictionary objects
      • column-Name
      • correlation-Name
      • new-table-Name
      • schemaName
      • Simple-column-Name
      • synonym-Name
      • table-Name
      • view-Name
      • index-Name
      • constraint-Name
      • cursor-Name
      • TriggerName
      • AuthorizationIdentifier
    • Statements
      • Interaction with the dependency system
      • ALTER TABLE statement
      • CALL (PROCEDURE) statement
      • CREATE statements
        • CREATE FUNCTION statement
        • CREATE INDEX statement
        • CREATE PROCEDURE statement
        • CREATE SCHEMA statement
        • CREATE SYNONYM statement
        • CREATE TABLE statement
        • CREATE TRIGGER statement
        • CREATE VIEW statement
      • DECLARE GLOBAL TEMPORARY TABLE statement
      • DELETE statement
      • DROP statements
        • DROP FUNCTION statement
        • DROP INDEX statement
        • DROP PROCEDURE statement
        • DROP SCHEMA statement
        • DROP SYNONYM statement
        • DROP TABLE statement
        • DROP TRIGGER statement
        • DROP VIEW statement
      • GRANT statement
      • INSERT statement
      • LOCK TABLE statement
      • RENAME statements
        • RENAME COLUMN statement
        • RENAME INDEX statement
        • RENAME TABLE statement
      • REVOKE statement
      • SET statements
        • SET ISOLATION statement
        • SET SCHEMA statement
      • SELECT statement
      • UPDATE statement
    • SQL clauses
      • CONSTRAINT clause
        • Column-level-constraint
        • Table-level constraint
        • References specification
        • searchCondition
      • FOR UPDATE clause
      • FROM clause
      • GROUP BY clause
      • HAVING clause
      • ORDER BY clause
      • WHERE clause
      • WHERE CURRENT OF clause
    • SQL expressions
      • SelectExpression
      • TableExpression
        • TableOrViewExpression
      • VALUES expression
      • Expression precedence
      • Boolean expressions
      • Dynamic parameters
        • Dynamic parameters example
        • Where dynamic parameters are allowed
    • JOIN operations
      • INNER JOIN operation
      • LEFT OUTER JOIN operation
      • RIGHT OUTER 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
      • AVG function
      • BIGINT function
      • CASE expressions
      • CAST function
      • CEIL or CEILING function
      • CHAR function
      • Concatenation operator
      • COS function
      • COUNT function
      • COUNT(*) function
      • CURRENT DATE function
      • CURRENT_DATE function
      • CURRENT ISOLATION 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
      • RTRIM function
      • SECOND function
      • SESSION_USER function
      • SIN function
      • SMALLINT function
      • SQRT function
      • SUBSTR function
      • SUM function
      • TAN 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
    • Built-in system procedures
      • SYSCS_UTIL.SYSCS_BACKUP_DATABASE system procedure
      • SYSCS_UTIL.SYSCS_BACKUP_DATABASE_NOWAIT 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_CHECKPOINT_DATABASE system procedure
      • SYSCS_UTIL.SYSCS_COMPRESS_TABLE system procedure
      • SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE system procedure
      • SYSCS_UTIL.SYSCS_DISABLE_LOG_ARCHIVE_MODE system procedure
      • SYSCS_UTIL.SYSCS_EXPORT_TABLE system procedure
      • SYSCS_UTIL.SYSCS_EXPORT_TABLE_LOBS_TO_EXTFILE system procedure
      • SYSCS_UTIL.SYSCS_EXPORT_QUERY system procedure
      • SYSCS_UTIL.SYSCS_EXPORT_QUERY_LOBS_TO_EXTFILE 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_FREEZE_DATABASE system procedure
      • SYSCS_UTIL.SYSCS_UNFREEZE_DATABASE system procedure
      • SYSCS_UTIL.SYSCS_RELOAD_SECURITY_POLICY 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_DIAG diagnostic tables and functions
    • 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
      • 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
      • VARCHAR data type
      • VARCHAR FOR BIT DATA data type
      • XML data type
  • SQL reserved words
  • Derby support for SQL-92 features
  • 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
    • SYSROUTINEPERMS system table
    • SYSSCHEMAS system table
    • SYSSTATISTICS system table
    • SYSSTATEMENTS system table
    • SYSTABLEPERMS system table
    • SYSTABLES system table
    • SYSTRIGGERS system table
    • SYSVIEWS system table
  • Derby exception messages and SQL states
    • SQL error messages and exceptions
  • JDBC reference
    • Core JDBC java.sql classes, interfaces, and methods
    • 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
      • java.sql.DatabaseMetaData.getProcedureColumns method
      • Parameters to getProcedureColumns
      • Columns in the ResultSet returned by getProcedureColumns
      • java.sql.DatabaseMetaData.getBestRowIdentifier method
    • java.sql.Statement interface
      • ResultSet objects
    • java.sql.CallableStatement interface
      • CallableStatements and OUT Parameters
      • CallableStatements and INOUT Parameters
    • java.sql.SQLException class
    • java.sql.PreparedStatement interface
      • Prepared statements and streaming columns
    • java.sql.ResultSet interface
      • ResultSets and streaming columns
    • java.sql.ResultSetMetaData interface
    • java.sql.SQLWarning class
    • java.sql.SQLXML 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
    • JDBC 2.0 features
      • java.sql.CallableStatement interface: supported JDBC 2.0 methods
      • java.sql.Connection interface: supported JDBC 2.0 methods
      • java.sql.DatabaseMetaData interface: supported JDBC 2.0 methods
      • java.sql.PreparedStatement interface: supported JDBC 2.0 methods
      • java.sql.ResultSet interface: supported JDBC 2.0 methods
      • java.sql.ResultSetMetaData interface: supported JDBC 2.0 methods
      • java.sql.Statement interface: supported JDBC 2.0 methods
    • java.sql.BatchUpdateException class
    • JDBC Package for Connected Device Configuration/Foundation Profile (JSR169)
    • JDBC 3.0 features
      • java.sql.Connection interface: supported JDBC 3.0 methods
      • java.sql.DatabaseMetaData interface: supported JDBC 3.0 methods
      • java.sql.ParameterMetaData interface: supported JDBC 3.0 methods
      • java.sql.PreparedStatement interface: supported JDBC 3.0 methods
      • java.sql.Savepoint interface
        • Setting and rolling back to a savepoint
        • Releasing a savepoint
        • Rules for savepoints
        • Restrictions on savepoints
      • java.sql.Statement interface: supported JDBC 3.0 methods
        • Autogenerated keys
    • JDBC 4.0-only features
      • Refined subclasses of SQLException
      • java.sql.Connection interface: JDBC 4.0 features
      • java.sql.DatabaseMetaData interface: JDBC 4.0 features
      • java.sql.Statement interface: JDBC 4.0 features
      • javax.sql.DataSource interface: JDBC 4.0 features
    • JDBC escape syntax
      • JDBC escape keyword for call statements
      • JDBC escape syntax
      • JDBC escape syntax for LIKE 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
    • encryptionKey=key attribute
    • encryptionProvider=providerName attribute
    • encryptionAlgorithm=algorithm 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
    • territory=ll_CC attribute
    • user=userName attribute
    • ssl=sslMode attribute
    • Creating a connection without specifying attributes
  • J2EE Compliance: Java Transaction API and javax.sql Interfaces
    • The JTA API
      • Notes on Product Behavior
        • Recovered Global Transactions
        • XAConnections, user names and passwords
    • javax.sql: JDBC Interfaces
  • Derby API
    • Stand-alone tools and utilities
    • JDBC implementation classes
      • JDBC driver
      • Data Source Classes
    • Miscellaneous utilities and interfaces
  • Supported territories
  • Derby limitations
    • Limitations for database manager values
    • DATE, TIME, and TIMESTAMP limitations
    • Limitations on identifier length
    • Numeric limitations
    • String limitations
    • XML limitations
  • Trademarks