Reading relations and indexed sets from databases

AIMMS does not support reading relations and indexed sets directly using the READ FROM TABLE statement. Therefore a table in a database such as

and with structure

cannot be directly copied to an AIMMS relation or an AIMMS indexed set.

This article details a workaround to transfer the above table to a relation and to an indexed set and is based on `this AIMMS 4.80 reading relations indexed set project`.

First, to add a column artificially to an existing table in a database is obtained using the SQL:

```1select A, B, 1 as one from AB ;
```

where the fragment `1 as one` tricks the SQL interpreter to add an artificial column with all 1’s.

This SQL fragment is embedded in an AIMMS database procedure as follows:

``` 1DatabaseProcedure dbpr_addColumnOne {
2    DataSource: "data\\one.dsn";
3    SqlQuery: {
4        "select A, B, 1 as one from AB ;"
5    }
6    Property: UseResultSet;
7    Mapping: {
8        "A" --> i_a,
9        "B" --> i_b,
10        "one" --> bp_indicatorAB(i_a,i_b)
11    }
12}
```

Subsequently, the result of that database procedure is read and converted to a relation and to an indexed set as follows:

Remarks:

• Line 1: Normal way of reading result set from a database procedure

• Line 2: Converting the binary parameter to a relation

• Line 3: Converting the binary parameter to an indexed set