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

   file burglar2sql.mos
   ````````````````````
   Data read from SQLite DB.
   
   (c) 2014 Fair Isaac Corporation
       author: L. Varghese, Oct. 2014, rev. Aug. 2023
*******************************************************!)

model Burglar2sql
 uses "mmxprs"
 uses "mmodbc"
 
 parameters
 ! SQLite database:
  CNCT="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
 end-declarations

! Reading data from SQLite DB
 SQLconnect(CNCT)
 if not getparam("SQLsuccess"): setioerr("Database connection failed")
 SQLexecute("select * from BurgData", [VALUE, WEIGHT])
 SQLdisconnect	

(! Alternatively:
 initializations from "mmodbc.odbc:" + CNCT
  [VALUE,WEIGHT] as "BurgData"
 end-initializations
!)

 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 MIP-problem

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

 writeln("Solution:\n Objective: ", getobjval)
 writeln(SOLTAKE)

 SQLconnect(CNCT)
 SQLexecute("delete from SolTake")
 SQLexecute("insert into SolTake values (?,?)", SOLTAKE)
 SQLdisconnect

(! Alternatively:
 initializations to "mmodbc.odbc:" + CNCT
  SOLTAKE as "SolTake"
 end-initializations
!)

end-model