Database and JDBC Driver Compatibility


General

AppThena converts tables into types automatically. Whether it can use a particular table or not depends on the table definition and the degree of support provided by the JDBC driver.

Table Restrictions

The table must be a "normal" relational table. In other words, the table must take the form of a list of columns and it must be possible to read and modify the table using standard SQL statements. Other sorts of tables such as object tables, nested tables and vendor specific extensions masquerading as tables are not supported.

The table must have a primary key or a suitable substitute. This version of AppThena supports unique indices and auto-increment columns as substitutes.

Column Restrictions

AppThena supports any column definition as long it contains a single value which can be mapped to a standard Java type. This means that object types and nested tables are not supported.

Furthermore, the following columns are not supported in this version of AppThena.

  • REF columns
  • BLOB columns that must be handled with the java.sql.Blob class
  • CLOB columns that must be handled with the java.sql.Clob class

Note that BLOB and CLOB columns are supported in most cases because the JDBC driver can treat them as byte[] and Strings respectively.

Reference Configurations

The table below lists the database servers and JDBC drivers that are used as reference configurations for testing AppThena. The reference configurations are usually chosen because they are the most recent production quality versions of the most significant databases with their standard JDBC drivers. Our goal is to make AppThena work as seamlessly as possible with these servers by pre-configuring AppThena for them in each release.

Your server and JDBC should work with AppThena even if they are not in this list. You may need to modify your application configuration file to work around any peculiarities that the database or driver have. Please if you are having problems with the application configuration.

The standard configurations are:

Database Server Version JDBC Driver Version Comments
Apache Derby 10.3 Apache Derby 10.3 Network Client JDBC Driver 10.3 Network server version.
Microsoft SQL Server 2005 Express Edition SP2 Microsoft SQL Server 2005 JDBC Driver 1.1
MySQL 5 5.0.27 Connector/J 5.0.6
Oracle 10g Express Edition 2 Oracle Thin Driver 10.2 Not supported in Beta 2.
Will be supported before version 1 is released.

We will be happy to add other databases and drivers to the list of reference configurations if there is sufficient demand. Please if you'd like us to add a particular configuration.

Apache Derby 10.3 with Network Client JDBC Driver 10.3

Summary

All column types except XML are supported. The XML type is not supported because the JDBC driver does not support it.

CLOB and BLOB columns are handled out of the box. They are treated as String and byte[] respectively.

All tables are supported as long as they have a primary key or a suitable substitute.

Known Issues

Data Types

The XML data type is not supported.

Apache Derby 10.2 with Network Client JDBC Driver 10.2

Summary

All column types except XML are supported. The XML type is not supported because the JDBC driver does not support it.

CLOB and BLOB columns are handled out of the box. They are treated as String and byte[] respectively.

All tables are supported as long as they have a primary key or a suitable substitute.

This version of the Derby JDBC driver does not identify auto-increment columns correctly. This causes problems with databases where some tables use auto-increment columns as primary keys and some use plain integers. The Derby 10.3 JDBC driver does not suffer from this problem.

Known Issues

Data Types

The XML data type is not supported.

"AS IDENTITY" Columns in Primary Keys

The Derby JDBC driver doesn't report whether or not columns are auto-incrementing. This means that AppThena can't tell whether an integer column was created with "AS IDENTITY" or not preventing it from working correctly if some tables have auto-incrementing primary keys and some don't. You may need to change some settings in the application configuration file (appthena.xml) in order to make AppThena work with your database.

This table tells you which settings to use and what effect it has on different types of primary key.

id-generator auto-increment PK is Integer With GENERATED ALWAYS AS IDENTITY PK is Integer With GENERATED BY DEFAULT AS IDENTITY PK is Plain Integer Other Primary Keys
auto heuristic Treated correctly as an auto-increment column Treated correctly as an auto-increment column Not supported Works correctly
user auto Not supported Treated as a user editable field Treated correctly as a user editable field Works correctly
max-id auto Not supported Works correctly. AppThena chooses value. Works correctly. AppThena chooses value. Works correctly

There are no settings that will enable AppThena to work correctly if some tables' primary keys were created with "GENERATED ALWAYS AS IDENTITY" and some are plain integers that don't use "AS IDENTITY".

AppThena can handle "GENERATED BY DEFAULT AS IDENTITY" primary keys whichever settings you use but it may cause problems if you use the user or max-id id-generator settings and you have another application which treats these columns as auto-incrementing. (See the generated-column-spec topic in the Derby documentation for more details.)

Other "AS IDENTITY" Columns

The limitation with the Derby JDBC driver also affects columns that aren't part of the table's primary key. Columns that aren't part of the primary key but are created with "AS IDENTITY" won't be handled correctly by default. AppThena treats them as properties that can be modified by the user.

You can solve this problem by making the column read only. To do this you must add a type hint to the application configuration file (appthena.xml) and then add a property hint for the column in question. Set the read-only attribute to read_only. e.g.

  <type name="TableWithIdentityColumn">
    <property name="OtherInt" read-only="read_only"/>
  </type>

Apache Derby 10.1 with Network Client JDBC Driver 10.1

This version of Derby is supported. It has exactly the same limitations as version 10.2.

Microsoft SQL Server 2005 with SQL Server JDBC Driver 1.1

Summary

All column types, including computed columns, are supported except for SQL_VARIANT which is not supported by the JDBC driver.

All tables are supported as long as they have a primary key or a suitable substitute.

Known Issues

Data Types

The SQL_VARIANT data type is not supported.

MySQL 5.0 with Connector/J 5.0

Summary

All column types are supported including BLOB and its variants. They are handled as byte[] rather than java.sql.Blob objects.

All tables are supported as long as they have a primary key or a suitable substitute.

Known Issues

This list of issues refers to MySQL 5.0.27 and Connector/J 5.0.6.

YEAR Columns

AppThena respresents database columns of type YEAR as short integers. The JDBC driver configuration setting called yearIsDateType has no effect on this.

Bit Field Columns

Columns of type BIT(M) where M is > 1 represent bit fields. The display length for these fields is too large due to a bug in the JDBC driver. Please set the display size of these columns in a property hint. The display size should be M/4.

Searching doesn't work correctly due to another JDBC driver bug.

For these reasons, we recommend that you use BINARY or VARBINARY instead of BIT(M) if possible.

Please note that these problems only occur when M is > 1. The BIT(1) column and its aliases BOOL, BOOLEAN and TINYINT(1) work correctly.

Time Columns

Although the MySQL TIME data type contains a period of time in the range -838:59:59 to 838:59:59, the JDBC driver restricts this type to times of day from 00:00:00 to 23:59:59. Consequently, AppThena is also restricted to times from 00:00:00 to 23:59:59.

The JDBC driver will throw an exception if it attempts to load a TIME value which is not a valid time of day.