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

    {info} Since version 21.2.0, IJC is fully compatible with MySQL 8, there is no need to adjust a default distribution.

    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 MySQL issues and limitations

    • MySQL tables created with COLLATION uft8_bin will not work. Current workaround is to use different collation. For example utf8_general_ci

    • Can not create IJC Schema on MySQL 5.7. Current workaround is to 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>";

    • In order for IJC to work correctly with MySQL 8 ONLY_FULL_GROUP_BY option has to be Turn off 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 ONLY_FULL_GROUP_BY mode:

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

    • MySQL 8 uses caching_sha2_password rather than mysql_native_password as of MySQL 5.7. As a result you may encounter difficulties when accessing MySQL 8 database.
    images/download/attachments/1803031/ijc.png

    To fix this, you should adjust your database settings, e.g.

     ALTER USER 'user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_new_password';
    
     FLUSH PRIVILEGES;

    For more details see https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password

    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.

    PostgreSQL

    IJC (since version 16.12.26.0 ) is fully compatible with PostgreSQL 9.6. and newer versions.

    IJC (since version 19.15.0) also support JChem PostgreSQL Cartridge

    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

    {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.

    Problems?

    If you find problems with particular database versions then please report them to the CXN support Freshdesk.