Certain ADELIA data types behave differently with different database management systems.
Alphanumerical type
Oracle
Oracle considers empty strings as NULL values. In order to avoid conflicts with the constraint NOT NULL WITH DEFAULT, the default value with character type fields is set as a space character (" "), rather than an empty string.
Character data objects more than 2,000 characters long are stored in LONG (Oracle 8i or <) or CLOB (Oracle 9 or >) columns. Such data inherits the limitations inherent to this ORACLE data type (cf. Image type).
SQL Server
Character data objects more than 8,000 characters long are stored in TEXT columns, which may restrict the use of certain SQL functions (cf. Image type).
Unicode alphanumerical type
Oracle
Unicode types are stored as NCHAR/NVARCHAR. Accordingly, the database must have been created with a UCS 2-compatible national character set (preferably AL16UTF16).
DB2-UDB
Using Unicode requires DB2 v8.1 or above, and a Unicode database must have been created. Use of DB2 v9.5 is recommended (as this version fixes certain inconsistencies involving character management functions).
Important: In a Unicode DB2 base, the CHAR type is treated as a byte but is stored in UTF-8. This can lead to problems when handling non-ASCII characters that are coded on multiple bytes: for example, storing a string containing five accented characters (éèêàô) requires a CHAR(10). In addition, with DB2 versions earlier than v9.5, the "length" SQL scalar function returns a number of bytes (length ('éèêàô') = 10 in a Unicode database).
Image type
Image types are interpreted differently, depending on the DBMS used.
Oracle
Version 9i or above
The image type is implemented using Oracle's BLOB type. There are certain limitations on how this data can be manipulated, but they are not very restrictive.
Version 8i or lower
SQL grammar is very limited in respect of the LONG RAW type (used to implement the Adelia image type): there can be only one such column in a table, and that column can only be referenced by one SELECT query.
SQL Server and DB2
There are a few restrictions concerning the manipulation of these data items, but these limitations rarely cause problems.
Date type
SQL Server
The DATETIME type has a value interval of 1/1/1753 - 31/12/9999. Adelia's *LOVAL value (January 1st, in the year zero) is not recognized. Consequently, you MUST initialize all the date variables used in your programs.
Timestamp type
Oracle
Oracle does not have a TIMESTAMP type. Adelia TIMESTAMP values are therefore generated using the DATE type, which is only accurate to the nearest second.
SQL Server
SQL Server does not have a TIMESTAMP type. Adelia TIMESTAMP values are therefore generated using the DATETIME type, which is only accurate to within 3.33 milliseconds.
Numerical auto increment type
An auto increment-type column is a numerical integer column whose value is automatically generated by the DBMS. Generally speaking, this column does not have to be specified in an INSERT or UPDATE instruction. This type of column is used to automatically generate a key.
Important: the value of the auto increment can only be retrieved after an INSERT instruction (option *GET_GEN_VAL of CREATE_SQL) for DBMS systems managed by Adelia via native drivers (C, JDBC or AS/400). Similarly, the auto increment type is only supported for SQL generations.
Oracle
The auto increment type is emulated by a trigger and a sequence generated by the generation of the Adelia PDM. Values can be inserted explicitly in the column, but the sequence is not updated automatically. This means that duplicate keys can be created.
The auto increment type is not supported in the mobile variant of Oracle (Oracle Lite).
DB2-UDB
The auto increment type is supported by the DBMS (GENERATED BY DEFAULT AS IDENTITY). Values can be inserted explicitly in the column, but the sequence is not updated automatically. This means that duplicate keys can be created.
The auto increment type is supported in the mobile variant of DB2 (DB2 Everyplace).
SQL Server
The auto increment type is supported by the DBMS (IDENTITY). Values cannot be inserted explicitly in the column unless you activate the IDENTITY INSERT (SET IDENTITY INSERT <TableName> ON) flag on the table.
In this case, the sequence is updated automatically.
The auto increment type is supported in the mobile variant of SQL Server (SQL Server Compact Edition).
MySQL
The auto increment type is supported by the DBMS (AUTO_INCREMENT). Values can be inserted explicitly in the column. In this case, the sequence is updated automatically.
Note: on MySQL, the auto increment-type column must be defined as the first element in the table's primary key (Identifier 1 and generation of the table with a key).
Microsoft Access
The auto increment type is supported by the DBMS (AutoNumber or COUNTER). Values can be inserted explicitly in the column. In this case, the sequence is updated automatically.
Note: on Access, the auto increment type is necessarily a four-byte binary. In terms of execution precision, you are thus limited to a numerical value (9,0) regardless of the Adelia definition.
DB2/400
The auto increment type (GENERATED BY DEFAULT AS IDENTITY) can only be used with OS/400 version V5R2MO or higher.
PostgreSQL
The auto increment type is supported by the DBMS (GENERATED BY DEFAULT AS IDENTITY). Values can be inserted explicitly in the column but the sequence is not updated automatically. This means that duplicate keys can be created.