Hi

I'm having the following message when executing the script below (>>>> is the line causing the error)

ElevateDB Error #600 An error occurred with the statement at line 8 and column 22 (File manager error (Access denied to the file C:\Users\AGhazzi\Desktop\Database\EDBConfig.EDBCfg))


SCRIPT
BEGIN
  
DECLARE N INTEGER DEFAULT 0;

EXECUTE IMMEDIATE 'SELECT COUNT(*) INTO ? FROM Configuration.Stores WHERE Name = ''Imports''' USING N;
IF N <> 0 THEN
>>>>>   EXECUTE IMMEDIATE 'DROP STORE Imports';
END IF;

EXECUTE IMMEDIATE 'CREATE STORE Imports
AS LOCAL PATH ''C:\Users\AGhazzi\Desktop\Database\''
DESCRIPTION ''Imports''';
END

Please advise.

Many Thanks
Alfred Ghazzi

Alfred


Two thoughts came to mind:

1. The preceding EXECUTE IMMEDIATE has set up a lock on the configuration file and its not being released
2. someone / some other process has a long on the configuration file

2 is, hopefully, easy for you to check

1. needs the script rewriting. EXECUTE IMMEDIATE is very convenient its essentially launching a thread, creating the necessary bits and then running a query.

having produced the script below there is a 3 - you've used single quotes where it should be double and the error message is a bit misleading although technically correct,

The script below works here - plug your names and path in and you should be fine



SCRIPT
BEGIN

DECLARE Dropper SENSITIVE CURSOR FOR sqlDropper;
DECLARE Checker SENSITIVE CURSOR FOR sqlChecker;
DECLARE N INTEGER DEFAULT 0;

PREPARE sqlChecker FROM 'SELECT * FROM Configuration.Stores WHERE Name = ''Junk''';
OPEN Checker;
SET N = ROWCOUNT(Checker);
CLOSE Checker;

IF N > 0 THEN
PREPARE sqlDropper FROM 'DROP STORE "Junk"';
EXECUTE sqlDropper;
CLOSE Dropper;
END IF;

PREPARE sqlDropper FROM 'CREATE STORE "Junk" AS LOCAL PATH ''z:\zap\a4\'' DESCRIPTION ''Junk''';
EXECUTE sqlDropper;
CLOSE Dropper;
END


Roy Lambert

Hi Roy

You are awesome as always. Really appreciate it...

if you could and for sake of learning, where did I use the single quote instead of double? Also, what is the logic behind using the single quote, double quote and sometimes no quote? is there one? or just a syntax necessity?

Many thanks for your superfast help.

Alfred Ghazzi

Alfred


>if you could and for sake of learning, where did I use the single quote instead of double?

Looking at it again I can'ty see it - I may have confused myself there when copying your code to start with and then altering stuff - sorry.

>Also, what is the logic behind using the single quote, double quote and sometimes no quote? is there one? or just a syntax necessity?

I think its  data uses single quotes objects use double quotes but if unambiguous can use no quotes - just part of the SQL spec

Roy

Hi Roy

Many thanks
Alfred