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

   file burglar2o.mos
   ``````````````````
   Use of ODBC driver for data handling,
   or alternatively, use of SQL statements.
   
   (c) 2008 Fair Isaac Corporation
       author: S. Heipcke, 2004, rev. July 2017
*******************************************************!)

model "Burglar2 (ODBC)"
 uses "mmxprs", "mmodbc"
 
 parameters
                          ! Windows only: Use Access database `burglar.mdb'
!  CNCT = 'DSN=MS Access Database;DBQ=burglar.mdb'
!  CNCTIO = "mmodbc.odbc:debug;burglar.mdb"
                          ! Use mysql database `burglar' (not provided)
!  CNCT = 'DSN=mysql;DB=burglar'
!  CNCTIO = "mmodbc.odbc:debug;DSN=mysql;DB=burglar"
                          ! Use SQLite database `burglar.sqlite'
  CNCT = 'burglar.sqlite'
  CNCTIO = "mmodbc.odbc:debug;burglar.sqlite"
 end-parameters
 
 declarations
  WTMAX = 102                    ! Maximum weight allowed
  ITEMS: set of string           ! Index set for items
  VALUE: array(ITEMS) of real    ! Value of items
  WEIGHT: array(ITEMS) of real   ! Weight of items
  SOLTAKE: array(ITEMS) of real  ! Solution values
 end-declarations

 initializations from CNCTIO
  [VALUE,WEIGHT] as "BurgData"
 end-initializations

(! Alternatively:
 SQLconnect(CNCT)
 SQLexecute("select ITEM,VALUE,WEIGHT from BurgData", [VALUE,WEIGHT])
 SQLdisconnect
!)

 declarations
  take: array(ITEMS) of mpvar    ! 1 if we take item i; 0 otherwise
 end-declarations

! Objective: maximize total value
 MaxVal:= sum(i in ITEMS) VALUE(i)*take(i) 

! Weight restriction
 sum(i in ITEMS) WEIGHT(i)*take(i) <= WTMAX

! All variables are 0/1
 forall(i in ITEMS) take(i) is_binary  

 maximize(MaxVal)                ! Solve the problem

! Solution output
 forall(i in ITEMS) SOLTAKE(i):= getsol(take(i))
 writeln(SOLTAKE)

! Insert solutions into database/spreadsheet: results from previous runs
! must be removed previously; otherwise the new results will either be
! appended to the existing ones or, if "ITEM" has been defined as key field
! in a database, the insertion will fail.
 initializations to CNCTIO
  SOLTAKE as "SolTake"
 end-initializations

(! Alternatively:
 SQLconnect(CNCT)
 SQLexecute("delete from SolTake")    ! Cleaning up previous results: works
                                      ! only for databases, cannot be used
                                      ! with spreadsheets (instead, delete
                                      ! previous solutions directly in the
                                      ! spreadsheet file)
 SQLexecute("insert into SolTake (ITEM,TAKE) values (?,?)" , SOLTAKE) 
 SQLdisconnect
!) 

end-model