Selection of columns/fields

The structure of the tables read from or written to using ODBC does not necessarily have to be the same as the tables in the Mosel model: tables may have more fields than required, or fields may be defined in a different order. To choose the fields from such tables that we wish to access we need to indicate the field names in the ODBC queries. In some of the previous SQL examples we have already named the fields we wish to access (instead of using a wildcard, such as select * from). With initializations blocks it is equally possible to indicate the names of the fields as is shown in the following example.

We work with the example from Sections Reading several arrays from a single table and Outputting several arrays into a single table where a single table in the data source holds data for several Mosel arrays. The following Mosel model odbcinv.mos reads in the two arrays COST and DUR separately. The index sets of the array COST are in inverse order.

model "ODBC selection of columns"
 uses "mmodbc"

 declarations
  PRODUCTS: set of string
  MACH: range
  COST: dynamic array(MACH,PRODUCTS) of real
  DUR: dynamic array(PRODUCTS,MACH) of integer
 end-declarations

 initializations from "mmodbc.odbc:debug;multicol.mdb"
  COST as "ProdData(Mach,Products,Cost)"
  DUR as "ProdData(Products,Mach,Duration)"
 end-initializations

! Print out what we have read
 writeln(COST); writeln(DUR)

! Delete and re-create the output table
 SQLconnect('multicol.mdb')
 SQLexecute("drop table CombData2")
 SQLexecute("create table CombData2 (Products varchar(10), Mach integer, Cost double,
 Duration integer)")
 SQLdisconnect

 initializations to "mmodbc.odbc:debug;multicol.mdb"
  COST as "CombData2(Mach,Products,Cost)"
  DUR as "CombData2(Products,Mach,Duration)"
 end-initializations

end-model 

When writing out the two arrays into the result table CombData using initializations to the data does not appear the way we would wish: the data for the second array gets appended to the data of the first instead of filling the remaining field with the additional data. The reason for this is that initializations to performs an `insert' command and not an `update' which is the command to use if the table already holds some data. To fill the table in the desired way it is therefore necessary to use SQL queries for completing the output. Below follows the complete SQL version of this model.

 declarations
  TEMP: array(PRODUCTS,MACH) of integer
 end-declarations

 setparam("SQLdebug",true)
 SQLconnect('multicol.mdb')

! 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)")

! Write out the 'COST' array
 SQLexecute("insert into CombData (Mach,Products,Cost) values (?,?,?)", 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)
 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 

A second possibility for formulating the SQL output query for the array COST is to use the numbering of columns (?1, ?2, etc.) to select which of the indices/value columns of the data array we want to write out (we might choose, for instance, to write out only a single index set), and in which order. This functionality has no direct correspondence in the formulation with initializations to blocks.

 SQLexecute("insert into CombData (Products,Mach,Cost) values (?2,?1,?3)",
            COST)

There is also an equivalent formulation of the `update' statement using the SQL command `update' instead of SQLupdate. We use again the numbering of columns to indicate where the indices and data entries of the Mosel array DUR are to be inserted:

 SQLexecute("update CombData set Duration=?3 where Products=?1 and Mach=?2", DUR)