Decision Optimization

Decision Optimization

Delivers prescriptive analytics capabilities and decision intelligence to improve decision-making.

 View Only

  • 1.  CPLEX Optimization 20.1 - JDBCRead​ parameters

    Posted Wed May 26, 2021 12:19 PM
    Hello.

    I am working with OPL 20.1 on a Windows 10 a 64 bits laptop. I connect to an Oracle 11g database, using a JDBC driver (ojdbc6-11.2.0.4.jar). I use sentence JDBCRead for read data, and require passing parameters to the SELECT statement. ​For JDBCRead​, ¿how can you pass parameters to the statement? for example:

    Products from JDBCRead(conn, "SELECT ProductName, InitialStock, Profit FROM ProductTable Where Profit = ?");

    I have this problem:

    Exception in thread "main" java.sql.SQLException: ORA-01008: no todas las variables han sido enlazadas

            at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
            at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
            at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951)
            at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)
            at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227)
            at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
            at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:195)
            at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:876)
            at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1175)
            at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1296)
            at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1498)
            at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:406)
    *** ERROR[ENGINE_001] at 41:194-195 PruebaOracle.dat: Excepci¾n de IBM ILOG Concert: ORA-01008: no todas las variables han sido enlazadas
    .
    ### OPL exception: Fallo en el proceso.


    Thank you. 

    Best regard.

    ------------------------------
    Alexander Diaz
    ------------------------------

    #DecisionOptimization


  • 2.  RE: CPLEX Optimization 20.1 - JDBCRead​ parameters

    Posted Thu May 27, 2021 10:01 AM

    Hi Alexander,

     

    I had the same issue when I tested CPLEX 20.1, I figured out a way to pass parameters to SQL queries (SQL server or Oracle) by passing to a flow control a dummy model that creates SQL queries with parameters (it passes them as data elements).

     

    I created a test (SQLQueryWithParams,mod and SQLQueryWithParams,mod) with the same info you provided. I tested and it worked.

     

    You need to run the Flow_Control I attached by modifying the model and the file name.

     

    I hope this trick help.

     

    Regards,

     

    PS: BTW, I am an OPL users, not an IBMer.

     

     

     

    Description: Description: email-signature-image_0014_H_COC-CTR_Surprint_1_-TO-3__BIL_ENG.png

    Nourredine Hail, PhD in Applied Mathematics
    Senior Operations Research & Data Scientist

    Data Analytics Governance team
    Canadian Tire Corporation
    2111 Steeles Avenue East, Brampton, ON, L6T4L5
    Phone: 905.792.5983  
    nourredine.hail@cantire.com

    "Anyone who stops learning is old, whether at twenty or eighty. Anyone who keeps learning stays young. The greatest thing in life is to keep your mind young."  Henry Ford

     




    Original Message