Skip site navigation (1) Skip section navigation (2)

Re: [NOVICE] Query with tables from 2 different databasesinJava

From: "Bart Degryse" <Bart(dot)Degryse(at)indicator(dot)be>
To: <pgsql-jdbc(at)postgresql(dot)org>,<pgsql-novice(at)postgresql(dot)org>
Cc: "Loredana Curugiu" <loredana(dot)curugiu(at)gmail(dot)com>,"Sean Davis" <sdavis2(at)mail(dot)nih(dot)gov>
Subject: Re: [NOVICE] Query with tables from 2 different databasesinJava
Date: 2007-06-08 08:29:26
Message-ID: 46692F86.A3DD.0030.0@indicator.be (view raw or flat)
Thread:
Lists: pgsql-jdbcpgsql-novice
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%';
 

ADDRFORMATNAMERECIDDATAAREAIDDATAAREA
nationalThis country30ashAshford
nationalNational687181679012012
internatioInternational countries29ashAshford
internatioInternational countries29as0not defined yet
nationalThis country30as0not defined yet
nationalNational216774985011Leuven
internatioInternational216774984011Leuven
nationalNational451094066hlmHelmond
internatioInternational451094067hlmHelmond
 
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(at)mail(dot)nih(dot)gov> 2007-06-07 17:57 >>>
Loredana Curugiu wrote:
> 
> 
> On 6/7/07, *Bart Degryse* <Bart(dot)Degryse(at)indicator(dot)be 
> <mailto:Bart(dot)Degryse(at)indicator(dot)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

In response to

Responses

pgsql-novice by date

Next:From: Achilleas MantziosDate: 2007-06-08 09:45:01
Subject: Re: [NOVICE] Query with tables from 2 different databasesinJava
Previous:From: Julius StroffekDate: 2007-06-08 08:18:55
Subject: Re: Query with tables from 2 different databases in Java

pgsql-jdbc by date

Next:From: Achilleas MantziosDate: 2007-06-08 09:45:01
Subject: Re: [NOVICE] Query with tables from 2 different databasesinJava
Previous:From: Julius StroffekDate: 2007-06-08 08:18:55
Subject: Re: Query with tables from 2 different databases in Java

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group