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

../../_images/data-sqlite-database.png

and with structure

../../_images/data-sqlite-structure.png

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:

../../_images/read-convert-data.png

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