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

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
------------------------------

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
------------------------------

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
------------------------------

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

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
------------------------------

Original Message:
Sent: Tue June 30, 2020 06:23 AM
From: Frank Mildner
Subject: CPLEX 12.9.0 connection to Access DB

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
------------------------------


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

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
------------------------------

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

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:

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
------------------------------


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

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:

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


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

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:

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


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

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:

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


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

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:

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


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

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:

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


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

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:

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


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

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:

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


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

Sorry, I don't have the resources to actually configure and run your example here. But I have these ideas to continue debugging:
1. Could you try to isolate the offending SQL statement? You could mix defining input manually and via database until you hit the blocking point. Maybe one can spot the problem once the statement is isolated.
2. Could you try with a small or dummy database? Just to make sure the problem is not the actual size of the data.

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

Original Message:
Sent: Sun July 19, 2020 07:27 AM
From: Frank Mildner
Subject: CPLEX 12.9.0 connection to Access DB

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:

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


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

Hello Daniel, 

thank for your your reply. My comments would be: 

1. If I substitute the offending statements, I continuously receive the same message for the remaining statements. That is why I would guess that sth in my syntax which works perfectly on MS Access is not being supported by the UCanAccess driver. I have approached the SorceForge community (provider of UCanAccess) in order to identify if I need to change sth in the SQL statments or in the configuration of the JDBC for UCannAccess to able to read my statements. Nonetheless, as reading and writing was perfectly possible with the Oil example from Mr Kong, I guess that the syntax is not the problem. 
2. Up to now I have not tried the smallest database possible. I will try this suggestion tomorrow. 

BR,



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

Original Message:
Sent: Thu July 23, 2020 09:38 AM
From: Daniel Junglas
Subject: CPLEX 12.9.0 connection to Access DB

Sorry, I don't have the resources to actually configure and run your example here. But I have these ideas to continue debugging:
1. Could you try to isolate the offending SQL statement? You could mix defining input manually and via database until you hit the blocking point. Maybe one can spot the problem once the statement is isolated.
2. Could you try with a small or dummy database? Just to make sure the problem is not the actual size of the data.

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


Original Message:
Sent: Sun July 19, 2020 07:27 AM
From: Frank Mildner
Subject: CPLEX 12.9.0 connection to Access DB

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:

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


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

Since you suspect there might be some unsupported syntax: did you try to simplify your SQL? Maybe so that it is just a plain query. No inner joins, no selection of columns using a dot. Just something like "SELECT boPName1 boPName2 FROM BOP". This is the most simple thing I can imagine and if this does not work then I am really out of ideas.
Once you get simple SQL working you can add more complicated things step by step until you hit a point at which things no longer work.

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

Original Message:
Sent: Fri July 24, 2020 09:15 AM
From: Frank Mildner
Subject: CPLEX 12.9.0 connection to Access DB

Hello Daniel, 

thank for your your reply. My comments would be: 

1. If I substitute the offending statements, I continuously receive the same message for the remaining statements. That is why I would guess that sth in my syntax which works perfectly on MS Access is not being supported by the UCanAccess driver. I have approached the SorceForge community (provider of UCanAccess) in order to identify if I need to change sth in the SQL statments or in the configuration of the JDBC for UCannAccess to able to read my statements. Nonetheless, as reading and writing was perfectly possible with the Oil example from Mr Kong, I guess that the syntax is not the problem. 
2. Up to now I have not tried the smallest database possible. I will try this suggestion tomorrow. 

BR,



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


Original Message:
Sent: Thu July 23, 2020 09:38 AM
From: Daniel Junglas
Subject: CPLEX 12.9.0 connection to Access DB

Sorry, I don't have the resources to actually configure and run your example here. But I have these ideas to continue debugging:
1. Could you try to isolate the offending SQL statement? You could mix defining input manually and via database until you hit the blocking point. Maybe one can spot the problem once the statement is isolated.
2. Could you try with a small or dummy database? Just to make sure the problem is not the actual size of the data.

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


Original Message:
Sent: Sun July 19, 2020 07:27 AM
From: Frank Mildner
Subject: CPLEX 12.9.0 connection to Access DB

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:

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


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