Initializing help system before first use

Dense vs. sparse data format
Type: Programming
Rating: 2 (easy-medium)
Description:

This example shows how to read data tables with different formats from a spreadsheet:

  • a dense 3x2 table
  • a dense 2x3 table
  • a sparse 2x3 table

A spreadsheet may be accessed from Mosel through an ODBC connection (using the odbc driver, indexeg.mos, or with SQL statements, indexeg2.mos), with the software-specific driver excel (indexeg3.mos), or with the portable mmsheet module that can be used to read and write xls, xlsx (indexeg5.mos) and csv (indexeg6.mos) files.
The first two methods (odbc driver and SQL statements) apply to spreadsheets and databases. In addition, for Oracle databases we show how to use the software-specific connection provided by the module mmoci (indexeg4.mos).

File(s): indexeg.mos, indexeg2.mos, indexeg3.mos, indexeg4.mos, indexeg5.mos, indexeg6.mos
Data file(s): indexeg.csv, indexeg.xls, indexeg.xlsx, indexeg.sqlite

indexeg.mos
(!*******************************************************
   Mosel Example Problems 
   ======================

   file indexeg.mos
   ````````````````
   Using ODBC with dense and sparse format data tables.
   - Using 'initializations from' with the odbc driver -
       
   (c) 2008 Fair Isaac Corporation
       author: S. Heipcke, 2006, rev. Feb. 2014
*******************************************************!)

model ODBCImpEx
 uses "mmodbc"

 declarations
  A: array(1..3, 1..2) of real
  B: array(1..2, 1..3) of real
  C: array(1..2, 1..3) of real
  D: array(R:range, 1..3) of real
  CSTR: string
 end-declarations

! CSTR:= 'mmodbc.odbc:indexeg.xls'
 CSTR:= "mmodbc.odbc:debug;indexeg.sqlite"
! CSTR:= "mmodbc.odbc:debug;DSN=sqlite;DATABASE=indexeg.sqlite"

 setparam("SQLverbose",true)

 ! Data must be dense - there are not enough columns to serve as index!
 initializations from CSTR
  A as 'Range3by2'
 end-initializations

 writeln("\n ===A=== ")
 forall(i in 1..3)
  writeln("Row(",i,"): ", A(i,1), " ", A(i,2))
 
 ! Dense data
 initializations from CSTR
  B as 'noindex;Range2by3'
 end-initializations
 
 writeln("\n ===B=== ")
 forall(i in 1..2)
  writeln("Row(",i,"): ", B(i,1), " ", B(i,2), " ", B(i,3)) 
 
 ! Indexed data
 initializations from CSTR
  C as 'Range2by3i'
 end-initializations

 writeln("\n ===C=== ")
 forall(i in 1..2)
  writeln("Row(",i,"): ", C(i,1), " ", C(i,2), " ", C(i,3))

 ! Partially indexed ("rectangular format") data
 initializations from CSTR
  D as 'RectRange'
 end-initializations

 writeln("\n ===D=== ")
 forall(i in R)
  writeln("Row(",i,"): ", D(i,1), " ", D(i,2), " ", D(i,3))

end-model
indexeg2.mos
(!*******************************************************
   Mosel Example Problems 
   ======================

   file indexeg2.mos
   `````````````````
   Using ODBC with dense and sparse format data tables.
   - Using SQL statements -
       
   (c) 2008 Fair Isaac Corporation
       author: Y. Colombani, 2002, rev. Feb. 2014
*******************************************************!)

model ODBCImpEx2
 uses "mmodbc"

 declarations
  A: array(1..3, 1..2) of real
  B: array(1..2, 1..3) of real
  C: array(1..2, 1..3) of real
  D: array(R:range, 1..3) of real
  CSTR: string
 end-declarations

! CSTR:= 'DSN=Excel Files;DBQ=C:/xpress/examples/mosel/WhitePapers/MoselODBC/indexeg.xls'
! CSTR:= 'indexeg.xls'

! CSTR:= 'DSN=sqlite;DATABASE=indexeg.sqlite'
 CSTR:= 'indexeg.sqlite'

 SQLconnect(CSTR)
 setparam("SQLverbose",true)
 setparam("SQLdebug",true)

 ! Data must be dense - there are not enough columns to serve as index!
 SQLexecute("select * from Range3by2 ", A)
 writeln("\n ===A=== ")
 forall(i in 1..3)
  writeln("Row(",i,"): ", A(i,1), " ", A(i,2))
 
 setparam("SQLndxcol", false)      ! Dense data
 SQLexecute("select * from Range2by3 ", B)
 writeln("\n ===B=== ")
 forall(i in 1..2)
  writeln("Row(",i,"): ", B(i,1), " ", B(i,2), " ", B(i,3))
 
 setparam("SQLndxcol", true)       ! Indexed data
 SQLexecute("select * from Range2by3i ", C)
 writeln("\n ===C=== ")
 forall(i in 1..2)
  writeln("Row(",i,"): ", C(i,1), " ", C(i,2), " ", C(i,3))

 setparam("SQLndxcol", false)      ! Partially indexed data
 SQLexecute("select * from RectRange ", D)
 writeln("\n ===D=== ")
 forall(i in R)
  writeln("Row(",i,"): ", D(i,1), " ", D(i,2), " ", D(i,3))

 SQLdisconnect

end-model
indexeg3.mos
(!*******************************************************
   Mosel Example Problems 
   ======================

   file indexeg3.mos
   `````````````````
   Using ODBC with dense and sparse format data tables.
   - Using 'initializations from' with the excel driver -
       
   (c) 2008 Fair Isaac Corporation
       author: S. Heipcke, 2007, rev. Apr. 2014
*******************************************************!)

model ODBCImpEx3
 uses "mmsheet"

 declarations
  A: array(1..3, 1..2) of real
  B: array(1..2, 1..3) of real
  C: array(1..2, 1..3) of real
  D: array(R:range, 1..3) of real
  CSTR: string
 end-declarations

 CSTR:= 'mmsheet.excel:indexeg.xls'

 ! Dense data ('noindex'), skipping the header line ('skiph')
 initializations from CSTR
  A as 'skiph;noindex;Range3by2'
 end-initializations

 writeln("\n ===A=== ")
 forall(i in 1..3)
  writeln("Row(",i,"): ", A(i,1), " ", A(i,2))
 
 ! Dense data
 initializations from CSTR
  B as 'skiph;noindex;Range2by3'
 end-initializations
 
 writeln("\n ===B=== ")
 forall(i in 1..2)
  writeln("Row(",i,"): ", B(i,1), " ", B(i,2), " ", B(i,3)) 
 
 ! Indexed data
 initializations from CSTR
  C as 'skiph;Range2by3i'
 end-initializations

 writeln("\n ===C=== ")
 forall(i in 1..2)
  writeln("Row(",i,"): ", C(i,1), " ", C(i,2), " ", C(i,3))

 ! Partially indexed ("rectangular format") data
 initializations from CSTR
  D as 'skiph;partndx;RectRange'
 end-initializations

 writeln("\n ===D=== ")
 forall(i in R)
  writeln("Row(",i,"): ", D(i,1), " ", D(i,2), " ", D(i,3))

end-model
indexeg4.mos
(!*******************************************************
   Mosel Example Problems 
   ======================

   file indexeg4.mos
   `````````````````
   Reading dense and sparse format data tables
   from an Oracle database.
   - Using 'initializations' and SQL statements -
       
   (c) 2008 Fair Isaac Corporation
       author: S. Heipcke, 2007, rev. Feb. 2014
*******************************************************!)

model OCIImpEx
 uses "mmoci"

 parameters                                 
  DB="myname/mypassword@dbname"     ! Login to Oracle database (not provided)
 end-parameters

 declarations
  A,A2: array(1..3, 1..2) of real
  B,B2: array(1..2, 1..3) of real
  C,C2: array(1..2, 1..3) of real
  D,D2: array(R:range, 1..3) of real
 end-declarations

! **** Using SQL statements ****

 setparam("OCIverbose",true)
 OCIlogon(DB)
 setparam("OCIdebug",true)

 ! Data must be dense - there are not enough columns to serve as index!
 OCIexecute("select * from Range3by2 ", A)
 writeln("\n ===A=== ")
 forall(i in 1..3) writeln("Row(",i,"): ", A(i,1), " ", A(i,2))
 
 setparam("OCIndxcol", false)      ! Dense data
 OCIexecute("select * from Range2by3 ", B)
 writeln("\n ===B=== ")
 forall(i in 1..2)
  writeln("Row(",i,"): ", B(i,1), " ", B(i,2), " ", B(i,3))
 
 setparam("OCIndxcol", true)       ! Indexed data 
 OCIexecute("select * from Range2by3i ", C)
 writeln("\n ===C=== ")
 forall(i in 1..2)
  writeln("Row(",i,"): ", C(i,1), " ", C(i,2), " ", C(i,3))

 setparam("OCIndxcol", false)      ! Partially indexed data
 OCIexecute("select * from RectRange ", D)
 writeln("\n ===D=== ")
 forall(i in R)
  writeln("Row(",i,"): ", D(i,1), " ", D(i,2), " ", D(i,3))

 OCIlogoff

! **** Using 'initializations from' ****

 initializations from "mmoci.oci:"+DB
  A2 as 'Range3by2'
  B2 as 'noindex;Range2by3'
  C2 as 'Range2by3i'
  D2 as 'noindex;RectRange'
 end-initializations

 writeln("\n ===A2=== ")
 forall(i in 1..3) writeln("Row(",i,"): ", A2(i,1), " ", A2(i,2))
 writeln("\n ===B2=== ")
 forall(i in 1..2)
  writeln("Row(",i,"): ", B2(i,1), " ", B2(i,2), " ", B2(i,3))
 writeln("\n ===C2=== ")
 forall(i in 1..2)
  writeln("Row(",i,"): ", C2(i,1), " ", C2(i,2), " ", C2(i,3))
 writeln("\n ===D2=== ")
 forall(i in R)
  writeln("Row(",i,"): ", D2(i,1), " ", D2(i,2), " ", D2(i,3))
 
end-model


**************************************************


! Creation of data tables in an Oracle database:


 declarations
  tsucc: array ({false,true}) of string
 end-declarations

 tsucc(false):="failed"; tsucc(true):="succeeded"

 OCIexecute("create table Range3by2 (First float, Second float)")
 writeln(" - Create Range3by2 (",tsucc(getparam("OCIsuccess")),")")
 OCIexecute("insert into Range3by2 (First, Second) values (:1, :2)", [ 1.2, 2.2, 2.1, 2.2, 3.1, 4.4])
 writeln(" - Insert values in Range3by2 (",tsucc(getparam("OCIsuccess")),",",
        getparam("OCIrowcnt"),'/',getparam("OCIrowxfr")," rows)")

 OCIexecute("create table Range2by3 (First float, Second float, Third float)")
 writeln(" - Create Range2by3 (",tsucc(getparam("OCIsuccess")),")")
 OCIexecute("insert into Range2by3 (First, Second, Third) values (:1, :2, :3)", [ 1.2, 1.2, 1.3, 2.1, 2.2, 2.3])
 writeln(" - Insert values in Range2by3 (",tsucc(getparam("OCIsuccess")),",",
        getparam("OCIrowcnt"),'/',getparam("OCIrowxfr")," rows)")

 OCIexecute("create table Range2by3i (Firsti integer, Secondi integer, Value float)")
 writeln(" - Create Range2by3i (",tsucc(getparam("OCIsuccess")),")")
 OCIexecute("insert into Range2by3i (Firsti, Secondi, Value) values (:1, :2, :3)", [ 1, 1, 1.1, 1, 2, 1.2, 1, 3, 1.3, 2, 1, 2.1, 2, 2, 2.2, 2, 3, 2.3])
 writeln(" - Insert values in Range2by3i (",tsucc(getparam("OCIsuccess")),",",
        getparam("OCIrowcnt"),'/',getparam("OCIrowxfr")," rows)")

 OCIexecute("create table RectRange (Firsti integer, Second_1 float, Second_2 float, Second_3 float)")
 writeln(" - Create RectRange (",tsucc(getparam("OCIsuccess")),")")
 OCIexecute("insert into RectRange (Firsti, Second_1, Second_2,Second_3) values (:1, :2, :3, :4)", [ 1, 1.1, 1.2, 1.3, 2, 2.1, 2.2, 2.3])
 writeln(" - Insert values in RectRange (",tsucc(getparam("OCIsuccess")),",",
        getparam("OCIrowcnt"),'/',getparam("OCIrowxfr")," rows)")
indexeg5.mos
(!*******************************************************
   Mosel Example Problems 
   ======================

   file indexeg5.mos
   `````````````````
   Using ODBC with dense and sparse format data tables.
   - Using 'initializations from' with the xls driver -
       
   (c) 2012 Fair Isaac Corporation
       author: S. Heipcke, Dec. 2012, rev. Apr. 2014
*******************************************************!)

model "ODBCImpEx3 (generic spreadsheet)"
 uses "mmsheet"

 parameters
!  CSTR = 'mmsheet.xls:indexeg.xls'
  CSTR = 'mmsheet.xlsx:indexeg.xlsx'
 end-parameters

 declarations
  A: array(1..3, 1..2) of real
  B: array(1..2, 1..3) of real
  C: array(1..2, 1..3) of real
  D: array(R:range, 1..3) of real
 end-declarations

 ! Dense data ('noindex'), skipping the header line ('skiph')
 initializations from CSTR
  A as 'skiph;noindex;Range3by2'
 end-initializations

 writeln("\n ===A=== ")
 forall(i in 1..3)
  writeln("Row(",i,"): ", A(i,1), " ", A(i,2))
 
 ! Dense data
 initializations from CSTR
  B as 'skiph;noindex;Range2by3'
 end-initializations
 
 writeln("\n ===B=== ")
 forall(i in 1..2)
  writeln("Row(",i,"): ", B(i,1), " ", B(i,2), " ", B(i,3)) 
 
 ! Indexed data
 initializations from CSTR
  C as 'skiph;Range2by3i'
 end-initializations

 writeln("\n ===C=== ")
 forall(i in 1..2)
  writeln("Row(",i,"): ", C(i,1), " ", C(i,2), " ", C(i,3))

 ! Partially indexed ("rectangular format") data
 initializations from CSTR
  D as 'skiph;partndx;RectRange'
 end-initializations

 writeln("\n ===D=== ")
 forall(i in R)
  writeln("Row(",i,"): ", D(i,1), " ", D(i,2), " ", D(i,3))

end-model
indexeg6.mos
(!*******************************************************
   Mosel Example Problems 
   ======================

   file indexeg6.mos
   `````````````````
   Using ODBC with dense and sparse format data tables.
   - Using 'initializations from' with the csv driver -
       
   (c) 2012 Fair Isaac Corporation
       author: S. Heipcke, Dec. 2012, rev. Apr. 2014
*******************************************************!)

model "ODBCImpEx3 (CSV)"
 uses "mmsheet"

 parameters
  CSTR = 'mmsheet.csv:indexeg.csv'
 end-parameters

 declarations
  A: array(1..3, 1..2) of real
  B: array(1..2, 1..3) of real
  C: array(1..2, 1..3) of real
  D: array(R:range, 1..3) of real
 end-declarations

 ! Dense data ('noindex')
 initializations from CSTR
  A as 'noindex;[B3:C5]'
 end-initializations

 writeln("\n ===A=== ")
 forall(i in 1..3)
  writeln("Row(",i,"): ", A(i,1), " ", A(i,2))
 
 ! Dense data
 initializations from CSTR
  B as 'noindex;[B9:D10]'
 end-initializations
 
 writeln("\n ===B=== ")
 forall(i in 1..2)
  writeln("Row(",i,"): ", B(i,1), " ", B(i,2), " ", B(i,3)) 
 
 ! Indexed (=sparse format) data
 initializations from CSTR
  C as '[B14:D19]'
 end-initializations

 writeln("\n ===C=== ")
 forall(i in 1..2)
  writeln("Row(",i,"): ", C(i,1), " ", C(i,2), " ", C(i,3))

 ! Partially indexed ("rectangular format") data
 initializations from CSTR
  D as 'partndx;[B22:E23]'
 end-initializations

 writeln("\n ===D=== ")
 forall(i in R)
  writeln("Row(",i,"): ", D(i,1), " ", D(i,2), " ", D(i,3))

end-model
Parent Topic Mosel language: Examples from the whitepaper 'Using ODBC and other database interfaces with Mosel'