I can write a Procedure like this, to look for new files in a store on the server:

BEGIN
 DECLARE LatestDate TIMESTAMP;
 DECLARE Crsr CURSOR FOR Stmt;     
 DECLARE FileName VARCHAR(120);

EXECUTE IMMEDIATE 'SET FILES STORE TO "CloudReports"';
                                                    
PREPARE Stmt FROM
'
SELECT
MAX(ModifiedOn) as "Name"
FROM Configuration."Files"
WHERE UPPER(Name) LIKE ''%.FR3'
';

OPEN Crsr;
FETCH FIRST FROM Crsr ('Name') INTO LatestDate;
CLOSE Crsr;

IF CAST(LatestDate AS VARCHAR(20)) = '' OR LatestDate IS NULL THEN
 SET LatestDate = TIMESTAMP '2000-01-01 00:00';
END IF;

-----------------------------

Rather than:

MAX(ModifiedOn) as "Name"

I can also write:

MAX(CreatedOn) as "Name"

-----------------------------

I am getting confused, I am making changes to files and the ModifiedOn and CreatedOn dates are not changing as I would expect. i.e., after changing and saving a file its "ModifiedOn" date remains the same. If you copy a file between locations the CreatedOn date is incremented, but the ModifiedOn date remains the same!!

This is a Windows issue not EDB ... When I look in the Windows Explorer it shows the same dates as EDB does for "ModifiedOn" and "CreatedOn" ... But it makes writing file-management routines in EDB really tough.

Does anyone else do stuff like this in EDB and have a work-around?

Adam

Adam,

Why not just maintain a table called "FileNames" that contains a list of files in the directory and their last modified date. If a file appears in the directory that is not in FileNames, or its modified date is more recent, then it is a new file. You then process the file and when successful, add it to FileNames.

I've done this in the past with Delphi code and it works great. If you are monitoring more than one directory and don't want to store the complete path name, then I've done an MD5(UpperCase(Trim(FileName))) to get a checksum of the file name. (You can use any checksum you like, it doesn't have to be MD5)  If the checksum is not found in the table, then it is a new file. If the checksum is found, to eliminate the rare chance of a MD5 collision, I confirm the file name after looking up all of the file names with that checksum using a table Range. (Either that, or use a second checksum to avoid collision. If both checksums agree, then the file has been already been processed. The chance of two checksum algorithms producing the *same pair* of checksums on two different file names is zero, IMHO).

To find duplicate file contents (different file names with same file contents), I've done a checksum on the file contents and compared it with another checksum stored in the table. If you use two different checksums then you can eliminate the need to physically store the file's contents in the database. (I wrote an application to archive files in a database from multiple directories).

Barry

Adam,

<< I am getting confused, I am making changes to files and the ModifiedOn
and CreatedOn dates are not changing as I would expect. i.e., after changing
and saving a file its "ModifiedOn" date remains the same. If you copy a file
between locations the CreatedOn date is incremented, but the ModifiedOn date
remains the same!! >>

The ModifiedOn date/time is retrieved from the LastWriteTime attribute on
the file, and it won't be updated in Windows until all open file handles for
the file are closed.  What kind of files are you trying to enumerate ?

Tim Young
Elevate Software
www.elevatesoft.com

>>The ModifiedOn date/time is retrieved from the LastWriteTime attribute on
>>the file, and it won't be updated in Windows until all open file handles for
>>the file are closed.  What kind of files are you trying to enumerate ?

What I am trying to do is pass report-templates (I use Fast-Reports, they are FR3 files) around the offices of my cloud-connected clients.

The idea is:

1. I edit a report in London.
2. A JOB copies it to the cloud after seeing that it is younger than the version already up there.
3. Users start their computers, a JOB polls the cloud, and repeats this process.

However, when the report is copied over me->cloud->user the CreatedOn and ModifiedOn dates seem to vary in a bit of a random way, sometimes staying the same, sometimes being updated to "now" on the receiving machine.

I tried CreatedOn first but this is sometimes updated when the file is copied, disrupting the security of the copy process.

ModifiedOn seemed a surer bet, as it seemed to only be updated after editing the file. However having tried it, it doesn't work that well either. The exact ways these values are updated by windows seem to depend on a number of issues including the OS (I seem to see different behaviour on XP machines).

... I think I will just have to use some other more complex process, such as the one suggested by Barry.

Adam


What abut doing something slightly whacky. Add a version number after the extension so you would have a file name like report.fr3.7.

In actual use your software could ignore the final number but use it to decide wether to download or not.

Roy Lambert

Adam,

<< However, when the report is copied over me->cloud->user the CreatedOn and
ModifiedOn dates seem to vary in a bit of a random way, sometimes staying
the same, sometimes being updated to "now" on the receiving machine. >>

Are the dates being updated properly when they reach the cloud storage ?

Another alternative may be to do a hash/checksum on the files to see if
they've changed.  You could add a server procedure/native stored procedure
to handle this.

Tim Young
Elevate Software
www.elevatesoft.com

Thanks Roy & Tim

ROY:
>>What abut doing something slightly whacky. Add a version number after the extension so you would have a file >>name like report.fr3.7.

I am considering something similar to that. I am also considering adding code in my report-editing application so it logs the reports I have edited. The problem is the reports are XML, and sometimes I change them external to the report-editing application.

TIM
>>Are the dates being updated properly when they reach the cloud storage ?

Honestly I am not exactly sure! What seems to be happening is that either the value from the file as it is uploaded is used, or a current-timestamp is used.

I would be happy if:
CreatedOn was set to a current timestamp (i.e. this file has been posted up to the cloud at moment XXX)
ModifiedOn should remain as the date _I_ modified it, not be set to a current timestamp, however it seems that sometimes it is.


>>Another alternative may be to do a hash/checksum on the files to see if
>>they've changed.  You could add a server procedure/native stored procedure
>>to handle this.

I haven't done programming of this type before, though I am sure I could google it & figure it out. I have added my own external modules for procedures ... so that step would be OK.

Thanks for the input. If I think of something clever I will try to post it up here, as I think this is a problem other users may run into where they have files associated with a database application which they might want to copy between offices.

Adam,

<< I haven't done programming of this type before, though I am sure I could
google it & figure it out. I have added my own external modules for
procedures ... so that step would be OK. >>

No need, already done for you:

http://www.elevatesoft.com/manual?action=viewmethod&id=edb2&product=delphi&version=7&comp=TEDBSession&method=CalculateCRC32ForStream

Just use it with a TFileStream to get what you want.  Internally, EDB reads
large chunks of the stream at a time and does an efficient calc on the bytes
in memory, so you don't need to worry about performance.

If you have any other questions, please let me know.

Tim Young
Elevate Software
www.elevatesoft.com

>>No need, already done for you:

(

Wow. Thanks.