Can't retrieve schema through JDBC

One Star

Can't retrieve schema through JDBC

We have set up a JDBC connection to a Progress/OpenEdge database, but when trying to retrieve schema objects nothing shows up. We can enter a value in the name filter, but when we hit Next no schemas appear in the 'list' of schemas (table structures) to retrieve. The tables can be queried through the SQL Builder window (Edit Queries) - we just can't retrieve their definitions.
We think this is because the userName we connect with is not the owner of the tables, but has privileges on them. Is there a way to connect as ?userName? and see the tables owned by ?owner?? What options do we have?

TOS 4.1.1
Any ideas?
Employee

Re: Can't retrieve schema through JDBC

First of all, can you try to use the owner as a userName to retrieve the table definition and see if that works.
If that works, I advice to use this userName who is the owner for the step of Retrieve the table definition.
Then when the step of Retrieve schema is done, you can edit the connection and reuse again your favorite userName.

Second solution is to find in the JDBC documentation of Progress; what's the correct JDBC URL to connect and specify the Schema you want to use as the default schema. Adding this into the JDBC URL can help you to focus your retrieve action on the correct schema catalog.
Best regards.
One Star

Re: Can't retrieve schema through JDBC

Thanks for the quick response.
We tried adding schema to the URL, but that didn't help.
jdbc:datadirectSmiley Surprisedpenedge://<IP>:<PORT>;databaseName=SUBS;schema=PUB
When creating the database connection there is a warning message at the top of the window that says "Schema must be specified". Can you tell me how to do that? Which field should it be in? What might the syntax be?
Thanks
One Star

Re: Can't retrieve schema through JDBC

Did you ever get this to work?
Community Manager

Re: Can't retrieve schema through JDBC

Hi mhedrich
Have you tried cantoine's solution to retrieve schema use the owner as a userName to retrieve the table definition, you can't see the owner of table through Talend, you could find its information on database server.
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Can't retrieve schema through JDBC

Did you get this to work? I have the same problem and would be happy about a solution.
Best regards
Roman
Six Stars

Re: Can't retrieve schema through JDBC

General JDBC
JDBC URL -> jdbc:datadirectSmiley Surprisedpenedge://100.10.10.10:20000;databaseName=DATABASE;schema=PUB
Driver jar -> C:\skyward\dlc\java\openedge.jar
Class name -> com.ddtek.jdbc.openedge.OpenEdgeDriver
User name -> user
Password -> password
Mapping file -> mysql_id
The above worked for me in retrieving the table schemas and building (and running) any SQL.
However, when used within a job I get the following error:
Exception in thread "main" java.lang.Error: java.lang.NoClassDefFoundError: com/ddtek/jdbc/base/BaseDriver
at jdbc_connectivity_test.jdbc_connect_to_db_0_1.JDBC_Connect_to_db.tJDBCInput_2Process(JDBC_Connect_to_db.java:701)
at jdbc_connectivity_test.jdbc_connect_to_db_0_1.JDBC_Connect_to_db.runJobInTOS(JDBC_Connect_to_db.java:872)
at jdbc_connectivity_test.jdbc_connect_to_db_0_1.JDBC_Connect_to_db.main(JDBC_Connect_to_db.java:746)
Caused by: java.lang.NoClassDefFoundError: com/ddtek/jdbc/base/BaseDriver