Java DB Reference Manual - Oracle

[Pages:379]Java DB Reference Manual

Version 10.6

Derby Document build: April 7, 2014, 5:20:21 PM (EDT)

Version 10.6 Java DB Reference Manual

Contents

Copyright..............................................................................................................................10

License................................................................................................................................. 11

Relationship between Java DB and Apache Derby.........................................................15

About this guide..................................................................................................................16 Purpose of this document...................................................................................... 16 Audience................................................................................................................... 16 How this guide is organized...................................................................................16

SQL language reference.....................................................................................................18 Capitalization and special characters....................................................................18 SQL identifiers ........................................................................................................ 18 Rules for SQL92 identifiers................................................................................. 19 SQL92Identifier.................................................................................................... 19 column-Name.......................................................................................................20 correlation-Name................................................................................................. 20 new-table-Name...................................................................................................21 schemaName....................................................................................................... 21 Simple-column-Name.......................................................................................... 21 synonym-Name.................................................................................................... 21 table-Name.......................................................................................................... 22 view-Name........................................................................................................... 22 index-Name..........................................................................................................22 constraint-Name...................................................................................................22 cursor-Name........................................................................................................ 23 TriggerName........................................................................................................ 23 AuthorizationIdentifier.......................................................................................... 23 RoleName............................................................................................................ 23 Statements................................................................................................................ 24 Interaction with the dependency system............................................................. 24 ALTER TABLE statement....................................................................................25 CALL (PROCEDURE) statement........................................................................ 29 CREATE statements........................................................................................... 30 DECLARE GLOBAL TEMPORARY TABLE statement....................................... 50 DELETE statement..............................................................................................53 DROP statements................................................................................................54 GRANT statement .............................................................................................. 57 INSERT statement...............................................................................................60 LOCK TABLE statement..................................................................................... 61 RENAME statements...........................................................................................62 REVOKE statement ............................................................................................64 SET statements................................................................................................... 68 SELECT statement..............................................................................................71 UPDATE statement............................................................................................. 72 SQL clauses............................................................................................................. 74 CONSTRAINT clause.......................................................................................... 74 FOR UPDATE clause..........................................................................................80 FROM clause.......................................................................................................80 GROUP BY clause.............................................................................................. 81 HAVING clause................................................................................................... 82 ORDER BY clause.............................................................................................. 82 The result offset and fetch first clauses.............................................................. 84

i

Version 10.6 Java DB Reference Manual

USING clause......................................................................................................85 WHERE clause....................................................................................................85 WHERE CURRENT OF clause...........................................................................86 SQL expressions......................................................................................................86 SelectExpression................................................................................................. 89 TableExpression.................................................................................................. 92 NEXT VALUE FOR expression...........................................................................93 VALUES expression............................................................................................ 94 Expression precedence....................................................................................... 95 Boolean expressions........................................................................................... 95 Dynamic parameters........................................................................................... 98 JOIN operations..................................................................................................... 100 INNER JOIN operation...................................................................................... 101 LEFT OUTER JOIN operation...........................................................................102 RIGHT OUTER JOIN operation........................................................................ 103 CROSS JOIN operation.................................................................................... 104 NATURAL JOIN operation................................................................................ 105 SQL queries............................................................................................................ 105 Query................................................................................................................. 105 ScalarSubquery................................................................................................. 107 TableSubquery...................................................................................................108 Built-in functions....................................................................................................109 Standard built-in functions.................................................................................109 Aggregates (set functions)................................................................................ 110 ABS or ABSVAL function.................................................................................. 111 ACOS function...................................................................................................111 ASIN function.....................................................................................................111 ATAN function................................................................................................... 112 ATAN2 function................................................................................................. 112 AVG function..................................................................................................... 112 BIGINT function................................................................................................. 113 CASE expressions.............................................................................................114 CAST function................................................................................................... 114 CEIL or CEILING function................................................................................. 117 CHAR function...................................................................................................117 COALESCE function......................................................................................... 119 Concatenation operator..................................................................................... 120 COS function..................................................................................................... 121 COSH function...................................................................................................121 COT function..................................................................................................... 121 COUNT function................................................................................................ 121 COUNT(*) function............................................................................................ 122 CURRENT DATE function.................................................................................122 CURRENT_DATE function................................................................................ 122 CURRENT ISOLATION function....................................................................... 122 CURRENT_ROLE function................................................................................122 CURRENT SCHEMA function........................................................................... 123 CURRENT TIME function..................................................................................123 CURRENT_TIME function................................................................................. 123 CURRENT TIMESTAMP function..................................................................... 123 CURRENT_TIMESTAMP function.....................................................................124 CURRENT_USER function................................................................................124 DATE function................................................................................................... 124 DAY function......................................................................................................125 DEGREES function........................................................................................... 125 DOUBLE function.............................................................................................. 125

ii

Version 10.6 Java DB Reference Manual

EXP function......................................................................................................126 FLOOR function.................................................................................................126 HOUR function.................................................................................................. 126 IDENTITY_VAL_LOCAL function...................................................................... 127 INTEGER function............................................................................................. 128 LCASE or LOWER function.............................................................................. 129 LENGTH function.............................................................................................. 129 LN or LOG function........................................................................................... 129 LOG10 function................................................................................................. 130 LOCATE function...............................................................................................130 LTRIM function.................................................................................................. 131 MAX function..................................................................................................... 131 MIN function...................................................................................................... 132 MINUTE function............................................................................................... 132 MOD function.....................................................................................................132 MONTH function................................................................................................133 NULLIF expressions.......................................................................................... 133 PI function..........................................................................................................133 RADIANS function............................................................................................. 134 RANDOM function............................................................................................. 134 RAND function...................................................................................................134 ROW_NUMBER function...................................................................................134 RTRIM function..................................................................................................135 SECOND function..............................................................................................135 SESSION_USER function................................................................................. 135 SIGN function.................................................................................................... 136 SIN function....................................................................................................... 136 SINH function.................................................................................................... 136 SMALLINT function........................................................................................... 136 SQRT function................................................................................................... 137 SUBSTR function.............................................................................................. 137 SUM function..................................................................................................... 138 TAN function......................................................................................................139 TANH function................................................................................................... 139 TIME function.................................................................................................... 139 TIMESTAMP function........................................................................................ 139 TRIM function.................................................................................................... 140 UCASE or UPPER function...............................................................................141 USER function................................................................................................... 142 VARCHAR function........................................................................................... 142 XMLEXISTS operator........................................................................................ 142 XMLPARSE operator.........................................................................................144 XMLQUERY operator........................................................................................ 145 XMLSERIALIZE operator.................................................................................. 146 YEAR function................................................................................................... 148 Built-in system functions...................................................................................... 148 SYSCS_UTIL.SYSCS_CHECK_TABLE system function..................................148 SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY system function......... 148 SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS system function............. 149 SYSCS_UTIL.SYSCS_GET_USER_ACCESS system function....................... 149 SYSCS_UTIL.SYSCS_GET_XPLAIN_MODE system function.........................150 SYSCS_UTIL.SYSCS_GET_XPLAIN_SCHEMA system function.................... 150 Built-in system procedures.................................................................................. 150 SYSCS_UTIL.SYSCS_BACKUP_DATABASE system procedure.................... 150 SYSCS_UTIL.SYSCS_BACKUP_DATABASE_NOWAIT system procedure....151

iii

Version 10.6 Java DB Reference Manual

SYSCS_UTIL.SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE system procedure.............................................................................................. 152 SYSCS_UTIL.SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE_NOWAIT system procedure.............................................................................................. 152 SYSCS_UTIL.SYSCS_EMPTY_STATEMENT_CACHE system procedure..... 153 SYSCS_UTIL.SYSCS_CHECKPOINT_DATABASE system procedure........... 154 SYSCS_UTIL.SYSCS_COMPRESS_TABLE system procedure...................... 154 SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE system procedure.....155 SYSCS_UTIL.SYSCS_DISABLE_LOG_ARCHIVE_MODE system procedure........................................................................................................... 157 SYSCS_UTIL.SYSCS_EXPORT_TABLE system procedure............................157 SYSCS_UTIL.SYSCS_EXPORT_TABLE_LOBS_TO_EXTFILE system procedure........................................................................................................... 159 SYSCS_UTIL.SYSCS_EXPORT_QUERY system procedure.......................... 160 SYSCS_UTIL.SYSCS_EXPORT_QUERY_LOBS_TO_EXTFILE system procedure........................................................................................................... 161 SYSCS_UTIL.SYSCS_IMPORT_DATA system procedure.............................. 162 SYSCS_UTIL.SYSCS_IMPORT_DATA_LOBS_FROM_EXTFILE system procedure........................................................................................................... 164 SYSCS_UTIL.SYSCS_IMPORT_TABLE system procedure.............................165 SYSCS_UTIL.SYSCS_IMPORT_TABLE_LOBS_FROM_EXTFILE system procedure........................................................................................................... 167 SYSCS_UTIL.SYSCS_FREEZE_DATABASE system procedure.....................168 SYSCS_UTIL.SYSCS_UNFREEZE_DATABASE system procedure................169 SYSCS_UTIL.SYSCS_RELOAD_SECURITY_POLICY system procedure...... 169 SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY system procedure...... 169 SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS system procedure.......... 170 SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING system procedure............170 SYSCS_UTIL.SYSCS_SET_XPLAIN_SCHEMA system procedure................. 171 SYSCS_UTIL.SYSCS_SET_XPLAIN_MODE system procedure......................171 SYSCS_UTIL.SYSCS_SET_USER_ACCESS system procedure.................... 172 SYSCS_UTIL.SYSCS_UPDATE_STATISTICS system procedure...................172 SYSCS_DIAG diagnostic tables and functions...................................................173 Data types............................................................................................................... 177 Built-In type overview........................................................................................ 177 Numeric types....................................................................................................178 Data type assignments and comparison, sorting, and ordering........................ 180 BIGINT data type.............................................................................................. 184 BLOB data type.................................................................................................185 CHAR data type................................................................................................ 185 CHAR FOR BIT DATA data type...................................................................... 186 CLOB data type.................................................................................................187 DATE data type................................................................................................. 188 DECIMAL data type...........................................................................................189 DOUBLE data type............................................................................................189 DOUBLE PRECISION data type.......................................................................190 FLOAT data type............................................................................................... 190 INTEGER data type...........................................................................................191 LONG VARCHAR data type..............................................................................191 LONG VARCHAR FOR BIT DATA data type................................................... 192 NUMERIC data type..........................................................................................192 REAL data type................................................................................................. 192 SMALLINT data type......................................................................................... 193 TIME data type..................................................................................................193 TIMESTAMP data type......................................................................................194 User-defined types............................................................................................ 194

iv

Version 10.6 Java DB Reference Manual

VARCHAR data type......................................................................................... 195 VARCHAR FOR BIT DATA data type...............................................................195 XML data type................................................................................................... 196

Argument matching.......................................................................................................... 197

SQL reserved words......................................................................................................... 199

Derby support for SQL-92 features ............................................................................... 203

Derby system tables......................................................................................................... 211 SYSALIASES system table................................................................................... 211 SYSCHECKS system table....................................................................................211 SYSCOLPERMS system table...............................................................................212 SYSCOLUMNS system table.................................................................................213 SYSCONGLOMERATES system table..................................................................214 SYSCONSTRAINTS system table......................................................................... 215 SYSDEPENDS system table..................................................................................215 SYSFILES system table.........................................................................................216 SYSFOREIGNKEYS system table.........................................................................216 SYSKEYS system table......................................................................................... 217 SYSPERMS system table...................................................................................... 217 SYSROLES system table...................................................................................... 218 SYSROUTINEPERMS system table...................................................................... 219 SYSSCHEMAS system table................................................................................. 220 SYSSEQUENCES system table............................................................................ 220 SYSSTATISTICS system table.............................................................................. 221 SYSSTATEMENTS system table...........................................................................221 SYSTABLEPERMS system table.......................................................................... 222 SYSTABLES system table.................................................................................... 223 SYSTRIGGERS system table................................................................................ 224 SYSVIEWS system table....................................................................................... 225

XPLAIN style tables.......................................................................................................... 226 SYSXPLAIN_STATEMENTS system table........................................................... 226 SYSXPLAIN_STATEMENT_TIMINGS system table............................................. 228 SYSXPLAIN_RESULTSETS system table............................................................ 230 SYSXPLAIN_RESULTSET_TIMINGS system table..............................................235 SYSXPLAIN_SCAN_PROPS system table........................................................... 238 SYSXPLAIN_SORT_PROPS system table........................................................... 241

Derby exception messages and SQL states.................................................................. 245 SQL error messages and exceptions.................................................................. 245

JDBC reference................................................................................................................. 289 java.sql.Driver interface.........................................................................................289 java.sql.Driver.getPropertyInfo method............................................................. 290 java.sql.DriverManager.getConnection method..................................................290 Derby database connection URL syntax...........................................................291 Syntax of database connection URLs for applications with embedded databases ........................................................................................................................... 291 Additional SQL syntax....................................................................................... 291 Attributes of the Derby database connection URL ...........................................292 java.sql.Connection interface............................................................................... 292 java.sql.Connection.setTransactionIsolation method........................................ 293 java.sql.Connection.setReadOnly method.........................................................293 java.sql.Connection.isReadOnly method...........................................................293 Connection functionality not supported............................................................. 293 java.sql.DatabaseMetaData interface................................................................... 293

v

Version 10.6 Java DB Reference Manual

DatabaseMetaData result sets.......................................................................... 294 java.sql.DatabaseMetaData.getProcedureColumns method............................. 294 Parameters to getProcedureColumns............................................................... 294 Columns in the ResultSet returned by getProcedureColumns.......................... 294 java.sql.DatabaseMetaData.getBestRowIdentifier method................................295 java.sql.Statement interface..................................................................................296 ResultSet objects ............................................................................................. 296 Autogenerated keys...........................................................................................297 java.sql.CallableStatement interface....................................................................298 CallableStatements and OUT Parameters .......................................................298 CallableStatements and INOUT Parameters ................................................... 298 java.sql.PreparedStatement interface.................................................................. 299 Prepared statements and streaming columns ..................................................299 java.sql.ResultSet interface.................................................................................. 301 ResultSets and streaming columns ................................................................. 302 java.sql.ResultSetMetaData interface.................................................................. 302 java.sql.SQLException class................................................................................ 302 java.sql.SQLWarning class................................................................................... 303 java.sql.Savepoint interface..................................................................................303 Mapping of java.sql.Types to SQL types............................................................ 303 Mapping of java.sql.Blob and java.sql.Clob interfaces...................................... 304 JDBC Package for Connected Device Configuration/Foundation Profile (JSR 169).......................................................................................................................... 306 JDBC 4.0-only features ........................................................................................ 307 Refined subclasses of SQLException............................................................... 307 java.sql.Connection interface: JDBC 4.0 features.............................................308 java.sql.DatabaseMetaData interface: JDBC 4.0 features................................ 308 java.sql.Statement interface: JDBC 4.0 features...............................................308 javax.sql.DataSource interface: JDBC 4.0 features.......................................... 308 java.sql.SQLXML interface................................................................................ 309 JDBC escape syntax ............................................................................................ 309 JDBC escape keyword for call statements....................................................... 309 JDBC escape syntax......................................................................................... 310 JDBC escape syntax for LIKE clauses............................................................. 310 JDBC escape syntax for fn keyword.................................................................310 JDBC escape syntax for outer joins..................................................................317 JDBC escape syntax for time formats.............................................................. 317 JDBC escape syntax for date formats.............................................................. 317 JDBC escape syntax for timestamp formats.....................................................318

Setting attributes for the database connection URL ....................................................319 bootPassword=key attribute................................................................................. 319 collation=collation attribute.................................................................................. 319 create=true attribute.............................................................................................. 320 createFrom=path attribute.................................................................................... 321 databaseName=nameofDatabase attribute..........................................................322 dataEncryption=true attribute...............................................................................322 drop=true attribute.................................................................................................323 encryptionKey=key attribute.................................................................................323 encryptionProvider=providerName attribute.......................................................324 encryptionAlgorithm=algorithm attribute............................................................ 324 failover=true attribute............................................................................................ 325 logDevice=logDirectoryPath attribute..................................................................326 newEncryptionKey=key attribute......................................................................... 326 newBootPassword=newPassword attribute........................................................326 password=userPassword attribute...................................................................... 327

vi

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download