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.
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.
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 SELTESTCommit/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.
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