Decision Optimization

Expand all | Collapse all

CPLEX 12.9.0 connection to Access DB

  • 1.  CPLEX 12.9.0 connection to Access DB

    Posted 9 days ago

    Dear Community,

     

    I am using CPLEX 12.9.0  and would like to connect my dat. file to a Microsoft Access DB.

    Unfortunately, I am unable to implement the suggested JDBC connection, as I can not identify the necessary steps for me to implement form the available documentation (https://github.com/IBMDecisionOptimization/OPL-jdbc-data-source/tree/master/examples/studio_integrationhttps://medium.com/@AlainChabrier/connect-opl-models-with-databases-217baed0935c). 

    Up to now I receive the error "could not include jdbc.js", when trying to implement the code in the dat.file as recommended in the documentation. 

    Could you please advice me on how to best set up the connection. 

    Thank you very much for your support!


     



    ------------------------------
    Frank Mildner
    ------------------------------


  • 2.  RE: CPLEX 12.9.0 connection to Access DB

    Posted 9 days ago
    This suggests that you did not store the jdbc.js file in the correct place. Where exactly did you store it? Can you try to store it in the project directory and/or the current working directory?

    ------------------------------
    Daniel Junglas
    ------------------------------



  • 3.  RE: CPLEX 12.9.0 connection to Access DB

    Posted 9 days ago
      |   view attached
    Hello Daniel, 

    thank you for your fast reply.

    I tried to implement the suggested oil-data example using mysql, but received the error message "impossible to load model". 

    I have attached my project file and would be very thankful if you could help me to sort it out. 

    Additionally I would have the following questions with regards to establishing a JDBC connection to Access DB: 

    1. Which JDBC Driver would you recommend to use (Ucan Access?)? 
    2. How can I identify the connection string for the JDBC driver, that I need in the dat.file? 
    3. Which adjustments do I need to make in the java script to access my Access DB?

    Thank you very much for your support. 

    Best Regards, Frank
     


    ------------------------------
    Frank Mildner
    ------------------------------

    Attachment(s)

    zip
    Integration.zip   2.18MB 1 version


  • 4.  RE: CPLEX 12.9.0 connection to Access DB

    Posted 9 days ago
    Edited by Frank Mildner 9 days ago
    One Additional Note: There already has been an incident, where a user tried to connect to Access DB via a UcanAccess JDBC driver, which did not work and the ticket on github was closed:

    https://community.ibm.com/community/user/datascience/communities/community-home/digestviewer/viewthread?GroupId=5557&MID=91052&CommunityKey=ab7de0fd-6f43-47a9-8261-33578a231bb7&tab=digestviewer

    Might it be an option to connect Access DB to MSsql and access the data via the running mssql JDBC connector?

    ------------------------------
    Frank Mildner
    ------------------------------



  • 5.  RE: CPLEX 12.9.0 connection to Access DB

    Posted 8 days ago
      |   view attached
    I have short update. I tried to establish the UcanAccess JDBC connection with several connector string options and always got the error message "Impossible to load model". I have attached my access db, all the java and OPL files in my project. 

    I would be very glad if you could give me an advice on how to fix the bug. 

    Thank you!

    ------------------------------
    Frank Mildner
    ------------------------------

    Attachment(s)



  • 6.  RE: CPLEX 12.9.0 connection to Access DB

    Posted 8 days ago
    Answers to your questions:
    1. I have no recommendation here.
    2. This is defined by the driver. Usually it is something like "jdbc:<drivername>:<path-and-args>". If you google "jdbc ucanaccess connection string" then you can find a lot of tutorials that describe how to setup the connection string. The first thing I found is this https://community.yellowfinbi.com/knowledge-base/article/how-to-use-ucanaccess-to-connect-to-access-databases-using-jdbc-only which gives example connection strings for Windows and Linux.
    3. As the instructions on the website say, you only have to edit the few first lines in jdbc.cs. The file jdbc.cs has three places that are marked "EDIT". These are the ones you have to edit. Everything else can stay the same.
    As for the "impossible to load model" error you mentioned in your other post: this is not necessarily related to problems with the data base. It could well be a syntax or logic error in your model definition. Please post the full (error) output in case the issue persists.

    ------------------------------
    Daniel Junglas
    ------------------------------



  • 7.  RE: CPLEX 12.9.0 connection to Access DB

    Posted 8 days ago
    Good Morning Daniel, 

    thank you for your reply. 

    In my search I found the same connection string, which I already implemented. I have also edited all three necessary fields in the jdbc.js - file. 

    Despite these changes I still receive "impossible to load model". I have attached the error with its properties in an attached jpg and a screen shot of my changes in the jdbc.js-file. If I should interpret the properties of the error, the error still refers to a flawed JDBC connection. 

    Could a  possible explanation  be that my database is not called custom_data_source? 

    Looking forward to your reply and assessment of the error message. 

    Best regards, Frank

    ------------------------------
    Frank Mildner
    ------------------------------



  • 8.  RE: CPLEX 12.9.0 connection to Access DB

    Posted 8 days ago
    Well, if you change the name of the database from the example then you also have to change the database name in your .dat file. But it looks like you already did that.

    What looks suspicious are your SQL statements. Are you sure this is valid SQL? Do these statements work outside of OPL? For example, for a select statement I would have expected a "FROM" somewhere in the statement.

    Can you also try running your project using oplrun.exe instead of the IDE? That may give more verbose error messages.

    ------------------------------
    Daniel Junglas
    ------------------------------



  • 9.  RE: CPLEX 12.9.0 connection to Access DB

    Posted 8 days ago
    Hi Daniel, 

    thanks for the remark on the SQL statements. When migrating the dat.file from a 12.6.3 to a 12.9 version, I searched and replaced all "from"-statements in the command statement, but unintendedly also deleted the "from" in the SQL statement. I have now re-inserted the from statements. 

    I ran the mod and dat file in oplrun in the command line. Apparently the mistake appears when executing line 68 of the jdbc.js where the lib and the jar are imported to OPL. 

    How would you assess the error message? 

    Best Regards, Frank

    ------------------------------
    Frank Mildner
    ------------------------------

    Attachment(s)

    js
    jdbc.js   3K 1 version


  • 10.  RE: CPLEX 12.9.0 connection to Access DB

    Posted 8 days ago
    You must set the JAVA_HOME environment variable. This must point to a valid Java runtime environment (in a valid java runtime environment there should be a bin\java.exe file).

    ------------------------------
    Daniel Junglas
    ------------------------------



  • 11.  RE: CPLEX 12.9.0 connection to Access DB

    Posted 7 days ago
    I have managed to set the JAVA_HOME  environment variable with a 64-bit Version of JRE. After running oplrun in cmd i recieve a looped message telling me that no sutiable driver could be found for the connection string (see attached txt. file showing the cmd output) 

    Websites that might be helpful in solving the issue: 

    https://community.yellowfinbi.com/knowledge-base/article/how-to-use-ucanaccess-to-connect-to-access-databases-using-jdbc-only
    https://www.benchresources.net/jdbc-msaccess-database-connection-steps-in-java-8/

    Looking forward to your assessment of the error. 

    Thank you  very much for all your support. 



    ------------------------------
    Frank Mildner
    ------------------------------

    Attachment(s)

    txt
    OUTPUT.txt   40K 1 version


  • 12.  RE: CPLEX 12.9.0 connection to Access DB

    Posted 7 days ago
    Are the DLLs for your driver in the PATH environment variable?
    Does the JAR configured in jdbc.js point to the correct and existing JAR for your JDBC driver? If in doubt use an absolute path and double check that the JAR is located in exactly that place.

    Does the driver work when you call it from a regular Java program? That would be the first thing to test to make sure this is not a problem with the driver.

    ------------------------------
    Daniel Junglas
    ------------------------------



  • 13.  RE: CPLEX 12.9.0 connection to Access DB

    Posted 7 days ago
    Good Morning Daniel, 

    following your recommendations and a documentation of @Viu Long Kong on github (https://github.com/IBMDecisionOptimization/OPL-jdbc-data-source/tree/master/src/test/examples/UCanAccess), where he documented how he had implemented the JDBC connection via UCanAccess , I was able to get the connection running. Key factors where: 
    • Not only refering to  the UcanAccess.jar as a jdcb driver but to all the lib-jars that the UcanAccess driver uses in the variable jdbc_drivers of the jdbc.js - file
    • Setting up the PATH environment variable for the JDCB Ucan Access driver. 

    Now its up to me to solve the SQL - bugs ;)

    Thank you very much for your enduring and very helpful support. 

    PS: Maybe you can document the solution of @Viu Long Kong on the github ticket that was already closed  and on the other thread in this community that was dealing with the issue of connecting MS Access to CPLEX 12.7 and higher versions via UCanAccess.


    ​​​

    ------------------------------
    Frank Mildner
    ------------------------------