Copyright
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
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
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
RENAME statements
RENAME INDEX statement
RENAME TABLE statement
REVOKE statement
SET statements
SET SCHEMA statement
SET CURRENT ISOLATION statement
CALL (PROCEDURE)
CONSTRAINT clause
Column-level-constraint
Table-level constraint
References specification
searchCondition
DECLARE GLOBAL TEMPORARY TABLE statement
DELETE statement
FOR UPDATE clause
FROM clause
GROUP BY clause
HAVING clause
INNER JOIN
INSERT statement
JOIN operation
LEFT OUTER JOIN
LOCK TABLE statement
ORDER BY clause
Query
RIGHT OUTER JOIN
ScalarSubquery
SelectExpression
SELECT statement
TableExpression
TableOrViewExpression
TableSubquery
UPDATE statement
Value
VALUES expression
Value
WHERE clause
WHERE CURRENT OF clause
Built-in functions
Standard built-in functions
Aggregates (set functions)
ABS or ABSVAL
ACOS function
ASIN function
ATAN function
AVG
BIGINT
CAST
CEIL or CEILING function
CHAR
Concatenation
COS function
COUNT
COUNT(*)
CURRENT DATE
CURRENT_DATE
CURRENT ISOLATION
CURRENT SCHEMA
CURRENT TIME
CURRENT_TIME
CURRENT TIMESTAMP
CURRENT_TIMESTAMP
CURRENT_USER
DATE
DAY
DEGREES function
DOUBLE
EXP function
FLOOR function
HOUR
IDENTITY_VAL_LOCAL
INTEGER
LCASE or LOWER
LENGTH
LN or LOG function
LOG10 function
LOCATE
LTRIM
MAX
MIN
MINUTE
MOD
MONTH
NULLIF and CASE expressions
PI function
RADIANS function
RTRIM
SECOND
SESSION_USER
SIN function
SMALLINT
SQRT
SUBSTR
TAN function
SUM
TIME
TIMESTAMP
UCASE or UPPER
USER
VARCHAR
XMLEXISTS operator
XMLPARSE operator
XMLQUERY operator
XMLSERIALIZE operator
YEAR
Built-in system functions
SYSCS_UTIL.SYSCS_CHECK_TABLE
SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS
SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY
Built-in system procedures
SYSCS_UTIL.SYSCS_COMPRESS_TABLE
SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE
SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS
SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING
SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY
SYSCS_UTIL.SYSCS_FREEZE_DATABASE
SYSCS_UTIL.SYSCS_UNFREEZE_DATABASE
SYSCS_UTIL.SYSCS_CHECKPOINT_DATABASE
SYSCS_UTIL.SYSCS_BACKUP_DATABASE
SYSCS_UTIL.SYSCS_BACKUP_DATABASE_NOWAIT
SYSCS_UTIL.SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE
SYSCS_UTIL.SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE_NOWAIT
SYSCS_UTIL.SYSCS_DISABLE_LOG_ARCHIVE_MODE
SYSCS_UTIL.SYSCS_EXPORT_TABLE
SYSCS_UTIL.SYSCS_EXPORT_QUERY
SYSCS_UTIL.SYSCS_IMPORT_TABLE
SYSCS_UTIL.SYSCS_IMPORT_DATA
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
BLOB
CHAR
CHAR FOR BIT DATA
CLOB
DATE
DECIMAL
DOUBLE
DOUBLE PRECISION
FLOAT
INTEGER
LONG VARCHAR
LONG VARCHAR FOR BIT DATA
NUMERIC
REAL
SMALLINT
TIME
TIMESTAMP
VARCHAR
VARCHAR FOR BIT DATA
XML data type
SQL expressions
Expression precedence
Example
Boolean expression
Dynamic parameters
Dynamic parameters example
Where dynamic parameters are allowed
SQL reserved words
Derby support for SQL-92 features
Derby System Tables
SYSALIASES
SYSCHECKS
SYSCOLPERMS
SYSCOLUMNS
SYSCONGLOMERATES
SYSCONSTRAINTS
SYSDEPENDS
SYSFILES
SYSFOREIGNKEYS
SYSKEYS
SYSROUTINEPERMS
SYSSCHEMAS
SYSSTATISTICS
SYSSTATEMENTS
SYSTABLEPERMS
SYSTABLES
SYSTRIGGERS
SYSVIEWS
Derby exception messages and SQL states
SQL error messages and exceptions
JDBC Reference
Core JDBC java.sql Classes, Interfaces, and Methods
java.sql.Driver
java.sql.DriverManager.getConnection
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.Driver.getPropertyInfo
java.sql.Connection
java.sql.Connection.setTransactionIsolation
java.sql.Connection.setReadOnly
java.sql.Connection.isReadOnly
Connection functionality not supported
java.sql.DatabaseMetaData
DatabaseMetaData result sets
getProcedureColumns
Parameters to getProcedureColumns
Columns in the ResultSet returned by getProcedureColumns
DatabaseMetaData functionality not supported
java.sql.Statement
ResultSet objects
java.sql.CallableStatement
CallableStatements and OUT Parameters
CallableStatements and INOUT Parameters
java.sql.SQLException
java.sql.PreparedStatement
Prepared statements and streaming columns
java.sql.ResultSet
ResultSets and streaming columns
java.sql.ResultSetMetaData
java.sql.SQLWarning
java.sql.SQLXML
Mapping of java.sql.Types to SQL types
java.sql.Blob and java.sql.Clob
Notes
java.sql.Connection
java.sql.ResultSet
java.sql.Statement
java.sql.PreparedStatement
java.sql.CallableStatement
java.sql.DatabaseMetaData
java.sql.ResultSetMetaData
java.sql.BatchUpdateException
JDBC Package for Connected Device Configuration/Foundation Profile (JSR169)
JDBC 3.0-only features
java.sql.Connection
java.sql.DatabaseMetaData
java.sql.ParameterMetaData
java.sql.PreparedStatement
java.sql.Savepoint
Setting and rolling back to a savepoint
Releasing a savepoint
Rules for savepoints
Restrictions on savepoints
java.sql.Statement
Autogenerated keys
JDBC 4.0-only features
Refined subclasses of SQLException
java.sql.Connection
java.sql.DatabaseMetaData
java.sql.Statement
javax.sql.DataSource
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
create=true
createFrom=Path
databaseName=nameofDatabase
dataEncryption=true
encryptionKey=<key>
encryptionProvider=providerName
encryptionAlgorithm=algorithm
logDevice=logDirectoryPath
newEncryptionKey= <key>
newBootPassword = <new password>
password=userPassword
restoreFrom=Path
rollForwardRecoveryFrom=Path
shutdown=true
territory=ll_CC
user=userName
(no attributes)
J2EE Compliance: Java Transaction API and javax.sql Extensions
JVM and libraries for J2EE features
The JTA API
Notes on Product Behavior
Recovered Global Transactions
XAConnections, user names and passwords
javax.sql: JDBC Extensions
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