WITH RECURSIVE clause supported by PGNP OleDb?

More
3 years 1 week ago #13887 by migele
migele created the topic: WITH RECURSIVE clause supported by PGNP OleDb?
We're looking into migrating our software products from Oracle to EnterpriseDB or Postgres 9.5, and are testing the setup. As part of the process we've converted Oracle's CONNECT BY clauses to WITH RECURSIVE clauses supported by Postgres.

When trying to execute a WITH RECURSIVE clause via the PGNP OleDb driver we're getting an 'Undefined table' error - like the temporary table would not be recognized by the PGNP OleDB driver. The driver version used is a Trial version PGNP-Postgres-SE-Trial-1.4.0.3420.x64.msi package.

Is WITH RECURSIVE clause supported?

The sql being executed is below for reference (sets up a nested site map) and works otherwise fine on pgAdmin/Postgres query tool, so the database itself is fine with it:

WITH RECURSIVE sm_parent
as
(SELECT MD_SITE_MAP.*, string(array[id]) AS path
FROM cdms_dev_eng.MD_SITE_MAP
where parent_id IS NULL
AND provider_name='XMLSiteMapProviderStripped'
and site_map_code='diabetes'
union all
SELECT sm_child.*, sm_parent.path||sm_child.id
FROM cdms_dev_eng.MD_SITE_MAP sm_child, sm_parent
where sm_child.site_map_code='diabetes'
and sm_child.provider_name='XMLSiteMapProviderStripped'
and sm_child.parent_id = sm_parent.id
)
select * from sm_parent
order by path;

Another pointer suggesting PGNP OleDb may not be fine with 'WITH RECURSIVE' is
http://stackoverflow.com/questions/2923 ... tiple-ctes

kr,
MS

Please Log in or Create an account to join the conversation.

More
3 years 5 days ago #13889 by Moderator
Moderator replied the topic: Re: WITH RECURSIVE clause supported by PGNP OleDb?
Hi!
thank you for letting us know about the issues!

We have reproduced the issues, including one with multiple CTEs mentioned on StackOverflow, and fixed them in build 3425. The fix will be officially released shortly. If you want to obtain a fixed module before the official release, please send request to support at pgoledb dot com.

Best regards!

Please Log in or Create an account to join the conversation.

Time to create page: 0.103 seconds
Powered by Kunena Forum