Outputting solution values

Writing the results of an optimization run to a database/spreadsheet is a two stage process.

1. Gather the solution data into a Mosel array.
2. Use ODBC/SQL/spreadsheet drivers to write to the external data source.

Using decision variables or constraints directly when writing out the data will not result in the solution values being written out.

The following Mosel model soleg.mos implements a tiny transportation problem using decision variables x of type mpvar. The array SOL receives the solution values of these variables. This array is then written out to an external data source (spreadsheet or database).

model "Solution values output"
 uses "mmxprs", "mmodbc"

 declarations
  R = 1..3
  S = 1..2
  SOL: array(R,S) of real        ! Array for solution values
  x: array(R,S) of mpvar         ! Decision variables
 end-declarations

! Define and solve the problem
 forall(i in R) sum(j in S) x(i,j) <= 4
 forall(j in S) sum(i in R) x(i,j) <= 6
 maximise( sum(i in R, j in S) (i*j)*x(i,j) )

! Get solution values from LP into the array SOL
 forall(i in R, j in S) SOL(i,j) := getsol(x(i,j))

! Use an initializations block with the odbc driver to write out data
 initializations to "mmodbc.odbc:soleg.sqlite"
  SOL as "MyOut1"
 end-initializations

end-model 

The alternative method of using SQL statements for writing out the data looks as follows for this problem:

 SQLconnect("soleg.sqlite")
 SQLexecute("insert into MyOut2 (First, Second, Solution) values (?,?,?)", SOL)
 SQLdisconnect 

When working with a database, it may be preferable to use SQLupdate instead of SQLexecute to avoid having to clear the contents of the output table before every model run. (Notice that the `update' command will only work if the table contains already data from previous runs). The corresponding SQL statement is:

 SQLupdate("select First, Second, Solution from MyOut2", SOL)

This command cannot be used with a spreadsheet; the results from previous runs always need to be removed by hand directly in the spreadsheet. With MS Excel we therefore recommend to use the excel driver instead of an ODBC connection to be able to overwrite any existing output data in the spreadsheet:

 initializations to "mmsheet.excel:soleg.xls"
  SOL as "skiph;MyOut1"
 end-initializations 

Note: Instead of explicitly creating an array SOL to hold the solution values, it is also possible to create a temporary array immediately within the initializations to block using evaluation of in conjunction with the array operator:

 initializations to "mmodbc.odbc:soleg.sqlite"
  evaluation of array(i in R, j in S) x(i,j).sol as "MyOut1"
 end-initializations 

Or the corresponding form within a SQL query:

 SQLexecute("insert into MyOut2 (First, Second, Solution) values (?,?,?)",
            array(i in R, j in S) x(i,j).sol)