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...
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!