| |||||||||||||||||||||||||||||
Dense vs. sparse data format Description The example 'indexeg' shows how to read data tables with different formats from a spreadsheet:
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. Further explanation of this example: Xpress Whitepaper 'Using ODBC and other database interfaces with Mosel', Section Examples - Dense vs. sparse data format.
Source Files By clicking on a file name, a preview is opened at the bottom of this page.
Data Files
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. Aug. 2023 *******************************************************!) 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) if not getparam("OCIsuccess"): setioerr("Database connection failed") 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)") | |||||||||||||||||||||||||||||
© Copyright 2023 Fair Isaac Corporation. |