SCRIPT
BEGIN
EXECUTE IMMEDIATE 'ALTER PROCEDURE "Replication_Client_DownloadFiles" ()
BEGIN

   DECLARE DevicesCursor CURSOR FOR DevicesStmt;
   DECLARE CurrentDevicesID INTEGER DEFAULT -1;
   DECLARE CurrentDeviceName VARCHAR DEFAULT '''';
   
   DECLARE DatabasesCursor CURSOR FOR DatabasesStmt;
   DECLARE CurrentDatabase VARCHAR DEFAULT '''';

   DECLARE UpdateFilesCursor CURSOR FOR UpdateFilesStmt;
   
   DECLARE ListOfTables CLOB;

   DECLARE CurrentFileName VARCHAR DEFAULT '''';

   DECLARE ClientInStore VARCHAR DEFAULT ''Replication_Client_In'';
   DECLARE ClientOutStore VARCHAR DEFAULT ''Replication_Client_Out'';
   DECLARE ServerInStore VARCHAR DEFAULT ''Replication_Server_In'';
   DECLARE ServerOutStore VARCHAR DEFAULT ''Replication_Server_Out'';
   DECLARE CurrentStore VARCHAR DEFAULT '''';

   ------------------------------------------------------------------------------------
   -- Get list of all update files from RemoteStore
   -- and load updates
   ----------------------------------------------------------------------------------

   ----------------------------------------------------------------------------------
   -- Table "Devices" contains only one record - the client computer
   ----------------------------------------------------------------------------------

   Execute Immediate ''Select DevicesID, upper(DeviceName) as DeviceName into ?,? from devices'' using CurrentDevicesID,CurrentDeviceName;
  
   EXECUTE IMMEDIATE ''SET FILES STORE TO "'' + ServerInStore + ''"'';
   PREPARE UpdateFilesStmt FROM ''SELECT * FROM Configuration.FILES where (OCCURS(? IN UPPER(Name)) > 0) ORDER BY CreatedOn'';
   OPEN UpdateFilesCursor using CurrentDeviceName;

   IF ROWCOUNT(UpdateFilesCursor) > 0 THEN
    
      PREPARE DatabasesStmt FROM ''SELECT Upper(DatabaseName) as Databasename FROM Databases where DevicesID = ?'';
      OPEN DatabasesCursor using CurrentDevicesID;
      FETCH FIRST FROM DatabasesCursor (''DatabaseName'') INTO CurrentDatabase;
   
      WHILE NOT EOF(DatabasesCursor) DO
        
         ----------------------------------------------------------------------------------
         -- Loop through all databases to be replicated and read the filenames,
         -- if the filename contains the current databasename, load the update
         ----------------------------------------------------------------------------------
   
         FETCH FIRST FROM UpdateFilesCursor (''Name'') INTO CurrentFileName;
   
         WHILE NOT EOF(UpdateFilesCursor) DO

            ----------------------------------------------------------------------------------
            -- Copy each found file into local In-Store
            ----------------------------------------------------------------------------------
   
            IF (OCCURS(CurrentDatabase IN UPPER(CurrentFileName)) > 0) THEN
   
               EXECUTE IMMEDIATE ''COPY FILE "'' + CurrentFileName + ''" IN STORE
                                 "'' + ServerInStore + ''" TO "'' +
                                 CurrentFileName + ''" IN STORE
                                 "'' + ClientInStore + ''"'';
               ----------------------------------------------------------------------------------
               -- Delete file on remoter server after sucessful download
               ----------------------------------------------------------------------------------
   
               EXECUTE IMMEDIATE ''DELETE FILE "'' + CurrentFileName + ''" FROM STORE
                                 "'' + ServerInStore + ''"'';
   
            END IF;
   
            FETCH NEXT FROM UpdateFilesCursor (''Name'') INTO CurrentFileName;
   
         END WHILE;

         -----------------------------------------------------------------------------------------
         -- load updates
         -----------------------------------------------------------------------------------------
   
         EXECUTE IMMEDIATE ''SET UPDATES STORE TO "'' + ClientInStore + ''"'';

         PREPARE SelectedUpdateFilesStmt FROM ''SELECT * FROM Configuration.UPDATES ORDER BY CreatedOn'';
         OPEN SelectedUpdateFilesCursor;
   
         FETCH FIRST FROM SelectedUpdateFilesCursor (''Name'') INTO CurrentFileName;
   
         WHILE NOT EOF(SelectedUpdateFilesCursor) DO
   
            EXECUTE IMMEDIATE ''LOAD UPDATES FOR DATABASE "'' + CurrentDatabase + ''"
                               FROM "'' + CurrentFileName + ''" IN STORE
                               "'' + ClientInStore + ''"'';
   
            ----------------------------------------------------------------------------------
            -- Delete file from local store after loading
            ----------------------------------------------------------------------------------
   
            EXECUTE IMMEDIATE ''DELETE FILE "'' + CurrentFileName + ''.EDBUpd" FROM STORE
                               "'' + ClientInStore + ''"'';
   
            FETCH NEXT FROM SelectedUpdateFilesCursor (''Name'') INTO CurrentFileName;
   
         END WHILE;

   
         FETCH NEXT FROM DatabasesCursor (''DatabaseName'') INTO CurrentDatabase;
   
      END WHILE;

     
   END IF;

END

VERSION 1.00';
END