Getting Started with Stored Procedures
Databases Stored Procedures can be designed in a specific Metadata and invoked in Mappings as a common datastore, allowing to send input parameters data and retrieving output results.
Add new comment
Due to some maintenance operations, stambia.org will be switched to read-only mode during the 13th November. It will be possible to read and download, but impossible to post on the forums or create new accounts. For any question please contact the support team.
Databases Stored Procedures can be designed in a specific Metadata and invoked in Mappings as a common datastore, allowing to send input parameters data and retrieving output results.
This article demonstrates how to define SQL Queries which will be executed when initializing a connection.
Those user defined queries can be useful when you need to execute automatically the same list of queries each time a connection is initialized, such as defining options, changing current working databases, ...
Read more: Executing SQL Queries at Connection initialization
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.
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:
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.
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.
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.
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.
To check if the JDBC-ODBC Bridge is already installed in the Runtime's JVM, you can follow the steps below:
If the JDBC-ODBC Bridge is not installed, the first step is to get the following files from a Java 7 distribution:
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.
Now that you have the two required files, you can configure the Runtime to use them.
<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.
To verify that it is correctly installed, refer to the previous "checking if the JDBC-ODBC Bridge is installed" step.
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.
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
Name: JDBC ODBC Bridge Wrapper
Example URL: jdbc:odbc:<alias>
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"
In this example, we will use a Microsoft Access and a Microsoft Excel "xls" file.
Here is their corresponding ODBC datasources:
Create a new "Microsoft Access" metadata file
Create a new "Microsoft Excel" metadata file
