Using the Shared Database

(!
    Example of using shared database.
    This is a variant of the Burglar example where the item values and weights are stored in the database.
    (c) 2019 Fair Isaac Corporation.
!)
model "burglar_with_solution_db"
  options keepassert
  uses "trusteddsn","mmodbc","mmxprs"

  parameters
    ! Whether to reset solution database at the start of the model
    RESET_SOLDB=false
  end-parameters

  ! **** Declare public data **** !
  declarations
    ! Maximum weight alowed for haul
    WTMAX: integer
    ! Index range for items
    Items=1..8

    x: array(Items) of mpvar

    VALUE: array(Items) of real
    WEIGHT: array(Items) of real
  end-declarations

  ! *** Allow connection to the solution database *** !
  trusteddsninit
  ! *** Connect to solution database *** !
  SQLconnect("DSN=customdb")
  assert(getparam("SQLsuccess"))

  ! *** Optionally, reset solution datbase to default items *** !
  if RESET_SOLDB then
    ! *** Create solution database *** !
    SQLexecute("CREATE TABLE IF NOT EXISTS Items (item INTEGER NOT NULL PRIMARY KEY, value DOUBLE NOT NULL, weight DOUBLE NOT NULL)")
    assert(getparam("SQLsuccess"))

    ! *** Populate solution database *** !
    ! Note: this deletes everything in the solution database - this is okay for the purpose of the example, probably not what you want to do
    ! in your actual model.
    SQLexecute("DELETE FROM Items")
    assert(getparam("SQLsuccess"))

    VALUE :: [15,100, 90, 60, 40, 15, 10,  1]
    WEIGHT:: [ 2, 20, 20, 30, 40, 30, 60, 10]
    SQLexecute("INSERT INTO Items (item,value,weight) VALUES (?,?,?)", [VALUE, WEIGHT])
    assert(getparam("SQLsuccess"))
  end-if

  ! *** Set the maximum weight - in a real model, this would be input data read from a file *** !
  WTMAX := 102

  ! *** Load data from the shared database *** !
  SQLexecute("SELECT item, value, weight FROM items", [VALUE, WEIGHT])
  assert(getparam("SQLsuccess"))

  ! *** Construct the model *** !
  MaxVal:= sum(i in Items) VALUE(i)*x(i)  ! Objective: maximize total value

  ! Weight restriction
  WtMax:= sum(i in Items) WEIGHT(i)*x(i) <= WTMAX

  forall(i in Items) x(i) is_binary  ! All x are 0/1

  ! *** Solve the problem *** !
  maximize(MaxVal)

  ! *** Print out the solution *** !
  writeln("Solution:\n Objective: ", getobjval)
  forall(i in Items)  writeln(" x(", i, "): ", x(i).sol)
end-model