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 |
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 |
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 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.
(13 Jun '13, 08:35)
Mark Culp
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
(14 Jun '13, 02:18)
Mario David
|