(!******************************************************
Mosel Example Problems
======================
file datesinout.mos
```````````````````
Reading/writing dates from/to
spreadsheets or databases via ODBC.
- Using 'initializations from' with odbc IO driver -
(c) 2008 Fair Isaac Corporation
author: S. Heipcke, Nov. 2007, rev. Aug. 2023
*******************************************************!)
model "Dates and times (ODBC)"
uses "mmsystem", "mmodbc"
parameters
! Use Excel spreadsheet `datetime.xls'
! CNCTIO = "mmodbc.odbc:datetime.xls"
! Use Access database `datetime.mdb'
CNCTIO = "mmodbc.odbc:debug;datetime.mdb"
! Use mysql database `datetime' (not provided)
! CNCTIO = "mmodbc.odbc:debug;DSN=mysql;DB=datetime"
! Use Oracle database `datetime' (not provided)
! CNCTIO = "mmoci.oci:debug;myname/mypassword@datetime"
! Use SQLite database 'datetime.sqlite' via ODBC
! CNCTIO = "mmodbc.odbc:debug;DSN=sqlite;DATABASE=datetime.sqlite"
! Use SQLite database 'datetime.sqlite' directly
! CNCTIO = "mmodbc.odbc:debug;DRIVER=mmsqlite;DB=datetime.sqlite;READONLY=false"
! CNCTIO = "mmodbc.odbc:debug;datetime.sqlite"
end-parameters
declarations
T: time
D: date
DT: datetime
Dates: array(1..5) of date
DList: list of date
end-declarations
! Read in dates / time (make sure to select the format used by the
! spreadsheet/database)
! 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")
!)
(! Oracle:
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")
!)
(! SQLite:
setparam("timefmt", "%0H:%0M:%0S")
setparam("datefmt", "%y-%0m-%0d")
setparam("datetimefmt", "%y-%0m-%0d %0H:%0M:%0S")
!)
initializations from CNCTIO
T as "Time1"
D as "Date1"
DT as "DateTime1"
Dates as "noindex;Dates"
DList as "Dates"
end-initializations
setparam("timefmt", "%h:%0M %p")
writeln(D, ", ", T)
writeln(DT)
writeln(Dates)
writeln(DList)
! Read date / time from strings
setparam("timefmt", "%Hh%0Mm")
setparam("datefmt", "%dm%0my%0y")
setparam("datetimefmt", "%dm%0my%0y, %Hh%0Mm")
initializations from CNCTIO
T as "Time2"
D as "Date2"
DT as "`DateTime2`" ! 'DateTime2' is a reserved word for MS Access
! DT as "DateTime2" ! Standard form for other databases
end-initializations
writeln(D, ", ", T)
writeln(DT)
! Use Mosel's default format
setparam("timefmt", "")
setparam("datefmt", "")
setparam("datetimefmt", "")
writeln(D, ", ", T)
writeln(DT)
! The following assumes that the database output fields have type string
initializations to CNCTIO
T as "TimeOut"
D as "DateOut"
DT as "DateTimeOut"
end-initializations
end-model
|
(!******************************************************
Mosel Example Problems
======================
file datesinout2.mos
````````````````````
Reading/writing dates from/to
spreadsheets or databases via ODBC.
- Using SQL commands -
(c) 2008 Fair Isaac Corporation
author: S. Heipcke, Nov. 2007, rev. Aug. 2023
*******************************************************!)
model "Dates and times (SQL)"
uses "mmsystem", "mmodbc"
options keepassert
parameters
! Use Excel spreadsheet `datetime.xls'
! CNCT = 'DSN=Excel Files;DBQ=C:/xpress/examples/mosel/WhitePapers/MoselData/datetime.xls'
! CNCT = 'datetime.xls'
! Use Access database `datetime.mdb'
! CNCT = 'DSN=MS Access Database;DBQ=C:/xpress/examples/mosel/WhitePapers/MoselData/datetime.mdb'
CNCT = 'datetime.mdb'
! Use mysql database `datetime' (not provided)
! CNCT = 'DSN=mysql;DB=datetime'
! Use database 'datetime.sqlite' via ODBC
! CNCT = 'DSN=sqlite;DATABASE=datetime.sqlite'
! Use SQLite database 'datetime.sqlite' directly
! CNCT = 'datetime.sqlite'
end-parameters
declarations
T: time
D: date
DT: datetime
Dates: array(1..5) of date
DList: list of date
end-declarations
setparam("SQLverbose",true)
SQLconnect(CNCT)
assert(getparam("SQLsuccess"))
! Read in dates / time (make sure to select the format used by the
! spreadsheet/database)
! 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")
!)
T:=time(SQLreadstring("select * from Time1"))
D:=date(SQLreadstring("select * from Date1"))
DT:=datetime(SQLreadstring("select * from DateTime1"))
SQLexecute("select * from Dates", Dates)
SQLexecute("select * from Dates", DList)
setparam("timefmt", "%h:%0M %p")
writeln(D, ", ", T)
writeln(DT)
writeln(Dates)
writeln(DList)
! Read date / time from strings
setparam("timefmt", "%Hh%0Mm")
setparam("datefmt", "%dm%0my%0y")
setparam("datetimefmt", "%dm%0my%0y, %Hh%0Mm")
T:=time(SQLreadstring("select * from Time2"))
D:=date(SQLreadstring("select * from Date2"))
! 'Datetime2' is a keyword for MS Access: need to use quotes or backquotes
DT:=datetime(SQLreadstring("select * from `DateTime2`"))
! Standard form for other databases:
! DT:=datetime(SQLreadstring("select * from DateTime2"))
writeln(D, ", ", T)
writeln(DT)
! Use Mosel's default format
setparam("timefmt", "")
setparam("datefmt", "")
setparam("datetimefmt", "")
writeln(D, ", ", T)
writeln(DT)
SQLexecute("delete from TimeOut") ! Cleaning up previous results: works
SQLexecute("delete from DateOut") ! only for databases, cannot be used
SQLexecute("delete from DateTimeOut") ! with spreadsheets (instead, delete
! previous solutions directly in the
! spreadsheet file)
SQLexecute("insert into TimeOut values (?)", [T])
assert(getparam("SQLsuccess"))
SQLexecute("insert into DateOut values (?)", [D])
assert(getparam("SQLsuccess"))
SQLexecute("insert into DateTimeOut values (?)", [DT])
assert(getparam("SQLsuccess"))
SQLdisconnect
end-model
|
(!******************************************************
Mosel Example Problems
======================
file datesinout3.mos
````````````````````
Reading/writing dates from/to
spreadsheets or databases via ODBC.
- Using 'initializations from' with the excel driver -
!!! If running this model produces the message
!!! 'Unexpected date/time format: ...'
!!! then the date format used by your spreadsheet is not among the
!!! preconfigured format settings tested by this model.
!!! You need to adapt the date and time formats to the format used
!!! internally by the spreadsheet, according to the date shown with
!!! the error message output by Mosel.
!!! (NB: these formats may be different from the spreadsheet display format)
(c) 2008 Fair Isaac Corporation
author: S. Heipcke, Nov. 2007, rev. Sep. 2014
*******************************************************!)
model "Dates and times (Excel)"
uses "mmsystem", "mmsheet"
parameters
CSTR = 'mmsheet.excel:datetime.xls'
end-parameters
declarations
T: time
D: date
DT: datetime
Dates: array(1..5) of date
DList: list of date
r: real
testdate: string
end-declarations
! Read a date into a string as a test for the date format:
! if the date format is recognized, we select the corresponding settings
initializations from CSTR
testdate as "skiph;Date1"
end-initializations
if testdate="2/20/2002" then ! m/d/y
setparam("timefmt", "%h:%0M:%0S %P")
setparam("datefmt", "%m/%d/%y")
setparam("datetimefmt", "%m/%d/%y %h:%0M:%0S %P")
elif testdate="20/02/2002" then ! dd/mm/yyyy
setparam("timefmt", "%0H:%0M:%0S")
setparam("datefmt", "%0d/%0m/%y")
setparam("datetimefmt", "%0d/%0m/%y %0H:%0M:%0S")
elif testdate="20.02.2002" then ! dd.mm.yyyy
setparam("timefmt", "%0H:%0M:%0S")
setparam("datefmt", "%0d.%0m.%y")
setparam("datetimefmt", "%0d.%0m.%y %0H:%0M:%0S")
else
writeln("Unexpected date/time format: ", testdate)
exit(0)
end-if
! Read in dates / time
initializations from CSTR
r as "skiph;Time1" ! Time is stored as a real
D as "skiph;Date1"
DT as "skiph;DateTime1"
Dates as "skiph;noindex;Dates"
DList as "skiph;Dates"
end-initializations
T:=time(round(r*24*3600*1000))
writeln(D, ", ", T)
writeln(DT)
writeln(Dates)
writeln(DList)
! Read date / time from strings (spreadsheet cells are formatted as text)
setparam("timefmt", "%Hh%0Mm")
setparam("datefmt", "%dm%0my%0y")
setparam("datetimefmt", "%dm%0my%0y, %Hh%0Mm")
initializations from CSTR
T as "skiph;Time2"
D as "skiph;Date2"
DT as "skiph;DateTime2"
end-initializations
writeln(D, ", ", T)
writeln(DT)
! Use Mosel's default format
setparam("timefmt", "")
setparam("datefmt", "")
setparam("datetimefmt", "")
writeln(D, ", ", T)
writeln(DT)
initializations to CSTR
T as "TimeOutE"
D as "DateOutE"
DT as "DateTimeOutE"
end-initializations
end-model
|
(!******************************************************
Mosel Example Problems
======================
file datesinout4.mos
````````````````````
Reading/writing dates from/to Oracle databases.
- Using OCI -
(c) 2008 Fair Isaac Corporation
author: S. Heipcke, Nov. 2007, rev. Aug. 2023
*******************************************************!)
model "Dates and times (OCI)"
uses "mmsystem", "mmoci"
options keepassert
parameters
DB="myname/mypassword@dbname" ! Login to Oracle database (not provided)
end-parameters
declarations
T: time
D: date
DT: datetime
Dates: array(1..5) of date
DList: list of date
end-declarations
setparam("OCIdebug",true)
OCIlogon(DB)
assert(getparam("OCIsuccess"))
writeln("Connection number: ",getparam("OCIconnection"))
! Read in dates / time (select the format used by the database)
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")
T:=time(OCIreadstring("select * from Time1"))
D:=date(OCIreadstring("select * from Date1"))
DT:=datetime(OCIreadstring("select * from DateTime1"))
OCIexecute("select * from Dates", Dates)
OCIexecute("select * from Dates", DList)
setparam("timefmt", "%h:%0M %p")
writeln(D, ", ", T)
writeln(DT)
writeln(Dates)
writeln(DList)
! Read date / time from strings
setparam("timefmt", "%Hh%0Mm")
setparam("datefmt", "%dm%0my%0y")
setparam("datetimefmt", "%dm%0my%0y, %Hh%0Mm")
T:=time(OCIreadstring("select * from Time2"))
D:=date(OCIreadstring("select * from Date2"))
DT:=datetime(OCIreadstring("select * from DateTime2"))
writeln(D, ", ", T)
writeln(DT)
! Use Mosel's default format
setparam("timefmt", "")
setparam("datefmt", "")
setparam("datetimefmt", "")
writeln(D, ", ", T)
writeln(DT)
OCIexecute("delete from TimeOut") ! Cleaning up previous results
OCIexecute("delete from DateOut")
OCIexecute("delete from DateTimeOut")
! The following assumes that the output fields have types date/timestamp;
! if they are just strings (as with 'datesinout.mos') we do not need the
! conversion to Oracle format with TO_... :
OCIexecute("insert into TimeOut values (TO_TIMESTAMP(:1,'HH24:MI:SS'))", [T])
assert(getparam("OCIsuccess"))
OCIexecute("insert into DateOut values (TO_DATE(:1,'YYYY-MM-DD'))", [D])
assert(getparam("OCIsuccess"))
OCIexecute("insert into DateTimeOut values (TO_TIMESTAMP(:1,'YYYY-MM-DD\"T\"HH24:MI:SS'))", [DT])
assert(getparam("OCIsuccess"))
OCIlogoff
end-model
**************************************************
! Creation of input and output tables in an Oracle database:
declarations
tsucc: array ({false,true}) of string
end-declarations
tsucc(false):="failed"; tsucc(true):="succeeded"
OCIexecute("create table Date1 (DateValue date)")
writeln(" - Create Date1 (",tsucc(getparam("OCIsuccess")),")")
OCIexecute("insert into Date1 (DateValue) values (:1)", ['20-Feb-2002'])
writeln(" - Insert values in Date1 (",tsucc(getparam("OCIsuccess")),",",
getparam("OCIrowcnt"),'/',getparam("OCIrowxfr")," rows)")
OCIexecute("create table Time1 (DateValue timestamp(3))")
writeln(" - Create Time1 (",tsucc(getparam("OCIsuccess")),")")
OCIexecute("insert into Time1 (DateValue) values (:1)", ['1-Jan-0 4:00:00pm'])
writeln(" - Insert values in Time1 (",tsucc(getparam("OCIsuccess")),",",
getparam("OCIrowcnt"),'/',getparam("OCIrowxfr")," rows)")
OCIexecute("delete from Time1")
writeln(" - Delete from Time1 (",tsucc(getparam("OCIsuccess")),")")
OCIexecute("create table DateTime1 (DateValue timestamp(3))")
writeln(" - Create DateTime1 (",tsucc(getparam("OCIsuccess")),")")
OCIexecute("insert into DateTime1 (DateValue) values (:1)", ['20-Feb-2002 4:00:00pm'])
writeln(" - Insert values in DateTime1 (",tsucc(getparam("OCIsuccess")),",",
getparam("OCIrowcnt"),'/',getparam("OCIrowxfr")," rows)")
OCIexecute("create table Dates (DateValue date)")
writeln(" - Create Dates (",tsucc(getparam("OCIsuccess")),")")
OCIexecute("insert into Dates (DateValue) values (:1)", ['21-Jan-1999','22-Feb-2000','23-Mar-2002','24-Apr-2005','25-May-2010'])
writeln(" - Insert values in Dates (",tsucc(getparam("OCIsuccess")),",",
getparam("OCIrowcnt"),'/',getparam("OCIrowxfr")," rows)")
OCIexecute("create table Date2 (DateValue varchar(10))")
writeln(" - Create Date2 (",tsucc(getparam("OCIsuccess")),")")
OCIexecute("insert into Date2 (DateValue) values (:1)", ['20m02y2002'])
writeln(" - Insert values in Date2 (",tsucc(getparam("OCIsuccess")),",",
getparam("OCIrowcnt"),'/',getparam("OCIrowxfr")," rows)")
OCIexecute("create table Time2 (DateValue varchar(12))")
writeln(" - Create Time2 (",tsucc(getparam("OCIsuccess")),")")
OCIexecute("insert into Time2 (DateValue) values (:1)", ['16h00m'])
writeln(" - Insert values in Time2 (",tsucc(getparam("OCIsuccess")),",",
getparam("OCIrowcnt"),'/',getparam("OCIrowxfr")," rows)")
OCIexecute("create table DateTime2 (DateValue varchar(25))")
writeln(" - Create DateTime2 (",tsucc(getparam("OCIsuccess")),")")
OCIexecute("insert into DateTime2 (DateValue) values (:1)", ['20m02y2002, 16h00m'])
writeln(" - Insert values in DateTime2 (",tsucc(getparam("OCIsuccess")),",",
getparam("OCIrowcnt"),'/',getparam("OCIrowxfr")," rows)")
OCIexecute("create table DateOut (DateValue date)")
writeln(" - Create DateOut (",tsucc(getparam("OCIsuccess")),")")
OCIexecute("create table TimeOut (DateValue timestamp(3))")
writeln(" - Create TimeOut (",tsucc(getparam("OCIsuccess")),")")
OCIexecute("create table DateTimeOut (DateValue timestamp(3))")
writeln(" - Create DateTimeOut (",tsucc(getparam("OCIsuccess")),")")
|
(!******************************************************
Mosel Example Problems
======================
file datesinout5.mos
````````````````````
Reading/writing dates from/to spreadsheets.
- Using 'initializations from' with the xsl driver -
(c) 2012 Fair Isaac Corporation
author: S. Heipcke, Dec. 2012, rev. Sep. 2014
*******************************************************!)
model "Dates and times (spreadsheet)"
uses "mmsystem", "mmsheet"
parameters
CSTR = 'mmsheet.xls:datetime.xls'
! CSTR = 'mmsheet.xlsx:datetime.xlsx'
end-parameters
declarations
T: time
D: date
DT: datetime
Dates: array(1..5) of date
DList: list of date
end-declarations
! Read in dates / time (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")
initializations from CSTR
T as "skiph;Time1"
D as "skiph;Date1"
DT as "skiph;DateTime1"
Dates as "skiph;noindex;Dates"
DList as "skiph;Dates"
end-initializations
writeln(D, ", ", T)
writeln(DT)
writeln(Dates)
! Read date / time from strings
setparam("timefmt", "%Hh%0Mm")
setparam("datefmt", "%dm%0my%0y")
setparam("datetimefmt", "%dm%0my%0y, %Hh%0Mm")
initializations from CSTR
T as "skiph;Time2"
D as "skiph;Date2"
DT as "skiph;DateTime2"
end-initializations
writeln(D, ", ", T)
writeln(DT)
! Use Mosel's default format
setparam("timefmt", "")
setparam("datefmt", "")
setparam("datetimefmt", "")
writeln(D, ", ", T)
writeln(DT)
initializations to CSTR
T as "TimeOutE"
D as "DateOutE"
DT as "DateTimeOutE"
end-initializations
end-model
|
