Working with records

In this section we work once more with the data range ProdData that has already been used in the example of Section Reading several arrays from a single table:

We now want to read this data into a record data structure, more precisely, an array of records where each record contains the data for one product-machine pair. Such a record may be defined in different ways: it may contain just the fields 'Cost' and 'Duration', using the product and machine as indices, or we could define a record with four fields, 'Product', 'Mach', 'Cost', and 'Duration', using a simple counter as index to the array. The model recordin.mos printed below implements both cases.

model "Record input (Excel)"
 uses "mmsheet"

 declarations
  PRODUCTS: set of string
  MACH: range
  ProdRec = record
   Cost: real
   Duration: integer
  end-record
  PDATA: dynamic array(PRODUCTS,MACH) of ProdRec

  R = 1..9
  AllDataRec = record
   Product: string
   Mach: integer
   Cost: real
   Duration: integer
  end-record
  ALLDATA: array(R) of AllDataRec
 end-declarations

! **** Reading complete records

 initializations from "mmsheet.excel:recorddata.xls"
  PDATA as "ProdData"
  ALLDATA as "noindex;ProdData"
 end-initializations

! Now let us see what we have
 writeln('PDATA is: ', PDATA)
 writeln('ALLDATA is: ', ALLDATA)

end-model 

This model will fill the fields of each record in the order of their definition with the data from a row of the input range in the order of the columns. That is, the first two columns of range ProdData will become the indices of PDATA, the third column is read into the 'Cost' field, and the forth column into the 'Duration' field. The record array ALLDATA will have the first column of ProdData in its first field ('Product'), the second column in the field 'Mach', and so on.

It is also possible (a) to select certain columns from a database table or spreadsheet range and (b) to specify which record fields to initialize. The former can be used to read data from a spreadsheet range or database table that contains other data or has columns/fields arranged in a different order from the Mosel model as we have already seen in the example of Section Selection of columns/fields. The following code extract shows how to read the contents of some record fields from specified parts of the input data range.

 declarations
  PDATA2: dynamic array(PRODUCTS,MACH) of ProdRec
  ALLDATA2: array(R) of AllDataRec
 end-declarations

! **** Reading record fields

 initializations from "mmodbc.odbc:recorddata.sqlite"
  PDATA2(Cost) as "ProdData(IndexP,IndexM,Cost)"
  ALLDATA2(Product,Mach,Duration) as "noindex;ProdData(IndexP,IndexM,Duration)"
 end-initializations 

This results in an array of records PDATA2 with values in the 'Cost' field and all 'Duration' fields at 0 and an array of records ALLDATA2 with values in the 'Product', 'Mach', and 'Duration' fields and all 'Cost' fields at 0.

When using the excel driver for accessing a spreadsheet it is equally possible to select columns from a spreadsheet range, either via column header names if they are included in the range specification or via column order numbers within the seleted range:

 initializations from "mmsheet.excel:recorddata.xls"
  PDATA as "skiph;ProdData"
  ALLDATA as "skiph,noindex;AllData"
  PDATA2(Cost) as "ProdData(#1,#2,#3)"
  ALLDATA2(Product,Mach,Duration) as "noindex;ProdData(#1,#2,#4)"
 end-initializations 

With SQL statements it would be possible to select columns from a spreadsheet range (or database table fields). However Mosel's syntax does not provide any means to select fields for an array of records in the SQLexecute statement. We can initialize the complete arrays of records as shown below, but it is not possible to select just certain record fields when reading or writing data (it would of course be possible to employ some auxiliary data structures for reading in the data and copy their contents to the array of records).

 setparam("SQLverbose",true)
 SQLconnect("recorddata.xls")
 SQLexecute("select * from ProdData", PDATA)
 setparam("SQLndxcol", false)           ! Dense data
 SQLexecute("select * from AllData", ALLDATA)
 SQLdisconnect 

Note further that any (record) types used in SQL statements must be declared as public.

 public declarations
  ProdRec = public record
   !...
  end-record
  AllDataRec = public record
   !...
  end-record
 end-declarations