Supported databases

Database support is primarily determined by the JDBC driver compatibility. IJC generally uses recent versions of the drivers and relies on the driver being backwardly compatible for older versions of the databases. The driver version that is used is reported in the Help -> About Instant JChem dialog in IJC. It will report something like this:

org.apache.derby/1 \[10.3.2.1 080529\]

which means the version number is 10.3.2.1.

Precise connection behaviour can be fine-tuned manually in *.ijs file. Please refer to user's guide for the description and examples how to set connection pool.

Whilst it is not possible to test every version or each database, IJC is believed to support the following database versions:

Derby


The Derby database is bundled in IJC as the local database and IJC is tied to the specific current version. You can find out which is the current version by looking at the module version number of the org.apache.derby module as described above.

MySQL


IJC is tested with MySQL version 5.5.

It is recommended to use InnoDB table type. It is not absolutely necessary, but it is still recommended.

You can find out which is the current version by looking at the module version number of the

com.mysql.jdbc

module as described above.

Known issue: Can not create IJC Schema on MySQL 5.7

Workaround: Turn off NO_ZERO_DATE restriction in the MySQL server instance.

There are multiple ways to do that. E.g. find out your settings with: 

SELECT @@GLOBAL.sql_mode;

and then apply new ones without NO_ZERO_DATE mode: 

SET GLOBAL sql_mode = "<new-value-without-the-mode>";

Oracle


Oracle is tested on Oracle 9i upwards (including the XE version). Compatibility depends on the compatibility of the Oracle JDBC driver which is supposed to be backwardly compatible. We have had reports that very early versions of 9i (9.0.1.1.1) do not work due to a driver incompatibility.

You can find out which is the current version by looking at the module version number of the

oracle.jdbc

module as described above.

Oracle 10g's flashback tables (table names beginning with BIN) are ignored by IJC.

Microsoft SQL

IJC 6.2 brings the support for Microsoft SQL server. You can use versions MSSQL 2005 and newer.

Supported MS SQL Server data types:

IJC field type

SQL Server data type

Text field

VARCHAR CHAR NVARCHAR NCHAR IMAGE

Integer number field

INTEGER SMALLINT TINYINT BIGINT DECIMAL NUMERIC

Decimal number field

REAL FLOAT DECIMAL NUMERIC

Boolean field

BIT

Date field

DATETIME

URL field

VARCHAR CHAR TEXT

Binary field

VARBINARY

Unicode support


Whilst traditionally most chemistry data has been described using the ASCII characterset, increasingly this is not the case. And if you are using languages other than those based in western europe this definitely won't be the case. If so, then you will want to ensure that your database supports character sets that go beyond the limited nature of ASCII, and typically that means supporting UNICODE characters. As IJC is based on Java it provides good UNICODE support automatically, but that does not necessarily mean that this support extends to the database. So if your database does not support UNICODE you will see characters being converted to the dreaded "box" symbols or similar.

Full details of character set support is beyond the scope of this document. See the documentation for your particular database for this. But we try to cover some of the basics here.

Oracle and Derby

Normally these databases support Unicode characters "out of the box". If you are using these databases then you should need to do nothing. If you are seeing problems then consult the documentation for your particular type of database.

MySQL

Whilst MySQL has extremely good support for multiple charactersets, somewhat perversely this usually is not enabled by default. There are many approaches possible, but the simplest way is following these two steps:

  • Set the database characterset when creating the database. This must be done before IJC connects to the database. To do this, create the database (e.g. using the msql commandline client) like this:

    CREATE DATABASE ijc_sample CHARACTER SET utf8 COLLATE utf8_general_ci;

    The CHARACTER SET keyword sets the characterset, and the COLLATE keyword sets how characters within that characterset are compared. Consult the MySQL documentation for full details.

  • Add the required parameters to the JDBC connection string. These parameters let the JDBC driver to convert received stream to UTF-8. The required parameters are as follow:

    useUnicode=true&characterEncoding=utf8

    and an example connection URL with unicode support will be like:

    jdbc:mysql://localhost:3306/ijc_sample?useUnicode=true&characterEncoding=utf8

JChem cartridge for Oracle


Use of the JChem cartridge is tested on Oracle 10g and 11g. Oracle 10g with the latest patch sets is recommended as this has been found to be more stable than the 11g release. The JChem cartridge does not run in the XE version of Oracle. See the JChem Oracle Cartridge page for more information on setting up and using the JChem cartridge.

Privileges


The minimum set of privileges required for operation of IJC is as follows:

Oracle

MySQL

create index

select

create sequence

insert

create table

update

create trigger

delete

create session

create

create session

drop

insert table

index

select sequence

alter

select table

 

unlimited tablespace

 

update table

 

These privileges are required for complete operation of IJC, including creating the IJC_* tables when IJC first connects to the database. In some cases, depending on the particular usage that is needed, some of these privileges (e.g. create any index/sequence/table) will not be necessary once the initial setup is complete, and so can be revoked.

When accessing tables from the non-default schema (this is currently only supported for Oracle) particular attention to the privileges of these tables in the foreign schemas is required. select, insert, update and delete privileges are needed on the individual tables, but in addition these privileges are needed:

Table

Privileges

Comment

JCHEMPROPERTIES

select, insert, update, delete

And any additional JChem property tables you might have defined

JCHEMPROPERTIES_CR

select, insert, update, delete

And any additional JChem property tables you might have defined

EACH_JCHEM_TABLE

select, insert, update, delete

select mandatory, others depending on the usage of the table

EACH_JCHEM_TABLE_UL

select, insert, update, delete

select mandatory, others are all necessary if structures are changed in the table, even if done by non-IJC systems

Some additional information on this is available in the user guide.
For JChem cartridge based tables please see the cartridge page for information about privileges.

Problems?

If you find problems with particular database versions then please report them to the IJC forum .