Re: Help requested: Multiple explicit connections for cross-db Join's... ?!

Hi,

I use PRO*C ?SQL*NET AND 2 DATANASES - so I qualify:

1. If you connect with ... at :db_string your connections stays as long as you don't close the connection:

in between you can query as mucg as you like.

2) I don't know about joining, cause I haven't tried it. But ..

• you could work around it by splitting the joining in an loop or working with a subselect.
• I'v read somewhere that it is better to work with views on the remote-tables ?? (haven't tried it either)

Contact me if you more - or examples

-- 
-- 
ir Danny Roosens
Genesys Belgium
Matt Krom <krom_at_media.mit.edu> wrote:


>Couldn't find this in the rtfm FAQ...  you may know the answer if you

>have used ProC for awhile, and/or use SQLNet and/or work with multiple

>databases... 

>

>I'm writing an application that needs to maintain open connections to

>two separate databases (conceivably on different machines), and perform

>a 'join' between them.  In the Oracle docs, I've come across two

>different ways to do this, both with problems:

>

>1)  do an EXEC SQL CREATE LINK DB1 CONNECT 'name' USING 'password' AT

>'host'...  and an EXEC SQL CREATE SYNONYM table1 FOR table1_at_DB1...  then

>simply doing a join using two SYNONYM's.  

>

>problem:  As I understand it, the db LINKs open a new connection for

>each query at run-time; I need constant connections (to reduce

>overhead).  Am I misunderstanding?

>

>2)  do two of EXEC SQL CONNECT :username IDENTIFIED BY :password  

>   AT DB_NAME USING :db_string...  then use EXEC SQL AT DB_NAME

>SELECT...

>

>Problem:  As I see it, there's no way to join across two databases; you

>have to specify a single database in the EXEC SQL AT statement.

>

>Any help, asap, is greatly appreciated!  Thank you.

>

> 

>Matthew Krom

>krom_at_media.mit.edu

>Cambridge, Massachusetts

>

>http://krom.www.media.mit.edu/~krom/