Error handling from DBC.SysExecSQL in my own procedure

Database
Enthusiast

Error handling from DBC.SysExecSQL in my own procedure

Hi!

I've got issue with error handling in my procedure

Here's some of my code:

DECLARE EXIT  HANDLER FOR SQLSTATE '52004'
BEGIN
drop database TDADMIN_MV_SPC
END;
SET L_SQL_CRT= 'CREATE DATABASE TDADMIN_MV_SPC FROM '|| TRIM(db_src) || ' AS PERM = ' || perm_size_gb*1024**3;
CALL DBC.SysExecSQL(L_SQL_CRT);

I want to drop the database TDADMIN_MV_SPC if errors with "database(...) already exists" or better if any error occurs.

Main issuse is that if the database already exists, the procedure fails and quit without entering the handler.

How can I handle errors from DBC.SysExecSQL or what other thing can I use to execute dynamic query?

Regards

4 REPLIES
Enthusiast

Re: Error handling from DBC.SysExecSQL in my own procedure

I've also tried using 

DECLARE EXIT  HANDLER FOR SQLSTATE 'T5612'
or
DECLARE EXIT HANDLER FOR SQLEXCEPTION

But it doesn't work.

Senior Apprentice

Re: Error handling from DBC.SysExecSQL in my own procedure

This should work (at least with the SQLEXCEPTION hadnler), can you show the actual source code?

Of course it will only drop the db, but not re-create it.

If you want to drop the database anyway why don't you drop it first ignoring the "database doesn't exists error"?

...
BEGIN -- nested compound statement just to ignore the "database doesn't exist" error
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
IF SQLCODE <> 3802 THEN RESIGNAL; END IF;
END;
DROP DATABASE TDADMIN_MV_SPC;
END;

SET L_SQL_CRT= 'CREATE DATABASE TDADMIN_MV_SPC FROM '|| TRIM(db_src) || ' AS PERM = ' || perm_size_gb*1024**3;

EXECUTE IMMEDIATE L_SQL_CRT;
...
Enthusiast

Re: Error handling from DBC.SysExecSQL in my own procedure

Hi Dieter.

I can't drop the database first. I tried that but the procedure is used by many users and sometimes the user don't have access to drop the database. That's why the better solution is to drop the database if any error occurs. 

I tried again DECLARE EXIT HANDLER FOR SQLEXCEPTION

Now it works fine. I don't know what was wrong last time. Thanks for help.

Enthusiast

Re: Error handling from DBC.SysExecSQL in my own procedure

Hi Dieter,

 

I was trying to find solution to my issue with execpetion handling in my stored procedure , and i found your post here, hence thought to catch up with you.

 

I have a stored procedure where i am executing SQL scripts in loop. The SQL Scripts are stored as value in a table.So the Stored Procedure i have designed in such a way that one by one it picks up the sql from this table and executes and stores the result into another table called RESULT_SUMMARY whether the scripts 'Pass' or 'Fail'. Basically i have designed this to automate test script execution.

 

The issue currently is that lets say out of 100 test cases, the 5th test script fails due to some syntax error like "Table does not exist", then the rest of the pending scripts do not execute further. The Stored Procedure stops there itself.

 

What can i use to resolve this issue?

Is it possible to skip the script which has error and execute the rest of the test scripts which are in queue?

 

Thanks

Lovey