![]() |
SQL-J Language Reference
|
Reference Manual |
Data Types
Built-In Type OverviewThe 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 TypesBuilt-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 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 OverviewNumeric types include the following types, which provide storage of varying sizes:
Numeric Type Promotion in ExpressionsIn 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.
-- returns a double precision
-- returns a decimal
-- returns an integer Storing Values of One Numeric Data Type in Columns of Another Numeric Data TypeAn 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 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 ArithmeticSQL-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:
Use the following formulas to determine the scale of the resulting data type for the following kinds of arithmetical expressions: 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: Implicit Type Conversion of String TypesCloudscape supports a number of implicit conversions between string and non-string types.
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'
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
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 TypesThe cases in which an explicit CAST is required to or from a string type are:
Comparing Booleans with Numeric Types and Assigning Numeric Types to BooleansBooleans can be compared with any numeric type, and you can assign a numeric type to a boolean and vice versa.
Data Types and Comparison, Sorting, and OrderingInternational String Data Types
The TypesCloudscape 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 SequencesFor 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 to Ordinary String Data TypesWhen an international data type is compared to an ordinary string data type, the locale-specific collating sequence is used. Locale-Sensitive String FunctionsFor 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 TypesDate/Time TypesConversions 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 TypesConversions between numerical type and international string types do not use a locale-sensitive format. |
|
![]() Cloudscape Version 3.6 For information about Cloudscape technical support, go to: www.cloudscape.com/support/.Copyright © 1998, 1999, 2000 Informix Software, Inc. All rights reserved. |