Query with tables from 2 different databases in Java

Lists: pgsql-jdbcpgsql-novice
From: "Loredana Curugiu" <loredana(dot)curugiu(at)gmail(dot)com>
To: pgsql-jdbc(at)postgresql(dot)org, pgsql-novice(at)postgresql(dot)org
Subject: Query with tables from 2 different databases in Java
Date: 2007-06-07 14:08:49
Message-ID: 1c23c8e70706070708m2f3be6f9o75571fd8a82ee390@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-jdbc pgsql-novice

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


From: Mark Lewis <mark(dot)lewis(at)mir3(dot)com>
To: Loredana Curugiu <loredana(dot)curugiu(at)gmail(dot)com>
Cc: pgsql-jdbc(at)postgresql(dot)org, pgsql-novice(at)postgresql(dot)org
Subject: Re: Query with tables from 2 different databases in Java
Date: 2007-06-07 14:13:54
Message-ID: 1181225634.31471.387.camel@archimedes
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-jdbc pgsql-novice

On Thu, 2007-06-07 at 17:08 +0300, 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?
>
> Thanks in advance!
>
>
> Regards,
> Loredana
>

Move the data to a single database, or else perform the query manually
by pulling back the matching results from each database individually and
then stitching them together.

-- Mark Lewis


From: "Tomeh, Husam" <htomeh(at)firstam(dot)com>
To: "Loredana Curugiu" <loredana(dot)curugiu(at)gmail(dot)com>, pgsql-jdbc(at)postgresql(dot)org, pgsql-novice(at)postgresql(dot)org
Subject: Re: [NOVICE] Query with tables from 2 different databases in Java
Date: 2007-06-07 14:21:09
Message-ID: F1B0F9305B343E43A1C3EECE48B853D5F948D8@CITGSNA01SXCH02.ana.firstamdata.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-jdbc pgsql-novice

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(at)postgresql(dot)org
[mailto:pgsql-novice-owner(at)postgresql(dot)org] On Behalf Of Loredana Curugiu
Sent: Thursday, June 07, 2007 7:09 AM
To: pgsql-jdbc(at)postgresql(dot)org; pgsql-novice(at)postgresql(dot)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
**********************************************************************


From: Sean Davis <sdavis2(at)mail(dot)nih(dot)gov>
To: Loredana Curugiu <loredana(dot)curugiu(at)gmail(dot)com>
Cc: pgsql-jdbc(at)postgresql(dot)org, pgsql-novice(at)postgresql(dot)org
Subject: Re: [NOVICE] Query with tables from 2 different databases in Java
Date: 2007-06-07 14:25:14
Message-ID: 4668154A.3090105@mail.nih.gov
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-jdbc pgsql-novice

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


From: Richard Broersma Jr <rabroersma(at)yahoo(dot)com>
To: Mark Lewis <mark(dot)lewis(at)mir3(dot)com>, Loredana Curugiu <loredana(dot)curugiu(at)gmail(dot)com>
Cc: pgsql-jdbc(at)postgresql(dot)org, pgsql-novice(at)postgresql(dot)org
Subject: Re: Query with tables from 2 different databases in Java
Date: 2007-06-07 14:34:10
Message-ID: 162676.53375.qm@web31814.mail.mud.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-jdbc pgsql-novice


--- Mark Lewis <mark(dot)lewis(at)mir3(dot)com> wrote:

> On Thu, 2007-06-07 at 17:08 +0300, 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?
> >
> > Thanks in advance!
> >
> >
> > Regards,
> > Loredana
> >
>
>
> Move the data to a single database, or else perform the query manually
> by pulling back the matching results from each database individually and
> then stitching them together.

if the two databases are part of the same cluster you can use DBLINK so that one database can see
the table of enterest from the other.
http://pgfoundry.org/projects/snapshot/

if the two databases are not contained in the same cluster you can use DBI-LINK.
http://pgfoundry.org/projects/dbi-link/

Then from java, you only have to perform a simple query from one database.

Regards,
Richard Broersma Jr.


From: "Bart Degryse" <Bart(dot)Degryse(at)indicator(dot)be>
To: <pgsql-jdbc(at)postgresql(dot)org>, <pgsql-novice(at)postgresql(dot)org>
Subject: Re: [NOVICE] Query with tables from 2 different databases inJava
Date: 2007-06-07 14:57:49
Message-ID: 4668390D.A3DD.0030.0@indicator.be
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-jdbc pgsql-novice

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(at)mail(dot)nih(dot)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


From: "Loredana Curugiu" <loredana(dot)curugiu(at)gmail(dot)com>
To: Bart(dot)Degryse(at)indicator(dot)be, pgsql-novice(at)postgresql(dot)org, pgsql-jdbc(at)postgresql(dot)org
Subject: Re: [NOVICE] Query with tables from 2 different databases inJava
Date: 2007-06-07 15:50:18
Message-ID: 1c23c8e70706070850h7696955aw2bfc8cee38d7b43f@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-jdbc pgsql-novice

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


From: Richard Broersma Jr <rabroersma(at)yahoo(dot)com>
To: Bart Degryse <Bart(dot)Degryse(at)indicator(dot)be>, pgsql-jdbc(at)postgresql(dot)org, pgsql-novice(at)postgresql(dot)org
Subject: Re: [NOVICE] Query with tables from 2 different databases inJava
Date: 2007-06-07 15:51:08
Message-ID: 394431.68251.qm@web31810.mail.mud.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-jdbc pgsql-novice


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

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.


From: Sean Davis <sdavis2(at)mail(dot)nih(dot)gov>
To: Loredana Curugiu <loredana(dot)curugiu(at)gmail(dot)com>
Cc: Bart(dot)Degryse(at)indicator(dot)be, pgsql-novice(at)postgresql(dot)org, pgsql-jdbc(at)postgresql(dot)org
Subject: Re: [NOVICE] Query with tables from 2 different databases inJava
Date: 2007-06-07 15:57:44
Message-ID: 46682AF8.1040901@mail.nih.gov
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-jdbc pgsql-novice

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


From: "Julius Stroffek" <julo(at)stroffek(dot)net>
To: "Mark Lewis" <mark(dot)lewis(at)mir3(dot)com>
Cc: "Loredana Curugiu" <loredana(dot)curugiu(at)gmail(dot)com>, pgsql-jdbc(at)postgresql(dot)org, pgsql-novice(at)postgresql(dot)org
Subject: Re: Query with tables from 2 different databases in Java
Date: 2007-06-08 08:18:55
Message-ID: 6ec50dfd0706080118u794797e9y4c21cb0d813da3ac@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-jdbc pgsql-novice

Hi Mark,

You can use JTA - Java Transaction API to encapsulate transaction atomicity
across different databases (and database vendors).

XADatasource, XAConnection, XAResource are objects you should be looking
for. A couple of links about the issue:

http://java.sun.com/products/jta/
http://java.sun.com/developer/EJTechTips/2005/tt0125.html#1
http://www.java-tips.org/java-ee-tips/enterprise-java-beans/introduction-to-the-java-transactio.html

However, I do not know how postgres sql driver implements those interfaces.

Regards,

Julius Stroffek

On 6/7/07, Mark Lewis <mark(dot)lewis(at)mir3(dot)com> wrote:
>
> On Thu, 2007-06-07 at 17:08 +0300, 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?
> >
> > Thanks in advance!
> >
> >
> > Regards,
> > Loredana
> >
>
>
> Move the data to a single database, or else perform the query manually
> by pulling back the matching results from each database individually and
> then stitching them together.
>
> -- Mark Lewis
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>


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
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-jdbc pgsql-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


From: Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)com>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: [NOVICE] Query with tables from 2 different databasesinJava
Date: 2007-06-08 09:45:01
Message-ID: 200706081245.01896.achill@matrix.gatewaynet.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-jdbc pgsql-novice

Στις Παρασκευή 08 Ιούνιος 2007 11:29, ο/η Bart Degryse έγραψε:
> Actually you can.
[snip]

Great writing Bart!
Good job.

--
Achilleas Mantzios