Welcome Guest! Log in
Stambia versions 2.x, 3.x, S17, S18, S19 and S20 are reaching End of Support January, 15th, 2024. Please consider upgrading to the supported Semarchy xDI versions. See Global Policy Support and the Semarchy Documentation.

The Stambia User Community is moving to Semarchy! All the applicable resources have already been moved or are currently being moved to their new location. Read more…

This article explains how to work with ODBC datasources within Stambia DI.

You'll learn how to install, configure, and create your first Metadata with examples.

 

This article is dedicated to Stambia DI S17, S18 and S19.

If you are using Stambia DI 2020 (S20.x.x) or higher, please refer to this article instead.

 

This article is a Work In Progress. We might also change its structure and category soon.

Please feel free to suggest improvements at the bottom of the page.

 

Choosing to use ODBC

Stambia DI is made with Java. Most database vendors provide JDBC drivers, so the most natural choice is to use these JDBC drivers instead of ODBC.

However, some technologies have no JDBC drivers.

Stambia DI comes with native drivers for some of these technologies:

  • Text files (text, csv, positional, ...) => Stambia DI File Driver (shipped by default with Stambia DI Runtime)
  • Structured files (hierarchical, xml, json, ...) => Stambia DI File Driver (shipped by default with Stambia DI Runtime)
  • Microsoft Excel ".xlsx" files => Stambia DI Excel Driver (shipped by default with Stambia DI Runtime)
  • For these technologies, prefer Stambia DI's driver instead of ODBC.

Other technologies (no Jdbc driver, no Stambia DI driver) may require to use an ODBC datasource. For example Microsoft Access Databases, Excel "xls" files, Dbase, Hyperfile, etc.

This article illustrates how to setup Stambia for working with ODBC datasources.

About the JDBC-ODBC Bridge

Stambia DI can work with ODBC datasources, using Java's "JDBC-ODBC Bridge". This is a Java component available in Oracle Java 7, which interacts with the operating system's native ODBC drivers.

JDBC ODBC Bridge

With Java 7, the JDBC-ODBC Bridge is included and will work instantly in Stambia DI.

Since Java 8, Oracle removed the "JDBC-ODBC Bridge" component from their Java distributions. In this case, Stambia DI's "JDBC-ODBC Bridge Wrapper" enables using Java 7's JDBC-ODBC Bridge with Java 8.

Requirements

  • A working ODBC datasource
  • Use the same architecture as your ODBC Datasource:
    • 32-bit ODBC Datasource => Use Java 32-bit and Stambia Designer 32-bit
    • 64-bit ODBC Datasource => Use Java 64-bit and Stambia Designer 64-bit
  • Additional requirements when using Java 8 or higher
    • Stambia Designer S18.0.3 or higher
    • Stambia Runtime S17.3.0 or higher
    • Java libraries copied from a Java 7 distribution

Installing the JDBC-ODBC Bridge libraries

This step is necessary only when using Java distributions which do not include the JDBC-ODBC Bridge.

For example, it is necessary with Oracle Java 8 and above.

Checking if the JDBC-ODBC Bridge is installed

To check if the JDBC-ODBC Bridge is already installed in the Runtime's JVM, you can follow the steps below:

  1. Create a process with a Scripting action (rhino)
  2. The Rhino code will be:
    java.lang.Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
  3. Execute this process
  4. If there is no error, then you do not need to install the libraries
  5. If there is a "java.lang.ClassNotFoundException: sun.jdbc.odbc.JdbcOdbcDriver" error, then proceed with the following installation steps

Preparation

If the JDBC-ODBC Bridge is not installed, the first step is to get the following files from a Java 7 distribution:

  • jre/lib/rt.jar
  • jre/bin/JdbcOdbc.dll (for Windows) or jre/bin/JdbcOdbc.so (for Linux)

Then copy them to a location that the Designer and Runtime can access.

Do not put them inside the Runtime directory, because this could cause conflicts with the Runtime's JVM classes.
Do not put them inside your Java installation directory, it would break your Java installation.

Runtime Configuration

Now that you have the two required files, you can configure the Runtime to use them.

  1. Open the properties/engineParameters.xml file
  2. Add the two following Java additional options
  3. Restart the Runtime
<javaConfiguration>
        <property name="jdbc.odbc.wrapped.lib.path" value="F:/Apps/Java/1.7.0_45_jdk_32/jre/bin/JdbcOdbc.dll"/>
        <property name="jdbc.odbc.wrapped.jar.path" value="F:/Apps/Java/1.7.0_45_jdk_32/jre/lib/rt.jar"/>
</javaConfiguration>

 

Refer to this article for more information about adding Java Options at the Runtime's startup.

That's it! The Runtime is now configured to access ODBC datasources.

 

This procedure is valid for Runtime versions S17.4.0 or higher.

For prior versions, the options must be added manually in the startup scripts of the Runtime.

If you need help to do that, contact the support team.

 

Designer Configuration

Similarly, the Designer need to be configured to use these files if you want to make Reverse / SQL operations on the ODBC datasource from the Designer.

For this, append the following lines to your stambia.ini (or stambia32.ini) file:

-Djdbc.odbc.wrapped.lib.path=F:/Apps/Java/1.7.0_45_jdk_32/jre/bin/JdbcOdbc.dll
-Djdbc.odbc.wrapped.jar.path=F:/Apps/Java/1.7.0_45_jdk_32/jre/lib/rt.jar

 

Then restart the Designer and open Window/Preferences/SQL Editor/JDBC Drivers

  • If the "JDBC ODBC Bridge Wrapper" entry does not exist then create if with the "Add" button:

Name: JDBC ODBC Bridge Wrapper

Example URL: jdbc:odbc:<alias>

  • Edit the "JDBC ODBC Bridge Wrapper" entry

In the "Extra class path" tab, Add Jar "stambiaRuntime\lib\jdbc\stambia.jdbc.driver.odbc.jar" and click "List drivers"

The Driver class name should be automatically set to "com.stambia.jdbc.odbc.JdbcOdbcDriverWrapper"

driver 1

 

Example: Access and Excel/xls datasources

In this example, we will use a Microsoft Access and a Microsoft Excel "xls" file.

Here is their corresponding ODBC datasources:

odbc dsn

 

Creating the Metadata

Access Metadata

Create a new "Microsoft Access" metadata file

  • Name: AccessDatabase
  • Driver:
    • Java 8 and above: select "JDBC ODBC Bridge Wrapper" => class "com.stambia.jdbc.odbc.JdbcOdbcDriverWrapper"
    • Java 7: select "JDBC ODBC Bridge" => class "sun.jdbc.odbc.JdbcOdbcDriver"
  • URL: jdbc:odbc:Access Database
  • You can use the wizard to reverse tables

access server

 

Excel XLS Metadata

Create a new "Microsoft Excel" metadata file

  • Name: ExcelWorkbook
  • Driver:
    • Java 8 and above: select "JDBC ODBC Bridge Wrapper" => class "com.stambia.jdbc.odbc.JdbcOdbcDriverWrapper"
    • Java 7: select "JDBC ODBC Bridge" => class "sun.jdbc.odbc.JdbcOdbcDriver"
  • URL: jdbc:odbc:Excel XLS File
  • You can also use the wizard to reverse tables

excel metadata

 

 

Articles

Suggest a new Article!