Databases

Currently supported or planned

Database Website Java State Remarks
Oracle 9.2.0 (Thin driver) www.oracle.com   Works Varchar null problem
Oracle 8.1.7 (Thin driver) www.oracle.com
Works No outer join
Blob limitation
Microsoft SQL Server 2000 www.microsoft.com   Works  
MySQL www.mysql.com   Works Varchar problem
Transaction support required
EXISTS requires version 4.1
FOREIGN KEY problems
INSERT INTO .. SELECT .. problem
IBM DB2 www.ibm.com   Works  
PostgreSQL www.pgsql.com   Works Commit/Rollback after exception required
Blob limitation
InterBase www.borland.com/interbase   Not tested  
Firebird firebird.sourceforge.net   Not tested  
PointBase www.pointbase.com Yes Works  
HSQLDB hsqldb.sourceforge.net Yes Works Transactions are not isolated
HAVING is not supported
Borland JDataStore www.borland.com/jdatastore Yes Problematic  
FirstSQL www.firstsql.com Yes Problematic  
McKoi www.mckoi.com Yes Problematic  
QED www.quadcap.com Yes Problematic  

You are welcome to add other databases to this list!

For databases with 'Problematic': for known issues see adapter source code.

Oracle

Varchar null problem

In Oracle (and only there), an empty String is the same as NULL.

It is not clear yet how to resolve these issues. One solution would be to always add one character at the end of VARCHAR data (for example, '$') but that would have many problems as well because other functionality such as LENGTH, LIKE would be affected.

Outer Join (8i)

Oracle 8i doesn't support the ANSI SQL-92 Outer Join syntax. At this time, Oracle 9i is required when using Outer Join. However, for Oracle 8i, ANSI SQL-92 Inner Join syntax is converted in the following way:

SELECT * FROM TEST T1 INNER JOIN TEST T2 ON T1.ID=T2.ID WHERE T1.ID=1
is converted to:
SELECT * FROM TEST T1,TEST T2 WHERE (T1.ID=T2.ID) AND (T1.ID=1)

A similar conversion for Outer Join is possible, however it is a little more complex as the ON condition can not be copied as it is, *= or =* must be used:

SELECT * FROM TEST T1 LEFT OUTER JOIN TEST T2 ON T1.ID=T2.ID WHERE T1.ID=1
would become
SELECT * FROM TEST T1,TEST T2 WHERE (T1.ID*=T2.ID) AND (T1.ID=1)

but

SELECT * FROM TEST T1 LEFT OUTER JOIN TEST T2 ON T2.ID=T1.ID WHERE T1.ID=1
would become
SELECT * FROM TEST T1,TEST T2 WHERE (T2.ID=*T1.ID) AND (T1.ID=1)

This conversion is currently not made (however, it can be supported if somebody needs it).

Blob / Clob issues

Both Oralce 8i and Oracle 9i don't directly support updating/inserting Blob or Clob data using the Thin driver (that is, the pure Java driver) if the data is bigger then 4000 bytes (which is very common for Blob and Clob). A workaround for this problem is implemented (using a temporary table). However, for Oracle 8i, the workaround still only supports updating / inserting one Blob or Clob column per PreparedStatement at a time. For Oracle 9i and all other databases, more than one Blob / Clob column can be inserted / updated per PreparedStatement.

MySQL

Installation

In order to test MySQL with LDBC, you will need to do the following after installing MySQL on your machine:

Varchar problem

MySQL currently truncates empty spaces at the end of a VARCHAR.

It is not clear yet how to resolve these issues. One solution would be to always add one character at the end of VARCHAR data (for example, '$') but that would have many problems as well because other functionality such as LENGTH, LIKE would be affected.

Transactions

To use LDBC, the underlying database needs to support transactions. However, transaction support is not enabled in all MySQL versions. MySQL only works with LDBC if the variable 'have_innodb' is set to 'YES'. MySQL 4.0.x supports transactions, but it must be enabled first (version 4.0.3 beta, Windows XP):

If it does not work for you, please check with the MySQL manual to enable transaction support.

EXISTS requires version 4.1

EXISTS and NOT EXISTS is only supported in version 4.1 of MySQL. The problem is, version 4.1 is an alpha version and doens't pass all LDBC test cases.

FOREIGN KEY problems

In some versions of MySQL, it is not possible to create foreign keys with two columns (for example, two tables with two columns defined as the primary key).

DROP FOREIGN KEY doesn't work in all versions of MySQL because it is not possible to retrieve the foreign key name from the DatabaseMetaData. MySQL uses some internal number as the foreign key name, but it is not possible to retrieve this number.

INSERT INTO .. SELECT .. problem

MySQL doesn't support INSERT INTO .. SELECT from the same table. This doesn't work: INSERT INTO SELTEST SELECT ID+1, NAME || '1' FROM SELTEST.

INSERT INTO SELTEST SELECT ID+1, NAME || '1' FROM SELTEST

PostgreSQL

Commit/Rollback after exception required

PostgreSQL has a really strange 'feature': after an exception occured, it is required to issue a commit or rollback. The reason for this behaviour is unknown. Non of the other databases behave in this way. It seems to be a bug in database, and hurts compatibility with other databases a lot. There is currently no workaround implemented in LDBC, and it is questionable if a workaround is possible.

Blob limitation

Trailing zeroes in Blobs (data type BYTEA) are truncated by PostgreSQL.

HSQLDB

No isolation and locking in transactions

The transaction behaviour of HSQLDB is different to most other databases. For HSQLDB, it is theoretically possible that concurrent inserts to tables with autoincrement keys can fail if both explicit values and generated values are used. Therefore, an application should not concurrently update or explicit values to autoincrement columns.

HAVING is not supported

The following SQL statement doesn't work:
SELECT ID, COUNT(*) FROM TEST GROUP BY ID HAVING COUNT(*)>1