Work with ODBC Datasources
This article explains how to work with ODBC datasources in Semarchy xDI.
Overview
Semarchy xDI uses JDBC drivers or Java libraries to communicate with sources and target systems. Some technologies do not support or have this type of connectivity but provide ODBC connectivity. For example, Microsoft Access, Microsoft Excel XLS files, DBase, HyperFile, etc.
Semarchy xDI works with ODBC datasources using the JDBC-ODBC Bridge available in Java. This Java component interacts with the operating system’s native ODBC drivers. The following schema illustrates how this connectivity works:
Requirements
To use an ODBC datasource to connect to a system, you need:
-
An ODBC datasource configured on the machine executing the Runtime.
-
The same architecture for Semarchy xDI and the datasource:
-
For a 32-bit ODBC Datasource: Java 32-bit and Semarchy xDI Designer 32-bit
-
For a 64-bit ODBC Datasource: Java 64-bit and Semarchy xDI Designer 64-bit
-
Create an ODBC Module
Java 8 and above no longer includes the JDBC-ODBC Bridge. To use it with newer Java version, download and install a Java 7 version and retrieve the following files from the Java distribution:
-
jre/lib/rt.jar
-
jre/bin/JdbcOdbc.dll
(Windows) orjre/bin/JdbcOdbc.so
(Linux)
Create an ODBC Bridge module as shown below, and add these libraries to this module. Refer to Manage Modules for more information.
You will use this module when creating your metadata.
Create a Metadata using ODBC
The following example shows the server creation wizard for a Microsoft Access metadata, using the ODBC Bridge Module and ODBC connectivity.
Note that the following properties must be set:
-
Name: Name of the server in the metadata.
-
Driver: The driver must be
com.semarchy.xdi.jdbc.odbc.JdbcOdbcDriverWrapper
-
URL: The URL is in the
jdbc:odbc:<alias>
format. Where<alias>
is the datasource alias defined in the system’s datasource manager.