Chapter 4. Data types

The Data Virtualization type system is based on Java/JDBC types. The runtime object is represented by the corresponding Java class, such as Long, Integer, Boolean, String, and so forth. For more information, see Runtime types. You can use domain types to extend the type system. For more information, see DDL metadata for domains.

4.1. Runtime types

Data Virtualization works with a core set of runtime types. Runtime types can be different from semantic types that are 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.

Note

Even if a type is declared with a length, precision, or scale argument, those restrictions are effectively ignored by the runtime system, but may be enforced/reported at the edge by OData, ODBC, JDBC. Geospatial types act in a similar manner. Extension metadata might be needed for SRID, type, and number of dimensions for consumption by tools/OData, but it is not yet enforced. In some instances you might need to use the ST_SETSRID function to ensure the SRID is associated.

Table 4.1. Data Virtualization runtime types

TypeDescriptionJava runtime classJDBC typeODBC type

string or varchar

Variable length character string with a maximum length of 4000.

java.lang.String

VARCHAR

VARCHAR

varbinary

Variable length binary string with a nominal maximum length of 8192.

byte[] [1]

VARBINARY

VARBINARY

char

A single 16 bit character - which cannot represent a value beyond the Basic Multilingual Plane. This limitation also applies to functions/expressions that expect a single character such as trim, textagg, texttable, and like escape.

java.lang.Character

CHAR

CHAR

boolean

A single bit, or Boolean, that can be true, false, or null (unknown)

java.lang.Boolean

BIT

SMALLINT

byte or tinyint

Numeric, integral type, signed 8-bit

java.lang.Byte

TINYINT

SMALLINT

short or smallint

Numeric, integral type, signed 16-bit

java.lang.Short

SMALLINT

SMALLINT

integer or serial

Numeric, integral type, signed 32-bit. The serial type also implies not null and has an auto-incrementing value that starts at 1. serial types are not automatically UNIQUE.

java.lang.Integer

INTEGER

INTEGER

long or bigint

Numeric, integral type, signed 64-bit

java.lang.Long

BIGINT

NUMERIC

biginteger

Numeric, integral type, arbitrary precision of up to 1000 digits

java.math.BigInteger

NUMERIC

NUMERIC

float or real

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.Double

DOUBLE

DOUBLE

bigdecimal or decimal

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)

java.sql.Time

TIME

TIME

timestamp

Datetime, representing a single date and time (year, month, day, hours, minutes, seconds, fractional seconds).

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 [2]

BLOB

VARCHAR

clob

Character large object, representing a stream of characters.

java.sql.Clob [3]

CLOB

VARCHAR

xml

XML document

java.sql.SQLXML[4]

JAVA_OBJECT

VARCHAR

geometry

Geospatial Object

java.sql.Blob [5]

BLOB

BLOB

geography (11.2+)

Geospatial Object

java.sql.Blob [6]

BLOB

BLOB

json (11.2+)

Character large object, representing a stream of JSON characters.

java.sql.Clob [7]

CLOB

VARCHAR

  1. The runtime type is org.teiid.core.types.BinaryType. Translators will need to explicitly handle BinaryType values. UDFs will instead have a byte[] value passed.
  2. The concrete type is expected to be org.teiid.core.types.BlobType
  3. The concrete type is expected to be org.teiid.core.types.ClobType
  4. The concrete type is expected to be org.teiid.core.types.XMLType
  5. The concrete type is expected to be org.teiid.core.types.GeometryType
  6. The concrete type is expected to be org.teiid.core.types.GeographyType
  7. The concrete type is expected to be org.teiid.core.types.JsonType
Note

Character, String, and character large objects (CLOB) types are not limited to ASCII/extended ASCII values. Character can hold codes up to 2^16-1 and String/CLOB can hold any value.

Arrays

An array of any type is designated by adding [] for each array dimension to the type declaration.

Example: Array types

string[]

integer[][]
Note

Array handling is typically in memory. It is not advisable to rely on the usage of large array values. Arrays of large objects (LOBs) are typically not handled correctly when serialized.

4.2. Type conversions

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 can be explicitly converted to any other type.
  • The NULL value can be converted to any type.
  • Any valid implicit conversion is also a valid explicit conversion.
  • In scenarios where literal values would normally require explicit conversions, you can apply implicit conversions if no loss of information occurs.
  • If widenComparisonToString is false (the default), Data Virtualization raises an exception if it detects that an explicit conversion cannot be applied implicitly in criteria.

  • If widenComparisonToString is true, then depending upon the comparison, a widening conversion is applied or the criteria are treated as false. For more information about widenComparisonToString, see System properties in the Administrator’s Guide.

    Example

    SELECT * FROM my.table WHERE created_by = 'not a date'

    If widenComparisonToString is false, and created_by is a date, not a date cannot be converted to a date value, and an exception results.

  • Explicit conversions that are not allowed between two types will result in an exception before execution. Allowed explicit conversions can still fail during processing if the runtime values are not actually convertible.
Warning

The Data Virtualization conversions of float/double/bigdecimal/timestamp to string rely on the JDBC/Java defined output formats. Pushdown behavior attempts to mimic these results, but can vary depending upon the actual source type and conversion logic. It is best not to assume use of the string form in criteria or other places where variations might lead to different results.

Table 4.2. Type conversions

Source typeValid implicit target typesValid explicit target types

string

clob

char, boolean, byte, short, integer, long, biginteger, float, double, bigdecimal, xml [a]

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, double, bigdecimal

boolean, byte, short, float

long

string, biginteger, bigdecimal, float [b], double [b]

boolean, byte, short, integer, float, double

biginteger

string, bigdecimal float [b], double [b]

boolean, byte, short, integer, long, float, double

bigdecimal

string, float [b], double [b]

boolean, byte, short, integer, long, biginteger, float, double

float

string, bigdecimal, double

boolean, byte, short, integer, long, biginteger

double

string, bigdecimal, float [b]

boolean, byte, short, integer, long, biginteger, float

date

string, timestamp

 

time

string, timestamp

 

timestamp

string

date, time

clob

 

string

json

clob

string

xml

 

string [c]

geography

 

geometry

[a] string to xml is equivalent to XMLPARSE(DOCUMENT exp). For more information, see XMLPARSE in XML functions.
[b] Implicit conversion to float/double only occurs for literal values.
[c] xml to string is equivalent to XMLSERIALIZE(exp AS STRING). For more information, see XMLSERIALIZE in XML functions.

4.3. Special conversion cases

Conversion of string literals

Data Virtualization automatically converts string literals within a SQL statement to their implied types. This typically occurs in a criteria comparison where an expression with a different datatype is compared to a literal string. For example: 

SELECT * FROM my.table WHERE created_by = '2016-01-02'

In the preceding example, if the created_by column has the data type of date, Data Virtualization automatically converts the data type of the string literal to a date.

Converting to Boolean

Data Virtualization can automatically convert literal strings and numeric type values to Boolean values as shwon in the following table:

Table 4.3. Boolean conversions

TypeLiteral valueBoolean value

String

'false'

false

 

'unknown'

null

 

other

true

Numeric

0

false

 

other

true

Date and time conversions

Data Virtualization can implicitly convert properly formatted literal strings to their associated date-related data types as shown in the following table:

Table 4.4. Date and time conversions

String literal formatPossible implicit conversion type

yyyy-mm-dd

DATE

hh:mm:ss

TIME

yyyy-mm-dd[ hh:mm:ss.[fff…]]

TIMESTAMP

The preceding formats are those expected by the JDBC date types. For information about using other formats, see the functions PARSEDATE, PARSETIME, and PARSETIMESTAMP in Date and time functions.

4.4. Escaped literal syntax

Rather than relying on implicit conversion, you can define data type values directly in SQL by using escape syntax. The string values that you supply must match the expected format exactly, or an exception will occur.

DatatypeEscaped syntaxStandard literal

BOOLEAN

{b 'true'}

TRUE

DATE

{d 'yyyy-mm-dd'}

DATE 'yyyy-mm-dd'

TIME

{t 'hh-mm-ss'}

TIME 'hh-mm-ss'

TIMESTAMP

{ts 'yyyy-mm-dd[ hh:mm:ss.[fff…]]'}

TIMESTAMP 'yyyy-mm-dd[ hh:mm:ss.[fff…]]'