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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
toddvorosAuthor Commented:
Clarification:  Where Database1 is on DSN1 and Database2 is on DSN2, a completely separate DSN.
0
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
toddvorosAuthor Commented:
You are indeed correct. Thanx!
0
Suhas .Senior 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Perl

From novice to tech pro — start learning today.