sql trigger HTTP GET procdure

[Former Member]

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

[Breck_Carter]

I think you need to do something with the result set returned by the procedure call; e.g., SELECT ... FROM push_dcn ( ... ).

See the page 19 in this Techwave presentation:

http://download.sybase.com/presentation/TW2005/SQL513.pdf

---

HTTP

TYPE attribute: ‘HTTP’, ‘HTTP:GET’, or ‘HTTP:POST’

Function variant

returns the body of the HTTP response

Procedure variant

returns all relevant information, including headers, from the HTTP response as attribute/value pairs in a two-column result set

HTTP status and HTTP body are returned as “Body” and “Status” attributes

---

Also see this: http://www.sybase.com/detail?id=1093467

For these and other FABULOUS documents, do a search from this page: http://sqlanywhere.blogspot.ca/2012/03/tales-from-doc-face.html

[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