Connection problem with JDBC driver Progress 10.1.C

Archived Topics
1

Hi folks,

I have just added a new Progress Database driver (JDBC driver Progress 10.1.C) in Ignition. However I am unable to connect to the progress database when I use this driver. The following error is displayed:

[color=#800040][b][color=#BF4040][color=#40BF40]org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory ([DataDirect][OpenEdge JDBC Driver][OpenEdge] Syntax error in SQL statement at or about " 1" (10713))

org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1225)org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:880)com.inductiveautomation.ignition.gateway.datasource.DatasourceImpl.getConnectionInternal(DatasourceImpl.java:189)com.inductiveautomation.ignition.gateway.datasource.DatasourceImpl.runTest(DatasourceImpl.java:145)com.inductiveautomation.ignition.gateway.datasource.DatasourceManagerImpl$FaultedDatasourceRetryer.run(DatasourceManagerImpl.java:1117)java.lang.Thread.run(Unknown Source)

java.sql.SQLException: [DataDirect][OpenEdge JDBC Driver][OpenEdge] Syntax error in SQL statement at or about " 1" (10713)

com.ddtek.jdbc.openedge.client.OpenEdgeClientRequest.prepareProcessReply(Unknown Source)com.ddtek.jdbc.openedge.client.OpenEdgeClientRequest.prepare(Unknown Source)com.ddtek.jdbc.openedge.OpenEdgeImplStatement.prepare(Unknown Source)com.ddtek.jdbc.openedge.OpenEdgeImplStatement.execute(Unknown Source)com.ddtek.jdbc.base.BaseStatement.commonExecute(Unknown Source)com.ddtek.jdbc.base.BaseStatement.executeQueryInternal(Unknown Source)com.ddtek.jdbc.base.BaseStatement.executeQuery(Unknown Source)org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)org.apache.commons.dbcp.PoolableConnectionFactory.validateConnection(PoolableConnectionFactory.java:332)org.apache.commons.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:1249)org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1221)org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:880)com.inductiveautomation.ignition.gateway.datasource.DatasourceImpl.getConnectionInternal(DatasourceImpl.java:189)com.inductiveautomation.ignition.gateway.datasource.DatasourceImpl.runTest(DatasourceImpl.java:145)com.inductiveautomation.ignition.gateway.datasource.DatasourceManagerImpl$FaultedDatasourceRetryer.run(DatasourceManagerImpl.java:1117)java.lang.Thread.run(Unknown Source)[/color][/color][/b][/color]

Does anyone have any suggestions on where I am going wrong? Thanks in advance for all your help.

1 Like
2

Update: Issue is now resolved and the jdbc driver is connection correctly. The following is what I did on my Win 2008 64 Bit environment:

  1. copy Openedge.jar file from progress folder “\OpenEdge\Java” into the “\user-lib\jdbc” folder of Ignition

2)Create a new Driver in Ignition (I called this driver Progress)

  1. Post the following to the Classname testbox:
    "com.ddtek.jdbc.openedge.OpenEdgeDriver "

  2. Post the following syntax to the URL format textbox:
    "“jdbc:datadirect:openedge://xxxxxx:xxxx;databaseName=xxxxx;defaultSchema=pub;Isolation Level =READ UNCOMMITTED;user = xxxxx;password =xxxxx”

  3. In the Default Validation post a simple Select Progress query for a table in the Database

  4. Default Translator used is MSSQL

7)Using this driver then set up your new connection to the Progress Database

2 Likes
3

Thanks for posting the resolution.

1 Like
4

For anyone else facing this issue, there is an alternate solution (without adding a new Progress driver). When you create the Progress driver in the Ignition Gateway initially and have “SELECT 1” as the validation query, it will carry over into any database connection you create with that driver.

As far as I know, “SELECT 1” throws an error, so the validation test fails (I ran it in Postman and got an error from Progress).

The solution for me ended up being the validation script that is associated with the connection, not the driver. This setting is hidden in the Databases -> Connections window on the Ignition gateway. You need to click “Show advanced properties” to find the connection’s Validation Query, not the driver’s validation query! The problem (in our case) was that I still kept getting the same “Syntax error in SQL statement at or about " 1” (10713)" error even though I changed the Driver Validation Query, I needed to also change the Connection Validation Query (in the Ignition Gateway).

3 Likes