|
Basic Topics | Advanced Topics | Reference Topics |
| Documentation Home Page |
Data Types Supported
Numeric Types
DECIMAL and NUMERIC types
Date Type
Date Functions
Interpreting 2 Digit Dates
Currency Type
BINARY Type
Strings
ASCII Strings
String Literals
String Functions
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,BIT,BOOLEAN | 1 byte signed integer |
| INT | INTEGER,SHORT,SMALLINT | 4 byte signed integer |
| LONG | 8 byte signed integer | |
| DECIMAL | NUMERIC | Varies with specification |
| CURRENCY | Descended from LONG. | |
| DOUBLE | 8 byte floating point | |
| FLOAT | 4 byte floating point | |
| DATE | DATETIME,TIME,TIMESTAMP | Descended from LONG |
| CHAR | VARCHAR,VARCHAR2 | Variable length String |
| SMALLCHAR | Single byte ASCII variable length String | |
| BINARY | VARBINARY,LONGVARBINARY, OLE,LONGCHAR,TEXT,IMAGE |
Binary data or long text |
The various numeric types follow all the same rules as their Java™ equivalents, with one exception. The database uses the minimum 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.
BIT and BOOLEAN can be used as synonyms for the BYTE type. Bit packing is not performed and these synonyms are provided as a convenience only. This type will accept most variations on the English language strings "true" and "false" (ignoring case) as 1 and 0 respectively.
There is no separate 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
By default, all AUTO-INCREMENT int columns start at 1,000, and all AUTO-INCREMENT long columns start at 10,000,000,000. All values increment by 1.
You can alter the base value using the command:
SET INCREMENT_BASE {<base>|MAX} ON {<table>.<column> | ALL}
For example, to set the next auto-inc value to 100 you would issue the command "SET INCREMENT_BASE 100 ON mytable.mycolumn". Sometimes it is necessary to tell a column that it should find its maximum value and then allocate auto-increment values above that. This is particularly useful if a table has been imported from an external source. An example of this would be something like "SET INCREMENT_BASE MAX ON mytable.mycolumn". If a large number of tables have been imported then it might be convenient to tell the database to set all the auto-increment columns to to be one more than their maximum values. This can be done with: "SET INCREMENT_BASE MAX ON ALL".
It is not currently possible to alter the increment step.
InstantDB supports fixed precision arithmetic using the CURRENCY type (see below). However, for compatibility 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 |
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 separator 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 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 nanosecond 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. Time zone differences can also result in unexpected values in a date column. To check what is really in a date column try a command like:
SELECT TO_DATE(date_COL, "hh:nn:ss dd:mm:yyyy")
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) |
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.
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 currently 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.
Declaring a column to be of the BINARY type (or one of its equivalents), allows it to hold an arbitrary array of bytes. The JDBC™ driver will return such objects as byte.
Data can be inserted into a binary column using several different conventions.
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 recognizes 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.
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 recognizes 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
InstantDB uses the Java™ standard char and String types to hold string data. As with all Java™ character based data each character is therefore held as a two byte Unicode character. Translation to and from local single byte character sets is performed automatically by Java™ IO and internationalization functions.
This has the advantage that InstantDB, simply by being written in Java™, can handle a large number of character sets with great ease. However, it has the downside that all string data, whether in memory or on disk, takes two bytes to represent each character. This is typical of Java™ applications.
Despite the very high capacities now available both in terms of disk sizes and memory capacities, there remain some environments, (such as hand held computers, personal organizers and mobile phones) where compactness is more important than inter-nationalisation. In such environments, InstantDB's SMALLCHAR data type can be used.
The SMALLCHAR data type takes a very simplistic approach to handling ASCII characters: it holds the least significant byte of the corresponding Unicode character. This corresponds to the normal ASCII character set and can therefore hold the normal range of Latin character sets.
SMALLCHAR columns can be used in every case where CHAR/VARCHAR columns can be used, with the exception that non-Latin character sets cannot be represented.
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.
Where it is necessary to have mixed interpretations, InstantDB provides the built in function:
TEXT ("literal")
Wherever this appears, the literal value will always be interpreted as a string, and not as a column name.
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") |