# GLPK/Table Statement

Table statements can be used to read data from external data sources into model objects, including sets and parameters. Table statements can also be used to write model results back to external data sources. Drivers are currently provided for ODBC databases generally, MySQL databases specifically, CSV files, and dBase files.

## Official documentation

The official GLPK documentation file doc/gmpl.pdf (before GLPK 4.45 doc/tables.pdf) contains a full reference for the GMPL tables functionality. See obtaining GLPK. This page only provides pointers and examples.
As of version 4.45 the following information was missing: A field name consists of alphabetic [A-Z,a-z] and numeric [0-9] characters and is case sensitive. It does not need to be unique .

## CSV

The format for comma-separated values (CSV) files is described in RFC 4180.
For CSV files, the driver identifier is "CSV". The table OUT statement completely replaces any existing file.
The CSV table driver has the following limitations built in:
* The number of columns must be less or equal 50.
* The length of each value must be less or equal 100 characters.
The following examples show reading and writing of a CSV file:

## dBase

For dBase files, the driver identifier is "xBASE". The table OUT statement completely replaces any existing file.
The dBase table driver has the following limitations built in:
* The number of columns must be less or equal 50.
* The length of each value must be less or equal 100 characters.

## SQL

For ODBC database connections, the driver identifier is "ODBC". For native MySQL database connections, the driver identifier is "MySQL".
Be aware that table UPDATE and table INSERT statements must be followed by a table OUT statement in order to effect the changes (as described in the execute SQL section).
When using the MySQL table driver, the table statements can make full use of the SQL commands provided by the connected database. The MySQL REPLACE statement, a non-standard extension to SQL, implies the subsequent table OUT statement.

### table IN

The table IN statement, in combination with other SQL statements, can be used to select data from a relational database. For instance:
Instead of a SELECT statement, the name of a table can be provided. In this case the complete table is selected.
The SELECT statement (or the table name) can be preceded by additional SQL statements:
A SQL statement can be split into multiple strings to overcome the 100 character single line string length limitation in GLPK. In this case, the termination of the SQL statement is signaled by the presence of an unquoted semicolon as the last character of the multi-line call (as shown above).
As of GLPK 4.44, strings are automatically trimmed of trailing space (0x20) chars by the GLPK strtrim function when using the ODBC and MySQL drivers.

### table OUT

The table OUT statement, in combination with other SQL statements, can be used to insert, update, or delete records in a relational database.
The INSERT statement can be used to insert records into a relational database. For example:
If the last SQL statement does not contain question marks as placeholders for the values, a table name is expected. This table name and the column names provided will be used to construct an INSERT statement.
The final SQL statement that is iterated over can be preceded by additional SQL statements:
This tactic can be used to avoid duplicate insertions.
It is possible to issue statements other than INSERT. For instance, the UPDATE statement can be used to update records in a relational database. For example:
The DELETE statement can be used to delete records from a relational database. For example:

### Executing the SQL

At present, SQL support within the GMPL language only allows for SQL commands to be issued within "table IN" and "table OUT" statement blocks.
Within a "table IN" block, the final SELECT statement (or table name) can be preceded by any number of preparatory SQL statements.
Within a "table OUT" block, the final INSERT/DELETE/UPDATE statement, which is iterated over the domain, can be preceded by any number of preparatory SQL statements.
To completely separate the preparatory statement(s) from the database I/O, a dummy output statement can be employed:

## Table statement bug (fixed GLPK 4.45)

GLPK versions prior to version 4.45 may encounter a memory overrun error when processing table statements. The issue is described in this thread and is now resolved.

## References

* ↑ Y. Shafranovich (October 2005). "Common Format and MIME Type for Comma-Separated Values (CSV) Files".