# 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:

### 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:
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:

### 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:
And then run make as per usual. If you attempt to address MySQL without this support, GLPK will report:

### 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:
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:
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:
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:
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:
The following connection string uses a file DSN within the application path:
A DSN-less connection string specifies a driver and driver-specific parameters, for example:

### 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:
For earlier versions of Access, an example of a valid DSN-less connection string would be:

#### 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:
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:
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.
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:

## 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.

## References

* ↑ "DateDiff Function". Microsoft Corporation. Retrieved 26 June 2011.
* ↑ "Access 2007 Help and How-to - Macros and programmability - Functions (alphabetical)". Microsoft Corporation. Retrieved 26 June 2011.
* ↑ "257819 How To Use ADO with Excel Data from Visual Basic or VBA". Microsoft Corporation. Retrieved 28 December 2010.
* ↑ "INF: Excel ODBC Driver and Text ODBC Driver Notes". Retrieved 30 May 2013.
* ↑ "Identifier Case Sensitivity". Oracle. Retrieved 2012-02-27.