JDBC - multiple database

Discussion in 'Java' started by cmk128@hotmail.com, Feb 23, 2007.

  1. Guest

    Hi
    If i invoke a select statement through JDBC, can JDBC send that to
    two different databases and combine the results for me?

    thanks
    from Peter ()
    , Feb 23, 2007
    #1
    1. Advertising

  2. hvt Guest

    On Feb 23, 9:06 am, wrote:
    > Hi
    > If i invoke a select statement through JDBC, can JDBC send that to
    > two different databases and combine the results for me?
    >
    > thanks
    > from Peter ()


    yep, this cud be done though u need to maintain two diff connection
    and statement instances to invoke jdbc query on two different
    databases.
    hvt, Feb 23, 2007
    #2
    1. Advertising

  3. Lew Guest

    Peter (cmk) wrote:
    >> If i invoke a select statement through JDBC, can JDBC send that to
    >> two different databases and combine the results for me?


    hvt wrote:
    > yep, this cud be done though u need to maintain two diff connection


    Please don't use such terse abbreviations. "Cud" is regurgitated grass that
    cattle chew. "diff" is a UNIX utility. "u" is a type of turn made by an
    automobile.

    > and statement instances to invoke jdbc query on two different
    > databases.


    That really depends on what you mean by the word "database". For example, in
    systems that have a "CREATE DATABASE" command you might be able to access more
    than one "DATABASE" through a single connection. Such systems refer to tables
    with a "database.table" syntax. You can even refer to multiple "DATABASEs" in
    the same SQL statement.

    - Lew
    Lew, Feb 23, 2007
    #3
  4. wrote:
    > If i invoke a select statement through JDBC, can JDBC send that to
    > two different databases and combine the results for me?


    Only if the the database server the statement is connected to
    via a connection supports the usage of remote/linked database
    servers in its SQL dialect.

    Arne
    =?UTF-8?B?QXJuZSBWYWpow7hq?=, Feb 24, 2007
    #4
  5. hvt Guest

    On Feb 24, 4:11 am, Lew <> wrote:
    > Peter (cmk) wrote:
    > >> If i invoke a select statement through JDBC, can JDBC send that to
    > >> two different databases and combine the results for me?

    > hvt wrote:
    > > yep, this cud be done though u need to maintain two diff connection

    >
    > Please don't use such terse abbreviations. "Cud" is regurgitated grass that
    > cattle chew. "diff" is a UNIX utility. "u" is a type of turn made by an
    > automobile.
    >
    > > and statement instances to invoke jdbc query on two different
    > > databases.

    >
    > That really depends on what you mean by the word "database". For example, in
    > systems that have a "CREATE DATABASE" command you might be able to access more
    > than one "DATABASE" through a single connection. Such systems refer to tables
    > with a "database.table" syntax. You can even refer to multiple "DATABASEs" in
    > the same SQL statement.
    >
    > - Lew


    lew thx fr ur suggestion ;) and all those dictionary explanations!!!
    hvt, Feb 24, 2007
    #5
  6. Lew Guest

    hvt wrote:
    > lew thx fr ur suggestion ;) and all those dictionary explanations!!!


    THX is a format for home stereo sound. :)
    <http://www.practical-home-theater-guide.com/thx-home-cinema.html>

    Seriously, it is a good idea to practice using full words and good grammar.
    here It will help you when you want to impress folks in the work world. Also
    it is much easier on other people in the newsgroup, especially the
    international community who may not have the immersion in American-style
    cell-phone textspeak
    <http://en.wikipedia.org/wiki/Newspeak>
    to help them read your posts.

    If your goal is to communicate. If not, then gradually you might find less and
    less useful response as people give up trying to adapt to the difficult text.


    Dz th hp u?

    Gosh, even I don't know what I just said. ;-) LOLLIGAG. WTF?

    <http://en.wikipedia.org/wiki/Leetspeak>

    - Lew
    Lew, Feb 24, 2007
    #6
  7. Lew wrote:
    > Peter (cmk) wrote:
    >>> If i invoke a select statement through JDBC, can JDBC send that to
    >>> two different databases and combine the results for me?

    >
    > hvt wrote:
    >> yep, this cud be done though u need to maintain two diff connection

    >
    > Please don't use such terse abbreviations. "Cud" is regurgitated grass
    > that cattle chew. "diff" is a UNIX utility. "u" is a type of turn made
    > by an automobile.
    >
    >> and statement instances to invoke jdbc query on two different
    >> databases.

    >
    > That really depends on what you mean by the word "database". For
    > example, in systems that have a "CREATE DATABASE" command you might be
    > able to access more than one "DATABASE" through a single connection.
    > Such systems refer to tables with a "database.table" syntax. You can
    > even refer to multiple "DATABASEs" in the same SQL statement.
    >

    'hvt' is dead wrong if transaction integrity is important to the OP.

    Unless you're using a transaction manager you can't guarantee that work
    units on the separate databases are all either committed or rolled back.
    Examples of this sort of framework are Pathway for HP's Guardian NonStop
    OS, BEA's Weblogic and IBM's MQ series.

    I don't think there's any such mechanism built into Java though I'll be
    happy to hear that I'm wrong.


    --
    martin@ | Martin Gregorie
    gregorie. | Essex, UK
    org |
    Martin Gregorie, Feb 24, 2007
    #7
  8. hvt Guest

    On Feb 24, 7:56 pm, Lew <> wrote:
    > hvt wrote:
    > > lew thx fr ur suggestion ;) and all those dictionary explanations!!!

    >
    > THX is a format for home stereo sound. :)
    > <http://www.practical-home-theater-guide.com/thx-home-cinema.html>
    >
    > Seriously, it is a good idea to practice using full words and good grammar.
    > here It will help you when you want to impress folks in the work world. Also
    > it is much easier on other people in the newsgroup, especially the
    > international community who may not have the immersion in American-style
    > cell-phone textspeak
    > <http://en.wikipedia.org/wiki/Newspeak>
    > to help them read your posts.
    >
    > If your goal is to communicate. If not, then gradually you might find less and
    > less useful response as people give up trying to adapt to the difficult text.
    >
    > Dz th hp u?
    >
    > Gosh, even I don't know what I just said. ;-) LOLLIGAG. WTF?
    >
    > <http://en.wikipedia.org/wiki/Leetspeak>
    >
    > - Lew


    lew i appreciate your and other group members concern over language
    used in the forum.

    Martin, as the question was posted:
    can JDBC send that to two different databases...
    this can be done by maintaining two different connection/statement
    object. I 've tried this for searching Weblogic portal repository and
    local application data through Lucene. Weblogic maintains it's portal
    repository data in pointbase (default database) and local application
    data was maintained in Oracle9i.
    If you see any problem in this please clarify.

    Now seeing this as how feasible is this approach for processing data
    through multiple databases, a solid framework is required (completely
    agree).
    hvt, Feb 25, 2007
    #8
  9. Martin Gregorie wrote:
    > Unless you're using a transaction manager you can't guarantee that work
    > units on the separate databases are all either committed or rolled back.
    > Examples of this sort of framework are Pathway for HP's Guardian NonStop
    > OS, BEA's Weblogic and IBM's MQ series.
    >
    > I don't think there's any such mechanism built into Java though I'll be
    > happy to hear that I'm wrong.


    All J2EE app servers has a built in transaction manager that can
    handle multiple XA compliant databases.

    Arne
    =?UTF-8?B?QXJuZSBWYWpow7hq?=, Feb 25, 2007
    #9
  10. hvt wrote:
    > Martin, as the question was posted:
    > can JDBC send that to two different databases...
    > this can be done by maintaining two different connection/statement
    > object. I 've tried this for searching Weblogic portal repository and
    > local application data through Lucene. Weblogic maintains it's portal
    > repository data in pointbase (default database) and local application
    > data was maintained in Oracle9i.
    > If you see any problem in this please clarify.
    >

    There's no problem with simply connecting to two or more databases as
    long as you're only reading them and you have no need for data
    consistency between the databases.

    However, if you're updating tables in one or more databases or if
    updates applied to one database are dependent on the content of other
    databases you have a potential problem. You can't guarantee that all
    updates are either committed or rolled back without using a transaction
    manager to enforce consistency across all the databases.

    The downside is that a transaction within the transaction manager will
    hold locks in all the databases your transaction accesses until all
    databases have successfully committed the transaction. If such a
    transaction is long running it can have a major impact on the
    performance and response times of the underlying databases.

    Note that the consistency issue arising from just connecting to several
    databases (i.e. not using a transaction manager) can be a problem for BI
    applications despite them being read-only: if one database is updated
    while you're retrieving data it could make the data set you're analyzing
    inconsistent. Visualize the situation where online stock control is
    being used, but supplier and customer accounts are only updated
    periodically: there's no way a BI application will get a consistent
    snapshot during the day. This the reason many organizations use data
    warehouses to support their BI systems. Once data is extracted from the
    operational databases and loaded into a data warehouse you know its
    consistent and will remain so until the next snapshot is extracted.

    HTH

    --
    martin@ | Martin Gregorie
    gregorie. | Essex, UK
    org |
    Martin Gregorie, Feb 26, 2007
    #10
  11. Arne Vajhøj wrote:
    > Martin Gregorie wrote:
    >> Unless you're using a transaction manager you can't guarantee that
    >> work units on the separate databases are all either committed or
    >> rolled back. Examples of this sort of framework are Pathway for HP's
    >> Guardian NonStop OS, BEA's Weblogic and IBM's MQ series.
    >>
    >> I don't think there's any such mechanism built into Java though I'll
    >> be happy to hear that I'm wrong.

    >
    > All J2EE app servers has a built in transaction manager that can
    > handle multiple XA compliant databases.
    >

    Thanks for the information. I've used transaction managers but not app
    servers so wasn't certain if they did the transaction management bit.

    IOW, if the OP is updating multiple databases in Java, he should only do
    it with the aid of an app server.


    --
    martin@ | Martin Gregorie
    gregorie. | Essex, UK
    org |
    Martin Gregorie, Feb 26, 2007
    #11
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Keith Wansbrough

    Re: jdbc help:sun.jdbc.odbc.JdbcOdbcDriver

    Keith Wansbrough, Aug 16, 2004, in forum: Java
    Replies:
    0
    Views:
    733
    Keith Wansbrough
    Aug 16, 2004
  2. Betty
    Replies:
    1
    Views:
    9,842
    Juha Laiho
    May 21, 2005
  3. Jesus M. Salvo Jr.
    Replies:
    2
    Views:
    3,994
    robert
    Feb 11, 2006
  4. Bruce
    Replies:
    4
    Views:
    734
    Bjorn Abelli
    Mar 25, 2006
  5. Lew
    Replies:
    0
    Views:
    500
Loading...

Share This Page