PLEASE DON'T BEAT ME - I am back again...

Hello all.

I must come back to my problem from about 2 weeks ago (=> http://www.elevatesoft.com/forums?action=view&category=edb&id=edb_general&msg=19592&page=1)

I thought the problem was solved, but I failed.

The target of this job sometimes in the future should be to delete files older than a criteria (more than xxx days old) from a store and after that make a new backup of the database on the EDBServer to the store.

Components:
EDBServer and EDBManager 2.24 build 3.
Windows 10 x64.
The store exists.
In the store several files exist.

I reduced the commands in the following job to a minimum to find out at which point the system fails. Therefore, don't think about the sense of this fragment here (file older than 10 minutes etc.) - when the problem perhaps might be solved the commands must be widened to loop through all the files in the store.

Every line has been manually typed in (no copy and paste!).


This works perfectly:
=====================================================
CREATE JOB "BackupTest1312"
RUN AS "System"
FROM DATE '2016-12-13' TO DATE '2016-12-13'
EVERY 5 MINUTES ON MON, TUE, WED, THU, FRI, SAT, SUN
BETWEEN TIME '15:01:34' AND TIME '15:03:34.999'
BEGIN
DECLARE TheStoreName VARCHAR DEFAULT '';
DECLARE TheDelFileName VARCHAR DEFAULT '';
DECLARE procCur CURSOR FOR procStmt;
SET TheStoreName = 'KFZMDataLocalBackup';
SET TheDelFileName = 'KFZMDataBackup-2016-12-01_1624.EDBBkp';
PREPARE procStmt FROM 'SELECT * FROM CONFIGURATION."Files" WHERE ModifiedOn <= CURRENT_DATE - INTERVAL ''10'' MINUTE ';
OPEN procCur;
EXECUTE IMMEDIATE 'SET FILES STORE TO "' + TheStoreName + '"';
EXECUTE IMMEDIATE 'DELETE FILE "' + TheDelFileName + '" FROM STORE "' + TheStoreName + '"';
CLOSE procCur;
END
VERSION 1.00

This fails (only difference: the line "FETCH FROM..." added):
=====================================================
As soon as the line "FETCH FIRST ................" is added, the job fails with the later error message:
CREATE JOB "BackupTest1312"
RUN AS "System"
FROM DATE '2016-12-13' TO DATE '2016-12-13'
EVERY 5 MINUTES ON MON, TUE, WED, THU, FRI, SAT, SUN
BETWEEN TIME '14:53:34' AND TIME '14:55:34.999'
BEGIN
DECLARE TheStoreName VARCHAR DEFAULT '';
DECLARE TheDelFileName VARCHAR DEFAULT '';
DECLARE procCur CURSOR FOR procStmt;
SET TheStoreName = 'KFZMDataLocalBackup';
SET TheDelFileName = 'KFZMDataBackup-2016-12-01_1624.EDBBkp';
PREPARE procStmt FROM 'SELECT * FROM CONFIGURATION."Files" WHERE ModifiedOn <= CURRENT_DATE - INTERVAL ''10'' MINUTE ';
OPEN procCur;
FETCH FIRST FROM procCur ('Name') INTO TheDelFileName;
EXECUTE IMMEDIATE 'SET FILES STORE TO "' + TheStoreName + '"';
EXECUTE IMMEDIATE 'DELETE FILE "' + TheDelFileName + '" FROM STORE "' + TheStoreName + '"';
CLOSE procCur;
END
VERSION 1.00

This is the error message directly copied from the logged events (identical both at "Prepare statement" and "Execute job"):
An error was found in the statement at line 12 and column 19 (Missing SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, GRANT, REVOKE, BACKUP, RESTORE, COMPARE, SET BACKUPS, MIGRATE, REPAIR, VERIFY, OPTIMIZE, IMPORT, EXPORT, DISCONNECT, REMOVE, PUBLISH, UNPUBLISH, SAVE, LOAD, SET UPDATES, COPY FI

BUT:
=====================================================
When executing exactly this job "as script" it prepares and runs perfectly!


Has anyone any idea?

Best regards
Joe

Joe

>Every line has been manually typed in (no copy and paste!).

Damn - there goes the easy fix suggestion


>CREATE JOB "BackupTest1312"
>RUN AS "System"
>FROM DATE '2016-12-13' TO DATE '2016-12-13'
>EVERY 5 MINUTES ON MON, TUE, WED, THU, FRI, SAT, SUN
>BETWEEN TIME '14:53:34' AND TIME '14:55:34.999'
>BEGIN
>DECLARE TheStoreName VARCHAR DEFAULT '';
>DECLARE TheDelFileName VARCHAR DEFAULT '';
>DECLARE procCur CURSOR FOR procStmt;
>SET TheStoreName = 'KFZMDataLocalBackup';
>SET TheDelFileName = 'KFZMDataBackup-2016-12-01_1624.EDBBkp';
>PREPARE procStmt FROM 'SELECT * FROM CONFIGURATION."Files" WHERE ModifiedOn <= CURRENT_DATE - INTERVAL ''10'' MINUTE ';
>OPEN procCur;
>FETCH FIRST FROM procCur ('Name') INTO TheDelFileName;
>EXECUTE IMMEDIATE 'SET FILES STORE TO "' + TheStoreName + '"';
>EXECUTE IMMEDIATE 'DELETE FILE "' + TheDelFileName + '" FROM STORE "' + TheStoreName + '"';
>CLOSE procCur;
>END
>VERSION 1.00
>
>When executing exactly this job "as script" it prepares and runs perfectly!

That baffles me - I didn't think there would be any difference.

Stage 1. Lets see if what you think is the problem is the problem. Comment out the two lines after the fetch first and see what happens.

Roy Lambert

Hi Joe,

I'm assuming that you want to delete all of the backups older than 10 days, so here is basically your same job looping through the entire retrieved list of the files that are delete candidates.  The changes that I made to your script was to set the files store BEFORE preparing the statement and removing the configuration. portion of the table specifier.  

CREATE JOB "BackupTest1312"
RUN AS "System"
FROM DATE '2016-12-13' TO DATE '2016-12-14'
EVERY 1 MINUTES ON MON, TUE, WED, THU, FRI, SAT, SUN
BETWEEN TIME '00:00:01' AND TIME '12:59:59.999'
BEGIN
DECLARE TheStoreName VARCHAR DEFAULT '';
DECLARE TheDelFileName VARCHAR DEFAULT '';
DECLARE procCur INSENSITIVE CURSOR FOR procStmt;
SET TheStoreName = 'KFZMDataLocalBackup';

EXECUTE IMMEDIATE 'SET FILES STORE TO "' + TheStoreName + '"';
PREPARE procStmt FROM 'SELECT * FROM files WHERE ModifiedOn <= CURRENT_DATE - INTERVAL ''10'' MINUTE ';
OPEN procCur;
FETCH FIRST FROM procCur ('Name') INTO TheDelFileName;
WHILE NOT EOF(procCur) DO
 EXECUTE IMMEDIATE 'DELETE FILE "' + TheDelFileName + '" FROM STORE "' + TheStoreName + '"';
 FETCH NEXT FROM procCur ('Name') INTO TheDelFileName;
 END WHILE;
CLOSE procCur;
END
VERSION 1.00

Hello Roy and Terry,

thank you for your answers!

@Roy: =============================================================================
I did as you said:
Commented out BOTH the lines "EXECUTE ... SET FILE..." AND "EXECUTE...DELETE...".
Result: The log says the job has been executed (of course without any result that can be prooved) - at least no error.

I did next:
Commented out ONLY the 2nd line "EXECUTE...DELETE...".
Result: The log says the job has been executed (of course without any result that can be prooved) - at least no error.

I did next:
Commented out nothing. This is the state as in my initial post.
Result: Error 700 : An error was found in the statement at line 12 and column 19 (Missing SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, GRANT, REVOKE, BACKUP, RESTORE, COMPARE, SET BACKUPS, MIGRATE, REPAIR, VERIFY, OPTIMIZE, IMPORT, EXPORT, DISCONNECT, REMOVE, PUBLISH, UNPUBLISH, SAVE, LOAD, SET UPDATES, COPY FI


@Terry: =============================================================================
Yes, you are right: the ultimate goal some day should be to delete all files older than xxx days.
Thank you for widening the job with the loop. But for this moment and to make sure that at least one file (that has been investigated by the system itself) will be deleted, I want to disclaim the loop. Later, if perhaps the job will do with one file, it can be widened with the loop.
I modified my job according to your suggestion and without the loop:

CREATE JOB "BackupTest1412"
RUN AS "System"
FROM DATE '2016-12-14' TO DATE '2016-12-14'
EVERY 5 MINUTES ON MON, TUE, WED, THU, FRI, SAT, SUN
BETWEEN TIME '09:52:34' AND TIME '09:55:34.999'
BEGIN
DECLARE TheStoreName VARCHAR DEFAULT '';
DECLARE TheDelFileName VARCHAR DEFAULT '';
DECLARE procCur CURSOR FOR procStmt;
SET TheStoreName = 'KFZMDataLocalBackup';
EXECUTE IMMEDIATE 'SET FILES STORE TO "' + TheStoreName + '"';
PREPARE procStmt FROM 'SELECT * FROM CONFIGURATION."Files" WHERE ModifiedOn <= CURRENT_DATE - INTERVAL ''10'' MINUTE ';
OPEN procCur;
FETCH FIRST FROM procCur ('Name') INTO TheDelFileName;
EXECUTE IMMEDIATE 'DELETE FILE "' + TheDelFileName + '" FROM STORE "' + TheStoreName + '"';
CLOSE procCur;
END
VERSION 1.00

Result:
*** WOW!!! DID THE JOB!! The first file in the store has gone to hell (correct) and the log shows no errors! *****


Encouraged by this success now I implemented the whole loop:

CREATE JOB "BackupTest1412"
RUN AS "System"
FROM DATE '2016-12-14' TO DATE '2016-12-14'
EVERY 5 MINUTES ON MON, TUE, WED, THU, FRI, SAT, SUN
BETWEEN TIME '10:08:34' AND TIME '10:11:34.999'
BEGIN
DECLARE TheStoreName VARCHAR DEFAULT '';
DECLARE TheDelFileName VARCHAR DEFAULT '';
DECLARE procCur CURSOR FOR procStmt;
SET TheStoreName = 'KFZMDataLocalBackup';
EXECUTE IMMEDIATE 'SET FILES STORE TO "' + TheStoreName + '"';
PREPARE procStmt FROM 'SELECT * FROM CONFIGURATION."Files" WHERE ModifiedOn <= CURRENT_DATE - INTERVAL ''10'' MINUTE ';
OPEN procCur;
FETCH FIRST FROM procCur ('Name') INTO TheDelFileName;
WHILE NOT EOF(procCur) DO
  EXECUTE IMMEDIATE 'DELETE FILE "' + TheDelFileName + '" FROM STORE "' + TheStoreName + '"';
  FETCH NEXT FROM procCur ('Name') INTO TheDelFileName;
END WHILE;
CLOSE procCur;
END
VERSION 1.00

Result:
*** WOW!!! DID THE JOB!! All files in the store according to the criteria have gone to hell (correct) and the log shows no errors! *****


What to say now????

For me, no real logic can be seen why this works now compared to my "old" statements (which I have taken from the EDB documentation and other posts out of this forum dealing with a similar subject).

Anyway: THANK YOU ALL A THOUSAND TIMES! You saved my life and bewared my heart attack.

I will play around with this code now, insert remarks for documentation, and to make it perfectly working under all conditions. In 2 or 3 days I will give a new and hopely good feedback here in this thread.

Thank you!
Joe

Joe


Now Terry has come up with solution I can sort of understand it. What I can't understand is why it works as a script but not as a job.

Playing in EDBManager if you execute SELECT * FROM CONFIGURATION."Files" from the context of the database you get zero lines since (I guess) you're asking for a store based at some weird directory. Open a store so the statement is executed in its context and the system knows where the directory is and gives a list of files.

Thinking about it I suppose setting the store is a bit like setting a parameter in a query. You have to do it with the query closed.

One thing to bear in mind for future debugging with scripts (especially those containing EXECUTE IMMEDIATE) you have to be wary of the line number quoted in error messages. I have asked Tim if it would be possible to get the line itself shown.

Roy Lambert

Hello all,

I would just like to give a new and POSITIVE feedback. The last 2 days I tested the job with differing criteria and scheduling, and besides that I expanded it with the creation of a new backup file every day. Now everything is running with full satisfaction.

Again - thank you - and Merry Christmas to all !

Joe

Joe,

Sorry for the delay in responding, but I thought I would chime in with some "why", even though you've got it working properly now.

<< This fails (only difference: the line "FETCH FROM..." added): >>

The reason why is that the FETCH is occurring on an empty result set, thus the result of the FETCH is NULL.  Thus, when you go to build the SQL statement dynamically, you're trying to add NULL to a string, which results in NULL.

The key fixes are:

1) Make sure that you issue the SET FILES STORE TO statement *before* querying the Configuration.Files system information table, per Terry's suggestion.

2) Make sure that you *always* check the result of a query by using the ROWCOUNT() function:

http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=ROWCOUNT

So, the fixed job would be:

CREATE JOB "BackupTest1312"
RUN AS "System"
FROM DATE '2016-12-13' TO DATE '2016-12-13'
EVERY 5 MINUTES ON MON, TUE, WED, THU, FRI, SAT, SUN
BETWEEN TIME '14:53:34' AND TIME '14:55:34.999'
BEGIN
DECLARE TheStoreName VARCHAR DEFAULT '';
DECLARE TheDelFileName VARCHAR DEFAULT '';
DECLARE procCur CURSOR FOR procStmt;
SET TheStoreName = 'KFZMDataLocalBackup';
SET TheDelFileName = 'KFZMDataBackup-2016-12-01_1624.EDBBkp';
EXECUTE IMMEDIATE 'SET FILES STORE TO "' + TheStoreName + '"';
PREPARE procStmt FROM 'SELECT * FROM CONFIGURATION."Files" WHERE ModifiedOn <= CURRENT_DATE - INTERVAL ''10'' MINUTE ';
OPEN procCur;
IF (ROWCOUNT(procCur) > 0) THEN
FETCH FIRST FROM procCur ('Name') INTO TheDelFileName;
EXECUTE IMMEDIATE 'DELETE FILE "' + TheDelFileName + '" FROM STORE "' + TheStoreName + '"';
END IF;
CLOSE procCur;
END
VERSION 1.00

Tim Young
Elevate Software
www.elevatesoft.com