| ||||||||||||||||||||||||||||
Reading sparse data from text files, spreadsheets and databases, and from memory Description Suppose we want to read in data of the form
from a file, setting up a dynamic array A(range, range) with just the A(i,j)= value(i,j) for the pairs (i,j) which exist in the file. Here is an example which shows different ways of doing this. We read data from
Accessing spreadsheets and databases With initializations from data from spreadsheets or databases is read in a similar way as text files are accessed; switching between data sources can be done by replacing the I/O driver name and the data source in the extended filename, such as "mmsheet.excel:data.xls" to access the spreadsheet data.xls or "mmodbc.odbc:data.mdb" to read from the MS Access database data.mdb and the string "mmodbc.odbc:DSN=mysql;DB=data" could be used to access a mysql database named data. Instead of using an initializations block, we may read data from a database into a Mosel model by writing out the corresponding SQL statements (this format allows fine-tuning of the queries and gives access to a larger range of commands, in particular when working with databases). When developing applications with ODBC/SQL statements, the user is advised to use the following two parameter settings to obtain error messages and debugging information from ODBC: setparam("SQLverbose", true) setparam("SQLdebug", true) The module mmoci (requires a separate license) defines a software-specific interface to Oracle databases that is used in a similar way as the ODBC connection. OCIlogon('myname/mypassword@dbname') OCIexecute("select Index_i,Index_j,Value from MyDataTable", A5) OCIlogoff Data input in memory using I/O drivers Using initializations from it is also possible to read data into a Mosel model from the calling application (C/C++, C#, Java) using a combination of the I/O drivers mem (data held in memory) and raw driver (data in binary format) in C, drivers dotnet and dotnetraw in C# programs, and drivers java and jraw in Java programs. Other options for data input Other possibilities of inputting data into a Mosel model include
Further explanation of this example: Whitepapers 'Using ODBC and other database interfaces with Mosel' and 'Generalized file handling in Mosel'
Source Files By clicking on a file name, a preview is opened at the bottom of this page. Data Files
duooci.mos (!****************************************************** Mosel Example Problems ====================== file duooci.mos ``````````````` Two ways of reading sparse data tables from Oracle databases. (c) 2008 Fair Isaac Corporation author: S. Heipcke, Jan. 2007, rev. Aug. 2023 *******************************************************!) model "Duo input (OCI)" uses "mmoci" parameters CNCT = 'myname/mypassword@dbname' end-parameters declarations A4, A5: dynamic array(range,range) of real end-declarations ! First method: use an initializations block with the oci driver initializations from "mmoci.oci:debug;"+CNCT A4 as 'MyDataTable' end-initializations ! Second method: use SQL statements OCIlogon(CNCT) if not getparam("OCIsuccess"): setioerr("Database connection failed") OCIexecute("select Index_i,Index_j,Value from MyDataTable", A5) OCIlogoff ! Now let us see what we have writeln('A4 is: ', A4) writeln('A5 is: ', A5) end-model ************************************************** ! Creation of the input table in an Oracle database: declarations tsucc: array ({false,true}) of string end-declarations tsucc(false):="failed"; tsucc(true):="succeeded" OCIexecute("create table MyDataTable (Index_i integer, Index_j integer, Value float)") writeln(" - Create MyDataTable (",tsucc(getparam("OCIsuccess")),")") declarations A: array(range,range) of real end-declarations A(1,1):=12.5; A(2,3):=5.6; A(10,9):=-7.1; A(3,2):=1 OCIexecute("insert into MyDataTable (Index_i, Index_j, Value) values (:1, :2, :3)", A) writeln(" - Insert values in MyDataTable (",tsucc(getparam("OCIsuccess")),",", getparam("OCIrowcnt"),'/',getparam("OCIrowxfr")," rows)") | ||||||||||||||||||||||||||||
© Copyright 2023 Fair Isaac Corporation. |