The forum will be down for maintenance at some point between Friday, September 25, 2020 at 5pm PDT and Sunday, September 27, 2020 at 11:59 PDT. Downtime is unknown but will be minimized.

I would be grateful for some pointers:

I am trying to use the Web Service feature of SQL Anywhere 16.0.0.2076 to POST to a URL, however I am finding that often, even though the URL is correct, I get an error that states that it cannot connect to the URL. I know the URL is correct as if I try again, it works correctly. What I would like to be able to do is either handle the error and automatically retry, or find a way to use Exception Handling to do it myself.

If I set up a function with an invalid URL to deliberately generate the same error, such as:

CREATE FUNCTION "spaceman"."WebServiceTest"(in "iData" long varchar) 
returns xml
url 'http://www.mynonexistentwebservice.com'
type 'HTTP:POST:text/xml'

and then

select WebServiceTest('Hello')
I get:
Unable to connect to the remote host specified by
'http://www.mynonexistentwebservice.com'
SQLCODE=-981, ODBC 3 State="HY000"

How can I handle this error myself? I cannot put exception handling in the original function, and if I call this function with another procedure that contains the exception handling, it is ignored - I assume as it is not this second procedure that is causing the issue:

Create procedure mysecondproc()
begin
select WebServiceTest('Hello');
    exception
    when others then
    message(string(SQLCODE,'  - ',SQLState))
END

Many thanks indeed

Rip

asked 11 Jun, 13:45

riprap's gravatar image

riprap
5515
accept rate: 0%


You are right, you cannot add exception handling to the web client function as it has no body. So exception handling must be done within the wrapper function/procedure. I guess this does not work as expected in your sample as the procedure just returns the result to the caller, and the error just appears during the opening of the result set. Try to consume the result set within the procedure itself (say, by using a local variable of type xml to store the function's return value, and then selecting the local variable as result set. Then the exception should by caught within the procedure itself.

FWIW, you might try Web client logging to find out why the web request does fail sometimes.

permanent link

answered 11 Jun, 14:34

Volker%20Barth's gravatar image

Volker Barth
36.7k343505761
accept rate: 34%

converted 11 Jun, 14:35

2

Just to add: Here's an explanation why SQL Anywhere handles result sets in procedures that way, quite different from other database products...

(11 Jun, 15:05) Volker Barth

The problem is that the "select WebServiceTest('Hello');" effectively returns from the procedure immediately, without executing any code that follows.

The solution is to use SET local (or SELECT INTO local) to call the function, then SELECT local to return the successful value... an error in the SET local or SELECT INTO local will be captureed.

I had to code this kludge in many locations throughout Foxhound.

Create procedure mysecondproc2()
begin
declare x XML;
SET x = WebServiceTest('Hello');
SELECT x;
    exception
    when others then
    message(string(SQLCODE,'  - ',SQLState))
END;

CALL mysecondproc2();

-981  - WW050
permanent link

answered 11 Jun, 14:45

Breck%20Carter's gravatar image

Breck Carter
30.8k496680992
accept rate: 20%

1

In other words, what Volker said :)

(11 Jun, 14:47) Breck Carter

Thank you Breck and Volker, that's just the answer I needed!

(11 Jun, 16:48) riprap
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:

×252
×43
×7

question asked: 11 Jun, 13:45

question was seen: 153 times

last updated: 11 Jun, 16:48