One of the main goals of Open SQL for Java is portability across the database platforms that SAP supports. To achieve this goal, Open SQL only supports a subset of the JDBC types defined in java.sql.Types.
The JDBC types that are supported in Open SQL can be used in table definitions created using the Java Dictionary. The JDBC types that are not supported are rejected by Open SQL.
The table below lists the set of supported Java Dictionary types, and their corresponding JDBC types along with their properties.
The Portable Range defines the range of values that can be stored in columns of this type, and for which you can expect common behavior across the supported database platforms.
More information: Value Checker API
The Comparable column indicates whether values of this type are comparable to each other in the database - this is referred to as general comparability. Such columns can be used in set functions, ordering and grouping clauses, search conditions, and SELECT DISTINCT statements.
More information: Open SQL Grammar
Mutual type comparability is required in statements and predicates comprising multiple operands. In Open SQL for Java the numeric types (SMALLINT, INTEGER, BIGINT, REAL, DOUBLE, and DECIMAL) are mutually comparable.
Java Dictionary Type |
JDBC Type |
Contains |
Portable Range |
Comparable |
---|---|---|---|---|
String |
VARCHAR (N) |
Varying length character strings with a maximum length of N characters |
1 - 1000 Characters |
Yes |
LONGVARCHAR(N) |
Varying length character strings with a maximum length of N characters |
1001 - 1333 Characters |
No |
|
CLOB |
Varying length character strings of virtually-unlimited size |
357.913.941 Characters |
No |
|
Binary |
BINARY (N) |
Byte strings with a fixed length of N bytes |
1 - 255 Bytes |
Yes |
LONGVARBINARY (N) |
Varying length byte strings with a maximum length of N bytes |
256 - 2000 Bytes |
No |
|
BLOB |
Varying length byte strings of virtually-unlimited size |
1.073.741.824 Bytes |
No |
|
Short |
SMALLINT |
Signed integral numbers |
-32768 to +32767 |
Yes |
Integer |
INTEGER |
Signed integral numbers |
-2147483648 to +2147483647 |
Yes |
Long |
BIGINT |
Signed integral numbers |
-9223372036854775808 to +9223372036854775807 |
Yes |
Float |
REAL |
Floating point numbers |
±1.175E-37 to ±3.4E+38 |
Yes |
Double |
DOUBLE |
Floating point numbers |
±1.0E-64 to ±9.9E+62 |
Yes |
Decimal (p,[s]) |
DECIMAL (p,[s]) |
Exact numeric numbers with precision P > 0 and scale S >= 0 |
1 <= P <= 31, 0 <= S <= 30 |
Yes |
Date |
DATE |
Time values expressed by hour, minute and second |
yyyy-mm-dd |
Yes |
Time |
TIME |
Time values expressed by hour, minute and second |
hh:mm:ss |
Yes |
Timestamp |
TIMESTAMP |
Timestamps expressed by date, time and fractional seconds |
yyyy-mm-dd hh:mm:ss.ff |
Yes |
The Java Dictionary uses the types string and binary for any kind of character or byte string. From a programming language perspective, the behavior of these types is independent of the string length. With regard to the storage of strings in relational databases, however, the situation is different because DBMSs usually provide different types for string storage - depending on the string length.
Unfortunately, the functions associated with these types differ and "large" string types are subject to certain restrictions (for example, LOB types must not be used in indexes). Hence, the application programmer who defines a database table in the Java Dictionary must be aware of how the Java Dictionary's string and binary types are mapped to the respective JDBC types - depending on the specified maximum string length.
More information about the conversion of JDBC to Java data types: Data Type Conversion Tables