• 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
      • RoleName
    • Statements
      • Interaction with the dependency system
      • ALTER TABLE statement
      • CALL (PROCEDURE) statement
      • CREATE statements
        • CREATE FUNCTION statement
        • CREATE INDEX statement
        • CREATE PROCEDURE statement
        • CREATE ROLE 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 ROLE 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 ROLE 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
      • The result offset and fetch first clauses
      • WHERE clause
      • WHERE CURRENT OF clause
    • SQL expressions
      • SelectExpression
      • TableExpression
        • TableViewOrFunctionExpression
          • TableFunctionInvocation
      • 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
      • ATAN2 function
      • AVG function
      • BIGINT function
      • CASE expressions
      • CAST function
      • CEIL or CEILING function
      • CHAR 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
      • 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
    • 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_EMPTY_STATEMENT_CACHE 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_UTIL.SYSCS_SET_USER_ACCESS system procedure
      • SYSCS_UTIL.SYSCS_UPDATE_STATISTICS 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
  • Argument matching
  • 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
    • SYSROLES 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
    • 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
      • 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.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
    • JDBC Package for Connected Device Configuration/Foundation Profile (JSR 169)
    • 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
      • java.sql.SQLXML interface
    • 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
    • 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.ldap.searchAuthDN
      • derby.authentication.ldap.searchAuthPW
      • derby.authentication.ldap.searchBase
      • derby.authentication.ldap.searchFilter
      • derby.authentication.provider
      • derby.authentication.server
      • derby.connection.requireAuthentication
      • 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.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.initialPages
      • derby.storage.minimumRecordSize
      • derby.storage.pageCacheSize
      • derby.storage.pageReservedSpace
      • derby.storage.pageSize
      • derby.storage.rowLocking
      • derby.storage.tempDirectory
      • derby.stream.error.field
      • derby.stream.error.file
      • derby.stream.error.method
      • derby.stream.error.logSeverityLevel
      • derby.system.bootAll
      • derby.system.durability
      • derby.system.home
      • derby.user.UserName
        • Caching user DNs
  • 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