![]() | ![]() Products ![]() ![]() ![]() ![]() |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 6 of 6 total |
![]() |
Wed, Nov 8 2017 6:12 PM | Permanent Link |
Jeff Cook Aspect Systems Ltd | 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 |
Thu, Nov 9 2017 8:57 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | 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 |
Thu, Nov 9 2017 6:07 PM | Permanent Link |
Jeff Cook Aspect Systems Ltd | 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 |
Fri, Nov 10 2017 3:02 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | 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 |
Fri, Nov 10 2017 2:33 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | 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 |
Sun, Nov 12 2017 5:17 PM | Permanent Link |
Jeff Cook Aspect Systems Ltd | 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 |
This web page was last updated on Wednesday, August 14, 2024 at 03:12 PM | Privacy Policy![]() © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? ![]() |