Description

This example demonstrates how to read set data from a database file using
MDB2GMS. In particular, it focuses on how to read multi dimensional set elements
and set elements with explanatory text when writing the results to an include
file.

This model is referenced in
   "Example 3 - Reading a one dimensional Set",
   "Example 4 - Reading a multi dimensional Set" and
   "Example 5 - Reading Sets with Explanatory Text"
from the MDB2GMS Documentation.

Keywords: MDB2GMS, data exchange, GAMS language features

Category : GAMS Data Utilities library

Main file : mdbReadingSets.gms   includes :  Sample.accdb  mdbReadingSets.gms

$title MDB2GMS Example 3,4,5 - Reading Sets from Database using MDB2GMS (mdbReadingSets,SEQ=132)

$onText
This example demonstrates how to read set data from a database file using
MDB2GMS. In particular, it focuses on how to read multi dimensional set elements
and set elements with explanatory text when writing the results to an include
file.

This model is referenced in
   "Example 3 - Reading a one dimensional Set",
   "Example 4 - Reading a multi dimensional Set" and
   "Example 5 - Reading Sets with Explanatory Text"
from the MDB2GMS Documentation.

Keywords: MDB2GMS, data exchange, GAMS language features
$offText

$callTool win32.msappavail Access
$if errorlevel 1 $abort.noError "No Access available"

* 1. Reading a one dimensional set from the table distances stored in Sample.accdb
$call mdb2gms I=Sample.accdb Q="SELECT distinct(city1) FROM distances" O=city_i.inc > %system.nullfile%
$ifE errorLevel<>0 $abort Error reading the one dimensional set using MDB2GMS!

Set i 'canning plants' /
$include city_i.inc
/;

display i;


* 2. Reading multi dimensional set elements
*    Suppose we want to define a two dimensional set ij(i,j) 'canning plants - markets'
*    based on the data of the table distances stored in Sample.accdb
$call mdb2gms I=Sample.accdb Q="SELECT city1, city2, ' ' FROM distances" O=city_ij.inc > %system.nullfile%
$ifE errorLevel<>0 $abort Error reading multi dimensional set using MDB2GMS!

* Alternatively, you may run the following statement:
*$call mdb2gms I=Sample.accdb Q="SELECT city1&'.'&city2 FROM distances" O=city_ij.inc > %system.nullfile%

Set j 'markets' / new-york, chicago, topeka /;

Set ij(i,j) 'two dimensional set' /
$include city_ij.inc
/;

display ij;


* 3. Reading set elements with explanatory text
* a) Storing the query results as include file
$call mdb2gms I=Sample.accdb B Q="SELECT setElement, explText FROM setData" O=setData.inc > %system.nullfile%
$ifE errorLevel<>0 $abort Error reading set with explanatory text using MDB2GMS (output: .inc)!

Set a /
$include setData.inc
/;

* b) Storing the results as GDX file --> use the arguments X and S
$call mdb2gms I=Sample.accdb Q="SELECT setElement, explText FROM setData" X=setData.gdx Y=set_b > %system.nullfile%
$ifE errorLevel<>0 $abort Error reading set with explanatory text using MDB2GMS (output: .gdx)!

Set b;
$gdxIn setData.gdx
$load b = set_b
$gdxIn