Caputuring Postgres exceptions in SQL stored proc

  • mc_coder
  • Topic Author
  • Visitor
  • Visitor
9 years 6 months ago #9853 by mc_coder
Hi,

I'm calling functions in a Postgres DB that make use of RAISE EXCEPTION. When I call the function from a SQL stored proc using linked server I don't seem to be able to capture the exception.

I'm tried calling my function as follows:

SELECT myfunc FROM OpenQuery(LINKEDSERVER,'select myfunc(25081) as myfunc ')

exec ('SELECT myfunc FROM OpenQuery(LINKEDSERVER,''select myfunc(25081) as myfunc'')')

Is it possible to capture the Postgres Exceptions when using linked servers and OpenQuery?

Regards,

Martin

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

  • mc_coder
  • Topic Author
  • Visitor
  • Visitor
9 years 6 months ago #9857 by mc_coder
Hi,

Is this forum still active? Just wondering if this is possible or should I be looking at another way to handle errors and exceptions.

Thanks,

Martin

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

More
9 years 6 months ago #9860 by Moderator
Hi Martin, sorry for the delay in answering your question. We were working on this issue today. The fix is almost ready. I just wanted to check how the function is called.
In our test we use:
create or replace function testnotimpl() returns int as
$$
begin
raise exception 'Not implemented' using errcode ='12345';
end;
$$
language plpgsql;

select * from openquery(PGSVR, 'select testnotimpl()');

If you are using a different way of calling the function please let us know. Fix will be available shortly.

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

More
9 years 6 months ago #9864 by Moderator
The issue was fixed. The following call:
select * from openquery(PGSVR, 'select testnotimpl()');
returns:
OLE DB provider "PGNP" for linked server "PGSVR" returned message "ERROR:  Not implemented
".
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "PGNP" for linked server "PGSVR".

Please contact Support on how to obtain the fixed module.

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

  • mc_coder
  • Topic Author
  • Visitor
  • Visitor
9 years 6 months ago #9865 by mc_coder
Hi,

Thanks for that. I've sent an email to support requesting the fix. I'll test and get back to you. When I call RAISE EXCEPTION in postgres I don't use the USING ERRCODE. Will that make a difference to this fix?

Regards,

Martin

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

More
9 years 5 months ago #9873 by Moderator
No, "USING ERRCODE" is optional. Moreover, the ERRCODE is returned from Postgres as 5 character SQL Error. Generally the SQL Error can be a combination of any characters, while OLEDB can only return integer "Native Errors". For this reason, OLEDB provider will not be able to return the error. So, I would recommend to not use "USING ERRCODE".

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

Time to create page: 0.124 seconds
Powered by Kunena Forum