|
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 |
|
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. 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 '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()
begin
declare x XML;
SET x = WebServiceTest('Hello');
SELECT x;
exception
when others then
message(string(SQLCODE,' - ',SQLState))
END;
CALL mysecondproc2();
-981 - WW050
|
