Thread: Query with tables from 2 different databases in Java

Query with tables from 2 different databases in Java

From

"Loredana Curugiu"

Date:

07 June 2007, 14:08:59

Hi,

I need a query to use tables from 2 different databases and
that query to be executed from a .java file. What do you recommand?

Thanks in advance!

Regards,
Loredana

Re: Query with tables from 2 different databases in Java

From

Sean Davis

Date:

07 June 2007, 14:25:30

Loredana Curugiu wrote:
> Hi,
>
> I need a query to use tables from 2 different databases and
> that query to be executed from a .java file. What do you recommand?

Hi, Loredana.  You can't execute a query that includes two different
databases in postgresql.  However, you may want to look at the dblink
contributed module (in contrib/dblink in the postgresql source) that
provides an extension to do this.  I have no idea how this will behave
in the context of a java client, though.

Sean

Re: Query with tables from 2 different databases in Java

From

"Tomeh, Husam"

Date:

07 June 2007, 14:39:18

This is an add-on package for database links that is usually bundled with Postgres in the 'contrib' directory in the source code. Look for 'contrib/dblink'.

Husam

firstdba.googlepages.com

From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Loredana Curugiu
Sent: Thursday, June 07, 2007 7:09 AM
To: pgsql-jdbc@postgresql.org; pgsql-novice@postgresql.org
Subject: [NOVICE] Query with tables from 2 different databases in Java

Hi,

I need a query to use tables from 2 different databases and
that query to be executed from a .java file. What do you recommand?

Thanks in advance!

Regards,
Loredana

**********************************************************************
This message contains confidential information intended only for the use of the addressee(s) named above and may contain information that is legally privileged. If you are not the addressee, or the person responsible for delivering it to the addressee, you are hereby notified that reading, disseminating, distributing or copying this message is strictly prohibited. If you have received this message by mistake, please immediately notify us by replying to the message and delete the original message immediately thereafter.

Thank you.

FADLD Tag
**********************************************************************

Re: Query with tables from 2 different databases inJava

From

"Bart Degryse"

Date:

07 June 2007, 15:19:47

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.

>>> Sean Davis <sdavis2@mail.nih.gov> 2007-06-07 16:25 >>>
Loredana Curugiu wrote:
> Hi,
>
> I need a query to use tables from 2 different databases and
> that query to be executed from a .java file. What do you recommand?

Hi, Loredana. You can't execute a query that includes two different
databases in postgresql. However, you may want to look at the dblink
contributed module (in contrib/dblink in the postgresql source) that
provides an extension to do this. I have no idea how this will behave
in the context of a java client, though.

Sean

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Re: [JDBC] Query with tables from 2 different databases inJava

From

"Loredana Curugiu"

Date:

07 June 2007, 15:50:25

On 6/7/07, Bart Degryse <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?

Re: [JDBC] Query with tables from 2 different databases inJava

From

Richard Broersma Jr

Date:

07 June 2007, 15:51:19

--- Bart Degryse <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.

I am sure that the same result can be implemented in pljava as you were able to achieve in plperl
just incase the OP doesn't have a strength in one of the other other pl-languages.

I am not exactly sure how DBLINK works, but I guess that the calling database has to pull all of
(or at least most of) the records from the table of the linked database in order to develop a
JOIN, which would effectivly and always be a SEQ Scan that is pushed through a less that optimal
com connection.  If the table was very large, this would probably be a preformance killer.

But if the table is simply a smaller look-up table with less-than a few thousand records that can
easily be materialized in the calling database, DBLINK could be a workable solution.

Regards,
Richard Broersma Jr.

Re: [JDBC] Query with tables from 2 different databases inJava

From

Sean Davis

Date:

07 June 2007, 15:57:49

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

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

From

"Bart Degryse"

Date:

08 June 2007, 08:29:50

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%';

ADDRFORMAT	NAME	RECID	DATAAREAID	DATAAREA
national	This country	30	ash	Ashford
national	National	687181679	012	012
internatio	International countries	29	ash	Ashford
internatio	International countries	29	as0	not defined yet
national	This country	30	as0	not defined yet
national	National	216774985	011	Leuven
internatio	International	216774984	011	Leuven
national	National	451094066	hlm	Helmond
internatio	International	451094067	hlm	Helmond

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