Обсуждение: Query with tables from 2 different databases in Java
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
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
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
--- Mark Lewis <mark.lewis@mir3.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: 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
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
**********************************************************************
>>> 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
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 youto 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?
--- 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.
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
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 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
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 ('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);
dataareaid varchar(3) PRIMARY KEY,
description text NOT NULL
);
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');
$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;
$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;
from data_from_db1('SELECT * FROM AddressFormatHeading') A left join data_from_db2('SELECT * FROM def_dataarea') B on A."DATAAREAID" = B.dataareaid
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 |
>>> 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
Στις Παρασκευή 08 Ιούνιος 2007 11:29, ο/η Bart Degryse έγραψε: > Actually you can. [snip] Great writing Bart! Good job. -- Achilleas Mantzios