Hi All

Been scratching my head (and beard) over this one.

I know how to COPY files to and between STOREs and how to delete them, but I
can't work out how to list the files in a STORE already.

Any hints?

Cheers

Jeff

--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz

2 steps:

1. SET Files Store TO <your store name>
("Files Store" is now set for the rest of the session, but can be changed at any time)

2. SELECT name FROM configuration.files

I would also suggest trying

SELECT * FROM configuration.files so you can see all the system variables (CreatedOn, ModifiedOn, Size) which EDB can return.

--

To make your life even easier, here is a SCRIPT which does some work on files in a STORE, it is used to delete files which have been renamed with a file extension ".OLD" by a separate procedure. This SCRIPT uses the "SET Files Store" code mentioned above

CREATE PROCEDURE "DeleteOLDFiles" ()
BEGIN
 DECLARE Crsr Cursor FOR Stmt;
 DECLARE StoreCrsr Cursor FOR StoreStmt;
 DECLARE FileName VARCHAR(120);
 DECLARE StoreName VARCHAR(120);

PREPARE StoreStmt FROM
'SELECT
Name
FROM Configuration."Stores"
WHERE NOT Name LIKE ''Cloud%''
AND NOT Name LIKE ''Reports%''  ';
OPEN StoreCrsr;
FETCH FIRST FROM StoreCrsr ('Name') INTO StoreName;
WHILE NOT EOF(StoreCrsr) DO
EXECUTE IMMEDIATE 'SET FILES STORE TO "'+StoreName+'"';
  PREPARE Stmt FROM
  'SELECT
  Name,
  ModifiedOn
  FROM Configuration."Files"
  WHERE Name LIKE ''%.OLD%''
  AND ModifiedOn <= Current_Date - INTERVAL ''7'' DAY ';
  OPEN Crsr;
  FETCH FIRST FROM Crsr ('Name') INTO FileName;
  WHILE NOT EOF(Crsr) DO
     IF NOT (FileName IS NULL) OR NOT (FileName='') THEN
        --iterate the file-list and copy / rename the taables.
        EXECUTE IMMEDIATE
          'DELETE FILE "'+FileName+' "
           FROM STORE "'+StoreName+'"';
     END IF;
     FETCH NEXT FROM Crsr ('Name') INTO FileName;
  END WHILE;
  CLOSE Crsr;                           
FETCH NEXT FROM StoreCrsr ('Name') INTO StoreName;
END WHILE;
  
END

Thanks Adam

That's exactly what I want.  I'm in the process of converting my DBISAM3 app
to EDB.  The old app uses zip files and I show them in a FileListBox to
allow for deleting old backups and restoring from backup.  This SELECT will
allow me to use a DBGrid to do the same thing.

You have also pre-empted another question.  I have converted my OPTIMIZE
routines and was pondering how to get rid of the .OLD files.  I had put that
problem on the back burner when I discovered that manually deleting the .OLD
files made no difference to the size of a backup.

Cheers and Thanks again.

Jeff

P.S.  As usual, I went back to the manual to see if I could have figured
this out for myself and, yes, there it is in black, white and yellow:-

8.15 SET FILES STORE

Sets the current files store used for configuration queries.

Syntax

SET FILES STORE TO <StoreName>
--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz

<Adam Brett> wrote in message
news:44B41BB5-3C5D-4905-BCB4-B23F44889683@news.elevatesoft.com...
>2 steps:
>
> 1. SET Files Store TO <your store name>
> ("Files Store" is now set for the rest of the session, but can be changed
> at any time)
>
> 2. SELECT name FROM configuration.files
>
> I would also suggest trying
>
> SELECT * FROM configuration.files so you can see all the system variables
> (CreatedOn, ModifiedOn, Size) which EDB can return.
>
> --
>
> To make your life even easier, here is a SCRIPT which does some work on
> files in a STORE, it is used to delete files which have been renamed with
> a file extension ".OLD" by a separate procedure. This SCRIPT uses the "SET
> Files Store" code mentioned above
>
> CREATE PROCEDURE "DeleteOLDFiles" ()
> BEGIN
>  DECLARE Crsr Cursor FOR Stmt;
>  DECLARE StoreCrsr Cursor FOR StoreStmt;
>  DECLARE FileName VARCHAR(120);
>  DECLARE StoreName VARCHAR(120);
>
> PREPARE StoreStmt FROM
> 'SELECT
> Name
> FROM Configuration."Stores"
> WHERE NOT Name LIKE ''Cloud%''
> AND NOT Name LIKE ''Reports%''  ';
> OPEN StoreCrsr;
> FETCH FIRST FROM StoreCrsr ('Name') INTO StoreName;
> WHILE NOT EOF(StoreCrsr) DO
> EXECUTE IMMEDIATE 'SET FILES STORE TO "'+StoreName+'"';
>   PREPARE Stmt FROM
>   'SELECT
>   Name,
>   ModifiedOn
>   FROM Configuration."Files"
>   WHERE Name LIKE ''%.OLD%''
>   AND ModifiedOn <= Current_Date - INTERVAL ''7'' DAY ';
>   OPEN Crsr;
>   FETCH FIRST FROM Crsr ('Name') INTO FileName;
>   WHILE NOT EOF(Crsr) DO
>      IF NOT (FileName IS NULL) OR NOT (FileName='') THEN
>         --iterate the file-list and copy / rename the taables.
>         EXECUTE IMMEDIATE
>           'DELETE FILE "'+FileName+' "
>            FROM STORE "'+StoreName+'"';
>      END IF;
>      FETCH NEXT FROM Crsr ('Name') INTO FileName;
>   END WHILE;
>   CLOSE Crsr;
> FETCH NEXT FROM StoreCrsr ('Name') INTO StoreName;
> END WHILE;
>
> END
>