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:
Type | Synonyms | Comment |
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.
- SELECT * FROM mytable WHERE cost < 30000
- SELECT * FROM mytable WHERE cost < 300.00
- 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.
- 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").
- As a string containing a sequence of integers. e.g. INSERT INTO
blobtable VALUES ("0x10, 0x20, 48, 64").
- 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.
- As a single hex number. e.g. INSERT INTO blobtable VALUES
(0001021B1C1D1E1F).
- 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.
|