| multicol.mos |
(!*******************************************************
Mosel Example Problems
======================
file multicol.mos
`````````````````
Reading several data arrays from a single table.
- Using 'initializations from' with odbc IO driver -
(c) 2008 Fair Isaac Corporation
author: S. Heipcke, 2006, rev. Jan. 2014
*******************************************************!)
model "Multiple data columns"
uses "mmetc" , "mmodbc"
declarations
PRODUCTS: set of string
MACH: range
COST,COST1,COST2: dynamic array(PRODUCTS,MACH) of real
DUR,DUR1,DUR2: dynamic array(PRODUCTS,MACH) of integer
end-declarations
! **** Read data in Mosel format ****
initializations from "multicol.dat"
[COST,DUR] as "ProdData"
end-initializations
writeln("Mosel format:"); writeln(COST); writeln(DUR)
! **** Read data in diskdata format ****
initializations from "mmetc.diskdata:sparse"
[COST1,DUR1] as "multicdd.dat"
end-initializations
writeln("'diskdata' format:"); writeln(COST1); writeln(DUR1)
! **** Reading data from databases ****
! This assumes the spreadsheet/database contains a table "ProdData"
! in sparse format (i.e., with indices) with the columns "COST" and "DUR"
(! Read data from the Excel spreadsheet multicol.xls
!
initializations from "mmodbc.odbc:multicol.xls"
[COST2,DUR2] as 'ProdData'
end-initializations
!)
! Read data from the Access database multicol.mdb
initializations from "mmodbc.odbc:multicol.mdb"
[COST2,DUR2] as 'ProdData'
end-initializations
! Read data from the mysql database multicol (not provided)
(!
initializations from "mmodbc.odbc:multicol"
[COST2,DUR2] as 'ProdData'
end-initializations
!)
! Read data from the SQLite database multicol.sqlite via ODBC
(!
initializations from "mmodbc.odbc:DSN=sqlite;DATABASE=multicol.sqlite"
[COST2,DUR2] as 'ProdData'
end-initializations
!)
! Read data from the SQLite database multicol.sqlite using direct access
(!
initializations from "mmodbc.odbc:multicol.sqlite"
[COST2,DUR2] as 'ProdData'
end-initializations
!)
writeln("ODBC:"); writeln(COST2); writeln(DUR2)
end-model
|
|
| multicol2.mos |
(!*******************************************************
Mosel Example Problems
======================
file multicol2.mos
``````````````````
Reading several data arrays from a single table.
- Using SQL commands -
(c) 2008 Fair Isaac Corporation
author: S. Heipcke, 2002, rev. Jan. 2014
*******************************************************!)
model "Multiple data columns (2)"
uses "mmetc" , "mmodbc"
declarations
PRODUCTS: set of string
MACH: range
COST,COST1,COST2: dynamic array(PRODUCTS,MACH) of real
DUR,DUR1,DUR2: dynamic array(PRODUCTS,MACH) of integer
end-declarations
! **** Read data in Mosel format ****
initializations from "multicol.dat"
[COST,DUR] as "ProdData"
end-initializations
writeln("Mosel format:"); writeln(COST); writeln(DUR)
! **** Read data in diskdata format ****
diskdata(ETC_IN+ETC_SPARSE, "multicdd.dat", [COST1,DUR1])
! Alternatively: use diskdata IO driver:
(!
initializations from "mmetc.diskdata:sparse"
[COST1,DUR1] as "multicdd.dat"
end-initializations
!)
writeln("'diskdata' format:"); writeln(COST1); writeln(DUR1)
! **** Reading data from databases ****
! This assumes the spreadsheet/database contains a table "ProdData"
! in sparse format (i.e., with indices) with the columns "COST" and "DUR"
! Read data from the Excel spreadsheet multicol.xls
! SQLconnect('DSN=Excel Files;DBQ=C:/xpress/examples/mosel/WhitePapers/MoselODBC/multicol.xls')
! SQLconnect('multicol.xls')
! Read data from the Access database multicol.mdb
! SQLconnect('DSN=MS Access Database;DBQ=C:/xpress/examples/mosel/WhitePapers/MoselODBC/multicol.mdb')
SQLconnect('multicol.mdb')
! Read data from the mysql database multicol (not provided)
! SQLconnect('DSN=mysql;DB=multicol')
! Read data from the SQLite database multicol.sqlite
! SQLconnect('DSN=sqlite;DATABASE=multicol.sqlite')
! SQLconnect('multicol.sqlite')
setparam("SQLverbose",true)
SQLexecute("select * from ProdData ", [COST2,DUR2])
SQLdisconnect
writeln("ODBC:"); writeln(COST2); writeln(DUR2)
end-model
|
|
| multicol3.mos |
(!*******************************************************
Mosel Example Problems
======================
file multicol3.mos
``````````````````
Reading several data arrays from a single table.
- Using 'initializations from' with excel IO driver -
(c) 2008 Fair Isaac Corporation
author: S. Heipcke, 2007, rev. Jul. 2010
*******************************************************!)
model "Multiple data columns (3)"
declarations
PRODUCTS: set of string
MACH: range
COST2: dynamic array(PRODUCTS,MACH) of real
DUR2: dynamic array(PRODUCTS,MACH) of integer
end-declarations
! **** Reading data from an Excel spreadheet ****
! This assumes the spreadsheet contains a table "ProdData" in
! sparse format (i.e., with indices) with the columns "COST" and "DUR"
initializations from "mmsheet.excel:multicol.xls"
[COST2,DUR2] as 'skiph;ProdData'
end-initializations
writeln("Excel:"); writeln(COST2); writeln(DUR2)
end-model
|
|
| multicol4.mos |
(!*******************************************************
Mosel Example Problems
======================
file multicol4.mos
``````````````````
Reading several data arrays from a single table.
- Using 'initializations' and OCI statements -
(c) 2012 Fair Isaac Corporation
author: S. Heipcke, Dec. 2012
*******************************************************!)
model "Multiple data columns (OCI)"
uses "mmoci"
parameters
DB="myname/mypassword@dbname" ! Login to Oracle database (not provided)
end-parameters
declarations
PRODUCTS: set of string
MACH: range
COST,COST1,COST2: dynamic array(PRODUCTS,MACH) of real
DUR,DUR1,DUR2: dynamic array(PRODUCTS,MACH) of integer
end-declarations
! **** Using SQL statements ****
! This assumes the database contains a table "ProdData"
! in sparse format (i.e., with indices) with the columns "COST" and "DUR"
setparam("OCIdebug",true)
OCIlogon(DB)
writeln("Connection number: ",getparam("OCIconnection"))
OCIexecute("select * from ProdData ", [COST1,DUR1])
OCIlogoff
writeln("OCI:"); writeln(COST1); writeln(DUR1)
! **** Using 'initializations from' ****
initializations from "mmoci.oci:debug;"+DB
[COST2,DUR2] as 'ProdData'
end-initializations
writeln("initializations from:"); writeln(COST2); writeln(DUR2)
end-model
**************************************************
! Creation of data tables in an Oracle database:
declarations
tsucc: array ({false,true}) of string
end-declarations
tsucc(false):="failed"; tsucc(true):="succeeded"
initializations from "multicol.dat"
[COST,DUR] as "ProdData"
end-initializations
setparam("OCIdebug",true)
OCIlogon(DB)
writeln("Connection number: ",getparam("OCIconnection"))
OCIexecute("drop table ProdData")
OCIexecute("create table ProdData (Products varchar(10), Mach integer, Cost float, Duration integer)")
writeln(" - Create ProdData (",tsucc(getparam("OCIsuccess")),")")
OCIexecute("insert into ProdData (Products, Mach, Cost, Duration) values (:1, :2, :3, :4)", [ COST,DUR ])
writeln(" - Insert values into ProdData (",tsucc(getparam("OCIsuccess")),",",
getparam("OCIrowcnt"),'/',getparam("OCIrowxfr")," rows)")
OCIlogoff
|
|
| multicol5.mos |
(!*******************************************************
Mosel Example Problems
======================
file multicol5.mos
``````````````````
Reading several data arrays from a single table.
- Using 'initializations from' with xls IO driver -
(c) 2012 Fair Isaac Corporation
author: S. Heipcke, Dec. 2012
*******************************************************!)
model "Multiple data columns (generic spreadsheet)"
uses "mmsheet"
parameters
! CSTR= 'mmsheet.xls:multicol.xls'
CSTR= 'mmsheet.xlsx:multicol.xlsx'
end-parameters
declarations
PRODUCTS: set of string
MACH: range
COST2: dynamic array(PRODUCTS,MACH) of real
DUR2: dynamic array(PRODUCTS,MACH) of integer
end-declarations
! **** Reading data from a spreadheet ****
! This assumes the spreadsheet contains a table "ProdData" in
! sparse format (i.e., with indices) with the columns "COST" and "DUR"
initializations from CSTR
[COST2,DUR2] as 'skiph;ProdData'
end-initializations
writeln("XLS:"); writeln(COST2); writeln(DUR2)
end-model
|
|
| multicol6.mos |
(!*******************************************************
Mosel Example Problems
======================
file multicol6.mos
``````````````````
Reading several data arrays from a single table.
- Using 'initializations from' with csv IO driver -
(c) 2012 Fair Isaac Corporation
author: S. Heipcke, Dec. 2012
*******************************************************!)
model "Multiple data columns (CSV)"
uses "mmsheet"
parameters
CSTR= 'mmsheet.csv:multicol.csv'
end-parameters
declarations
PRODUCTS: set of string
MACH: range
COST2: dynamic array(PRODUCTS,MACH) of real
DUR2: dynamic array(PRODUCTS,MACH) of integer
end-declarations
! **** Reading data from a spreadheet ****
! This assumes the spreadsheet contains a data table in
! sparse format (i.e., with indices) with the columns "COST" and "DUR"
initializations from CSTR
[COST2,DUR2] as '[A3:D11]'
end-initializations
writeln("CSV:"); writeln(COST2); writeln(DUR2)
end-model
|
|
| multiout.mos |
(!*******************************************************
Mosel Example Problems
======================
file multiout.mos
`````````````````
Output several data arrays into a single table.
- Using 'initializations to' with odbc driver -
(c) 2008 Fair Isaac Corporation
author: S. Heipcke, 2006, rev. Jan. 2014
*******************************************************!)
model "Output multiple data columns"
uses "mmetc" , "mmodbc"
declarations
PRODUCTS: set of string
MACH: range
COST: dynamic array(PRODUCTS,MACH) of real
DUR: dynamic array(PRODUCTS,MACH) of integer
end-declarations
! Read data
initializations from "multiout.dat"
COST DUR
end-initializations
! **** Writing data to text files ****
! Write out data in Mosel format: add data to the input file
initializations to "multiout.dat"
[COST,DUR] as "CombData"
end-initializations
! Write out data in diskdata format
initializations to "mmetc.diskdata:sparse"
[COST,DUR] as "multiodd.dat"
end-initializations
! **** Writing data to databases ****
setparam("SQLverbose",true)
! Write data to the Access database multicol.mdb
! (this assumes that the table 'CombData' has been created previously):
initializations to "mmodbc.odbc:debug;multicol.mdb"
[COST,DUR] as 'CombData'
end-initializations
! Write data to the Excel spreadsheet multicol.xls
! (this assumes that the range 'CombData' has been created previously):
(! initializations to "mmodbc.odbc:debug;multicol.xls"
[COST,DUR] as 'CombData'
end-initializations
!)
! Write data to the SQLite database multicol.sqlite
initializations to "mmodbc.odbc:debug;multicol.sqlite"
[COST,DUR] as 'CombData'
end-initializations
end-model
|
|
| multiout2.mos |
(!*******************************************************
Mosel Example Problems
======================
file multiout2.mos
``````````````````
Output several data arrays into a single table.
- Using SQL commands -
(c) 2008 Fair Isaac Corporation
author: S. Heipcke, 2002, rev. Feb. 2014
*******************************************************!)
model "Output multiple data columns (2)"
uses "mmetc" , "mmodbc"
declarations
PRODUCTS: set of string
MACH: range
COST: dynamic array(PRODUCTS,MACH) of real
DUR: dynamic array(PRODUCTS,MACH) of integer
Tables: list of string
end-declarations
! Read data
initializations from "multiout.dat"
COST DUR
end-initializations
! **** Writing data to text files ****
! Write out data in Mosel format: add data to the input file
initializations to "multiout.dat"
[COST,DUR] as "CombData"
end-initializations
! Write out data in diskdata format
diskdata(ETC_OUT+ETC_SPARSE, "multiodd.dat", [COST,DUR])
! **** Writing data to databases ****
! Write data to the Access database multicol.mdb
! SQLconnect('DSN=MS Access Database;DBQ=C:/xpress/examples/mosel/WhitePapers/MoselODBC/multicol.mdb')
SQLconnect('multicol.mdb')
setparam("SQLverbose",true)
setparam("SQLdebug",true)
SQLtables(Tables)
if findfirst(Tables, "CombData")>0 then SQLexecute("drop table CombData"); end-if
SQLexecute("create table CombData (Products varchar(10), Mach integer, Cost double, Duration integer)")
SQLexecute("insert into CombData(Products, Mach, Cost, Duration) values (?,?,?,?)", [COST,DUR])
SQLdisconnect
! Write data to the mysql database multicol (not provided)
! SQLconnect('DSN=mysql;DB=multicol')
! Write data to the SQLite database multicol
! SQLconnect('DSN=sqlite;DATABASE=multicol')
SQLconnect('multicol.sqlite')
setparam("SQLverbose",true)
Tables:=[]; SQLtables(Tables)
if findfirst(Tables, "CombData")>0 then SQLexecute("drop table CombData"); end-if
SQLexecute("create table CombData (Products varchar(10), Mach integer, Cost double, Duration integer)")
SQLexecute("insert into CombData(Products, Mach, Cost, Duration) values (?,?,?,?)", [COST,DUR])
SQLdisconnect
! Write data to the Excel spreadsheet multicol.xls
! (this assumes that the range 'CombData' has been created previously):
! SQLconnect('DSN=Excel Files;DBQ=C:/xpress/examples/mosel/WhitePapers/MoselODBC/multicol.xls')
(!
SQLconnect('multicol.xls')
setparam("SQLverbose",true)
SQLexecute("insert into CombData (Products, Mach, Cost, Duration) values (?,?,?,?)", [COST,DUR])
SQLdisconnect
!)
end-model
|
|
| multiout3.mos |
(!*******************************************************
Mosel Example Problems
======================
file multiout3.mos
`````````````````
Output several data arrays into a single table.
- Using 'initializations to' with excel IO driver -
(c) 2008 Fair Isaac Corporation
author: S. Heipcke, 2007, rev. Dec. 2012
*******************************************************!)
model "Output multiple data columns (3)"
uses "mmsheet"
declarations
PRODUCTS: set of string
MACH: range
COST: dynamic array(PRODUCTS,MACH) of real
DUR: dynamic array(PRODUCTS,MACH) of integer
end-declarations
! Read data
initializations from "multiout.dat"
COST DUR
end-initializations
! **** Writing data to the Excel spreadsheets multicol.xls ****
! Options:
! skiph - the range 'CombData' includes a header line
! grow - only the starting line of the range is specified
initializations to "mmsheet.excel:skiph;grow;multicol.xls"
[COST,DUR] as 'CombData'
end-initializations
! Alternative: specify the range/worksheet
initializations to "mmsheet.excel:multicol.xls"
[COST,DUR] as 'grow;[Sheet1$L4:O4]'
end-initializations
end-model
|
|
| multiout4.mos |
(!*******************************************************
Mosel Example Problems
======================
file multiout4.mos
``````````````````
Output several data arrays into a single table.
- Using 'initializations' and OCI statements -
(c) 2012 Fair Isaac Corporation
author: S. Heipcke, Dec. 2012
*******************************************************!)
model "Output multiple data columns (OCI)"
uses "mmoci"
parameters
DB="myname/mypassword@dbname" ! Login to Oracle database (not provided)
end-parameters
declarations
PRODUCTS: set of string
MACH: range
COST: dynamic array(PRODUCTS,MACH) of real
DUR: dynamic array(PRODUCTS,MACH) of integer
end-declarations
! Read data
initializations from "multiout.dat"
COST DUR
end-initializations
! **** Using SQL statements ****
setparam("OCIdebug",true)
OCIlogon(DB)
writeln("Connection number: ", getparam("OCIconnection"))
OCIexecute("create table CombData (Products varchar(10), Mach integer, Cost double, Duration integer)")
OCIexecute("delete from CombData")
OCIexecute("insert into CombData(Products, Mach, Cost, Duration) values (:1,:2,:3,:4)", [COST,DUR])
OCIlogoff
! **** Using 'initializations to' ****
! (this assumes that the table 'CombData' has been created previously)
initializations to "mmoci.oci:debug;"+DB
[COST,DUR] as 'CombData'
end-initializations
end-model
|
|
| multiout5.mos |
(!*******************************************************
Mosel Example Problems
======================
file multiout5.mos
``````````````````
Output several data arrays into a single table.
- Using 'initializations to' with the xls IO driver -
(c) 2012 Fair Isaac Corporation
author: S. Heipcke, Dec. 2012
*******************************************************!)
model "Output multiple data columns (generic spreadsheet)"
uses "mmsheet"
parameters
CSTR= 'mmsheet.xls:'
DFILE= 'multicol.xls'
! CSTR= 'mmsheet.xlsx:'
! DFILE= 'multicol.xlsx'
end-parameters
declarations
PRODUCTS: set of string
MACH: range
COST: dynamic array(PRODUCTS,MACH) of real
DUR: dynamic array(PRODUCTS,MACH) of integer
end-declarations
! Read data
initializations from "multiout.dat"
COST DUR
end-initializations
! **** Writing data to the spreadsheet multicol.xls ****
! Options:
! skiph - the range 'CombData' includes a header line
! grow - only the starting line of the range is specified
initializations to CSTR + "skiph;grow;" + DFILE
[COST,DUR] as 'CombData'
end-initializations
! Alternative: specify the range/worksheet
initializations to CSTR + DFILE
[COST,DUR] as 'grow;[Sheet1$L4:O4]'
end-initializations
end-model
|
|
| multiout6.mos |
(!*******************************************************
Mosel Example Problems
======================
file multiout6.mos
``````````````````
Output several data arrays into a single table.
- Using 'initializations to' with the csv IO driver -
(c) 2012 Fair Isaac Corporation
author: S. Heipcke, Dec. 2012
*******************************************************!)
model "Output multiple data columns (CSV)"
uses "mmsheet"
parameters
CSTR= 'mmsheet.csv:multiout.csv'
end-parameters
declarations
PRODUCTS: set of string
MACH: range
COST: dynamic array(PRODUCTS,MACH) of real
DUR: dynamic array(PRODUCTS,MACH) of integer
end-declarations
! Read data
initializations from "multiout.dat"
COST DUR
end-initializations
! **** Writing data to the spreadsheet multiout.csv ****
! Options:
! skiph - the range includes a header line that needs to be skipped
! grow - only the starting line of the range is specified
initializations to CSTR
[COST,DUR] as 'skiph;grow;[F2:I2]'
end-initializations
end-model
|
|
| multitab.mos |
(!*******************************************************
Mosel Example Problems
======================
file multitab.mos
`````````````````
Reading an array from several data tables.
Output parts of an array into several tables.
- Using 'initializations from' with odbc I/O driver -
(c) 2008 Fair Isaac Corporation
author: S. Heipcke, 2006, rev. May 2017
*******************************************************!)
model "Multiple data sources"
uses "mmodbc", "mmsystem"
declarations
CUST: set of integer
PERIOD: range
INCOME,INCOME1,INCOME2,INCOME3: dynamic array(CUST,PERIOD) of real
end-declarations
initializations from "multitab.dat"
INCOME as "COLDAT1"
INCOME as "COLDAT2"
INCOME as "COLDAT3"
end-initializations
writeln(INCOME)
setparam("SQLverbose", true)
! Different ways of reading the data from a spreadsheet or database
! CNCT:= 'mmodbc.odbc:multitab.xls'
CNCT:= 'mmodbc.odbc:multitab.mdb'
! CNCT:= 'mmodbc.odbc:multitab.sqlite'
! CNCT:= 'mmodbc.odbc:DSN=sqlite;DATABASE=multitab.sqlite'
! Method 1: Data in columns, with CUST index value included
initializations from CNCT
INCOME1 as 'COLDAT1'
INCOME1 as 'COLDAT2'
INCOME1 as 'COLDAT3'
end-initializations
writeln("1: ", INCOME1)
! Method 2: Data in columns, without CUST index value
procedure readcol(cust:integer, table:string)
declarations
TEMP: array(PERIOD) of real
end-declarations
initializations from CNCT
TEMP as table
end-initializations
forall(p in PERIOD) INCOME2(cust,p):=TEMP(p)
end-procedure
forall(c in CUST) readcol(c, "COLDAT"+c+"A")
writeln("2: ", INCOME2)
! Method 3: Data in rows, without CUST index value
procedure readrow(cust:integer, table:string)
declarations
TEMP: array(1..2,PERIOD) of real
end-declarations
initializations from CNCT
TEMP as 'noindex;'+table
end-initializations
forall(p in PERIOD) INCOME3(cust,p):=TEMP(2,p)
end-procedure
finalize(PERIOD) ! The index sets must be known+fixed
forall(c in CUST) readrow(c, "ROWDAT"+c)
writeln("3: ", INCOME3)
! ******** Output an array into several tables ********
forall(c in CUST)
initializations to "multitabout.dat"
evaluation of array(c1=c,p in PERIOD) INCOME1(c1,p) as "COLDAT"+c
end-initializations
! Create the tables for data output
fcopy('multitab.mdb','multitabout.mdb')
CNCTOUT:= 'multitabout.mdb'
! CNCTOUT:= 'multitabout.sqlite'
SQLconnect(CNCTOUT)
forall(c in CUST) do
SQLexecute("drop table COLDAT"+c)
SQLexecute("create table COLDAT"+c+" (CUST integer,PERIOD integer,INCOME float)")
SQLexecute("drop table COLDAT"+c+"A")
SQLexecute("create table COLDAT"+c+"A (PERIOD integer,INCOME float)")
end-do
SQLdisconnect
! Method 1: Data in columns, with CUST index value included
CNCTOUT:= 'mmodbc.odbc:'+CNCTOUT
forall(c in CUST) do
initializations to CNCTOUT
evaluation of array(c1=c,p in PERIOD) INCOME1(c1,p) as "COLDAT"+c
end-initializations
end-do
! Method 2: Data in columns, without CUST index value
forall(c in CUST) do
initializations to CNCTOUT
evaluation of array(p in PERIOD) INCOME1(c,p) as "COLDAT"+c+"A"
end-initializations
end-do
end-model
|
|
| multitab2.mos |
(!*******************************************************
Mosel Example Problems
======================
file multitab2.mos
``````````````````
Reading an array from several data tables.
Output parts of an array into several tables.
- Using SQL commands -
(c) 2008 Fair Isaac Corporation
author: S. Heipcke, 2002, rev. May 2017
*******************************************************!)
model "Multiple data sources (2)"
uses "mmodbc", "mmsystem"
declarations
CUST: set of integer
PERIOD: range
INCOME,INCOME1,INCOME2,INCOME3: dynamic array(CUST,PERIOD) of real
end-declarations
initializations from "multitab.dat"
INCOME as "COLDAT1"
INCOME as "COLDAT2"
INCOME as "COLDAT3"
end-initializations
writeln(INCOME)
setparam("SQLverbose", true)
! Different ways of reading the data from a spreadsheet or database
! SQLconnect('DSN=Excel Files;DBQ=C:/xpress/examples/mosel/WhitePapers/MoselODBC/multitab.xls')
! SQLconnect('multitab.xls')
! SQLconnect('DSN=MS Access Database;DBQ=C:/xpress/examples/mosel/WhitePapers/MoselODBC/multitab.mdb')
SQLconnect('multitab.mdb')
! SQLconnect('DSN=sqlite;DATABASE=multitab.sqlite')
! SQLconnect('multitab.sqlite')
! Method 1: Data in columns, with CUST index value included
SQLexecute("select CUST,PERIOD,INCOME from COLDAT1", INCOME1)
SQLexecute("select CUST,PERIOD,INCOME from COLDAT2", INCOME1)
SQLexecute("select CUST,PERIOD,INCOME from COLDAT3", INCOME1)
writeln("1: ", INCOME1)
! Method 2: Data in columns, without CUST index value
procedure readcol(cust:integer, table:string)
declarations
TEMP: array(PERIOD) of real
end-declarations
SQLexecute("select PERIOD,INCOME from "+table, TEMP)
forall(p in PERIOD) INCOME2(cust,p):=TEMP(p)
end-procedure
forall(c in CUST) readcol(c, "COLDAT"+c+"A")
writeln("2: ", INCOME2)
! Method 3: Data in rows, without CUST index value
procedure readrow(cust:integer, table:string)
declarations
TEMP: array(1..2,PERIOD) of real
end-declarations
SQLexecute("select * from "+table, TEMP)
forall(p in PERIOD) INCOME3(cust,p):=TEMP(2,p)
end-procedure
finalize(PERIOD) ! The index sets must be known+fixed
setparam("sqlndxcol",false) ! Data specified in dense format (no indices)
forall(c in CUST) readrow(c, "ROWDAT"+c)
setparam("sqlndxcol",true)
writeln("3: ", INCOME3)
! ******** Output an array into several tables ********
forall(c in CUST)
initializations to "multitabout.dat"
evaluation of array(c1=c,p in PERIOD) INCOME1(c1,p) as "COLDAT"+c
end-initializations
fcopy('multitab.mdb','multitabout.mdb')
SQLconnect('multitabout.mdb')
! SQLconnect('multitabout.sqlite')
! Method 1: Data in columns, with CUST index value included
forall(c in CUST) do
SQLexecute("drop table COLDAT"+c)
SQLexecute("create table COLDAT"+c+" (CUST integer,PERIOD integer,INCOME float)")
SQLexecute("insert into COLDAT"+c+"(CUST,PERIOD,INCOME) values (?,?,?)",
array(c1=c,p in PERIOD) INCOME1(c1,p))
(!or:
SQLexecute("insert into COLDAT"+c+"(CUST,PERIOD,INCOME) values ("+c+",?,?)", array(p in PERIOD) INCOME1(c,p))
!)
end-do
! Method 2: Data in columns, without CUST index value
forall(c in CUST) do
SQLexecute("drop table COLDAT"+c+"A")
SQLexecute("create table COLDAT"+c+"A (PERIOD integer,INCOME float)")
SQLexecute("insert into COLDAT"+c+"A(PERIOD,INCOME) values (?,?)",
array(p in PERIOD) INCOME1(c,p))
end-do
SQLdisconnect
end-model
|
|
| multitab3.mos |
(!*******************************************************
Mosel Example Problems
======================
file multitab3.mos
`````````````````
Reading an array from several data tables.
Output parts of an array into several tables.
- Using 'initializations from' with excel I/O driver -
(c) 2008 Fair Isaac Corporation
author: S. Heipcke, 2007, rev. May 2017
*******************************************************!)
model "Multiple data sources (3)"
uses "mmsheet", "mmsystem"
parameters
CNCT = 'mmsheet.excel:skiph;multitab.xls'
CNCTOUT= 'mmsheet.excel:multitabout.xls'
end-parameters
declarations
CUST: set of integer
PERIOD: range
INCOME1,INCOME2,INCOME3: dynamic array(CUST,PERIOD) of real
end-declarations
! Different ways of reading the data from an Excel spreadsheet
! (spreadsheet ranges include a header line -> use option 'skiph')
! Method 1: Data in columns, with CUST index value included
initializations from CNCT
INCOME1 as 'COLDAT1'
INCOME1 as 'COLDAT2'
INCOME1 as 'COLDAT3'
end-initializations
writeln("1: ", INCOME1)
! Method 2: Data in columns, without CUST index value
procedure readcol(cust:integer, table:string)
declarations
TEMP: array(PERIOD) of real
end-declarations
initializations from CNCT
TEMP as table
end-initializations
forall(p in PERIOD) INCOME2(cust,p):=TEMP(p)
end-procedure
forall(c in CUST) readcol(c, "COLDAT"+c+"A")
writeln("2: ", INCOME2)
! Method 3: Data in rows, without CUST index value
procedure readrow(cust:integer, table:string)
declarations
TEMP: array(1..2,PERIOD) of real
end-declarations
initializations from CNCT
TEMP as 'noindex;'+table
end-initializations
forall(p in PERIOD) INCOME3(cust,p):=TEMP(2,p)
end-procedure
finalize(PERIOD) ! The index sets must be known+fixed
forall(c in CUST) readrow(c, "ROWDAT"+c)
writeln("3: ", INCOME3)
! ******** Output an array into several tables ********
fcopy("multitab.xls","multitabout.xls")
fopen(CNCTOUT, F_OUTPUT)
forall(c in CUST)
initializations to CNCTOUT
! Output into separate tables within one sheet, CUST index value included
evaluation of array(c1=c,p in PERIOD) INCOME1(c1,p) as "grow;skiph;[Sheet2$R2C"+(c*4)+":R2C"+(c*4+2)+"]"
! Output without CUST index value
evaluation of array(p in PERIOD) INCOME1(c,p) as "grow;skiph;[Sheet2$R15C"+(c*3)+":R15C"+(c*3+1)+"]"
end-initializations
fclose(F_OUTPUT)
end-model
|
|
| multitab4.mos |
(!*******************************************************
Mosel Example Problems
======================
file multitab4.mos
``````````````````
Reading an array from several data tables.
Output parts of an array into several tables.
- Using 'initializations' and OCI statements -
(c) 2012 Fair Isaac Corporation
author: S. Heipcke, Dec. 2012, rev. May 2017
*******************************************************!)
model "Multiple data sources (OCI)"
uses "mmoci", "mmsystem"
parameters
DB="myname/mypassword@dbname" ! Login to Oracle database (not provided)
CNCT="mmoci.oci:debug;"+DB
end-parameters
declarations
CUST: set of integer
PERIOD: range
INCOME,INCOME1,INCOME2,INCOME3: dynamic array(CUST,PERIOD) of real
end-declarations
! **** Using SQL statements ****
setparam("OCIdebug",true)
OCIlogon(DB)
writeln("Connection number: ",getparam("OCIconnection"))
! Method 1: Data in columns, with CUST index value included
OCIexecute("select CUST,PERIOD,INCOME from COLDAT1", INCOME1)
OCIexecute("select CUST,PERIOD,INCOME from COLDAT2", INCOME1)
OCIexecute("select CUST,PERIOD,INCOME from COLDAT3", INCOME1)
writeln("1: ", INCOME1)
! Method 2: Data in columns, without CUST index value
procedure readcol(cust:integer, table:string)
declarations
TEMP: array(PERIOD) of real
end-declarations
OCIexecute("select PERIOD,INCOME from "+table, TEMP)
forall(p in PERIOD) INCOME2(cust,p):=TEMP(p)
end-procedure
forall(c in CUST) readcol(c, "COLDAT"+c+"A")
writeln("2: ", INCOME2)
! Method 3: Data in rows, without CUST index value
procedure readrow(cust:integer, table:string)
declarations
TEMP: array(1..2,PERIOD) of real
end-declarations
OCIexecute("select * from "+table, TEMP)
forall(p in PERIOD) INCOME3(cust,p):=TEMP(2,p)
end-procedure
finalize(PERIOD) ! The index sets must be known+fixed
setparam("ocindxcol",false) ! Data specified in dense format (no indices)
forall(c in CUST) readrow(c, "ROWDAT"+c)
setparam("ocindxcol",true)
writeln("3: ", INCOME3)
! ******** Output an array into several tables ********
(!
! Method 1: Data in columns, with CUST index value included
forall(i in 1..3) do
OCIexecute("drop table COLDATOUT" + i)
OCIexecute("create table COLDATOUT"+i+" (CUST integer, PERIOD integer, INCOME float)")
OCIexecute("insert into COLDATOUT"+c+" (CUST,PERIOD,INCOME) values (:1, :2, :3)",
array(c1=c,p in PERIOD) INCOME1(c1,p))
end-do
! Method 2: Data in columns, without CUST index value
forall(i in 1..3) do
OCIexecute("drop table COLDATOUT" + i)
OCIexecute("create table COLDATOUT"+i+" (CUST integer, PERIOD integer, INCOME float)")
OCIexecute("drop table COLDATOUT" + i + "A")
OCIexecute("create table COLDATOUT"+i+"A (CUST integer, PERIOD integer, INCOME float)")
OCIexecute("insert into COLDATOUT"+c+"A (CUST,PERIOD,INCOME) values (:1, :2)",
array(p in PERIOD) INCOME1(c,p))
end-do
!)
OCIlogoff
! **** Using 'initializations from' ****
! Method 1: Data in columns, with CUST index value included
initializations from CNCT
INCOME1 as 'COLDAT1'
INCOME1 as 'COLDAT2'
INCOME1 as 'COLDAT3'
end-initializations
writeln("1: ", INCOME1)
! Method 2: Data in columns, without CUST index value
procedure readcol2(cust:integer, table:string)
declarations
TEMP: array(PERIOD) of real
end-declarations
initializations from CNCT
TEMP as table
end-initializations
forall(p in PERIOD) INCOME2(cust,p):=TEMP(p)
end-procedure
forall(c in CUST) readcol2(c, "COLDAT"+c+"A")
writeln("2: ", INCOME2)
! Method 3: Data in rows, without CUST index value
procedure readrow2(cust:integer, table:string)
declarations
TEMP: array(1..2,PERIOD) of real
end-declarations
initializations from CNCT
TEMP as 'noindex;'+table
end-initializations
forall(p in PERIOD) INCOME3(cust,p):=TEMP(2,p)
end-procedure
finalize(PERIOD) ! The index sets must be known+fixed
forall(c in CUST) readrow2(c, "ROWDAT"+c)
writeln("3: ", INCOME3)
end-model
**************************************************
! Creation of data tables in an Oracle database:
declarations
tsucc: array ({false,true}) of string
INCOME1T,INCOME2T,INCOME3T: dynamic array(CUST,PERIOD) of real
end-declarations
tsucc(false):="failed"; tsucc(true):="succeeded"
initializations from "multitab.dat"
INCOME1T as "COLDAT1"
INCOME2T as "COLDAT2"
INCOME3T as "COLDAT3"
end-initializations
initializations from "multitab.dat"
INCOME as "COLDAT1"
INCOME as "COLDAT2"
INCOME as "COLDAT3"
end-initializations
setparam("OCIdebug",true)
OCIlogon(DB)
writeln("Connection number: ",getparam("OCIconnection"))
forall(i in 1..3) OCIexecute("drop table COLDAT" + i)
OCIexecute("create table COLDAT1 (CUST integer, PERIOD integer, INCOME float)")
writeln(" - Create COLDAT1 (",tsucc(getparam("OCIsuccess")),")")
OCIexecute("insert into COLDAT1 (CUST, PERIOD, INCOME) values (:1, :2, :3)", INCOME1T)
writeln(" - Insert values into COLDAT1 (",tsucc(getparam("OCIsuccess")),",",
getparam("OCIrowcnt"),'/',getparam("OCIrowxfr")," rows)")
OCIexecute("create table COLDAT2 (CUST integer, PERIOD integer, INCOME float)")
writeln(" - Create COLDAT2 (",tsucc(getparam("OCIsuccess")),")")
OCIexecute("insert into COLDAT2 (CUST, PERIOD, INCOME) values (:1, :2, :3)", INCOME2T)
writeln(" - Insert values into COLDAT2 (",tsucc(getparam("OCIsuccess")),",",
getparam("OCIrowcnt"),'/',getparam("OCIrowxfr")," rows)")
OCIexecute("create table COLDAT3 (CUST integer, PERIOD integer, INCOME float)")
writeln(" - Create COLDAT3 (",tsucc(getparam("OCIsuccess")),")")
OCIexecute("insert into COLDAT3 (CUST, PERIOD, INCOME) values (:1, :2, :3)", INCOME3T)
writeln(" - Insert values into COLDAT3 (",tsucc(getparam("OCIsuccess")),",",
getparam("OCIrowcnt"),'/',getparam("OCIrowxfr")," rows)")
forall(i in 1..3) do
OCIexecute("drop table COLDAT" + i + "A")
OCIexecute("create table COLDAT" + i + "A (PERIOD integer, INCOME float)")
writeln(" - Create COLDAT" + i + "A (",tsucc(getparam("OCIsuccess")),")")
OCIexecute("insert into COLDAT" + i + "A (PERIOD, INCOME) values (:1, :2)",
array(p in PERIOD) INCOME(i,p))
writeln(" - Insert values into COLDAT" + i +
" (",tsucc(getparam("OCIsuccess")),",",
getparam("OCIrowcnt"),'/',getparam("OCIrowxfr")," rows)")
end-do
procedure writerow(cust:integer, table:string)
declarations
tname: text
tlist,tlist2: list of real
end-declarations
OCIexecute("drop table " + table)
OCIexecute("create table " + table + " (F1 float, F2 float, F3 float, F4 float, F5 float)")
writeln(" - Create " + table + "(",tsucc(getparam("OCIsuccess")),")")
tname:= "F1"
forall(p in PERIOD | p>1) tname += ", F" + p
vname:= ":1"
forall(p in PERIOD | p>1) vname += ", :" + p
forall(p in PERIOD ) tlist+=[real(p)]
OCIexecute("insert into " + table + "(" + tname + ") values (" + vname +")",
tlist)
writeln(" - Insert values into " + table +
" (",tsucc(getparam("OCIsuccess")),",",
getparam("OCIrowcnt"),'/',getparam("OCIrowxfr")," rows)")
forall(p in PERIOD ) tlist2+=[INCOME(cust,p)]
OCIexecute("insert into " + table + "(" + tname + ") values (" + vname +")",
tlist2)
writeln(" - Insert values into " + table +
" (",tsucc(getparam("OCIsuccess")),",",
getparam("OCIrowcnt"),'/',getparam("OCIrowxfr")," rows)")
end-procedure
setparam("ocindxcol",false) ! Data specified in dense format (no indices)
forall(c in CUST) writerow(c, "ROWDAT" + c)
setparam("ocindxcol",true)
OCIlogoff
|
|
| multitab5.mos |
(!*******************************************************
Mosel Example Problems
======================
file multitab5.mos
`````````````````
Reading an array from several data tables.
Output parts of an array into several tables.
- Using 'initializations from' with the xls I/O driver -
(c) 2012 Fair Isaac Corporation
author: S. Heipcke, Dec. 2012, rev. May 2017
*******************************************************!)
model "Multiple data sources (generic spreadsheet)"
uses "mmsheet"
parameters
! CNCT= 'mmsheet.xls:skiph;multitab.xls'
CNCT= 'mmsheet.xlsx:skiph;multitab.xlsx'
CNCTOUT= 'mmsheet.xlsx:multitabout.xlsx'
end-parameters
declarations
CUST: set of integer
PERIOD: range
INCOME1,INCOME2,INCOME3: dynamic array(CUST,PERIOD) of real
end-declarations
! Different ways of reading the data from a spreadsheet
! (spreadsheet ranges include a header line -> use option 'skiph')
! Method 1: Data in columns, with CUST index value included
initializations from CNCT
INCOME1 as 'COLDAT1'
INCOME1 as 'COLDAT2'
INCOME1 as 'COLDAT3'
end-initializations
writeln("1: ", INCOME1)
! Method 2: Data in columns, without CUST index value
procedure readcol(cust:integer, table:string)
declarations
TEMP: array(PERIOD) of real
end-declarations
initializations from CNCT
TEMP as table
end-initializations
forall(p in PERIOD) INCOME2(cust,p):=TEMP(p)
end-procedure
forall(c in CUST) readcol(c, "COLDAT"+c+"A")
writeln("2: ", INCOME2)
! Method 3: Data in rows, without CUST index value
procedure readrow(cust:integer, table:string)
declarations
TEMP: array(1..2,PERIOD) of real
end-declarations
initializations from CNCT
TEMP as 'noindex;'+table
end-initializations
forall(p in PERIOD) INCOME3(cust,p):=TEMP(2,p)
end-procedure
finalize(PERIOD) ! The index sets must be known+fixed
forall(c in CUST) readrow(c, "ROWDAT"+c)
writeln("3: ", INCOME3)
! ******** Output an array into several tables ********
fopen(CNCTOUT, F_OUTPUT)
forall(c in CUST)
initializations to CNCTOUT
! Output into separate sheets, CUST index value included
evaluation of array(c1=c,p in PERIOD) INCOME1(c1,p) as "grow;skiph+;[Sheet"+c+"$A2:C2](CUST,PERIOD,INCOME)"
! Output into separate tables within one sheet, CUST index value included
evaluation of array(c1=c,p in PERIOD) INCOME1(c1,p) as "grow;skiph+;[Sheet1$R2C"+(10+c*4)+":R2C"+(10+c*4+2)+"](CUST,PERIOD,INCOME)"
! Output without CUST index value
evaluation of array(p in PERIOD) INCOME1(c,p) as "grow;skiph+;[Sheet1$R15C"+(c*3)+":R15C"+(c*3+1)+"](PERIOD,INCOME)"
end-initializations
fclose(F_OUTPUT)
end-model
|
|
| multitab6.mos |
(!*******************************************************
Mosel Example Problems
======================
file multitab6.mos
`````````````````
Reading an array from several data tables.
Output parts of an array into several tables.
- Using 'initializations from' with the csv I/O driver -
(c) 2012 Fair Isaac Corporation
author: S. Heipcke, Dec. 2012, rev. May 2017
*******************************************************!)
model "Multiple data sources (CSV)"
uses "mmsheet", "mmsystem"
parameters
CNCT= 'mmsheet.csv:multitab.csv'
CNCTOUT= 'mmsheet.csv:multitabout.csv'
end-parameters
declarations
CUST: set of integer
PERIOD: range
INCOME1,INCOME2,INCOME3: dynamic array(CUST,PERIOD) of real
end-declarations
! **** Different ways of reading the data from a CSV spreadsheet
! Method 1: Data in columns, with CUST index value included
initializations from CNCT
INCOME1 as '[A3:C7]'
INCOME1 as '[E3:G7]'
INCOME1 as '[I3:K7]'
end-initializations
writeln("1: ", INCOME1)
! Method 2: Data in columns, without CUST index value
procedure readcol(cust:integer, table:string)
declarations
TEMP: array(PERIOD) of real
end-declarations
initializations from CNCT
TEMP as table
end-initializations
forall(p in PERIOD) INCOME2(cust,p):=TEMP(p)
end-procedure
forall(c in CUST)
readcol(c, "[R11C" + (1+(c-1)*3) + ":R15C" + (2+(c-1)*3) + "]")
writeln("2: ", INCOME2)
! Method 3: Data in rows, without CUST index value
procedure readrow(cust:integer, table:string)
declarations
TEMP: array(1..2,PERIOD) of real
end-declarations
initializations from CNCT
TEMP as 'noindex;'+table
end-initializations
forall(p in PERIOD) INCOME3(cust,p):=TEMP(2,p)
end-procedure
finalize(PERIOD) ! The index sets must be known+fixed
forall(c in CUST) readrow(c, "[B" + (21+(c-1)*5) + ":F" + (22+(c-1)*5) + "]")
writeln("3: ", INCOME3)
! ******** Output an array into several tables ********
fopen(CNCTOUT, F_OUTPUT)
forall(c in CUST)
initializations to CNCTOUT
! Method 1: CUST index value included
evaluation of array(c1=c,p in PERIOD) INCOME1(c1,p) as "grow;skiph+;[R2C"+(c*4)+":R2C"+(c*4+2)+"](CUST,PERIOD,INCOME)"
! Method 2: without CUST index value
evaluation of array(p in PERIOD) INCOME1(c,p) as "grow;skiph+;[R15C"+(c*3)+":R15C"+(c*3+1)+"](PERIOD,INCOME)"
end-initializations
fclose(F_OUTPUT)
end-model
|
|
