Initializing help system before first use

Writing out solution values to text files, spreadsheets or databases
Type: Programming
Rating: 2 (easy-medium)
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.
File(s): soleg.mos, soleg2.mos, soleg3.mos, soleg4.mos, soleg5.mos, soleg6.mos
Data file(s): soleg.csv, soleg.mdb, soleg.xls, soleg.xlsx, soleg.sqlite

soleg.mos
(!******************************************************
   Mosel Example Problems
   ====================== 

   file soleg.mos
   ``````````````
   Writing out solution values to
   a spreadsheet or database via ODBC.
   - Using 'initializations to' with the odbc driver -
  
   (c) 2008 Fair Isaac Corporation
       author: S. Heipcke, 2006, rev. Oct. 2017
*******************************************************!)

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

 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)) 

! Data output using an initializations block with the odbc driver
 initializations to "mmodbc.odbc:debug;"+CNCT
  SOL as "MyOut1"
 end-initializations

(! Alternative form:
 initializations to "mmodbc.odbc:debug;"+CNCT
  evaluation of array(i in R, j in S) x(i,j).sol as "MyOut1"
 end-initializations
!)
end-model
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. Oct. 2017
*******************************************************!)

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

 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)
 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)

 SQLdisconnect

end-model
soleg3.mos
(!******************************************************
   Mosel Example Problems
   ====================== 

   file soleg3.mos
   ```````````````
   Writing out solution values to
   an Excel spreadsheet.
   - Using 'initializations to' with the excel driver -
  
   (c) 2008 Fair Isaac Corporation
       author: S. Heipcke, 2007, rev. Oct. 2017
*******************************************************!)

model "Solution values output (3)"
 uses "mmxprs", "mmsheet"

 parameters
  CNCT = "soleg.xls"             ! Use Excel spreadsheet `soleg.xls'
 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)) 

! Data output using an initializations block with the excel driver
 initializations to "mmsheet.excel:"+CNCT
  SOL as "grow;MyOut3"
 end-initializations

(! Alternative form:
 initializations to "mmmsheet.excel"+CNCT
  evaluation of array(i in R, j in S) x(i,j).sol as "grow;MyOut3"
 end-initializations
!)
end-model
soleg4.mos
(!******************************************************
   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. Oct. 2017
*******************************************************!)

model "Solution values output (4)"
 uses "mmxprs", "mmoci"

 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)
 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)

 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")),")")
soleg5.mos
(!******************************************************
   Mosel Example Problems
   ====================== 

   file soleg5.mos
   ```````````````
   Writing out solution values to a spreadsheet.
   - Using 'initializations to' with the xls driver -
  
   (c) 2012 Fair Isaac Corporation
       author: S. Heipcke, Dec. 2012, rev. Oct. 2017
*******************************************************!)

model "Solution values output(generic spreadsheet)"
 uses "mmxprs", "mmsheet"

 parameters
  CNCT= 'mmsheet.xls:soleg.xls'
!  CNCT= 'mmsheet.xlsx:soleg.xlsx'
 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)) 

! Data output using an initializations block with the excel driver
 initializations to CNCT
  SOL as "grow;MyOut3"
 end-initializations

(! Alternative form:
 initializations to CNCT
  evaluation of array(i in R, j in S) x(i,j).sol as "grow;MyOut3"
 end-initializations
!)
end-model
soleg6.mos
(!******************************************************
   Mosel Example Problems
   ====================== 

   file soleg6.mos
   ```````````````
   Writing out solution values to a spreadsheet.
   - Using 'initializations to' with the csv driver -
  
   (c) 2012 Fair Isaac Corporation
       author: S. Heipcke, Dec. 2012, rev. Oct. 2017
*******************************************************!)

model "Solution values output (CSV)"
 uses "mmxprs", "mmsheet"

 parameters
  CNCT= 'mmsheet.csv:soleg.csv'
 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)) 

! Data output using an initializations block with the xls driver
 initializations to CNCT
  SOL as "grow;[A3:C3]"
 end-initializations

(! Alternative form:
 initializations to CNCT
  evaluation of array(i in R, j in S) x(i,j).sol as "grow;[A3:C3]"
 end-initializations
!)
end-model
Parent Topic Mosel language: Examples from the whitepaper 'Using ODBC and other database interfaces with Mosel'

© 2001-2020 Fair Isaac Corporation. All rights reserved. This documentation is the property of Fair Isaac Corporation (“FICO”). Receipt or possession of this documentation does not convey rights to disclose, reproduce, make derivative works, use, or allow others to use it except solely for internal evaluation purposes to determine whether to purchase a license to the software described in this documentation, or as otherwise set forth in a written software license agreement between you and FICO (or a FICO affiliate). Use of this documentation and the software described in it must conform strictly to the foregoing permitted uses, and no other use is permitted.