Supported Data Types

---

Data Types Supported

As InstantDB is written entirely in Java™, and since it's only interface is via the JDBC™ API, it seemed sensible to make the Java™ data types the basis for InstantDB's data types. So that's exactly what it does. The following data types are available:

SET table1 AUTO INCREMENT OFF

SET table1 AUTO INCREMENT ON

SET INCREMENT_BASE {<base>|MAX} ON {<table>.<column> | ALL}

DECIMAL and NUMERIC types

InstantDB supports fixed precision arithmetic using the CURRENCY type (see below). However, for compatability with other SQL implementations, it also accepts the DECIMAL and NUMERIC data types.

These are not fixed precision data types in InstantDB. Instead they are mapped to INT, LONG or DOUBLE types as follows.

Precision	Scale	Mapped type
none	none	LONG
any	> 0	DOUBLE
1 to 9	none or 0	INT
> 9	none or 0	LONG

Date Type

The DATE type is stored in memory and on disk as a long value, just as Java™ itself does. InstantDB uses its own date conversion methods to translate date and timestamps into long values.

InstantDB provides a non-standard SQL extension to control how dates are formatted for output:

format can include any of: dd, mm, mmm, yy, yyyy in any order, with any seperator character. e.g dd-mmm-yyyy. mm indicates that a 2 character number should be used. mmm indicates that a 3 character string should be used. yy and yyyy indicate 2 and 4 digit years respectively. The default format is "yyyy-mm-dd".

Once this command has been issued, all tables created after it will store this as their date format. Any queries against these tables will use the stored format to represent dates. This command is connection specific, i.e. only tables created on the same database connection will be affected.

Date values can actually hold the time as well as the date. To access this, it is simply a matter of changing the format string to include any of: hh, nn, ss and lll. These represent hours, minutes, seconds and milliseconds respectively. For example

Will cause results from the next date column created to produce output of the form: 13:12:58.626 16/11/1997.

When inserting values into a DATE column, InstantDB also accepts the string "NOW", or "now", to represent the current date. This will include only the date part of the current time if the column format includes only date fields. The full current timestamp can be stored using the string value "current time".

Note that InstantDB does not store nano-second values, only milliseconds. Consequently, when a java.util.Timestamp value is inserted into the database there may be a small rounding error at the millisecond level.

Inserting a literal timestamp into a date column will result in the full timestamp being inserted, regardless of the date format. Timezone differences can also result in unexpected values in a date column. To check what is really in a date column try a command like:

Date Functions

The following functions relate to Date column processing.

Name	Parameters	Example
TO_DATE Returns a date formatted according to format string.	date format	to_date(date1, "hh:nn:ss dd:mm:yyyy")
TO_NUMBER Returns the numeric value of the selected time field. The fields are as defined in java.util.Calendar.	date field	to_number(date1, 10)

Interpreting 2 Digit Dates

By default, all two digit years are interpreted as 20th century years. 21st century years are entered by specifying 4 digits. For many applications this is insufficient. InstantDB therefore allows the property milleniumBoundary to be set in the properties file. When set, this allows two digit dates less than milleniumBoundary to be interpreted as 21st century dates.

e.g. If milleniumBoundary is set to 40, then all two digit dates in the range 00..39 will be interpreted as 2000 through 2039 respectively, and all two digit dates in the range 40..99 will be interpreted as 1940 through 1999 respectively.

Currency Type

Another non-standard extension allows the CURRENCY type to be parsed and formatted:

A currency column is always created with the current currency symbol and number of digits after the decimal point. Once created, it retains those settings, even if new SET CURRENCY statements change the global values.

The default currency symbol is $, and the default number of digits after the decimal point is 2.

When entering currencies, the value can either include or exclude the column's currency symbol. However, if you include the currency symbol then you must also quote the value:

and

are both equivalent (assuming the default global settings).

Currency values are held as appropriately scaled java long values. So the value $300.00 is actually held as 30,000 cents. Where InstantDB can obtain a context for evaluating a currency constant it will apply suitable formatting rules. Thus, the following statements all produce identical results.

1. SELECT * FROM mytable WHERE cost < 30000
2. SELECT * FROM mytable WHERE cost < 300.00
3. SELECT * FROM mytable WHERE cost < '$300.00'

For both DATE and CURRENCY types, the formatting information is held on a per connection basis. So when you perform a SET DATE or SET CURRENCY command, the changes affect only the current connection.

When a CREATE TABLE is subsequently performed, the format settings in effect for the current connection are stored with any date or currency columns that get created. The formatting options do not affect how values are actually stored, only how they are displayed, and in the case of currency columns, how values must be formatted for input.

Note that it is not currenctly possible to have different format settings within the same table. So for example, you couldn't display US Dollars and German Marks in the same table. Similarly, you can't have two date columns in a table where one only displays the date and the other only displays the time.

When a results set is created, the formatting options for any columns in the results set are dependent on the columns from which they were sourced. They are not dependent on the current connection's settings.

BINARY Type

Declaring a column to be of the BINARY type (or one of its equivalents), allows it to hold an aribtrary array of bytes. The JDBC™ driver will return such objects as byte.

Data can be inserted into a binary column using several different conventions.

1. As a string which describes a filename. In this case InstantDB will read the contents of the file as the binary data. e.g. INSERT INTO blobtable VALUES ("c:\example\binary.dat").
2. As a string containing a sequence of integers. e.g. INSERT INTO blobtable VALUES ("0x10, 0x20, 48, 64").
3. As a text string. e.g. INSERT INTO blobtable VALUES ("hello world"). Note that, if you use the TEXT or LONGCHAR pseudonyms for binary columns, InstantDB will assume that you only want literal text in this column, i.e. it will not attempt to interpret the data as numbers or filenames.
4. As a single hex number. e.g. INSERT INTO blobtable VALUES (0001021B1C1D1E1F).
5. Using the PreparedStatement.setBytes() and setObject() methods.

Be careful when using the "0x10, 0x20, 48, 64" style. The resultant values must fall into the normal Java™ byte range -128..127. If the resultant value is outside this range then it will be interpreted as a "short" and saved as two bytes. If outside the short range, then as an "int", and if outside the int range, as a "long".

The table containing the binary column only holds a pointer to the actual binary data. This is held in a separate "blob" file. InstantDB recognises two types of binary data as special: Strings and Java Objects™. Strings are always returned as Java™ String objects. Objects saved using the PreparedStatement.setObject() method are returned in their native Object format. Such objects must implement the java.io.Serializable interface in order to be saved as binary data.

Blobs are cached just like any other data. So be careful when handling large blobs. You might want to explicitly declare blob columns as CACHE 0 ROWS.

Strings

InstantDB can handles strings via the CHAR(n) data type. Strings can be delimited by either a single quote ' or a double quote ". Thus, 'a string' and "a string" are both equivalent. Within a string, the other quote character can be used freely: "Here's a string with a quote". The delimiter used can be included in a string by using a backslash \ as an escape: "He said \"How's That\"".

Remember that Java™ itself recognises the backslash as an escape character. So while the above might be OK for keyboard input, Within a Java™ program you'll need to include a double backslash in order to pass the \ through to InstantDB, and you'll also need to escape the double quotes. So the above would be written in a Java™ program as: "He said \\\"How's That\\\"".

You can also include horizontal tabs and newlines in strings by using the usual \t and \n character sequences.

One more thing about strings. InstantDB supports the non-standard extension IGNORE CASE in LIKE clauses. Thus

String Literals

Some development environments automatically generate SQL statements of the form:

i.e. Both the column name and the target value are quoted. By default InstantDB will accept this and will search the column called mycol for the value 1234.

However this can cause serious problems where a genuine string literal gets mistaken for a column name. For example:

Should this be interpreted as a join on the two columns, or a search for the text "mycol" in myothercol?

To dispense with such ambiguities, InstantDB allows its default behaviour to be modified by setting the property strictLiterals=1 in the database properties file. When set in this way, InstantDB interprets all quoted strings as string literals. The only exceptions to this are DATE strings which can usually be interpreted from context.

The SQL:

SET LITERALS [STRICT_ON|STRICT_OFF]

can be used to change the behavior if required. Note that any such change applies to all active connections, not just the issuing connection. In the above statement, STRICT_OFF is the default.

String Functions

The following functions relate to String column processing.

Name	Parameters	Example
UPPER Returns a string converted to upper case.	string	UPPER("abcd")
LOWER Returns a string converted to lower case.	string	LOWER("ABCD")
SUBSTR Returns a substring of another string.	string start (from 1) length (optional)	SUBSTR("ABCD",1,3)
LENGTH Returns the length of a string.	string	LENGTH("ABCD")