Please be aware that the SAP SQL Anywhere Forum will be shut down on August 29th, 2024 when all it's content will be migrated to the SAP Community.

Or: How do I waste time while trying to find a string truncation error?

Suddenly one of our stored functions returned SQLCODE -638 ("Right truncation of string data"). Though the code uses several MESSAGE statements, I did not find the according "hitting" statement. Neither code inspection (including triggers) nor debugging in Sybase Central nor using Request Level Logging worked seemed to work. In the end an error handler with TRACEBACK() gave some clue:

I had used a COMPUTEd column which is calculated by a stored function, and that accesses connection_propery('AppInfo'), and that happened to returns more characters than expected on the test box, and since option string_rtruncation was set to ON (at least in one of my test connections, which I had not noticed for a while...sigh!), it rose this error.

While the error source is clarified and fixed, I would like to know whether RLL or another mechanism can help to trace the calling of a function when triggered by a COMPUTE clause. - I surely can debug this a function, however, noticing this function is called at all was the main pitfall...


Correction: A closer look at the request level logs shows that with option "PROCEDURES", the calls of those stored functions are logged, too. So there is a facility to log these calls with request logging, even if the function does not (yet!) use the error handling code as suggested by Mark. My oversight, sorry:)

asked 28 Jun '12, 13:04

Volker%20Barth's gravatar image

Volker Barth
accept rate: 34%

edited 29 Jun '12, 10:56

The obvious answer, of course, is: Use real error handling in your UDF, and write a message to the log in case something goes wrong - yes, that's right.

I guess I had simply expected that such a "trivial", non-deterministic UDF couldn't fail:)

(28 Jun '12, 13:06) Volker Barth

Volker: I think you answered your own question - add exception handlers to ALL of your procedures and functions.

I generally add exception handlers to all of my procedures even ones that I do not expect exceptions to occur. It is a defensive mechanism: by adding exception handler(s) to routines that I do not expect to have exceptions I will hopefully notice immediately (during development/testing) that something is happening that I did not expect.

Here is the generally template:

procedure foo( ...parameters... )
    when ...specific-exception... then
    when others then
        call my_exception_handler( 'foo', sqlcode, sqlstate, errormsg(), traceback(*) );

The my_exception_handler() routine logs the unexpected exception either on the console or in my app-specific log table or both depending on my app settings.

I should note that my_exception_handler() routine does not directly log the information into my app-specific log table. Instead it fires an event and it is the event that logs the info into the table. This is necessary since the my_exception_handler() cannot commit the transaction but the event handler can!


permanent link

answered 28 Jun '12, 13:18

Mark%20Culp's gravatar image

Mark Culp
accept rate: 41%

edited 28 Jun '12, 13:23


Great practical help, Mark (as expected)!

The obvious next question: How big is the performance impact for error handling (as in your sample) in case no exception does occur? (I'm sure it won't be too expensive in my particular case, so this is primarily out of curiosity...)

(29 Jun '12, 09:57) Volker Barth
Replies hidden

Zero overhead in my experience... Foxhound has many many exception handlers, including multiple in-line BEGIN EXCEPTION END "try-catch" blocks inside fast loops, no penalty whatsoever. I.e., they have never, ever shown up in the procedure profiler.

(29 Jun '12, 10:24) Breck Carter

Exception handling has almost no overhead unless the exception handler is invoked.

(29 Jun '12, 10:35) Elmi Eflov

As both Breck and Elmi has said, there is very little overhead. The only time there is any effect is when the procedure is initially parsed which is done when the procedure is invoked for the first time. Beyond that the existence of the exception handler is nil... until it is invoked.

(29 Jun '12, 18:29) Mark Culp
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "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:


question asked: 28 Jun '12, 13:04

question was seen: 16,327 times

last updated: 29 Jun '12, 18:29