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

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