KB180113306: Automation Engine - Problem connecting to database when testing connection to external database (ODBC)

Summary

After upgrading to Automation Engine 16.0, when testing the database connection in the Automation Engine Pilot > Configure, an error appears while connecting to the database:

"Problem connecting to ODBC-Database".

Symptoms

Before upgrading to Automation Engine 16.0, the external Database Connection was working fine and could be used in the SmartNames to extract some information from the database.

After the upgrade to Automation Engine 16.0, this fails and the database SmartNames linked to this database can't be resolved anymore.

Technical background

Starting with Java SE 8, the JDBC-ODBC Bridge will no longer be included with the JDK. The JDBC-ODBC Bridge has always been considered transitional and a non-supported product[1] that was only provided with select JDK bundles and not included with the JRE. The JDBC-ODBC bridge provides limited support for JDBC 2.0 and does not support more recent versions of the JDBC specification. We would recommend that you use a JDBC driver provided by the vendor of your database or a commercial JDBC Driver instead of the JDBC-ODBC Bridge.

Solution

Use a JDBC driver which exists for different types of databases.

Microsoft SQL Server

If your Microsoft SQL Server is configured to use SQL Authentication, go to Automation Engine Pilot > Tools menu > Configure > External Databases and insert a new database connection.

Fill in the details as shown in the following screenshot.

If your Microsoft SQL Server is configured to use Windows Authentication, you need to download the JDBC driver from the Microsoft website (https://www.microsoft.com/en-us/download/details.aspx?id=11774).

Depending on the version of SQL Server, download the JDBC 4.0, 4.1, 4.2 or 6.0 driver and then unpack the files. Place the jar file on bg_custom_v010\dat\Dbase (folder doesn't exists by default) and put the sqljdbc_auth.dll file on \Windows\System32.

Then, go to the Automation Engine Pilot > Tools menu > Configure > External Databases and insert a new database connection.

Instead of using the ODBC source as DBMS Type, choose Other, configure the Database settings as show in the screenshot below.

MySQL Database

To solve this problem, download the latest MySQL Driver (https://dev.mysql.com/downloads/connector/j/5.1.html) and place it on bg_custom_v010\dat\Dbase (folder doesn't exists by default) (Oracle doesn't allow Esko to distribute this driver, but you can download the file for free).

Then go to the Automation Engine Pilot > Tools menu > Configure > External Databases and insert a new database connection.

Instead of using the ODBC source as DBMS Type, choose Other, configure the Database settings and type in the location of the mysql-connectorj.jar file.

For some types of databases, there doesn't exists a JDBC driver, but there is also a commercial driver available from easysoft (http://www.easysoft.com/index.html), which is still developed.

In case of Excel file, you can also export XML files out of Excel, then use XPath SmartName to query the results out of the Excel file. Or export CSV and use the Map Data task to convert the CSV to XML.

Workaround

Install Hotfix HF_2016-Q3-Jul-006_20160712. This will install the Java 7 driver back in Automation Engine 16.0. We, however, cannot guarantee that this driver will continue to work on newer databases or on new operating system. We strongly advice to use a JDBC driver, whenever possible.