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

asked 12 Jul '12, 09:46

MartinKraemer's gravatar image

MartinKraemer
46235
accept rate: 0%

edited 15 Mar '13, 19:49

Mark%20Culp's gravatar image

Mark Culp
22.7k9129266


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

permanent link

answered 12 Jul '12, 10:52

Breck%20Carter's gravatar image

Breck Carter
27.4k424585836
accept rate: 21%

edited 12 Jul '12, 10:58

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

permanent link

answered 13 Jun '13, 07:14

Mario%20David's gravatar image

Mario David
1
accept rate: 0%

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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×104
×59
×13
×8

question asked: 12 Jul '12, 09:46

question was seen: 2,324 times

last updated: 14 Jun '13, 02:18