After getting this error:

Cannot open file
C:\Beckersoft\Stores\Replication_Client_In\EDV1_Umsatz_2013-06-23
14-51-48.818.EDBUpd.EDBUpd

when trying to load an update within a new script, I wonder when EDB
expects a file extension (resp. adds an extension) and when not.

Obviously there's a difference between 'SET FILES STORE' and 'SET UPDATE
STORE' as of the extensions.

Since I couldn't find anything in the help files: what is the exact rule?

Thanks Uli

Correction: SET FILES STORE or SET UPDATES STORE don't change the
extension, but it seems that "LOAD UPDATES..." expects a filename
*without* extension.

Since I rewrote some scripts and used the ".EDBUpd" extension, I ran
into the error.

Uli

Uli,

Your post is not totally clear to me. However, here is an observation: Whenever you call a COPY FILE or other "FILE" based command in EDB you must include the file extension, but whenever you call UPDATE DATABASE or other similar command you must not include the file extension.

COPY FILE "MyUpdate.EDBUpd" IN STORE "Updates" TO "MyUpdate.EDBUpd" IN STORE "Cloud"

LOAD UPDATES FOR DATABASE "MyDatabase" FROM "MyUpdate" IN STORE "Cloud"

Adam,

> Your post is not totally clear to me. However, here is an observation: Whenever you call a COPY FILE or other "FILE" based command in EDB you must include the file extension, but whenever you call UPDATE DATABASE or other similar command you must not include the file extension.
>
> COPY FILE "MyUpdate.EDBUpd" IN STORE "Updates" TO "MyUpdate.EDBUpd" IN STORE "Cloud"
>
> LOAD UPDATES FOR DATABASE "MyDatabase" FROM "MyUpdate" IN STORE "Cloud"

thanks and sorry about the vague post.

I was just wondering when I need to use a filename *with* or *without*
extension in order to load updates.

Your observation is correct.

In addition I found out that

  EXECUTE IMMEDIATE 'SET FILES STORE TO "' + ServerInStore + '"';
  PREPARE UpdateFilesStmt FROM 'SELECT * FROM Configuration.FILES ORDER
BY CreatedOn';

returns all files *with* extension

while

   PREPARE UpdateFilesStmt FROM 'SELECT * FROM Configuration.UPDATES
ORDER BY CreatedOn';

returns all files *without* extension.

Since I am collecting several files from a store and pass the filenames
as parameter to a procedure, the update failed because the filenames
included the extension.

I just would like to know the rule behind that and couldn't find
anything in the help files.

Thanks Uli

Uli,

<< Since I couldn't find anything in the help files: what is the exact rule?
>>

Anything that is EDB-specific and has a customizable file extension should
*not* have the file extension included (backup files, update files,
config/database catalog files, log files).  For general store file listings
and general file manipulation, you *must* use the file extension because EDB
does not care/need to do anything special with such files.

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

Tim Young
Elevate Software
www.elevatesoft.com

Tim,

> Anything that is EDB-specific and has a customizable file extension
> should *not* have the file extension included (backup files, update
> files, config/database catalog files, log files).  For general store
> file listings and general file manipulation, you *must* use the file
> extension because EDB does not care/need to do anything special with
> such files.

Thanks for the clarification! That makes sense.

Uli

Uli,

The reason is because it is allowable for programmers to change the file extensions of EDB files (such as updates, tables, blb files, etc)

To access this open EDBMgr, open a session, right click on it and select "Edit Session" then select "Customizations"

--

I agree that renaming file-extensions is a bit tedious in EDB, but once you can do it it is trivial, have a look at the following SQL is useful for the type of processes you are doing (copying Update files and then running update):

BEGIN
 DECLARE Crsr Cursor FOR Stmt;
 DECLARE FileName VARCHAR(120);

PREPARE Stmt FROM
'SELECT
  Name,
  CreatedOn
FROM Configuration."Files"
WHERE Name LIKE ''%.EDBUpd''
ORDER BY CreatedOn ';

OPEN Crsr;
FETCH FIRST FROM Crsr ('Name') INTO FileName;
WHILE NOT EOF(Crsr) DO
  SET FileName = REPLACE('.EDBUpd' WITH '' IN FileName);
  IF NOT (FileName IS NULL) OR NOT (FileName='') THEN
     --iterate the file-list and copy / rename the tables.
     EXECUTE IMMEDIATE
       'COPY FILE "'+FileName+'.EDBUpd"
        IN STORE Upload TO "' + FileName + '.EDBUpd" IN STORE CloudUpload';
     EXECUTE IMMEDIATE
       'RENAME FILE "'+FileName+
       '.EDBUpd" IN STORE Upload
        TO "'+FileName+'.OLDUpd"';
  END IF;
  FETCH NEXT FROM Crsr ('Name') INTO FileName;
END WHILE;          
CLOSE Crsr;

PREPARE Stmt FROM
'SELECT
 Name,
 CreatedOn
FROM Configuration."Files"
WHERE Name LIKE ''%.EDBUpd''
AND Name LIKE ''CloudUpdate%''
ORDER BY CreatedOn ';
OPEN Crsr;
FETCH FIRST FROM Crsr ('Name') INTO FileName;
WHILE NOT EOF(Crsr) DO
  IF NOT (FileName IS NULL) OR NOT (FileName='') THEN
    SET FileName = REPLACE('.EDBUpd' WITH '' IN FileName);
     --iterate the file-list and copy / rename the tables.
    EXECUTE IMMEDIATE
       'LOAD UPDATES FOR DATABASE "XXXXX" FROM "'+FileName+
       '" IN STORE Download
        IGNORE MISSING UPDATES';
    EXECUTE IMMEDIATE
       'RENAME FILE "'+FileName+
       '.EDBUpd" IN STORE Download
        TO "'+FileName+'.OLDUpd"';
  END IF;
  FETCH NEXT FROM Crsr ('Name') INTO FileName;
END WHILE;          
CLOSE Crsr;                   

Adam,

thanks for the sample code. That does the job.

Regards Uli

Adam,

<< I agree that renaming file-extensions is a bit tedious in EDB, but once
you can do it it is trivial, have a look at the following SQL is useful for
the type of processes you are doing (copying Update files and then running
update): >>

Just to point out:  you can just query the Updates system information table
in the Configuration database to get just the updates in a particular store:

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

It's a little easier than having to do the filtering yourself in code, and
it has additional information about the update files(s).  More importantly,
it has the *actual creation timestamp* from inside the update file, which is
safer to use with an ORDER BY by than the file timestamp, which can get
reset during copying around from various systems.

Tim Young
Elevate Software
www.elevatesoft.com

Tim

Thanks for this, it considerably simplifies my update processes and makes them more reliable at the same time. I still have trouble keeping track of all the information/configuration/system tables that are available in EDB.

Now I'll just have to write some new versions of procedures for my clients & get them installed ...