JBoss.orgCommunity Documentation
Teiid supports a core set of runtime types. Runtime types can be different than semantic types defined in type fields at design time. The runtime type can also be specified at design time or it will be automatically chosen as the closest base type to the semantic type.
Table 3.1. Teiid Runtime Types
Type | Description | Java Runtime Class | JDBC Type | ODBC Type |
---|---|---|---|---|
string | variable length character string with a maximum length of 4000 | java.lang.String | VARCHAR | VARCHAR |
char | a single Unicode character | java.lang.Character | CHAR | CHAR |
boolean | a single bit, or Boolean, with two possible values | java.lang.Boolean | BIT | SMALLINT |
byte | numeric, integral type, signed 8-bit | java.lang.Byte | TINYINT | SMALLINT |
short | numeric, integral type, signed 16-bit | java.lang.Short | SMALLINT | SMALLINT |
integer | numeric, integral type, signed 32-bit | java.lang.Integer | INTEGER | INTEGER |
long | numeric, integral type, signed 64-bit | java.lang.Long | BIGINT | NUMERIC |
biginteger | numeric, integral type, arbitrary precision of up to 1000 digits | java.lang.BigInteger | NUMERIC | NUMERIC |
float | numeric, floating point type, 32-bit IEEE 754 floating-point numbers | java.lang.Float | REAL | FLOAT |
double | numeric, floating point type, 64-bit IEEE 754 floating-point numbers | java.lang.String | DOUBLE | DOUBLE |
bigdecimal | numeric, floating point type, arbitrary precision of up to 1000 digits | java.math.BigDecimal | NUMERIC | NUMERIC |
date | datetime, representing a single day (year, month, day) | java.sql.Date | DATE | DATE |
time | datetime, representing a single time (hours, minutes, seconds, milliseconds) | java.sql.Time | TIME | TIME |
timestamp | datetime, representing a single date and time (year, month, day, hours, minutes, seconds, milliseconds, nanoseconds) | java.sql.Timestamp | TIMESTAMP | TIMESTAMP |
object | any arbitrary Java object, must implement java.lang.Serializable | Any | JAVA_OBJECT | VARCHAR |
blob | binary large object, representing a stream of bytes | java.sql.Blob [a] | BLOB | VARCHAR |
clob | character large object, representing a stream of characters | java.sql.Clob [b] | CLOB | VARCHAR |
xml | XML document | java.sql.SQLXML [c] | JAVA_OBJECT | VARCHAR |
[a] The concrete type is expected to be com.metamatrix.common.types.BlobType [b] The concrete type is expected to be com.metamatrix.common.types.ClobType [c] The concrete type is expected to be com.metamatrix.common.types.XMLType |
Data types may be converted from one form to another either
explicitly or implicitly. Implicit conversions automatically occur
in criteria and expressions to ease development. Explicit datatype
conversions require the use of the
CONVERT
function or
CAST
keyword.
Type Conversion Considerations
Any type may be implicitly converted to the OBJECT type.
The OBJECT type may be explicitly converted to any other type.
The
NULL
value may be converted to any type.
Any valid implicit conversion is also a valid explicit conversion.
Situations involving literal values that would normally require explicit conversions may have the explicit conversion applied implicitly if no loss of information occurs.
When Teiid detects that an explicit conversion can not be applied implicitly in criteria, the criteria will be treated as false. For example:
SELECT * FROM my.group WHERE created_by = ‘not a date’
Given that created_by is typed as date, rather than converting
'not a date'
to a date value, the criteria will remain as a string
comparison and therefore be false.
Explicit conversions that are not allowed between two types will result in an exception before execution. Allowed explicit conversions may still fail during processing if the runtime values are not actually convertable.
Table 3.2. Type Conversions
Source Type | Valid Implicit Target Types | Valid Explicit Target Types |
---|---|---|
string | clob | char, boolean, byte, short, integer, long, biginteger, float, double, bigdecimal, xml |
char | string | |
boolean | string, byte, short, integer, long, biginteger, float, double, bigdecimal | |
byte | string, short, integer, long, biginteger, float, double, bigdecimal | boolean |
short | string, integer, long, biginteger, float, double, bigdecimal | boolean, byte |
integer | string, long, biginteger, float, double, bigdecimal | boolean, byte, short |
long | string, biginteger, bigdecimal | boolean, byte, short, integer, float, double |
biginteger | string, bigdecimal | boolean, byte, short, integer, long, float, double |
bigdecimal | string | boolean, byte, short, integer, long, biginteger, float, double |
date | string, timestamp | |
time | string, timestamp | |
timestamp | string | date, time |
clob | string | |
xml | string |
Teiid automatically converts string literals within a SQL statement to their implied types. This typically occurs in a criteria comparison where an element with a different datatype is compared to a literal string:
SELECT * FROM my.group WHERE created_by = ‘2003-01-02’
Here if the created_by element has the datatype of date, Teiid automatically converts the string literal to a date datatype as well.
Teiid can automatically convert literal strings and numeric type values to Boolean values as follows:
Type | Literal Value | Boolean Value |
---|---|---|
String | 'true' | true |
'false' | false | |
other | false | |
Numeric | 1 | true |
0 | false | |
other | error |
Teiid can implicitly convert properly formatted literal strings to their associated date-related datatypes as follows:
String Literal Format | Possible Implicit Conversion Type |
---|---|
yyyy-mm-dd | DATE |
hh:mm:ss | TIME |
yyyy-mm-dd hh:mm:ss.fffffffff [a] | TIMESTAMP |
[a] fractional seconds are optional |
The formats above are those expected by the JDBC date types. To
use other formats see the functions
PARSEDATE
,
PARSETIME
,
PARSETIMESTAMP
.