(!******************************************************
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, rev. Aug. 2023
*******************************************************!)
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
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)
if not getparam("SQLsuccess"): setioerr("Database connection failed")
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)
if not getparam("SQLsuccess"): setioerr("Database connection failed")
! 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, rev. Aug. 2023
*******************************************************!)
model "Union handling (OCI)"
uses "mmoci", "mmsystem"
parameters
DB="myname/mypassword@dbname" ! Login to Oracle database (not provided)
end-parameters
procedure gendb
declarations
tsucc: array ({false,true}) of string
end-declarations
tsucc(false):="failed"; tsucc(true):="succeeded"
setparam("OCIverbose",true)
if not getparam("SQLsuccess"): setioerr("Database connection failed")
! 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)
if not getparam("OCIsuccess"): setioerr("Database connection failed")
(!
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)
if not getparam("OCIsuccess"): setioerr("Database connection failed")
! 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, rev. Feb. 2024
*******************************************************!)
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 "[A:A]"
LU as "[R1C3:R1C"+(2+LU.size)+"]"
end-initializations
end-model
|
