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:

getting started bridge

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) or jre/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.

getting started use module

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.

getting started microsoft access metadata

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.