sql trigger HTTP GET procdure

[#1]

hey there,

I'm trying to make a http get call from within an after insert trigger by using a stored procedure.

All I can get is an error message http://dcx.sybase.com/1100/en/saerrors_en11/errm187.html

Here is my procedure

ALTER PROCEDURE "dba"."push_dcn"(IN reqid VARCHAR(4), msgid INTEGER)
url 'http://win-utt00kfdfei:8000/dcn/DCNServlet?cmd=wf&security=admin&domain=default&username=supAdmin&password=s3pAdmin&domain=default&package=vacationrequest:1.0&dcn_request={%22op%22:%22:upsert%22,%22id%22:%22!msgid%22,%22to%22:%22supAdmin%22,%22subject%22:%22New%20Vacation%20Request%20%28ID_!reqid%29%22,%22from%22:%22supAdmin%22,%22body%22:%22%22,%22received%22:%222012-07-12T10:07:45+05:00%22,%22read%22:false,%22priority%22:true}' 
type 'HTTP:GET';

And this is my trigger

ALTER TRIGGER "VacationReqTrigger" after INSERT
ON "dba"."VacationRequests"
for each row
BEGIN

CALL "dba"."push_dcn"("reqid" = '100',"msgid" = 2221);

END

[Former Member]

hi,

i've exact the same problem by trying to do a CALL "http_procedure" on an trigger.

Your solution with

SELECT * FROM "dba"."push_dcn"("reqid" = '100',"msgid" = 2221);

or

SELECT  "Body" FROM "dba"."push_dcn"("reqid" = '100',"msgid" = 2221);

doesn't work for me as well. the error code i get is 946 (see: http://dcx.sybase.com/1100/en/saerrors_en11/errm946.html). Any thoughts on that?

Or is this generally a bad idea to do an http call within a trigger, and do it some other way?

thx in advance! Mario

[MarkCulp]

As Breck has indicated, your trigger cannot generate a result set - you need to consume the result set generated by the procedure call so that it does not propagate from the trigger into the statement context that caused the trigger to be called.

For example:

SELECT *
  INTO #temp
  FROM "dba"."push_dcn"("reqid" = '100',"msgid" = 2221);

This stores the result set from the call to push_dcn into the (automatically generated) temporary table #temp.

[Former Member]

ahh, i see - thanks! in the meantime i read the above mentioned artice: http://www.sybase.com/detail?id=1093467 where it is done like this: SET xmlResult = (SELECT C2 From sp_GetQuoteYahoo() with (C1 long varchar,C2 long varchar) WHERE C1 = 'Body');

this works out for me as well.

bye