Data Types

The SQL type system determines the compile-time and runtime type of an expression. Each type has a certain range of permissible values that can be assigned to a column or value of that type.

The special value NULL, denotes an unassigned or missing value of any of the types (columns that have been assigned as non-nullable using NOT NULL clause or the primary key columns cannot have a NULL value). The supported types are given below.

BIGINT

Provides 8 bytes storage for long integer values. An attempt to put a BIGINT value into another exact numeric type with smaller size/precision (e.g. INT) fails if the value overflows the maximum allowable by the smaller type.

For behavior with other types in expressions, see Numeric type promotion in expressions, Storing values of one numeric data type in columns of another numeric data type.

Equivalent Java type java.lang.Long
Minimum value java.lang.Long.MIN_VALUE (-9223372036854775808 )
Maximum value java.lang.Long.MAX_VALUE (9223372036854775807 )
JDBC metadata type java.sql.Types.BIGINT
JDBC methods ResultSet.getLong, PreparedStatement.setLong
Equivalent .NET type System.Int64
ADO.NET metadata type Vmware.Data.SQLFire.SQLFType.Long
ADO.NET mapped System.Data.Dbtype Int64
ADO.NET getter DbDataReader.GetInt64

BLOB

A binary large object represents an array of raw bytes of varying length.

Equivalent Java type java.lang.Blob
Maximum length (also default length) java.lang.Integer.MAX_VALUE (2147483647 )
JDBC metadata type java.sql.Types.BLOB
JDBC methods ResultSet.getBlob, PreparedStatement.setBlob
Equivalent .NET type no specific type mapping; use byte[] where required
ADO.NET metadata type Vmware.Data.SQLFire.SQLFType.Blob
ADO.NET mapped System.Data.Dbtype Binary
ADO.NET getter DbDataReader.GetBytes
{ BLOB | BINARY LARGE OBJECT } [ ( length [{ K | M | G }] ) ] 

The length of the BLOB is expressed in number of bytes by default. The suffixes K, M, G stand for kilobyte, megabyte and gigabyte respectively.

CREATE TABLE blob_data(id INT primary key, data BLOB(10M)); 
–- search for a blob 
select length(data) from blob_data where id = 100;

CHAR

Provides for fixed-length strings. If a string value is shorter than the expected length, then spaces are inserted to pad the string to the expected length. If a string value is longer than the expected length, then any trailing blanks are trimmed to make the length same as the expected length, while an exception is raised if characters other than spaces are required to be truncated. For comparision operations, the shorter CHAR string is padded with spaces to the longer value. Similarly when mixing CHARs and VARCHARs in expressions , the shorter value is padded with spaces to the length of longer string.

To represent a single quotation mark within a string, use two quotation marks:

VALUES 'going to Chandra''s place' 

The length of CHAR is an unsigned integer constant.

Equivalent Java type java.lang.String
Maximum length java.lang.Integer.MAX_VALUE (2147483647 )
Default length 1
JDBC metadata type java.sql.Types.CHAR
JDBC methods ResultSet.getString, PreparedStatement.setString
Equivalent .NET type System.String
ADO.NET metadata type Vmware.Data.SQLFire.SQLFType.Char
ADO.NET mapped System.Data.Dbtype StringFixedLength
ADO.NET getter DbDataReader.GetString
CHAR[ACTER] [(length)] 

CHAR FOR BIT DATA

Provides for fixed-length byte strings. This is used when CHAR type is not appropriate due to unstructured data or otherwise. If a value is shorter than the expected length, then it is padded with 0x20 byte values. No padding is performed for comparisons between CHAR FOR BIT DATA and VARCHAR FOR BIT DATA types as per SQL-92 standard (this is different from the way some DBMSs behave). An operation involving a VARCHAR FOR BIT DATA and CHAR FOR BIT DATA yields a value of type VARCHAR FOR BIT DATA.

The length is an unsigned integer constant for the length of value in bytes.

Equivalent Java type use byte[], java.io.InputStream, or java.lang.OutputStream as required
Maximum length 254 bytes
Default length 1
JDBC metadata type java.sql.Types.BINARY
JDBC methods ResultSet.getBytes, PreparedStatement.setBytes
Equivalent .NET type use byte[] where required
ADO.NET metadata type Vmware.Data.SQLFire.SQLFType.Binary
ADO.NET mapped System.Data.Dbtype Binary
ADO.NET getter DbDataReader.GetBytes
{ CHAR | CHARACTER }[(length)] FOR BIT DATA 

CLOB

A character large object represents an array of characters of varying length. It is used to store large character-based data such as documents.

The length of the CLOB is expressed in number of characters by default. The suffixes K, M, G stand for kilo, mega and giga respectively.

Equivalent Java type java.sql.Clob
Maximum length java.lang.Integer.MAX_VALUE (2147483647 )
Default length java.lang.Integer.MAX_VALUE (2147483647 )
JDBC metadata type java.sql.Types.CLOB
JDBC methods ResultSet.getClob, PreparedStatement.setClob
Equivalent .NET type no specific type; use char[] or string
ADO.NET metadata type Vmware.Data.SQLFire.SQLFType.Clob
ADO.NET mapped System.Data.Dbtype String
ADO.NET getter DbDataReader.GetChars, DbDataReader.GetString
{ CLOB | CHARACTER LARGE OBJECT } [ ( length [{ K | M | G }] ) ] 
CREATE TABLE clob_data(id INT primary key, text CLOB(10M)); 
–- search for a clob
select text from clob_data where id = 100;

DATE

Provides for storage of a date as year-month-day. Supported formats are:

yyyy-mm-dd 
mm/dd/yyyy 
dd.mm.yyyy 

The year (yyyy) must always be expressed with four digits, while months (mm) and days (dd) may have either one or two digits. DATEs, TIMEs, and TIMESTAMPs must not be mixed with one another in expressions except with an explicit CAST.

Equivalent Java type java.sql.Date
JDBC metadata type java.sql.Types.DATE
JDBC methods ResultSet.getDate, PreparedStatement.setDate
Equivalent .NET type System.DateTime
ADO.NET metadata type Vmware.Data.SQLFire.SQLFType.Date
ADO.NET mapped System.Data.Dbtype Date
ADO.NET getter DbDataReader.GetDateTime
VALUES '2010-05-04'
VALUES DATE('2001-10-12')

The latter example uses the DATE() function described in the section Built-in functions and procedures.

DECIMAL

Provides an exact decimal value having a specified precision and scale. The precision is the total number of digits both to the left and the right of the decimal point, and the scale is the number of digits in the fraction to the right of the decimal point.

A numeric value (e.g. INT, BIGINT, SMALLINT) can be put into a DECIMAL as long as non-fractional precision is not lost else a range exception is thrown (SQLState: “22003�?). When truncating trailing digits from a DECIMAL, the value is rounded down.

For behavior with other types in expressions, see Numeric type promotion in expressions, Scale for decimal arithmetic and Storing values of one numeric data type in columns of another numeric data type.

Equivalent Java type java.math.BigDecimal
Precision min/max 1 to 31
Scale min/max less than or equal to precision
Default precision 5
Default scale 0
JDBC metadata type java.sql.Types.DECIMAL
JDBC methods ResultSet.getBigDecimal, PreparedStatement.setBigDecimal
Equivalent .NET type System.Decimal
ADO.NET metadata type Vmware.Data.SQLFire.SQLFType.Decimal
ADO.NET mapped System.Data.Dbtype Decimal
ADO.NET getter DbDataReader.GetDecimal
{ DECIMAL | DEC } [(precision [, scale ])]
-- this cast loses fractional precision 
values cast (23.8372 AS decimal(4,1)); 
-–- results in: 
23.8 
-- this cast is outside the range 
values cast (97824 AS decimal(4,1)); 
–-- throws exception: 
ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(4,1). 

DOUBLE

Provides 8-byte storage for numbers using IEEE floating-point notation.

Arithmetic operations do not round their resulting values to zero. If the values are too small, you will receive an exception. Numeric floating point constants are limited to a length of 30 characters.

For behavior with other types in expressions, see Numeric type promotion in expressions, and Storing values of one numeric data type in columns of another numeric data type.

Equivalent Java type java.lang.Double
Note: The maximum/minimum limits are different from those of java.lang.Double as noted below.
Minimum value -1.79769E+30
Maximum value 1.79769E+308
Smallest positive value 2.225E-307
Largest negative value -2.225E-307
Default precision 5
Default scale 0
JDBC metadata type java.sql.Types.DOUBLE
JDBC methods ResultSet.getDouble, PreparedStatement.setDouble
Equivalent .NET type System.Double
ADO.NET metadata type Vmware.Data.SQLFire.SQLFType.Double
ADO.NET mapped System.Data.Dbtype Double
ADO.NET getter DbDataReader.GetDouble
–- examples of valid values 
values 233.31E3; 
values 8928E+06; 
-- this example will throw a range exception (SQLState: �?42820�?) 
values 123456789012345678901234567890123456789e0; 

DOUBLE PRECISION

Synonyn for DOUBLE.

FLOAT

Alias for a REAL or DOUBLE data type, depending on the specified precision. The default precision is 53 making it equivalent to DOUBLE. A precision of 23 or less makes FLOAT equivalent to REAL while greater than 23 makes it equivalent to DOUBLE.

Equivalent Java type java.lang.Double or java.lang.Float depending on precision
Minumum/Maximum limits Same as those for FLOAT if the precision is less than 23. Otherwise, same minimum/maximum limits as those for DOUBLE.
Default precision 53
JDBC metadata type java.sql.Types.FLOAT
JDBC methods ResultSet.getFloat/getDouble, PreparedStatement.setFloat/setDouble
Equivalent .NET type System.Double or System.Single
ADO.NET metadata type Vmware.Data.SQLFire.SQLFType.Float
ADO.NET mapped System.Data.Dbtype Double
ADO.NET getter DbDataReader.GetFloat/GetDouble
FLOAT [(precision)]

INTEGER

Provides 4 bytes storage for integer values. INT can be used as a synonym for INTEGER in CREATE TABLE.

For behavior with other types in expressions, see Numeric type promotion in expressions, and Storing values of one numeric data type in columns of another numeric data type.

Equivalent Java type java.lang.Integer
Minimum value java.lang.Integer.MIN_VALUE (-2147483648)
Maximum value java.lang.Integer.MAX_VALUE (21474836487)
JDBC metadata type java.sql.Types.INTEGER
JDBC methods ResultSet.getInt, PreparedStatement.setInt
Equivalent .NET type System.Int32
ADO.NET metadata type Vmware.Data.SQLFire.SQLFType.Integer
ADO.NET mapped System.Data.Dbtype Int32
ADO.NET getter DbDataReader.GetInt32

LONG VARCHAR

Identical to VARCHAR except that the maximum length does not have to be specified and is 32700.

Equivalent Java type java.lang.String
Maximum length 32700
JDBC metadata type java.sql.Types.LONGVARCHAR
JDBC methods ResultSet.getString, PreparedStatement.setString
Equivalent .NET type System.String
ADO.NET metadata type Vmware.Data.SQLFire.SQLFType.LongVarChar
ADO.NET mapped System.Data.Dbtype String
ADO.NET getter DbDataReader.GetString

LONG VARCHAR FOR BIT DATA

Identical to VARCHAR FOR BIT DATA except that the maximum length does not have to be specified and is 32700.

Equivalent Java type use byte[] or java.io.InputStream / java.lang.OutputStream as required
Maximum length 32700 bytes
JDBC metadata type java.sql.Types.LONGVARBINARY
JDBC methods ResultSet.getBytes, PreparedStatement.setBytes
Equivalent .NET type use byte[] where required
ADO.NET metadata type Vmware.Data.SQLFire.SQLFType.LongVarBinary
ADO.NET mapped System.Data.Dbtype Binary
ADO.NET getter DbDataReader.GetBytes

NUMERIC

Synonym for DECIMAL data type.

The meta-data differences from DECIMAL are listed below. Otherwise, NUMERIC behaves identically to DECIMAL.

JDBC metadata type java.sql.Types.NUMERIC
ADO.NET metadata type Vmware.Data.SQLFire.SQLFType.Numeric
ADO.NET mapped System.Data.Dbtype Decimal
NUMERIC [(precision [, scale ])]

REAL

Provides 4-byte storage for numbers using IEEE floating-point notation.

Arithmetic operations do not round their resulting values to zero. If the values are too small, you will receive an exception. Constants always map to DOUBLE – use an explicit CAST to convert a constant to REAL.

For behavior with other types in expressions, see Numeric type promotion in expressions, Storing values of one numeric data type in columns of another numeric data type.

The maximum/minimum limits are different from those of java.lang.Float as noted below.

Equivalent Java type java.lang.Float
Minimum value -3.402E+38
Maximum value 3.402E+38
Smallest positive value 1.175E-37
Largest negative value -1.175E-37
JDBC metadata type java.sql.Types.REAL
JDBC methods ResultSet.getFloat, PreparedStatement.setFloat
Equivalent .NET type System.Single
ADO.NET metadata type Vmware.Data.SQLFire.SQLFType.Real
ADO.NET mapped System.Data.Dbtype Single
ADO.NET getter DbDataReader.GetFloat
–- examples of valid values 
values cast (233.31E3 as REAL); 
values cast (8928E+06 as REAL); 
-- this example will throw a range exception (SQLState: �?42820�?) 
values cast (123456789012345678901234567890123456789e0 as REAL);

SMALLINT

Provides 2 bytes storage for short integer values.

For behavior with other types in expressions, see Numeric type promotion in expressions, and Storing values of one numeric data type in columns of another numeric data type.

Equivalent Java type java.lang.Short
Minimum value java.lang.Short.MIN_VALUE (-32768 )
Maximum value java.lang.Short.MAX_VALUE (32767)
JDBC metadata type java.sql.Types.SMALLINT
JDBC methods ResultSet.getShort, PreparedStatement.setShort
Equivalent .NET type System.Int16
ADO.NET metadata type Vmware.Data.SQLFire.SQLFType.Short
ADO.NET mapped System.Data.Dbtype Int16
ADO.NET getter DbDataReader.GetInt16

TIME

Provides for storage of time of day as hour-minutes-seconds. Supported formats are:

hh:mm[:ss] 
hh.mm[.ss] 
hh[:mm] {AM | PM} 

Hours (hh) may have one or two digits, while minutes (mm) and seconds (ss), if present, must have two digits. DATEs, TIMEs, and TIMESTAMPs must not be mixed with one another in expressions except with an explicit CAST.

Equivalent Java type java.sql.Time
JDBC metadata type java.sql.Types.TIME
JDBC methods ResultSet.getTime, PreparedStatement.setTime
Equivalent .NET type System.DateTime
ADO.NET metadata type Vmware.Data.SQLFire.SQLFType.Time
ADO.NET mapped System.Data.Dbtype Time
ADO.NET getter DbDataReader.GetDateTime
VALUES '13:23:12' 
VALUES TIME('13:23:12')

The latter example uses the TIME() function described in the section Built-in functions and procedures.

TIMESTAMP

Provides for storage of both DATE and TIME as a combined value. In addition it allows for fractional seconds having up to six digits. Supported formats are:

yyyy-MM-dd hh:mm:ss[.nnnnnn] 
yyyy-MM-dd-hh.mm.ss[.nnnnnn] 

The year (yyyy) must always be expressed with four digits. Months (MM), days (dd), and hours (hh) may have one or two digits while minutes (mm) and seconds (ss) must have two digits. Microseconds, if present, may have between one and six digits. DATEs, TIMEs, and TIMESTAMPs must not be mixed with one another in expressions except with an explicit CAST.

Equivalent Java type java.sql.Timestamp
JDBC metadata type java.sql.Types.TIMESTAMP
JDBC methods ResultSet.getTimestamp, PreparedStatement.setTimestamp
Equivalent .NET type System.DateTime
ADO.NET metadata type Vmware.Data.SQLFire.SQLFType.TimeStamp
ADO.NET mapped System.Data.Dbtype DateTime
ADO.NET getter DbDataReader.GetDateTime
VALUES '2000-02-03 12:23:04' 
VALUES TIMESTAMP(' 2000-02-03 12:23:04.827') 
VALUES TIMESTAMP('2000-02-03 12:23:04')

The latter examples use the TIMESTAMP() function described in the section Built-in functions and procedures.

User-Defined Types

SQLFire allows you to create user-defined types. A user-defined type is a serializable Java class whose instances are stored in columns. The class must implement the java.io.Serializable interface. For efficient data transport and storage, the class should also implement the java.io.Externalizable interface. If the class implements the vFabric GemFire com.gemstone.gemfire.DataSerializable interface, then it will be used for optimal storage and peer-to-peer transport.

Note: You cannot register a custom .NET type as a user-defined type in SQLFire.

For information on creating and removing types, see CREATE TYPE and DROP TYPE.

For information on writing the Java classes that implement user-defined types, see Programming User-Defined Types.

VARCHAR

Provides for variable-length strings with a maximum limit for length. If a string value is longer than the maximum length, then any trailing blanks are trimmed to make the length same as the maximum length, while an exception is raised if characters other than spaces are required to be truncated. When mixing CHARs and VARCHARs in expressions, the shorter value is padded with spaces to the length of longer string.

The type of a string constant is CHAR, not VARCHAR. To represent a single quotation mark within a string, use two quotation marks:

VALUES 'going to Chandra''s place' 

The length of VARCHAR is an unsigned integer constant.

Equivalent Java type java.lang.String
Maximum length 32672
JDBC metadata type java.sql.Types.VARCHAR
JDBC methods ResultSet.getString, PreparedStatement.setString
Equivalent .NET type System.String
ADO.NET metadata type Vmware.Data.SQLFire.SQLFType.VarChar
ADO.NET mapped System.Data.Dbtype String
ADO.NET getter DbDataReader.GetString
{ VARCHAR | CHAR VARYING | CHARACTER VARYING }(length)

VARCHAR FOR BIT DATA

Provides for variable-length byte strings with a maximum limit for length. This is used when character types are not appropriate due to unstructured data or otherwise. No padding is performed for comparisons between CHAR FOR BIT DATA and VARCHAR FOR BIT DATA types as per SQL-92 standard (this is different from the way some DBMSs behave). An operation involving a VARCHAR FOR BIT DATA and CHAR FOR BIT DATA yields a value of type VARCHAR FOR BIT DATA.

The type of a byte literal is always a VARCHAR FOR BIT DATA, not a CHAR FOR BIT DATA.

The length is an unsigned integer constant for the length of value in bytes.

Equivalent Java type use byte[] or java.io.InputStream / java.lang.OutputStream as required
Maximum length 32672 bytes
JDBC metadata type java.sql.Types.VARBINARY
JDBC methods ResultSet.getBytes, PreparedStatement.setBytes
Equivalent .NET type use byte[] where required
ADO.NET metadata type Vmware.Data.SQLFire.SQLFType.VarBinary
ADO.NET mapped System.Data.Dbtype Binary
ADO.NET getter DbDataReader.GetBytes
{ VARCHAR | CHAR VARYING | CHARACTER VARYING }[(length)] FOR BIT DATA 

XML

This type can be used for Extensible Markup Language (XML) documents:
  • to store XML documents that conform to the SQL/XML definition of a well-formed XML(DOCUMENT(ANY)) value
  • Transiently for XML(SEQUENCE) values, that might not be well-formed XML(DOCUMENT(ANY)) values
Note:

SQLFire does not support the SQLXML type from the SQL standard, or the java SQLXML type, rather the values are stored as normal strings and user must use XMLSERIALIZE, XMLPARSE functions explicitly for inserts and queries as appropriate.

For an application to execute XML related functions, the application must either use a JDK having inbuilt JAXP/Xalan support (e.g. Sun JDK5) or must have classes for a JAXP parser and for Xalan in the server classpath (serializer.jar, xalan.jar).

To fetch XML values from a SQLFire database using JDBC or ADO.NET drivers, use the XMLSERIALIZE operator in the SQL query. For example:

SELECT XMLSERIALIZE(doc as CLOB) FROM t1

Then retrieve the XML value by using the ResultSet.getXXX method that corresponds to the target serialization type (e.g. getClob in above example using CLOB data types). To store an XML value into a SQLFire database using JDBC, use the XMLPARSE operator in the SQL statement. For example:

INSERT INTO t1(doc) VALUES XMLPARSE( DOCUMENT CAST (? AS CLOB) PRESERVE WHITESPACE)

Then use any of the PreparedStatement.setXXX methods that are compatible with String types (e.g. in the above example use PreparedStatement.setString or PreparedStatement.setCharacterStream).

Equivalent Java type java.lang.String; use explicit XML functions for inserts/queries as explained above
Maximum length java.lang.Integer.MAX_VALUE (2147483647)
JDBC metadata type none
JDBC methods as per the target serialization type
Equivalent .NET type System.String; use explicit XML functions for inserts/queries as explained above
ADO.NET metadata type none
ADO.NET mapped System.Data.Dbtype none
ADO.NET getter DbDataReader.GetString