Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 3 of 3 total |
Finding what files are in a STORE |
Mon, Apr 28 2014 5:20 AM | Permanent Link |
Jeff Cook Aspect Systems Ltd | 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 |
Mon, Apr 28 2014 6:24 AM | Permanent Link |
Adam Brett Orixa Systems | 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 |
Mon, Apr 28 2014 5:15 PM | Permanent Link |
Jeff Cook Aspect Systems Ltd | 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 > |
This web page was last updated on Tuesday, September 17, 2024 at 04:19 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |