Re: Enhanced exception trace info

[Breck Carter [TeamSybase]]

I support Bill's request 100%.

Microsoft SQL Server 2005 and 2008 offer ERROR_PROCEDURE() and
ERROR_LINE() as part of the TRY/CATCH mechanism described in
http://msdn.microsoft.com/en-us/library/ms175976.aspx

=====
-- Create procedure to retrieve error information.
CREATE PROCEDURE usp_GetErrorInfo
AS
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
GO

BEGIN TRY
-- Generate divide-by-zero error.
SELECT 1/0;
END TRY
BEGIN CATCH
-- Execute error retrieval routine.
EXECUTE usp_GetErrorInfo;
END CATCH;
=====

In effect Bill's asking for that, plus more. At the very least, if
ERROR_PROCEDURE() and ERROR_LINE() were available from a SQL Anywhere
EXCEPTION handler, they would tell us where the exception occurred and
bring BEGIN/EXCEPTION up to par with MSS's TRY/CATCH.

Go one step further, and give us an "ERROR_SOURCE()" string containing
the SQL code that was executing when the exception occurred, and you'd
be leaping past Microsoft when it comes to diagnosing runtime errors.

FWIW we're talking about production here, not some kind of
"development mode"... the really difficult exceptions occur in
production. In development, we have the debugger, etcetera... it's in
the real world where hard problems happen.

Breck

On 17 Jun 2009 16:03:19 -0700, "Bill Aumen" <bi...@aumenconsulting.com>
wrote:

>It would ge really GREAT if we could capture the exact contents of the most
>recently executed SQL statement for tracing errors. We tried the
>laststatement property, but it gives us the entire begin/end block of the
>stored proc. The result of traceback(*) was null.
>
>For example, if we executed the statement in the stored proc:
> SELECT (1 / li_var) ; where li_var is zero and caused a divide by zero
>exception.
>What we would really like to be able to capture with the sqlstate, sqlcode,
>etc would be the actual select statement executed: SELECT ( 1 / 0);
>
>
>Another example would be: we execute the statement in our stored proc:
> insert into wsreq_web_service_request
> ( wsreq_id,
> wsreq_timestamp,
> wsreq_request,
> syusr_id )
> values( ai_request_id,
> ld_created_ts,
> ax_in_xml,
> as_syusr_id ) ;
>We would like to be able to capture the kind of info that we can dig out of
>the transaction log after running dbtran:
>--INSERT-1495-01054342558
>CREATE VARIABLE n2 LONG VARCHAR
>go
>SET n2 = '<TRANSACTION...'
>go
>INSERT INTO app_owner.wsreq_web_service_request(wsreq_id,wsreq_timestamp,
> wsreq_request,syusr_id)
>VALUES (3282,'2009-06-09 07:00:00.103',n2,'app_owner')
>go
>
>
>That kind of info would give us the ability to see EXACTLY what caused the
>error.
>
>
>Thanks,
>Bill
>

--
Breck Carter http://sqlanywhere.blogspot.com/

RisingRoad SQL Anywhere and MobiLink Professional Services
breck....@risingroad.com

[Bill Aumen]

[Breck Carter [TeamSybase]]

Breck

>It would ge really GREAT if we could capture the exact contents of the most

--

[David Shuman]

I do not know if I am missing something here, as these thoughts seem to
match up well with the concepts expressed in this other thread (Better
error message) minus one thing -- this all seems to be expressed in terms
available inside a SQL Anywhere environment. So I ask -- will such a
select statement/functions be accessible from an application environment
such as java or powerbuilder development reasonably (without excessive
coding) available after receiving the erroneous SQLCODE?

As such this concept could be the answer to the other thread.

--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/

[Bill Aumen]

yes, I do see the overlap. So I will add some additional comments to that
post.

Thanks,
Bill

"David Shuman" <d.shuman...@att.com> wrote in message
news:op.uvrzy...@xperi051.adm.xperi.net...