Skip to Content
1

Calling web service client procedure from within DB trigger

Nov 30, 2017 at 10:29 AM

66

avatar image

Hello, all

I get "Illegal cursor operation attempt" when calling web service client procedure form within a trigger.

I found no reference to this limitation in the documentation or in the web search.

Basically, I have a table with several data fields and a "send" flag. When "send" flag becomes 1, trigger fires, which calls web service client procedure.

The procedure executes successfully, but the operation stops with the error and the "send" field is not updated.

Can you please, tell me if there is a limitation of launching web service client procedure from a trigger?

Thank you

10 |10000 characters needed characters left characters exceeded

Could you please how us the trigger code? Is that a BEFORE or AFTER trigger?

And as always: What SQL Anywhere version do you use?

0
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Arcady Abramov Nov 30, 2017 at 06:50 PM
0

This is an after column update trigger.

The only code in there is the call to the web client procedure.

Version is 16

Share
10 |10000 characters needed characters left characters exceeded
Volker Barth Dec 01, 2017 at 10:14 AM
0

With v16.0.0.2546 I certainly can call web client functions or procedures within a trigger.

----

When you call a web client procedure, how do you "consume" the result set / return value in your trigger? Note that within a trigger each result set of a select or call statement must be consumed because the trigger cannot "return it" itself. You dan do so by using "select into" or the like or by using a cursor loop to fetch each row of the returned result set.

The error message "Illegal cursor operation attempt" seems to imply a return set is not correctly consumed. I.e. I do also get this message when I simply "call" the web client procedure.

Here's a sample using the ECB's Euro exchange rates:

-- Web client function for Euro exchange rates as published by the ECB
create function WCF_ECB_DailyExchangeRates() returns XML
URL 'HTTP://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml' 
type 'HTTP:GET';
select WCF_ECB_DailyExchangeRates();

-- Web client function for Euro exchange rates as published by the ECB
create or replace procedure WCP_ECB_DailyExchangeRates()
result (Attribute long varchar, Value long varchar)
URL 'HTTP://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml' 
type 'HTTP:GET';


-- sample table with trigger
create table T_Test
(
  pk_Test  int default autoincrement,
  flagSend  bit default 0
);

create or replace trigger TIU_Test
  before insert, update on T_Test
  referencing new as N
  for each row 
begin
  if N.flagSend = 1 then
     -- first attempt: Call procedure without consuming the result set
     call WCP_ECB_DailyExchangeRates();
  end if;
end;

insert T_Test(flagSend) values (0); -- ok: No procedure call
insert T_Test(flagSend) values (1);
 -- error SQLCODE -187: "Illegal cursor operation attempt"


-- modified trigger: consume procedure result set
create or replace trigger TIU_Test
  before insert, update on T_Test
  referencing new as N
  for each row 
begin
  if N.flagSend = 1 then
     -- second attempt: Consume result set via FOR loop
     for crs as crsLoop cursor for
       call WCP_ECB_DailyExchangeRates()
     do
        message 'Attribute ' || Attribute || '...' to client; 
     end for;
  end if;
end;

insert T_Test(flagSend) values (1); -- OK now

-- re-modified trigger: consume function return value
create or replace trigger TIU_Test
  before insert, update on T_Test
  referencing new as N
  for each row 
begin
  if N.flagSend = 1 then
     -- third attempt: Consume function return value
     begin
         declare xmlValue xml;
         set xmlValue = WCF_ECB_DailyExchangeRates();
     end;
  end if;
end;

insert T_Test(flagSend) values (1); -- >OK, as well
Share
10 |10000 characters needed characters left characters exceeded
Arcady Abramov Dec 04, 2017 at 11:25 AM
0

My web call is a POST, not a GET.

However, this is now a purely academic question. I decided to implement a C# based WS and a application web client, so I do not use the triggers anymore.

Thank you for your time

Arcady

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Well, I don't think the behaviour would be different for a POST method call - AFAIK, each web client function/procedure does return "something" simply because it has to supply the response of the web server to the caller, and that "something" has to be consumed within the trigger.

0