Hi,
How can exchange the data between different remote databases?  In other words, I have three locations and to move certain data from some tables to the other remote locations using jobs (maybe).  Any help or hint on how to achieve that?


Regards,
Abdulaziz Jasser

Abdulaziz ,

If you are using Client/Server its easy: use STORES.
There are local and remote STOREs, and you can copy files between them only by using EDB.
Please find the details here:
http://www.elevatesoft.com/manual?action=viewtopic&id=edb2&product=rsdelphiwin32&version=XE2&topic=Creating_Using_Stores


If in addition you are also wanting to synchronize data between databases, then you should also take a look here:

http://www.elevatesoft.com/manual?action=viewtopic&id=edb2&product=rsdelphiwin32&version=XE2&topic=Publish_Unpublish_Databases

http://www.elevatesoft.com/manual?action=viewtopic&id=edb2&product=rsdelphiwin32&version=XE2&topic=Saving_Loading_Databases

--
Fernando Dias
[Team Elevate]

Abdulaziz


If these are all running independently then look up PUBLISH in the OLH.

You might be able to use backup and restore depending on the configurations involved.

If all of the remote sessions are running on the same PC simultaneously then you can't easily move data between sessions and I'd be tempted to use tables rather than sql. Copying between tables is easier.

Roy Lambert [Team Elevate]

Fernando,
Roy,

Thanks for prompt reply.  However, can I use a JOB to create a store, export the data to it, and then import the data to the other database?  Can you show some JOBS script code?


Regards,
Abdulaziz Jasser

Abdulaziz ,

Yes you can use JOBs to do all that, but please note that you must be running an EDB server to execute the JOBs.
JOBs can only be executed on a server, not locally.

You can start by creating the stores, for that you don't need any JOB as you only have to create the stores once.
Then you can use a JOB to do the export/import/file transfer , whatever you want.

Here is an example.
This is a simplified version of a job that I use myself in one of my applications to make and then copy backups from branch databases to the headquarters server:


CREATE JOB "AGV2-ExpDados"
RUN AS "System"
FROM DATE '2012-01-01' TO DATE '2999-12-31'
EVERY 7 HOURS
BETWEEN TIME '06:00' AND TIME '23:00'
CATEGORY ''
BEGIN  DECLARE BKDate, BKTime, FName VARCHAR;

  -- composing the file name for the backup
  SET BKDate = CAST(CURRENT_DATE AS VARCHAR);
  SET BKDate = REPLACE('-' WITH '' IN BKDate);
  SET BKTime = CAST(CURRENT_TIME AS VARCHAR);
  SET BKTime = SUBSTRING(REPLACE(':' WITH '' IN BKTime), 1, 4) ;
  SET FName = 'MyBackup-'+ BKDate + '-' + BKTime  ;
  
  --backing up the database to a local store
  EXECUTE IMMEDIATE '
    BACKUP DATABASE "MyDatabase"
    AS "' + FName + '"
    TO STORE "LocalBackups"
    INCLUDE CATALOG ' ;

  -- copying the backup file to a remote store
  BEGIN
    EXECUTE IMMEDIATE '
     COPY FILE "' + FName + '.EDBBkp" IN STORE "LocalBackups"
     TO
     "' + FName + '.EDBBkp" IN STORE "RemoteBackups"' ;
  EXCEPTION
    -- this is to silently catch all the exceptions that
    -- might happen while transferring the file
    -- you may log the errors here, for example, or just ignore them
  END;
END


--
Fernando Dias
[Team Elevate]

Fernando wrote:

<<Yes you can use JOBs to do all that, but please note that you must be running an EDB server to execute the JOBs.  JOBs can only be executed on a server, not locally.>>

I am totally a ware of this...thanks.  But what if I want to backup certian tables..do I have include the catalog?  I don't want to end-up with correpted database.


Regards,
Abdulaziz Jasser

One more thing...does RESTORE DATABASE clause delete all the current data (for all tables or some tables) or just add data only?  In my case, I will delete certian data and add them using restore clause.  Is it going to be like this?


Regards,
Abdulaziz Jasser

Abdulaziz,

Yes you can include the catalog, or not, thats up to you, and you may also do a complete backup or only a few tables.
You can find the details here:
 
http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=BACKUP_DATABASE


--
Fernando Dias
[Team Elevate]

Abdulaziz ,

No, it's not going to do that, a restore always restores all the data.
It seems that what you want to do is to synchronizing data between databases, not backup/restore.

What you have to do is to PUBLISH your database (or some tables of your database).
When a database is PUBLISHed, any further updates, deletes and inserts are logged and can be saved to an update file using SAVE UPDATES statement, then you can copy the Updates file to the other location (perhaps using a JOB) and load the updates to the other copy of the database using LOAD UPDATES.
You can find the details here:
http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=PUBLISH_DATABASE

--
Fernando Dias
[Team Elevate]

Abdulaziz,

Another useful link:
http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=Replication

--
Fernando Dias
[Team Elevate]