Overview

Database support in OPL

OPL allows reading data from data bases using a .dat file. This is done by means of three different statements:

• XXXConnection conn(CONNECTION_STRING, INITIAL_UPDATE);
• data from XXXRead(conn, SQL_QUERY);
• results to XXXPublish(conn, SQL_UPDATE);

Here XXX is a placeholder for a database connection type, for example JDBC, ODBC, SQLite, MySQL, and so on. OPL will search for and select an appropriate database driver based on this type.

CONNECTION_STRING is a database connection string that is passed to the driver in order to connect to the database. For example:

"jdbc:mysql:database?username=john&password=unknown"

INITIAL_UPDATE is explained in the section Prebuilt database connection types.

SQL_QUERY is an SQL query string such as:

"FROM datatable SELECT col1, col3"

OPL will run the query using the specified connection and assign the result to the OPL elements to be filled. Each row in the query result fills one element in the OPL model.

SQL_UPDATE is an SQL update expression such as:

"INSERT INTO resulttable VALUES (?, ?)"

OPL will execute this statement once for each element that is published using this statement.

Here is a short example:

• .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:sqlite:database.db",
                      "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(?, ?)");

• SQLite database file database.db has one table ProductTable:

  
     ProductName | Profit | InitialStock
     ------------+--------+-------------
      Product1   |    100 |     10
      Product2   |    200 |     15

With this data the following will be executed:

1. OPL will load the .dat file and recognize the JDBCConnection statement. It takes the connection string "jdbc:sqlite:database.db" and passes that to the generic Java JDBC driver. That driver will inspect the connection string and as a result will look for a SQLite driver and connect to the database called database.db file using that driver. It will return a handle to that connection to OPL. OPL stores that handle as well as the string:

   "DROP TABLE ...; CREATE TABLE ..."

   Note that the string is stored for later use. It is not executed at this point in time.
2. OPL will execute JDBCRead statement to fill the Products set with data from the database. It will read the records from the query result line by line and create one tuple for each line. As a result, the Products set will have this form:

   { <"Product1", 10, 100>, <"Product2", 15, 200> }

   Note how the order of the queried fields in the statement matches the order in which the fields are defined in the tuple:

   ProductName, InitialStock, Profit

   The names of the columns are not required to match the names of the tuple fields. Only the order of fields must match. This also implies that the order of columns in the database is not required to be the same as the fields in the tuple. Also the table in the database can have more columns than the tuple has fields.
3. Next OPL will build and optimize the mathematical optimization model.
4. After the optimal solution is found, OPL will populate the Results set.
5. Finally, OPL will execute the JDBCPublish operation. Before executing the first XXXPublish operation for a connection, OPL will execute the SQL statement that was passed as the second argument to XXXConnection(). In your example this is the string:

   DROP TABLE IF EXISTS results; CREATE TABLE results (Product VARCHAR, Sell INTEGER)

   That string (re)creates the results table from scratch. After executing this, OPL will publish the results set into the results table using the specified SQL update statement. Here the ? in the publish SQL statement matches the fields in the tuple. The values for ? are assigned in the order of the tuple fields. Again, the name of tuple fields and columns in the table need not match, only the order is important. Assuming the optimal solution was

   sell[Product1] = 5 and Sell[Product2] = 7

   , after executing the JDBCPublish() statement the database will have an additional table called "results" of this form:

   
    Product  | Sell
    ---------+-----`
    Product1 | 5
    Product2 | 7

Some important technical details

An XXXConnection() statement may result in up to two connections being made to the database. As soon as the XXXConnection() statement is executed, OPL creates a read-only connection to the database. This connection is used to process XXXRead() statements. Once all data has been read into the model, this connection is closed. Upon encountering an XXXConnection() statement, OPL also saves the CONNECTION_STRING and INITIAL_UPDATE strings. If during post-processing an XXXPublish() statement is encountered for that connection, then OPL creates a new connection that is also open for writing. This connection is used to process the XXXPublish() statements. Before the first statement is executed, OPL also executes the INITIAL_UPDATE command. Note that if there is no XXXPublish() statement for a connection, then the INITIAL_UPDATE is NOT executed!

Additional information

In the prebuilt database connectors that ship with OPL, all string arguments are preprocessed with the following benefits:

• Environment variables can be referenced in a string via the ${ENVIRONMENT_VARIABLE_NAME} syntax and will have their value interpolated before the string is passed on to the driver.
• Model elements can be referenced in a string via the $(MODEL_ELEMENT_NAME) syntax and will have their value interpolated before the string is passed to the driver.
• Characters can be escaped using %xx, where xx is the byte value for the character (in hexadecimal notation and exactly two characters wide). Note that with updates using %xx, multi-byte characters require multiple escape sequences (one for each byte).

The first two bullet points allow for building dynamic queries.