All Forums Database
aklopis 6 posts Joined 08/15
17 Jun 2016
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

aklopis 6 posts Joined 08/15
17 Jun 2016

I've also tried using 

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

But it doesn't work.

dnoeth 4628 posts Joined 11/04
17 Jun 2016

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

 

Dieter

aklopis 6 posts Joined 08/15
21 Jun 2016

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.
 

You must sign in to leave a comment.