Get diagnostics exception - error line

iskander · #1 (**permalink**) 11-08-07, 11:59

How can I in a SQL stored procedure use Get diagnostics exception to get the line where the error occurred?

Marcus_A · #2 (**permalink**) 11-08-07, 14:04

Line? Are you talking about the line number of the stored procedure or the row information?

The get diagnostics provides information from the SQLCA (RTFM if you don't don't know what SQLCA is).

stolze · #3 (**permalink**) 11-09-07, 12:04

SQLCA doesn't contain line number. It wouldn't make much sense if the SP changes once in a while and the line numbers would change. Instead, you could use some probe mechanism:

Code:

DECLARE probe INT DEFAULT 0;

...
SET probe = 1;
...do something that may throw an exception...

SET probe = 28;
...do something that may throw an exception...

Then your condition handler can check the value in the "probe" variable and return that in a message text or something. That would be much more stable.

iskander · #4 (**permalink**) 11-09-07, 12:07

Thanks mate!
that is brilliant.

Internet Services

Web Development

Digital Marketing

Consumer Tech