SQL-J Language Reference
Page 89 of 121

Data Types

Built-In Type Overview

The SQL-J type system is used by the language compiler to determine the compile-time type of an expression and by the language execution system to determine the runtime type of an expression, which can be a subtype or implementation of the compile-time type.

Each type has associated with it values of that type. In addition, values in the database or resulting from expressions can be NULL, which means the value is missing or unknown. Although there are some places where the keyword NULL can be explicitly used, it is not in itself a value, because it needs to have a type associated with it.

The syntax presented in this section is the syntax you use when specifying a column's data type in a CREATE TABLE statement.

Each built-in type in SQL-J has a Java class associated with it. For more information, see Column Values and Type Correspondence.

Cloudscape Version 3.6 supports the following data types:

Cloudscape also supports the use of Java data types, and it supplies a number of built-in Java data types that are used internally or in system tables. See Java Data Types (User-Defined Data Types).

SQL-J Types, Java Types, and JDBC Types

Built-in types have corresponding Java data types. This correspondence is pertinent at compile time, when Cloudscape validates data types for inserts, joins, unions, and any statement in which two values must be compared somehow. A PreparedStatement is compiled when it is prepared; a Statement is compiled when it is executed. Values associated with a column of the built-in type automatically map, when an SQL-J statement is compiled, to values of the corresponding Java data type during compilation. For example, an insert into a column of type INTEGER expects a value of an INTEGER data type. Because the INTEGER data type has a compile-time corresponding Java data type of java.lang.Integer, Cloudscape also accepts a value of java.lang.Integer at compile time.

-- the compiler determines that the data type of the
-- value to be inserted into myIntColumn matches
-- the expected type
INSERT INTO myTable (myIntColumn)
VALUES (new java.lang.Integer('2'))

Since built-in types do not correspond to Java primitives at compile time, you cannot construct a Java primitive with a simple literal within the context of an SQL-J statement. For example, the literal 1 is an INTEGER value (which corresponds to a java.lang.Integer). This makes calling Java methods that take primitives a bit tricky; see SQL-J to Java Type Correspondence.

However, when working in a Java program using JDBC methods to set the runtime value of dynamic parameters or to retrieve values from a ResultSet, you typically do work with Java primitives. For example, you would typically use the stint method of java.sql.PreparedStatement to insert values into an INTEGER column and the getInt method of java.sql.ResultSet to retrieve values from that column. That's because of the type correspondence set up by JDBC. A JDBC INTEGER type automatically maps to a Java int type; an SQL-J INTEGER type automatically maps to a JDBC INTEGER type. So, within the context of a JDBC program, type correspondence works differently than within the compile-time context of a SQL-J statement.

The data type man pages in this section list the corresponding compile-time Java type for the data type. (For more detailed reference on the compile-time correspondence between SQL-J and Java data types, see SQL-J to Java Type Correspondence.) They also list the corresponding JDBC type (but not its corresponding Java type). For information on how JDBC types map to Java types, see the JDBC documentation.

Numeric Types

Numeric Type Overview

Numeric types include the following types, which provide storage of varying sizes:

Numeric Type Promotion in Expressions

In expressions that use only integer types, Cloudscape promotes the type of the result to at least INTEGER. In expressions that mix integer with non-integer types, Cloudscape promotes the result of the expression to the highest type in the expression. Table 1-7 shows the promotion of data types in expressions.

Table 1-7 Type Promotion in Expressions

Largest Type That Appears in Expression

Resulting Type of Expression

DOUBLE PRECISION

DOUBLE PRECISION

REAL

DOUBLE PRECISION

DECIMAL

DECIMAL

LONGINT

LONGINT

INTEGER

INTEGER

SMALLINT

INTEGER

TINYINT

INTEGER

For example:

-- returns a double precision
VALUES 1 + 1.0e0

-- returns a decimal
VALUES 1 + 1.0

-- returns an integer
VALUES CAST (1 AS TINYINT) + CAST (1 AS TINYINT)

Storing Values of One Numeric Data Type in Columns of Another Numeric Data Type

An attempt to put a floating-point type of a larger storage size into a location of a smaller size fails only if the value cannot be stored in the smaller-size location. For example:

create table mytable (r REAL, d DOUBLE PRECISION);
0 rows inserted/updated/deleted
INSERT INTO mytable (r, d) values (3.4028236E38, 3.4028235E38);
ERROR X0X41: The number '3.4028236E38' is outside the range of
the real datatype.

You can store a floating point type in an INTEGER column; the fractional part of the number is truncated. For example:

INSERT INTO mytable(integer_column) values (1.09e0);
1 row inserted/updated/deleted
SELECT integer_column
FROM mytable;
I --------------- 1

Integer types can always be placed successfully in approximate numeric values, although with the possible loss of some precision.

Integers can be stored in decimals if the DECIMAL precision is large enough for the value. For example:

ij> insert into mytable (decimal_column)
VALUES (55555555556666666666);
ERROR X0Y21: The number '55555555556666666666' is outside the
range of the target DECIMAL/NUMERIC(5,2) datatype.

An attempt to put an integer value of a larger storage size into a location of a smaller size fails if the value cannot be stored in the smaller-size location. For example:

INSERT INTO mytable (tinyint_column) values 6555;
ERROR X0X76: The number '6555' is outside the range of
the tinyint datatype.

NOTE: When truncating trailing digits from a NUMERIC value, Cloudscape rounds down.

Scale for Decimal Arithmetic

SQL-J statements can involve arithmetic expressions that use decimal data types of different precisions (the total number of digits, both to the left and to the right of the decimal point) and scales (the number of digits of the fractional component). The precision and scale of the resulting decimal type depend on the precision and scale of the operands.

Given an arithmetic expression that involves two decimal operands:

  • lp stands for the precision of the left operand
  • rp stands for the precision of the right operand
  • ls stands for the scale of the left operand
  • rs stands for the scale of the right operand

Use the following formulas to determine the scale of the resulting data type for the following kinds of arithmetical expressions:

  • multiplication

    ls + rs

  • division

    max(ls + rp - rs + 1, 4)

  • AVG()

    max(max(ls, rs), 4)

  • all others

    max(ls, rs)

For example, the scale of the resulting data type of the following expression is 7:

11.0/1111.33
// max ((2 + 6 - 2 + 1), 4)

Use the following formulas to determine the precision of the resulting data type for the following kinds of arithmetical expressions:

  • multiplication

    lp + rp

  • addition

    2 * (p - s) + s

  • division

    lp - ls + rp + max(ls + rp - rs + 1, 4)

  • all others

    max(lp - ls, rp - rs) + 1 + max(ls, rs)

Implicit Type Conversion of String Types

Cloudscape supports a number of implicit conversions between string and non-string types.

String types consist of:

Implicit Conversions from Strings to Other Built-In Data Types

NOTE: In a statement in which there is an equijoin condition that requires an implicit (var)char conversion, the statement is disqualified for consideration for a hash join.

A cast to the type of the non-string type may not be what you want, however. For example, the following comparisons raise an exception, because Cloudscape attempts to cast `2.5' to an integer, which contains an illegal character for the integer data type:

1 < '2.5'
1 < CAST ('2.5' AS INT)

  • Use in function or assignment expecting non-string type

    If a string type is used in a non-string type built-in expression as an argument to a function expecting a non-string type built-in argument, the string type is converted to the expected non-string type built-in type before use. Function here means arithmetic operators and built-in functions as well as assignments (as in INSERT and UPDATE) and unions. (It does not mean a Java method or method alias; no implicit conversion is done on the arguments to Java methods.)

    For an arithmetic expression, at least one operand of an arithmetic operator must be a numeric type. (An exception is thrown if both operands are string types.) The string type is implicitly cast to the same numeric type as the other operand.

    For example, the following expressions are treated the same:

    INSERT INTO T (intColumn) VALUES '1'

    INSERT INTO T (intColumn) VALUES (CAST ('1' AS INT))

    VALUES 7.2 + '5.0'

    VALUES 7.2 + CAST ('5.0' AS DECIMAL)

    The following expression raises an exception:

    -- raises an exception
    VALUES '3' + '5'

    The limitations on implicit conversions when compared to explicit conversions are:

    • SUM and AVG aggregates are not supported on string types.
    • If a string type is passed to the EXTRACT() built-in function, the string type is implicitly cast to a DATE when extracting the YEAR, MONTH, or DAY and implicitly cast to a TIME when extracting the HOUR, MINUTE, or SECOND. When using an explicit CAST, you can cast the string to the desired data type.

For the rules on invalid characters and when leading and trailing spaces are stripped out, see CAST and Implicit Conversions from Character Strings.

Implicit Conversion to Strings from Other Built-In Data Types

  • Use in Function or Assignment Expecting String Type

    If a non-string built-in type is used in a string expression or as an argument to a string function, the non-string built-in type is converted to a string type before use. Function here means string operators ("||"), the LIKE predicate, and built-in functions as well as assignments (as in INSERT and UPDATE). For example, the following expressions are treated the same:

    INSERT INTO T (charColumn) VALUES 1

    INSERT INTO T (charColumn) VALUES (CAST (1 AS CHAR(1)))

For the rules on size compatibility and padding with spaces, see Size Requirements for Explicit and Implicit Conversions to Character Strings.

Limitations on Implicit Conversions from or to String Types

The cases in which an explicit CAST is required to or from a string type are:

  • From Java data types to string types. You can call the toString method if you want this behavior.
  • For the receiver or arguments to a Java method call.

Comparing Booleans with Numeric Types and Assigning Numeric Types to Booleans

Booleans can be compared with any numeric type, and you can assign a numeric type to a boolean and vice versa.

  • When you compare a number with a boolean, zero equals false, and any other value equals true.
  • When you assign a number to a boolean, zero becomes false, and any other value becomes true. For example, if you insert 0 into a boolean column, the 0 will be converted to false to be stored in the column, and if you insert -1, the value will be converted to true.
  • When you assign a boolean to a number, false becomes zero, and true becomes 1. For example, if you insert false into an int column, the false is converted to 0 to be stored in the column, and if you insert true, the value will be converted to 1.
  • You cannot pass booleans to methods expecting numbers, and vice versa.

Data Types and Comparison, Sorting, and Ordering

Table 1-8 Data Types and Sorting, Ordering, and Comparisons


All Types

Java Data Types that Correctly Implement .equals and .hashCode

Orderable Java Data Types

Built-inTypes (and Corresponding Java Data Types)

Comparisons = and <>


X

X

X

Comparisons >, >=, <, <=



X

X

Ordering operations include DISTINCT, GROUP BY, UNION, and ORDER BY



X

X

International String Data Types

The Types

Cloudscape provides three locale-sensitive string data types:

These strings behave like their counterparts in most respects. How their behavior differs is explained below.

See Localizing Cloudscape in the Cloudscape Developer's Guide for information on working with database locales.

Locale-Specific Collating Sequences

For the operations that require determining the order of data, for ordinary strings, Cloudscape uses a simple binary ordering. Such an ordering is not adequate for some languages--locales, in Java's terminology--which have their own rules. For example, in Norwegian `aa' comes after `z'.

The international string data types are ordered according to the rules of the database's locale; Cloudscape uses a locale-specific collating sequence.

The operations that require returning the order of data are:

  • Comparisons using comparison operators (<, >, etc.)
  • Statements that involve sorting (ORDER BY, GROUP BY, DISTINCT, MAX, and MIN)
  • Statements that use the LIKE keyword

    In addition, in cases where a pair of adjacent characters is treated as a single character for ordering purposes, Cloudscape does not split the pair when doing a LIKE operation. For example, consider an NCHAR column C with the Norwegian locale containing the values `a', `az', and `aa'. The value `aa' is considered by the Norwegian locale to sort as a single character. The expression C LIKE `a%' would be TRUE for `a' and `az', but it would be FALSE for `aa because the locale does not allow these the two `a's to be considered separately.

Comparisons to Ordinary String Data Types

When an international data type is compared to an ordinary string data type, the locale-specific collating sequence is used.

Locale-Sensitive String Functions

For the three new data types, the UPPER and LOWER functions follow the rules of the database's locale in translating the characters to upper and lower case.

International String Data Types and Conversions to Other Types

Date/Time Types

Conversions between date/time type and international string types use a locale-sensitive format. See Conversions Between Date/Time Data Types and International String Data Types.

Numerical Types

Conversions between numerical type and international string types do not use a locale-sensitive format.