Hello Daniel,
unfortunately, I would kindly like to ask you for additional support with regards to my issue.
I now have a feasible model, which is running without any problems if I manually provide the data input in the dat. file. Additionally I have validated that my DB connection setup with UCanAccess is running by depolying the example that Viu Long Kong supplied on
GitHub. But, if I use my ms access db connection to supply the input data, oplrun keeps interrupting the process while "reading from the database" at the same spot. I have checked that all the data types that I am using in Access match the declarations in .mod file, I have erased all German umlauts from my syntax and I have bracketed SQL statements when needed (e.g. if "_" is part of the table name). I have attached all necessary mod and dat files, the databases, the necessary components for setting up the database connection and cmd-logs of Viu Long Kong's example and mine.
I would kindly like to ask you for your recommendations on how to proceed.
Thank you very much for your endeavour in advance and I am looking forward to your feedback!
------------------------------
Frank Mildner
------------------------------
Original Message:
Sent: Thu July 02, 2020 03:04 AM
From: Frank Mildner
Subject: CPLEX 12.9.0 connection to Access DB
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
Original Message:
Sent: Wed July 01, 2020 09:37 AM
From: Daniel Junglas
Subject: CPLEX 12.9.0 connection to Access DB
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
Original Message:
Sent: Wed July 01, 2020 09:29 AM
From: Frank Mildner
Subject: CPLEX 12.9.0 connection to Access DB
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
Original Message:
Sent: Wed July 01, 2020 04:31 AM
From: Daniel Junglas
Subject: CPLEX 12.9.0 connection to Access DB
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
Original Message:
Sent: Wed July 01, 2020 04:17 AM
From: Frank Mildner
Subject: CPLEX 12.9.0 connection to Access DB
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
Original Message:
Sent: Wed July 01, 2020 03:52 AM
From: Daniel Junglas
Subject: CPLEX 12.9.0 connection to Access DB
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
Original Message:
Sent: Wed July 01, 2020 02:51 AM
From: Frank Mildner
Subject: CPLEX 12.9.0 connection to Access DB
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
Original Message:
Sent: Wed July 01, 2020 02:08 AM
From: Daniel Junglas
Subject: CPLEX 12.9.0 connection to Access DB
Answers to your questions:
- I have no recommendation here.
- 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.
- 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
Original Message:
Sent: Tue June 30, 2020 05:26 AM
From: Frank Mildner
Subject: CPLEX 12.9.0 connection to Access DB
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
Original Message:
Sent: Mon June 29, 2020 10:04 AM
From: Daniel Junglas
Subject: CPLEX 12.9.0 connection to Access DB
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
Original Message:
Sent: Mon June 29, 2020 02:42 AM
From: Frank Mildner
Subject: CPLEX 12.9.0 connection to Access DB
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_integration; https://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
------------------------------
#DecisionOptimization