Re: [NOVICE] Query with tables from 2 different databasesinJava - Mailing list pgsql-jdbc

Actually you can.

Create 2 srf function, one for each database. Then use them as tables in a select statement.

In my example I will select joined data from an Oracle and a PostgreSQL database from within another PostgreSQL database.

 

On Oracle

CREATE TABLE ADDRESSFORMATHEADING (
  ADDRFORMAT VARCHAR2(10 BYTE) DEFAULT '.' NOT NULL,
  NAME VARCHAR2(30 BYTE) DEFAULT '.' NOT NULL,
  DATAAREAID VARCHAR2(3 BYTE) DEFAULT 'dat' NOT NULL,
  RECID NUMBER(10) NOT NULL
)

Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) Values ('national', 'This country', 'ash', 30);
Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) Values ('be', 'Address Belgium', 'lil', 501);
Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) Values ('fr', 'Address France', 'lil', 496);
Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) Values ('it', 'Italie', 'bol', 3138);
Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) Values ('national', 'National', '012', 687181679);
Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) Values ('internatio', 'International countries', 'ash', 29);
Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) Values ('be', 'Beglie', 'bol', 3187);
Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) Values ('sp', 'Address Spain', 'bar', 1302174);
Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) Values ('internatio', 'International countries', 'as0', 29);
Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) Values ('national', 'This country', 'as0', 30);
Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) Values ('national', 'National', '011', 216774985);
Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) Values ('internatio', 'International', '011', 216774984);
Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) Values ('national', 'National', 'hlm', 451094066);
Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) Values ('internatio', 'International', 'hlm', 451094067);

 

On PostgreSQL database webdev

CREATE TABLE public.def_dataarea (
  dataareaid varchar(3) PRIMARY KEY,
  description text NOT NULL
);

INSERT INTO def_dataarea VALUES ('ash', 'Ashford');
INSERT INTO def_dataarea VALUES ('lil', 'Lille');
INSERT INTO def_dataarea VALUES ('bol', 'Bolognia');
INSERT INTO def_dataarea VALUES ('012', '012');
INSERT INTO def_dataarea VALUES ('bar', 'Barcelona');
INSERT INTO def_dataarea VALUES ('011', 'Leuven');
INSERT INTO def_dataarea VALUES ('hlm', 'Helmond');

 

On PostgreSQL database defrev_dev

CREATE TYPE db1_row AS ("ADDRFORMAT" varchar(10), "NAME" varchar(30), "DATAAREAID" varchar(3), "RECID" numeric(10,0));

CREATE TYPE db2_row AS (dataareaid varchar(3), description text);

 

CREATE OR REPLACE FUNCTION "public"."data_from_db1" (text) RETURNS SETOF db1_row AS
$body$
use DBI;
my $query = shift;
return unless (defined $query);
my $dbh_ora = DBI->connect('dbi:Oracle:database=bmssa;host=firev120-1.indicator.be;sid=mars', 'bmssa', '********')
              or die "Couldn't connect to database: " . DBI->errstr;
my $sel = $dbh_ora->prepare($query)
          or die "Couldn't prepare statement: " . $dbh_ora->errstr;
$sel->execute() or die "Couldn't execute statement: " . $sel->errstr;
while (my $row = $sel->fetchrow_hashref) {
  return_next($row);
}
$dbh_ora->disconnect;
return;
$body$
LANGUAGE 'plperlu' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

 

CREATE OR REPLACE FUNCTION "public"."data_from_db2" (text) RETURNS SETOF db2_row AS
$body$
  use DBI;
  my $query = shift;
  return unless (defined $query);
  my $dbh_pg = DBI->connect('dbi:Pg:dbname=webdev;host=10.100.1.21;port=5432', 'logstock_sys', '********')
               or die "Couldn't connect to database: " . DBI->errstr;
  my $sel = $dbh_pg->prepare($query)
            or die "Couldn't prepare statement: " . $dbh_pg->errstr;
  $sel->execute() or die "Couldn't execute statement: " . $sel->errstr;
  while (my $row = $sel->fetchrow_hashref) {
    return_next($row);
  }
  $dbh_pg->disconnect;
  return;
$body$
LANGUAGE 'plperlu' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

 

select A."ADDRFORMAT", A."NAME", A."RECID", A."DATAAREAID", COALESCE(B.description, 'not defined yet') as "DATAAREA"
from data_from_db1('SELECT * FROM AddressFormatHeading') A left join data_from_db2('SELECT * FROM def_dataarea') B on A."DATAAREAID" = B.dataareaid

where A."ADDRFORMAT" like '%natio%';

 

 

Some remarks

- Performance is influenced by the performance capacities of all three databases and of the network

- In PostgreSQL 8.2.4 return_next(...) is still buffered in memory, meaning that the database only returns the data to the calling environment when the return; statement is reached. So memory is heavily used and thus also a limiting factor in how much data you can fetch this way. Maybe in some future version return_next will return data immediately. That being said I have done joins of 25000 X 2000 records returning 300000 records. It takes a while, but is acceptable.

- If you connect to a certain database several time during your session performance gain is to be found in using seperate connect and disconnect functions which stores $dbh_ora and $dbh_pg in the global hash %_SHARED

- Carefully read http://www.postgresql.org/docs/8.2/interactive/plperl.html and the perldoc on DBI (http://search.cpan.org/~timb/DBI/DBI.pm) It's all in there...somewhere.

- I suppose this could be done with pljava as well but I have never tried.

 

Good luck

 

 

>>> Sean Davis <sdavis2@mail.nih.gov> 2007-06-07 17:57 >>>
Loredana Curugiu wrote:
>
>
> On 6/7/07, *Bart Degryse* <Bart.Degryse@indicator.be
> <mailto:Bart.Degryse@indicator.be>> wrote:
>
>     Personally I have no good experiences with neither dblink nor dbi-link.
>     They perferm only acceptably on very few records.
>     My approach now is to use srf plperl functions. That approach
>     requires you
>     to have a reasonable knowledge of perl, but is in my experience the
>     fastest
>     (or at least slow) one and certainly the most flexible one.
>
>
> How would you use tables from different databases in the same select
> using perl?

You cannot.  You would need to write a function that does the queries
within it and joins the results within the function.

Sean