/
Supporting multiple DBMS

Supporting multiple DBMS

Owned by Nuno Marques
Last updated: Oct 05, 2017

TermServer currently supports MySQL database management system (DBMS).

 

Even though Hibernate abstract much of the code conversion between different database by using a dialect for each database, issues still arise within the application

Here are some of the issues will adding support for Oracle database.

Reserved Words

  • Oracle has a different set of reserved words than MySQL.  Creating tables will fail if a table name or column name is a reserved word.  Hibernate allows names to be escaped with quotes.  Here are two examples.

In SourceDataFileJpa size should be: 

/** The file size. */
@Column(name = "\"size\"", nullable = false, unique = false)
private Long size;


In TerminologyJpa current should be:

/** The is current. */
@Column(name = "\"current\"", nullable = false)
private boolean current = false;

In WorklistJpa number should be:

/** The number, also the last part of the name. */
@Column(name="\"number\"", nullable = false)
private int number;

Empty Strings

  • Oracle will convert an empty string ("") to a NULL.  This causes failures when loading data as fields that are NOT NULL and have a "" in MySQL cannot have a NULL in Oracle.

FIX: still being tested.

OPTION 1: make affected columns NULL.  This may cause an issue with referential integrity.

OPTION 2: set default value to “ “ (single space) for affected columns.

OPTION 3: there has to be something better than 1 or 2!

Specifying Database Engines

  • In MySQL you can specify which database engine to use.  These engines do not apply to other DBMS expect for MariaDB which is a fork of MySQL before Oracle's purchase and possibly Amazon's Aurora.
  • It is best to avoid using features that are not incorporated into each supported DBMS.
  • However, this may not be possible in every circumstance such using the database functionality to tune its performance.  One option is to create an import.sql file for each supported database to execute platform specific code.

Hibernate Conversion of Java Data Types

  • HIbernate will use annotation in Java code to construct the tables and other objects in the database.  Due to the differences in DBMS not all conversions are the same.  For example, setting a string to length of 4000 will be handled differently in MySQL than Oracle.  Hibernate will use MySQL longtext which does not specify a limit.  The application will succeed inserting a string of 5000 characters even though the maximum length was set to 4000.  In Oracle Hibernate will use VARCHAR2(4000) and the same string of 5000 characters will fail upon insert or update.
  • FIX: Test to find maximum value used by the application and set the length in the java jpa class to a larger value.  For example, if the largest value was 5000 characters and the field was 4000, set the length of the field to 6000-8000 characters.  Valid that all the supported databases are capable of holding the value.