Is there any way to delete directories that start with a certain prefix and are
older than 6 months?
I have the following code but it doesn't seem to be 100% effective. For
example, if the directory has a subdirectory then it doesn't get deleted.
---------------------------
--DirDel.mac
--This will search for directories older than six months and delete them.
con database
--create table to hold directories then cycle through the loop.
SET ERROR MESSAGE 705 OFF
DROP CURSOR c1
SET ERROR MESSAGE 705 ON
SELECT COUNT (*) +
INTO vCount +
INDICATOR viCount +
FROM SYS_TABLES +
WHERE SYS_TABLE_NAME = 'ttDirHold'
IF vCount > 0 THEN
DROP Table ttDirHold
ENDIF
CREATE TEMP Table ttDirHold (DirHold NOTE, ttDirHoldRecNo INTEGER)
AUTONUM ttDirHoldRecNo IN ttDirHold USING 1 1 NUM
INSERT INTO ttDirHold (DirHold) VALUES <DirectoryPath>
DECLARE c1 CURSOR FOR SELECT DirHold +
FROM ttDirHold
OPEN c1
FETCH c1 INTO vDirHold INDICATOR viDirHold
WHILE SQLCODE <> 100 THEN
SET ERROR MESSAGE 705 OFF
DROP CURSOR c2
SET ERROR MESSAGE 705 ON
SELECT COUNT (*) +
INTO vCount +
INDICATOR viCount +
FROM SYS_TABLES +
WHERE SYS_TABLE_NAME = 'ttDir'
IF vCount > 0 THEN
DROP Table ttDir
ENDIF
CREATE Temp Table ttDir (DirNote NOTE)
SET VAR vCurrDir = (CVAL('CurrDir'))
SET VAR vOutFile = (.vCurrDir + '\Dir.csv')
OUTPUT &vOutFile
DIR &vDirHold
OUTPUT SCREEN
--LOAD ttdir FROM &voutfile AS CSV
SET ERROR MESSAGES OFF
LOAD ttDir FROM &vOutFile
SET ERROR MESSAGES ON
SET VAR v6MonthsAgo DATE = (ADDMON(.#DATE,-6))
SET VAR v6MonthsAgoYr INTEGER = (IYR(.v6MonthsAgo))
SET VAR v6MonthsAgoMon INTEGER = (IMON(.v6MonthsAgo))
SET VAR v6MonthsAgoDay INTEGER = (IDAY(.v6MonthsAgo))
SET VAR v6MonthsAgo TEXT = ('20' +
+ (IFLT(.v6MonthsAgoYr,10,('0' + (CTXT(.v6MonthsAgoYr))),+
(CTXT(.v6MonthsAgoYr)))) +
+ (IFLT(.v6MonthsAgoMon,10,('0' + (CTXT(.v6MonthsAgoMon))),+
(CTXT(.v6MonthsAgoMon)))) +
+ (IFLT(.v6MonthsAgoDay,10,('0' + (CTXT(.v6MonthsAgoDay))),+
(CTXT(.v6MonthsAgoDay)))))
SELECT COUNT (*) +
INTO vCount +
INDICATOR viCount +
FROM ttDir +
WHERE DirNote NOT LIKE '20%' +
OR DirNote IS NULL +
OR DirNote >= .v6MonthsAgo
IF vCount > 0 THEN
DELETE ROWS +
FROM ttDir +
WHERE DirNote NOT LIKE '20%' +
OR DirNote IS NULL +
OR DirNote >= .v6MonthsAgo
ENDIF
DECLARE c2 CURSOR FOR SELECT DirNote +
FROM ttDir
OPEN c2
FETCH c2 INTO vDirNote INDIC viDirNote
WHILE SQLCODE <> 100 THEN
SET VAR vDelDir = (.vDirHold + '\' + .vDirNote)
SET VAR vChkFile = (CHKFILE(.vDelDir))
IF vChkFile = 1 THEN
SET ERROR MESSAGES OFF
DELETE &vDelDir
RMDIR &vDelDir
SET ERROR MESSAGES ON
ENDIF
FETCH c2 INTO vDirNote INDIC viDirNote
ENDWHILE
DROP CURSOR c2
FETCH c1 INTO vDirHold INDICATOR viDirHold
ENDWHILE
DROP CURSOR c1
disconnect
LABEL STOPIT
RETURN
