Java DB

Apache Derby

Derby Reference Manual

Derby Getting Started
Derby Reference Manual
Derby Developer's Guide
Derby Performance Tuning
Derby Server and Admin Guide
Derby Tools and Utilities
Derby Reference Manual
-SQL language reference
-Capitalization and special characters
-SQL identifiers
-Rules for SQL92 identifiers
-SQL92Identifier
-column-Name
-correlation-Name
-new-table-Name
-schemaName
-Simple-column-Name
-synonym-Name
-table-Name
-view-Name
-index-Name
-constraint-Name
-cursor-Name
-TriggerName
-
-RoleName
-Interaction with the dependency system
-ALTER TABLE
-CALL (PROCEDURE)
-CREATE statements
-DECLARE GLOBAL TEMPORARY TABLE
-DELETE
-DROP statements
-GRANT
-INSERT
-LOCK TABLE
-RENAME statements
-REVOKE
-SET statements
-SELECT
-UPDATE
-SQL clauses
-SQL expressions
-JOIN operations
-SQL queries
-Built-in functions
-Standard built-in functions
-Aggregates (set functions)
-ABS or ABSVAL function
-ACOS function
-ASIN function
-ATAN function
-ATAN2 function
-AVG function
-BIGINT function
-CASE expressions
-CAST function
-CEIL or CEILING function
-CHAR function
-COALESCE function
-Concatenation operator
-COS function
-COSH function
-COT function
-COUNT function
-COUNT(*) function
-CURRENT DATE function
-CURRENT_DATE function
-CURRENT ISOLATION function
-CURRENT_ROLE function
-CURRENT SCHEMA function
-CURRENT TIME function
-CURRENT_TIME function
-CURRENT TIMESTAMP function
-CURRENT_TIMESTAMP function
-CURRENT_USER function
-DATE function
-DAY function
-DEGREES function
-DOUBLE function
-EXP function
-FLOOR function
-HOUR function
-IDENTITY_VAL_LOCAL function
-INTEGER function
-LCASE or LOWER function
-LENGTH function
-LN or LOG function
-LOG10 function
-LOCATE function
-LTRIM function
-MAX function
-MIN function
-MINUTE function
-MOD function
-MONTH function
-NULLIF expressions
-PI function
-RADIANS function
-RANDOM function
-RAND function
-ROW_NUMBER function
-RTRIM function
-SECOND function
-SESSION_USER function
-SIGN function
-SIN function
-SINH function
-SMALLINT function
-SQRT function
-SUBSTR function
-SUM function
-TAN function
-TANH function
-TIME function
-TIMESTAMP function
-TRIM function
-UCASE or UPPER function
-USER function
-VARCHAR function
-XMLEXISTS operator
-XMLPARSE operator
-XMLQUERY operator
-XMLSERIALIZE operator
-YEAR function
-Built-in system functions
-Built-in system procedures
-SYSCS_B ACKUP_D ATABASE
-SYSCS_BAC KUP_DATAB ASE_NOWAIT
-SYSCS_BACKUP_DAT ABASE_AND_ENABLE _LOG_ARCHIVE_MODE
-SYSCS_BACKUP_DATAB ASE_AND_ENABLE_LOG ARCHIVE_MODE_NOWAIT
-SYSCS_EMP TY_STATEM ENT_CACHE
-SYSCS_CH ECKPOINT _DATABASE
-SYSCS_COMPRESS_TABLE
-SYSCS_INP LACE_COMP RESS_TABLE
-SYSCS_DISA BLE_LOG_AR CHIVE_MODE
-SYSCS_EXPORT_TABLE
-SYSCS_EXPOR T_TABLE_LOB S_TO_EXTFILE
-SYSCS_EXPORT_QUERY
-SYSCS_EXPOR T_QUERY_LOB S_TO_EXTFILE
-SYSCS_IMPORT_DATA
-SYSCS_IMPOR T_DATA_LOBS FROM_EXTFILE
-SYSCS_IMPORT_TABLE
-SYSCS_IMPORT _TABLE_LOBS_ FROM_EXTFILE
-SYSCS_F REEZE_D ATABASE
-SYSCS_U NFREEZE DATABASE
-SYSCS_REL OAD_SECUR ITY_POLICY
-SYSCS_SET _DATABASE _PROPERTY
-SYSCS_SET _RUNTIMES TATISTICS
-SYSCS_SET _STATISTI CS_TIMING
-SYSCS_S ET_XPLA N_SCHEMA
-SYSCS_S ET_XPLA IN_MODE
-SYSCS_S ET_USER _ACCESS
-SYSCS_U PDATE_S ATISTICS
-SYSCS_DIAG diagnostic tables and functions
-Data types
-Argument matching
-SQL reserved words
-Derby support for SQL-92 features
-Derby system tables
-XPLAIN style tables
-Derby exception messages and SQL states
-JDBC reference
-java.sql.Driver interface
-java. sql. DriverManager. getConnection method
-java.sql.Connection interface
-java. sql. DatabaseMetaData interface
-java.sql.Statement interface
-java. sql. CallableStatement interface
-java. sql. PreparedStatement interface
-java.sql.ResultSet interface
-java. sql. ResultSetMetaData interface
-java.sql.SQLException class
-java.sql.SQLWarning class
-java.sql.Savepoint interface
-Mapping of java. sql. Types to SQL types
-JDBC Package for Connected Device Configuration/Foundation Profile (JSR 169)
-JDBC 4.0-only features
-Setting attributes for the database connection URL
-bootPassword= key attribute
-collation= collation attribute
-create= true attribute
-createFrom= path attribute
-databaseName= nameofDatabase attribute
-dataEncryption= true attribute
-drop= true attribute
-encryptionKey= key attribute
-encryptionProvider= providerName attribute
-encryptionAlgorithm= algorithm attribute
-failover= true attribute
-logDevice= logDirectoryPath attribute
-newEncryptionKey= key attribute
-newBootPassword= newPassword attribute
-password= userPassword attribute
-restoreFrom= path attribute
-rollForwardRecoveryFrom= path attribute
-securityMechanism= value attribute
-shutdown= true attribute
-slaveHost= hostname attribute
-slavePort= portValue attribute
-startMaster= true attribute
-startSlave= true attribute
-stopMaster= true attribute
-stopSlave= true attribute
-territory= ll_CC attribute
-traceDirectory= path attribute
-traceFile= path attribute
-traceFileAppend= true attribute
-traceLevel= value attribute
-upgrade= true attribute
-user= userName attribute
-ssl= sslMode attribute
-Creating a connection without specifying attributes
-Derby property reference
-Scope of Derby properties
-Dynamic and static properties
-Derby properties
-derby. authentication. builtin. algorithm
-derby. authentication. ldap. searchAuthDN
-derby. authentication. ldap. searchAuthPW
-derby. authentication. ldap. searchBase
-derby. authentication. ldap. searchFilter
-
-
-derby. connection. requireAuthentication
-derby. database. defaultConnectionMode
-derby. database. forceDatabaseLock
-
-
-
-derby. database. readOnlyAccessUsers
-derby. database. sqlAuthorization
-derby.infolog.append
-derby. jdbc. xaTransactionTimeout
-
-derby. language. logStatementText
-
-
-derby. locks. escalationThreshold
-derby.locks.monitor
-
-derby. replication. logBufferSize
-derby. replication. maxLogShippingInterval
-derby. replication. minLogShippingInterval
-
-
-derby. storage. minimumRecordSize
-
-derby. storage. pageReservedSpace
-
-
-
-
-
-
-derby. stream. error. logSeverityLevel
-derby.system.bootAll
-
-derby.system.home
-derby.user.UserName
-J2EE Compliance: Java Transaction API and javax. sql Interfaces
-Derby API
-Supported territories
-Derby limitations

 

CAST function

The CAST function converts a value from one data type to another and provides a data type to a dynamic parameter (?) or a NULL value.

CAST expressions are permitted anywhere expressions are permitted.

Syntax

CAST ( [ Expression | NULL | ? ]
    AS Datatype)

The data type to which you are casting an expression is the target type. The data type of the expression from which you are casting is the source type.

CAST conversions among SQL-92 data types

The following table shows valid explicit conversions between source types and target types for SQL data types. This table shows which explicit conversions between data types are valid. The first column on the table lists the source data types. The first row lists the target data types. A "Y" indicates that a conversion from the source to the target is valid. For example, the first cell in the second row lists the source data type SMALLINT. The remaining cells on the second row indicate the whether or not you can convert SMALLINT to the target data types that are listed in the first row of the table.
Table 1. Explicit conversions between source types and target types for SQL data types
Types

S
M
A
L
L
I
N
T

I
N
T
E
G
E
R

B
I
G
I
N
T

D
E
C
I
M
A
L

R
E
A
L

D
O
U
B
L
E

F
L
O
A
T

C
H
A
R

V
A
R
C
H
A
R

L
O
N
G

V
A
R
C
H
A
R

C
H
A
R

F
O
R

B
I
T

D
A
T
A

V
A
R
C
H
A
R

F
O
R

B
I
T

D
A
T
A

L
O
N
G

V
A
R
C
H
A
R

F
O
R

B
I
T

D
A
T
A

C
L
O
B

B
L
O
B

D
A
T
E

T
I
M
E

T
I
M
E
S
T
A
M
P

X
M
L
SMALLINT Y Y Y Y Y Y Y Y - - - - - - - - - - -
INTEGER Y Y Y Y Y Y Y Y - - - - - - - - - - -
BIGINT Y Y Y Y Y Y Y Y - - - - - - - - - - -
DECIMAL Y Y Y Y Y Y Y Y - - - - - - - - - - -
REAL Y Y Y Y Y Y Y - - - - - - - - - - - -
DOUBLE Y Y Y Y Y Y Y - - - - - - - - - - - -
FLOAT Y Y Y Y Y Y Y - - - - - - - - - - - -
CHAR Y Y Y Y - - - Y Y Y - - - Y - Y Y Y -
VARCHAR Y Y Y Y - - - Y Y Y - - - Y - Y Y Y -
LONG VARCHAR - - - - - - - Y Y Y - - - Y - - - - -
CHAR FOR BIT DATA - - - - - - - - - - Y Y Y Y Y - - - -
VARCHAR FOR BIT DATA - - - - - - - - - - Y Y Y Y Y - - - -
LONG VARCHAR FOR BIT DATA - - - - - - - - - - Y Y Y Y Y - - - -
CLOB - - - - - - - Y Y Y - - - Y - - - - -
BLOB - - - - - - - - - - - - - - Y - - - -
DATE - - - - - - - Y Y - - - - - - Y - - -
TIME - - - - - - - Y Y - - - - - - - Y - -
TIMESTAMP - - - - - - - Y Y - - - - - - Y Y Y -
XML - - - - - - - - - - - - - - - - - - Y

If a conversion is valid, CASTs are allowed. Size incompatibilities between the source and target types might cause runtime errors.

Notes

In this discussion, the Derby SQL-92 data types are categorized as follows:

Conversions from numeric types

A numeric type can be converted to any other numeric type. If the target type cannot represent the non-fractional component without truncation, an exception is raised. If the target numeric cannot represent the fractional component (scale) of the source numeric, then the source is silently truncated to fit into the target. For example, casting 763.1234 as INTEGER yields 763.

Conversions from and to bit strings

Bit strings can be converted to other bit strings, but not character strings. Strings that are converted to bit strings are padded with trailing zeros to fit the size of the target bit string. The BLOB type is more limited and requires explicit casting. In most cases the BLOB type cannot be casted to and from other types.

Conversions of date/time values

A date/time value can always be converted to and from a TIMESTAMP. If a DATE is converted to a TIMESTAMP, the TIME component of the resulting TIMESTAMP is always 00:00:00. If a TIME data value is converted to a TIMESTAMP, the DATE component is set to the value of CURRENT_DATE at the time the CAST is executed. If a TIMESTAMP is converted to a DATE, the TIME component is silently truncated. If a TIMESTAMP is converted to a TIME, the DATE component is silently truncated.

SELECT CAST (miles AS INT)
FROM Flights
-- convert timestamps to text
INSERT INTO mytable (text_column)
VALUES (CAST (CURRENT_TIMESTAMP AS VARCHAR(100)))
-- you must cast NULL as a data type to use it
SELECT airline
FROM Airlines
UNION ALL
VALUES (CAST (NULL AS CHAR(2)))
-- cast a double as a decimal
SELECT CAST (FLYING_TIME AS DECIMAL(5,2))
FROM FLIGHTS
-- cast a SMALLINT to a BIGINT
VALUES CAST (CAST (12 as SMALLINT) as BIGINT)

Conversions of XML values

An XML value cannot be converted to any non-XML type using an explicit or implicit CAST. Use the XMLSERIALIZE operator to convert an XML type to a character type.

Parent topic: Built-in functions
 

javadb@jdbcurl.com