Table Of Content

Derby Reference Manual Version 10.10 Derby Document build: March 27, 2014, 12:21:19 PM (PDT) Version 10.10 Derby Reference Manual Contents Copyright..............................................................................................................................11 License.................................................................................................................................12 About this guide..................................................................................................................16 Purpose of this document......................................................................................16 Audience...................................................................................................................16 How this guide is organized...................................................................................16 SQL syntax used in this manual............................................................................17 SQL language reference.....................................................................................................18 Capitalization and special characters....................................................................18 SQL identifiers ........................................................................................................18 Rules for SQL92 identifiers.................................................................................19 SQL92Identifier....................................................................................................19 aggregateName...................................................................................................20 authorizationIdentifier...........................................................................................20 columnName........................................................................................................20 constraintName....................................................................................................21 correlationName...................................................................................................21 cursorName.........................................................................................................21 functionName.......................................................................................................22 indexName...........................................................................................................22 newTableName....................................................................................................22 procedureName...................................................................................................23 roleName.............................................................................................................23 schemaName.......................................................................................................23 sequenceName....................................................................................................24 simpleColumnName.............................................................................................24 synonymName.....................................................................................................24 tableName............................................................................................................24 triggerName.........................................................................................................25 typeName.............................................................................................................25 viewName............................................................................................................25 Statements................................................................................................................25 Interaction with the dependency system.............................................................26 ALTER TABLE statement....................................................................................27 CALL (PROCEDURE) statement........................................................................32 CREATE statements...........................................................................................32 DECLARE GLOBAL TEMPORARY TABLE statement.......................................55 DELETE statement..............................................................................................58 DROP statements................................................................................................58 GRANT statement ..............................................................................................62 INSERT statement...............................................................................................65 LOCK TABLE statement.....................................................................................66 RENAME statements...........................................................................................68 REVOKE statement ............................................................................................69 SELECT statement..............................................................................................73 SET statements...................................................................................................75 TRUNCATE TABLE statement............................................................................77 UPDATE statement.............................................................................................78 SQL clauses.............................................................................................................79 CONSTRAINT clause..........................................................................................79 i Version 10.10 Derby Reference Manual EXTERNAL NAME clause...................................................................................85 FOR UPDATE clause..........................................................................................86 FROM clause.......................................................................................................87 GROUP BY clause..............................................................................................87 HAVING clause...................................................................................................88 WINDOW clause.................................................................................................89 ORDER BY clause..............................................................................................89 The result offset and fetch first clauses..............................................................90 USING clause......................................................................................................91 WHERE clause....................................................................................................92 WHERE CURRENT OF clause...........................................................................93 SQL expressions......................................................................................................93 selectExpression..................................................................................................96 tableExpression...................................................................................................99 NEXT VALUE FOR expression.........................................................................100 VALUES expression..........................................................................................101 Expression precedence.....................................................................................102 Boolean expressions.........................................................................................102 CASE expression..............................................................................................105 Dynamic parameters.........................................................................................105 JOIN operations.....................................................................................................108 INNER JOIN operation......................................................................................108 LEFT OUTER JOIN operation...........................................................................110 RIGHT OUTER JOIN operation........................................................................111 CROSS JOIN operation....................................................................................111 NATURAL JOIN operation................................................................................112 SQL queries............................................................................................................113 query..................................................................................................................113 scalarSubquery..................................................................................................115 tableSubquery....................................................................................................115 Built-in functions....................................................................................................116 Standard built-in functions.................................................................................117 Aggregates (set functions)................................................................................118 ABS or ABSVAL function..................................................................................119 ACOS function...................................................................................................119 ASIN function.....................................................................................................119 ATAN function...................................................................................................120 ATAN2 function.................................................................................................120 AVG function.....................................................................................................120 BIGINT function.................................................................................................121 CAST function...................................................................................................122 CEIL or CEILING function.................................................................................125 CHAR function...................................................................................................126 COALESCE function.........................................................................................127 Concatenation operator.....................................................................................128 COS function.....................................................................................................129 COSH function...................................................................................................129 COT function.....................................................................................................129 COUNT function................................................................................................129 COUNT(*) function............................................................................................130 CURRENT DATE function.................................................................................130 CURRENT_DATE function................................................................................130 CURRENT ISOLATION function.......................................................................131 CURRENT_ROLE function................................................................................131 CURRENT SCHEMA function...........................................................................131 CURRENT TIME function..................................................................................131 ii Version 10.10 Derby Reference Manual CURRENT_TIME function.................................................................................132 CURRENT TIMESTAMP function.....................................................................132 CURRENT_TIMESTAMP function.....................................................................132 CURRENT_USER function................................................................................132 DATE function...................................................................................................133 DAY function......................................................................................................133 DEGREES function...........................................................................................134 DOUBLE function..............................................................................................134 EXP function......................................................................................................135 FLOOR function.................................................................................................135 HOUR function..................................................................................................135 IDENTITY_VAL_LOCAL function......................................................................135 INTEGER function.............................................................................................137 LCASE or LOWER function..............................................................................137 LENGTH function..............................................................................................138 LN or LOG function...........................................................................................138 LOG10 function.................................................................................................138 LOCATE function...............................................................................................139 LTRIM function..................................................................................................139 MAX function.....................................................................................................140 MIN function......................................................................................................141 MINUTE function...............................................................................................141 MOD function.....................................................................................................142 MONTH function................................................................................................142 NULLIF function.................................................................................................142 PI function..........................................................................................................143 RADIANS function.............................................................................................143 RANDOM function.............................................................................................143 RAND function...................................................................................................143 ROW_NUMBER function...................................................................................143 RTRIM function..................................................................................................144 SECOND function..............................................................................................144 SESSION_USER function.................................................................................145 SIGN function....................................................................................................145 SIN function.......................................................................................................145 SINH function....................................................................................................146 SMALLINT function...........................................................................................146 SQRT function...................................................................................................146 SUBSTR function..............................................................................................147 SUM function.....................................................................................................148 TAN function......................................................................................................148 TANH function...................................................................................................148 TIME function....................................................................................................149 TIMESTAMP function........................................................................................149 TRIM function....................................................................................................150 UCASE or UPPER function...............................................................................151 USER function...................................................................................................151 VARCHAR function...........................................................................................152 XMLEXISTS operator........................................................................................152 XMLPARSE operator.........................................................................................153 XMLQUERY operator........................................................................................155 XMLSERIALIZE operator..................................................................................156 YEAR function...................................................................................................157 Built-in system functions......................................................................................158 SYSCS_UTIL.SYSCS_CHECK_TABLE system function..................................158 SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY system function.........159 iii Version 10.10 Derby Reference Manual SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS system function.............159 SYSCS_UTIL.SYSCS_GET_USER_ACCESS system function.......................159 SYSCS_UTIL.SYSCS_GET_XPLAIN_MODE system function.........................160 SYSCS_UTIL.SYSCS_GET_XPLAIN_SCHEMA system function....................160 SYSCS_UTIL.SYSCS_PEEK_AT_SEQUENCE system function.....................161 Built-in system procedures..................................................................................161 SYSCS_UTIL.SYSCS_BACKUP_DATABASE system procedure....................162 SYSCS_UTIL.SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE system procedure..............................................................................................162 SYSCS_UTIL.SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE_NOWAIT system procedure..............................................................................................163 SYSCS_UTIL.SYSCS_BACKUP_DATABASE_NOWAIT system procedure....164 SYSCS_UTIL.SYSCS_CHECKPOINT_DATABASE system procedure...........165 SYSCS_UTIL.SYSCS_COMPRESS_TABLE system procedure......................165 SYSCS_UTIL.SYSCS_CREATE_USER system procedure.............................167 SYSCS_UTIL.SYSCS_DISABLE_LOG_ARCHIVE_MODE system procedure...........................................................................................................168 SYSCS_UTIL.SYSCS_DROP_STATISTICS system procedure.......................169 SYSCS_UTIL.SYSCS_DROP_USER system procedure..................................169 SYSCS_UTIL.SYSCS_EMPTY_STATEMENT_CACHE system procedure.....170 SYSCS_UTIL.SYSCS_EXPORT_QUERY system procedure..........................171 SYSCS_UTIL.SYSCS_EXPORT_QUERY_LOBS_TO_EXTFILE system procedure...........................................................................................................172 SYSCS_UTIL.SYSCS_EXPORT_TABLE system procedure............................173 SYSCS_UTIL.SYSCS_EXPORT_TABLE_LOBS_TO_EXTFILE system procedure...........................................................................................................175 SYSCS_UTIL.SYSCS_FREEZE_DATABASE system procedure.....................176 SYSCS_UTIL.SYSCS_IMPORT_DATA system procedure..............................177 SYSCS_UTIL.SYSCS_IMPORT_DATA_LOBS_FROM_EXTFILE system procedure...........................................................................................................178 SYSCS_UTIL.SYSCS_IMPORT_TABLE system procedure.............................180 SYSCS_UTIL.SYSCS_IMPORT_TABLE_LOBS_FROM_EXTFILE system procedure...........................................................................................................182 SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE system procedure.....183 SYSCS_UTIL.SYSCS_INVALIDATE_STORED_STATEMENTS system procedure...........................................................................................................185 SYSCS_UTIL.SYSCS_MODIFY_PASSWORD system procedure...................186 SYSCS_UTIL.SYSCS_REGISTER_TOOL system procedure..........................186 SYSCS_UTIL.SYSCS_RELOAD_SECURITY_POLICY system procedure......188 SYSCS_UTIL.SYSCS_RESET_PASSWORD system procedure.....................188 SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY system procedure......189 SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS system procedure..........190 SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING system procedure............190 SYSCS_UTIL.SYSCS_SET_USER_ACCESS system procedure....................191 SYSCS_UTIL.SYSCS_SET_XPLAIN_MODE system procedure......................191 SYSCS_UTIL.SYSCS_SET_XPLAIN_SCHEMA system procedure.................192 SYSCS_UTIL.SYSCS_UNFREEZE_DATABASE system procedure................193 SYSCS_UTIL.SYSCS_UPDATE_STATISTICS system procedure...................193 System procedures for storing jar files in a database.......................................194 SYSCS_DIAG diagnostic tables and functions...................................................196 SYSCS_DIAG.CONTAINED_ROLES diagnostic table function........................197 SYSCS_DIAG.ERROR_LOG_READER diagnostic table function....................197 SYSCS_DIAG.ERROR_MESSAGES diagnostic table......................................198 SYSCS_DIAG.LOCK_TABLE diagnostic table.................................................199 SYSCS_DIAG.SPACE_TABLE diagnostic table function.................................200 SYSCS_DIAG.STATEMENT_CACHE diagnostic table....................................202 iv Version 10.10 Derby Reference Manual SYSCS_DIAG.STATEMENT_DURATION diagnostic table function.................203 SYSCS_DIAG.TRANSACTION_TABLE diagnostic table..................................204 Data types...............................................................................................................205 Built-In type overview........................................................................................205 Numeric types....................................................................................................206 Data type assignments and comparison, sorting, and ordering........................208 BIGINT data type..............................................................................................212 BLOB data type.................................................................................................213 BOOLEAN data type.........................................................................................214 CHAR data type................................................................................................215 CHAR FOR BIT DATA data type......................................................................215 CLOB data type.................................................................................................216 DATE data type.................................................................................................217 DECIMAL data type...........................................................................................217 DOUBLE data type............................................................................................218 DOUBLE PRECISION data type.......................................................................218 FLOAT data type...............................................................................................219 INTEGER data type...........................................................................................220 LONG VARCHAR data type..............................................................................220 LONG VARCHAR FOR BIT DATA data type...................................................221 NUMERIC data type..........................................................................................221 REAL data type.................................................................................................221 SMALLINT data type.........................................................................................222 TIME data type..................................................................................................223 TIMESTAMP data type......................................................................................223 User-defined types............................................................................................224 VARCHAR data type.........................................................................................224 VARCHAR FOR BIT DATA data type...............................................................225 XML data type...................................................................................................225 Argument matching..........................................................................................................227 SQL reserved words.........................................................................................................229 Derby support for SQL-92 features ...............................................................................233 Derby system tables.........................................................................................................241 SYSALIASES system table...................................................................................241 SYSCHECKS system table....................................................................................242 SYSCOLPERMS system table...............................................................................242 SYSCOLUMNS system table.................................................................................243 SYSCONGLOMERATES system table..................................................................245 SYSCONSTRAINTS system table.........................................................................245 SYSDEPENDS system table..................................................................................246 SYSFILES system table.........................................................................................247 SYSFOREIGNKEYS system table.........................................................................247 SYSKEYS system table.........................................................................................248 SYSPERMS system table......................................................................................248 SYSROLES system table......................................................................................250 SYSROUTINEPERMS system table......................................................................251 SYSSCHEMAS system table.................................................................................252 SYSSEQUENCES system table............................................................................253 SYSSTATEMENTS system table...........................................................................254 SYSSTATISTICS system table..............................................................................255 SYSTABLEPERMS system table..........................................................................256 SYSTABLES system table....................................................................................258 SYSTRIGGERS system table................................................................................259 SYSUSERS system table......................................................................................260 v Version 10.10 Derby Reference Manual SYSVIEWS system table.......................................................................................261 XPLAIN style tables..........................................................................................................262 SYSXPLAIN_STATEMENTS system table...........................................................262 SYSXPLAIN_STATEMENT_TIMINGS system table.............................................264 SYSXPLAIN_RESULTSETS system table............................................................266 SYSXPLAIN_RESULTSET_TIMINGS system table..............................................271 SYSXPLAIN_SCAN_PROPS system table...........................................................273 SYSXPLAIN_SORT_PROPS system table...........................................................277 Derby exception messages and SQL states..................................................................280 SQL error messages and exceptions..................................................................280 JDBC reference.................................................................................................................326 java.sql.Driver interface.........................................................................................326 java.sql.Driver.getPropertyInfo method.............................................................327 java.sql.DriverManager.getConnection method..................................................327 Derby database connection URL syntax...........................................................328 Syntax of database connection URLs for applications with embedded databases..........................................................................................................328 Additional SQL syntax.......................................................................................329 Attributes of the Derby database connection URL ...........................................329 java.sql.Connection interface...............................................................................329 java.sql.Connection.setTransactionIsolation method........................................330 java.sql.Connection.setReadOnly method.........................................................330 java.sql.Connection.isReadOnly method...........................................................331 Connection functionality not supported.............................................................331 java.sql.DatabaseMetaData interface...................................................................331 DatabaseMetaData result sets..........................................................................331 Columns in the ResultSets returned by getFunctionColumns and getProcedureColumns.......................................................................................331 java.sql.DatabaseMetaData.getBestRowIdentifier method................................332 java.sql.Statement interface..................................................................................333 ResultSet objects .............................................................................................333 Autogenerated keys...........................................................................................334 java.sql.CallableStatement interface....................................................................335 CallableStatements and OUT Parameters .......................................................335 CallableStatements and INOUT parameters ....................................................335 java.sql.PreparedStatement interface..................................................................336 Prepared statements and streaming columns ..................................................337 java.sql.ResultSet interface..................................................................................338 ResultSets and streaming columns .................................................................339 java.sql.ResultSetMetaData interface..................................................................340 java.sql.SQLException class................................................................................340 java.sql.SQLWarning class...................................................................................340 java.sql.Savepoint interface..................................................................................340 Mapping of java.sql.Types to SQL types............................................................340 Mapping of java.sql.Blob and java.sql.Clob interfaces......................................341 JDBC Package for Connected Device Configuration/Foundation Profile (JSR 169)..........................................................................................................................343 Features supported on all JDBC 4 releases.......................................................344 Refined subclasses of SQLException...............................................................345 java.sql.Connection interface: JDBC 4.0 features.............................................345 java.sql.DatabaseMetaData interface: JDBC 4.0 features................................345 java.sql.Statement interface: JDBC 4.0 features...............................................346 javax.sql.DataSource interface: JDBC 3 and JDBC 4.0 features......................346 java.sql.SQLXML interface................................................................................346 vi Version 10.10 Derby Reference Manual Features supported on JDBC 4.1 and above......................................................346 java.sql.Connection interface: JDBC 4.1 features.............................................347 javax.sql.DataSource interface: JDBC 4.1 features..........................................347 JDBC 4.2-only features.........................................................................................347 JDBC support for Java SE 8 Compact Profiles................................................347 java.sql.DatabaseMetaData interface: JDBC 4.2 features................................348 java.sql.SQLType interface................................................................................348 JDBC escape syntax ............................................................................................348 JDBC escape keyword for call statements.......................................................349 JDBC escape syntax for LIKE clauses.............................................................349 JDBC escape syntax for limit/offset clauses.....................................................350 JDBC escape syntax for fn keyword.................................................................350 JDBC escape syntax for outer joins..................................................................356 JDBC escape syntax for time formats..............................................................357 JDBC escape syntax for date formats..............................................................357 JDBC escape syntax for timestamp formats.....................................................358 Setting attributes for the database connection URL ....................................................359 bootPassword=key attribute.................................................................................359 collation=collation attribute..................................................................................360 create=true attribute..............................................................................................360 createFrom=path attribute....................................................................................361 databaseName=nameofDatabase attribute..........................................................362 dataEncryption=true attribute...............................................................................362 decryptDatabase=true attribute............................................................................363 deregister=false attribute......................................................................................363 drop=true attribute.................................................................................................365 encryptionKey=key attribute.................................................................................365 encryptionKeyLength=length attribute................................................................366 encryptionProvider=providerName attribute.......................................................366 encryptionAlgorithm=algorithm attribute............................................................367 failover=true attribute............................................................................................368 logDevice=logDirectoryPath attribute..................................................................368 newBootPassword=newPassword attribute........................................................369 newEncryptionKey=key attribute.........................................................................369 password=userPassword attribute......................................................................370 restoreFrom=path attribute...................................................................................370 retrieveMessageText=false attribute....................................................................370 rollForwardRecoveryFrom=path attribute...........................................................371 securityMechanism=value attribute.....................................................................371 shutdown=true attribute........................................................................................372 slaveHost=hostname attribute..............................................................................372 slavePort=portValue attribute...............................................................................373 ssl=sslMode attribute............................................................................................373 startMaster=true attribute.....................................................................................373 startSlave=true attribute........................................................................................374 stopMaster=true attribute......................................................................................375 stopSlave=true attribute........................................................................................375 territory=ll_CC attribute.........................................................................................376 traceDirectory=path attribute................................................................................377 traceFile=path attribute.........................................................................................378 traceFileAppend=true attribute.............................................................................378 traceLevel=value attribute.....................................................................................378 upgrade=true attribute...........................................................................................380 user=userName attribute.......................................................................................380 Creating a connection without specifying attributes.........................................381 vii Version 10.10 Derby Reference Manual Derby property reference.................................................................................................382 Scope of Derby properties....................................................................................382 Dynamic and static properties.............................................................................382 Derby properties....................................................................................................382 derby.authentication.builtin.algorithm................................................................385 derby.authentication.builtin.iterations.................................................................386 derby.authentication.builtin.saltLength...............................................................386 derby.authentication.ldap.searchAuthDN..........................................................387 derby.authentication.ldap.searchAuthPW..........................................................387 derby.authentication.ldap.searchBase...............................................................388 derby.authentication.ldap.searchFilter...............................................................388 derby.authentication.native.passwordLifetimeMillis...........................................389 derby.authentication.native.passwordLifetimeThreshold...................................390 derby.authentication.provider.............................................................................390 derby.authentication.server................................................................................392 derby.connection.requireAuthentication.............................................................393 derby.database.classpath..................................................................................394 derby.database.defaultConnectionMode...........................................................394 derby.database.forceDatabaseLock..................................................................395 derby.database.fullAccessUsers........................................................................396 derby.database.noAutoBoot..............................................................................396 derby.database.propertiesOnly..........................................................................397 derby.database.readOnlyAccessUsers..............................................................397 derby.database.sqlAuthorization........................................................................398 derby.infolog.append.........................................................................................399 derby.jdbc.xaTransactionTimeout......................................................................399 derby.language.logQueryPlan...........................................................................399 derby.language.logStatementText.....................................................................400 derby.language.sequence.preallocator..............................................................400 derby.language.statementCacheSize................................................................401 derby.locks.deadlockTimeout............................................................................401 derby.locks.deadlockTrace................................................................................402 derby.locks.escalationThreshold........................................................................402 derby.locks.monitor............................................................................................403 derby.locks.waitTimeout....................................................................................403 derby.replication.logBufferSize..........................................................................404 derby.replication.maxLogShippingInterval.........................................................405 derby.replication.minLogShippingInterval..........................................................405 derby.replication.verbose...................................................................................406 derby.storage.indexStats.auto...........................................................................406 derby.storage.indexStats.log.............................................................................406 derby.storage.indexStats.trace..........................................................................407 derby.storage.initialPages..................................................................................408 derby.storage.minimumRecordSize...................................................................409 derby.storage.pageCacheSize...........................................................................409 derby.storage.pageReservedSpace..................................................................410 derby.storage.pageSize.....................................................................................410 derby.storage.rowLocking..................................................................................411 derby.storage.tempDirectory..............................................................................412 derby.storage.useDefaultFilePermissions.........................................................412 derby.stream.error.extendedDiagSeverityLevel.................................................414 derby.stream.error.field......................................................................................415 derby.stream.error.file........................................................................................415 derby.stream.error.logBootTrace.......................................................................415 derby.stream.error.logSeverityLevel..................................................................416 viii

Description:
Mar 27, 2014 Version 10.10 Derby Reference Manual i. Contents. Copyright. SQL syntax used in this manual.17.
ebook img

Derby Reference Manual - Apache DB Project - The Apache PDF

434 Pages·2014·1.98 MB·English
by  
Save to my drive
Quick download
Download
Upgrade Premium
Most books are stored in the elastic cloud where traffic is expensive. For this reason, we have a limit on daily download.

Preview Derby Reference Manual - Apache DB Project - The Apache

Derby Reference Manual Version 10.10 Derby Document build: March 27, 2014, 12:21:19 PM (PDT) Version 10.10 Derby Reference Manual Contents Copyright..............................................................................................................................11 License.................................................................................................................................12 About this guide..................................................................................................................16 Purpose of this document......................................................................................16 Audience...................................................................................................................16 How this guide is organized...................................................................................16 SQL syntax used in this manual............................................................................17 SQL language reference.....................................................................................................18 Capitalization and special characters....................................................................18 SQL identifiers ........................................................................................................18 Rules for SQL92 identifiers.................................................................................19 SQL92Identifier....................................................................................................19 aggregateName...................................................................................................20 authorizationIdentifier...........................................................................................20 columnName........................................................................................................20 constraintName....................................................................................................21 correlationName...................................................................................................21 cursorName.........................................................................................................21 functionName.......................................................................................................22 indexName...........................................................................................................22 newTableName....................................................................................................22 procedureName...................................................................................................23 roleName.............................................................................................................23 schemaName.......................................................................................................23 sequenceName....................................................................................................24 simpleColumnName.............................................................................................24 synonymName.....................................................................................................24 tableName............................................................................................................24 triggerName.........................................................................................................25 typeName.............................................................................................................25 viewName............................................................................................................25 Statements................................................................................................................25 Interaction with the dependency system.............................................................26 ALTER TABLE statement....................................................................................27 CALL (PROCEDURE) statement........................................................................32 CREATE statements...........................................................................................32 DECLARE GLOBAL TEMPORARY TABLE statement.......................................55 DELETE statement..............................................................................................58 DROP statements................................................................................................58 GRANT statement ..............................................................................................62 INSERT statement...............................................................................................65 LOCK TABLE statement.....................................................................................66 RENAME statements...........................................................................................68 REVOKE statement ............................................................................................69 SELECT statement..............................................................................................73 SET statements...................................................................................................75 TRUNCATE TABLE statement............................................................................77 UPDATE statement.............................................................................................78 SQL clauses.............................................................................................................79 CONSTRAINT clause..........................................................................................79 i Version 10.10 Derby Reference Manual EXTERNAL NAME clause...................................................................................85 FOR UPDATE clause..........................................................................................86 FROM clause.......................................................................................................87 GROUP BY clause..............................................................................................87 HAVING clause...................................................................................................88 WINDOW clause.................................................................................................89 ORDER BY clause..............................................................................................89 The result offset and fetch first clauses..............................................................90 USING clause......................................................................................................91 WHERE clause....................................................................................................92 WHERE CURRENT OF clause...........................................................................93 SQL expressions......................................................................................................93 selectExpression..................................................................................................96 tableExpression...................................................................................................99 NEXT VALUE FOR expression.........................................................................100 VALUES expression..........................................................................................101 Expression precedence.....................................................................................102 Boolean expressions.........................................................................................102 CASE expression..............................................................................................105 Dynamic parameters.........................................................................................105 JOIN operations.....................................................................................................108 INNER JOIN operation......................................................................................108 LEFT OUTER JOIN operation...........................................................................110 RIGHT OUTER JOIN operation........................................................................111 CROSS JOIN operation....................................................................................111 NATURAL JOIN operation................................................................................112 SQL queries............................................................................................................113 query..................................................................................................................113 scalarSubquery..................................................................................................115 tableSubquery....................................................................................................115 Built-in functions....................................................................................................116 Standard built-in functions.................................................................................117 Aggregates (set functions)................................................................................118 ABS or ABSVAL function..................................................................................119 ACOS function...................................................................................................119 ASIN function.....................................................................................................119 ATAN function...................................................................................................120 ATAN2 function.................................................................................................120 AVG function.....................................................................................................120 BIGINT function.................................................................................................121 CAST function...................................................................................................122 CEIL or CEILING function.................................................................................125 CHAR function...................................................................................................126 COALESCE function.........................................................................................127 Concatenation operator.....................................................................................128 COS function.....................................................................................................129 COSH function...................................................................................................129 COT function.....................................................................................................129 COUNT function................................................................................................129 COUNT(*) function............................................................................................130 CURRENT DATE function.................................................................................130 CURRENT_DATE function................................................................................130 CURRENT ISOLATION function.......................................................................131 CURRENT_ROLE function................................................................................131 CURRENT SCHEMA function...........................................................................131 CURRENT TIME function..................................................................................131 ii Version 10.10 Derby Reference Manual CURRENT_TIME function.................................................................................132 CURRENT TIMESTAMP function.....................................................................132 CURRENT_TIMESTAMP function.....................................................................132 CURRENT_USER function................................................................................132 DATE function...................................................................................................133 DAY function......................................................................................................133 DEGREES function...........................................................................................134 DOUBLE function..............................................................................................134 EXP function......................................................................................................135 FLOOR function.................................................................................................135 HOUR function..................................................................................................135 IDENTITY_VAL_LOCAL function......................................................................135 INTEGER function.............................................................................................137 LCASE or LOWER function..............................................................................137 LENGTH function..............................................................................................138 LN or LOG function...........................................................................................138 LOG10 function.................................................................................................138 LOCATE function...............................................................................................139 LTRIM function..................................................................................................139 MAX function.....................................................................................................140 MIN function......................................................................................................141 MINUTE function...............................................................................................141 MOD function.....................................................................................................142 MONTH function................................................................................................142 NULLIF function.................................................................................................142 PI function..........................................................................................................143 RADIANS function.............................................................................................143 RANDOM function.............................................................................................143 RAND function...................................................................................................143 ROW_NUMBER function...................................................................................143 RTRIM function..................................................................................................144 SECOND function..............................................................................................144 SESSION_USER function.................................................................................145 SIGN function....................................................................................................145 SIN function.......................................................................................................145 SINH function....................................................................................................146 SMALLINT function...........................................................................................146 SQRT function...................................................................................................146 SUBSTR function..............................................................................................147 SUM function.....................................................................................................148 TAN function......................................................................................................148 TANH function...................................................................................................148 TIME function....................................................................................................149 TIMESTAMP function........................................................................................149 TRIM function....................................................................................................150 UCASE or UPPER function...............................................................................151 USER function...................................................................................................151 VARCHAR function...........................................................................................152 XMLEXISTS operator........................................................................................152 XMLPARSE operator.........................................................................................153 XMLQUERY operator........................................................................................155 XMLSERIALIZE operator..................................................................................156 YEAR function...................................................................................................157 Built-in system functions......................................................................................158 SYSCS_UTIL.SYSCS_CHECK_TABLE system function..................................158 SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY system function.........159 iii Version 10.10 Derby Reference Manual SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS system function.............159 SYSCS_UTIL.SYSCS_GET_USER_ACCESS system function.......................159 SYSCS_UTIL.SYSCS_GET_XPLAIN_MODE system function.........................160 SYSCS_UTIL.SYSCS_GET_XPLAIN_SCHEMA system function....................160 SYSCS_UTIL.SYSCS_PEEK_AT_SEQUENCE system function.....................161 Built-in system procedures..................................................................................161 SYSCS_UTIL.SYSCS_BACKUP_DATABASE system procedure....................162 SYSCS_UTIL.SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE system procedure..............................................................................................162 SYSCS_UTIL.SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE_NOWAIT system procedure..............................................................................................163 SYSCS_UTIL.SYSCS_BACKUP_DATABASE_NOWAIT system procedure....164 SYSCS_UTIL.SYSCS_CHECKPOINT_DATABASE system procedure...........165 SYSCS_UTIL.SYSCS_COMPRESS_TABLE system procedure......................165 SYSCS_UTIL.SYSCS_CREATE_USER system procedure.............................167 SYSCS_UTIL.SYSCS_DISABLE_LOG_ARCHIVE_MODE system procedure...........................................................................................................168 SYSCS_UTIL.SYSCS_DROP_STATISTICS system procedure.......................169 SYSCS_UTIL.SYSCS_DROP_USER system procedure..................................169 SYSCS_UTIL.SYSCS_EMPTY_STATEMENT_CACHE system procedure.....170 SYSCS_UTIL.SYSCS_EXPORT_QUERY system procedure..........................171 SYSCS_UTIL.SYSCS_EXPORT_QUERY_LOBS_TO_EXTFILE system procedure...........................................................................................................172 SYSCS_UTIL.SYSCS_EXPORT_TABLE system procedure............................173 SYSCS_UTIL.SYSCS_EXPORT_TABLE_LOBS_TO_EXTFILE system procedure...........................................................................................................175 SYSCS_UTIL.SYSCS_FREEZE_DATABASE system procedure.....................176 SYSCS_UTIL.SYSCS_IMPORT_DATA system procedure..............................177 SYSCS_UTIL.SYSCS_IMPORT_DATA_LOBS_FROM_EXTFILE system procedure...........................................................................................................178 SYSCS_UTIL.SYSCS_IMPORT_TABLE system procedure.............................180 SYSCS_UTIL.SYSCS_IMPORT_TABLE_LOBS_FROM_EXTFILE system procedure...........................................................................................................182 SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE system procedure.....183 SYSCS_UTIL.SYSCS_INVALIDATE_STORED_STATEMENTS system procedure...........................................................................................................185 SYSCS_UTIL.SYSCS_MODIFY_PASSWORD system procedure...................186 SYSCS_UTIL.SYSCS_REGISTER_TOOL system procedure..........................186 SYSCS_UTIL.SYSCS_RELOAD_SECURITY_POLICY system procedure......188 SYSCS_UTIL.SYSCS_RESET_PASSWORD system procedure.....................188 SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY system procedure......189 SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS system procedure..........190 SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING system procedure............190 SYSCS_UTIL.SYSCS_SET_USER_ACCESS system procedure....................191 SYSCS_UTIL.SYSCS_SET_XPLAIN_MODE system procedure......................191 SYSCS_UTIL.SYSCS_SET_XPLAIN_SCHEMA system procedure.................192 SYSCS_UTIL.SYSCS_UNFREEZE_DATABASE system procedure................193 SYSCS_UTIL.SYSCS_UPDATE_STATISTICS system procedure...................193 System procedures for storing jar files in a database.......................................194 SYSCS_DIAG diagnostic tables and functions...................................................196 SYSCS_DIAG.CONTAINED_ROLES diagnostic table function........................197 SYSCS_DIAG.ERROR_LOG_READER diagnostic table function....................197 SYSCS_DIAG.ERROR_MESSAGES diagnostic table......................................198 SYSCS_DIAG.LOCK_TABLE diagnostic table.................................................199 SYSCS_DIAG.SPACE_TABLE diagnostic table function.................................200 SYSCS_DIAG.STATEMENT_CACHE diagnostic table....................................202 iv Version 10.10 Derby Reference Manual SYSCS_DIAG.STATEMENT_DURATION diagnostic table function.................203 SYSCS_DIAG.TRANSACTION_TABLE diagnostic table..................................204 Data types...............................................................................................................205 Built-In type overview........................................................................................205 Numeric types....................................................................................................206 Data type assignments and comparison, sorting, and ordering........................208 BIGINT data type..............................................................................................212 BLOB data type.................................................................................................213 BOOLEAN data type.........................................................................................214 CHAR data type................................................................................................215 CHAR FOR BIT DATA data type......................................................................215 CLOB data type.................................................................................................216 DATE data type.................................................................................................217 DECIMAL data type...........................................................................................217 DOUBLE data type............................................................................................218 DOUBLE PRECISION data type.......................................................................218 FLOAT data type...............................................................................................219 INTEGER data type...........................................................................................220 LONG VARCHAR data type..............................................................................220 LONG VARCHAR FOR BIT DATA data type...................................................221 NUMERIC data type..........................................................................................221 REAL data type.................................................................................................221 SMALLINT data type.........................................................................................222 TIME data type..................................................................................................223 TIMESTAMP data type......................................................................................223 User-defined types............................................................................................224 VARCHAR data type.........................................................................................224 VARCHAR FOR BIT DATA data type...............................................................225 XML data type...................................................................................................225 Argument matching..........................................................................................................227 SQL reserved words.........................................................................................................229 Derby support for SQL-92 features ...............................................................................233 Derby system tables.........................................................................................................241 SYSALIASES system table...................................................................................241 SYSCHECKS system table....................................................................................242 SYSCOLPERMS system table...............................................................................242 SYSCOLUMNS system table.................................................................................243 SYSCONGLOMERATES system table..................................................................245 SYSCONSTRAINTS system table.........................................................................245 SYSDEPENDS system table..................................................................................246 SYSFILES system table.........................................................................................247 SYSFOREIGNKEYS system table.........................................................................247 SYSKEYS system table.........................................................................................248 SYSPERMS system table......................................................................................248 SYSROLES system table......................................................................................250 SYSROUTINEPERMS system table......................................................................251 SYSSCHEMAS system table.................................................................................252 SYSSEQUENCES system table............................................................................253 SYSSTATEMENTS system table...........................................................................254 SYSSTATISTICS system table..............................................................................255 SYSTABLEPERMS system table..........................................................................256 SYSTABLES system table....................................................................................258 SYSTRIGGERS system table................................................................................259 SYSUSERS system table......................................................................................260 v Version 10.10 Derby Reference Manual SYSVIEWS system table.......................................................................................261 XPLAIN style tables..........................................................................................................262 SYSXPLAIN_STATEMENTS system table...........................................................262 SYSXPLAIN_STATEMENT_TIMINGS system table.............................................264 SYSXPLAIN_RESULTSETS system table............................................................266 SYSXPLAIN_RESULTSET_TIMINGS system table..............................................271 SYSXPLAIN_SCAN_PROPS system table...........................................................273 SYSXPLAIN_SORT_PROPS system table...........................................................277 Derby exception messages and SQL states..................................................................280 SQL error messages and exceptions..................................................................280 JDBC reference.................................................................................................................326 java.sql.Driver interface.........................................................................................326 java.sql.Driver.getPropertyInfo method.............................................................327 java.sql.DriverManager.getConnection method..................................................327 Derby database connection URL syntax...........................................................328 Syntax of database connection URLs for applications with embedded databases..........................................................................................................328 Additional SQL syntax.......................................................................................329 Attributes of the Derby database connection URL ...........................................329 java.sql.Connection interface...............................................................................329 java.sql.Connection.setTransactionIsolation method........................................330 java.sql.Connection.setReadOnly method.........................................................330 java.sql.Connection.isReadOnly method...........................................................331 Connection functionality not supported.............................................................331 java.sql.DatabaseMetaData interface...................................................................331 DatabaseMetaData result sets..........................................................................331 Columns in the ResultSets returned by getFunctionColumns and getProcedureColumns.......................................................................................331 java.sql.DatabaseMetaData.getBestRowIdentifier method................................332 java.sql.Statement interface..................................................................................333 ResultSet objects .............................................................................................333 Autogenerated keys...........................................................................................334 java.sql.CallableStatement interface....................................................................335 CallableStatements and OUT Parameters .......................................................335 CallableStatements and INOUT parameters ....................................................335 java.sql.PreparedStatement interface..................................................................336 Prepared statements and streaming columns ..................................................337 java.sql.ResultSet interface..................................................................................338 ResultSets and streaming columns .................................................................339 java.sql.ResultSetMetaData interface..................................................................340 java.sql.SQLException class................................................................................340 java.sql.SQLWarning class...................................................................................340 java.sql.Savepoint interface..................................................................................340 Mapping of java.sql.Types to SQL types............................................................340 Mapping of java.sql.Blob and java.sql.Clob interfaces......................................341 JDBC Package for Connected Device Configuration/Foundation Profile (JSR 169)..........................................................................................................................343 Features supported on all JDBC 4 releases.......................................................344 Refined subclasses of SQLException...............................................................345 java.sql.Connection interface: JDBC 4.0 features.............................................345 java.sql.DatabaseMetaData interface: JDBC 4.0 features................................345 java.sql.Statement interface: JDBC 4.0 features...............................................346 javax.sql.DataSource interface: JDBC 3 and JDBC 4.0 features......................346 java.sql.SQLXML interface................................................................................346 vi Version 10.10 Derby Reference Manual Features supported on JDBC 4.1 and above......................................................346 java.sql.Connection interface: JDBC 4.1 features.............................................347 javax.sql.DataSource interface: JDBC 4.1 features..........................................347 JDBC 4.2-only features.........................................................................................347 JDBC support for Java SE 8 Compact Profiles................................................347 java.sql.DatabaseMetaData interface: JDBC 4.2 features................................348 java.sql.SQLType interface................................................................................348 JDBC escape syntax ............................................................................................348 JDBC escape keyword for call statements.......................................................349 JDBC escape syntax for LIKE clauses.............................................................349 JDBC escape syntax for limit/offset clauses.....................................................350 JDBC escape syntax for fn keyword.................................................................350 JDBC escape syntax for outer joins..................................................................356 JDBC escape syntax for time formats..............................................................357 JDBC escape syntax for date formats..............................................................357 JDBC escape syntax for timestamp formats.....................................................358 Setting attributes for the database connection URL ....................................................359 bootPassword=key attribute.................................................................................359 collation=collation attribute..................................................................................360 create=true attribute..............................................................................................360 createFrom=path attribute....................................................................................361 databaseName=nameofDatabase attribute..........................................................362 dataEncryption=true attribute...............................................................................362 decryptDatabase=true attribute............................................................................363 deregister=false attribute......................................................................................363 drop=true attribute.................................................................................................365 encryptionKey=key attribute.................................................................................365 encryptionKeyLength=length attribute................................................................366 encryptionProvider=providerName attribute.......................................................366 encryptionAlgorithm=algorithm attribute............................................................367 failover=true attribute............................................................................................368 logDevice=logDirectoryPath attribute..................................................................368 newBootPassword=newPassword attribute........................................................369 newEncryptionKey=key attribute.........................................................................369 password=userPassword attribute......................................................................370 restoreFrom=path attribute...................................................................................370 retrieveMessageText=false attribute....................................................................370 rollForwardRecoveryFrom=path attribute...........................................................371 securityMechanism=value attribute.....................................................................371 shutdown=true attribute........................................................................................372 slaveHost=hostname attribute..............................................................................372 slavePort=portValue attribute...............................................................................373 ssl=sslMode attribute............................................................................................373 startMaster=true attribute.....................................................................................373 startSlave=true attribute........................................................................................374 stopMaster=true attribute......................................................................................375 stopSlave=true attribute........................................................................................375 territory=ll_CC attribute.........................................................................................376 traceDirectory=path attribute................................................................................377 traceFile=path attribute.........................................................................................378 traceFileAppend=true attribute.............................................................................378 traceLevel=value attribute.....................................................................................378 upgrade=true attribute...........................................................................................380 user=userName attribute.......................................................................................380 Creating a connection without specifying attributes.........................................381 vii Version 10.10 Derby Reference Manual Derby property reference.................................................................................................382 Scope of Derby properties....................................................................................382 Dynamic and static properties.............................................................................382 Derby properties....................................................................................................382 derby.authentication.builtin.algorithm................................................................385 derby.authentication.builtin.iterations.................................................................386 derby.authentication.builtin.saltLength...............................................................386 derby.authentication.ldap.searchAuthDN..........................................................387 derby.authentication.ldap.searchAuthPW..........................................................387 derby.authentication.ldap.searchBase...............................................................388 derby.authentication.ldap.searchFilter...............................................................388 derby.authentication.native.passwordLifetimeMillis...........................................389 derby.authentication.native.passwordLifetimeThreshold...................................390 derby.authentication.provider.............................................................................390 derby.authentication.server................................................................................392 derby.connection.requireAuthentication.............................................................393 derby.database.classpath..................................................................................394 derby.database.defaultConnectionMode...........................................................394 derby.database.forceDatabaseLock..................................................................395 derby.database.fullAccessUsers........................................................................396 derby.database.noAutoBoot..............................................................................396 derby.database.propertiesOnly..........................................................................397 derby.database.readOnlyAccessUsers..............................................................397 derby.database.sqlAuthorization........................................................................398 derby.infolog.append.........................................................................................399 derby.jdbc.xaTransactionTimeout......................................................................399 derby.language.logQueryPlan...........................................................................399 derby.language.logStatementText.....................................................................400 derby.language.sequence.preallocator..............................................................400 derby.language.statementCacheSize................................................................401 derby.locks.deadlockTimeout............................................................................401 derby.locks.deadlockTrace................................................................................402 derby.locks.escalationThreshold........................................................................402 derby.locks.monitor............................................................................................403 derby.locks.waitTimeout....................................................................................403 derby.replication.logBufferSize..........................................................................404 derby.replication.maxLogShippingInterval.........................................................405 derby.replication.minLogShippingInterval..........................................................405 derby.replication.verbose...................................................................................406 derby.storage.indexStats.auto...........................................................................406 derby.storage.indexStats.log.............................................................................406 derby.storage.indexStats.trace..........................................................................407 derby.storage.initialPages..................................................................................408 derby.storage.minimumRecordSize...................................................................409 derby.storage.pageCacheSize...........................................................................409 derby.storage.pageReservedSpace..................................................................410 derby.storage.pageSize.....................................................................................410 derby.storage.rowLocking..................................................................................411 derby.storage.tempDirectory..............................................................................412 derby.storage.useDefaultFilePermissions.........................................................412 derby.stream.error.extendedDiagSeverityLevel.................................................414 derby.stream.error.field......................................................................................415 derby.stream.error.file........................................................................................415 derby.stream.error.logBootTrace.......................................................................415 derby.stream.error.logSeverityLevel..................................................................416 viii

Description:
Mar 27, 2014 Version 10.10 Derby Reference Manual i. Contents. Copyright. SQL syntax used in this manual.17.
See more

The list of books you might like

Upgrade Premium
Most books are stored in the elastic cloud where traffic is expensive. For this reason, we have a limit on daily download.
DMCA & Copyright: Dear all, most of the website is community built, users are uploading hundred of books everyday, which makes really hard for us to identify copyrighted material, please contact us if you want any material removed.
Copyright @ PDFdrive.to, 2022 | We love our users