SAP SQL Anywhere

Question

I want to be able to trap and handle all SQL exceptions raised in a SQL Anywhere web service application. The following example shows an exception that is not captured by the EXCEPTION block in a web service; i.e., the following message appears in the browser window...

-------------------- SQL error: Cannot convert 0 to a date

...and nothing appears in the engine console log. Here's the SQL Anywhere Version 11.0.1.2276 code:

CREATE SERVICE root 
   TYPE 'RAW' AUTHORIZATION OFF USER DBA
   AS CALL p();

CREATE PROCEDURE p()
RESULT ( html_string LONG VARCHAR )
BEGIN

DECLARE @sqlcode   INTEGER;
DECLARE @sqlstate  VARCHAR ( 5 );
DECLARE @errormsg  VARCHAR ( 32767 );

CALL dbo.sa_set_http_header( 'Content-Type', 'text/html' );

SELECT STRING ( 
   '<HTML>',
   '<TITLE>Simple Web Service With Exception</TITLE>',
   '<STYLE>TD { font-family: "VERDANA", "ARIAL", "HELVETICA", "SANS-SERIF"; font-size: 9pt }</STYLE>',
   '<BODY>',
   '<TABLE>',
   '<TR>',
   '<TD>', CURRENT TIMESTAMP, '</TD>',
   '<TD>', YMD ( dummy_col, 1, 1 ), '</TD>',
   '</TR>',
   '</TABLE>',
   '</BODY></HTML>' )
FROM SYS.dummy;

EXCEPTION
   WHEN OTHERS THEN
      SELECT SQLCODE, SQLSTATE, ERRORMSG() 
        INTO @sqlcode, @sqlstate, @errormsg;
      MESSAGE STRING ( 
         'EXCEPTION in p at ', 
         CURRENT TIMESTAMP, 
         ': SQLCODE = ', @sqlcode,  
         ', SQLSTATE = ', @sqlstate,  
         ', ERRORMSG() = ', @errormsg ) 
         TO CONSOLE;

END;

Here is the engine command line, and the browser url to launch the web service:

"%SQLANY11%\bin32\dbeng11.exe" -o dbeng11_log.txt -xs http(port=80) ddd11.db

http://localhost

For an explanation of this (surprising to some) behavior, see Ivan Bowman's discussion here: http://sqlanywhere-forum.sap.com/questions/1177/how-to-form-an-exception-handler/1218#1218

If this line of code is removed...

   '<TD>', YMD ( dummy_col, 1, 1 ), '</TD>',

...then the web service does not raise an exception, and this is displayed in the browser window:

2010-10-18 04:10:13.515

If the result set (a single string) is placed INTO a local variable, and a second SELECT is used to return the string to the browser, the error is trapped, nothing appears in the browser window, and the following message appears in the SQL Anywhere console window.

This is what I want (the ability to deal with the exception)...

EXCEPTION in p at 2010-10-18 04:16:01.406: SQLCODE = -157, SQLSTATE = 53018, ERRORMSG() = Cannot convert 0 to a date

Is this how I have to do it? ...

CREATE PROCEDURE p()
RESULT ( html_string LONG VARCHAR )
BEGIN

DECLARE @sqlcode   INTEGER;
DECLARE @sqlstate  VARCHAR ( 5 );
DECLARE @errormsg  VARCHAR ( 32767 );

DECLARE @html_string LONG VARCHAR;

CALL dbo.sa_set_http_header( 'Content-Type', 'text/html' );

SELECT STRING ( 
   '<HTML>',
   '<TITLE>Simple Web Service With Exception</TITLE>',
   '<STYLE>TD { font-family: "VERDANA", "ARIAL", "HELVETICA", "SANS-SERIF"; font-size: 9pt }</STYLE>',
   '<BODY>',
   '<TABLE>',
   '<TR>',
   '<TD>', CURRENT TIMESTAMP, '</TD>',
   '<TD>', YMD ( dummy_col, 1, 1 ), '</TD>',
   '</TR>',
   '</TABLE>',
   '</BODY></HTML>' )
INTO @html_string
FROM SYS.dummy;

SELECT @html_string;

EXCEPTION
   WHEN OTHERS THEN
      SELECT SQLCODE, SQLSTATE, ERRORMSG() 
        INTO @sqlcode, @sqlstate, @errormsg;
      MESSAGE STRING ( 
         'EXCEPTION in p at ', 
         CURRENT TIMESTAMP, 
         ': SQLCODE = ', @sqlcode,  
         ', SQLSTATE = ', @sqlstate,  
         ', ERRORMSG() = ', @errormsg ) 
         TO CONSOLE;

END;

Answer 1

2

That will work. A simpler assignment statement using:

SET @html_string = <expression>;

will also work.

permanent link

answered 18 Oct '10, 13:33

Bruce Hay
2.6k●1●5●10
accept rate: 48%

So the simple rule is: When errors in a result set should get trapped within a block of code that returns that result set (e.g. a stored proc), then it's necessary to force the fetching of the result set within the block itself, e.g. by using SELECT INTO or the like.?

(18 Oct '10, 13:46) Volker Barth

1

@Volker: If the procedure is called from another procedure, then the caller will be able to catch the EXCEPTION, so this technique is only required in client-facing SQL blocks (which is Newspeak for procedures called from the outside world).

(18 Oct '10, 16:00) Breck Carter

@Bruce: Yabbut... the example has a FROM clause, as do most of my web services, so coding a SELECT on the right side of a SET is no simpler.

(18 Oct '10, 16:02) Breck Carter

@Breck: That's understood, but my question for a (not so simple) rule is aimed on catching errors within the block itself and not leaving it to the caller to handle them. - I agree that's not the common requirement:)

(18 Oct '10, 16:16) Volker Barth

flat view

SAP SQL Anywhere

Is this how I have to trap exceptions raised by the final SELECT in a web service?

Follow this question

Related questions