List exceptions from an SQL Script

Database
Enthusiast

Re: List exceptions from an SQL Script

I've changed the c.YearNumber to vTearNumber.

It has run OK.

 

 

Enthusiast

Re: List exceptions from an SQL Script

Ok, with those corrections I'm still not getting anything I can see that contains the exception?

I've changed the dbc. to 'datalab'

Here's the SP and the CALL script:

 

REPLACE PROCEDURE datalab.ERROR_LOGGING

(IN SQL_TEXT VARCHAR(6000), OUT QUERY_STATUS VARCHAR(200))

BEGIN 

   DECLARE vYearNumber INTEGER;
   DECLARE vCountOfItems INTEGER;
   DECLARE vSQL_TEXT VARCHAR(6000);
   DECLARE vQUERY_STATUS VARCHAR(200);
	
   DECLARE EXIT HANDLER FOR SqlException
   BEGIN

   END;

						   FOR c AS -- process each row
									   SELECT  Year_Number 
									   FROM    datalab.YEARLY_SALES
						   DO 
						      INSERT INTO datalab.YEARLY_SALES_UPDATE
						      SELECT  DISTINCT YEAR_NUMBER, Scanned_Sales_Value
						              FROM    datalab.YEARLY_SALES
						              WHERE   YEAR_NUMBER = vYearNumber;
						   END FOR;

END;
---------------------------------------------------------------------------------------------------
CALL datalab.ERROR_LOGGING('SELECT  YEAR_NUMBER, id, Scanned_Sales_Value  FROM    datalab.YEARLY_SALES_UPDATE', a);

 

Enthusiast

Re: List exceptions from an SQL Script

I should add that the error should be that the field 'id' doesn't exist in the table.

Junior Contributor

Re: List exceptions from an SQL Script

vYearNumber is wrong, you need the data from the cursor: c.YearNumber

 

But I don't understand what you're trying to do, you call the SP with a Select?

Can you explain exactly what you want to do?

Enthusiast

Re: List exceptions from an SQL Script

OK, all I want to do is capture exceptions from SQL scripts.

I want to know what went wrong (if anything) when an SQL script was run.

That's it, I don't care how it's done but I just need to know the best way to do it.

Hope I'm clear now?

Thanks for your help as always.  Smiley Happy

Highlighted
Junior Contributor

Re: List exceptions from an SQL Script

Assuming the "script" is used to process data you can capture errors within an SP using Error Handlers, which insert the error information into a log table.

An EXIT handler will stop the execution of the SP while a CONTINUE handler simple skips the current statement and runs the next.

Handlers might be generic (FOR SQLEXCEPTION) or for a specific (usually an expected error which can be treated within the script) error (FOR SQLSTATE **bleep**).

And you can define different handlers for each BEGIN/END block.

 

There are lot of examples in the Stored Procedure manual.

You might also have a look at my CollectStatsQueue which shows a nested CONTINUE handler to catch individual errors during stats collection and a global EXIT handler for any other (unexpected) errors, both insert errors into a log table (this is the direct link to the script)

Enthusiast

Re: List exceptions from an SQL Script

Thanks dnoeth,

Much appreciated!

Peter