Contents

Copyright

About This Document

• Purpose of This Document
• Audience
• How This Document Is Organized

SQL Language Reference

• Capitalization and Special Characters
• SQL Identifiers
  • Rules for SQL92Identifiers
  • SQL92Identifier
    • Qualifying Dictionary Objects
  • schema-Name
  • table-Name
  • new-table-Name
  • view-Name
  • correlation-Name
  • column-Name
  • Simple-column-Name
  • index-Name
  • constraint-Name
  • cursor-Name
  • TriggerName
  • AuthorizationIdentifier
• Statements
  • Interaction with the Dependency System
  • ALTER TABLE statement
    • ALTER TABLE
  • CREATE Statements
    • CREATE FUNCTION Statement
    • CREATE INDEX statement
    • CREATE PROCEDURE Statement
    • CREATE SCHEMA statement
    • CREATE TABLE statement
    • CREATE TRIGGER statement
    • CREATE VIEW statement
  • DROP Statements
    • DROP FUNCTION
    • DROP INDEX statement
    • DROP PROCEDURE
    • DROP SCHEMA statement
    • DROP TABLE statement
    • DROP TRIGGER statement
    • DROP VIEW statement
  • RENAME Statements
    • RENAME INDEX statement
    • RENAME TABLE statement
  • SET Statements
    • SET SCHEMA statement
    • SET CURRENT ISOLATION statement
  • CALL (PROCEDURE)
  • CONSTRAINT clause
  • DECLARE GLOBAL TEMPORARY TABLE statement
    • Data-type
    • ON COMMIT
    • NOT LOGGED
  • 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
  • TableSubquery
  • UPDATE statement
  • VALUES expression
  • WHERE clause
  • WHERE CURRENT OF clause
• Built-In Functions
  • Standard Built-In Functions
  • Aggregates (Set Functions)
  • ABS or ABSVAL
  • AVG
  • BIGINT
  • CAST
  • CHAR
  • LENGTH
  • Concatenation
  • NULLIF and CASE expressions
  • COUNT
  • COUNT(*)
  • CURRENT DATE
  • CURRENT_DATE
  • CURRENT ISOLATION
  • CURRENT SCHEMA
  • CURRENT TIME
  • CURRENT_TIME
  • CURRENT TIMESTAMP
  • CURRENT_TIMESTAMP
  • CURRENT_USER
  • DATE
  • DAY
  • DOUBLE
  • HOUR
  • IDENTITY_VAL_LOCAL
  • INTEGER
  • LOCATE
  • LCASE or LOWER
  • LTRIM
  • MAX
  • MIN
  • MINUTE
  • MOD
  • MONTH
  • RTRIM
  • SECOND
  • SESSION_USER
  • SMALLINT
  • SQRT
  • SUBSTR
  • SUM
  • TIME
  • TIMESTAMP
  • UCASE or UPPER
  • USER
  • VARCHAR
  • 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_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_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
• 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
• SYSCOLUMNS
• SYSCONGLOMERATES
• SYSCONSTRAINTS
• SYSDEPENDS
• SYSFILES
• SYSFOREIGNKEYS
• SYSKEYS
• SYSSCHEMAS
• SYSSTATISTICS
• SYSSTATEMENTS
• SYSTABLES
• SYSTRIGGERS
• SYSVIEWS

Derby Exception Messages and SQL States

• Understanding SQLExceptions
• SQLState and Error Message Reference
  • Common Exceptions

JDBC Reference

• Core JDBC java.sql Classes, Interfaces, and Methods
• java.sql.Driver
  • Embedded Databases
• java.sql.DriverManager.getConnection
  • Derby Database Connection URL Syntax
  • Embedded Databases
  • Additional Syntax
  • The Derby Database Connection URL Attributes
• 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.Statement functionality not supported
• java.sql.PreparedStatement
  • Streaming Columns
• java.sql.CallableStatement
  • CallableStatements and OUT Parameters
  • CallableStatements and INOUT Parameters
• java.sql.ResultSet
  • Streaming Columns
• java.sql.ResultSetMetaData
  • Source and updatability (all data types)
• java.sql.SQLException
• java.sql.SQLWarning
• java.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 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
  • Restrictions
• java.sql.Statement
  • Autogenerated Keys
• JDBC Escape Syntax
  • call
  • d
  • escape
  • fn
  • oj
  • t
  • ts

Database Connection URL Attributes

• bootPassword=key
• create=true
• databaseName=nameofDatabase
• dataEncryption=true
• encryptionProvider=providerName
• encryptionAlgorithm=algorithm
• territory=ll_CC
• logDevice=logDirectoryPath
• password=userPassword
• rollForwardRecoveryFrom=Path
• createFrom=Path
• restoreFrom=Path
• shutdown=true
• user=userName
• (no attributes)

J2EE Compliance: Java Transaction API and javax.sql Extensions

• J2EE Overview
  • JVM and Libraries for J2EE Features
• The JTA API
  • Notes on Product Behavior
    • Recovered Global Transactions
    • XAConnections and User Names and Passwords
• javax.sql: JDBC Extensions

Appendix A. Derby API

• Stand-Alone Tools and Utilities
• JDBC Implementation Classes
  • JDBC Driver
  • Data Source Classes
• Miscellaneous Utilities and Interfaces

Appendix B. Supported territories

Appendix C. Derby limitations

• Identifier length limitations
• Numeric limitations
• String limitations
• DATE, TIME, and TIMESTAMP limitations
• Database Manager limitations

Trademarks

Index