| odbcinv.mos |
(!*******************************************************
Mosel Example Problems
======================
file odbcinv.mos
````````````````
ODBC with selection statements using columns in
inverted order.
(c) 2008 Fair Isaac Corporation
author: S. Heipcke, 2006, rev. Jan. 2014
*******************************************************!)
model "ODBC selection of columns"
uses "mmodbc"
declarations
PRODUCTS: set of string
MACH: range
COST,COST2: dynamic array(MACH,PRODUCTS) of real
DUR,DUR2,TEMP: dynamic array(PRODUCTS,MACH) of integer
end-declarations
DB:='multicol.mdb'
! DB:='multicol.sqlite'
! DB:='DSN=sqlite;DATABASE=multicol.sqlite'
setparam("SQLdebug",true)
SQLconnect(DB)
! Read data from the table 'ProdData'
SQLexecute("select Mach,Products,Cost from ProdData", COST)
SQLexecute("select Products,Mach,Duration from ProdData", DUR)
! Print out what we have read
writeln(COST); writeln(DUR)
! Write out data to another table (after deleting and re-creating the table)
SQLexecute("drop table CombData")
SQLexecute("create table CombData (Products varchar(10), Mach integer, Cost double, Duration integer)")
SQLexecute("insert into CombData (Mach,Products,Cost) values (?,?,?)", COST)
! Altenatively:
! SQLexecute("insert into CombData (Products,Mach,Cost) values (?2,?1,?3)", COST)
! Fill the 'Duration' field of the output table:
! 1. update the existing entries, 2. add new entries
SQLupdate("select Products,Mach,Duration from CombData", DUR)
! Equivalent:
! SQLexecute("update CombData set Duration=?3 where Products=?1 and Mach=?2", DUR)
forall(p in PRODUCTS, m in MACH | exists(DUR(p,m)) and not exists(COST(m,p)))
TEMP(p,m) := DUR(p,m)
SQLexecute("insert into CombData (Products,Mach,Duration) values (?,?,?)", TEMP)
SQLdisconnect
initializations from "mmodbc.odbc:debug;"+DB
COST2 as "ProdData(Mach,Products,Cost)"
DUR2 as "ProdData(Products,Mach,Duration)"
end-initializations
writeln(COST2); writeln(DUR2)
! Delete and re-create the output table
SQLconnect(DB)
SQLexecute("drop table CombData2")
SQLexecute("create table CombData2 (Products varchar(10), Mach integer, Cost double, Duration integer)")
SQLdisconnect
initializations to "mmodbc.odbc:debug;"+DB
COST2 as "CombData2(Mach,Products,Cost)"
DUR2 as "CombData2(Products,Mach,Duration)"
end-initializations
end-model
|
|
| odbcselfunc.mos |
(!*******************************************************
Mosel Example Problems
======================
file odbcselfunc.mos
````````````````````
ODBC with selection statements and functions.
(c) 2008 Fair Isaac Corporation
author: S. Heipcke, 2002, rev. Sep. 2018
*******************************************************!)
model "ODBC selection and functions"
uses "mmodbc"
declarations
Item: set of string
COST1,COST2,COST3: dynamic array(Item) of real
end-declarations
setparam("SQLdebug",true)
! SQLconnect('DSN=Excel Files;DBQ=C:/xpress/examples/mosel/WhitePapers/MoselData/odbcsel.xls')
! SQLconnect('odbcsel.xls')
! SQLconnect('DSN=MS Access Database;DBQ=C:/xpress/examples/mosel/WhitePapers/MoselData/odbcsel.mdb')
SQLconnect('odbcsel.mdb')
! SQLconnect('DSN=sqlite;DATABASE=odbcsel.sqlite')
! SQLconnect('odbcsel.sqlite')
! Select data depending on the value of a second field, the limit for which
! is given in a second table USER_OPTIONS
SQLexecute("select ITEM,COST from MYDATA where DIST > (select MINDIST from USER_OPTIONS)", COST1)
! Select data depending on the values of ITEM
SQLexecute("select ITEM,COST from MYDATA where ITEM in ('A', 'C', 'D', 'G')",
COST2)
! Select data depending on the values of the ratio COST/DIST
SQLexecute("select ITEM,COST from MYDATA where COST/DIST between 0.01 and 0.1",
COST3)
writeln("COST1: ", COST1, ", COST2: ", COST2, ", COST3: ", COST3)
! Print the DIST value of ITEM 'B'
writeln("Distance of 'B': ",
SQLreadreal("select DIST from MYDATA where ITEM='B'"))
! Number of entries with COST>30
writeln("Count COST>30: ",
SQLreadinteger("select count(*) from MYDATA where COST>30"))
! Total and average distances
writeln("Total distance: ", SQLreadreal("select sum(DIST) from MYDATA"),
", average distance: ", SQLreadreal("select avg(DIST) from MYDATA"))
SQLdisconnect
end-model
|
|
| odbcchkstatus.mos |
(!******************************************************
Mosel Example Problems
======================
file odbcchkstatus.mos
``````````````````````
Implementing 'stop on error' behaviour for SQL statements.
(c) 2020 Fair Isaac Corporation
author: S. Heipcke, Feb. 2020
*******************************************************!)
model "Check SQL status"
uses "mmodbc", "mmsystem"
parameters
DB="mytest.sqlite"
end-parameters
forward procedure checksqlstatus
forward procedure createdb
declarations
A: array(string) of real
end-declarations
A::(['a','ab','b'])[1.5,2,2.5]
setparam("SQLverbose",true)
setparam("SQLdebug",true)
SQLconnect(DB)
createdb
SQLexecute("insert into TestTable (AnIndx,AValue) values (?,?)", A)
checksqlstatus
! This is a duplicate entry, so causes an error
initializations to "mmodbc.odbc:debug;"+DB
A as "TestTable(AnIndx,AValue)"
end-initializations
!)
! This is a duplicate entry, so causes an error
SQLexecute("insert into TestTable (AnIndx,AValue) values (?,?)", A)
checksqlstatus
SQLdisconnect
!**** Check status and stop on error ****
procedure checksqlstatus
declarations
sstat: boolean
end-declarations
sstat:= getparam("SQLsuccess")
writeln_("SQL status: ", if(sstat, "succeeded", "failed"), ". ",
getparam("SQLrowcnt"), " rows affected")
if not sstat then
setioerr("SQL error") ! Stop on error if parameter 'ioctrl' is false
end-if
end-procedure
!**** Database creation ****
procedure createdb
declarations
tsucc: array ({false,true}) of string
Tables: list of string
end-declarations
tsucc(false):="failed"; tsucc(true):="succeeded"
! Delete the table if it exists already
SQLtables(Tables)
if findfirst(Tables, "TestTable")>0 then
SQLexecute("drop table TestTable")
end-if
! Create a new table
SQLexecute("create table TestTable (AnIndx varchar(50), ACharField char(3), Status boolean, CreationDate date, CreationTime time, AValue double, primary key (AnIndx))")
writeln(" - Create TestTable (",tsucc(getparam("SQLsuccess")),")")
end-procedure
end-model
|
|
| ociinv.mos |
(!*******************************************************
Mosel Example Problems
======================
file ociinv.mos
```````````````
Accessing an Oracle database with selection
statements using columns in inverted order.
(c) 2008 Fair Isaac Corporation
author: S. Heipcke, 2007, rev. Jul. 2010
*******************************************************!)
model "OCI selection of columns"
uses "mmoci"
parameters
DB="myname/mypassword@dbname" ! Login to Oracle database (not provided)
end-parameters
declarations
PRODUCTS: set of string
MACH: range
COST,COST2: dynamic array(MACH,PRODUCTS) of real
DUR,DUR2,TEMP: dynamic array(PRODUCTS,MACH) of integer
end-declarations
setparam("OCIdebug",true)
OCIlogon(DB)
! Read data from the table 'ProdData'
OCIexecute("select Mach,Products,Cost from ProdData", COST)
OCIexecute("select Products,Mach,Duration from ProdData", DUR)
! Print out what we have read
writeln(COST); writeln(DUR)
! Write out data to another table (after deleting and re-creating the table)
OCIexecute("drop table CombData")
OCIexecute("create table CombData (Products varchar(10), Mach integer, Cost float, Duration integer)")
OCIexecute("insert into CombData (Mach,Products,Cost) values (:1,:2,:3)", COST)
! Altenatively:
! OCIexecute("insert into CombData (Products,Mach,Cost) values (:2,:1,:3)", COST)
! Fill the 'Duration' field of the output table:
! 1. update the existing entries, 2. add new entries
OCIexecute("update CombData set Duration=:3 where Products=:1 and Mach=:2", DUR)
forall(p in PRODUCTS, m in MACH | exists(DUR(p,m)) and not exists(COST(m,p)))
TEMP(p,m) := DUR(p,m)
OCIexecute("insert into CombData (Products,Mach,Duration) values (:1,:2,:3)", TEMP)
OCIlogoff
initializations from "mmoci.oci:debug;"+DB
COST2 as "ProdData(Mach,Products,Cost)"
DUR2 as "ProdData(Products,Mach,Duration)"
end-initializations
writeln(COST2); writeln(DUR2)
! Delete and re-create the output table
OCIlogon(DB)
OCIexecute("drop table CombData2")
OCIexecute("create table CombData2 (Products varchar(10), Mach integer, Cost float, Duration integer)")
OCIlogoff
initializations to "mmoci.oci:debug;"+DB
COST2 as "CombData2(Mach,Products,Cost)"
DUR2 as "CombData2(Products,Mach,Duration)"
end-initializations
end-model
**************************************************
! Creation of the data table in an Oracle database:
declarations
tsucc: array ({false,true}) of string
end-declarations
tsucc(false):="failed"; tsucc(true):="succeeded"
OCIexecute("create table ProdData (Mach integer, Products varchar(10), Cost float, Duration integer)")
writeln(" - Create ProdData (",tsucc(getparam("OCIsuccess")),")")
declarations
PRODUCTS: set of string
MACH: range
COST: dynamic array(PRODUCTS,MACH) of real
DUR: dynamic array(PRODUCTS,MACH) of integer
end-declarations
initializations from "ocidata.dat"
COST DUR
end-initializations
OCIexecute("insert into ProdData (Mach, Products, Cost, Duration) values (:2, :1, :3, :4)", [COST,DUR])
writeln(" - Insert values in ProdData (",tsucc(getparam("OCIsuccess")),",",
getparam("OCIrowcnt"),'/',getparam("OCIrowxfr")," rows)")
|
|
| ociselfunc.mos |
(!*******************************************************
Mosel Example Problems
======================
file ociselfunc.mos
```````````````````
Accessing an Oracle database with SQL selection
statements and functions.
(c) 2008 Fair Isaac Corporation
author: S. Heipcke, 2007, rev. Jul. 2010
*******************************************************!)
model "OCI selections and functions"
uses "mmoci"
parameters
DB="myname/mypassword@dbname" ! Login to Oracle database (not provided)
end-parameters
declarations
Item: set of string
COST1,COST2,COST3: dynamic array(Item) of real
end-declarations
! setparam("OCIdebug",true)
OCIlogon(DB)
! Select data depending on the value of a second field, the limit for which
! is given in a second table USER_OPTIONS
OCIexecute("select ITEM,COST from MYDATA where DIST > (select MINDIST from USER_OPTIONS)", COST1)
! Select data depending on the values of ITEM
OCIexecute("select ITEM,COST from MYDATA where ITEM in ('A', 'C', 'D', 'G')",
COST2)
! Select data depending on the values of the ratio COST/DIST
OCIexecute("select ITEM,COST from MYDATA where COST/DIST between 0.01 and 0.1",
COST3)
writeln("COST1: ", COST1, ", COST2: ", COST2, ", COST3: ", COST3)
! Print the DIST value of ITEM 'B'
writeln("Distance of 'B': ",
OCIreadreal("select DIST from MYDATA where ITEM='B'"))
! Number of entries with COST>30
writeln("Count COST>30: ",
OCIreadinteger("select count(*) from MYDATA where COST>30"))
! Total and average distances
writeln("Total distance: ", OCIreadreal("select sum(DIST) from MYDATA"),
", average distance: ", OCIreadreal("select avg(DIST) from MYDATA"))
OCIlogoff
end-model
**************************************************
! Creation of the data table in an Oracle database:
declarations
tsucc: array ({false,true}) of string
ITEM: set of string
CO, DI: array(ITEM) of real
end-declarations
tsucc(false):="failed"; tsucc(true):="succeeded"
initializations from "ocidata.dat"
[CO, DI] as "CostDist"
end-initializations
OCIexecute("create table MYDATA (ITEM varchar(5), COST float, DIST float)")
writeln(" - Create MYDATA (",tsucc(getparam("OCIsuccess")),")")
OCIexecute("insert into MYDATA (ITEM, COST, DIST) values (:1, :2, :3)",
[CO,DI])
writeln(" - Insert values in MYDATA (",tsucc(getparam("OCIsuccess")),",",
getparam("OCIrowcnt"),'/',getparam("OCIrowxfr")," rows)")
OCIexecute("create table USER_OPTIONS (MINDIST float, ETC integer)")
writeln(" - Create USER_OPTIONS (",tsucc(getparam("OCIsuccess")),")")
OCIexecute("insert into USER_OPTIONS (MINDIST) values (:1)", [500])
writeln(" - Insert values in USER_OPTIONS (",tsucc(getparam("OCIsuccess")),",",
getparam("OCIrowcnt"),'/',getparam("OCIrowxfr")," rows)")
|
|
| odbcinspectdb.mos |
(!******************************************************
Mosel Example Problems
======================
file odbcinspectdb.mos
``````````````````````
Retrieving information about database table structure.
(c) 2014 Fair Isaac Corporation
author: S. Heipcke, Feb. 2014
*******************************************************!)
model "Analyze DB structure"
uses "mmodbc", "mmsystem"
parameters
DB="personnel.sqlite"
! DB="DSN=mysql;DB=test"
end-parameters
forward procedure create_db
declarations
tables: list of string
pkeylist: list of string
pkeyind: list of integer
fnames: dynamic array(Fields: range) of string
ftypes: dynamic array(Fields) of integer
ftypenames: dynamic array(Fields) of string
end-declarations
setparam("SQLverbose",true)
! Uncomment the following line to generate the database
create_db
SQLconnect(DB)
! Retrieve list of database tables
SQLtables(tables)
forall(t in tables) do
! Retrieve primary keys
SQLprimarykeys(t, pkeylist)
writeln(t, " primary key field names: ", pkeylist)
SQLprimarykeys(t, pkeyind)
writeln(t, " primary key field indices: ", pkeyind)
! Retrieve table structure
writeln(t, " has ", SQLcolumns(t,fnames,ftypes), " fields")
res:=SQLcolumns(t,fnames,ftypenames)
forall(f in Fields | exists(fnames(f)))
writeln(f, ": ", fnames(f), " ", ftypes(f), ": ", ftypenames(f))
! Delete aux. arrays for next loop iteration
delcell(fnames)
delcell(ftypes)
delcell(ftypenames)
end-do
SQLdisconnect
!**** Database creation ****
procedure create_db
declarations
tsucc: array ({false,true}) of string
Tables: list of string
end-declarations
tsucc(false):="failed"; tsucc(true):="succeeded"
SQLconnect(DB)
! Delete the table if it exists already
SQLtables(Tables)
if findfirst(Tables, "PersList")>0 then
SQLexecute("drop table PersList")
end-if
! Create a new table
SQLexecute("create table PersList (Surname varchar(50), FirstName varchar(50), Age integer, Address varchar(200), Country char(3), Status boolean, CreationDate date, CreationTime time, AValue double, primary key (FirstName, Surname))")
writeln(" - Create PersList (",tsucc(getparam("SQLsuccess")),")")
SQLdisconnect
end-procedure
end-model
|
|
