Link to home
Start Free TrialLog in
Avatar of toddvoros
toddvorosFlag for United States of America

asked on

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.
.
.
Avatar of wilcoxon
wilcoxon
Flag of United States of America image

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

Avatar of toddvoros

ASKER

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??
Clarification:  Where Database1 is on DSN1 and Database2 is on DSN2, a completely separate DSN.
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).
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).
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.
ASKER CERTIFIED SOLUTION
Avatar of wilcoxon
wilcoxon
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You are indeed correct. Thanx!
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