FICO Xpress Optimization Examples Repository: Writing out solution values to text files, spreadsheets or databases

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.

(!****************************************************** Mosel Example Problems ====================== file soleg4.mos ``````````````` Writing out solution values to an Oracle database. - Using 'initializations to' and SQL statements - (c) 2008 Fair Isaac Corporation author: S. Heipcke, 2007, rev. Aug. 2023 *******************************************************!) model "Solution values output (4)" uses "mmxprs", "mmoci" options keepassert parameters DB="myname/mypassword@dbname" ! Login to Oracle database (not provided) 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)) ! **** Using 'initializations to' **** initializations to "mmoci.oci:debug;"+DB SOL as "MyOut1" end-initializations ! **** Using SQL statements **** OCIlogon(DB) assert(getparam("OCIsuccess")) setparam("OCIdebug",true) OCIexecute("delete from MyOut2") OCIexecute("insert into MyOut2 (First, Second, Solution) values (:1,:2,:3)", SOL) (! Alternative form: OCIexecute("insert into MyOut2 (First, Second, Solution) values (:1,:2,:3)", array(i in R, j in S) x(i,j).sol) !) ! Alternatively after the first model run: ! OCIexecute("update MyOut2 set Solution=:3 where First=:1 and Second=:2", SOL) assert(getparam("OCIsuccess")) OCIlogoff end-model ************************************************** ! Creation of output tables in an Oracle database: declarations tsucc: array ({false,true}) of string end-declarations tsucc(false):="failed"; tsucc(true):="succeeded" OCIexecute("create table MyOut1 (First integer, Second integer, Solution float)") writeln(" - Create MyOut1 (",tsucc(getparam("OCIsuccess")),")") OCIexecute("create table MyOut2 (First integer, Second integer, Solution float)") writeln(" - Create MyOut2 (",tsucc(getparam("OCIsuccess")),")")