DDC and OpenEdge On-Premises: Accessing 'pub' schema namespace and "ABL friendly" tables and avoiding Error 7519 - Forum - DataDirect Cloud - Progress Community

DDC and OpenEdge On-Premises: Accessing 'pub' schema namespace and "ABL friendly" tables and avoiding Error 7519

 Forum

DDC and OpenEdge On-Premises: Accessing 'pub' schema namespace and "ABL friendly" tables and avoiding Error 7519


  • NOTE:  The issue noted below is a good description of how SQL clients generally have issues with PUB schema namespace, but it is no longer (starting in April 2014) for using DDC with OpenEdge On-Premises databases.  DDC now automatically defaults to the PUB schema, so you do NOT need to add the SET SCHEMA "pub" Initiatization String.  The Default Schema is set to "pub" for new OpenEdge data sources.


    I was playing with OpenEdge On-Premise Adapter from DataDirect Cloud and was running into the issue that I wanted to access pre-existing ABL OpenEdge Tables from JDBC.    These are, of course, in the 'pub' schema namespace.

    The ISSUE:

    • DDC does correctly show the Tables in the PUB schema in the SQL Tester.   But you can't do simple SQL like:    
               SELECT * FROM Customer.
    • If you do the above, you get a SQL exception:
               Table/View/Synonym not found (7519)
    • You need to specify the schema namespace:  
               eg.  SELET * FROM pub.Customer
    • This is fine when you can control the client application using JDBC/ODBC, but in my case, I was using a cloud tool and could not affect the SQL generated.

    Solution:

    • I found I could use the "Initialization String" capabiity in the Advanced tab for my OpenEdge On-Premise Data Source.
    • If you add this, then you more external tools work as expected:
      • Initialization String:  SET SCHEMA 'pub'
    • This SQL will now work as desired:
      • SELECT * FROM Customer.

    Improved Solution:

    • In December 2013, DataDirect Cloud updated the OpenEdge and OpenEdge On-Premise Data Sources to support a new Advanced Configuration Property:
             Default Schema: pub
    • Setting this to 'pub' (no quotes) has the same effect as the Initialization String without passing the SQL.   It impacts the connection directly.

  • Note that you may also have to have the dba grant select privilege to the account you use for the JDBC connection. The default is that SQL users have no access rights to the tables in the PUB schema until they are explicitly granted.