How to join two different databases in one sql select statement in Perl using DBI?

How can I code a single SQL select statement that references two different databases in the same SQL select statement in Perl using DBI?  I.E., There are two Microsoft SQL server databases, Database1 and Database2 .  I need to be able to do this in Perl:

     
select              d.MyData, t.OtherData
from                database1.dbo.TableInDatabase1 d
left outer join database2.dbo.TableInDatabase2 t on t.CommonIDKey = d.CommonIDKey

Any help on this is greatly appreciated.

Normally you connect to one database at time:

$DB = DBI->connect("DBI:ODBC:$DSN", $user, $pw) or die "Couldn't open database: '$DBI::errstr'; stopped";
$sth = $DB->prepare ($UseQuery) or die "couldn't do it";
$sth->execute() or die "Couldn't execute statement: $DBI::errstr; stopped";

but I need a solution that lets a SINGLE SQL statement in Perl access BOTH Database1 and Database2 in the same SQL statement.
Doing separate connects to each database and pulling data from each database independently is NOT an option.

Thank you.
.
.
toddvorosAsked:
Who is Participating?
 
wilcoxonCommented:
No.  A single connection is required.  Once you are connected to the server, you can access any database via SQL just like any other connection (via sqlcmd or whatever).  You can also switch databases using normal SQL commands ("use database2" I think).
0
 
wilcoxonCommented:
If you connect to one database, you can run any valid SQL - including SQL that references other databases.  If you use DBI to connect to either database1 or database2, you should be able to run your query as you have it written.
$DB = DBI->connect("DBI:ODBC:$DSN1", $user, $pw) or die "Couldn't open database: '$DBI::errstr'; stopped";
my $UseQuery = <<EOSQL;
select              d.MyData, t.OtherData 
from                database1.dbo.TableInDatabase1 d 
left outer join database2.dbo.TableInDatabase2 t on t.CommonIDKey = d.CommonIDKey 
EOSQL
$sth = $DB->prepare ($UseQuery) or die "couldn't do it";
$sth->execute() or die "Couldn't execute statement: $DBI::errstr; stopped";

Open in new window

0
 
toddvorosAuthor Commented:
That doesn't makes sense to me.  How does the SQL statement connect to Database2 when there is no connect open to it?  Each database must have some type of open connection in order to access it.  IE, how does the SQL get to DSN2 when only DSN1 has been opened in your example??
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
toddvorosAuthor Commented:
Clarification:  Where Database1 is on DSN1 and Database2 is on DSN2, a completely separate DSN.
0
 
wilcoxonCommented:
Are you saying that database1 and database2 are on completely different servers?  If so, how can you run your query in anything?  If you just mean that the DSN is different because the databases are different (but they are on the same server) then there is no problem.

DBI simply opens a connection using the specified DBD driver (DBD::ODBC in this case).  Once it is connected, it is mostly no different than if you connected to the database using sqlcmd (or whatever client you use to manually connect to your SQL Server databases).
0
 
wilcoxonCommented:
Also, if you have problems with DBD::ODBC, you can try DBD::Sybase (in theory, it should work for SQL Server (there's still mention of SQL Server in the docs at least) but I've never tried it).
0
 
toddvorosAuthor Commented:
They are on the same server, but have unique DSN's, so I assume two sth's are required to be merged somehow in order for this to work, and that's what I am trying to figure out how to code.
0
 
toddvorosAuthor Commented:
You are indeed correct. Thanx!
0
 
Suhas .QA ManagerCommented:
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I have recommended this question be closed as follows:

Accept: wilcoxon (http:#a40739416)

If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.

suhasbharadwaj
Experts-Exchange Cleanup Volunteer
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.