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 2020 (S20.x.x) or higher.
If you are using Stambia DI S17, S18 or S19, 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.
Communicating with databases in Java World is most of the time performed through JDBC Drivers.
Most database vendors provide JDBC drivers, so the most natural choice is to use those JDBC drivers when possible instead of ODBC.
However, some technologies have no JDBC drivers available.
Stambia DI comes with native drivers for some of these technologies.
We therefore advise to use those Stambia drivers for the following 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)
Other technologies (no JDBC driver available, no Stambia DI driver available yet) 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 which interacts with the operating system's native ODBC drivers.
Installation Procedure
You can below the complete installation procedure for being able to work with ODBC datasources under Stambia DI 2020 and higher.
JDBC-ODBC Java Component has been removed from Java 8 and higher versions, which therefore require some additional configuration to be able to use it.
We propose a solution to still be able to use JDBC-ODBC Bridge under Java 8 and higher, which requires some installation steps presented below.
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
Preparation
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)
Those files are the files which contains the necessary to use JDBC-ODBC Bridge.
Simply download a Java 7 version and copy those files somewhere on your file system, we will use them in next step.
As a reminder, this workaround is required as JDBC-ODBC bridge does not exist anymore under Java 8 and higher versions.
ODBC Module creation
Next step is to create an ODBC Module in which we'll add everything required to work with ODBC, which means libraries retrieved in previous step.
Open Stambia DI Designer, create an ODBC Module and add inside the two files retrieved previously.
For further information about Modules creation and usage, you can consult "Getting started with modules" article.
Here is an example of ODBC Module:
That's it, you can now use this ODBC Module in your Metadata when you want to connect through ODBC.
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:
Creating the Metadata
Access Metadata
Create a new "Microsoft Access" Metadata file.
Select the previously created ODBC Module, or create a new one if you have not created it yet.
Finally, define connection properties as below:
- Name: <label for this server>
- Driver: com.stambia.jdbc.odbc.JdbcOdbcDriverWrapper
- URL: jdbc:odbc:<name of the datasource>
For instance, with the Access datasource example:
Excel XLS Metadata
Create a new "Microsoft Excel" Metadata file.
Select the previously created ODBC Module, or create a new one if you have not created it yet.
Finally, define connection properties as below:
- Name: <label for this server>
- Driver: com.stambia.jdbc.odbc.JdbcOdbcDriverWrapper
- URL: jdbc:odbc:<name of the datasource>
For instance, with the Excel datasource example:
