GLPK/ODBC

Open Database Connectivity (ODBC) provides a standardized interface to relational database management systems. GLPK uses ODBC interfaces to communicate with relational databases and spreadsheets holding runtime optimization model data.

ODBC requires an installed driver manager and a database-specific ODBC connection.

Official documentation

The official GLPK documentation file doc/gmpl.pdf contains a full reference to the use of tables from relational databases and spreadsheets (GLPK 4.27 - 4.44: see doc/tables.pdf). The bulk of that material is therefore not repeated here. See obtaining GLPK.

Table statement

The table statement is used to access a particular ODBC database:

table name alias IN 'ODBC' conn sql . . . sql :
set <- [ fld , . . . , fld ] , par ~ fld , . . . , par ~ fld ;

table name alias domain OUT 'ODBC' conn sql . . . sql :
expr ~ fld , . . . , expr ~ fld ;

100 character limitation

As currently programmed, strings in GMPL are limited to 100 characters. This limit is easily reached in a table statement, be it:

• a connection string (conn) statement, or
• an SQL (sql) statement.

One solution is to use a data set name (DSN) connection string and only include the password and user details:

'DSN=glpk;UID=glpk;PWD=gnu'

If this is not sufficiently flexible, a file DSN (see later) can be used.

An SQL statement can be spread over multiple lines. The last character of the last line of the SQL statement must be a semicolon (;). The individual strings are separated by spaces and are concatenated before being sent to the SQL command processor. For example:

 table ta {(i, j) in {i1 in 1..9} cross {i2 in 1..9}} OUT
   'ODBC' 'DSN=glpk;UID=glpk;PWD=gnu'
   'DELETE FROM sudoku_solution' # line 1 of SQL statement 1
   'WHERE ID = ' & id & ';'      # line 2 of SQL statement 1
   'INSERT INTO sudoku_solution' # line 1 of SQL statement 2
   '(ID, COL, LIN, VAL)'         # line 2 of SQL statement 2
   'VALUES(?, ?, ?, ?);' :       # line 3 of SQL statement 2
   id ~ ID, i ~ COL, j ~ LIN, (sum{k in 1..9} x[i,j,k] * k) ~ VAL;

Custom build

It is safe to increase the MAX_LENGTH macro in file src/glpmpl.h from 100 to 240 to allow the processing of longer symbolic values, including the control strings passed to the ODBC driver. The downside is that your models may no longer be valid on a default build of GLPK. This issue is also discussed in this early-2012 thread.

MySQL support

GLPK source build

A default build of GLPK does not enable the MySQL table driver. To use this database, you need to configure GLPK as follows:

./configure --enable-dl --enable-mysql ...

And then run make as per usual. If you attempt to address MySQL without this support, GLPK will report:

MySQL table driver not supported

GLPK Windows 7 executable

If you are running 64-bit Windows 7 and wish to use the GLPSOL executable to pull data from a MySQL database, solve the problem contained in your MathProg .mod file, and then write the solution back to your database, try the following (confirmed September 2011 using GLPK 4.47). You will first need:

• 64-bit MySQL Community Server 5.5.16 for Windows (x86, 64-bit) — download
• 64-bit MySQL ODBC Connector 5.1.8 for Windows (x86, 64-bit) — download
• 64-bit GLPK executable — see obtaining Windows executables

Set up a 64-bit data source using c:\Windows\system32\odbcad32.exe as described below, to access your MySQL data base.

The following MathProg code shows one way of populating a MathProg set in your model:

/* Import finished goods set from MySQL database */

table t IN "iODBC"
   'DSN=glpk;UID=glpk;PWD=glpk'
   'export_finished_good' :
   FIN <- [ FinishedGood ];

In the above example, export_finished_good and FinishedGood are the relevant table and field names from the MySQL database. And FIN is the MathProg set that you want to populate with data from the database.

The following MathProg code shows one way of populating a two-dimensional MathProg array in your model:

/* Import finished good demand from MySQL database */

table t IN "iODBC"
   'DSN=glpk;UID=glpk;PWD=glpk'
   'export_finished_good_demand' :
   [ FinishedGood, PERIOD ], FinDem ~ DEMAND;

In this example, the demand for a particular good in each period is read in. The data is obtained from the MySQL table export_finished_good_demand and used to populate the MathProg array FinDem, dimensioned by FIN and TIM — the two sets that represent finished goods and time periods, respectively. The field that holds the list of finished goods is named FinishedGood, the field that holds the list of time periods is named PERIOD, and the field that holds the demand for each finished good in each time period is named DEMAND. The MathProg array receiving this data was previously declared as FinDem{FIN, TIM}.

Installing an ODBC driver manager

ODBC is integral to Windows. For Linux, two open source projects provide major implementations of the ODBC API:

• iODBC — www.iodbc.org
• unixODBC — www.unixodbc.org.

The libraries to be used by the ODBC connectors are stored in the configuration file /etc/odbcinst.ini. For example, if using the MySQL relational database management system on Linux:

[MySQL]
Description     = ODBC for MySQL
Driver          = /usr/lib/odbc/libmyodbc.so
Setup           = /usr/lib/odbc/libodbcmyS.so
UsageCount      = 1
CPTimeout       =
CPReuse         =

Windows stores the same information under the registry key HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI.

Setting up a DSN

A data set name (DSN) is the data structure describing access data for a relational database. Three types of DSN exist:

• system DSN — stored in /etc/odbc.ini under Linux or in the Windows registry key HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI
• user DSN — stored in ~/.odbc.ini under Linux or in the Windows registry key HKEY_CURRENT_USER\Software\ODBC\ODBC.INI
• file DSN

Editors for DSNs are provided by iODBC and unixODBC under Linux.

If using 32-bit Windows, invoke c:\WINDOWS\system32\odbcad32.exe for editing data sources. If using 64-bit Windows, both 32-bit and 64-bit data sources can exist. For editing 64-bit data sources, call c:\WINDOWS\system32\odbcad32.exe and for editing 32-bit data sources, call c:\WINDOWS\syswow64\odbcad32.exe.

A file DSN stores the description of a single DSN in a single file, for instance:

[ODBC]
DRIVER=Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)
DBQ=.\sudoku.xlsx

For a file DSN, Windows defaults to the path c:\Program Files\Common Files\ODBC\Data Sources.

Passwords in DSN are not encrypted, hence for security reasons, DSN information should not be stored on insecure machines.

Connection strings

An application using an ODBC interface must pass the access data for a data base connection as a connection string to the ODBC API.

The connection string may optionally refer to a DSN, overwriting some (or all) of the fields in the process.

The following connection string refers to a DSN called glpk and overwrites the current username and password:

DSN=glpk;UID=myuser;PWD=mypassword

The following connection string uses a file DSN within the application path:

FileDSN=.\sudoku_excel.dsn

A DSN-less connection string specifies a driver and driver-specific parameters, for example:

DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb);DBQ=.\sudoku.xlsx

Microsoft Access

Microsoft Access is the relational database application that forms part of the office suite from Microsoft. Drivers for Access are contained in the downloadable "2007 Office System Driver: Data Connectivity Components" and "Microsoft Access Database Engine 2010 Redistributable" resources.

For Access 2007 and 2010, an example of a valid DSN-less connection string would be:

DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};dbq=.\glpk.accdb

For earlier versions of Access, an example of a valid DSN-less connection string would be:

DRIVER={Microsoft Access Driver (*.mdb)};dbq=.\glpk.mdb

Machine architecture

Microsoft Access 2007 is a 32-bit application and, if you wish to use ODBC to link into Access, you must use the 32-bit version of GLPK. If you need to use the 64-bit version of GLPK, then you will need to install the 64-bit ODBC connector for Access. It is contained in the 64-bit version of "Microsoft Access Database Engine 2010 Redistributable" or in the 64-bit version of Microsoft Office 2010. The 32-bit and 64-bit installations of Microsoft Office are mutually exclusive — you will need to remove 32-bit Access if you want to use 64-bit Access.

SQL syntax

The following SQL syntax applies when writing GMPL models which interface with Access:

• strings must be enclosed in apostrophes (e.g. 'Value') and not in quotation marks (e.g. "Value")
• to convert DateTime (timestamp) fields to the time representation used by GLPK, call the Access function DateDiff 
• column aliases cannot be reserved names, like time
• column names containing spaces must be enclosed in square brackets (e.g. [Product Code]).

For example:

set ProdTime, dimen 2;
param sales{ProdTime};
table products_table IN "ODBC"
  'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};dbq=.\sales.accdb'
  'SELECT'
  '  [Product Code] AS p,'
  '  DateDiff(''s'', #01/01/1970#, Period) AS t,'
  '  [Net Sales] AS s'
  'FROM [Historic Sales]'
  'WHERE [Product Text] = ''Unskimmed Milk'';' :
  ProdTime <- [p, t], sales ~ s;

A list of Access 2007 functions can be found at .

Microsoft Excel

Microsoft Excel is the spreadsheet program offered by Microsoft. Drivers for Excel are contained in the downloadable "2007 Office System Driver: Data Connectivity Components" and "Microsoft Access Database Engine 2010 Redistributable" resources. The "Microsoft Access Database Engine 2010 Redistributable" resource contains both 32-bit and 64-bit versions. Use the version that matches the GLPK library you have.

The following connection string uses a file DSN-less connection to Microsoft Excel:

DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb);DBQ=.\sudoku.xlsx;READONLY=FALSE

An ODBC connection to Excel is read-only by default. You must set ReadOnly to False to write data back to Excel.

The Excel ODBC driver does not support DELETE, UPDATE, or ALTER TABLE statements. Essentially it can only append new lines.

The following fields are supported:

Field	Significance
Driver	ODBC connectivity driver
DBQ	Excel file name
FirstRowHasNames	1 = true, 0 = false
ReadOnly	False = write-enabled

In the example below, the entries of set 'orders' are written to the range 'result' in the Excel workbook out.xlsx. For the example to work, the Excel workbook out.xlsx must exist and must contain a named range 'result' with 3 columns.

param tableName, symbolic;
set orders, dimen 3;
solve;
table result {(p, o, q) in orders} OUT "ODBC" 
  'DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};' &
  'DBQ=.\out.xlsx;READONLY=FALSE'
  'DROP TABLE [' & tableName & '];'
  'CREATE TABLE [' & tableName & ']'
  '(Product CHAR(20), OrderNumber CHAR(10), Quantity DOUBLE);'
  'INSERT INTO  [' & tableName & ']'
  '(Product, OrderNumber, Quantity)'
  'VALUES(?, ?, ?);' :
  p, o, q;
data;
param tableName := 'result';
set orders :=
  'Full Milk',         '0600000101', 10.,
  'UHT Milk',          '0600000102', 16.,
  'Semi Skimmed Milk', '0600000103', 14.,
  'UHT Milk',          '0600000104', 36.;
end;

For more information, try a search on "Excel" in the help-glpk mailing list.

Executing a series of SQL statements that do not write records

You may wish to execute one or more SQL statements that do not write records. For example, you may wish to delete all records from a table. That can be tricky because if the last line does not contain a "?", GMPL will assume that it names a table and will do a spurious INSERT INTO that. One workaround is to code the last statement with an unnecessary "?" in it somewhere. For instance:

 table DeleteRecords {x in 1..1} OUT
   'ODBC' 'DSN=glpk;UID=glpk;PWD=gnu'
   'DELETE FROM sudoku_solution WHERE ?=?' :
   x, x;

ODBC pitfalls

If floating-point data is to be read from the database, GLPSOL can only process it if the data contains a dot "." as the decimal separator. So the ODBC driver has to make sure to return the data in such a format. In case of an Oracle 11g database, set the environment variable NLS_LANG to "American_America.WE8ISO8859P1", then the database returns a "." as the decimal separator for numeric data (the ODBC driver reads the variable and formats the data accordingly).

Usage of "*" in SELECT statement has to be handled with care. GMPL needs to have the exact same column names in the SQL statement that are also referenced in the GMPL table statement. There is no automatic matching of SQL columns to GMPL field names.

Depending on the SQL implementation and operating system column names may be case sensitive.