Hi all,

after 2 complete days of trying without success and reading all forum posts regarding stores and files where I got many useful practices, I have no more idea how to proceed.

I use ElevateDB 2.24 Build 3.

From within a database regular backups are made by a job. The backup files are written to the store "KFZMDataLocalBackup". Everything fine.

Now, every day when a new backup file is created files that are older than x days should be deleted from the store by the job.

To simplify and first test for functionality I created a new (temporary) job that should simply delete one known file.

Here it is:

EXECUTE IMMEDIATE 'SET FILES STORE TO "KFZMDataLocalBackup"';
EXECUTE IMMEDIATE 'DELETE FILE "KFZMDataBackup-2016-11-28-1405.EDBBkp" FROM STORE "KFZMDataLocalBackup"';

The store exists.
The file exists.

It makes no difference whether
- I exclude the first line "...SET FILES..."
- I run the commands from within a job
- I run/prepare the commands as a script

Every time this error message is generated (points to the line "...DELETE FILE..."):
"An error was found in the statement at line xx and column yy (Expected FROM but instead found  )"

What am I doing wrong? I'm in a desperate state. Perhaps anyone here can help me?

Thank you very much in advance
Joe

Joe


I do something very similar

EXECUTE IMMEDIATE 'DELETE FILE "' + ThisDay  + '.EDBBkp" FROM STORE "buTfR"';

The only difference I can see to your code is I set the file name separately. That's because I'm using Monday..Sunday as part of the file name and I had to build that depending on the date.

I thought it might be something to do with the maximum identifier length <<The maximum length of an identifier is 40 characters.>> but it doesn't seem to exceed that.

I wonder if its the way this is formed

KFZMDataBackup-2016-11-28-1405.EDBBkp

Its not a date, its not a timestamp and if you have 1405 backups in one day then WOW

You may be introducing some banned characters which cause the SQL parser to have a hissy fit.

Roy Lambert

Joe,

<< Here it is:

EXECUTE IMMEDIATE 'SET FILES STORE TO "KFZMDataLocalBackup"';
EXECUTE IMMEDIATE 'DELETE FILE "KFZMDataBackup-2016-11-28-1405.EDBBkp" FROM STORE "KFZMDataLocalBackup"'; >>

Is that the entire body of the job definition  ?

If so, then please post the complete error message that you're seeing, complete with line/column numbers.  That will help pinpoint where the issue is.

Thanks,

Tim Young
Elevate Software
www.elevatesoft.com

Joe,

BTW, for the record, I did try your exact code here and it works fine, so it's not something obvious.

Tim Young
Elevate Software
www.elevatesoft.com

Thank you very much for your quick responses!

@Roy:
1405 Backups one day!?? Yes, wow, but in fact it is the timestamp (2:05 pm). We here in Europe deal with 24 hours a day

@Tim:
For now, and for simplifying and testing and verifying purposes, the job should only delete one file. Later, when everything will work, the job will be widened so that the file name(s) will be taken from variables etc. I did that already, but ran into the problem that nothing worked, and therefore reduced step by step, until now we have the absolute minimum.

This is the whole job:
==========================================
CREATE JOB "DeleteFileTest"
RUN AS "System"
FROM DATE '2016-11-30' TO DATE '2016-11-30'
EVERY 5 MINUTES ON MON, TUE, WED, THU, FRI, SAT, SUN
BETWEEN TIME '09:05' AND TIME '09:20:00.999'
BEGIN
EXECUTE IMMEDIATE 'SET FILES STORE TO "KFZMDataLocalBackup"';
EXECUTE IMMEDIATE 'DELETE FILE "KFZMDataBackup-2016-11-28-1405.EDBBkp" FROM STORE "KFZMDataLocalBackup"';
END
VERSION 1.00

And in the LOG file two error messages are found. One from "PrepareStatement" and one from "ExecuteJob". Both messages are identical and are taken here per copy and paste (isn't there a blank character at the end of the line before the last bracket?).
==========================================
An error was found in the statement at line 4 and column 19 (Expected FROM but instead found  )


BTW:
I tried the same job but with different file name without the dashes "-": "KFZMDataBackup201611281405.EDBBkp". But nothing changes, the same error.

I wish you all the best for new ideas...

Joe

Joe


What happens if you try the two statements just as normal SQL in EDBManager?

I always have difficulty figuring out where the problem is in scripts but if you can break it down to directly executed SQL it gets easier.

Roy Lambert

Hello Roy,

I tried the following (in EDB Manager):

Create a Stored Procedure
========================================
CREATE PROCEDURE "A04-DeleteTestFile" ()
BEGIN
EXECUTE IMMEDIATE 'SET FILES STORE TO "KFZMDataLocalBackup"';
EXECUTE IMMEDIATE 'DELETE FILE "KFZMDataBackup-2016-11-28-1405.EDBBkp" FROM STORE "KFZMDataLocalBackup"';
END
VERSION 1.00

Result on execution:
ElevateDB Error #700 An error was found in the statement at line 4 and column 19 (Expected FROM but instead found  )



Create a SQL Statement
========================================
SET FILES STORE TO "KFZMDataLocalBackup";
DELETE FILE "KFZMDataBackup-2016-11-28-1405.EDBBkp" FROM STORE "KFZMDataLocalBackup";

Result on "Prepare / Execute":
ElevateDB Error #700 An error was found in the statement at line 2 and column 1 (Expected end of expression but instead found DELETE)



Create a Script
========================================
SCRIPT
BEGIN
SET FILES STORE TO "KFZMDataLocalBackup";
DELETE FILE "KFZMDataBackup-2016-11-28-1405.EDBBkp" FROM STORE "KFZMDataLocalBackup";
END

Result on "Prepare / Execute":
ElevateDB Error #700 An error was found in the script at line 3 and column 11 (Expected = but instead found STORE)


Create a Script
========================================
SCRIPT
BEGIN
EXECUTE IMMEDIATE 'SET FILES STORE TO "KFZMDataLocalBackup"';
EXECUTE IMMEDIATE 'DELETE FILE "KFZMDataBackup-2016-11-28-1405.EDBBkp" FROM STORE "KFZMDataLocalBackup"';
END

Result on "Prepare":
no error message

Result on "Execute":
ElevateDB Error #700 An error was found in the statement at line 4 and column 19 (Expected FROM but instead found  )


What else could I do?

Best regards
Joe

Joe

As straight SQL you have to use the terminator that EDBManager defines. The default is ! so try this


SET FILES STORE TO "KFZMDataLocalBackup"!
DELETE FILE "KFZMDataBackup-2016-11-28-1405.EDBBkp" FROM STORE "KFZMDataLocalBackup"!

I just tried here with a store of mine

SET FILES STORE TO "buTfR"!
DELETE FILE "Test.EDBBkp" FROM STORE "buTfR"!

EDBManager lies to me and tells me the file was deleted successfully when I know for a fact there wasn't one there - but at least it ran happily

On a philosophical viewpoint (for when Tim reads this) I suppose that since the file is no longer there, even if it wasn't there to start with, it has been successfully deleted. <vbg>

Roy Lambert

Roy,

I tried the "!" in a SQL statement, but without success. The error messages remain the same.

Sigh....

Joe

>I tried the "!" in a SQL statement, but without success. The error messages remain the same.

Is that the same as in

ElevateDB Error #700 An error was found in the statement at line 2 and column 1 (Expected end of expression but instead found DELETE)

or the same as in

"An error was found in the statement at line xx and column yy (Expected FROM but instead found  )"

If the former you still have the statement wrong.

If the latter then you can now try messing about a bit. If you've been cutting & pasting try typing in directly, if that doesn't work try altering the file name to say test and see if that works. Change the store name to something different. Create a new session and see what happens in there.

One question I haven't asked so far is are you using unicode or ansi (shouldn't make a difference but just in case)

I just created a store called KFZMDataLocalBackup and tried here and its fine (using ansi) so its something at your end by the look of it.

Roy Lambert