Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

I would be grateful for some pointers:

I am trying to use the Web Service feature of SQL Anywhere 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 ''
type 'HTTP:POST:text/xml'

and then

select WebServiceTest('Hello')
I get:
Unable to connect to the remote host specified by
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()
select WebServiceTest('Hello');
    when others then
    message(string(SQLCODE,'  - ',SQLState))

Many thanks indeed


asked 11 Jun '20, 13:45

riprap's gravatar image

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 '20, 14:34

Volker%20Barth's gravatar image

Volker Barth
accept rate: 34%

converted 11 Jun '20, 14:35


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 '20, 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()
declare x XML;
SET x = WebServiceTest('Hello');
    when others then
    message(string(SQLCODE,'  - ',SQLState))

CALL mysecondproc2();

-981  - WW050
permanent link

answered 11 Jun '20, 14:45

Breck%20Carter's gravatar image

Breck Carter
accept rate: 20%


In other words, what Volker said :)

(11 Jun '20, 14:47) Breck Carter

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

(11 Jun '20, 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



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "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:


question asked: 11 Jun '20, 13:45

question was seen: 1,508 times

last updated: 11 Jun '20, 16:48