In OPL 20.1, we have introduced a new extension framework. This framework allows OPL to read data from databases using statements in a .dat file. Using this framework, you can build a custom database connection handler.
There are prebuild database connections for CSV, JDBC, MySQL, ODBC and SQLite.
For instance, the prebuilt JDBC Connection extends OPL with the following statements:
- JDBCConnection conn(CONNECTION_STRING, INITIAL_UPDATE);
- data from JDBCRead(conn, SQL_QUERY);
- results to JDBCPublish(conn, SQL_UPDATE)
where:
- CONNECTION_STRING is the database connection string
- INITIAL_UPDATE is some SQL statements executed prior to data being published to database.
- SQL_QUERY is a SQL query
- SQL_UPDATE is a SQL update statement
Here's a short example using JDBC:
.mod file:
tuple D {
string product; // name of product
int stock; // initial stock of this product
double profit; // profit for selling one unit of this product
};
{D} Products = ...;
dvar int+ sell[Products];
maximize sum(p in Products) sell[p];
subject to {
// Some constraints that make this
// an interesting model
}
tuple R {
string product;
int sell;
};
{R} Results = { <p, x[p]> | p in Products };
.dat file:
JDBCConnection conn("jdbc:sqlserver://localhost;instanceName=SQLEXPRESS;databaseName=demo_db;integratedSecurity=true",
"DROP TABLE IF EXISTS results; CREATE TABLE results (Product VARCHAR, Sell INTEGER)");
Products from JDBCRead(conn, "SELECT ProductName, InitialStock, Profit" FROM ProductTable");
Results to JDBCPublish(conn, "INSERT INTO results VALUES(?, ?)");
With this example, the following will be executed:
- OPL wil load the .dat file and recognize the JDBCConnection statement. It takes the connection string and builds a JDBC connection with the connection string.
- OPL executes JDBCRead statement to fill the Products set with data from the database.
- OPL builds and optimize the mathematical optimization model
- After the optimal solution is found, OPL populates the Results set.
- OPL Executes the JDBCPublish operation. Before results update statements are run, the INITIAL_UPDATE statement from the connection is execute. In this example, this has the effect of creating the results table.
We provide a collection of prebuild connection handlers so that connections can be used in .dat without the need to install external libraries (except for database drivers).
CSV Connection
The CSVConnection is always available in .dat files since its code is compiled directly into OPL.
It uses the IloCsvReader class from Concert. The connection string is the name of the CSV file which can have additional URL-encoded arguments to define header and separators.
The SQL_QUERY strings for this connection type is a comma-separated list of fields to select from the table.
JDBC Connection
The JDBCConnection type is builtin OPL and thus always supported.
This connection uses Java (JDBC) to connect to databases. A Java Virtual Machine must be available.
The connection string is passed to the JDBC driver, and the queries are usual SQL queries.
JDBC connections require that you download the JDBC driver for your datasource and make the drivers available to the JVM used by OPL, either by passing the argument -classpath / path/to/driver.jar, either by setting environment variable ODMS_JAVA_ARGS or using command line argument -Xjavaargs for oplrun (or oplrunjava on non-Windows).
MySQL Connection
his connection directly connects to a MySQL database. The code is not compiled directly into OPL but is provided in the opltabMySQL shared library.
The source code can be found in
<Install_dir>/opl/examples/opl_interfaces/ libopltabMySQL.c and can be used as a starting point for implementing other database connections. The CONNECTION_STRING is a semi-colon separated list of name/value pairs that sepcify the database and credentials that are used for connection.
The MySQL connection requires the C API for MySQL (libmysqlclient) is installed.
ODBC Connection
This connection uses ODBC to connect to databases. The code is not compiled directly into OPL but is provided in the opltabODBC shared library. The source code can be found in <Install_dir>/opl/examples/opl_interfaces/libopltabODBC.c
the SQL_QUERY and SQL_UPDATE queries are queries that are passed to your database driver. They are SQL queries if the database driver support SQL.
The CONNECTION_STRING is an ODBC connection string that will be passed to the ODBC library.
SQLite Connection
This connection directly connects to a SQLite database. The code is not compiled directly into OPL but is provided in the opltabSQLite shared library. The source code can be found in <Install_dir>/opl/examples/opl_interfaces/ libopltabSQLite.c and can be used as a starting point for implementing other database connections.
The CONNECTION_STRING for this connection type is the name of the file that contains the database
The SQLite connection requires the sqlite binaries to be installed.
OPL database support examples
The following examples show how to build custom database connections:
<Install_dir>/opl/examples/opl_interfaces/libopltabODBC.c<Install_dir>/opl/examples/opl_interfaces/libopltabMySQL.c<Install_dir>/opl/examples/opl_interfaces/libopltabSQLite.c
The following examples show how to use the data connections in .dat
<Install_dir>/opl/examples/opl/tabledata/tablehandler.mod<Install_dir>/opl/examples/opl/tabledata/tablehandler_csv.dat<Install_dir>/opl/examples/opl/tabledata/tablehandler_jdbc.dat<Install_dir>/opl/examples/opl/tabledata/tablehandler_odbc.dat<Install_dir>/opl/examples/opl/tabledata/tablehandler_sqlite.dat<Install_dir>/opl/examples/opl/tabledata/tablehandler_sqlserver.dat
#DecisionOptimization