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
------------------------------
Original Message:
Sent: Tue November 16, 2021 05:15 AM
From: Viu Long Kong
Subject: Execute StoredProcedure (MSSQL) in OPL 20.1
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
Original Message:
Sent: Sat November 13, 2021 05:04 PM
From: Sabri Suyunu
Subject: Execute StoredProcedure (MSSQL) in OPL 20.1
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