The forum will be down for maintenance over the weekend of August 18-20, 2017. The forum will be shut down on the evening (EDT) of Friday, August 18. Downtime is unknown but may be up to two days. The forum will be restarted as soon as maintenance is complete.

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;

asked 18 Oct '10, 08:33

Breck%20Carter's gravatar image

Breck Carter
26.5k433604876
accept rate: 21%


That will work. A simpler assignment statement using:

SET @html_string = <expression>;

will also work.

permanent link

answered 18 Oct '10, 13:33

Bruce%20Hay's gravatar image

Bruce Hay
2.6k1510
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
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:

×6
×1

question asked: 18 Oct '10, 08:33

question was seen: 1,527 times

last updated: 18 Oct '10, 13:33