Table Of Content

Derby Reference Manual Version 10.9 Derby Document build: august 22, 2012, 12:03:05 PM (CEST) Version 10.9 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 language reference.....................................................................................................18 Capitalization and special characters....................................................................18 SQL identifiers ........................................................................................................18 Rules for SQL92 identifiers.................................................................................19 SQL92Identifier....................................................................................................19 column-Name.......................................................................................................20 correlation-Name.................................................................................................20 new-table-Name...................................................................................................21 schemaName.......................................................................................................21 Simple-column-Name..........................................................................................21 synonym-Name....................................................................................................21 table-Name..........................................................................................................22 view-Name...........................................................................................................22 index-Name..........................................................................................................22 constraint-Name...................................................................................................22 cursor-Name........................................................................................................23 TriggerName........................................................................................................23 AuthorizationIdentifier..........................................................................................23 RoleName............................................................................................................23 Statements................................................................................................................24 Interaction with the dependency system.............................................................24 ALTER TABLE statement....................................................................................25 CALL (PROCEDURE) statement........................................................................29 CREATE statements...........................................................................................30 DECLARE GLOBAL TEMPORARY TABLE statement.......................................52 DELETE statement..............................................................................................54 DROP statements................................................................................................55 GRANT statement ..............................................................................................58 INSERT statement...............................................................................................61 LOCK TABLE statement.....................................................................................63 RENAME statements...........................................................................................64 REVOKE statement ............................................................................................65 SELECT statement..............................................................................................70 SET statements...................................................................................................72 TRUNCATE TABLE statement............................................................................74 UPDATE statement.............................................................................................74 SQL clauses.............................................................................................................75 CONSTRAINT clause..........................................................................................76 FOR UPDATE clause..........................................................................................82 FROM clause.......................................................................................................82 GROUP BY clause..............................................................................................83 HAVING clause...................................................................................................84 ORDER BY clause..............................................................................................84 The result offset and fetch first clauses..............................................................86 i Version 10.9 Derby Reference Manual USING clause......................................................................................................87 WHERE clause....................................................................................................88 WHERE CURRENT OF clause...........................................................................88 SQL expressions......................................................................................................89 SelectExpression.................................................................................................92 TableExpression..................................................................................................94 NEXT VALUE FOR expression...........................................................................95 VALUES expression............................................................................................96 Expression precedence.......................................................................................97 Boolean expressions...........................................................................................98 CASE expression..............................................................................................100 Dynamic parameters.........................................................................................101 JOIN operations.....................................................................................................103 INNER JOIN operation......................................................................................104 LEFT OUTER JOIN operation...........................................................................105 RIGHT OUTER JOIN operation........................................................................106 CROSS JOIN operation....................................................................................107 NATURAL JOIN operation................................................................................107 SQL queries............................................................................................................108 Query.................................................................................................................108 ScalarSubquery.................................................................................................110 TableSubquery...................................................................................................111 Built-in functions....................................................................................................112 Standard built-in functions.................................................................................112 Aggregates (set functions)................................................................................113 ABS or ABSVAL function..................................................................................114 ACOS function...................................................................................................114 ASIN function.....................................................................................................114 ATAN function...................................................................................................115 ATAN2 function.................................................................................................115 AVG function.....................................................................................................115 BIGINT function.................................................................................................116 CAST function...................................................................................................117 CEIL or CEILING function.................................................................................120 CHAR function...................................................................................................121 COALESCE function.........................................................................................122 Concatenation operator.....................................................................................123 COS function.....................................................................................................124 COSH function...................................................................................................124 COT function.....................................................................................................124 COUNT function................................................................................................124 COUNT(*) function............................................................................................125 CURRENT DATE function.................................................................................125 CURRENT_DATE function................................................................................125 CURRENT ISOLATION function.......................................................................126 CURRENT_ROLE function................................................................................126 CURRENT SCHEMA function...........................................................................126 CURRENT TIME function..................................................................................126 CURRENT_TIME function.................................................................................126 CURRENT TIMESTAMP function.....................................................................127 CURRENT_TIMESTAMP function.....................................................................127 CURRENT_USER function................................................................................127 DATE function...................................................................................................128 DAY function......................................................................................................128 DEGREES function...........................................................................................129 DOUBLE function..............................................................................................129 ii Version 10.9 Derby Reference Manual EXP function......................................................................................................129 FLOOR function.................................................................................................130 HOUR function..................................................................................................130 IDENTITY_VAL_LOCAL function......................................................................130 INTEGER function.............................................................................................132 LCASE or LOWER function..............................................................................132 LENGTH function..............................................................................................133 LN or LOG function...........................................................................................133 LOG10 function.................................................................................................133 LOCATE function...............................................................................................134 LTRIM function..................................................................................................134 MAX function.....................................................................................................135 MIN function......................................................................................................135 MINUTE function...............................................................................................136 MOD function.....................................................................................................136 MONTH function................................................................................................137 NULLIF function.................................................................................................137 PI function..........................................................................................................137 RADIANS function.............................................................................................137 RANDOM function.............................................................................................138 RAND function...................................................................................................138 ROW_NUMBER function...................................................................................138 RTRIM function..................................................................................................139 SECOND function..............................................................................................139 SESSION_USER function.................................................................................139 SIGN function....................................................................................................140 SIN function.......................................................................................................140 SINH function....................................................................................................140 SMALLINT function...........................................................................................140 SQRT function...................................................................................................141 SUBSTR function..............................................................................................141 SUM function.....................................................................................................142 TAN function......................................................................................................143 TANH function...................................................................................................143 TIME function....................................................................................................143 TIMESTAMP function........................................................................................144 TRIM function....................................................................................................144 UCASE or UPPER function...............................................................................145 USER function...................................................................................................146 VARCHAR function...........................................................................................146 XMLEXISTS operator........................................................................................146 XMLPARSE operator.........................................................................................148 XMLQUERY operator........................................................................................149 XMLSERIALIZE operator..................................................................................151 YEAR function...................................................................................................152 Built-in system functions......................................................................................152 SYSCS_UTIL.SYSCS_CHECK_TABLE system function..................................152 SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY system function.........153 SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS system function.............153 SYSCS_UTIL.SYSCS_GET_USER_ACCESS system function.......................154 SYSCS_UTIL.SYSCS_GET_XPLAIN_MODE system function.........................154 SYSCS_UTIL.SYSCS_GET_XPLAIN_SCHEMA system function....................155 SYSCS_UTIL.SYSCS_PEEK_AT_SEQUENCE system function.....................155 Built-in system procedures..................................................................................156 SYSCS_UTIL.SYSCS_BACKUP_DATABASE system procedure....................156 iii Version 10.9 Derby Reference Manual SYSCS_UTIL.SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE system procedure..............................................................................................157 SYSCS_UTIL.SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE_NOWAIT system procedure..............................................................................................157 SYSCS_UTIL.SYSCS_BACKUP_DATABASE_NOWAIT system procedure....158 SYSCS_UTIL.SYSCS_CHECKPOINT_DATABASE system procedure...........159 SYSCS_UTIL.SYSCS_COMPRESS_TABLE system procedure......................160 SYSCS_UTIL.SYSCS_CREATE_USER system procedure.............................161 SYSCS_UTIL.SYSCS_DISABLE_LOG_ARCHIVE_MODE system procedure...........................................................................................................162 SYSCS_UTIL.SYSCS_DROP_STATISTICS system procedure.......................163 SYSCS_UTIL.SYSCS_DROP_USER system procedure..................................164 SYSCS_UTIL.SYSCS_EMPTY_STATEMENT_CACHE system procedure.....164 SYSCS_UTIL.SYSCS_EXPORT_QUERY system procedure..........................165 SYSCS_UTIL.SYSCS_EXPORT_QUERY_LOBS_TO_EXTFILE system procedure...........................................................................................................166 SYSCS_UTIL.SYSCS_EXPORT_TABLE system procedure............................167 SYSCS_UTIL.SYSCS_EXPORT_TABLE_LOBS_TO_EXTFILE system procedure...........................................................................................................169 SYSCS_UTIL.SYSCS_FREEZE_DATABASE system procedure.....................170 SYSCS_UTIL.SYSCS_IMPORT_DATA system procedure..............................171 SYSCS_UTIL.SYSCS_IMPORT_DATA_LOBS_FROM_EXTFILE system procedure...........................................................................................................172 SYSCS_UTIL.SYSCS_IMPORT_TABLE system procedure.............................174 SYSCS_UTIL.SYSCS_IMPORT_TABLE_LOBS_FROM_EXTFILE system procedure...........................................................................................................175 SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE system procedure.....177 SYSCS_UTIL.SYSCS_MODIFY_PASSWORD system procedure...................179 SYSCS_UTIL.SYSCS_RELOAD_SECURITY_POLICY system procedure......179 SYSCS_UTIL.SYSCS_RESET_PASSWORD system procedure.....................180 SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY system procedure......181 SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS system procedure..........181 SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING system procedure............182 SYSCS_UTIL.SYSCS_SET_USER_ACCESS system procedure....................182 SYSCS_UTIL.SYSCS_SET_XPLAIN_MODE system procedure......................183 SYSCS_UTIL.SYSCS_SET_XPLAIN_SCHEMA system procedure.................184 SYSCS_UTIL.SYSCS_UNFREEZE_DATABASE system procedure................184 SYSCS_UTIL.SYSCS_UPDATE_STATISTICS system procedure...................185 System procedures for storing jar files in a database.......................................186 SYSCS_DIAG diagnostic tables and functions...................................................188 Data types...............................................................................................................193 Built-In type overview........................................................................................193 Numeric types....................................................................................................193 Data type assignments and comparison, sorting, and ordering........................195 BIGINT data type..............................................................................................199 BLOB data type.................................................................................................200 BOOLEAN data type.........................................................................................201 CHAR data type................................................................................................202 CHAR FOR BIT DATA data type......................................................................202 CLOB data type.................................................................................................203 DATE data type.................................................................................................204 DECIMAL data type...........................................................................................204 DOUBLE data type............................................................................................205 DOUBLE PRECISION data type.......................................................................205 FLOAT data type...............................................................................................206 INTEGER data type...........................................................................................207 iv Version 10.9 Derby Reference Manual LONG VARCHAR data type..............................................................................207 LONG VARCHAR FOR BIT DATA data type...................................................208 NUMERIC data type..........................................................................................208 REAL data type.................................................................................................208 SMALLINT data type.........................................................................................209 TIME data type..................................................................................................209 TIMESTAMP data type......................................................................................210 User-defined types............................................................................................211 VARCHAR data type.........................................................................................211 VARCHAR FOR BIT DATA data type...............................................................211 XML data type...................................................................................................212 Argument matching..........................................................................................................214 SQL reserved words.........................................................................................................216 Derby support for SQL-92 features ...............................................................................220 Derby system tables.........................................................................................................228 SYSALIASES system table...................................................................................228 SYSCHECKS system table....................................................................................229 SYSCOLPERMS system table...............................................................................229 SYSCOLUMNS system table.................................................................................230 SYSCONGLOMERATES system table..................................................................231 SYSCONSTRAINTS system table.........................................................................232 SYSDEPENDS system table..................................................................................233 SYSFILES system table.........................................................................................234 SYSFOREIGNKEYS system table.........................................................................234 SYSKEYS system table.........................................................................................235 SYSPERMS system table......................................................................................235 SYSROLES system table......................................................................................237 SYSROUTINEPERMS system table......................................................................238 SYSSCHEMAS system table.................................................................................239 SYSSEQUENCES system table............................................................................240 SYSSTATEMENTS system table...........................................................................241 SYSSTATISTICS system table..............................................................................242 SYSTABLEPERMS system table..........................................................................243 SYSTABLES system table....................................................................................245 SYSTRIGGERS system table................................................................................245 SYSUSERS system table......................................................................................247 SYSVIEWS system table.......................................................................................247 XPLAIN style tables..........................................................................................................249 SYSXPLAIN_STATEMENTS system table...........................................................249 SYSXPLAIN_STATEMENT_TIMINGS system table.............................................251 SYSXPLAIN_RESULTSETS system table............................................................253 SYSXPLAIN_RESULTSET_TIMINGS system table..............................................258 SYSXPLAIN_SCAN_PROPS system table...........................................................260 SYSXPLAIN_SORT_PROPS system table...........................................................264 Derby exception messages and SQL states..................................................................267 SQL error messages and exceptions..................................................................267 JDBC reference.................................................................................................................312 java.sql.Driver interface.........................................................................................312 java.sql.Driver.getPropertyInfo method.............................................................313 java.sql.DriverManager.getConnection method..................................................313 Derby database connection URL syntax...........................................................314 v Version 10.9 Derby Reference Manual Syntax of database connection URLs for applications with embedded databases ...........................................................................................................................314 Additional SQL syntax.......................................................................................315 Attributes of the Derby database connection URL ...........................................315 java.sql.Connection interface...............................................................................315 java.sql.Connection.setTransactionIsolation method........................................316 java.sql.Connection.setReadOnly method.........................................................316 java.sql.Connection.isReadOnly method...........................................................316 Connection functionality not supported.............................................................316 java.sql.DatabaseMetaData interface...................................................................317 DatabaseMetaData result sets..........................................................................317 java.sql.DatabaseMetaData.getProcedureColumns method.............................317 Parameters to getProcedureColumns...............................................................317 Columns in the ResultSet returned by getProcedureColumns..........................317 java.sql.DatabaseMetaData.getBestRowIdentifier method................................318 java.sql.Statement interface..................................................................................319 ResultSet objects .............................................................................................320 Autogenerated keys...........................................................................................320 java.sql.CallableStatement interface....................................................................321 CallableStatements and OUT Parameters .......................................................321 CallableStatements and INOUT parameters ....................................................321 java.sql.PreparedStatement interface..................................................................323 Prepared statements and streaming columns ..................................................323 java.sql.ResultSet interface..................................................................................324 ResultSets and streaming columns .................................................................325 java.sql.ResultSetMetaData interface..................................................................326 java.sql.SQLException class................................................................................326 java.sql.SQLWarning class...................................................................................326 java.sql.Savepoint interface..................................................................................326 Mapping of java.sql.Types to SQL types............................................................326 Mapping of java.sql.Blob and java.sql.Clob interfaces......................................327 JDBC Package for Connected Device Configuration/Foundation Profile (JSR 169)..........................................................................................................................329 JDBC 4.0 and 4.1 features....................................................................................330 Refined subclasses of SQLException...............................................................331 java.sql.Connection interface: JDBC 4.0 features.............................................331 java.sql.DatabaseMetaData interface: JDBC 4.0 features................................331 java.sql.Statement interface: JDBC 4.0 features...............................................332 javax.sql.DataSource interface: JDBC 4.0 features..........................................332 java.sql.SQLXML interface................................................................................332 JDBC 4.1-only features.........................................................................................332 java.sql.Connection interface: JDBC 4.1 features.............................................333 JDBC escape syntax ............................................................................................333 JDBC escape keyword for call statements.......................................................333 JDBC escape syntax for LIKE clauses.............................................................334 JDBC escape syntax for limit/offset clauses.....................................................334 JDBC escape syntax for fn keyword.................................................................335 JDBC escape syntax for outer joins..................................................................341 JDBC escape syntax for time formats..............................................................342 JDBC escape syntax for date formats..............................................................342 JDBC escape syntax for timestamp formats.....................................................342 Setting attributes for the database connection URL ....................................................343 bootPassword=key attribute.................................................................................343 collation=collation attribute..................................................................................343 create=true attribute..............................................................................................344 vi Version 10.9 Derby Reference Manual createFrom=path attribute....................................................................................345 databaseName=nameofDatabase attribute..........................................................346 dataEncryption=true attribute...............................................................................346 deregister=false attribute......................................................................................347 drop=true attribute.................................................................................................347 encryptionKey=key attribute.................................................................................348 encryptionProvider=providerName attribute.......................................................349 encryptionAlgorithm=algorithm attribute............................................................349 failover=true attribute............................................................................................350 logDevice=logDirectoryPath attribute..................................................................351 newEncryptionKey=key attribute.........................................................................351 newBootPassword=newPassword attribute........................................................351 password=userPassword attribute......................................................................352 restoreFrom=path attribute...................................................................................352 retrieveMessageText=false attribute....................................................................352 rollForwardRecoveryFrom=path attribute...........................................................353 securityMechanism=value attribute.....................................................................353 shutdown=true attribute........................................................................................354 slaveHost=hostname attribute..............................................................................354 slavePort=portValue attribute...............................................................................355 ssl=sslMode attribute............................................................................................355 startMaster=true attribute.....................................................................................355 startSlave=true attribute........................................................................................356 stopMaster=true attribute......................................................................................357 stopSlave=true attribute........................................................................................357 territory=ll_CC attribute.........................................................................................358 traceDirectory=path attribute................................................................................359 traceFile=path attribute.........................................................................................359 traceFileAppend=true attribute.............................................................................360 traceLevel=value attribute.....................................................................................360 upgrade=true attribute...........................................................................................361 user=userName attribute.......................................................................................362 Creating a connection without specifying attributes.........................................362 Derby property reference.................................................................................................363 Scope of Derby properties....................................................................................363 Dynamic and static properties.............................................................................363 Derby properties....................................................................................................363 derby.authentication.builtin.algorithm................................................................366 derby.authentication.builtin.iterations.................................................................367 derby.authentication.builtin.saltLength...............................................................367 derby.authentication.ldap.searchAuthDN..........................................................368 derby.authentication.ldap.searchAuthPW..........................................................368 derby.authentication.ldap.searchBase...............................................................369 derby.authentication.ldap.searchFilter...............................................................369 derby.authentication.native.passwordLifetimeMillis...........................................370 derby.authentication.native.passwordLifetimeThreshold...................................371 derby.authentication.provider.............................................................................371 derby.authentication.server................................................................................373 derby.connection.requireAuthentication.............................................................374 derby.database.classpath..................................................................................375 derby.database.defaultConnectionMode...........................................................375 derby.database.forceDatabaseLock..................................................................376 derby.database.fullAccessUsers........................................................................377 derby.database.noAutoBoot..............................................................................377 derby.database.propertiesOnly..........................................................................378 vii Version 10.9 Derby Reference Manual derby.database.readOnlyAccessUsers..............................................................378 derby.database.sqlAuthorization........................................................................379 derby.infolog.append.........................................................................................380 derby.jdbc.xaTransactionTimeout......................................................................380 derby.language.logQueryPlan...........................................................................380 derby.language.logStatementText.....................................................................381 derby.language.sequence.preallocator..............................................................381 derby.language.statementCacheSize................................................................382 derby.locks.deadlockTimeout............................................................................382 derby.locks.deadlockTrace................................................................................383 derby.locks.escalationThreshold........................................................................383 derby.locks.monitor............................................................................................384 derby.locks.waitTimeout....................................................................................385 derby.replication.logBufferSize..........................................................................385 derby.replication.maxLogShippingInterval.........................................................386 derby.replication.minLogShippingInterval..........................................................386 derby.replication.verbose...................................................................................387 derby.storage.indexStats.auto...........................................................................387 derby.storage.indexStats.log.............................................................................388 derby.storage.indexStats.trace..........................................................................388 derby.storage.initialPages..................................................................................389 derby.storage.minimumRecordSize...................................................................390 derby.storage.pageCacheSize...........................................................................390 derby.storage.pageReservedSpace..................................................................391 derby.storage.pageSize.....................................................................................392 derby.storage.rowLocking..................................................................................392 derby.storage.tempDirectory..............................................................................393 derby.storage.useDefaultFilePermissions.........................................................394 derby.stream.error.extendedDiagSeverityLevel.................................................395 derby.stream.error.field......................................................................................396 derby.stream.error.file........................................................................................396 derby.stream.error.logBootTrace.......................................................................397 derby.stream.error.logSeverityLevel..................................................................397 derby.stream.error.method................................................................................398 derby.system.bootAll..........................................................................................398 derby.system.durability......................................................................................399 derby.system.home............................................................................................400 derby.user.UserName........................................................................................400 Java EE Compliance: Java Transaction API and javax.sql Interfaces.........................403 The JTA API ..........................................................................................................404 Notes on Product Behavior...............................................................................404 javax.sql: JDBC Interfaces....................................................................................404 Derby API...........................................................................................................................406 Stand-alone tools and utilities.............................................................................406 JDBC implementation classes..............................................................................406 JDBC driver.......................................................................................................406 Data Source Classes.........................................................................................406 Miscellaneous utilities and interfaces.................................................................407 Supported territories .......................................................................................................408 Derby limitations...............................................................................................................409 Limitations for database values...........................................................................409 DATE, TIME, and TIMESTAMP limitations...........................................................409 Limitations on identifier length ...........................................................................410 Numeric limitations................................................................................................410 viii

Description:
Aug 22, 2012 Version 10.9 Derby Reference Manual SQL language reference .. SQL identifiers .
ebook img

Derby Reference Manual - Apache DB Project - The Apache PDF

415 Pages·2012·1.86 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.9 Derby Document build: august 22, 2012, 12:03:05 PM (CEST) Version 10.9 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 language reference.....................................................................................................18 Capitalization and special characters....................................................................18 SQL identifiers ........................................................................................................18 Rules for SQL92 identifiers.................................................................................19 SQL92Identifier....................................................................................................19 column-Name.......................................................................................................20 correlation-Name.................................................................................................20 new-table-Name...................................................................................................21 schemaName.......................................................................................................21 Simple-column-Name..........................................................................................21 synonym-Name....................................................................................................21 table-Name..........................................................................................................22 view-Name...........................................................................................................22 index-Name..........................................................................................................22 constraint-Name...................................................................................................22 cursor-Name........................................................................................................23 TriggerName........................................................................................................23 AuthorizationIdentifier..........................................................................................23 RoleName............................................................................................................23 Statements................................................................................................................24 Interaction with the dependency system.............................................................24 ALTER TABLE statement....................................................................................25 CALL (PROCEDURE) statement........................................................................29 CREATE statements...........................................................................................30 DECLARE GLOBAL TEMPORARY TABLE statement.......................................52 DELETE statement..............................................................................................54 DROP statements................................................................................................55 GRANT statement ..............................................................................................58 INSERT statement...............................................................................................61 LOCK TABLE statement.....................................................................................63 RENAME statements...........................................................................................64 REVOKE statement ............................................................................................65 SELECT statement..............................................................................................70 SET statements...................................................................................................72 TRUNCATE TABLE statement............................................................................74 UPDATE statement.............................................................................................74 SQL clauses.............................................................................................................75 CONSTRAINT clause..........................................................................................76 FOR UPDATE clause..........................................................................................82 FROM clause.......................................................................................................82 GROUP BY clause..............................................................................................83 HAVING clause...................................................................................................84 ORDER BY clause..............................................................................................84 The result offset and fetch first clauses..............................................................86 i Version 10.9 Derby Reference Manual USING clause......................................................................................................87 WHERE clause....................................................................................................88 WHERE CURRENT OF clause...........................................................................88 SQL expressions......................................................................................................89 SelectExpression.................................................................................................92 TableExpression..................................................................................................94 NEXT VALUE FOR expression...........................................................................95 VALUES expression............................................................................................96 Expression precedence.......................................................................................97 Boolean expressions...........................................................................................98 CASE expression..............................................................................................100 Dynamic parameters.........................................................................................101 JOIN operations.....................................................................................................103 INNER JOIN operation......................................................................................104 LEFT OUTER JOIN operation...........................................................................105 RIGHT OUTER JOIN operation........................................................................106 CROSS JOIN operation....................................................................................107 NATURAL JOIN operation................................................................................107 SQL queries............................................................................................................108 Query.................................................................................................................108 ScalarSubquery.................................................................................................110 TableSubquery...................................................................................................111 Built-in functions....................................................................................................112 Standard built-in functions.................................................................................112 Aggregates (set functions)................................................................................113 ABS or ABSVAL function..................................................................................114 ACOS function...................................................................................................114 ASIN function.....................................................................................................114 ATAN function...................................................................................................115 ATAN2 function.................................................................................................115 AVG function.....................................................................................................115 BIGINT function.................................................................................................116 CAST function...................................................................................................117 CEIL or CEILING function.................................................................................120 CHAR function...................................................................................................121 COALESCE function.........................................................................................122 Concatenation operator.....................................................................................123 COS function.....................................................................................................124 COSH function...................................................................................................124 COT function.....................................................................................................124 COUNT function................................................................................................124 COUNT(*) function............................................................................................125 CURRENT DATE function.................................................................................125 CURRENT_DATE function................................................................................125 CURRENT ISOLATION function.......................................................................126 CURRENT_ROLE function................................................................................126 CURRENT SCHEMA function...........................................................................126 CURRENT TIME function..................................................................................126 CURRENT_TIME function.................................................................................126 CURRENT TIMESTAMP function.....................................................................127 CURRENT_TIMESTAMP function.....................................................................127 CURRENT_USER function................................................................................127 DATE function...................................................................................................128 DAY function......................................................................................................128 DEGREES function...........................................................................................129 DOUBLE function..............................................................................................129 ii Version 10.9 Derby Reference Manual EXP function......................................................................................................129 FLOOR function.................................................................................................130 HOUR function..................................................................................................130 IDENTITY_VAL_LOCAL function......................................................................130 INTEGER function.............................................................................................132 LCASE or LOWER function..............................................................................132 LENGTH function..............................................................................................133 LN or LOG function...........................................................................................133 LOG10 function.................................................................................................133 LOCATE function...............................................................................................134 LTRIM function..................................................................................................134 MAX function.....................................................................................................135 MIN function......................................................................................................135 MINUTE function...............................................................................................136 MOD function.....................................................................................................136 MONTH function................................................................................................137 NULLIF function.................................................................................................137 PI function..........................................................................................................137 RADIANS function.............................................................................................137 RANDOM function.............................................................................................138 RAND function...................................................................................................138 ROW_NUMBER function...................................................................................138 RTRIM function..................................................................................................139 SECOND function..............................................................................................139 SESSION_USER function.................................................................................139 SIGN function....................................................................................................140 SIN function.......................................................................................................140 SINH function....................................................................................................140 SMALLINT function...........................................................................................140 SQRT function...................................................................................................141 SUBSTR function..............................................................................................141 SUM function.....................................................................................................142 TAN function......................................................................................................143 TANH function...................................................................................................143 TIME function....................................................................................................143 TIMESTAMP function........................................................................................144 TRIM function....................................................................................................144 UCASE or UPPER function...............................................................................145 USER function...................................................................................................146 VARCHAR function...........................................................................................146 XMLEXISTS operator........................................................................................146 XMLPARSE operator.........................................................................................148 XMLQUERY operator........................................................................................149 XMLSERIALIZE operator..................................................................................151 YEAR function...................................................................................................152 Built-in system functions......................................................................................152 SYSCS_UTIL.SYSCS_CHECK_TABLE system function..................................152 SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY system function.........153 SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS system function.............153 SYSCS_UTIL.SYSCS_GET_USER_ACCESS system function.......................154 SYSCS_UTIL.SYSCS_GET_XPLAIN_MODE system function.........................154 SYSCS_UTIL.SYSCS_GET_XPLAIN_SCHEMA system function....................155 SYSCS_UTIL.SYSCS_PEEK_AT_SEQUENCE system function.....................155 Built-in system procedures..................................................................................156 SYSCS_UTIL.SYSCS_BACKUP_DATABASE system procedure....................156 iii Version 10.9 Derby Reference Manual SYSCS_UTIL.SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE system procedure..............................................................................................157 SYSCS_UTIL.SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE_NOWAIT system procedure..............................................................................................157 SYSCS_UTIL.SYSCS_BACKUP_DATABASE_NOWAIT system procedure....158 SYSCS_UTIL.SYSCS_CHECKPOINT_DATABASE system procedure...........159 SYSCS_UTIL.SYSCS_COMPRESS_TABLE system procedure......................160 SYSCS_UTIL.SYSCS_CREATE_USER system procedure.............................161 SYSCS_UTIL.SYSCS_DISABLE_LOG_ARCHIVE_MODE system procedure...........................................................................................................162 SYSCS_UTIL.SYSCS_DROP_STATISTICS system procedure.......................163 SYSCS_UTIL.SYSCS_DROP_USER system procedure..................................164 SYSCS_UTIL.SYSCS_EMPTY_STATEMENT_CACHE system procedure.....164 SYSCS_UTIL.SYSCS_EXPORT_QUERY system procedure..........................165 SYSCS_UTIL.SYSCS_EXPORT_QUERY_LOBS_TO_EXTFILE system procedure...........................................................................................................166 SYSCS_UTIL.SYSCS_EXPORT_TABLE system procedure............................167 SYSCS_UTIL.SYSCS_EXPORT_TABLE_LOBS_TO_EXTFILE system procedure...........................................................................................................169 SYSCS_UTIL.SYSCS_FREEZE_DATABASE system procedure.....................170 SYSCS_UTIL.SYSCS_IMPORT_DATA system procedure..............................171 SYSCS_UTIL.SYSCS_IMPORT_DATA_LOBS_FROM_EXTFILE system procedure...........................................................................................................172 SYSCS_UTIL.SYSCS_IMPORT_TABLE system procedure.............................174 SYSCS_UTIL.SYSCS_IMPORT_TABLE_LOBS_FROM_EXTFILE system procedure...........................................................................................................175 SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE system procedure.....177 SYSCS_UTIL.SYSCS_MODIFY_PASSWORD system procedure...................179 SYSCS_UTIL.SYSCS_RELOAD_SECURITY_POLICY system procedure......179 SYSCS_UTIL.SYSCS_RESET_PASSWORD system procedure.....................180 SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY system procedure......181 SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS system procedure..........181 SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING system procedure............182 SYSCS_UTIL.SYSCS_SET_USER_ACCESS system procedure....................182 SYSCS_UTIL.SYSCS_SET_XPLAIN_MODE system procedure......................183 SYSCS_UTIL.SYSCS_SET_XPLAIN_SCHEMA system procedure.................184 SYSCS_UTIL.SYSCS_UNFREEZE_DATABASE system procedure................184 SYSCS_UTIL.SYSCS_UPDATE_STATISTICS system procedure...................185 System procedures for storing jar files in a database.......................................186 SYSCS_DIAG diagnostic tables and functions...................................................188 Data types...............................................................................................................193 Built-In type overview........................................................................................193 Numeric types....................................................................................................193 Data type assignments and comparison, sorting, and ordering........................195 BIGINT data type..............................................................................................199 BLOB data type.................................................................................................200 BOOLEAN data type.........................................................................................201 CHAR data type................................................................................................202 CHAR FOR BIT DATA data type......................................................................202 CLOB data type.................................................................................................203 DATE data type.................................................................................................204 DECIMAL data type...........................................................................................204 DOUBLE data type............................................................................................205 DOUBLE PRECISION data type.......................................................................205 FLOAT data type...............................................................................................206 INTEGER data type...........................................................................................207 iv Version 10.9 Derby Reference Manual LONG VARCHAR data type..............................................................................207 LONG VARCHAR FOR BIT DATA data type...................................................208 NUMERIC data type..........................................................................................208 REAL data type.................................................................................................208 SMALLINT data type.........................................................................................209 TIME data type..................................................................................................209 TIMESTAMP data type......................................................................................210 User-defined types............................................................................................211 VARCHAR data type.........................................................................................211 VARCHAR FOR BIT DATA data type...............................................................211 XML data type...................................................................................................212 Argument matching..........................................................................................................214 SQL reserved words.........................................................................................................216 Derby support for SQL-92 features ...............................................................................220 Derby system tables.........................................................................................................228 SYSALIASES system table...................................................................................228 SYSCHECKS system table....................................................................................229 SYSCOLPERMS system table...............................................................................229 SYSCOLUMNS system table.................................................................................230 SYSCONGLOMERATES system table..................................................................231 SYSCONSTRAINTS system table.........................................................................232 SYSDEPENDS system table..................................................................................233 SYSFILES system table.........................................................................................234 SYSFOREIGNKEYS system table.........................................................................234 SYSKEYS system table.........................................................................................235 SYSPERMS system table......................................................................................235 SYSROLES system table......................................................................................237 SYSROUTINEPERMS system table......................................................................238 SYSSCHEMAS system table.................................................................................239 SYSSEQUENCES system table............................................................................240 SYSSTATEMENTS system table...........................................................................241 SYSSTATISTICS system table..............................................................................242 SYSTABLEPERMS system table..........................................................................243 SYSTABLES system table....................................................................................245 SYSTRIGGERS system table................................................................................245 SYSUSERS system table......................................................................................247 SYSVIEWS system table.......................................................................................247 XPLAIN style tables..........................................................................................................249 SYSXPLAIN_STATEMENTS system table...........................................................249 SYSXPLAIN_STATEMENT_TIMINGS system table.............................................251 SYSXPLAIN_RESULTSETS system table............................................................253 SYSXPLAIN_RESULTSET_TIMINGS system table..............................................258 SYSXPLAIN_SCAN_PROPS system table...........................................................260 SYSXPLAIN_SORT_PROPS system table...........................................................264 Derby exception messages and SQL states..................................................................267 SQL error messages and exceptions..................................................................267 JDBC reference.................................................................................................................312 java.sql.Driver interface.........................................................................................312 java.sql.Driver.getPropertyInfo method.............................................................313 java.sql.DriverManager.getConnection method..................................................313 Derby database connection URL syntax...........................................................314 v Version 10.9 Derby Reference Manual Syntax of database connection URLs for applications with embedded databases ...........................................................................................................................314 Additional SQL syntax.......................................................................................315 Attributes of the Derby database connection URL ...........................................315 java.sql.Connection interface...............................................................................315 java.sql.Connection.setTransactionIsolation method........................................316 java.sql.Connection.setReadOnly method.........................................................316 java.sql.Connection.isReadOnly method...........................................................316 Connection functionality not supported.............................................................316 java.sql.DatabaseMetaData interface...................................................................317 DatabaseMetaData result sets..........................................................................317 java.sql.DatabaseMetaData.getProcedureColumns method.............................317 Parameters to getProcedureColumns...............................................................317 Columns in the ResultSet returned by getProcedureColumns..........................317 java.sql.DatabaseMetaData.getBestRowIdentifier method................................318 java.sql.Statement interface..................................................................................319 ResultSet objects .............................................................................................320 Autogenerated keys...........................................................................................320 java.sql.CallableStatement interface....................................................................321 CallableStatements and OUT Parameters .......................................................321 CallableStatements and INOUT parameters ....................................................321 java.sql.PreparedStatement interface..................................................................323 Prepared statements and streaming columns ..................................................323 java.sql.ResultSet interface..................................................................................324 ResultSets and streaming columns .................................................................325 java.sql.ResultSetMetaData interface..................................................................326 java.sql.SQLException class................................................................................326 java.sql.SQLWarning class...................................................................................326 java.sql.Savepoint interface..................................................................................326 Mapping of java.sql.Types to SQL types............................................................326 Mapping of java.sql.Blob and java.sql.Clob interfaces......................................327 JDBC Package for Connected Device Configuration/Foundation Profile (JSR 169)..........................................................................................................................329 JDBC 4.0 and 4.1 features....................................................................................330 Refined subclasses of SQLException...............................................................331 java.sql.Connection interface: JDBC 4.0 features.............................................331 java.sql.DatabaseMetaData interface: JDBC 4.0 features................................331 java.sql.Statement interface: JDBC 4.0 features...............................................332 javax.sql.DataSource interface: JDBC 4.0 features..........................................332 java.sql.SQLXML interface................................................................................332 JDBC 4.1-only features.........................................................................................332 java.sql.Connection interface: JDBC 4.1 features.............................................333 JDBC escape syntax ............................................................................................333 JDBC escape keyword for call statements.......................................................333 JDBC escape syntax for LIKE clauses.............................................................334 JDBC escape syntax for limit/offset clauses.....................................................334 JDBC escape syntax for fn keyword.................................................................335 JDBC escape syntax for outer joins..................................................................341 JDBC escape syntax for time formats..............................................................342 JDBC escape syntax for date formats..............................................................342 JDBC escape syntax for timestamp formats.....................................................342 Setting attributes for the database connection URL ....................................................343 bootPassword=key attribute.................................................................................343 collation=collation attribute..................................................................................343 create=true attribute..............................................................................................344 vi Version 10.9 Derby Reference Manual createFrom=path attribute....................................................................................345 databaseName=nameofDatabase attribute..........................................................346 dataEncryption=true attribute...............................................................................346 deregister=false attribute......................................................................................347 drop=true attribute.................................................................................................347 encryptionKey=key attribute.................................................................................348 encryptionProvider=providerName attribute.......................................................349 encryptionAlgorithm=algorithm attribute............................................................349 failover=true attribute............................................................................................350 logDevice=logDirectoryPath attribute..................................................................351 newEncryptionKey=key attribute.........................................................................351 newBootPassword=newPassword attribute........................................................351 password=userPassword attribute......................................................................352 restoreFrom=path attribute...................................................................................352 retrieveMessageText=false attribute....................................................................352 rollForwardRecoveryFrom=path attribute...........................................................353 securityMechanism=value attribute.....................................................................353 shutdown=true attribute........................................................................................354 slaveHost=hostname attribute..............................................................................354 slavePort=portValue attribute...............................................................................355 ssl=sslMode attribute............................................................................................355 startMaster=true attribute.....................................................................................355 startSlave=true attribute........................................................................................356 stopMaster=true attribute......................................................................................357 stopSlave=true attribute........................................................................................357 territory=ll_CC attribute.........................................................................................358 traceDirectory=path attribute................................................................................359 traceFile=path attribute.........................................................................................359 traceFileAppend=true attribute.............................................................................360 traceLevel=value attribute.....................................................................................360 upgrade=true attribute...........................................................................................361 user=userName attribute.......................................................................................362 Creating a connection without specifying attributes.........................................362 Derby property reference.................................................................................................363 Scope of Derby properties....................................................................................363 Dynamic and static properties.............................................................................363 Derby properties....................................................................................................363 derby.authentication.builtin.algorithm................................................................366 derby.authentication.builtin.iterations.................................................................367 derby.authentication.builtin.saltLength...............................................................367 derby.authentication.ldap.searchAuthDN..........................................................368 derby.authentication.ldap.searchAuthPW..........................................................368 derby.authentication.ldap.searchBase...............................................................369 derby.authentication.ldap.searchFilter...............................................................369 derby.authentication.native.passwordLifetimeMillis...........................................370 derby.authentication.native.passwordLifetimeThreshold...................................371 derby.authentication.provider.............................................................................371 derby.authentication.server................................................................................373 derby.connection.requireAuthentication.............................................................374 derby.database.classpath..................................................................................375 derby.database.defaultConnectionMode...........................................................375 derby.database.forceDatabaseLock..................................................................376 derby.database.fullAccessUsers........................................................................377 derby.database.noAutoBoot..............................................................................377 derby.database.propertiesOnly..........................................................................378 vii Version 10.9 Derby Reference Manual derby.database.readOnlyAccessUsers..............................................................378 derby.database.sqlAuthorization........................................................................379 derby.infolog.append.........................................................................................380 derby.jdbc.xaTransactionTimeout......................................................................380 derby.language.logQueryPlan...........................................................................380 derby.language.logStatementText.....................................................................381 derby.language.sequence.preallocator..............................................................381 derby.language.statementCacheSize................................................................382 derby.locks.deadlockTimeout............................................................................382 derby.locks.deadlockTrace................................................................................383 derby.locks.escalationThreshold........................................................................383 derby.locks.monitor............................................................................................384 derby.locks.waitTimeout....................................................................................385 derby.replication.logBufferSize..........................................................................385 derby.replication.maxLogShippingInterval.........................................................386 derby.replication.minLogShippingInterval..........................................................386 derby.replication.verbose...................................................................................387 derby.storage.indexStats.auto...........................................................................387 derby.storage.indexStats.log.............................................................................388 derby.storage.indexStats.trace..........................................................................388 derby.storage.initialPages..................................................................................389 derby.storage.minimumRecordSize...................................................................390 derby.storage.pageCacheSize...........................................................................390 derby.storage.pageReservedSpace..................................................................391 derby.storage.pageSize.....................................................................................392 derby.storage.rowLocking..................................................................................392 derby.storage.tempDirectory..............................................................................393 derby.storage.useDefaultFilePermissions.........................................................394 derby.stream.error.extendedDiagSeverityLevel.................................................395 derby.stream.error.field......................................................................................396 derby.stream.error.file........................................................................................396 derby.stream.error.logBootTrace.......................................................................397 derby.stream.error.logSeverityLevel..................................................................397 derby.stream.error.method................................................................................398 derby.system.bootAll..........................................................................................398 derby.system.durability......................................................................................399 derby.system.home............................................................................................400 derby.user.UserName........................................................................................400 Java EE Compliance: Java Transaction API and javax.sql Interfaces.........................403 The JTA API ..........................................................................................................404 Notes on Product Behavior...............................................................................404 javax.sql: JDBC Interfaces....................................................................................404 Derby API...........................................................................................................................406 Stand-alone tools and utilities.............................................................................406 JDBC implementation classes..............................................................................406 JDBC driver.......................................................................................................406 Data Source Classes.........................................................................................406 Miscellaneous utilities and interfaces.................................................................407 Supported territories .......................................................................................................408 Derby limitations...............................................................................................................409 Limitations for database values...........................................................................409 DATE, TIME, and TIMESTAMP limitations...........................................................409 Limitations on identifier length ...........................................................................410 Numeric limitations................................................................................................410 viii

Description:
Aug 22, 2012 Version 10.9 Derby Reference Manual SQL language reference .. SQL identifiers .
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