FICO
FICO Xpress Optimization Examples Repository
FICO Optimization Community FICO Xpress Optimization Home
Back to examples browserPrevious exampleNext example

Reading sparse data from text files, spreadsheets and databases, and from memory

Description

Suppose we want to read in data of the form

i, j, value(i,j)

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

  1. differently formatted text files (example trio.mos described in the 'Mosel User Guide', Section 3.4 Reading sparse data): using 'initializations from', 'readln', or 'diskdata',
  2. spreadsheets or databases (examples duo.mos, duosheet.mos, duooci.mos, duoexc.mos, and duodd.mos described in the Xpress Whitepaper 'Using ODBC and other database interfaces with Mosel') ,
  3. the application (C/Java) executing the Mosel model (examples datainputc.mos and datainputj.mos
into different arrays, and using writeln show that the arrays hold identical data.



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.

Using initializations from the extended filename will take a form like "mmoci.oci:myname/mypassword@dbname" and it is also possible to work with PL/SQL statements like the following:

 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

  • Static modules: data exchange between the calling application and a model via custom functions, using the Mosel Native Interface - see the example of embedding the cutting stock problem in the Whitepaper 'Embedding Optimization Algorithms'.
  • Custom format/custom data sources: the Mosel Native Interface allows the user to implement his own I/O drivers (to be used with initializations blocks and all other Mosel functionality involving file access, such as matrix export or model compilation). Possible uses of such user-written drivers include encoding/decoding of data on the fly or compression/decompression of data. See the Xpress Whitepaper 'Generalized file handling in Mosel' for the complete code and documentation of an example implementing a compression driver with the zip library.

    Furthermore, the Mosel IO driver pipe may be used to recover the output of an external program. See the section on I/O drivers in the 'Mosel Libraries Reference Manual' for more information.



Further explanation of this example: Whitepapers 'Using ODBC and other database interfaces with Mosel' and 'Generalized file handling in Mosel'


Source Files

Data Files





datainout.c

/*******************************************************
   Mosel Example Problems
   ====================== 

   file datainout.c
   ````````````````
   Exchanging data between model and host application
   through a static module.
   - Sparse data format -
   
   (c) 2008 Fair Isaac Corporation
       author: S.Heipcke, Mar. 2006
********************************************************/

#include <stdio.h>
#include <stdlib.h>
#include "xprm_mc.h"
#include "xprm_ni.h"

typedef struct
{                                 /* Initial values for array 'data': */
 int index_i,index_j;             /*   index names */
 double value;                    /*   data value */
} s_mydata;

static s_mydata data[]={{1,1,12.5}, {2,3,5.6}, {10,9,-7.1}, {3,2,1}};

s_mydata dataout[9];


/* Initialization function of the module 'datainout' */
static int datainout_init(XPRMnifct nifct, int *interver,int *libver,
		XPRMdsointer **interf);

/*****************/
/* Main function */
/*****************/
int main()
{
 XPRMmodel mod;
 int result;
 char params[120];

 if(XPRMinit())                   /* Initialize Mosel */
  return 1;

 /* Register 'datainout' as a static module (=stored in the program) */
 if(XPRMregstatdso("datainout",datainout_init))
  return 2;

 /* Parameters: the address of the data tables and their size */
 sprintf(params,"INDATA='%p',INSIZE=%d,OUTDATA='%p',OUTSIZE=%d", 
         data,sizeof(data)/sizeof(s_mydata), 
	 dataout,sizeof(dataout)/sizeof(s_mydata));

 if(XPRMexecmod(NULL, "datainout.mos", params, &result, &mod))
  return 3;                       /* Execute the model file */

 return result;
}

/************************* Body of the module 'datainout' ******************/

static int dt_readdata(XPRMcontext ctx,void *libctx);
static int dt_writedata(XPRMcontext ctx,void *libctx);

static XPRMdsofct tabfct[]=
        {
         {"readdata",1000,XPRM_TYP_NOT,3,"AII.rsi",dt_readdata},
         {"writedata",1001,XPRM_TYP_NOT,3,"AII.rsi",dt_writedata}
        };

static XPRMdsointer dsointer=
        {
         0,NULL,
         sizeof(tabfct)/sizeof(XPRMdsofct),tabfct,
         0,NULL,
         0,NULL
        };

static XPRMnifct mm;             /* To store the mosel function table */

/*****************************************/
/* Initialization function of the module */
/*****************************************/
static int datainout_init(XPRMnifct nifct, int *interver,int *libver,
		XPRMdsointer **interf)
/* The following header is required to compile 'datainout' as a DSO file:
DSO_INIT datainout_init(XPRMnifct nifct, int *interver,int *libver,
		XPRMdsointer **interf)
*/
{
 mm=nifct;                      /* Save the table of functions */
 *interver=XPRM_NIVERS;         /* The interface version we are using */
 *libver=XPRM_MKVER(0,0,1);     /* The version of the module: 0.0.1 */
 *interf=&dsointer;             /* Our interface */

 return 0;
}

/*********************************************************/
/* Initialize an array with data in C:                   */
/*  readdata(array(range,range) of real,string,integer)  */
/*********************************************************/
static int dt_readdata(XPRMcontext ctx,void *libctx)
{
 XPRMarray arr;
 XPRMstring adr_s;
 s_mydata *adr;
 int indices[2],siz,i;

 arr=XPRM_POP_REF(ctx);             /* The array */
 adr_s=XPRM_POP_STRING(ctx);        /* Data location (as a string) */
 siz=XPRM_POP_INT(ctx);             /* Data size */
 sscanf(adr_s, "%p", &adr);         /* Get the address from the string */

 for(i=0;i<siz;i++)
 {
  indices[0]=adr[i].index_i;
  indices[1]=adr[i].index_j;
  mm->setarrvalreal(ctx, arr, indices, adr[i].value);
 }
 return XPRM_RT_OK;
}

/**********************************************************/
/* Initialize a C array with data from the model:         */
/*  writedata(array(range,range) of real,string,integer)  */
/**********************************************************/
static int dt_writedata(XPRMcontext ctx,void *libctx)
{
 XPRMarray arr;
 XPRMstring adr_s;
 XPRMset ndxset;
 s_mydata *adr;
 int indices[2],i,siz;

 arr=XPRM_POP_REF(ctx);             /* The array */
 adr_s=XPRM_POP_STRING(ctx);        /* Data location (as a string) */
 siz=XPRM_POP_INT(ctx);             /* Data size */
 sscanf(adr_s, "%p", &adr);         /* Get the address from the string */

 i=0;                               /* Print out values read from the model */
 if(!mm->getfirstarrtruentry(arr,indices))   /* Get first index tuple */
  do
  {                                 /* Get the array value */
   mm->getarrval(arr, indices, &(adr[i].value));
   adr[i].index_i=indices[0];       /* Get the indices */
   adr[i].index_j=indices[1];
   printf(" A(%d,%d): %g\n", adr[i].index_i, adr[i].index_j,
         adr[i].value);
   i++;	 
  } while(!mm->getnextarrtruentry(arr,indices) && i<siz);  /* Get next index */
 return XPRM_RT_OK;
}

Back to examples browserPrevious exampleNext example