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

   file ociinv.mos
   ```````````````
   Accessing an Oracle database with selection 
   statements using columns in inverted order.
       
   (c) 2008 Fair Isaac Corporation
       author: S. Heipcke, 2007, rev. Aug. 2023
*******************************************************!)

model "OCI selection of columns"
 uses "mmoci"
 options keepassert

 parameters
  DB="myname/mypassword@dbname"     ! Login to Oracle database (not provided)
 end-parameters

 declarations
  PRODUCTS: set of string
  MACH: range
  COST,COST2: dynamic array(MACH,PRODUCTS) of real
  DUR,DUR2,TEMP: dynamic array(PRODUCTS,MACH) of integer
 end-declarations

 setparam("OCIdebug",true)
 OCIlogon(DB)
 assert(getparam("OCIsuccess"))

! Read data from the table 'ProdData'
 OCIexecute("select Mach,Products,Cost from ProdData", COST)
 OCIexecute("select Products,Mach,Duration from ProdData", DUR)

! Print out what we have read
 writeln(COST); writeln(DUR)

! Write out data to another table (after deleting and re-creating the table)
 OCIexecute("drop table CombData")
 OCIexecute("create table CombData (Products varchar(10), Mach integer, Cost float, Duration integer)")
 assert(getparam("OCIsuccess"))

 OCIexecute("insert into CombData (Mach,Products,Cost) values (:1,:2,:3)", COST)
! Altenatively:
! OCIexecute("insert into CombData (Products,Mach,Cost) values (:2,:1,:3)", COST)
 assert(getparam("OCIsuccess"))

! Fill the 'Duration' field of the output table:
! 1. update the existing entries, 2. add new entries
 OCIexecute("update CombData set Duration=:3 where Products=:1 and Mach=:2", DUR)
 assert(getparam("OCIsuccess"))
 forall(p in PRODUCTS, m in MACH | exists(DUR(p,m)) and not exists(COST(m,p)))
  TEMP(p,m) := DUR(p,m)
 OCIexecute("insert into CombData (Products,Mach,Duration) values (:1,:2,:3)", TEMP)
 assert(getparam("OCIsuccess"))
 
 OCIlogoff

 initializations from "mmoci.oci:debug;"+DB
  COST2 as "ProdData(Mach,Products,Cost)"
  DUR2 as "ProdData(Products,Mach,Duration)"
 end-initializations

 writeln(COST2); writeln(DUR2)

! Delete and re-create the output table
 OCIlogon(DB)
 assert(getparam("OCIsuccess"))
 OCIexecute("drop table CombData2")
 OCIexecute("create table CombData2 (Products varchar(10), Mach integer, Cost float, Duration integer)")
 assert(getparam("OCIsuccess"))
 OCIlogoff

 initializations to "mmoci.oci:debug;"+DB
  COST2 as "CombData2(Mach,Products,Cost)"
  DUR2 as "CombData2(Products,Mach,Duration)"
 end-initializations
 
end-model


**************************************************


! Creation of the data table in an Oracle database:


 declarations
  tsucc: array ({false,true}) of string
 end-declarations

 tsucc(false):="failed"; tsucc(true):="succeeded"

 OCIexecute("create table ProdData (Mach integer, Products varchar(10), Cost float, Duration integer)")
 writeln(" - Create ProdData (",tsucc(getparam("OCIsuccess")),")")

 declarations
  PRODUCTS: set of string
  MACH: range
  COST: dynamic array(PRODUCTS,MACH) of real
  DUR: dynamic array(PRODUCTS,MACH) of integer
 end-declarations

 initializations from "ocidata.dat"
  COST DUR
 end-initializations

 OCIexecute("insert into ProdData (Mach, Products, Cost, Duration) values (:2, :1, :3, :4)", [COST,DUR])
 writeln(" - Insert values in ProdData (",tsucc(getparam("OCIsuccess")),",",
        getparam("OCIrowcnt"),'/',getparam("OCIrowxfr")," rows)")