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:
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.
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.jdbcmodule as described above.
{primary} 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 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.jdbcmodule as described above.
Oracle 10g's flashback tables (table names beginning with BIN) are ignored by IJC.
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 |
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=utf8and an example connection URL with unicode support will be like:
jdbc:mysql://localhost:3306/ijc_sample?useUnicode=true&characterEncoding=utf8Use 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.
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 |
{primary} 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.
If you find problems with particular database versions then please report them to the CXN support Freshdesk.