Interfacing GAMS with other applications

Tutorial and examples

Erwin Kalvelagen, erwin@gams.com
Amsterdam Optimization
http://www.gams.com/~erwin

This document will illustrate how GAMS can cooperate with other programs, and how GAMS can be called from familiar programming environments.

The text in this document assumes you have access to a recent GAMS system. The latest GAMS system can always be downloaded from the download page http://www.gams.com/download.

This document is under permanent construction.

Content

0. What's new

1. Importing and exporting data

1.1 GAMS Syntax for flat file input

1.2 Importing from Excel

1.2.1 Comma delimited files

1.2.2 The XLS2GMS utility

1.2.3 Excel as database

1.3 Importing from Oracle

1.3.1 SQL*Plus

1.3.2 SQL2GMS

1.3.3 Dates

1.4 Importing from Access

1.4.1 Comma delimited files

1.4.2 The MDB2GMS utility

1.4.3 MDB2GMS advanced use

1.4.4 Download MDB2GMS

1.4.5 SQL2GMS

1.4.6 Dates

1.5 Importing from DB2

1.6 Importing from MySQL

1.7 Importing from SQL Server

1.8 Importing from Sybase

1.9 The SQL2GMS utility

1.9.1 Reading DBASE files

1.9.2 SQL2GMS advanced use

1.9.3 Download SQL2GMS

1.9.4 SQL2GMS script

1.9.5 SQL2GMS timeouts

1.10 GAMS Syntax for flat file output

1.11 Exporting to Excel

1.11.1 Excel Text Import

1.11.2 Excel CSV Import

1.11.3 GDX to Excel

1.11.4 GDX2XLS

1.11.5 GDX2XLS AutoFilter

1.12 Exporting to Oracle

1.12.1 Oracle CSV Import

1.12.2 GDX to Oracle

1.13 Exporting to Access

1.13.1 Access CSV Import

1.13.2 GDX to Access

1.13.3 VBScript

1.13.4 JScript

1.13.5 Combining GDX2ACCESS with VBscript

1.13.6 Using a ref file with GDX2ACCESS 2.2

1.14 Exporting to dBase

1.15 Exporting to DB2

1.16 Exporting to MySQL

1.17 Exporting to SQL Server

1.18 Exporting to Sybase

1.19 Other tools for spreadsheet reading and writing

1.20 Exporting to LaTeX

1.21 Exporting to Gnuplot

1.22 Exporting MPS files

1.23 Importing MPS files

1.23.1 MPS2GAMS

1.23.2 MPS2GMS

1.24 Importing NETGEN network problems

1.25 Importing GNETGEN network problems

1.26 Exporting to HTML

1.27 Exporting to XML

1.28 A Mini User Interface

1.28.1 The ASK utility

1.28.2 FileOpenBox

1.28.3 Download ASK

1.29 GDX Viewer

1.29.1 GAMS IDE built-in viewer

1.29.2 Standalone GDX Viewer

1.29.3 GDXViewer List View

1.29.4 GDXViewer Cube View

1.29.5 GDXViewer Graphs

1.29.6 GDXViewer Command Line Processing

1.29.7 Download GDXViewer

1.30 ShellExecute

1.31 Tips, tricks and caveats

Tip: extension to 3 and more dimensions

Tip: macro's for repetitions

Caveat: Non American English language settings

Tip: Mapping index label names I

Tip: Mapping index label names II

Tip: Save and restart

Tip: Save and restart 2

Tip: Once a day data extraction

Tip: Reading CSV files with SQL2GMS

2 Executing GAMS

2.1 GAMS return codes

2.2 Spawning GAMS

2.3 Spawning GAMS from C: a minimal example

2.4 Spawning GAMS from Visual Basic

2.5 Spawning GAMS from Delphi

2.6 Spawning GAMS from Excel and Access

2.7 Spawning GAMS from Visual C++

2.8 Spawning GAMS from Oracle (advanced example)

2.9 Spawning GAMS from Java

2.10 Spawning GAMS from a Web Server

2.11 Spawning GAMS from a Job Scheduler

2.11.1 The Windows Task Scheduler

2.11.2 The Windows NT AT Command

2.11.3 cron on Unix/Linux

2.12 Remote execution of GAMS jobs from the IDE

2.13 Running GAMS from an ActiveX component

2.14 Spawning GAMS from C#

3 External functions

3.1 A simple example

3.2 Spline interpolation

4 GDX API

4.1 GDX Introduction

4.2 API Documentation

4.3 Language bindings

0. What's new

1. Importing and exporting data

1.1 GAMS Syntax for flat file input

To start with the bad news: GAMS does not have an equivalent of a read statement. All data has to be available at compile time, and inserted into the compiler input stream. This may seem a significant drawback, and indeed for some applications this is really a problem, but in many cases we can live with the techniques discussed in this document.

A basic task when importing data is to separate model specification and data input. The include facility in GAMS is very helpful in this respect. For instance when the data for a table is actually coming from another environment, one could replace the TABLE statement by an include statement. A GAMS TABLE is in fact very well suited for a human being to be read or written, but it is rather awkward for programs to generate (e.g. the numbers have to be approximately below the corresponding headers). Therefore often parameters are used, and long series of assignments are generated. For instance consider the following fragment from the TRNSPORT model which can be found in the model library:

table d(i,j) 'distance in thousands of miles'
             NEW-YORK CHICAGO TOPEKA
    SEATTLE     2.5     1.7     1.8
    SAN-DIEGO   2.5     1.8     1.4 ;

parameter d(i,j) 'distance in thousands of miles';
$include data.inc
display d;

d("SEATTLE","NEW-YORK") = 2.5;
d("SAN-DIEGO","NEW-YORK") = 2.5;
d("SEATTLE","CHICAGO") = 1.7;
d("SAN-DIEGO","CHICAGO") = 1.8;
d("SEATTLE","TOPEKA") = 1.8;
d("SAN-DIEGO","TOPEKA") = 1.4;

parameter d(i,j) 'distance in thousands of miles';
$offlisting
$include data.inc
$onlisting
display d;

parameter d(i,j) 'distance in thousands of miles' /
$include data2.inc
/;
display d;

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: TABLEs and PARAMETERs in GAMS are really the same thing. Internally both are handled identically. The only difference is how data is entered. With a TABLE a tabular input format is used, while PARAMETERS are either calculated or inputted as a list.

1.2 Importing from Excel

1.2.1 Comma delimited files

table d(i,j) 'distance in thousands of miles'
             NEW-YORK CHICAGO TOPEKA
    SEATTLE     2.5     1.7     1.8
    SAN-DIEGO   2.5     1.8     1.4

Notice that we added a 'dummy' string in cell A1. This is necessary as we need a placeholder there (the underlying problem is a bug in GAMS: the comma should be enough to signal the end of a field). Now we save this worksheet as a CSV file - the type is: CSV (Comma delimited) - which will look like:

dummy,new-york,chicago,topeka
seattle,2.5,1.7,1.8
san-diego,2.5,1.8,1.4

table d(i,j) 'distance in thousands of miles'
$ondelim
$include data.csv
$offdelim
display d;

  40  table d(i,j)  'distance in thousands of miles'
INCLUDE    D:\SUPPORT\DATA.CSV
  43  dummy,new-york,chicago,topeka
  44  seattle,2.5,1.7,1.8
  45  san-diego,2.5,1.8,1.4
  47  display d;

1.2.2 XLS2GMS, a simple utility

The input file consists of an .XLS file. By default the used part of the first sheet is exported, but this can be changed by setting an appropriate range. An example of a range that can be specified is Sheet2!A1:G8. An output file is created that can be used as a GAMS include file. The browse buttons open a File Open or a Save As dialog box that can help you in navigating around on your hard disk. It is advised to use absolute paths, as for windows applications it is not always obvious what the current directory is.

The philosophy of the utility is that you write pure GAMS syntax in the spreadsheet. I.e. you can write GAMS statements such as TABLE, SET, PARAMETER or parts of them. It is your responsability to write correct GAMS syntax. The only "smart" thing the utility does it to align cells, such that GAMS TABLE data are correctly handled.

In the above example we have a simple spreadsheet:

The output for this spreadsheet will look like:

* -----------------------------------------------------
* XLS2GMS Version 1.3, September 2000
* Erwin Kalvelagen, GAMS Development Corp.
* -----------------------------------------------------
* Application: Microsoft Excel
* Version:     8.0e
* Workbook:    C:\My Documents\test.xls
* Sheet:       Sheet1
* Range:       $A$1:$C$3
* -----------------------------------------------------
   j1 j2
i1 1  4
i2 2  3
* -----------------------------------------------------

Some uses of XLS2GMS are found in:

1.2.3 Excel as database

SELECT year,loc,prod,'sales',sales FROM [profitdata$] UNION SELECT year,loc,prod,'profit',profit FROM [profitdata$]

$ontext

  Test MS EXCEL access through ODBC

$offtext

set y 'years'   /1997,1998/;
set c 'city'    /la,nyc,sfo,was/;
set p 'product' /hardware,software/;
set k 'key'     /sales,profit/;

$onecho > excelcmd.txt
c=DRIVER=Microsoft Excel Driver (*.xls);dbq=%system.fp%profit.xls;
q=SELECT year,loc,prod,'sales',sales FROM [profitdata$] UNION SELECT year,loc,prod,'profit',profit FROM [profitdata$]
o=excel.inc
$offecho
$call =sql2gms @excelcmd.txt

parameter d(y,c,p,k) /
$include excel.inc
/;
display d;

* -----------------------------------------------------
* SQL2GMS Version 2.2, May 2005
* Erwin Kalvelagen, GAMS Development Corp
* -----------------------------------------------------
* ADO version: 2.8
* Connection string: DRIVER=Microsoft Excel Driver (*.xls);dbq=C:\gams projects\wtools\ver002\examples\profit.xls;
* Provider: MSDASQL
* Query: SELECT year,loc,prod,'sales',sales FROM [profitdata$] UNION SELECT year,loc,prod,'profit',profit FROM [profitdata$]
* -----------------------------------------------------
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
1998.la.software.profit 10
1998.la.software.sales 70
1998.nyc.hardware.profit 7
1998.nyc.hardware.sales 120
1998.nyc.software.profit 14
1998.nyc.software.sales 120
1998.sfo.hardware.profit 12
1998.sfo.hardware.sales 90
1998.sfo.software.profit 15
1998.sfo.software.sales 70
1998.was.hardware.profit 12
1998.was.hardware.sales 130
1998.was.software.profit 15
1998.was.software.sales 80
* -----------------------------------------------------

----     65 PARAMETER d

INDEX 1 = 1997

                   sales      profit

la .hardware      80.000       8.000
la .software      60.000      16.000
nyc.hardware     110.000       5.000
nyc.software     100.000      10.000
sfo.hardware      80.000       9.000
sfo.software      50.000      10.000
was.hardware     120.000       7.000
was.software      70.000      20.000

INDEX 1 = 1998

                   sales      profit

la .hardware      70.000       6.000
la .software      70.000      10.000
nyc.hardware     120.000       7.000
nyc.software     120.000      14.000
sfo.hardware      90.000      12.000
sfo.software      70.000      15.000
was.hardware     130.000      12.000
was.software      80.000      15.000

1.3 Importing from Oracle

1.3.1 SQL*Plus

SQL> describe dist;
 Name                                Null?    Type
 ----------------------------------- -------- ------------------------
 LOCA                                NOT NULL VARCHAR2(10)
 LOCB                                NOT NULL VARCHAR2(10)
 DISTANCE                                     NUMBER

SQL> select * from dist;

LOCA       LOCB        DISTANCE
---------- ---------- ---------
seattle    new-york         2.5
seattle    chicago          1.7
seattle    topeka           1.8
san-diego  new-york         2.5
san-diego  chicago          1.8
san-diego  topeka           1.4

6 rows selected.

SQL>

set pagesize 0
set pause off
set heading off
spool data
select loca||','||locb||','||distance from dist;
spool off

seattle,new-york,2.5
seattle,chicago,1.7
seattle,topeka,1.8
san-diego,new-york,2.5
san-diego,chicago,1.8
san-diego,topeka,1.4

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

parameter d(i,j) 'distance in thousands of miles' /
$ondelim
$include data.lst
$offdelim
/;
display d;

1.3.2 SQL2GMS

1.3.3 Dates

For most software it is easy to generate dates that GAMS can import and understand. The most common issue is that GAMS is one day off compared to Excel, Delphi, Access, ODBC etc. Oracle is somewhat more involved. First it is useful to have the date/time exported as a Julian date. This can be done with the following stored procedure:

-- julian representation of a date/time 
-- Erwin Kalvelagen, feb 2007
create or replace function to_julian(d IN TIMESTAMP)
return number
is
begin
return to_number(to_char(d,'J')) + to_number(to_char(d,'SSSSS'))/86400;
end;

scalar
   refdategams   "march 16, 2006, 00:00"
   refdateoracle "march 16, 2006, 00:00"  /2453811/
   datediff      "difference between GAMS and Oracle date"
;
refdategams = jdate(2006,3,16);
datediff = refdategams-refdateoracle;  

1.4 Importing from Access

1.4.1 Comma delimited files

After choosing Save As/Export and selecting Text Files we get the following window:

Just using the default settings, we get the following file:

"seattle","new-york",2.50
"seattle","chicago",1.70
"seattle","topeka",1.80
"san-diego","new-york",2.50
"san-diego","chicago",1.80
"san-diego","topeka",1.40

parameter d(i,j) 'distance in thousands of miles' /
$ondelim
$include dist.txt
$offdelim
/;
display d;

1.4.2 MDB2GMS, a simple utility

To extract data from an Access database we use a technique called DAO or Data Access Objects. DAO in its turn uses the Microsoft Jet Database Engine to access the Access database. The name of the database file ( .mdb file) is specified in the Input file field. The output file is specified in the second edit control. The Browse buttons allow you to navigate around on your hard disks.

To select data the standard query language SQL is used. A query results in a table, which is formatted as a GAMS parameter initialization construct. Parameter initializations look in GAMS like:

parameter p(i,j) /
        i1.j1 10.0
        i1.j2 12.1
        i1.j3  8.2
/;

Note that the order of the columns is irrelevant unless you specify select *.

For the exact syntax of the queries that is accepted by Access we refer to the documentation that comes with MS Access. One notable syntax feature is that when field names or table names contain blanks, they can be specified in square brackets. Examples:

select * from mytable

select year, production from [production table]

select [GAMS City],value from [example table],CityMapper where [Access City]=city

• Quote blanks can be used to quote strings that contains blanks. If the string is already quoted this step is not performed. If the name contains an embedded single quote, the surrounding quotes will be double quotes. If the name already contains a double quote, the surrounding quotes will be single quotes. If both single and double quotes are present in the string, then all double quotes are replaced by single quotes and the surrounding quotes will be double quotes. The following examples will illustrate the behavior:

  input	output	remarks
  hello	'hello'
  "hello"	"hello"	not 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'	not touched, will generate an error in GAMS
  art"ificial	'art"ificial'
  art"ifi'cial	"art'ifi'cial"

  By default this option is turned off.
• mute will reduce the amount of information written to the output file. The version number etc. is suppressed.
• No listing will prevent the data to be echoed to the listing file. For large data sets this can reduce the size of the listing file.

The utility can also be used in batch mode. I.e. if you specify from the command line:

mdb2gms "I=c:\My Documents\test.mdb" O=test.inc Q="select * from data"

Consider the following table:

Assume we want to extract sales and profit data. This can be done with the following GAMS code:

set y 'years' /1997*1998/;
set loc 'locations' /nyc,was,la,sfo/;
set prd 'products' /hardware, software/;

parameter sales(prd,loc,y) /
$call =d:\util\mdb2gms I="C:\My Documents\test.mdb" Q="select prod,loc,year,sales from data" O=d:\tmp\sales.inc
$include d:\tmp\sales.inc
/;
display sales;

parameter profit(prd,loc,y) /
$call =d:\util\mdb2gms I="C:\My Documents\test.mdb" Q="select prod,loc,year,profit from data" O=d:\tmp\profit.inc
$include d:\tmp\profit.inc
/;
display profit;

   1  set y years /1997*1998/;
   2  set loc locations /nyc,was,la,sfo/;
   3  set prd products /hardware, software/;
   4
   5  parameter sales(prd,loc,y) /
INCLUDE    D:\TMP\SALES.INC
   8  * -----------------------------------------------------
   9  * MDB2GMS Version 1.2, September 2000
  10  * Erwin Kalvelagen, GAMS Development Corp
  11  * -----------------------------------------------------
  12  * DAO version: 3.51
  13  * Jet version: 3.0
  14  * Database:    C:\My Documents\test.mdb
  15  * Query:       select prod,loc,year,sales from data
  16  * -----------------------------------------------------
  17  software.nyc.1997 100
  18  software.was.1997 70
  19  software.la.1997 60
  20  software.sfo.1997 50
  21  hardware.nyc.1997 110
  22  hardware.was.1997 120
  23  hardware.la.1997 80
  24  hardware.sfo.1997 80
  25  hardware.la.1998 70
  26  software.la.1998 70
  27  hardware.nyc.1998 120
  28  software.nyc.1998 120
  29  hardware.sfo.1998 90
  30  software.sfo.1998 70
  31  hardware.was.1998 130
  32  software.was.1998 80
  33  * -----------------------------------------------------
  34  /;
  35  display sales;
  36
  37  parameter profit(prd,loc,y) /
INCLUDE    D:\TMP\PROFIT.INC
  40  * -----------------------------------------------------
  41  * MDB2GMS Version 1.2, September 2000
  42  * Erwin Kalvelagen, GAMS Development Corp
  43  * -----------------------------------------------------
  44  * DAO version: 3.51
  45  * Jet version: 3.0
  46  * Database:    C:\My Documents\test.mdb
  47  * Query:       select prod,loc,year,profit from data
  48  * -----------------------------------------------------
  49  software.nyc.1997 10
  50  software.was.1997 20
  51  software.la.1997 16
  52  software.sfo.1997 10
  53  hardware.nyc.1997 5
  54  hardware.was.1997 7
  55  hardware.la.1997 8
  56  hardware.sfo.1997 9
  57  hardware.la.1998 6
  58  software.la.1998 10
  59  hardware.nyc.1998 7
  60  software.nyc.1998 14
  61  hardware.sfo.1998 12
  62  software.sfo.1998 15
  63  hardware.was.1998 12
  64  software.was.1998 15
  65  * -----------------------------------------------------
  66  /;
  67  display profit;

  SEQ   GLOBAL TYPE      PARENT   LOCAL  FILENAME

     1        1 INPUT          0       0  D:\IMPORT\SRC\ACCESS\TEST1.GMS
     2        6 CALL           1       6  =d:\util\mdb2gms I="C:\My Documents\test.mdb" Q="select prod,loc,year,sales from data" O=d:\tmp\sales.inc
     3        7 INCLUDE        1       7  .D:\TMP\SALES.INC
     4       38 CALL           1      12  =d:\util\mdb2gms I="C:\My Documents\test.mdb" Q="select prod,loc,year,profit from data" O=d:\tmp\profit.inc
     5       39 INCLUDE        1      13  .D:\TMP\PROFIT.INC

The results of the DISPLAY statements can be used to verify the data was read correctly:

----     35 PARAMETER sales

                    1997        1998

hardware.nyc     110.000     120.000
hardware.was     120.000     130.000
hardware.la       80.000      70.000
hardware.sfo      80.000      90.000
software.nyc     100.000     120.000
software.was      70.000      80.000
software.la       60.000      70.000
software.sfo      50.000      70.000


----     67 PARAMETER profit

                    1997        1998

hardware.nyc       5.000       7.000
hardware.was       7.000      12.000
hardware.la        8.000       6.000
hardware.sfo       9.000      12.000
software.nyc      10.000      14.000
software.was      20.000      15.000
software.la       16.000      10.000
software.sfo      10.000      15.000

Note: To load the above table into one parameter we can use the following SQL query:

SELECT year,loc,prod,'sales',sales FROM data
UNION 
SELECT year,loc,prod,'profit',profit FROM data

which will generate an include file that can be read as follows:

parameter data(year,loc,prod,*) /
$include data.inc
/;

To be complete we show the generated include file:

* -----------------------------------------------------
* MDB2GMS Version 2.0, November 2003
* Erwin Kalvelagen, GAMS Development Corp
* -----------------------------------------------------
* DAO version: 3.6
* Jet version: 4.0
* Database:    E:\models\trnsportdata.mdb
* 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
1998.la.software.profit 10
1998.la.software.sales 70
1998.nyc.hardware.profit 7
1998.nyc.hardware.sales 120
1998.nyc.software.profit 14
1998.nyc.software.sales 120
1998.sfo.hardware.profit 12
1998.sfo.hardware.sales 90
1998.sfo.software.profit 15
1998.sfo.software.sales 70
1998.was.hardware.profit 12
1998.was.hardware.sales 130
1998.was.software.profit 15
1998.was.software.sales 80
* -----------------------------------------------------

If the '=' is absent first a shell (Unix) or command.com (Windows) is loaded, and subsequently this command processor will load the program. This extra step is only needed when executing for instance batch files, but is just a waste of resources otherwise. In our case it is even required to run directory as Window's programs running under a command processor are running detached: the command processor will terminate, giving control back to GAMS while the import filter is not finished yet. This will cause GAMS to read output files, which are not ready yet. If the program is run directly, GAMS will wait with reading the file until the import filter has properly terminated. Another problem when running a program via command.com is he limited command line length (128 characters). I.e. the '=' is an ugly but necessary detail.

Note: for illustrative purposes we also show you another way of doing this:

$call start /w d:\util\mdb2gms "i=c:\my documents\test.mdb" o=d:\tmp\test.in q="select * from mytable"

which uses the Windows start command to make sure mdb2gms finishes before GAMS proceeds.

Note 2: The original design by the author introduced a new dollar control option: $import which would combine the $call and the $include constructs. A prototype has been implemented that allowed the user to say:

$import mdb2gms "i=c:\my documents\test.mdb" q="select * from mytable"

instead of

$call =d:\utils\mdb2gms "i=c:\my documents\test.mdb" o=d:\tmp\x.inc q="select * from mytable"
$include d:\tmp\x.inc

Unfortunately this feature never made it into the production version of GAMS.

@filename

set i /
$call =d:\utils\mdb2gms @"C:\My Documents\options.txt"
$include "c:\my documents\data.inc"
/;

I=C:\my documents\test.mdb
Q=select * from mytable
O=C:\my documents\data.inc

$set cmdfile "c:\windows\temp\commands.txt"
$echo "I=E:\models\labordata.mdb" >  "%cmdfile%"
$echo "O=E:\models\labor.INC"     >> "%cmdfile%"
$echo "Q=select * from labor"     >> "%cmdfile%"
$call ="d:\win9x program files\gams21.2\mdb2gms.exe" @"%cmdfile%"

parameter p /
$include "E:\models\labor.INC"
/;
display p;

$set cmdfile "c:\windows\temp\commands.txt"
$onecho  >  "%cmdfile%"
I=E:\models\labordata.mdb
O=E:\models\labor.INC
Q=select * from labor
$offecho
$call ="d:\win9x program files\gams21.2\mdb2gms.exe" @"%cmdfile%"

parameter p /
$include "E:\models\labor.INC"
/;
display p;

Version 1.4 of MDB2GMS allows to write data to a GDX file instead of or in addition to a GAMS include file. GDX files are explained in in section section 1.29. The command line parameter is X=gdxfile.gdx.

The following command line options are available for mdb2gms: R=batchrowsize

Option	Description
I=inputfile	This option is required and specifies the name of the .MDB file containing the Access database. If the file contains blanks the name should be surrounded by double quotes. It is advised to use absolute paths, so Access has no confusion what file to open. On a network UNC names can be used, and files from another computer can be accessed, e.g. "\\hostname\c\my documents\a.mdb." This option is required.
O=outputfile	This option specifies the name of the output file. Make sure the directory is writeable. UNC names can be used. This is a required option.
Q=query	This option can be used to specify an SQL query. Queries contain spaces and thus have to be surrounded by double quotes. For the exact syntax of the queries that is accepted by Access we refer to the documentation that comes with MS Access. One notable syntax feature is that when field names or table names contain blanks, they can be specified in square brackets. Examples: Q="select * from mytable" Q="select year, production from [production table]" Q="select [GAMS City],value from [example table],CityMapper where [Access City]=city"
D	Debug. This option can be used for debugging purposes. If specified the import filter will no run minimized but "restored", i.e. as a normal window. In addition the program will not terminate until the user clicks the Close button. This allows you to monitor possible errors during execution of mdb2gms.
B	If this parameter is specified, strings that have blanks in them will be quoted. If the string is already quoted this step is not performed. If the name contains an embedded single quote, the surrounding quotes will be double quotes. If the name already contains a double quote, the surrounding quotes will be single quotes. If both single and double quotes are present in the string, then all double quotes are replaced by single quotes and the surrounding quotes will be double quotes. By default this option is turned off.
M	Run in modest or mute mode: no additional information, such as version number etc. is written to the listing file.
L	Embed the data in $offlisting, $onlisting. A quick way to reduce the size of the listing file.
@filename	Causes the program to read options from a 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 it were specified on the command line.
X=GDXfilename	Writes results to the specified GDX file. For more information on GDX files see section GDXViewer. This option is new in version 1.4.
Version 2.8 introduces a command line parameter to set how many rows are imported by the utility at the time. By default R=100. This strategy gives a significant performance improvement. E.g. a complex 625000 row query had the following result: R=n Output seconds 1 include file 276.69 1 gdx file 265.11 100 include file 48.75 100 gdx file 40.41

Notes

• The import filter is a true Windows program. It will cause some screen switching if the console application gams.exe (command line version) is run from a maximized DOS box. If you run the model from the IDE, by default only some icons will become visible in the taskbar while the import filter is doing its work.
• The filter uses OLE automation and uses Access to extract data from the .mdb file. This technique requires that MS Access is available on the local machine. Each time the filter is executed a new Access application is started. The Access application will terminate again as soon as the filter is finished. The filter is using OLE automation and late binding. This allows simple coding, but better is performance is possible using other techniques.
• The source of the program is available for adaptation. The filter is written in Delphi Professional version 4. It is compatible with Delphi 3, 4 and 5 all editions (Standard, Professional and Enterprise). See the downloads below.
• The $call option is not correctly parsed. The line: $call "c:\tmp\mdb2gms" I=c:\tmp\xxx.mdb O=c:\tmp\xxx.inc will cause a compilation error $210: unknown dollar control option. This is a bug in the GAMS parser. When preceeded by an '=', the line is parsed correctly.
• The error messages provided by Access/Jet are sometimes cryptic. E.g. when misspelling a field name in the SQL statement, the error returned reads: "Too few parameters. Expected 1". You may want to debug your SQL queries in a tool like Microsoft Query (see the screen shot below).
  
• In some configurations mdb2gms is very slow. Here is some emperical evidence with some timings:

	Laptop Dell Inspiron 7000, 366 MHz Celeron, 128 MB RAM, Windows 98, MS Office 97			Dell precision 210 Workstation, 2 times 450 Mhz PIII, 384 MB, Windows NT 4.0 workstation, MS Office 2000
Import program	mdb2gms	sql2gms	sql2gms	mdb2gms	sql2gms	sql2gms
Protocols	DAO/JET	ADO/OLEDB	ADO/ODBC	DAO/JET	ADO/OLEDB	ADO/ODBC
connect string or input file	I=c:\tmp\test.mdb	C="Provider= Microsoft.Jet.OLEDB.4.0; Data Source= c:\tmp\test.mdb"	C=MyDSN	I=c:\tmp\test.mdb	C="Provider= Microsoft.Jet.OLEDB.4.0; Data Source= c:\tmp\test.mdb"	C=MyDSN
Query	Q="select * from large"	Q="select * from large"	Q="select * from large"	Q="select * from large"	Q="select * from large"	Q="select * from large"
Versions reported	DAO version: 3.51 Jet version: 3.0	ADO version: 2.1	ADO version: 2.1	DAO version: 3.6 Jet version: 3.0	ADO version: 2.1	ADO version: 2.1
Records	1000	1000	1000	1000	1000	1000
Elapsed time (seconds)	64.2	0.3	0.5	1.7	0.3	0.3

See also 1.9 The SQL2GMS utility.

1.4.3 MDB2GMS advanced use

$ontext

  Example database access with MDB2GMS

  Multiple queries in one call

$offtext

$onecho > cmd.txt
I=%system.fp%sample.mdb

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 =mdb2gms @cmd.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;

$ontext

  Example database access with MDB2GMS

  Multiple queries in one call, store in GDX file

$offtext

$onecho > cmd.txt
I=%system.fp%sample.mdb
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
p4=sales

Q5=select prod,loc,year,profit from data
p5=profit
$offecho

$call =mdb2gms @cmd.txt

$call =gdxviewer sample.gdx

set y 'years';
set loc 'locations';
set prd 'products';
parameter sales(prd,loc,y);
parameter profit(prd,loc,y);

$gdxin 'sample.gdx'
$load y=year loc prd sales profit

display sales;
display profit;

1.4.4 Download MDB2GMS

Example applications:

• Zoltan Lelkes, Endre Rev, Tivadar Farkas, Zsolt Fonyo, Tibor Kovacs and Ian Jones, Multicommodity transportation and supply problem with stepwise constant cost function, Budapest Univ. of Technology and Economics, Department of Chemical Engineering, and SABMiller Europe.
• Jimmy Ernesto Avendano Castillo, Route Optimization for Hazardous Materials Transport, MSc. thesis, International Institute for Geo-information Science and Earth Observation, Enschede, The Netherlands, march 2004.
• Shreeharsha Hegde, Potential of SVG for a Carthographic Interface to a Route Optimization model for the Transport of Hazardous Material, MSc. thesis, International Institute for Geo-information Science and Earth Observation, Enschede, The Netherlands, march 2004.
• TREMOVE model, http://www.tremove.org.

1.4.5 SQL2GMS

ODBC

C=DRIVER={Microsoft Access Driver (*.mdb)};dbq=c:\path\file.mdb 

OLEDB

C=Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\path\file.mdb

1.4.6 Dates

Suppose we have an MS Access table with one single date column:

datefield
----------
3/12/2007 
3/13/2007 10:00:00 AM 
3/14/2007 8:30:00 PM

$call =mdb2gms I="%system.fp%sample.mdb" Q="select datefield,Cdbl(datefield) from datetable" O=x.inc

parameter p(*) /
$include x.inc
/
;
display p;

alias(*,i);
parameter q(*,*);
loop(i$p(i),
 q(i,'year') = gyear(p(i));
 q(i,'month') = gmonth(p(i));
 q(i,'day') = gday(p(i));
 q(i,'hour') = ghour(p(i));
 q(i,'minute') = gminute(p(i));
);
display q;

* -----------------------------------------------------
* MDB2GMS Version 2.8, January 2007 
* Erwin Kalvelagen, GAMS Development Corp 
* ----------------------------------------------------- 
* DAO version: 3.6 
* Jet version: 4.0 
* Database:    D:\mdb2gms\examples\sample.mdb 
* Query:       select datefield,Cdbl(datefield) from datetable 
* ----------------------------------------------------- 
'3/12/2007' 39153 
'3/13/2007 10:00:00 AM' 39154.4166666667 
'3/14/2007 8:30:00 PM' 39155.8541666667 
* -----------------------------------------------------

----     28 PARAMETER p  

3/12/2007             39153.000,    3/13/2007 10:00:00 AM 39154.417,    3/14/2007 8:30:00 PM  39155.854


----     39 PARAMETER q  

                            year       month         day        hour      minute

3/12/2007                2007.000       3.000      13.000
3/13/2007 10:00:00 AM    2007.000       3.000      14.000      10.000
3/14/2007 8:30:00 PM     2007.000       3.000      15.000      20.000      30.000

1.5 Importing from DB2

------------------------------------- Command Entered -------------------------------------
describe table db2admin.dist
 ;
-------------------------------------------------------------------------------------------

Column                         Type      Type
name                           schema    name               Length   Scale Nulls
------------------------------ --------- ------------------ -------- ----- -----
LOCA                           SYSIBM    VARCHAR                  10     0 No
LOCB                           SYSIBM    VARCHAR                  10     0 No
DISTANCE                       SYSIBM    DOUBLE                    8     0 Yes

  3 record(s) selected.



------------------------------------- Command Entered -------------------------------------
select * from dist ;
-------------------------------------------------------------------------------------------

LOCA       LOCB       DISTANCE
---------- ---------- ------------------------
seattle    new-york     +2.50000000000000E+000
seattle    chicago      +1.70000000000000E+000
seattle    topeka       +1.80000000000000E+000
san-diego  new-york     +2.50000000000000E+000
san-diego  chicago      +1.80000000000000E+000
san-diego  topeka       +1.40000000000000E+000

  6 record(s) selected.



------------------------------------- Command Entered -------------------------------------
export to c:\tmp\export.txt of del select * from dist ;
-------------------------------------------------------------------------------------------
SQL3104N  The Export utility is beginning to export data to file
"c:\tmp\export.txt".

SQL3105N  The Export utility has finished exporting "6" rows.


Number of rows exported: 6

"seattle","new-york",+2.50000000000000E+000
"seattle","chicago",+1.70000000000000E+000
"seattle","topeka",+1.80000000000000E+000
"san-diego","new-york",+2.50000000000000E+000
"san-diego","chicago",+1.80000000000000E+000
"san-diego","topeka",+1.40000000000000E+000

parameter d(i,j) 'distance in thousands of miles' /
$ondelim
$include export.txt
$offdelim
/;
display d;

1.6 Importing from MySQL

mysql> select * from dist;
+-----------+----------+----------+
| loca      | locb     | distance |
+-----------+----------+----------+
| seattle   | new-york |       50 |
| seattle   | chicago  |      300 |
| seattle   | topeka   |        0 |
| san-diego | new-york |      275 |
| san-diego | chicago  |        0 |
| san-diego | topeka   |      275 |
+-----------+----------+----------+
6 rows in set (0.01 sec)

mysql> select * from dist into outfile '/tmp/data.csv' 
    -> fields terminated by ','
    -> optionally enclosed by '"'
    -> lines terminated by '\n';
Query OK, 6 rows affected (0.00 sec)

"seattle","new-york",50
"seattle","chicago",300
"seattle","topeka",0
"san-diego","new-york",275
"san-diego","chicago",0
"san-diego","topeka",275

[erwin@localhost erwin]$ cat myscript
use test
select * from dist into outfile '/tmp/data.csv' 
     fields terminated by ','
     optionally enclosed by '"'
     lines terminated by '\n';
[erwin@localhost erwin]$ cat x.gms

set i /seattle, san-diego/;
set j /new-york, chicago, topeka/;

$call 'mysql -u root < myscript'
parameter dist(i,j) /
$ondelim
$include /tmp/data.csv
$offdelim
/;

display dist; 

[erwin@localhost erwin]$ gams x
GAMS Rev 132  Copyright (C) 1987-2002 GAMS Development. All rights reserved
Licensee: GAMS Development Corporation, Washington, DC   G871201:0000XX-XXX
          Free Demo,  202-342-0180,  sales@gams.com,  www.gams.com   DC9999
--- Starting compilation
--- x.gms(5) 1 Mb
--- call mysql -u root < myscript
--- .data.csv(6) 1 Mb
--- x.gms(15) 1 Mb
--- Starting execution
--- x.gms(18) 1 Mb
*** Status: Normal completion
[erwin@localhost erwin]$

   1
   2  set i /seattle, san-diego/;
   3  set j /new-york, chicago, topeka/;
   4
   6  parameter dist(i,j) /
INCLUDE    /tmp/data.csv
   9  "seattle","new-york",50
  10  "seattle","chicago",300
  11  "seattle","topeka",0
  12  "san-diego","new-york",275
  13  "san-diego","chicago",0
  14  "san-diego","topeka",275
  16  /;
  17
  18  display dist;
  19
  20
  21

   SEQ   GLOBAL TYPE      PARENT   LOCAL  FILENAME

     1        1 INPUT          0       0  /home/erwin/x.gms
     2        5 CALL           1       5  mysql -u root < myscript
     3        8 INCLUDE        1       8  ./tmp/data.csv



----     18 PARAMETER dist

             new-york     chicago      topeka

seattle        50.000     300.000
san-diego     275.000                 275.000

file script /myscript/;
putclose script
   "use test"/
   "select * from dist into outfile '/tmp/data.csv' "/
   "   fields terminated by ','"/
   "   optionally enclosed by '",'"',"'"/
   "   lines terminated by '\n';"/
;

$onecho > myscript
use test
select * from dist into outfile '/tmp/data.csv' 
     fields terminated by ','
     optionally enclosed by '"'
     lines terminated by '\n';
$offecho

1.7 Importing from SQL Server

$set commandfile commands.txt
$onecho > %commandfile%
C=provider=sqloledb;data source=athlon\SQLExpress;Initial catalog=test;user id=sa;password=password
O=C:\WINNT\gamsdir\xx.inc
Q=SELECT * FROM x
$offecho
$call =sql2gms @%commandfile%
parameter p(i,j) /
$include "C:\WINNT\gamsdir\xx.inc"
/;
display p;

To export SQL Server data to CSV files we can use the BCP utility.

C:\Program Files\Microsoft SQL Server\90\Tools\binn>bcp test..results out x.csv -S athlon\sqlexpress -c -U sa -P password -t,

Starting copy...

6 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 10     Average : (600.00 rows per sec.)

C:\Program Files\Microsoft SQL Server\90\Tools\binn>type x.csv
seattle,new-york,50.0
seattle,chicago,300.0
seattle,topeka,0.0
san-diego,new-york,275.0
san-diego,chicago,0.0
san-diego,topeka,275.0

Other tools to export files include DTS (Data Transformation Services) and linked ODBC data sources.

Finally we can program directly an interface between SQL server tables and GAMS GDX files. A small example in C# can look like:

gdxio = new csharpclient();
//
// read a set
//
gdxio.gdxdatawritestrstart(ap, "location", "from db", 1, csharpclient.dt_set, 0);
String q = "select distinct(location) from exporttable";
SqlCommand cmd = new SqlCommand(q, conn);
SqlDataReader myReader = cmd.ExecuteReader();
String[] astrelements = new String[10];
for (int i = 0; i < 10; ++i)
    astrelements[i] = "";
double[] avals = new double[5];
while (myReader.Read())
{
    astrelements[0] = myReader.GetString(0);
    avals[0] = 0.0;
    Boolean ok = gdxio.gdxdatawritestr(ap,astrelements,avals);
}
gdxio.gdxdatawritedone(ap);
myReader.Close();

//
// read a data table
//
gdxio.gdxdatawritestrstart(ap, "data", "from db", 2, csharpclient.dt_par, 0);
q = "select location, capacity, cost exporttable";
cmd = new SqlCommand(q, conn);
myReader = cmd.ExecuteReader();
while (myReader.Read())
{
    astrelements[0] = myReader.GetString(0);
    astrelements[1] = "capacity";
    avals[0] = myReader.GetInt32(1);
    Boolean ok = gdxio.gdxdatawritestr(ap, astrelements, avals);

    if (!myReader.IsDBNull(2)) {
        astrelements[1] = "cost";
        avals[0] = myReader.GetDouble(2);
        ok = gdxio.gdxdatawritestr(ap, astrelements, avals);
    }
}
gdxio.gdxdatawritedone(ap);
myReader.Close();
gdxio.gdxclose(ref ap);

1.8 Importing from Sybase

An example of use of this utility is shown below:

[erwin@fedora sybase]$ isql -U sa -S LOCALHOST -D testdb -P sybase -J iso_1
1> select * from results
2> go
 loca       locb       shipment
 ---------- ---------- --------------------
 seattle    new-york              50.000000
 seattle    chicago              300.000000
 seattle    topeka                 0.000000
 san-diego  new-york             275.000000
 san-diego  chicago                0.000000
 san-diego  topeka               275.000000
 
(6 rows affected)
1> quit
[erwin@fedora sybase]$ cat bcp.fmt
10.0
4
1       SYBCHAR 0       0       "\""    1       loca
2       SYBCHAR 0       10      "\",\"" 1       loca
3       SYBCHAR 0       10      "\","   2       locb
4       SYBCHAR 0       17      "\n"    3       shipment
[erwin@fedora sybase]$ bcp testdb..results out res.txt -S LOCALHOST  -U sa -P sybase -J iso_1 -f bcp.fmt
 
Starting copy...
 
6 rows copied.
Clock Time (ms.): total = 1  Avg = 0 (6000.00 rows per sec.)
[erwin@fedora sybase]$ cat res.txt
"seattle","new-york",50.0
"seattle","chicago",300.0
"seattle","topeka",0.0
"san-diego","new-york",275.0
"san-diego","chicago",0.0
"san-diego","topeka",275.0
[erwin@fedora sybase]$

This can be automated using the following GAMS code:

sets
  i   'canning plants'   / seattle, san-diego /
  j   'markets'          / new-york, chicago, topeka / ;


$onecho > bcp.fmt
10.0
4
1       SYBCHAR 0       0       "\""    1       loca
2       SYBCHAR 0       10      "\",\"" 1       loca
3       SYBCHAR 0       10      "\","   2       locb
4       SYBCHAR 0       17      "\n"    3       shipment
$offecho
$call "bcp testdb..results out res.txt -S LOCALHOST  -U sa -P sybase -J iso_1 -f bcp.fmt"
parameter d(i,j)  'distance in thousands of miles'
/
$ondelim
$include res.txt
$offdelim
/;
display d;

[erwin@fedora sybase]$ gams trnsport_sybase
--- Starting compilation
--- trnsport_sybase.gms(14) 3 Mb
--- call bcp testdb..results out res.txt -S LOCALHOST  -U sa -P sybase -J iso_1 -f bcp.fmt
 
Starting copy...
 
6 rows copied.
Clock Time (ms.): total = 1  Avg = 0 (6000.00 rows per sec.)
--- .res.txt(6) 3 Mb
--- trnsport_sybase.gms(22) 3 Mb
--- Starting execution
--- trnsport_sybase.gms(27) 4 Mb
*** Status: Normal completion
[erwin@fedora sybase]$

1.9 The SQL2GMS utility

ADO is "Microsoft's strategic, high-level interface to all kinds of data" (http://www.microsoft.com/data/ado/default.htm). ADO can be downloaded as part of MDAC (Microsoft Data Access Components) at http://www.microsoft.com/data/download.htm.

When running SQL2GMS utility without command line parameters, the following window will appear:

The output file is specified in the first edit line. This is the file that will contain the results and can be included in your GAMS model. The Browse button allows you to navigate around on your hard disks.

The connection string specifies the database to connect to and the protocol to use. The following table gives some examples:

database	connection string
ODBC
ODBC data source	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)}; Dbq=d:\ppp\fff.mdb;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;
OLEDB
OLE DB Data link file	File name=d:\ppp\fff.udl;
OLE DB Provider for ODBC Access (Jet)	Provider =MSDASQL; Driver={Microsoft Access Driver (*.mdb)};Dbq= d:\ppp\fff.mdb; 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.Jet.OLEDB.4.0; Data Source=d:\ppp\fff.mdb; User Id=yyy; Password=zzz;
OLE DB Provider for Microsoft Jet (Access) with System Database	Provider=Microsoft.Jet.OLEDB.4.0; Data Source=d:\ppp\fff.mdb; Jet OLEDB:System Database=fff.mdw; Specify user id and password in the U=xxx and P=zzz options. If MDB 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
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.Jet.OLEDB.4.0; Data Source=d:\ppp\fff.mdb; 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.Jet.OLEDB.4.0;Data Source=xxx.mdb; 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 your local database guru and see http://www.able-consulting.com/ADO_Conn.htm, and http://www.able-consulting.com/ADO_Faq.htm.

ODBC can be used to access a number of databases such as Oracle, SQL Server, MS Data Engine, etc. Consult your database documentation on information how to connect to your database. In some cases, database client software needs to be installed in addition to the ODBC driver. E.g. the Oracle8 ODBC driver uses OCI (Oracle Call Interface) and Net8 software. Therefore in case of Oracle, make sure the underlying protocols (SQL*Net) work properly before debugging the ODBC connection. The ODBC Source Administrator allows logging ODBC traffic to a file. This can be used to debug the connection.

There are a few special options:

• Quote blanks can be used to quote strings that contains blanks. If the string is already quoted this step is not performed. If the name contains an embedded single quote, the surrounding quotes will be double quotes. If the name already contains a double quote, the surrounding quotes will be single quotes. If both single and double quotes are present in the string, then all double quotes are replaced by single quotes and the surrounding quotes will be double quotes. The following examples will illustrate the behavior:

  input	output	remarks
  hello	'hello'
  "hello"	"hello"	not 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'	not touched, will generate an error in GAMS
  art"ificial	'art"ificial'
  art"ifi'cial	"art'ifi'cial"

  By default this option is turned off.
• mute will reduce the amount of information written to the output file. The version number etc. is suppressed.
• No listing will prevent the data to be echoed to the listing file. For large data sets this can reduce the size of the listing file.
• User name can be used to specify the Database login name. If this is already specified in the connection string, you may leave this blank.
• Password can be used to specify the Database password. If this is already specified in the connection string, you may leave this blank.

The utility can also be used in batch mode. I.e. if you specify from the command line:

sql2gms C="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\My Documents\test.mdb;" O=test.inc Q="select * from data"

The example used in paragraph 1.4.2 is trivial to adapt for SQL2GMS:

set y 'years' /1997*1998/;
set loc 'locations' /nyc,was,la,sfo/;
set prd 'products' /hardware, software/;

parameter sales(prd,loc,y) /
$call =sql2gms C="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\My Documents\test.mdb" Q="select prod,loc,year,sales from data" O=sales.inc
$include sales.inc
/;
display sales;

parameter profit(prd,loc,y) /
$call =sql2gms C="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\My Documents\test.mdb" Q="select prod,loc,year,profit from data" O=profit.inc
$include profit.inc
/;
display profit;

$call =sql2gms c="DRIVER={SQL Server};SERVER=VANBUREN;UID=xxx;PWD=yyy;DATABASE=adp1SQL" q="select * from table1" B O=x.inc
$include x.inc

GAMS Rev 116  Copyright (C) 1987-2000 GAMS Development. All rights reserved
Licensee: harrison                                       G990810:0928CE-WIN
          GAMS Development Corporation                               DC1556
--- Starting compilation
--- .X.INC(6) 1 Mb 1 Error
*** Error 333 in D:\TMP\X.INC
    Error triggered by message above
--- .X.INC(6) 1 Mb
--- X.GMS(5) 1 Mb 1 Error
*** Status: Compilation error(s)
Exit code = 2

* -----------------------------------------------------
* SQL2GMS Version 1.2, September 2000
* Erwin Kalvelagen, GAMS Development Corp
* -----------------------------------------------------
* ADO version: 2.1
$error [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'xxx'

   1
INCLUDE    D:\TMP\X.INC
   4  * -----------------------------------------------------
   5  * SQL2GMS Version 1.2, September 2000
   6  * Erwin Kalvelagen, GAMS Development Corp
   7  * -----------------------------------------------------
   8  * ADO version: 2.1
   9  $error [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'xxx'
****       $333
**** LINE      6 IN FILE D:\TMP\X.INC
  10
  11

If the '=' is absent first a shell (Unix) or command.com (Windows) is loaded, and subsequently this command processor will load the program. This extra step is only needed when executing for instance batch files, but is just a waste of resources otherwise. In our case it is even required to run directory as Window's programs running under a command processor are running detached: the command processor will terminate, giving control back to GAMS while the import filter is not finished yet. This will cause GAMS to read output files, which are not ready yet. If the program is run directly, GAMS will wait with reading the file until the import filter has properly terminated. Another problem when running a program via command.com is he limited command line length (128 characters). I.e. the '=' is an ugly but necessary detail.

Note: for illustrative purposes we also show you another way of doing this:

$call start /w d:\util\sql2gms c="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\import\test\test.mdb" o=d:\tmp\test.in q="select * from mytable"

which uses the Windows start command to make sure sql2gms finishes before GAMS proceeds.

Note 2: The original design by the author introduced a new dollar control option: $import which would combine the $call and the $include constructs. A prototype has been implemented that allowed the user to say:

$import sql2gms c="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\import\test\test.mdb" q="select * from mytable"

instead of

$call =d:\utils\sql2gms c="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\import\test\test.mdb" o=d:\tmp\x.inc q="select * from mytable"
$include d:\tmp\x.inc

Unfortunately this feature never made it into the production version of GAMS.

@filename

set i /
$call =sql2gms @"C:\My Documents\options.txt"
$include "c:\my documents\data.inc"
/;

C=Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\import\test\test.mdb
Q=select * from mytable
O=C:\my documents\data.inc

$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 ="d:\win9x program files\gams21.2\sql2gms.exe" @"%cmdfile%"

parameter p /
$include "E:\models\labor.INC"
/;
display p;

$set cmdfile "c:\windows\temp\commands.txt"
$onecho  >  "%cmdfile%"
C=DSN=sample 
O=E:\models\labor.INC
Q=select * from labor
$offecho
$call ="d:\win9x program files\gams21.2\sql2gms.exe" @"%cmdfile%"

parameter p /
$include "E:\models\labor.INC"
/;
display p;

Version 1.3 of SQL2GMS allows to write data to a GDX file instead of or in addition to a GAMS include file. GDX files are explained in in section section 1.29. The command line parameter is X=gdxfile.gdx.

The following command line options are available for sql2gms:

Option	Description
C=connectionstring	This option is required and specifies to which database ADO should connect to. The following are some examples of connection strings: C="MyDSN" C="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\My Documents\test.mdb;" C="DRIVER={SQL Server};SERVER=VANBUREN;DATABASE=adp1SQL" C="driver={SQL Server}; server=VANBUREN; database=erwindb; UID=erwin; PWD=mypassword; " C="driver={Oracle ODBC Driver};database=erwindb;UID=system;PWD=manager;"
O=outputfile	This option specifies the name of the output file. Make sure the directory is writeable. UNC names can be used. This is a required option.
Q=query	This option can be used to specify an SQL query. Queries contain spaces and thus have to be surrounded by double quotes. For the exact syntax of the queries that is accepted by Access we refer to the documentation that comes with MS Access. One notable syntax feature is that when field names or table names contain blanks, they can be specified in square brackets. Examples: Q="select * from mytable" Q="select year, production from [production table]" Q="select [GAMS City],value from [example table],CityMapper where [Access City]=city" For an example of calling a stored procedure see section 1.9.5 SQL2GMS timeouts
U=username	If not specified in the connection string this can be used to provide a database user name. Example: U="system" .
P=password	If not specified in the connection string this can be used to provide a database password. Example: P="manager" .
D	Debug. This option can be used for debugging purposes. If specified the import filter will no run minimized but "restored", i.e. as a normal window. In addition the program will not terminate until the user clicks the Close button. This allows you to monitor possible errors during execution of mdb2gms.
B	If this parameter is specified, strings that have blanks in them will be quoted. If the string is already quoted this step is not performed. If the name contains an embedded single quote, the surrounding quotes will be double quotes. If the name already contains a double quote, the surrounding quotes will be single quotes. If both single and double quotes are present in the string, then all double quotes are replaced by single quotes and the surrounding quotes will be double quotes. By default this option is turned off.
M	Run in modest or mute mode: no additional information, such as version number etc. is written to the listing file.
L	Embed the data in $offlisting, $onlisting. A quick way to reduce the size of the listing file.
@filename	Causes the program to read options from a 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 it were specified on the command line.
X=GDXfilename	Writes results to the specified GDX file. For more information on GDX files see section GDXViewer. This option is new in version 1.3.

Notes

• The import filter is a true Windows program. It will cause some screen switching if the console application gams.exe (command line version) is run from a maximized DOS box. If you run the model from the IDE, by default only some icons will become visible in the taskbar while the import filter is doing its work.
• The source of the program is available for adaptation. The filter is written in Delphi Professional version 4. It is compatible with Delphi 3, 4 and 5 all editions (Standard, Professional and Enterprise). See the downloads below.
• The $call option is not correctly parsed. The line: $call "c:\tmp\sql2gms" @c:\tmp\commands.txt will cause a compilation error $2: identifier expected. This is a bug in the GAMS parser. When preceeded by an '=', the line is parsed correctly.
• It is possible to access Excel data through ODBC. As an example consider the spreadsheet:


Notice that the range B1:C5 has been given a name: myrange. Using ODBC the first row has special meaning: it contains the field names. To extract this table we could create a DSN (Data Source Name) using the ODBC Data Source Administrator that points to this .xls file. Suppose this DSN is called MyExcel. Now the following GAMS fragment will import this table using ADO and ODBC:

set i /
$call =sql2gms C=MyExcel Q="select city from `myrange`" B O=d:\tmp\seti.inc
$include seti.inc
/;
parameter data(i) /
$call =sql2gms C=MyExcel Q="select * from `myrange`" B O=d:\tmp\data.inc
$include data.inc
/;
display data;

Notice that the range name is in "back-quotes", and that the B option is used to preserve the blanks in the label names.
• Microsoft provides a Text Driver in order to be able to access text files. The driver supports the following formats: Fixed Length, CSV Delimited, Tab Delimited, and Custom Delimited. As an example we consider the data file:

  
  3 i1 j1
  4 i1 j2
  5 i2 j1
  6 i3 j1
  4 i3 j2
  

  This is a file with Fixed Length fields. Data like this cannot be imported directly into GAMS. However using the ODBC Text Driver we can convert this easily into something that can be digested by GAMS. Configuring a data source using the Text Driver with columns value, i and j as:

  Name	Type	Width
  Value	Integer	2
  i	Char	3
  j	Char	2

  We can use the following GAMS statements to read this file:

  
  set i /i1*i10/;
  set j /j1*j10/;
  parameter data(i,j) /
  $call =sql2gms C="TextDSN" Q="select i,j,value from test.txt" O=d:\tmp\test.inc
  $include d:\tmp\test.inc
  /;
  

• Sometimes the following construct is useful: only import from the database if the local data files do not exist:

  
  $if not exist i.txt $call =sql2gms.exe c="DSN=ora" q="select distinct i from test" o=i.txt
  $if not exist j.txt $call =sql2gms.exe c="DSN=ora" q="select distinct j from test" o=j.txt
  $if not exist value.txt $call =sql2gms.exe c="DSN=ora" q="select i,j,value from test" o=value.txt
  
  set i /
  $include i.txt
  /;
  
  set j /
  $include j.txt
  /;
  
  parameter value(i,j) /
  $include value.txt
  /;
  

  To trigger new data to be retrieved from the database, just delete the text files i.txt, j.txt and value.txt.

1.9.1 Reading DBASE Files

Q=select columns from dbf_file_name
C=DRIVER=Microsoft dBase Driver (*.dbf);dbq=directory_of_dbf_file_name

$ontext

   Read DBASE table:

    Table 2. Population by sex, residence, and intercensal rates of increase
             for total population, each census: 1948-1997

   Source:
      United Nations - Demographic Yearbook, Historical supplement. New York, 1999

$offtext

set country / 
102005 "Algeria"
103005 "Angola"
106305 "Benin"
107405 "Botswana"
109605 "Burkina Faso"
110405 "Burundi"
110805 "Cameroon"
112005 "Cape Verde"
112405 "Central African Republic"
113005 "Chad"
115405 "Comoros"
116105 "Congo"
116905 "Côte d'Ivoire"
118005 "Democratic Republic of the Congo"
118205 "Djibouti"
119205 "Egypt"
120405 "Equatorial Guinea"
120505 "Eritrea"
120805 "Ethiopia"
121005 "Former Ethiopia"
126005 "Gabon"
126405 "Gambia"
127805 "Ghana"
130405 "Guinea"
130605 "Guinea-Bissau"
138805 "Kenya"
142405 "Lesotho"
143405 "Liberia"
143805 "Libyan Arab Jamahiriya"
145405 "Madagascar"
145605 "Malawi"
147805 "Mali"
148805 "Mauritania"
149005 "Mauritius"
149015 "Mauritius, Island of Mauritius"
149025 "Mauritius, Rodrigues"
149035 "Mauritius, Others"
151005 "Morocco"
151405 "Mozambique"
152005 "Namibia"
156205 "Niger"
156605 "Nigeria"
164405 "Réunion"
165205 "Rwanda"
165715 "Saint Helena ex. dep."
165725 "Saint Helena: Ascension"
165735 "Saint Helena: Tristan da Cunha"
166805 "Sao Tome and Principe"
167605 "Senegal"
168005 "Seychelles"
168805 "Sierra Leone"
169605 "Somalia"
170005 "South Africa"
173105 "Sudan"
174005 "Swaziland"
176405 "Togo"
178405 "Tunisia"
179405 "Uganda"
185505 "United Republic of Tanzania"
185515 "United Republic of Tanzania: Tanganyika"
185525 "United Republic of Tanzania: Zanzibar"
190205 "Western Sahara"
192805 "Zambia"
193805 "Zimbabwe"
202905 "Anguilla"
203105 "Antigua and Barbuda"
203705 "Aruba"
204805 "Bahamas"
205405 "Barbados"
206105 "Belize"
206205 "Bermuda"
208805 "British Virgin Islands"
211605 "Canada"
212205 "Cayman Islands"
216805 "Costa Rica"
217005 "Cuba"
218505 "Dominica"
218805 "Dominican Republic"
220005 "El Salvador"
229005 "Greenland"
229105 "Grenada"
229405 "Guadeloupe"
230005 "Guatemala"
231205 "Haiti"
232005 "Honduras"
237405 "Jamaica"
248005 "Martinique"
249605 "Mexico"
250705 "Montserrat"
253305 "Netherlands Antilles"
255805 "Nicaragua"
259405 "Panama"
259605 "Panama: Former Canal Zone"
263905 "Puerto Rico"
266205 "Saint Kitts-Nevis"
266305 "Saint Lucia"
266405 "Saint Pierre and Miquelon"
266505 "Saint Vincent and the Grenadines"
277405 "Trinidad and Tobago"
279105 "Turks and Caicos Islands"
286005 "United States"
286405 "United States Virgin Islands"
303405 "Argentina"
306805 "Bolivia"
307805 "Brazil"
313405 "Chile"
315005 "Colombia"
319405 "Ecuador"
322405 "Falkland Islands (Malvinas)"
324005 "French Guiana"
330805 "Guyana"
360405 "Paraguay"
360805 "Peru"
373405 "Suriname"
387405 "Uruguay"
387805 "Venezuela"
401405 "Afghanistan"
403605 "Armenia"
404605 "Azerbaijan"
405005 "Bahrain"
405205 "Bangladesh"
406405 "Bhutan"
409205 "Brunei Darussalam"
410705 "Cambodia"
413805 "China"
413905 "China: Hong Kong SAR"
417405 "Cyprus"
419005 "East Timor"
426305 "Georgia"
434005 "India"
434405 "Indonesia"
435005 "Iran (Islamic Republic of)"
435405 "Iraq"
436205 "Israel"
437805 "Japan"
438405 "Jordan"
438505 "Kazakhstan"
439005 "Korea (Dem. People's Republic of)"
439405 "Korea (Republic of)"
439805 "Kuwait"
440005 "Kyrgyzstan"
440205 "Lao People's Democratic Republic"
441205 "Lebanon"
445005 "Macau"
445805 "Malaysia"
445825 "Malaysia: Peninsular Malaysia"
445835 "Malaysia: Sabah"
445845 "Malaysia: Sarawak"
447405 "Maldives"
450605 "Mongolia"
451605 "Myanmar"
452605 "Nepal"
458605 "Oman"
459005 "Pakistan"
461205 "Philippines"
464005 "Qatar"
467205 "Saudi Arabia"
469405 "Singapore"
472905 "Sri Lanka"
475205 "Syrian Arab Republic"
475605 "Tajikistan"
476005 "Thailand"
478805 "Turkey"
478905 "Turkmenistan"
483005 "United Arab Emirates"
487305 "Uzbekistan"
487905 "Viet Nam"
488105 "West Bank and Gaza Strip"
491905 "Yemen"
492005 "Yemen: Former Democratic Yemen"
492105 "Yemen: Former Yemen Arab Republic"
501805 "Albania"
502805 "Andorra"
504405 "Austria"
505805 "Belarus"
506005 "Belgium"
507105 "Bosnia and Herzegovina"
509405 "Bulgaria"
513205 "Channel Islands"
513215 "Channel Islands: Guernsey"
513225 "Jersey"
517105 "Croatia"
517805 "Former Czechoslovakia"
517905 "Czech Republic"
518405 "Denmark"
520705 "Estonia"
522005 "Faeroe Islands"
523405 "Finland"
523805 "France"
526805 "Germany: Federal Republic of Germany"
526905 "Germany: Former German Democratic Republic"
528005 "Gibraltar"
528805 "Greece"
531605 "Holy See"
532805 "Hungary"
533205 "Iceland"
535805 "Ireland"
536005 "Isle of Man"
536605 "Italy"
540705 "Latvia"
544205 "Liechtenstein"
544405 "Lithuania"
544605 "Luxembourg"
547905 "Malta"
550205 "Monaco"
553005 "Netherlands"
558405 "Norway"
562005 "Poland"
562405 "Portugal"
564105 "Republic of Moldova"
564805 "Romania"
565005 "Russian Federation"
566605 "San Marino"
569305 "Slovakia"
569505 "Slovenia"
571605 "Spain"
573805 "Svalbard and Jan Mayen Islands"
574405 "Sweden"
574805 "Switzerland"
576205 "The Former Yugoslav Rep. of Macedonia"
579805 "Ukraine"
584005 "United Kingdom"
584015 "United Kingdom: England and Wales"
584025 "United Kingdom: Northern Ireland"
584035 "United Kingdom: Scotland"
592405 "Former Yugoslavia"
592505 "Yugoslavia"
602405 "American Samoa"
604205 "Australia"
614405 "Christmas Islands"
614805 "Cocos (Keeling) Islands"
616405 "Cook Islands"
623005 "Fiji"
624405 "French Polynesia"
629805 "Guam"
638705 "Kiribati"
648105 "Marshall Islands"
649805 "Micronesia"
652205 "Nauru"
654205 "New Caledonia"
655405 "New Zealand"
658005 "Niue"
658205 "Norfolk Island"
658305 "Northern Mariana Islands"
659105 "Palau"
660205 "Papua New Guinea"
661605 "Pitcairn"
667005 "Samoa"
670205 "Solomon Islands"
676805 "Tokelau"
677005 "Tonga"
679005 "Tuvalu"
687505 "Vanuatu"
690005 "Wallis and Futuna Islands"
780005 "Former USSR" 
/;

set reliability / 
 0 "Final figure, complete"
 1 "Provisional figure"
 2 "Final figure, incomplete/questionable reliability"
 3 "Provisional figure with questionable reliability"
 4 "No information available on reliability"
 5 "United Nations Population Division estimates"
 6 "Other estimates" 
/;

set vardef 'this is used to define the data record' / 
 1 "Enumerated population: Total"
 2 "Enumerated population: Male"
 3 "Enumerated population: Female"
 4 "Urban population: Number"
 5 "Urban population: Per cent"
 6 "Rural population: Number"
 7 "Rural population: Per cent"
 8 "Annual rate of increase (%)" 
/;

set year /1948*1998/;
set day /0,1*31/;
set month /0,1*12/;

set rectype / 
 1 "Census, complete tabulation: de facto"
 2 "Census, sample tabulation: de facto"
 3 "Census, complete tabulation: de jure"
 4 "Census, sample tabulation: de jure"
 5 "Sample survey: de facto"
 6 "Sample survey: de jure"
 7 "Estimate: de facto"
 8 "Estimate: de jure" 
/;

$onecho > table02.cmd
q=select country,year,month,day,variable,rectype,datum from table02
c=DRIVER=Microsoft dBase Driver (*.dbf);dbq=%system.fp%
o=table02.inc
$offecho
$call =sql2gms @table02.cmd

parameter population(country,year,month,day,vardef,rectype) /
$include table02.inc
/;
display population;

GAMS Rev 142  Copyright (C) 1987-2005 GAMS Development. All rights reserved
Licensee: Erwin Kalvelagen                               G031217:2011CE-WIN
          GAMS Development Corporation                               DC1556
--- Starting compilation
--- dbase.gms(312) 3 Mb
--- call =sql2gms @table02.cmd
ADO version: 2.8
Query: select country,year,month,day,variable,rectype,datum from table02
Number of rows: 5980
Elapsed time: 0.23 seconds
Done
--- .table02.inc(5990) 3 Mb
--- dbase.gms(317) 3 Mb
--- Starting execution
--- dbase.gms(6307) 4 Mb
*** Status: Normal completion

1.9.1 SQL2GMS advanced use

Writing to the GDX file is also supported through the command line using the X=gdxfilename command line parameter.

As there is significant overhead in connecting to the database, a facility has been added to execute multiple queries in one call. For example:

$ontext

  Example database access with SQL2GMS (ODBC Driver)

  Multiple queries in one call

$offtext

$onecho > cmd.txt
C=DRIVER=Microsoft Access Driver (*.mdb);dbq=%system.fp%sample.mdb

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 @cmd.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;

$ontext

  Example database access with SQL2GMS (OLEDB Driver)

  Multiple queries in one call, store in GDX file

$offtext

$onecho > cmd.txt
C=Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%system.fp%sample.mdb
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 @cmd.txt

$call =shellExecute gdxviewer sample.gdx

set y 'years';
set loc 'locations';
set prd 'products';
parameter sales(prd,loc,y);
parameter profit(prd,loc,y);

$gdxin 'sample.gdx'
$load y=year loc prd sales profit

display sales;
display profit;

GAMS Rev 142  Copyright (C) 1987-2005 GAMS Development. All rights reserved
Licensee: Erwin Kalvelagen                               G031217:2011CE-WIN
          GAMS Development Corporation                               DC1556
--- Starting compilation
--- salesprofitdb10.gms(29) 2 Mb
--- call =sql2gms @cmd.txt
GDXIO.DLL version:_GAMS_GDX_233_2005-03-03
ADO version: 2.8
Query: select distinct(year) from data
GDX id: year (Set)
Number of rows: 2
Elapsed time: 0.02 seconds
Done
Query: select distinct(loc) from data
GDX id: loc (Set)
Number of rows: 4
Elapsed time: 0.00 seconds
Done
Query: select distinct(prod) from data
GDX id: prd (Set)
Number of rows: 2
Elapsed time: 0.00 seconds
Done
Query: select prod,loc,year,sales from data
GDX id: sales (Parameter)
Number of rows: 16
Elapsed time: 0.02 seconds
Done
Query: select prod,loc,year,profit from data
GDX id: profit (Parameter)
Number of rows: 16
Elapsed time: 0.00 seconds
Done
--- salesprofitdb10.gms(31) 2 Mb
--- call =shellExecute gdxviewer sample.gdx
ShellExecute Version 1.0
--- salesprofitdb10.gms(39) 2 Mb
--- GDXin=C:\gams projects\wtools\ver002\examples\sample.gdx
--- salesprofitdb10.gms(44) 3 Mb
--- Starting execution
--- salesprofitdb10.gms(44) 4 Mb
*** Status: Normal completion

1.9.3 Download SQL2GMS

1.9.4. SQL2GMS script

$ontext

   This script mimics SQL2GMS.

   Erwin Kalvelagen
   November 2006

$offtext

$onecho > sql2gms.vbs
'
' parameters
'
t1 = 3                    ' connection timeout
t2 = 0                    ' command timeout
c = "Provider=MSDASQL;Driver={SQL Server};Server=DUOLAP\SQLEXPRESS;Database=testdata;Uid=gams;Pwd=gams;"  ' connection string
q = "select * from data"  ' query
o = "output.inc"          ' the output file to be generated
b = false                 ' whether to quote indices (e.g. because of embedded blanks)

'
' create ADO connection object
'
set ADOConnection = CreateObject("ADODB.Connection")
ADOVersion = ADOConnection.Version
WScript.Echo "ADO Version:",ADOVersion

'
' make db connection
'
ADOConnection.ConnectionTimeout = t1
ADOConnection.ConnectionString = c
ADOConnection.Open

'
' Open file
'
set fso = CreateObject("Scripting.FileSystemObject")
set outputfile = fso.CreateTextFile(o,True)
outputfile.writeLine "*----------------------------------------------------"
outputfile.writeLine "*  SQL2GMS/Vbscript 1.0"
outputfile.writeLine "*  Connection:"&c
outputfile.writeLine "*  Query:"&q
outputfile.writeLine "*----------------------------------------------------"

'
' setup query
'
starttime = time
ADOConnection.CommandTimeout = t2
const adCmdText = 1
set RecordSet = ADOConnection.Execute(q,,adCmdText)

'
' get results
'
NumberOfFields = RecordSet.Fields.Count
eof = RecordSet.EOF
if eof then
   WScript.Echo "No records"
   Wscript.quit
end if

'
'  loop through records
'
NumberOfRows = 0
do until eof
   NumberOfRows = NumberOfRows + 1
   Row = RecordSet.GetRows(1)
   if NumberOfFields > 1 then
      s = Row(0,0)
      if b then
        s = quotestring(s)
      end if
      Outputfile.Write s
   end if

   for i=2 to NumberOfFields-1
       s = Row(i-1,0)
       if b then
         s = quotestring(s)
       end if
       Outputfile.Write "."
       Outputfile.Write s
   next

   s = Row(NumberOfFields-1,0)
   OutputFile.Write " "
   OutputFile.Writeline s

   eof = RecordSet.EOF
loop

OutputFile.Close

Wscript.echo "Records read:"&NumberOfRows
Wscript.echo "Elapsed time:"&DateDiff("s",starttime,time)&" seconds."


function quotestring(s)
has_single_quotes = false
has_double_quotes = false
needs_quoting = false

'
' check input string for special characters
'
for j=1 to len(s)
   ch = Mid(s,j,1)
   select case ch
     case "'"
         has_single_quotes = true
     case """"
         has_double_quotes = true
     case " ","/",";",","
         needs_quoting = true
     case else
         k = asc(ch)
         if (k<=31) or (k>=127) then
            needs_quoting = true
         end if
   end select
next

'
' check if we have if gams keyword
'
kw = array("ABORT","ACRONYM","ACRONYMS","ALIAS","BINARY","DISPLAY","ELSE", _
            "EQUATION","EQUATIONS","EXECUTE","FILE","FILES","FOR","FREE",  _
            "IF","INTEGER","LOOP","MODEL","MODELS","NEGATIVE","OPTION",    _
            "OPTIONS","PARAMETER","PARAMETERS","POSITIVE","PROCEDURE",     _
            "PROCEDURES","PUT","PUTCLEAR","PUTCLOSE","PUTHD","PUTPAGE",    _
            "PUTTL","SCALAR","SCALARS","SEMICONT","SET","SETS","SOS1",     _
            "SOS2","TABLE","VARIABLE","VARIABLES","WHILE")

if not needs_quoting then
   for j = 0 to Ubound(kw)
      if strcomp(s,kw(j),1)=0 then
        needs_quoting = true
        exit for
      end if
   next
end if

'
' already quoted?
'
ch = left(s,1)
select case ch
   case "'", """"
       quotestring = s
       exit function
end select

' check for special case
if has_single_quotes and has_double_quotes then
   quotestring = """" & replace(s, """", "'") & """"
elseif has_single_quotes then
   quotestring = """" & s & """"
elseif has_double_quotes then
   quotestring = "'" & s & "'"
elseif needs_quoting then
   quotestring = "'" & s & "'"
else
   quotestring = s
end if


end function
$offecho

execute '=cscript sql2gms.vbs';

1.9.5 SQL2GMS timeouts