Hi Nourredine
I did some beta testing on 20.1 and certainly found that the ODBC connectivity was a little lacking in performance, error handling and documentation. I managed to get it to work on trusted connection (which is what I think you want) with this:
ODBCConnection MyDatabase("DRIVER=ODBC Driver 11 for SQL Server;SERVER=<SERVER_NAME>;UID=;Trusted_Connection=Yes;APP=<APP_NAME>;WSID=<SERVER_NAME>;DATABASE=<DATABASE_NAME>;","");
or probably better:
ODBCConnection MyDatabase("DRIVER=SQL Server Native Client 11.0;SERVER=<SERVER_NAME>;UID=;Trusted_Connection=Yes;APP=<APP_NAME>;WSID=<SERVER_NAME>;DATABASE=<DATABASE_NAME>;","");
Some of the fields are optional. The APP_NAME can be anything. And probably the WSID too. Not sure if this will cover your instance issue as I think I'm just connecting to the default instance here.
I found the performance is roughly 3 times slower than the old DBLink and IBM have said that there are no plans to develop native drivers for either SQL Server or Oracle, or though of course you can develop your own.
There were also challenges over data type conversion which caused meaningless error messages. I had to do things like this:
x_set from ODBCRead(Database, "SELECT CAST(y AS REAL) FROM z")
So it is possible to get it working but you have to kick it around a bit.
I tried the JDBC connectivity but it was even slower and so I gave up with it.
I'm hoping that some kind person will write and publish a native driver for both SQL Server and Oracle.
Andrew
------------------------------
Andrew Bullock
------------------------------
Original Message:
Sent: Wed December 23, 2020 09:47 AM
From: Nourredine Hail
Subject: Connection to SQL server thru CPLEX 20.1
Hello everyone,
I am struggling with the new database connection string syntax of CPLEX 20.1. I have used many connection strings in the past including the old one with DBLink and the most recent one via the IBM java script for CPLEX 12.7 and higher. I understand that the java script built by IBM to connect to databases is no more needed for CPLEX 20.1. But what is the exact syntax to connect to SQL server where the server name = serverName, instance name = InstanceName and the data base name = DatadaseName, assuming it is a trusted connection. What will be the connection string for the same situation except there is a user name and a password.
Let us assume I am using either JDBC or ODBC.
Can you please help?
Thanks.
------------------------------
Nourredine Hail
------------------------------
#DecisionOptimization