FICO
FICO Xpress Optimization Examples Repository
FICO Optimization Community FICO Xpress Optimization Home
Back to examples browserPrevious exampleNext example

Writing out solution values to text files, spreadsheets or databases

Description
Solution values of decision variables or constraints need to be copied into an array. The contents of this array can then be written to a spreadsheet or database using ODBC or software-specific drivers (or simply to text file using Mosel's default output).

  • Spreadsheets and databases can be accessed from Mosel through an ODBC connection using the odbc driver (soleg.mos) or with SQL statements (soleg2.mos).
  • If Microsoft Excel is installed, the software-specific driver excel (soleg3.mos) can be used.
  • For Oracle databases a software-specific connection is provided by the module mmoci (soleg4.mos).
  • For generic spreadsheets it is recommended to use the portable mmsheet module that provides subroutines to read and write xls and xlsx (soleg5.mos) and csv (soleg6.mos) files.
As an alternative to the explicit copying of arrays of solution values you can use the keyword evaluation in initializations to blocks. If preceded by the marker evaluation of solution values, results of Mosel functions or other expressions can be used directly in the initializations to block as shown in the example initeval.mos.

Further explanation of this example: Xpress Whitepaper 'Using ODBC and other database interfaces with Mosel', Section Examples - Outputting solution values. 'Mosel User Guide', Section 10.2.4 Solution output with initializations to.


Source Files

Data Files
soleg.csv[download]
soleg.mdb[download]
soleg.xls[download]
soleg.xlsx[download]
soleg.sqlite[download]





soleg2.mos

(!******************************************************
   Mosel Example Problems
   ====================== 

   file soleg2.mos
   ```````````````
   Writing out solution values to
   a spreadsheet or database via ODBC.
   - Using SQL commands -
   
   (c) 2008 Fair Isaac Corporation
       author: S. Heipcke, 2006, rev. Aug. 2023
*******************************************************!)

model "Solution values output (2)"
 uses "mmxprs", "mmodbc"
 options keepassert

 parameters
!  CNCT = "soleg.xls"            ! Use Excel spreadsheet `soleg.xls'
  CNCT = "soleg.mdb"             ! Use Access database `soleg.mdb'
                                 ! Use SQLite database `soleg' via ODBC
!  CNCT = 'DSN=sqlite;DATABASE=soleg.sqlite'
!  CNCT = 'soleg.sqlite'         ! Use SQLite database `soleg' directly
 end-parameters
 
 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)) 

 setparam("SQLdebug", true)

! Data output using SQL statements
 SQLconnect(CNCT)
 assert(getparam("SQLsuccess"))
 SQLexecute("insert into MyOut2 (First, Second, Solution) values (?,?,?)", SOL)

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

! Alternatively after the first model run (for databases only):
! SQLupdate("select First, Second, Solution from MyOut2", SOL)
! or:
! SQLexecute("update MyOut2 set Solution=?3 where First=?1 and Second=?2", SOL)

 assert(getparam("SQLsuccess"))

 SQLdisconnect

end-model

Back to examples browserPrevious exampleNext example