Skip to content
This repository has been archived by the owner on Jan 3, 2021. It is now read-only.

Database Compatibility

Jump to bottom
cygri edited this page Jan 24, 2013 · 14 revisions

This page lists known compatibility issues with specific databases.

Microsoft SQL Server

Oracle

  • Exceptions when running {?s ?p "value"} queries that can touch CLOB or BLOB columns: Issue #115
  • Columns of BFILE datatype cannot be used in D2RQ mappings: Issue #114
  • Catch-all issue for unsupported Oracle datatypes: Issue #170

MySQL

  • MySQL TIME can store values in the range from -838:59:59 to 838:59:59. In D2RQ, values between 00:00:00 and 23:59:59 are mapped to xsd:time, while other values are ignored (treated as NULL).
  • MySQL DATE, DATETIME and TIMESTAMP support a special error value consisting of all-zero datetimes. This is treated like NULL by D2RQ.
  • MySQL DATE and DATETIME allow zero as the day or month to indicate an unknown value. This is not supported in the equivalent XSD types. 1979-00-00 is treated as 1978-11-30. The MySQL JDBC driver does not seem to offer any way to distinguish these two values.
  • MySQL YEAR is mapped to an RDF literal "XXXX-01-01"^^xsd:date. That's because the MySQL JDBC driver reports YEAR-typed columns as a DATE of January 1st of the year.
  • Fixed-length CHAR and types have any trailing spaces stripped. This may cause issues with comparisons, joins, etc. on such columns.
  • BIT types have a fixed length in MySQL, and D2RQ returns them as a plain literal bit string of 0s and 1s. But any leading 0s are missing because we cannot work out the declared length of the column.
  • TINYINT(1) is treated as xsd:boolean, as is conventional in MySQL. All other TINYINT types are treated as numbers.
  • ENUM values are mapped to plain literals.
  • SET values are mapped to a single plain literal "value1,value2,value3" where the values are ordered as in the type definition.
  • There might be issues with numeric columns that specify ZEROFILL
  • Spatial extensions not handled

PostgreSQL

HSQLDB

  • See Issue #119 for a list of known datatype compatibility issues.
  • INTERVAL types can be returned in queries (as plain literals), but not compared
  • OTHER and all ARRAY types are not handled
  • HSQLDB 2.2.8 has bugs with zero-length BLOB and CLOB, and with the DOUBLE value NaN, causing trouble in D2RQ. These were since fixed in HSQLDB and should be gone if a later version is used.
  • Frequent problems with DISTINCT when LOB types are used: Issue #4

MS Access

  • Creating mapping file doesn't work if the default JDBC ODBC driver (i.e., sun.jdbc.odbc.JdbcOdbcDriver) is used. The minimum parameters that were used to generate mapping file using generate-mapping was -d sun.jdbc.odbc.JdbcOdbcDriver -o accessMap.ttl jdbc:odbc:accessDSN. The SUN JDBC ODBC driver is included by default if you have SUN JRE installed on your machine. Otherwise, you need to add rt.jar in your classpath. However, if the mapping file is created manually for Access DB then the dump-rdf and d2r-server works fine using the manually created mapping file.
  • One possible way to auto generate mapping file is to use some other JDBC ODBC driver for MS Access. For example, we used the trial version of StelsMDB JDBC ODBC Driver to successfully generate the mapping file. The minimum parameters that can be used to generate mapping file using generate-mapping is -d jstels.jdbc.mdb.MDBDriver2 -o accessMap.ttl jdbc:jstels:mdb:DB.accdb. Further, the dump-rdf and d2r-server also works fine using that particular driver.
  • Reportedly, there are issues with date and time columns: Issue #116

Firebird/Interbase

  • TODO

Sybase

  • Sybase support is being added for D2RQ v1.0
  • We have tested Sybase with jTDS - SQL Server JDBC driver, which also supports Sybase besides SQL Server
  • We have only tested Adaptive Server Enterprise and not the other Sybase products
  • No systematic testing of datatypes has been done; only a verification that generated SQL syntax is correct

ISO standard Core SQL 2008

  • INTERVAL typed values are mapped to plain literals and cannot be compared.
  • ARRAY types cannot be used in D2RQ mappings.

Dealing with datatype issues

D2RQ has poor support for some column types on some databases. Where important data is stored in such columns, the mapping author has to explicitly convert the datatype to another datatype (e.g., to a character string type) in the mapping using d2rq:sqlExpression.

Clone this wiki locally