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; |