Table of Contents
- Overview
- Requirements
- Batch Usage
- Multi-Query Batch Usage
- Interactive Usage
- Connection Strings
- ODBC Examples
- Strategies
- Parameter Files
- Notes
- Attention
SQL2GMS
is deprecated (see GAMS 40 SQL2GMS release notes). Please use Connect agent SQLReader instead.
Overview
SQL2GMS
is a tool to convert data from an SQL database into GAMS readable format. The source is any data source accessible through Microsoft's Data Access components including ADO, ODBC and OLEDB. The target is a GAMS Include File (.inc) or a GAMS GDX File (.gdx).
When running the executable sql2gms.exe without command line arguments, the tool will run interactively with a built-in GUI interface. Alternatively SQL2GMS
can be run in batch mode, which is useful when running it directly from a GAMS model without user intervention using the $call
command at compile time or the execute
command at execution time.
Database tables can be considered as a generalization of a GAMS parameter. GAMS parameters have multiple index columns but just one value column. If the table is organized as multi-valued table, a UNION operation in the SQL statement can be used to generate the correct GAMS file.
There are no special requirements on the data types used in the database. The data is converted to strings, which is almost always possible. Data types like LONG BINARY may not be convertible to a string, in which case an exception will be raised. In general NULL's should not be allowed to get into a GAMS data structure. The handling of NULL's can be specified in an option.
Besides parameters it is also possible to generate set data.
If you need to read data from an MS Access database or MS Excel spreadsheet, the accompanying tools MDB2GMS or XLS2GMS resp. GDXXRW may be more appropriate. In some cases it may be more useful to use SQL2GMS
to read from Access and Excel data sources.
Requirements
SQL2GMS
runs only on Windows PC's with [MDAC] (https://en.wikipedia.org/wiki/Microsoft_Data_Access_Components) (Microsoft Data Objects) installed. In many cases this will be installed on your computer. Otherwise it can be downloaded from the [Microsoft Download Center] (https://www.microsoft.com/en-in/download/details.aspx?id=21995) (direct link to MDAC).
In order to use SQL2GMS
effectively, you will need to have a working knowledge of [SQL] (https://en.wikipedia.org/wiki/SQL) in order to formulate proper database queries. In addition you will need some knowledge on how to connect to your database using ODBC or ADO.
Batch Usage
SQL2GMS
can be run in batch mode without user intervention from within the GAMS model by using the $call
resp. execute
statements or directly from command prompt while specifying all arguments in the command-line. A SQL2GMS
batch call is of the following form:
sql2gms connectionString outputFile queryString
A proper call will at least contain the following three command-line arguments:
- The name of the database and the instruction for connecting to the database must be specified within the connectionString, which is indicated by the option C. i.e.
C=connectionString
. - The name of outputFile, either an include file (.inc) or GDX file (.gdx), must be specified. Using an include file to store the results of the query is indicated by the option
O
, i.e.O=outputFile.inc
, while the use of a GDX file is indicated by the optionX
, i.e.X=outputFile.gdx
. - The SQL queryString, containing the SQL statement to be executed on the database, must be specified within the option
Q
, i.e.Q=queryString
.
See also Command-line Arguments below for a complete list of all possible command-line arguments. Consider that the $call
or execute
usage is rather error prone and you will need to spend a little bit of time to get the call correct and reliable. Alternatively, use the interactive built-in GUI interface or enter the command-line arguments in an external text file in order to write a more structured and readable command. The use of an external parameter file is indicated by preceding the file name with a @
(At sign).
Also consider to take a look at the section Strategies, mentioning some of the drawbacks of the batch usage and how to overcome them.
Command-line Arguments
The following table summarizes the command-line arguments that can be specified when using SQL2GMS
directly from the GAMS model or command prompt.
Argument | Interpretation | Default | Description |
---|---|---|---|
C | connectionString | none | Specify the connectionString (required). |
O | outputIncludeFile | none | Specify the name of the output file (.inc). Either O= or X= must be specified (or both). |
On | nth outputIncludeFile | none | Match the nth query with the nth output file (.inc format) if multiple queries are used. |
X | outputGDXFile | none | Specify the name of the output file (.gdx). Either O= or X= must be specified (or both). |
Q | Query | none | This option can be used to specify a SQL query (required). |
Qn | nth query | none | Match the nth query with the nth output file (.inc) format or with the nth set- or parameter name when writing to GDX if multiple queries are used. |
S | setName | none | If we write to a GDX file, use this option to specify the name of a set to be used inside the GDX file. |
Sn | nth setName | none | Match the nth query with the nth set in the GDX file if multiple queries are used. |
Y | setName (with expl. text) | none | If we write to a GDX file, use this option to specify the name of a set to be used inside the GDX file. Use this argument to store a set with explanatory text. |
Yn | nth setName (with expl. text) | none | Match the nth query with the nth set (with explanatory text) in the GDX file if multiple queries are used. |
A | parameterName | none | If we write to a GDX file, use this option to specify the name of a parameter to be used inside the GDX file. |
An | nth parameterName | none | Match the nth query with the nth parameter in the GDX file if multiple queries are used. |
L | Listing | disabled | Controls if the data is embedded in the listing file. |
M | Mute | disabled | Controls if additional information is written to the log and include file. |
B | Quote Blanks | disabled | Quote strings if they contain blanks or embedded quotes. |
@fileName | ext. options file | none | Causes the program to read options from an external text file. |
N | iniFileName | sql2gms.ini | Indicates the usage of a different INI file. |
T1 | connectionTimeOut | 15 | Indicates how long to wait while establishing a connection before terminating the attempt and generating an error. |
T2 | commandTimeOut | 30 | Indicates how long to wait while executing a command before terminating the attempt and generating an error. |
T | timeOut | none | Sets both T1 and T2 . |
D | Debug | disabled | Generate debug information. |
E | Empty sets | disabled | Allow an empty result set; without this option an empty result set will generate an error. |
R | rowBatchSize | 100 | Row batch size; the default is 100 records. |
P | Password | none | Specify a password for the database. |
W | Wiring | none | Maps database columns to GAMS index positions. |
Some more detailed remarks on the command-line arguments:
C = string (connectionString, default = none)
This option is required and specifies which database is to be used and how
SQl2GMS
talks to the database. A collection of valid connection strings can be found in section Connection Strings. Often the connection string will need to be surrounded by quotes, as in:C="DSN=sample"
. This option is demonstrated in all examples, see Example 1 - Reading a single valued Table for instance.O = string (outputIncludeFile, default = none)
This option specifies the name of the output file. The format of the output file will be a GAMS include file for a parameter or set statement. Make sure the directory is writable. UNC names can be used. An output file must be specified for batch operation: i.e. either
O=
orX=
needs to be specified (or both). The include file will be an ASCII file that can be read by GAMS using the $include command within the data definition of a set, parameter or scalar. If the include file already exists, it will be overwritten. This option is demonstrated in Example 1 - Reading a single valued Table for instance.On = string (outputIncludeFile, default = none)
When using multiple queries in a single
SQL2GMS
call, you can append a number to match a query with an output file, as an include file storing the results for multiples queries cannot be interpreted later on in your GAMS model when using the include file in a set or parameter definition:
Q1="SELECT a, b FROM table"
O1=ab.inc
Q2="SELECT c, d FROM table"
O2=cd.inc
See also section Multi-Query Batch Usage or Example 7 - Multi-Query Batch Example for instance.
X = string (outputGDXFile, default = none)
This option specifies the name of the output file. The format of the output file will be a GAMS GDX file. Make sure the directory is writable. UNC names can be used. If the GDX file already exists it will be overwritten - it is not possible to append to a GDX file. An output file must be specified for batch operation: i.e. either
O=
orX=
needs to be specified (or both). This option is demonstrated in Example 5 - Reading Set with Explanatory Text or Example 7 - Multi-Query Batch Example for instance.Q = string (query, default = none)
This option can be used to specify an SQL query. Queries can contain spaces and thus have to be surrounded by double quotes. For the exact syntax of the queries that is accepted by the database we refer to the documentation that comes with your RDBMS (Relational DataBase Management System). The query is passed on directly to the RDBMS so the complete power and expressiveness of SQL is available including stored procedures etc. For an exact description of allowed expressions consult a text on your database system. This option is demonstrated in Example 1 - Reading a single valued Table for instance.
Qn = string (query, default = none)
When using multiple queries in a single
SQL2GMS
call, you can append a number to match a query with an output file, as an include file storing the results for multiples queries cannot be interpreted later on in your GAMS model when using the include file in a set or parameter definition. In addition, you can match the results of a query with a specific set- or parameter name when writing to GDX.
Q1="SELECT a, b FROM table"
O1=ab.inc
Q2="SELECT c, d FROM table"
O2=cd.inc
or (GDX output file format - where several sets and parameters can be stored in a single file):
Q1="SELECT a, b FROM table"
A1=abParameter
Q2="SELECT c FROM table"
S2=cSet
Note the usage of the arguments An resp. Sn in order to store the results as parameter resp. set and to specify the name of the symbols. See also section Multi-Query Batch Usage or Example 7 - Multi-Query Batch Example for instance.
S = string (setName, default = none)
If we write to a GDX file, use this option to specify the name of a set to be stored in the GDX file (containing the results of the query). This option is demonstrated in Example 4 - Reading a multi dimensional Set.
Sn = string (setName, default = none)
If multiple queries are used in a single
SQL2GMS
call while writing to a GDX file, use this option to specify the name of the nth set to be stored in the GDX file (containing the results of the nth query), e.g.
Q1="SELECT i FROM table"
S1=iSet
Q2="SELECT j FROM table"
S2=jSet
See section Multi-Query Batch Usage and Example 7 - Multi-Query Batch Example for instance.
Y = string (setName, default = none)
If we write to a GDX file, use this option to specify the name of a set to be used inside the GDX file. The last column specified within the select clause in the SQL statement will be used as explanatory text. This option is demonstrated in Example 5 - Reading Sets with Explanatory Text for instance.
Yn = string (setName, default = none)
If multiple queries are used in a single
SQL2GMS
call while writing to a GDX file, use this option to specify the name of the nth set (with explanatory text) to be stored in the GDX file (containing the results of the nth query), e.g.
Q1="SELECT i, explTextForSeti FROM table"
Y1=iSet
Q2="SELECT j, explTextForSetj FROM table"
Y2=jSet
The last column specified within the select clause in the SQL statements will be used as explanatory text.
A = string (parameterName, default = none)
If we write to a GDX file, use this option to specify the name of a parameter to be stored the GDX file (containing the results of the query).
Note:MDB2GMS
usesP
, butP
was already taken inSQL2GMS
for specifying the password.An = string (parameterName, default = none)
If multiple queries are used in a single
SQL2GMS
call while writing to a GDX file, use this option to specify the name of the nth parameter to be stored in the GDX file (containing the results of the nth query), e.g.
Q1="SELECT i, j, value FROM table"
A1=ijValue
Q2="SELECT n, m, value FROM table"
A2=nmValue
See section Multi-Query Batch Usage and Example 7 - Multi-Query Batch Example for instance. for instance.
L (Listing, disabled by default)
Embed the data between the
$offListing
and$onListing
dollar control options, so the data will not be listed in the listing file. This is a quick way to reduce the size of the listing file when including very large data files into the model. Otherwise the listing file would become too large to be handled comfortably.Controls if additional information (GAMS and
SQL2GMS
version numbers, number of rows in the data, elapsed time, used query etc.) is written to the log and include file.B (Quote Blanks, disabled by default)
Quote strings if they contain blanks or embedded quotes. If a string does not contain a blank or embedded quotes, it will remain unquoted. If the string contains a single quote the quotes applied will be double quotes and if the string contains already a double quote, the surrounding quotes will be single quotes. In the special case that the string contains both, the double quotes are replaced by single quotes. For more information see subsection Quotes. This option only applies to an output include file.
@fileName = string (fileName, default = none)
Causes the program to read options from an external text file. If the file name contains blanks, it can be surrounded by double quotes. The option file contains one option per line, in the same syntax as if they were specified directly on the command-line. See also Parameter Files for some further details.
N = string (fileName, default = none)
Use a different INI file than the standard sql2gms.ini located in the same directory as the executable sql2gms.exe.
T1 = integer (connectionTimeOut, default = 15)
Indicates how long to wait while establishing a connection before terminating the attempt and generating an error. The value sets, in seconds, how long to wait for the connection to open. Default is 15. If you set the property to zero, ADO will wait indefinitely until the connection is opened. A value of -1 indicates that the default value is to be used. Note: the provider needs to support this functionality.
T2 = integer (commandTimeOut, default = 30)
Indicates how long to wait while executing a command before terminating the attempt and generating an error. The value sets, in seconds, how long to wait for a command to execute. Default is 30. If you set the property to zero, ADO will wait indefinitely until the execution is complete. A value of -1 indicates that the default value is to be used. Note: the provider needs to support this functionality.
T = integer (timeOut, default = none)
D (Debug, disabled by default)
Generate debug information. This option must be specified in an INI file when using the interactive mode of
SQL2GMS
.E (Empty sets, disabled by default)
Allow an empty result set; without this option an empty result set will generate an error. This option must be specified in an INI file when using the interactive mode of
SQL2GMS
.R = integer (rowBatchSize, default = 100)
Row batch size; the default is 100 records. This option must be specified in an INI file when using the interactive mode of
SQL2GMS
.P = string (password, default = none)
Specify a password for the database.
W = string (wiring, default = none)
By using the
W
option, one can map database columns to GAMS index positions. See model [Wiring] for reference (note thatMDB2GMS
is used to access the MS Access database instead ofSQL2GMS
, however this does not affect thewiring
specification).
Example 1 - Reading a single valued Table
Suppose we want to read the distances parameter of the [trnsport] model from the GAMS Model Library. The data is stored in the Microsoft Access Database format (file Sample.accdb).
The data can be queried with a simple SQL statement:
By running the following SQL2GMS
statement, the connection to the database Sample.accdb is established. In addition, the data will be queried and the results are written to a GAMS include file afterwards (.inc).
sql2gms C="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Sample.accdb" Q="SELECT city1, city2, distance FROM distances" O=distances.inc
The connection string is specified using the argument C. Note that the string is enclosed by quotes, as the string contains blanks. The database file name Sample.accdb is also specified within the connection string. For more information on how to connect to your database, see section Connection Strings. The arguments Q and O are used to specify the query and the output file name (and format).
The generated include file distances.inc looks like:
* -----------------------------------------------------
* SQL2GMS 3.0 25.2.0 r67636 ALFA Released 15Aug18 VS8 x86 32bit/MS Windows
* Erwin Kalvelagen, GAMS Development Corp
* -----------------------------------------------------
* ADO version: 10.0
* Connection string: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Sample.accdb
* Provider: MSDASQL
* Query: SELECT city1, city2, distance FROM distances
* -----------------------------------------------------
SEATTLE.NEW-YORK 2.5
SAN-DIEGO.NEW-YORK 2.5
SEATTLE.CHICAGO 1.7
SAN-DIEGO.CHICAGO 1.8
SEATTLE.TOPEKA 1.8
SAN-DIEGO.TOPEKA 1.4
* -----------------------------------------------------
The commented header section summarizes some information about the SQl2GMS
resp. GAMS version and about the executed database query. The standard export format is to consider the last column as the value column (containing the distances) and the previous columns as the indices (containing the city names). The indices are separated by a dot, allowing the generated include file to be used as part of a parameter declaration statement in your GAMS model.
Retrieving the data using SQL2GMS
from the database and including the queried data in your GAMS model within the parameter declaration statement (at compile time) can be combined in the following way:
Set
i 'canning plants' / seattle, san-diego /
j 'markets' / new-york, chicago, topeka /;
$call sql2gms C="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Sample.accdb" Q="SELECT city1, city2, distance FROM distances" O=distances.inc
Parameter d(i,j) 'distance in thousands of miles' /
$include distances.inc
/;
display d;
Finally, the values of the parameter d
are displayed:
new-york chicago topeka seattle 2.500 1.700 1.800 san-diego 2.500 1.800 1.400
This example is also part of the GAMS Data Utilities Library, see model [Distances2] for reference. Note that the query results are written to a GDX file in addition.
Example 2 - Reading a multi valued Table
In this scenario, we want two read the three index columns year
, loc
, prod
and the value columns sales
and profit
from the database file Sample.accdb. Therefore, we use two separate parameters and queries or alternatively a parameter with an extra index position (for sales
resp. profit
) and a UNION select.
Consider the table with two value columns sales
and profit
:
Two separate Parameters
A simple way to import this into GAMS is to use two parameters and two SQL queries. The SQL queries can look like:
We can generate a include file sales.inc by running the following command:
sql2gms C="DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};dbq=Sample.accdb" Q="SELECT year, loc, prod, sales FROM data" O=sales.inc
Note that we specify the first query in order to select the sales and the relevant index columns within the Q argument. The query results are written to sales.inc using the O argument. Analogously we generate a include file profit.inc by running the following command while specifying the second query in order to obtain the profits and the relevant index columns:
sql2gms C="DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};dbq=Sample.accdb" Q="SELECT year, loc, prod, profit FROM data" O=profit.inc
Retrieving the data using SQL2GMS
from the database Sample.accdb and including the queried data in your GAMS model within the parameter declaration statements of sales
and profit
(at compile time) can be combined in the following way:
Set
year 'years' / 1997*1998 /
loc 'locations' / nyc, was, la, sfo /
prd 'products' / hardware, software /;
$call sql2gms C="DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};dbq=Sample.accdb" Q="SELECT year, loc, prod, sales FROM data" O=sales.inc
Parameter sales(year,loc,prd) /
$include sales.inc
/;
$call sql2gms C="DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};dbq=Sample.accdb" Q="SELECT year, loc, prod, profit FROM data" O=profit.inc
Parameter profit(year,loc,prd) /
$include profit.inc
/;
Single Parameter with extra Index Position
The operation can also be performed in one big swoop by using a different GAMS datastructure, i.e. a single parameter is defined with an extra index type
to indicate the data type (sales or profit). The index and value columns will be selected by the following SQL statement. Note the UNION operation in order to combine the results and the strings 'sales' resp. 'profit' to identify the data type later on.
The data is accessed, queried and written to data.inc by running the following command:
sql2gms @howToRead.txt
Note that usage of the external parameter file howToRead.txt shown below in order to increase the readability of the command (one argument per line, quotes can be omitted):
C=DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};dbq=Sample.accdb Q=SELECT year, loc, prod, 'sales', sales FROM data UNION SELECT year, loc, prod, 'profit', profit FROM data O=data.inc
The generated include file data.inc looks like (shortened for presentation):
* -----------------------------------------------------
* SQL2GMS 3.0 25.2.0 r67636 ALFA Released 15Aug18 VS8 x86 32bit/MS Windows
* Erwin Kalvelagen, GAMS Development Corp
* -----------------------------------------------------
* ADO version: 10.0
* Connection string: DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};dbq=Sample.accdb
* Provider: MSDASQL
* Query: SELECT year, loc, prod, 'sales', sales FROM data UNION SELECT year, loc, prod, 'profit', profit FROM data
* -----------------------------------------------------
1997.la.hardware.profit 8
1997.la.hardware.sales 80
1997.la.software.profit 16
1997.la.software.sales 60
1997.nyc.hardware.profit 5
1997.nyc.hardware.sales 110
1997.nyc.software.profit 10
1997.nyc.software.sales 100
1997.sfo.hardware.profit 9
1997.sfo.hardware.sales 80
1997.sfo.software.profit 10
1997.sfo.software.sales 50
1997.was.hardware.profit 7
1997.was.hardware.sales 120
1997.was.software.profit 20
1997.was.software.sales 70
1998.la.hardware.profit 6
1998.la.hardware.sales 70
* -----------------------------------------------------
Retrieving the data using SQL2GMS
from the database and including the queried data in your GAMS model within the parameter declaration (at compile time) can be combined in the following way (note that the parameter has a fourth index type
in order to access the data type sales
resp. profit
):
$onEcho > howToRead.txt
C=DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};dbq=Sample.accdb
Q=SELECT year, loc, prod, 'sales', sales FROM data UNION SELECT year, loc, prod, 'profit', profit FROM data
O=data.inc
$offEcho
Set
year 'years' / 1997*1998 /
loc 'locations' / nyc, was, la, sfo /
prd 'products' / hardware, software /
type 'data type' / sales, profit /;
$call sql2gms @howToRead.txt
Parameter data(year,loc,prd,type) /
$include data.inc
/;
This example is also part of the GAMS Data Utilities Library, see model [SalesProfitDB5] for reference.
Example 3 - Reading a one dimensional Set
This example demonstrates how to read set elements of a one dimensional set from a single column of a database file. Suppose we want to read the column City1 (see table distances) in order to define the set i
in the GAMS model. Make sure elements are unique by using the distinct operation within the SQL statement (otherwise there will be an error when including the file within the set definition in the GAMS model, as some set elements will be redefined):
The include file city1.inc looks like (header informations are removed in order to shorten the representation):
* -----------------------------------------------------
SAN-DIEGO
SEATTLE
* -----------------------------------------------------
All steps (data access via SQL2GMS
, set definition) can be combined:
$call sql2gms C="Provider=MMicrosoft.ACE.OLEDB.12.0;Data Source=Sample.accdb" Q="SELECT distinct(city1) FROM distances" O=city_i.inc
Set i 'canning plants' /
$include city_i.inc
/;
display i;
The display statement generates the following output in the listing file:
---- 56 SET i seattle , san-diego
Example 4 - Reading a multi dimensional Set
When reading a multi dimensional set from database and writing the results to an include file by using the O argument, one has to observe that the elements in the include file must have the correct format in order to be interpreted as element of a multi dimensional set. For instance, a line containing a
b
c
is not recognized as a proper set element of a three dimensional set. In particular, one has to add periods between the single elements, i.e. a
.b
.c
will be interpreted correctly.
Depending on your DBMS (DataBase Management System), these periods must be added explicitly in a different way within the SQL statement. E.g. add a dummy value field by adding a quoted blank to the select clause (index1, index2, index3 and dataTable are some placeholders):
or by adding the periods explicitly within the select clause (|| or & depending on DBMS):
For instance, suppose we want to define a two dimensional set
Set ij(i,j) 'canning plants - markets';
based on the data of the table distances stored in Sample.accdb. The following SQL2GMS
statement connects you to the database, queries the columns with the city names and adds an empty value field in order to create periods between the set elements:
sql2gms C="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Sample.accdb" Q="SELECT city1, city2, ' ' FROM distances" O=city_ij.inc
The include file city_ij.inc looks like (header informations are removed in order to shorten the representation):
* -----------------------------------------------------
SEATTLE.NEW-YORK ' '
SAN-DIEGO.NEW-YORK ' '
SEATTLE.CHICAGO ' '
SAN-DIEGO.CHICAGO ' '
SEATTLE.TOPEKA ' '
SAN-DIEGO.TOPEKA ' '
* -----------------------------------------------------
Without adding the empty value field, the resulting include file would look like (shortened):
* -----------------------------------------------------
SEATTLE NEW-YORK
SAN-DIEGO NEW-YORK
* -----------------------------------------------------
Since the periods are missing, the lines are not recognized as valid elements of a two dimensional set. All steps can be combined in the following way:
Set
i 'canning plants' / seattle, san-diego /
j 'markets' / new-york, chicago, topeka /;
$call sql2gms C="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Sample.accdb" Q="SELECT city1, city2, ' ' FROM distances" O=city_ij.inc
Set ij(i,j) 'two dimensional set' /
$include city_ij.inc
/;
display ij;
The display statement generates the following output in the listing file:
---- 75 SET ij two dimensional set new-york chicago topeka SAN-DIEGO YES YES YES SEATTLE YES YES YES
Note that there is no need to add periods explicitly when reading multi dimensional sets, if the results are written only to a GDX file by using the X and S resp. Y arguments, i.e. there is no need to modify the query:
when using SQL2GMS
in the following way:
sql2gms C="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Sample.accdb" Q="SELECT index1, index2, index3 FROM datatable" X=setData.gdx S=setName
which will generate the file setData.gdx with a three dimensional set named setName
containing the results of the query.
Example 5 - Reading Sets with Explanatory Text
In this example, we will demonstrate how to read set elements with explanatory text from a database file using SQL2GMS
. In the first place, we are going to write the query results to an include file, afterwards we use the Y argument in order to store the query results as a set with explanatory text in a GDX file.
Note the blanks and the mixed quotes in the column containing the explanatory text. The data can be accessed by the following query:
Writing the Query Results in an include File
The last column in the select clause will be used as explanatory text. Take in mind to add the argument B in order to handle text strings with embedded blanks or quotes. The following GAMS code accesses the data and writes the results to an include file setData.inc:
$call sql2gms C="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Sample.accdb" B Q="SELECT setElement, explText FROM setData" O=setData.inc
Set a /
$include setData.inc
/;
The resulting include file will look like (header informations are removed in order to shorten the representation):
* -----------------------------------------------------
firstSetElement "Explanatory text for the first 'set element'"
secondSetElement 'Explanatory text for the second "set element"'
thirdSetElement "Explanatory text for the third 'set element'"
fourthSetElement 'Explanatory text for the fourth set element'
* -----------------------------------------------------
Note the handling of the quotes according to the description in B.
Writing the Query Results in a GDX File
When storing the results of the query as a set with explanatory text in a GDX file, there is no need to observe embedded blanks or quotes manually, instead one can use the Y argument. The last column specified in the select clause of the SQL statement will be interpreted as explanatory text. The following GAMS code accesses the data and writes the results to a GDX file setData.inc:
$call sql2gms C="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Sample.accdb" Q="SELECT setElement, explText FROM setData" X=setData.gdx Y=set_b
Set b;
$gdxIn setData.gdx
$load b = set_b
$gdxIn
Note that the name of the set in the GDX file is set_b
(specified within the Y
argument), while the name of the GDX file was specified within the X argument.
Example 6 - Index Mapping
In some cases the index elements used in the database are not the same as in the GAMS model. E.g. consider the case where the GAMS model has defined a set as:
Set i / NY, DC, LA, SF /;
Now assume a data table looks like:
This means we have to map ‘new york' to ‘NY' etc. This mapping can be done in two places: either in GAMS or in the database.
Index Mapping done in GAMS
When we export the table directly, we get the following include file (header informations are removed in order to shorten the representation):
* -----------------------------------------------------
'new york' 100
'los angeles' 120
'san francisco' 105
'washington dc' 102
* -----------------------------------------------------
Note that the single quotes are added by activating the option B (quote blanks), as the index elements contain blanks. Accessing the data, importing the resulting include file and converting it to a different index space can be done by the following GAMS code:
Set i / NY, DC, LA, SF /;
Set idb 'from database' / 'new york', 'washington dc', 'los angeles', 'san francisco' /;
$call sql2gms C="DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};dbq=Sample.accdb" B O="city1.inc" Q="SELECT city, value FROM [example table]"
Parameter dbdata(idb) /
$include city1.inc
/;
Set mapindx(i,idb) / NY.'new york', DC.'washington dc', LA.'los angeles', SF.'san francisco' /;
Parameter data(i);
data(i) = sum(mapindx(i,idb), dbdata(idb));
display data;
The display statement generates the following output in the listing file:
---- 47 PARAMETER data NY 100.000, DC 102.000, LA 120.000, SF 105.000
This example is also part of the GAMS Data Utilities Library, see model [IndexMapping3] for reference.
Index mapping done in Database
The second approach is to handle the mapping inside the database. We can introduce a mapping table that looks like:
This table can be used in a join to export the data in a format we can use by executing the query:
The resulting include file looks like (header informations are removed in order to shorten the representation):
* -----------------------------------------------------
la 120
ny 100
sf 105
dc 102
* -----------------------------------------------------
All steps can be combined in the GAMS model:
Set i / NY, DC, LA, SF /;
$onEcho > howToRead.txt
C=DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};dbq=Sample.accdb
Q=SELECT [GAMS City], [value] FROM example_table, CityMapper WHERE CityMapper.[Access City]=example_table.city
O=city2.inc
$offEcho
$call sql2gms @howToRead.txt
Parameter data(i) /
$include city2.inc
/;
display data;
The display statement generates the following output in the listing file:
---- 38 PARAMETER data NY 100.000, DC 102.000, LA 120.000, SF 105.000
Note: MS Access allows table names with embedded blanks. In that case the table name can be surrounded by square brackets. Other databases may not allow this.
This example is also part of the GAMS Data Utilities Library, see model [IndexMapping4] for reference.
Multi-Query Batch Usage
In some cases a number of small queries need to be performed on the same database. However, several individual SQL2GMS
execution can become expensive, since there is significant overhead in starting Access and opening the database. For these cases, we have added the option to do multiple queries in one call. To execute several queries in a single SQL2GMS
call and write several GAMS include files containing the results of the queries, we can use the command-line arguments Qn and On. The structure of a multi-query call looks like:
C=DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};dbq=sample.accdb Q1=firstQuery O1=outputFileName.inc Q2=secondQuery O2=outputFileName.inc Q3=thirdQuery O3=outputFileName.inc
The terms firstQuery
, secondQuery
etc. are placeholders for some SQL statements. We see that the argument Qn
is matched by an argument On
. That means that the results of the n-th query are written to the n-th output file.
In case we want to store the results of a multi-query call to a single GDX file, we can use the command-line arguments Qn, Sn, An and Yn. The structure of a multi-query call when writing to a GDX file looks like:
C=DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};dbq=sample.accdb
X=sample.gdx
Q1=firstQuery
S1=setName
Q2=secondQuery
S2=setName
Q3=thirdQuery
A3=parameterName
Q4=fourthQuery
A4=setName
Again, the terms firstQuery
, secondQuery
etc. are placeholders for some SQL statements. Here we see that a query Qn
is matched by either a set name Sn
or a parameter name An
(the letter P
was taken already: it is used to specify a password), i.e. the results of the first query will be stored as a set whose name is specified within the S1
argument, the results of the third query will be stored as a parameter whose name is specified within the A3
argument etc. The X
argument is used to specify the name of the GDX file.
For a complete example see section Example 7 - Multi-Query Batch Example .
Example 7 - Multi-Query Batch Example
As an example database we use the following Access table (file Sample.mdb):
We want to extract the following information:
- The set year
- The set loc
- The set prd
- The parameter sales
- The parameter profit
Output: Several include Files
This can be accomplished using the following GAMS code with multiple queries in a single SQL2GMS
call (note the usage of the distinct operator in the select clauses of the queries whose results will be used as sets in order to keep the set elements unique):
$onEcho > howToRead.txt
C=DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};dbq=Sample.accdb
Q1=SELECT distinct(year) FROM data
O1=year.inc
Q2=SELECT distinct(loc) FROM data
O2=loc.inc
Q3=SELECT distinct(prod) FROM data
O3=prod.inc
Q4=SELECT prod, loc, year, sales FROM data
O4=sales.inc
Q5=SELECT prod, loc, year, profit FROM data
O5=profit.inc
$offEcho
$call =sql2gms @howToRead.txt
Set y 'years' /
$include year.inc
/;
Set loc 'locations' /
$include loc.inc
/;
Set prd 'products' /
$include prod.inc
/;
Parameter sales(prd,loc,y) /
$include sales.inc
/;
display sales;
Parameter profit(prd,loc,y) /
$include profit.inc
/;
display profit;
This example is also part of the GAMS Data Utilities Library, see model [SalesProfitDB6] for reference.
Output: A single GDX File
The same example imported through a GDX file can look like:
$onEcho > howToRead.txt
C=Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Sample.accdb
X=Sample.gdx
Q1=SELECT distinct(year) FROM data
S1=year
Q2=SELECT distinct(loc) FROM data
S2=loc
Q3=SELECT distinct(prod) FROM data
S3=prd
Q4=SELECT prod, loc, year, sales FROM data
A4=sales
Q5=SELECT prod, loc, year, profit FROM data
A5=profit
$offEcho
$call =sql2gms @howToRead.txt
$call ="%gams.sysdir%studio/studio" Sample.gdx
Set
y 'years'
loc 'locations'
prd 'products';
Parameter
sales(prd,loc,y)
profit(prd,loc,y);
$gdxIn sample.gdx
$load y=year prd loc sales profit
$gdxIn
display sales, profit;
The call of the GDXViewer will display the GDX file in the stand-alone GDX viewer. This example is also part of the GAMS Data Utilities Library, see model [SalesProfitDB7] for reference.
Interactive Usage
When the tool is called without command-line arguments, it will startup interactively. Using it this way, one can specify options such as the connection string, the query and the final destination file (a GAMS include file or a GDX file) using the built-in interactive environment. The main screen (see figure below) contains a number of buttons and edit boxes, which are explained below.
- Output GAMS Include file (*.inc). If you want to create a GAMS include file, then specify here the destination file. See also outputIncludeFile for some more detailed remarks.
- Output GDX file (*.gdx). As an alternative to a GAMS include file, the tool can also generate a GDX file. One or both of the output files need to be specified. See also outputGDXFile for some more detailed notes.
- SQL Query. The SQL Query box is the place to provide the query. Note that the actual area for text can be larger than is displayed: use the cursor-keys to scroll. See also Q for some more detailed notes.
- Progress Memo. This memo field is used to show progress of the application. Also error messages from the database are printed here. This is a read-only field.
- The edit boxes above all have a drop down list which can be used to access quickly file names and queries that have been used earlier (even from a previous session).
- The Tables button will pop up a new window with the contents of the database file selected in the input file edit line. This allows you to see all table names and field names needed to specify a correct SQL query. An exception will be generated if no database file name is specified in the input edit line.
- The Options button will pop up a window where you can specify a number of options. The connection string is an important option, which needs to be set correctly before a query can be submitted successfully.
- Pressing the Help button will show this documentation.
- Pressing the OK button will execute the query and an output file will be generated.
- The Batch button will give information on how the current extract command can be executed directly from GAMS in a batch environment. The batch call will be displayed and can be copied onto the clipboard. In the IDE press
Ctrl-C
or chooseEdit|Paste
to copy the contents of the clipboard to a GAMS text file.
- Pressing the Close button will exit the application. The current settings will be saved in an INI file so when you run
SQL2GMS
again all current settings will be restored.
Options
The Options window can be created by pressing the options button:
The following options are available in the options window:
- User name: Here you can specify the user name for logging in to the RDBMS. For databases without user authentication, this can be left empty.
- Password: This edit box allows you to specify the password for the database system. The characters are echoed as a '*'.
- Connection String: The connection string determines how
SQL2GMS
talks to the database. For more informations see Connection Strings.
- ODBC Data Sources/Drivers: This drop down list can be used to compose a connection string when an ODBC data source or driver is involved. The list will show all configured data sources and all available ODBC drivers.
- Quote blanks: Quote strings if they contain blanks or embedded quotes. See also B for some more detailed notes.
- Mute: Don't include the extra informational text (such as used query etc.) in the include file. The equivalent command-line argument is M.
- No listing: Surround the include file by
$offListing
and$onListing
so that the data will not be echoed to the listing file. The equivalent command-line argument is L.
- Format SQL: If an SQL text is reloaded in the SQL Edit Box, it will be formatted: keywords will be printed in CAPS and the FROM and WHERE clause will be printed on their own line. If this check box is unchecked this formatting will not take place and SQL queries will be shown as is.
The following options are only needed in special cases:
- NULL: This radio box determines how NULL's are handled. A NULL in an index position or a value column will usually make the result of the query useless: the GAMS record will be invalid. To alert you on NULL's the default to throw an exception is a safe choice. In special cases you may want to map NULL's to an empty string or a 'NULL' string.
- Time-out values for connection time and command execution time expressed in seconds. If -1 is specified then it will use the default value which is 15 seconds for the connection and 30 for the commands. See also T1, T2 resp. T for some more detailed notes.
The following buttons have an obvious functionality:
- OK button will accept the changes made.
- Cancel button will ignore the changes made and all option settings will revert to their previous values.
- Help button will show this documentation.
- Test Connection will try to make a connection to the database using the given connection string.
If the settings are correct you will see something like:
The following options can only be specified in an INI file; there is no interactive equivalent:
Key | Type | Meaning |
---|---|---|
D | Generate debug output | |
E | Allow an empty result set; without this option an empty result set will generate an error | |
R | integer | Row batch size; the default is 100 records |
Connection Strings
The connection string determines to which database the tool will try to connect. You can give simply the name of an ODBC Data Source or provide much more elaborate connection strings. Here is an example list:
- ODBC Examples
Source Connection String ODBC Data Source MyDSN
ODBC Data Source DSN=MyDSN
ODBC DSN with userid and password DSN=xxx;UID=yyy;PWD=zzz;
ODBC File DSN FILEDSN=d:\ppp\fff.dsn;UID=yyy;PWD=zzz;
ODBC DSN-less Text Driver Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=d:\ppp\; Extensions=asc,csv,tab,txt;Persist Security Info=False
(Note: the filename is used in the FROM clause in the query string.)ODBC DSN-less MS Access Driver Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=d:\ppp\fff.accdb;Uid=yyy;Pwd=zzz;
ODBC DSN-less driver for MS SQL Server Driver={SQL Server};Server=sss;Database=ddd;Uid=yyy;Pwd=zzz;
ODBC DSN-less MS Access Driver Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=d:\ppp\fff.accdb;Uid=yyy;Pwd=zzz;
ODBC DSN-less driver for MS SQL Server Driver={SQL Server};Server=sss;Database=ddd;Uid=yyy;Pwd=zzz;
ODBC Driver for Oracle Driver={Microsoft ODBC for Oracle};Server=sss;Uid=yyy;Pwd=zzz
ODBC Driver for Oracle (old) Driver={Microsoft ODBC Driver for Oracle};ConnectString=sss; Uid=yyy; Pwd=zzz;
ODBC driver for MySQL DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=test; UID=xxx;PWD=yyy;OPTION=3
See http://www.mysql.com/products/myodbc/manual_toc.htmlODBC driver for Interbase 6/Firebird DRIVER={XTG Systems InterBase6 ODBC driver};DB=localhost:d:\gams projects\sql2gms\ver2.0\ib.gdb;UID=xxx;PWD=yy
- OLE DB Examples
Source Connection String OLE DB Data link file File name=d:\ppp\fff.udl;
OLE DB Provider for ODBC Access (Jet) Provider=MSDASQL; Driver={SQL Server}; Server=sss; Database= ddd; Uid=yyy; Pwd=zzz;
OLE DB Provider for ODBC SQL Server Provider=MSDASQL; Driver={SQL Server}; Server=sss; Database= ddd; Uid=yyy; Pwd=zzz;
OLE DB Provider for Microsoft Jet (Access) Provider=Microsoft.ACE.OLEDB.12.0; Data Source=d:\ppp\fff.accdb; User Id=yyy; Password=zzz;
OLE DB Provider for Microsoft Jet (Access) with System Database Provider=Microsoft.ACE.OLEDB.12.0; Data Source=d:\ppp\fff.accdb; Jet OLEDB:System Database=fff.mdw;
Specify user id and password in the U=xxx and P=zzz SQL2GMS options. If ACCDB file has a password, add: Jet OLEDB:Database Password= xxx;.OLE DB Provider for SQL Server Provider=sqloledb; Network Library=DBMSSOCN; Data Source=ddd ; Initial Catalog=ccc; User Id=yyy; Password=zzz;
OLE DB Provider for SQL Server with trusted connection security Provider=sqloledb; Network Library=DBMSSOCN; Data Source=ddd; Initial Catalog=ccc; Trusted_Connection=yes;
- MS Remote Examples
Source Connection String MS Remote - Access (Jet) through ODBC DSN Remote Server=http://xxx; Remote Provider=MSDASQL; DSN= nnn; Uid=yyy; Pwd=zzz;
MS Remote - Access (Jet) through OLE DB Provider Provider=MS Remote; Remote Server=http://xxx; Remote Provider=Microsoft.ACE.OLEDB.12.0; Data Source=d:\ppp\fff.accdb; Uid=yyy; Pwd=zzz;
MS Remote - Access (Jet) through OLE DB Provider with an RDS Datafactory Custom Handler Provider=MS Remote; Remote Server=http://xxx; Handler=MSDFMAP.Handler; Data Source=MyConnectTag
The entry in \winnt\Msdfmap.ini is: [connect MyConnectTag] Access=ReadWrite Connect="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=xxx.accdb; User Id=yyy; Password=zzz;"
MS Remote - SQL Server using ODBC DSN Remote Server=http://xxx; Remote Provider=MSDASQL; Network Library=DBMSSOCN; DSN=nnn; Uid=yyy; Pwd=zzz;
MS Remote - SQL Server using OLE DB Provider Provider=MS Remote; Remote Server=http://xxx; Remote Provider=SQLOLEDB; Network Library=DBMSSOCN;
Data Source=nnn; Initial Catalog=ddd; User Id=yyy; Password=zzz;MS Remote - SQL Server through OLE DB Provider with an RDS Datafactory Custom Handler Provider=MS Remote; Remote Server=http://xxx; Handler=MSDFMAP.Handler; Data Source=MyConnectTag
The entry in \winnt\Msdfmap.ini is: [connect MyConnectTag] Access=ReadWrite Connect="Provider=SQLOLEDB; Network Library=DBMSSOCN; Data Source=nnn; Initial Catalog=ddd; User Id=yyy; Password=zzz;"
For more information consult the documentation with your database driver. ODBC drivers can be had from several sources: Microsoft delivers ODBC with a number of drivers; database providers may have likely an ODBC driver for their RDBMS available and finally there are a number of third party ODBC drivers available (e.g. from http://www.easysoft.com ).
ODBC Examples
In this section we show a few examples using SQL2GMS
with ODBC data sources.
ODBC Driver Manager
To configure ODBC data sources use the ODBC Data Source Administrator
. This tool can be invoked from the Start button menu: Settings|Control Panel
, and click on the ODBC Data Sources
icon:
Under Windows XP the sequence is: Control Panel|Performance
and Maintenance|Administrative Tools
and click on the Data Sources (ODBC)
icon. Under Windows 10, access the Control Panel
at first, type 'odbc' in the top right box and select either 'Set up ODBC data sources (32-bit)' or 'Set up ODBC data sources (64-bit)'. The ODBC Data Source Administrator tool looks like:
To create a new data source, click the Add button, select a driver, give it a name (this is the DSN name) and configure the data source.
Example 8 - Reading from an MS Access Database
There are several ways to export data from an SQL database into a GAMS include file:
- Export a CSV (Comma Separated Values) file using Access Export.
- Use the MDB2GMS tool.
- Use
SQL2GMS
with a configured ODBC data source. The connection string will look like:"DSN=mydsn".
- Use
SQL2GMS
with a DSN-less ODBC connection. In this case we need to specify both the driver and the location of the database file explicitly in the connection string. The connection string will look like:"Driver=Microsoft Access Driver (*.mdb, *.accdb);dbq=D:\data\mydata.accdb".
- Use
SQL2GMS
with an OLE DB driver. The connection string can look like:"Microsoft.ACE.OLEDB.12.0;Data Source=D:\data\mydata.accdb"
Example 9 - Reading from an MS Excel Spreadsheet
There are numerous ways to export data from an Excel spreadsheet into a GAMS include file:
- Export a CSV (Comma Separated Values) file using Excel Export.
- Use the XLS2GMS tool.
- Use the GDXXRW tool.
- Use
SQL2GMS
with an Excel ODBC connection. An example is shown below.
Consider the spreadsheet ExcelDist.xls:
After configuring a data source ExcelDist that uses the Excel ODBC driver and points to the .xls file containing the above sheet, we can use the connection string: "DSN=ExcelDist"
. With the database browser we see:
I.e. the table name is Sheet1$
. We now can formulate the query: SELECT city1, city2, distance FROM [Sheet1$]
. We need the brackets to protect the funny table name. The result is (header removed in order to shorten the presentation=:
* -----------------------------------------------------
SEATTLE.NEW-YORK 2.5
SAN-DIEGO.NEW-YORK 2.5
SEATTLE.CHICAGO 1.7
SAN-DIEGO.CHICAGO 1.8
SEATTLE.TOPEKA 1.8
SAN-DIEGO.TOPEKA 1.4
* -----------------------------------------------------
Although other tools are often more convenient to use, this approach is useful if you need to select a subsection of the spreadsheet table. It is easy to select just a few columns or rows from a table using a properly formulated SQL query. The skeleton would be: SELECT columns_to_extract FROM [sheet1$] WHERE rows_to_extract
.
An example of a more complex spreadsheet is (profit.xls):
A DSN-less connection string would be: "DRIVER=Microsoft Excel Driver (*.xls); DBQ=d:\gams projects\sql2gms\ver2.0\profit.xls"
. The browser will show:
A possible query that maps the two value columns into a GAMS parameter is:
SELECT year, loc, prod, 'sales', sales
FROM [profitdata$]
UNION
SELECT year, loc, prod, 'profit', profit
FROM [profitdata$]
The result is (shortened for presentation):
* -----------------------------------------------------
1997.la.hardware.profit 8
1997.la.hardware.sales 80
1997.la.software.profit 16
1997.la.software.sales 60
1997.nyc.hardware.profit 5
1997.nyc.hardware.sales 110
1997.nyc.software.profit 10
1997.nyc.software.sales 100
1997.sfo.hardware.profit 9
1997.sfo.hardware.sales 80
1997.sfo.software.profit 10
1997.sfo.software.sales 50
1997.was.hardware.profit 7
1997.was.hardware.sales 120
1997.was.software.profit 20
1997.was.software.sales 70
1998.la.hardware.profit 6
1998.la.hardware.sales 70
* -----------------------------------------------------
This example is also part of the GAMS Data Utilities Library, see model [Excel] for reference.
Example 10 - Reading from a Text File
Microsoft delivers ODBC with a text file driver which allows you to read a text file as if it is a database table.
A fixed format file such as:
City1 City2 Distance SEATTLE NEW-YORK 2.5 SAN-DIEGO NEW-YORK 2.5 SEATTLE CHICAGO 1.7 SAN-DIEGO CHICAGO 1.8 SEATTLE TOPEKA 1.8 SAN-DIEGO TOPEKA 1.4
can be read using Fixed Length setting of the text driver:
The resulting include file will look like:
* -----------------------------------------------------
* SQL2GMS Version 2.0, January 2004
* Erwin Kalvelagen, GAMS Development Corp
* -----------------------------------------------------
* ADO version: 2.7
* Connection string: DSN=text
* Query: SELECT city1, city2, distance FROM odbcdata.txt
* Provider: MSDASQL
* -----------------------------------------------------
SEATTLE.NEW-YORK 2.5
SAN-DIEGO.NEW-YORK 2.5
SEATTLE.CHICAGO 1.7
SAN-DIEGO.CHICAGO 1.8
SEATTLE.TOPEKA 1.8
SAN-DIEGO.TOPEKA 1.4
* -----------------------------------------------------
Note that the text file is specified directly within the FROM clause.
A CSV file can be interpreted as a table as well, or any other separated format. We will try to read:
City1;City2;Distance SEATTLE;NEW-YORK;2.5 SAN-DIEGO;NEW-YORK;2.5 SEATTLE;CHICAGO;1.7 SAN-DIEGO;CHICAGO;1.8 SEATTLE;TOPEKA;1.8 SAN-DIEGO;TOPEKA;1.4
This can be read using:
The actual formats used are stored by ODBC in an INI file schema.ini (located in the directory of the data files) which can be inspected directly:
[odbcdata.txt] ColNameHeader=True Format=FixedLength MaxScanRows=25 CharacterSet=ANSI Col1=city1 Char Width 11 Col2=city2 Char Width 11 Col3=distance Float Width 10 [odbcdata2.txt] ColNameHeader=True Format=Delimited(;) MaxScanRows=25 CharacterSet=OEM Col1=City1 Char Width 255 Col2=City2 Char Width 255 Col3=Distance Float
This example is also part of the GAMS Data Utilities Library, see model [Text] for reference.
Strategies
Including SQL statements to extract data from a database inside your model can lead to a number of difficulties:
- The database can change between runs, leading to results that are not reproducible. A possible scenario is a user calling you with a complaint: "the model is giving strange results". You run the model to verify and now the results are ok. The reason may be because the data in the database has changed.
- There is significant overhead in extracting data from a database. If there is no need to get new data from the database it is better to use a snapshot stored locally in a format directly accessible by GAMS.
- It is often beneficial to look at the extracted data. A first reason, is just to make sure the data arrived correctly. Another argument is that viewing data in a different way may lead to a better understanding of the data. A complete "under-the-hood" approach may cause difficulties in understanding certain model behavior.
Often it is a good strategy to separate the data extraction step from the rest of the model logic.
If the sub-models form a chain or a tree, like in:
Data Extraction --> Data Manipulation --> Model Definition --> Model Solution --> Report Writing
we can conveniently use the save/restart facility. The individual submodel are coded as:
- Step 0 - Data Extraction: sr0.gms
$onText step 0: data extraction from database execute as: > gams sr0 save=s0 $offText Set i 'suppliers' j 'demand centers'; Parameter demand(j) supply(i) dist(i,j) 'distances'; $onEcho > howtoRead.txt C=Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%system.fp%transportation.accdb Q1=SELECT name FROM suppliers O1=i.inc Q2=SELECT name FROM demandcenters O2=j.inc Q3=SELECT name,demand FROM demandcenters O3=demand.inc Q4=SELECT name, supply FROM suppliers O4=supply.inc Q5=SELECT supplier, demandcenter, distance FROM distances O5=dist.inc $offEcho $call =sql2gms.exe @howtoRead.txt Set i / $include i.inc /; Set j / $include j.inc /; Parameter demand / $include demand.inc /; Parameter supply / $include supply.inc /; Parameter dist / $include dist.inc /; display i, j, demand, supply, dist;
- Step 1 - Data Manipulation: sr1.gms
$onText step 1: data manipulation step execute as: > gams sr1 restart=s0 save=s1 $offText Scalar f 'freight in dollars per case per thousand miles' / 90 /; Parameter c(i,j) 'transport cost in thousands of dollars per case'; c(i,j) = f*dist(i,j)/1000;
- Step 2 - Model Definition: sr2.gms
$onText step 2: model definition execute as: > gams sr2 restart=s1 save=s2 $offText Variable x(i,j) 'shipment quantities in cases' z 'total transportation costs in thousands of dollars'; Positive Variable x; Equation ecost 'define objective function' esupply(i) 'observe supply limit at plant i' edemand(j) 'satisfy demand at market j'; ecost.. z =e= sum((i,j), c(i,j)*x(i,j)); esupply(i).. sum(j, x(i,j)) =l= supply(i); edemand(j).. sum(i, x(i,j)) =g= demand(j);
- Step 3 - Model Solution: sr3.gms
$onText step 3: model solution execute as: > gams sr3 restart=s2 save=s3 $offText option lp = cplex; Model transport / all /; solve transport using lp minimizing z;
- Step 4 - Report Writing: sr4.gms
$onText step 4: report writing execute as: > gams sr4 restart=s3 $offtext abort$(transport.modelStat <> 1) "model not solved to optimality"; display x.l, z.l;
A model that executes all steps can be written as:
execute '=gams.exe sr0 lo=3 save=s0';
abort$errorLevel "step 0 failed";
execute '=gams.exe sr1 lo=3 restart=s0 save=s1';
abort$errorLevel "step 1 failed";
execute '=gams.exe sr2 lo=3 restart=s1 save=s2';
abort$errorLevel "step 2 failed";
execute '=gams.exe sr3 lo=3 restart=s2 save=s3';
abort$errorLevel "step 3 failed";
execute '=gams.exe sr4 lo=3 restart=s3';
abort$errorLevel "step 4 failed";
If you only change the reporting step, i.e. generating some output using PUT
statements, then you only need to change and re-execute step 4. If you change solver or solver options, then only steps 3 and 4 need to be redone. For a small model like this, this exercise may not be very useful, but when the model is large and every step is complex and expensive, this is a convenient way to achieve quicker turn-around times in many cases.
The model [SQLSr5] is also part of the GAMS Data Utilities Library.
In some cases the save/restart facility is not appropriate. A more general approach is to save the data from the database in a GDX file, which can then be used by other models. We can use the model from step 0 to store the data in a GDX file:
SQL2GDX1.gms
$onText
Store data from Access database into a GDX file.
$offText
execute '=gams.exe sr0 lo=3 gdx=trnsport.gdx';
abort$errorLevel "step 0 failed";
execute '=gdxviewer.exe trnsport.gdx';
The model [SQL2GDX1] is also part of the GAMS Data Utilities Library.
We can also let SQL2GMS
create the GDX file:
SQL2GDX2.gms
$onText
Store data from Access database into a GDX file.
$offText
$onEcho > howToRead.txt
C=Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%system.fp%transportation.accdb
X=%system.fp%transportation.gdx
Q1=SELECT name FROM suppliers
S1=i
Q2=SELECT name FROM demandcenters
S2=j
Q3=SELECT name, demand FROM demandcenters
A3=demand
Q4=SELECT name, supply FROM suppliers
A4=supply
Q5=SELECT supplier, demandcenter, distance FROM distances
A5=dist
$offEcho
$call =sql2gms.exe @howToRead.txt
The first approach has the advantage that a complete audit record is available from the data moved from the database to the GDX file in the sr0.lst listing file. If someone ever wonders what came out of the database and how this was stored in the GDX file, that file gives the answer.
The model [SQL2GDX2] is also part of the GAMS Data Utilities Library.
To load the GDX data the following fragment can be used:
GDXTRNSPORT.gms
This model demonstrates how to load the transportation data from GDX file at compile time.
Set
i 'suppliers'
j 'demand centers';
Parameter
demand(j)
supply(i)
dist(i,j) 'distances';
$gdxIn transportation.gdx
$load i j demand supply dist
$gdxIn
display i, j, demand, supply, dist;
DBTimestamp2.gms
In one application I had to retrieve data from the database each morning, at the first run of the model. The rest of the day, the data extracted that morning could be used. The following logic can implement this:
$onText
Retrieve data from data base first run each morning.
$offText
$onEcho > getdate.txt
C=Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%system.fp%transportation.accdb
Q=select day(now())
O=dbtimestamp.inc
$offEcho
$if not exist dbtimestamp.inc $call "echo 0 > dbtimestamp.inc"
Scalar dbtimestamp 'day of month when data was retrieved' /
$include dbtimestamp.inc
/;
Scalar currentday 'day of this run';
currentday = gday(jnow);
display "compare", dbtimestamp, currentday;
if(dbtimestamp<>currentday,
execute '=gams.exe sr0 lo=3 gdx=transportation.gdx';
abort$errorLevel "step 0 (database access) failed";
execute '=sql2gms.exe @getdate.txt'
);
The include file dbtimestamp.inc contains the day of the month (1,..,31) on which the data was extracted from the database. If this file does not exist, we initialize it with 0. We then compare this number with the current day of the month. If the numbers do not agree, we execute the database extraction step and rewrite the dbtimestamp.inc file. This last operation could be done using a PUT
statement, but in this case we used an SQL statement.
The model [DBTimestamp2] is also part of the GAMS Data Utilities Library.
Parameter Files
Parameters can be specified in an external parameter file. This is important if the length of the command-line exceeds 255 characters, which is a hard limit on the length that GAMS allows for command-lines. Instead of specifying a long command line as in:
$call =sql2gms C="DSN=sample" O="c:\My Documents\data.inc" Q="SELECT * FROM mytable"
we can use a command line like:
$call =sql2gms @"c:\My Documents\options.txt"
The parameter file
c:\My Documents\options.txt
can look like:
C=DSN=sample O=c:\My Documents\data.inc Q=SELECT * FROM mytable
It is possible to write the parameter file from inside a GAMS model using the $echo
command. The following example will illustrate this:
$set cmdfile "c:\windows\temp\commands.txt"
$echo "C=DSN=sample" > "%cmdfile%"
$echo "O=E:\models\labor.INC" >> "%cmdfile%"
$echo "Q=SELECT * FROM labor" >> "%cmdfile%"
$call =sql2gms @"%cmdfile%"
Parameter p /
$include "E:\models\labor.INC"
/;
display p;
Newer versions of GAMS allow the usage of the $onEcho
and $offEcho
commands:
$set cmdfile "c:\windows\temp\commands.txt"
$onEcho > "%cmdfile%"
C=DSN=sample
O=E:\models\labor.INC
Q=SELECT * FROM labor
$offEcho
$call =sql2gms @"%cmdfile%"
Parameter p /
$include "E:\models\labor.INC"
/;
display p;
If a query becomes very long, it is possible to spread it out over several lines. To signal a setting will continue on the next line insert the character \ as the last character. E.g.:
Q=SELECT prod, loc, year, 'sales', sales FROM data \ UNION \ SELECT prod, loc, year, 'profit', profit FROM data
Notes
ADO
ActiveX Data Objects. Microsoft's data-access object model. An object-oriented architecture for accessing data stored in SQL databases and related data sources. Accessible from a large number of host languages such as VB, C++, Delphi. Supersedes ODBC. Many SQL databases provide ADO access through either OLEDB or ODBC.
ODBC
An API and driver manager system for accessing data stored in an RDBMS. The API provides applications a way to talk to databases while the driver manager application allows users to install, configure and manage ODBC drivers for different databases.
OLEDB
Driver architecture for SQL databases. A driver is called a OLE DB provider. This is used from ADO.
UNC Names
UNC means Unified Naming Convention. UNC names are a Microsoft convention to name files across a network. The general format is:
\\<server>\<share>\<path>\<file>
Examples:
\\athlon\c\My Documents\sql2gms.rtf
GDX Files
A GDX file contains GAMS data in binary format. The following GAMS commands will operate on GDX files: $gdxIn, $load, execute_load, execute_unload. The GDX=filename command-line option will save all data to a GDX file. A GDX file can be viewed in the GAMS IDE using File|Open
.
MDB2GMS
MDB2GMS
is a tool to import tables from MS Access databases. This utility directly uses MS Access and DAO (Data Access Objects) resulting in a somewhat simpler interface. It is not needed to specify a connection string, but just a .accdb or .mdb file. The query mechanism is similar: a query is sent as it is to the database server and the result set is translated into a GAMS representation. For more information see MDB2GMS.
XLS2GMS
XLS2GMS
is a tool to import data from an Excel spreadsheet. It considers the content of a selected range as GAMS source code. For more information XLS2GMS.
GDXXRW
GDXXRW
is a utility to read and write Excel spreadsheet data. GDXXRW
can read multiple ranges in a spreadsheet and write the data to a GDX file, or read from a GDX file, and write the data to different ranges in a spreadsheet. For more information GDXXRW.
Quotes
Examples of handling of indices when the option B for quoting strings containing blanks is used:
Input | Output | Remarks |
---|---|---|
Hello | hello | blanks or embedded quotes |
"hello" | "hello" | touched, is quoted already |
'hello' | 'hello' | id. |
"hello' | "hello' | id., but will generate an error in GAMS |
o'brien | "o'brien" | |
'o'brien' | 'o'brien' | touched, will generate an error in GAMS |
art"ificial | 'art"ificial' | |
art"ifi'cial | "art'ifi'cial" |
$CALL Command
The $call
command in GAMS will execute an external program at compile time. There are two forms:
$call externalProgram
$call =externalProgram
The version without the leading '=' calls the external through the command processor (command.com or cmd.exe). The second version with the '=' bypasses the command processor and directly executes the external program. We mention some of the differences:
- Some commands are not external programs but built-in commands of the command processor. Examples are COPY, DIR, DEL, ERASE, CD, MKDIR, MD, REN, TYPE. If you want to execute these commands you will need to use the form
$call externalProgram
which uses the command processor. - If you want to execute a batch file (.bat or .cmd file) then you will need to use the form
$call externalProgram
. - If it is important to stop with an appropriate error message if the external program does not exist, only use the form
$call =externalProgram
. The other form is not reliable in this respect. This can lead to surprising results and the situation is often difficult to debug, so in general we would recommend to use the form:$call =externalProgram
. - When calling pure Windows programs it is important to call the second form. The first form will not wait until the external Windows program has finished. If it is important to use a command processor in the invocation of a Windows program, use the
START
command, as in:$call start /w externalWindowsProgram
. Otherwise, it is preferred to use:$call =externalWindowsProgram
.
- Attention
- In general it is recommended to use the
$call =externalProgram
version for its better error-handling.
When command-line arguments need to be passed to the external program, they can be added to the line, separated by blanks:
$call externalProgram parameter1 parameter2
$call =externalProgram parameter1 parameter2
The total length of the command-line can not exceed 255 characters. If the program name or the parameters contain blanks or quotes you will need to quote them. You can use single or double quotes. In general the following syntax will work:
$call '"external program" "parameter 1" "parameter 2"'
$call ="external program" "parameter 1" "parameter 2"
It is noted that the first form needs additional quotes around the whole command-line due to bugs in the parsing of the $call in GAMS. The second form work without additional quotes only if the = appears outside the double quotes.
Compile Time Commands
All $
commands in GAMS are performed at compile time. All other statements are executed at execution time. This means that a compile time command will be executed before an execution time command, even if it is below. As an example consider:
File batchfile / x.bat /;
putClose batchfile "dir"/;
$call x.bat
This fragment does not work correctly as already during compilation, the $call
is executed, while the put statements are only executed after the compilation phase has ended and GAMS has started the execution phase. The above code can be fixed by moving the writing of the batch file to compilation time as in
$echo "dir" > x.bat
$call x.bat
or by moving the external program invocation to execution time:
File batchfile / x.bat /;
putClose batchfile "dir"/;
execute x.bat;
Notice that all $
commands do not include a semi-colon but are terminated by the end-of-line.