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