Hi

I'm writing a script that will eventually be part of a JOB.

The following lines of code are giving me a problem
==========================================================
   USE MEMORYDB;
   BEGIN
      EXECUTE IMMEDIATE 'CREATE TEMPORARY TABLE "ExportUsers"
      (
         "Name" VARCHAR(40),
         "Password" VARCHAR(40),
         "Roles" VARCHAR(1024),
         PRIMARY KEY ("Name")
      )';
   EXCEPTION
        EXECUTE IMMEDIATE 'DELETE FROM TABLE "ExportUsers"';
   END;
=========================================================

The intention is to create a temporary table or, if it already exists,
delete the rows from it.  Either way, I should have an empty table for
the next part of the script

I obviously don't understand the exception handling in scripts as, when
I run this in EDB Manager I get:-
------------------------------------------------------------------------
ElevateDB Error #400 An error occurred with the statement at line 13 and
column 25 (The temporary table ExportUsers already exists in the schema
Default)
------------------------------------------------------------------------

So why doesn't the exception block handle this?

Cheers

Jeff

Jeff


I suspect its something to do with EXECUTE IMMEDIATE being executed in the contect of the query and ignoring the USE statement.

This should get you off the ground

SCRIPT
BEGIN
DECLARE ActionCursor SENSITIVE CURSOR FOR ActionStmt;
DECLARE InfoCursor SENSITIVE CURSOR FOR InfoStmt;
USE Demo;
PREPARE InfoStmt FROM 'SELECT * FROM Information.TemporaryTables WHERE Name=''ExportUsers''';
OPEN InfoCursor;
IF (ROWCOUNT(InfoCursor) > 0) THEN
 PREPARE ActionStmt FROM 'EMPTY TABLE ExportUsers';
ELSE
 PREPARE ActionStmt FROM 'CREATE TEMPORARY TABLE "ExportUsers"
      (
         "Name" VARCHAR(40),
         "Password" VARCHAR(40),
         "Roles" VARCHAR(1024),
         PRIMARY KEY ("Name")
      )';
END IF;
OPEN ActionCursor;
CLOSE InfoCursor;
CLOSE ActionCursor;
END


Roy Lambert

Roy Lambert wrote:
<<
I suspect its something to do with EXECUTE IMMEDIATE being executed in the contect of the query and ignoring the USE statement.
...
>>

Hi Roy

Thank you for your help.  Unfortunately I still have the same problem

I have pasted my whole script up to the error below my sig. with the error line marked thus <<<<

I'm getting the same error i.e.

"ElevateDB Error #400 An error occurred with the statement at line 23 and column 9 (The temporary table ExportUsers already exists in the schema Default)"

I have run :-
SELECT * FROM Information.TemporaryTables WHERE Name='ExportUsers'
... which returns zero rows.

But with using breakpoints, I can see that it executes the Prepare of the CREATE and not the DELETE statement.

  IF (ROWCOUNT(InfoCursor) > 0) THEN
     PREPARE ActionStmt FROM 'EMPTY TABLE ExportUsers';
  ELSE
     PREPARE ActionStmt FROM 'CREATE TEMPORARY TABLE "ExportUsers"

What sort of weirdery is that?

Cheers

Jeff
=====================================================
SCRIPT
BEGIN
--   DECLARE DBCursor CURSOR FOR DBStmt;
  DECLARE ActionCursor SENSITIVE CURSOR FOR ActionStmt;
  DECLARE InfoCursor SENSITIVE CURSOR FOR InfoStmt;

  PREPARE InfoStmt FROM 'SELECT * FROM Information.TemporaryTables WHERE Name=''ExportUsers''';
  OPEN InfoCursor;
  
  USE MEMORYDB;
  
  IF (ROWCOUNT(InfoCursor) > 0) THEN
     PREPARE ActionStmt FROM 'EMPTY TABLE ExportUsers';
  ELSE
     PREPARE ActionStmt FROM 'CREATE TEMPORARY TABLE "ExportUsers"
      (
         "Name" VARCHAR(40),
         "Password" VARCHAR(40),
         "Roles" VARCHAR(1024),
         PRIMARY KEY ("Name")
      )';
  END IF;
  OPEN ActionCursor;  <<<<
  CLOSE InfoCursor;
  CLOSE ActionCursor;
<snip>
END

Jeff


The only significant difference I can spot is the placement of the USE MEMORYDB line. Try moving that up above the PREPARE line

If that doesn't work shout "TIM" asn loud as you can!

Roy Lambert

Jeff,

Sorry about the delay in responding.  I'm knee-deep in development, and I sometimes forget to visit the support forums when I'm in such a state...

<< I obviously don't understand the exception handling in scripts as, when I run this in EDB Manager I get:- >>

If you don't want the exceptions to stop the script, just make sure to uncheck the Pause on Exceptions in the script window in the EDB Manager.  You can also just hit the F9 key again to start the script running again after the exception occurs.

Tim Young
Elevate Software
www.elevatesoft.com

On 11/11/2017 8:33 AM, Tim Young [Elevate Software] wrote:
> If you don't want the exceptions to stop the script, just make sure to uncheck the Pause on Exceptions in the script window in the EDB Manager.  You can also just hit the F9 key again to start the script running again after the exception occurs.
>

Ah Ha!  So I was quite clever, in that my script was actually working!

It was my lack of knowledge/stupidity - I'd left "Pause on Exceptions"
ticked and EDB Manager was doing exactly what I had asked it to do.

Thanks Tim and Roy

Cheers

Jeff