Decision Optimization

 View Only
  • 1.  Execute StoredProcedure (MSSQL) in OPL 20.1

    Posted Sat November 13, 2021 05:05 PM
    Hello,
    I'm using OPL 20.1
    I couldn't execute Stored Procedure (MSSQL) in my dat file. I can read from table and write to table but can not execute sp.

    I try this solution but it doesn't work.
    ODBCConnection conn("DRIVER=[driver here];SERVER=[server here];Trusted_Connection=Yes;DATABASE=[db here];","exec [sql_procedure here]");

    In earlier version, we can execute sp with DBExecute but in version 20.1 I couldn't find a solution. Is there any solution or workaround?

    Regards

    ------------------------------
    Sabri Suyunu
    ------------------------------

    #DecisionOptimization


  • 2.  RE: Execute StoredProcedure (MSSQL) in OPL 20.1

    Posted Mon November 15, 2021 04:59 AM
    Dear Sabri,

    From what you describe. It doesn't seem a problem related to OPL but rather that your stored procedure cannot be found during the connection.
    Have you checked that it is properly registered in the SQL server :
    https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/view-the-definition-of-a-stored-procedure?view=sql-server-ver15

    I hope it helps,

            Renaud

    ------------------------------
    Renaud Dumeur
    ------------------------------



  • 3.  RE: Execute StoredProcedure (MSSQL) in OPL 20.1

    Posted Mon November 15, 2021 05:40 AM
    Dear Renaud,
    Thank you for your reply. However, your suggestion does not help.
    I have been working with MSSQL for 12 years. And unfortunately I will ignore your advice about verifying SQL server registration. If you have a solution suggestion that references the IBM site, not the Microsoft site, I would be very grateful.
    How can i get help from IBM's documentations on this topic? Or do you have any other solution idea? I have been always a happy user of Cplex but now struggling a lot with OPLs database connections and quite disappointed with the recent documentation and support.
    Regards,

    ------------------------------
    Sabri Suyunu
    ------------------------------



  • 4.  RE: Execute StoredProcedure (MSSQL) in OPL 20.1

    Posted Mon November 15, 2021 11:48 AM
    Dear Sabri,
    As you seem to confirm that the stored procedure is indeed registered, and given you correctly use the OBDCConnection  to emulate DBExecute, it would help to know what happens server side. Isn't there a journal/log where the error message describing the failing connection  is stored?
    Also, what error message does OPL provide (if any)?
    Cheers,

    ------------------------------
    Renaud Dumeur
    ------------------------------



  • 5.  RE: Execute StoredProcedure (MSSQL) in OPL 20.1

    Posted Mon November 15, 2021 12:05 PM
    The new Database support is describe here:
    https://www.ibm.com/docs/en/icos/20.1.0?topic=2010-new-database-support-in-opl

    ------------------------------
    Renaud Dumeur
    ------------------------------



  • 6.  RE: Execute StoredProcedure (MSSQL) in OPL 20.1

    Posted Mon November 15, 2021 08:54 AM

    I already ask the question to the IBM support. There is no replacement function for DBExecute.

    One way to emulate DBExecute() is to use the initial_update parameter for XXXConnection. For example:

    JDBCConnection conn("jdbc:driver:spec", "{call PROCEDURE}")

    The only solution I found to solve this problem was to use SAS Optmodel instead of Cplex to solve the problem I have to solve at this particular moment.



    ------------------------------
    Marko BLAIS
    ------------------------------



  • 7.  RE: Execute StoredProcedure (MSSQL) in OPL 20.1

    Posted Tue November 16, 2021 05:16 AM
    Hi Sabri,

    You are right, there is no equivalent to DBExecute in COS 20.1.
    The work around is to create a connection and use the initialization statement to invoke a stored procedure.

    https://docs.microsoft.com/en-us/sql/relational-databases/native-client-odbc-how-to/running-stored-procedures-call-stored-procedures?view=sql-server-ver15 specifies that the stored procedure syntax with ODBC is like:

    {? = CALL procname (?,?)}

    I believe that it should work if you use it as:

    ODBCConnection conn("DRIVER=[driver here];SERVER=[server here];Trusted_Connection=Yes;DATABASE=[db here];","{call procname}");

    Note that the limitation of this is that of course you cannot specify parameters to the stored procedure.

    Can you please give this a try ?

    ------------------------------
    Viu Long Kong
    ------------------------------



  • 8.  RE: Execute StoredProcedure (MSSQL) in OPL 20.1

    Posted Wed November 17, 2021 09:05 AM
    Hi,
    Thank you very much for your messages and support.
    I first would like to explain why database connection is so important in our case. We are currently working on an end-to-end solution with IBM. We first write the data that we receive thru the SAP integration into MSSQL. Then, after solving the optimization model (written in OPL), we write the results to MSSQL. We manage all these processes and scenarios with the custom UI we developed. For this reason, MSSQL connections in OPL are of great importance for the project.

    I've been using OPL since its very old versions. Even I wrote an article (in Turkish) about how SQL connections
    are made in various versions of OPL (https://medium.com/lumtify/ibm-cplex-mssql-database-connection-88b55117163a ).

    And here's how I solved running Stored Procedure in MSSQL:
    Both Renaud's and Viu's solutions posted are correct, but their implementation is slightly different from the DBExecute logic. In earlier versions, DBExecute could be run alone and trigger a stored Procedure. And there was no need to associate it with any tuple.

    But in 20.1, once a connection is established with ODBCConnection conn("DRIVER=[driver here];SERVER=[server here];Trusted_Connection=Yes;DATABASE=[db here];","exec [sql_procedure here]"), you need to associate this connection with an ODBCPublish or ODBCRead and use it. So you need to use this connection at least once. It makes sense if you want this procedure to run every time you read or write. But if you want to run it once, like me, it is not much practical.

    Then how I solved this problem: I created a dummy table in SQL and had it exec this procedure while assigning records to it. In this way, I got my issue resolved. I plan to prepare a Medium post about it :)
    Again thank you very much for your support.

    Regards

    ------------------------------
    Sabri Suyunu
    ------------------------------



  • 9.  RE: Execute StoredProcedure (MSSQL) in OPL 20.1

    Posted Wed November 17, 2021 10:19 AM
    Hi Sabri,

    There is a way to use stored procedures with parameters to read the data in CPLEX 20.1; it is bit tricky, but it works.

    I defined a dummy model (CreateSP) that creates the stored procedures that will be used by another model (Knapsack) to read the data via an OPL flow control script. 

    The stored procedures I used are parameterized. I have 2 parameters, you can expand it if you have more than 2 parameters. Let me know if you have any issues.

    The flow control script execute these models in sequence. The Knapsack model runs twice because I wanted to check if the looping works  when reading the data from stored procedures.

    Flow the below step to replicate the work I did
    1. Unzip the SQL script and run it on 
    2. Create an OPL project (e.g. Knapsack_SQL) and run it!

    Please do not use this trick in your article unless you mention my name!

    Thanks,

    ------------------------------
    Nourredine Hail
    ------------------------------