|
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 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... EDIT: 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:) |
|
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... ) begin ...code... exception when ...specific-exception... then ...handle-specific-exception... when others then call my_exception_handler( 'foo', sqlcode, sqlstate, errormsg(), traceback(*) ); resignal; end; 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! HTH 1
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
1
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
2
Exception handling has almost no overhead unless the exception handler is invoked.
(29 Jun '12, 10:35)
Elmi Eflov
1
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
|
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:)