The OPL-jdbc-data-source project provides database reading capabilities for tuples and tuplesets. This project defines a custom data source in Java that makes use of JDBC to read data. It is available as source or as precompiled binaries.

Here's how to use the project to read tuplesets from a database in the .dat of an OPL model

• Download the jdbc data source binaries. Unzip this archive and copy opl_jdbc/jdbc.js and opl_jdbc/jdbc-custom-data-source.jar into your project directory.
• Download your jdbc database driver and copy it into your project directory. Your jdbc database driver is a .jar that depends on your particular database.

Then you need to edit jdbc.js and configure paths. In particular, you specify your jdbc driver as well as the directory it is in. If you copied your database driver into your project directory, you just need to edit jdbc_driver.

// EDIT: you want to change this for your actual driver
var jdbc_driver = "mssql-jdbc-7.4.1.jre8.jar"
// EDIT: specify where to look for the jdbc driver. Default is in . (alongside this .js script) and in ../../external_libs
var jdbc_driver_path = ".;../../external_libs"

The data input definition relies on support functions defined in jdbc.js.

The following code fragments were extracted from the example data definition from the OPL jdbc custom data source example, demo.dat

Whenever you need to use database related feature, include jdbc.js:

includeScript("jdbc.js");

You create a database connector using the JDBC connection string:

var db = JDBCConnector("jdbc:sqlserver://localhost;instanceName=SQLEXPRESS;databaseName=custom_data_source;integratedSecurity=true");

JDBC connection strings describe the connection to your database and depends on your database. Some examples

Input tables are defined using the read method of the JDBCConnector. The first parameter is the tuple or tupleset name. The second parameter is the SQL statement:

db.read("Gasolines", "SELECT NAME FROM GASDATA");
db.read("Oils", "SELECT NAME FROM OILDATA");
db.read("GasData", "SELECT * FROM GASDATA");
db.read("OilData", "SELECT * FROM OILDATA");

The execute method allows to execute SQL statements. This is mostly used for table creation and other non data tasks in the database:

// drop previous results table and recreate it:
db.execute("DROP TABLE result");
db.execute("CREATE TABLE result(oil VARCHAR(30), gas VARCHAR(30), blend FLOAT, a FLOAT)");

Data are written back to the database using the update method. The first parameter is the tuple or tupleset name. The second parameter is the SQL statement:

db.update("items", "INSERT INTO result(oil, gas, blend, a) VALUES (?,?,?,?)");

The OPL-jdbc-data-source provides a full example.

Additional information

JDBC drivers are provided by your database vendors. This section lists a few drivers

JDBC Connection Strings

JDBC driver downloads