IDB logo InstantDB

Basic

Home
Features
Installation
README
Data types
Examples

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:

TypeSynonymsComment
BYTE TINYINT 1 byte signed integer
INT INTEGER,SHORT,SMALLINT 4 byte signed integer
LONG 8 byte signed integer
CURRENCY Descended from LONG.
DOUBLE 8 byte floating point
FLOAT 4 byte floating point
DATE Descended from LONG
CHAR VARCHAR Variable length String
BINARY VARBINARY,LONGVARBINARY,
OLE,LONGCHAR,TEXT,IMAGE
Binary data or long text

Numeric Types

The various numeric types follow all the same rules as their Java equivalents, with one exception. The database uses the minumum negative number for BYTE, INT and LONG types as its NULL representation. Any attempt to use the minimum value with these types will result in a SQLException. This does not apply to FLOAT or DOUBLE where "not a number" is used to represent NULL.

There is no seperate COUNTER type. Instead, the column condition AUTO INCREMENT can be added to a column during table creation.

e.g. CREATE TABLE table1 (int1 int AUTO INCREMENT)

The auto-increment condition can be applied to both int and long column types.

It is often convenient to be able to switch AUTO INCREMENT off, e.g. during IMPORT operations. This can be done in InstantDB using the SQL:


SET table1 AUTO INCREMENT OFF

To switch AUTO INCREMENT back on:

SET table1 AUTO INCREMENT ON

Date and Currency Types

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:

SET DATE FORMAT format

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

SET DATE FORMAT "hh:nn:ss.lll dd/mm/yyyy"
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 time.

If a date format is specified that does not include hh (i.e. no hours) then InstantDB does not store a full timestamp in columns created using that format. This allows pure dates to be compared with each other and with the "NOW" keyword.

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.

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

SET CURRENCY {SYMBOL symbol|DECIMAL num. decimal digits}

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:

INSERT INTO curtable VALUES (200.00)

and

INSERT INTO curtable VALUES ('$200.00')

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 knows nothing about the structure of individual blobs of data. The one exception to this is that it does remember when data was input as a human readable string. In this case, it is capable of returning the string representation rather than just an array of bytes.

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

SELECT * FROM mytable WHERE name LIKE "A%" IGNORE CASE

will match all names beginning with upper case 'A' or lower case 'a'. By default, string comparison is case sensitive. You can make all LIKE comparisons case insensitive by including the following line in the database properties.

likeIgnoreCase=1

String Literals

Some development environments automatically generate SQL statements of the form:
SELECT * FROM mytable WHERE "mycol" = "1234"

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:

SELECT * FROM mytable WHERE "myothercol" = "mycol"

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.