How can I pass params to query in SQL JDBCRead?

Question

I'm working in a opl project. In my .dat file I have

px = 20
JDBCConnection in("jdbc:sqlite:my_database.db", "");
my_param from JDBCRead(in, "SELECT COL FROM TABLE WHERE COL2 <= 20");

I need:

my_param from JDBCRead(in, "SELECT COL FROM TABLE WHERE COL2 <= :px");

All variables are defined correctly in .mod file

Answer 1

Here an example where you set the string from .dat in the .mod

.mod

string paramread=...;
string parambuses=...;
string paramwrite=...;

tuple param
    {
    int nbKids;
    }

    {param} params=...;

    assert card(params)==1;

    int nbKids=first(params).nbKids;

    // a tuple is like a struct in C, a class in C++ or a record in Pascal
    tuple bus
    {
    key int nbSeats;
    float cost;
    }

    // This is a tuple set
    {bus} buses=...;

    // asserts help make sure data is fine
    assert forall(b in buses) b.nbSeats>0;
    assert forall(b in buses) b.cost>0;

    // decision variable array
    dvar int+ nbBus[buses];

    // objective
    minimize
     sum(b in buses) b.cost*nbBus[b];
     
    // constraints
    subject to
    {
     sum(b in buses) b.nbSeats*nbBus[b]>=nbKids;
    }

    tuple result
    {
       key int nbSeats;
       int nbBuses;
    }

    {result} results={<b.nbSeats,nbBus[b]> | b in buses};
    
    main
    {
      thisOplModel.generate();
      thisOpLModel.data.paramRead="nn"
;    }

.dat

SheetConnection s("zoo2.xlsx");


params from SheetRead(s,paramread); 
buses from SheetRead(s,parambuses);
results to SheetWrite(s,paramwrite);

and the main model to make this work

main
{
  var source = new IloOplModelSource("variablesheetreadstring.mod");
  var cplex = new IloCplex();
  var def = new IloOplModelDefinition(source);
  var opl = new IloOplModel(def,cplex);
  var data1=new IloOplDataElements();
  
  data1.paramread="params!A2";
  data1.parambuses="buses!A2:B3";
  data1.paramwrite="buses!E2:F3";
  
  opl.addDataSource(data1);
  
  var data2 = new IloOplDataSource("variablesheetreadstring.dat");
  opl.addDataSource(data2);
  opl.generate();
  if (cplex.solve()) {
     writeln("OBJ = " + cplex.getObjValue());
     opl.postProcess();

  } else {
     writeln("No solution");
  }
  

  opl.end();
  
  
}