I decided to go from a perpetual backup where the date was part of the file name to a cyclic one where it uses the day of the week. To do this I'm trying to get DELETE FILE working - should be simple enough but

This line

SET FILES STORE TO "buTfR";

produces

ElevateDB Error #700 An error was found in the script at line 39 and column 12 (Expected = but instead found STORE)

and this one

DELETE FILE "JUNK.EDBUpd" FROM STORE "buTfR";

produces

ElevateDB Error #700 An error was found in the script at line 40 and column 9 (Expected FROM but instead found FILE)

when trying to prepare the script.

What am I doing wrong?


Roy Lambert

Roy,

<< This line

SET FILES STORE TO "buTfR";

produces

ElevateDB Error #700 An error was found in the script at line 39 and column 12 (Expected = but instead found STORE) >>

Please post the context in which you're running the SQL.  I just tried the same in the EDB Manager in an empty SQL window, and get this:

ElevateDB Error #401 The store buTfR does not exist in the configuration EDBConfig

Tim Young
Elevate Software
www.elevatesoft.com

Tim

Its going to run in a job.To test, in EDBManager,  I selected the "execute job as script" option from the job. I tried preparing in Configuration & TfRData. The store buTfR exists in the session I'm using which is the same session as that the job is in.

I'm using


2.19b2 ansi in W7x64


SCRIPT
BEGIN DECLARE BackupCmnd VARCHAR DEFAULT '';
DECLARE ThisDay VARCHAR DEFAULT '';
DECLARE DBCursor CURSOR FOR DBStmt;
DECLARE StorePath VARCHAR DEFAULT '';
DECLARE StoreName VARCHAR DEFAULT 'buTfR';
DECLARE InfoCursor SENSITIVE CURSOR FOR InfoStmt;
PREPARE InfoStmt FROM 'SELECT * FROM Configuration.Stores WHERE Name=?';

USE TfRData;

SET ThisDay = 'TfR - ' + CASE EXTRACT(DAYOFWEEK FROM CURRENT_DATE)
                   WHEN 1 THEN 'Monday'
                   WHEN 2 THEN 'Tuesday'
                   WHEN 3 THEN 'Wednesday'
                   WHEN 4 THEN 'Thursday'
                   WHEN 5 THEN 'Friday'
                   WHEN 6 THEN 'Saturday'
                   WHEN 7 THEN 'Sunday'
                  END;

SET BackupCmnd = 'BACKUP DATABASE "TfRData" AS "' + ThisDay + '" TO STORE "buTfR"';
    
OPEN InfoCursor USING StoreName;
IF (ROWCOUNT(InfoCursor) = 0) THEN
 PREPARE DBStmt FROM 'SELECT _ParamData FROM Config WHERE _ID = ''BackupPath''';
 OPEN DBCursor;
 IF (ROWCOUNT(DBCursor) = 1) THEN
  FETCH FIRST FROM DBCursor ('_ParamData') INTO StorePath;
  EXECUTE IMMEDIATE 'CREATE STORE "buTfR" AS LOCAL PATH '+QUOTEDSTR(StorePath);
 ELSE
  EXECUTE IMMEDIATE 'CREATE STORE "buTfR" AS LOCAL PATH '+QUOTEDSTR('C:\TfR Backups');
 END IF;
END IF;
CLOSE DBCursor;
CLOSE InfoCursor;

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< this is where those two lines went

EXECUTE IMMEDIATE  BackupCmnd;

END

Roy Lambert

Roy,

<< Its going to run in a job.To test, in EDBManager,  I selected the "execute job as script" option from the job. I tried preparing in Configuration & TfRData. The store buTfR exists in the session I'm using which is the same session as that the job is in. >>

EXECUTE IMMEDIATE 'SET FILES STORE TO "buTfR"';
EXECUTE IMMEDIATE 'DELETE FILE "JUNK.EDBUpd" FROM STORE "buTfR"';

Tim Young
Elevate Software
www.elevatesoft.com

Tim


I think that means its SQL rather than SQL/PSM - right?

Roy Lambert

Roy,

<< I think that means its SQL rather than SQL/PSM - right? >>

Correct.  They are administrative statements, which means that they can be executed as one-off queries or in scripts, like DDL or DML statements.

Tim Young
Elevate Software
www.elevatesoft.com