(!******************************************************
Mosel Example Problems
======================
file unioninout.mos
```````````````````
Reading/writing unions from/t databases via ODBC.
- Using 'initializations from' with odbc IO driver -
(c) 2021 Fair Isaac Corporation
author: S. Heipcke, Apr. 2021
*******************************************************!)
model "Union handling (ODBC)"
uses 'mmodbc', 'mmsystem'
parameters
! Use mysql database `uniondata' (not provided)
! CNCTIO = 'mmodbc.odbc:debug;DSN=mysql;DB=uniondata'
! Use SQLite database 'uniondata.sqlite' via ODBC
! CNCTIO = 'mmodbc.odbc:debug;DSN=sqlite;DATABASE=uniondata.sqlite'
! Use SQLite database 'uniondata.sqlite' directly
CNCTIO = "mmodbc.odbc:debug;uniondata.sqlite"
end-parameters
declarations
L,L2: list of any
L3: list of text or real
LS: list of text
end-declarations
! Date and time formats
(! Access and Excel:
setparam("timefmt", "%y-%0m-%0d %0H:%0M:%0S")
setparam("datefmt", "%y-%0m-%0d")
setparam("datetimefmt", "%y-%0m-%0d %0H:%0M:%0S")
!)
(! mysql:
setparam("timefmt", "%0H:%0M:%0S")
setparam("datefmt", "%y-%0m-%0d")
setparam("datetimefmt", "%y-%0m-%0d %0H:%0M:%0S")
!)
(! SQLite:
setparam("timefmt", "%0H:%0M:%0S")
setparam("datefmt", "%y-%0m-%0d")
setparam("datetimefmt", "%y-%0m-%0d %0H:%0M:%0S")
!)
setparam("SQLverbose",true)
! Reading data of different types from a database
initializations from CNCTIO
L as "UnionTab"
L2 as "UnionLst"
L3 as "UnionTab"
end-initializations
write("L orig: ")
forall(i in L) write (i,": ", i.typeid, "; ")
writeln
! Date and time types are read in textual form
L(5).date:=date(text(L(5)))
L(6).time:=time(text(L(6)))
write("L new: ")
forall(i in L) write (i,": ", i.typeid, "; ")
writeln
! Reading into a list defined with a restricted set of types
write("L3: ")
forall(i in L3) write (i,": ", i.typeid, "; ")
writeln
! Textual database types are always read as string
write("L2: ")
forall(i in L2) write (i,": ", i.typeid, "; ")
writeln
(! Delete existing contents of result tables:
SQLconnect(CNCT)
SQLexecute("delete from UnionOut")
SQLexecute("delete from Union2Out")
SQLdisconnect
!)
LS:=sum(i in L) [text(i)]
initializations to CNCTIO
! Writing data of type 'any' to a database table with various different types
L as "UnionOut(IVal,RVal,BVal,SVal,DVal,TVal)"
! Writing data of type 'any' into textual fields of a database
L as "Union2Out"
! Writing data of a union type to a database
L3 as "UnionOut(IVal,RVal,BVal,SVal,DVal,TVal)"
! Writing text-format data to a database table with various different types
LS as "UnionOut(IVal,RVal,BVal,SVal,DVal,TVal)"
end-initializations
writeln("Output to DB terminated.")
end-model
|
(!******************************************************
Mosel Example Problems
======================
file unioninout2.mos
````````````````````
Reading/writing unions from/to databases via ODBC.
- Using SQL commands -
(c) 2021 Fair Isaac Corporation
author: S. Heipcke, Apr. 2021
*******************************************************!)
model "Union handling (SQL)"
uses 'mmodbc', 'mmsystem'
parameters
! Use mysql database `uniondata' (not provided)
! CNCT = 'DSN=mysql;DB=uniondata'
! Use SQLite database 'uniondata.sqlite' via ODBC
! CNCT = 'DSN=sqlite;DATABASE=uniondata.sqlite'
! Use SQLite database 'uniondata.sqlite' directly
CNCT = "uniondata.sqlite"
end-parameters
procedure gendb
declarations
L: list of any
tsucc: array ({false,true}) of string
end-declarations
tsucc(false):="failed"; tsucc(true):="succeeded"
setparam("SQLverbose",true)
(! mysql date and time formats:
setparam("timefmt", "%0H:%0M:%0S")
setparam("datefmt", "%y-%0m-%0d")
setparam("datetimefmt", "%y-%0m-%0d %0H:%0M:%0S")
!)
(! SQLite date and time formats:
setparam("timefmt", "%0H:%0M:%0S")
setparam("datefmt", "%y-%0m-%0d")
setparam("datetimefmt", "%y-%0m-%0d %0H:%0M:%0S")
!)
fdelete('uniondata.sqlite')
SQLconnect(CNCT)
SQLexecute("drop table UnionTab")
SQLexecute("drop table UnionLst")
SQLexecute("drop table UnionOut")
SQLexecute("drop table Union2Out")
SQLexecute("create table UnionTab (IVal integer, RVal real, BVal boolean, SVal varchar(20), DVal date, TVal timestamp(3))") !sqlite
! SQLexecute("create table UnionTab (IVal integer, RVal real, BVal boolean, SVal varchar(20), DVal date, TVal time(3))") !mysql
writeln(" - Create UnionTab (",tsucc(getparam("SQLsuccess")),")")
SQLexecute("insert into UnionTab (IVal,RVal,SVal,BVal,TVal,DVal) values(?1,?2,?3,?4,?5,?6)", [ 5, 1.75, "some text", true, '11:25:30', '2021-03-20' ])
writeln(" - Insert values into UnionTab (",tsucc(getparam("SQLsuccess")),",",
getparam("SQLrowcnt"),'/',getparam("SQLrowxfr")," rows)")
SQLexecute("create table UnionLst (UValues varchar(20))")
writeln(" - Create UnionLst (",tsucc(getparam("SQLsuccess")),")")
SQLexecute("insert into UnionLst (UValues) values (?1)", [string(5), string(1.75), string(true), "some text", '2021-03-20', '11:25:30'])
writeln(" - Insert values into UnionLst (",tsucc(getparam("SQLsuccess")),",",
getparam("SQLrowcnt"),'/',getparam("SQLrowxfr")," rows)")
SQLexecute("create table UnionOut (IVal integer, RVal real, BVal boolean, SVal varchar(20), DVal date, TVal timestamp(3))") !sqlite
! SQLexecute("create table UnionOut (IVal integer, RVal real, BVal boolean, SVal varchar(20), DVal date, TVal time(3))") !mysql
writeln(" - CreateUnionOut (",tsucc(getparam("SQLsuccess")),")")
SQLexecute("create table Union2Out (UnionValues varchar(20))")
writeln(" - Create Union2Out (",tsucc(getparam("SQLsuccess")),")")
SQLdisconnect
end-procedure
! gendb
declarations
L,L2: list of any
L3: list of text or real
LS: list of text
end-declarations
setparam("SQLverbose",true)
setparam("SQLdebug",true)
SQLconnect(CNCT)
! Reading data of different types from a database
SQLexecute("select * from UnionTab", L)
write("L orig: ")
forall(i in L) write (i,": ", i.typeid, "; ")
writeln
! Date and time types are read in textual form
L(5).date:=date(text(L(5)))
L(6).time:=time(text(L(6)))
write("L new: ")
forall(i in L) write (i,": ", i.typeid, "; ")
writeln
! Reading into a list defined with a restricted set of types
SQLexecute("select * from UnionTab", L3)
write("L3: ")
forall(i in L3) write (i,": ", i.typeid, "; ")
writeln
! Textual database types are always read as string
SQLexecute("select * from UnionLst", L2)
write("L2: ")
forall(i in L2) write (i,": ", i.typeid, "; ")
writeln
! Writing data of type 'any' to a database
SQLexecute("delete from UnionOut")
SQLexecute("insert into UnionOut (IVal,RVal,BVal,SVal,DVal,TVal) values(?1,?2,?3,?4,?5,?6)", L)
writeln(" - writing L into UnionOut succeeded (",getparam("SQLsuccess"),")")
! Writing data of type 'any' into textual fields of a database
SQLexecute("delete from Union2Out")
SQLexecute("insert into Union2Out (UnionValues) values(?1)", L)
writeln(" - writing L into Union2Out succeeded (",getparam("SQLsuccess"),")")
! Writing data of a union type to a database
SQLexecute("insert into UnionOut (IVal,RVal,BVal,SVal,DVal,TVal) values(?1,?2,?3,?4,?5,?6)", L3)
writeln(" - writing L3 into UnionOut succeeded (",getparam("SQLsuccess"),")")
! Writing text-format data to a database table with various different types
LS:=sum(i in L) [text(i)]
SQLexecute("insert into UnionOut (IVal,RVal,BVal,SVal,DVal,TVal) values(?1,?2,?3,?4,?5,?6)", LS)
writeln(" - writing LS into UnionOut succeeded (",getparam("SQLsuccess"),")")
SQLdisconnect
end-model
|
(!******************************************************
Mosel Example Problems
======================
file unioninout3.mos
````````````````````
Reading/writing unions from/to
spreadsheets or databases.
- Using 'initializations from' with the excel driver -
(c) 2021 Fair Isaac Corporation
author: S. Heipcke, Apr. 2021
*******************************************************!)
model "Union handling (Excel)"
uses 'mmsheet','mmsystem'
parameters
CSTR= 'mmsheet.excel:uniondata.xlsx'
CSTR_OUT= 'mmsheet.excel:uniondataout.xlsx'
end-parameters
function convertexceltime(r: real): time
ra:=abs(r)
if ra<1 then
returned:=time(round(ra*24*60*60*1000))
else
returned:=time(round((ra-floor(ra))*24*60*60*1000))
end-if
end-function
declarations
L,L2,L3: list of any
LU: list of text or real
LU2: list of text or real or boolean
end-declarations
! Reading data of different types from an Excel file
initializations from CSTR
L as "skiph;[A:A]"
L2 as "skiph;[C:C]"
L3 as "skiph;[E:E]"
LU as "skiph;[C:C]" ! This would fail to read from [A:A] due to missing boolean type
LU2 as "skiph;[A:A]"
end-initializations
write("L orig: ")
forall(i in L) write (i,": ", i.typeid, "; ")
writeln
! Date types are read in textual form, time types have a numerical value
L(5).date:=date(L(5).string)
L(6).time:=convertexceltime(L(6).real)
write("L new: ")
forall(i in L) write (i,": ", i.typeid, "; ")
writeln
! List defined with a restricted set of types
write("LU: ")
forall(i in LU) write (i,": ", i.typeid, "; ")
writeln
write("LU2: ")
forall(i in LU2) write (i,": ", i.typeid, "; ")
writeln
! Contents formated as 'text'
write("L2: ")
forall(i in L2) write (i,": ", i.typeid, "; ")
writeln
! Contents formated as 'text' enforced via preceding apostrophe
write("L3: ")
forall(i in L3) write (i,": ", i.typeid, "; ")
writeln
! The 'msheet.excel' driver does not create files
fcopy("uniondata.xlsx","uniondataout.xlsx")
! Writing data of type 'any' to an Excel file
initializations to CSTR_OUT
L as "[Output$A:A]"
LU as "[Output$R1C3:R1C"+(2+LU.size)+"]"
end-initializations
end-model
|
(!******************************************************
Mosel Example Problems
======================
file unioninout4.mos
````````````````````
Reading/writing unions from/to an Oracle database.
- Using 'initializations' and OCI statements -
(c) 2021 Fair Isaac Corporation
author: S. Heipcke, Apr. 2021
*******************************************************!)
model "Union handling (OCI)"
uses "mmoci", "mmsystem"
parameters
DB="myname/mypassword@dbname" ! Login to Oracle database (not provided)
end-parameters
procedure gendb
declarations
L: list of any
tsucc: array ({false,true}) of string
end-declarations
tsucc(false):="failed"; tsucc(true):="succeeded"
setparam("OCIverbose",true)
! Oracle date and time formats:
setparam("timefmt", "%0d-%N-%0Y %0h.%0M.%0S.%0s %P")
setparam("datefmt", "%0d-%N-%0Y")
setparam("datetimefmt", "%0d-%N-%0Y %0h.%0M.%0S.%0s %P")
OCIlogon(DB)
(!
OCIexecute("drop table UnionTab")
OCIexecute("drop table UnionLst")
OCIexecute("drop table UnionOut")
OCIexecute("drop table Union2Out")
!)
OCIexecute("create table UnionTab (IVal integer, RVal float, BVal number(1,0), SVal varchar(20), DVal date, TVal timestamp(3))")
writeln(" - Create UnionTab (",tsucc(getparam("OCIsuccess")),")")
OCIexecute("insert into UnionTab (IVal,RVal,SVal,BVal,TVal,DVal) values(:1,:2,:3,:4,:5,:6)", [ 5, 1.75, "some text", true, '1-Jan-1 11:25:30am', '20-Mar-2021' ])
writeln(" - Insert values into UnionTab (",tsucc(getparam("OCIsuccess")),",",
getparam("OCIrowcnt"),'/',getparam("OCIrowxfr")," rows)")
OCIexecute("create table UnionLst (UValues varchar(20))")
writeln(" - Create UnionLst (",tsucc(getparam("OCIsuccess")),")")
OCIexecute("insert into UnionLst (UValues) values (:1)", [string(5), string(1.75), string(true), "some text", '20-Mar-2021', '1-Jan-0 11:25:30am'])
writeln(" - Insert values into UnionLst (",tsucc(getparam("OCIsuccess")),",",
getparam("OCIrowcnt"),'/',getparam("OCIrowxfr")," rows)")
OCIexecute("create table UnionOut (IVal integer, RVal float, BVal number(1,0), SVal varchar(20), DVal date, TVal timestamp(3))")
writeln(" - CreateUnionOut (",tsucc(getparam("OCIsuccess")),")")
OCIexecute("create table Union2Out (UnionValues varchar(25))")
writeln(" - Create Union2Out (",tsucc(getparam("OCIsuccess")),")")
OCIlogoff
end-procedure
gendb
declarations
L,L2: list of any
L3: list of text or real
LS: list of text
end-declarations
setparam("OCIverbose",true)
setparam("OCIdebug",true)
OCIlogon(DB)
! Reading data of different types from a database
OCIexecute("select * from UnionTab", L)
write("L orig: ")
forall(i in L) write (i,": ", i.typeid, "; ")
writeln
! Date and time types are read in textual form
L(5).date:=date(text(L(5)))
L(6).datetime:=datetime(text(L(6)))
write("L new: ")
forall(i in L) write (i,": ", i.typeid, "; ")
writeln
! Reading into a list defined with a restricted set of types
OCIexecute("select * from UnionTab", L3)
write("L3: ")
forall(i in L3) write (i,": ", i.typeid, "; ")
writeln
! Textual database types are always read as string
OCIexecute("select * from UnionLst", L2)
write("L2: ")
forall(i in L2) write (i,": ", i.typeid, "; ")
writeln
! Writing data of type 'any' to a database
OCIexecute("delete from UnionOut")
OCIexecute("insert into UnionOut (IVal,RVal,BVal,SVal,DVal,TVal) values(:1,:2,:3,:4,:5,:6)", L)
writeln(" - writing L into UnionOut succeeded (",getparam("OCIsuccess"),")")
! Writing data of type 'any' into textual fields of a database
OCIexecute("delete from Union2Out")
OCIexecute("insert into Union2Out (UnionValues) values(:1)", L)
writeln(" - writing L into Union2Out succeeded (",getparam("OCIsuccess"),")")
! Writing data of a union type to a database
OCIexecute("insert into UnionOut (IVal,RVal,BVal,SVal,DVal,TVal) values(:1,:2,:3,:4,:5,:6)", L3)
writeln(" - writing L3 into UnionOut succeeded (",getparam("OCIsuccess"),")")
! Writing text-format data to a database table with various different types
LS:=sum(i in L) [text(i)]
OCIexecute("insert into UnionOut (IVal,RVal,BVal,SVal,DVal,TVal) values(:1,:2,:3,:4,:5,:6)", LS)
writeln(" - writing LS into UnionOut succeeded (",getparam("OCIsuccess"),")")
OCIlogoff
end-model
|
(!******************************************************
Mosel Example Problems
======================
file unioninout5.mos
````````````````````
Reading/writing unions from/to spreadsheets.
- Using 'initializations from' with the xls/xlsx driver -
(c) 2021 Fair Isaac Corporation
author: S. Heipcke, Apr. 2021
*******************************************************!)
model "Union handling (generic spreadsheet)"
uses "mmsheet", "mmsystem"
parameters
CSTR_IN= 'mmsheet.xls:uniondata.xls'
! CSTR_IN= 'mmsheet.xlsx:uniondata.xlsx'
CSTR_OUT= 'mmsheet.xls:uniondataout.xls'
! CSTR_OUT= 'mmsheet.xlsx:uniondataout.xlsx'
end-parameters
declarations
L,L2,L3: list of any
LU: list of text or real
LU2: list of text or real or boolean
end-declarations
! Date and time formats (make sure to use the default format of mmsystem,
! not the format used for display in the spreadsheet)
setparam("timefmt", "%0H:%0M:%0S")
setparam("datefmt", "%.y-%0m-%0d")
setparam("datetimefmt", "%.y-%0m-%0dT%0H:%0M:%0S")
! Reading data of different types from a spreadsheet file
initializations from CSTR_IN
L as "skiph;[A:A]"
L2 as "skiph;[C:C]"
L3 as "skiph;[E:E]"
LU as "skiph;[C:C]" ! This would fail to read from [A:A] due to missing boolean type
LU2 as "skiph;[A:A]"
end-initializations
write("L orig: ")
forall(i in L) write (i,": ", i.typeid, "; ")
writeln
! Date and time types are read in textual form
L(5).date:=date(L(5).string)
L(6).time:=time(L(6).string)
write("L new: ")
forall(i in L) write (i,": ", i.typeid, "; ")
writeln
! List defined with a restricted set of types
write("LU: ")
forall(i in LU) write (i,": ", i.typeid, "; ")
writeln
write("LU2: ")
forall(i in LU2) write (i,": ", i.typeid, "; ")
writeln
! Contents formated as 'text'
write("L2: ")
forall(i in L2) write (i,": ", i.typeid, "; ")
writeln
! Contents formated as 'text' enforced via preceding apostrophe
write("L3: ")
forall(i in L3) write (i,": ", i.typeid, "; ")
writeln
! Writing data of type 'any' to a spreadsheet file
initializations to CSTR_OUT
L as "[]"
LU as "[R1C3:R1C"+(2+LU.size)+"]"
end-initializations
end-model
|
