(!******************************************************
   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