Decision Optimization

Decision Optimization

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


#Analytics
#DecisionOptimization
#DecisionOptimization
 View Only

  • 1.  Connection to SQL server thru CPLEX 20.1

    Posted 12/23/20 01:16 PM

    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


  • 2.  RE: Connection to SQL server thru CPLEX 20.1

    Posted 01/05/21 05:34 AM
    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
    ------------------------------



  • 3.  RE: Connection to SQL server thru CPLEX 20.1

    Posted 01/05/21 08:06 AM
    Hi Andrew,

    I appreciate your suggestions and inputs. I agree with you about DBLink; it was hassle free and so simple to connect to SQL server and Oracle.
    In the company I work for, optimization team used CPLEX 12.10 to connect to SQL server and Oracle via a java script written by IBM. The performance is fine and close to DBLink.

    We have been told that CPLEX 20.1 offers good database connectivity features, for now I am struggling to get it work. Besides that, if I have to worry about the performance then I will definitely think twice before spending more time on the tests. I will probably wait until more people try the new version to move forward. In the meantime, perhaps there will be a new release with better documentation and easy connectivity set-up (like DBLink).

    Thanks again for the feedback.

    Cheers


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

    Original Message


  • 4.  RE: Connection to SQL server thru CPLEX 20.1

    Posted 04/02/21 12:03 PM
    Hi Andrew
    Quick question: did you put in the sql sentence that data type conversion (i.e use of CAST function) since you were getting an error from cplex saying that "column is not floating point"?
    Thanks

    ------------------------------
    Cesar Guzman
    ------------------------------

    Original Message


  • 5.  RE: Connection to SQL server thru CPLEX 20.1

    Posted 04/06/21 08:24 AM
    Hi Cesar

    The original error message I was getting using SQL Server that required a CAST() to fix it was "Exception from IBM ILOG Concert: Cannot handle column type 6". This was where the SQL Server column was a float and I needed a 
    CAST(y AS REAL)​
    to prevent the error.

    I've just double checked this and found that whilst this was a problem in the 20.1 beta, it was fixed for the full 20.1 release and the CAST() is no longer needed.

    Note also that there is a more significant problem with Oracle even in the full 20.1 release - it can't handle integers regardless of how you CAST() them as Oracle stores integers as NUMBER(10,0) and this cannot be handled by the current code. IBM are aware of this and are in the process of fixing it. I don't know when the fix will be released.

    Hope that helps you
    Andrew

    ------------------------------
    Andrew Bullock
    ------------------------------

    Original Message


  • 6.  RE: Connection to SQL server thru CPLEX 20.1

    Posted 04/06/21 11:12 AM
    Thanks Andrew for your answer.
    What I found is that you should ensure that the mapping of the tuple in OPL to your database engine table definition should match exactly in terms of data type. It sounds obvious but in my opinion it should not be mandatory, for instance: if you define in your DB engine that certain column is an integer but in your tuple you have defined for that column a float as a data type, in this version of cplex 20.1, I had issues with it, despite an integer set is a subset of other real numbers set. The previous data interfaces (like dblink) did not have issues with it (including the truncation when you accidentally define a float in your database engine but the tuple is expecting an integer)

    ------------------------------
    Cesar Guzman
    ------------------------------

    Original Message


  • 7.  RE: Connection to SQL server thru CPLEX 20.1

    Posted 04/06/21 11:53 AM
    Cesar

    Yes, that makes sense - I saw those messages too when I was experimenting with 20.1 in December and now you've reminded me of them. At least the error message you got is reasonably clear, whilst the error messages from the core ODBC part are very uninformative - I asked IBM to improve these as well.

    I'm not sure if it is a good thing or a bad thing that it doesn't implicitly do type conversion any more. Either way, at least the issue is a known one now.

    Andrew

    ------------------------------
    Andrew Bullock
    ------------------------------

    Original Message


Related Content