Recent

Author Topic: Working with multiple databases  (Read 1053 times)

devEric69

  • Full Member
  • ***
  • Posts: 225
Re: Working with multiple databases
« Reply #15 on: January 11, 2020, 08:45:56 pm »
The ideal would be to find a trick to CRUD by SQL on only one of the 2 bases, for all the records, of the first base, and of the second base (with words like "IN", "UNION", or stored procedures...).
Otherwise, maybe, tiOPF (https://wiki.freepascal.org/tiOPF) could wrap and talk with both databases in parallel.
« Last Edit: January 11, 2020, 08:50:44 pm by devEric69 »
use: Linux 64 bits (Ubuntu 18.04 LTS).
Lazarus version: 2.0.4 (svn revision: 62502M) compiled with fpc 3.0.4 - fpDebug \ Dwarf3.

valdir.marcos

  • Hero Member
  • *****
  • Posts: 965
Re: Working with multiple databases
« Reply #16 on: January 12, 2020, 03:48:26 am »
Have you looked at FOREIGN DATA WRAPPERs on the postgresql side?

A postgresql foreign days wrapper essentially maps a foreign database table with a postgresql table header.  Then, you can use a sql statement in PG rjoining tables/views in both databases. 

Ref pg documentation for PG v9.1 and greater, and here:
https://wiki.postgresql.org/wiki/Foreign_data_wrappers
Very interesting.

valdir.marcos

  • Hero Member
  • *****
  • Posts: 965
Re: Working with multiple databases
« Reply #17 on: January 12, 2020, 03:53:24 am »
The ideal would be to find a trick to CRUD by SQL on only one of the 2 bases, for all the records, of the first base, and of the second base (with words like "IN", "UNION", or stored procedures...).
Otherwise, maybe, tiOPF (https://wiki.freepascal.org/tiOPF) could wrap and talk with both databases in parallel.
Your idea is not usual, but database replication is:
https://en.wikipedia.org/wiki/Replication_(computing)
https://www.ibm.com/support/knowledgecenter/SSXVXZ_2.1.4/com.ibm.i2.ibase.admin.doc/What_is_Database_Replication.html
https://www.geeksforgeeks.org/data-replication-in-dbms/

Vodnik

  • Full Member
  • ***
  • Posts: 114
Re: Working with multiple databases
« Reply #18 on: January 13, 2020, 02:52:38 pm »
Thank you for ideas, Loquin and devEric69!
Never heard about this, investigating...

devEric69

  • Full Member
  • ***
  • Posts: 225
Re: Working with multiple databases
« Reply #19 on: January 13, 2020, 04:32:00 pm »
I don't know the technical and societal context of your project, but the simplest solution would be - if and only if it doesn't pose a "diplomatic" problem, and if it doesn't go against this constraint that makes the specificity of your project, of course - would be to do a SQL refactor first: to pump all the data of a database, and to reinject the data 3FN in the second one, and to work then only on the remaining database.
use: Linux 64 bits (Ubuntu 18.04 LTS).
Lazarus version: 2.0.4 (svn revision: 62502M) compiled with fpc 3.0.4 - fpDebug \ Dwarf3.