Tim Howell wrote:
Does anyone know of a module that allows queries to be executed across databases running on multiple RDBMSs?
One option I've thought of is to do really simple parsing of the SQL, and then building a temporary DB in memory on one of the DBMSs, then running the query, unmodified, against that DB.
That is what DBD::RAM and it's replacement DBD::AnyData already do. The example below creates in-memory tables from two different RDBMSs, then queries a join on the in-memory tables. It should work with any DBI accessible RDBMSs and there are also other ways to use in-memory tables (see the DBD::AnyData docs).
The example assumes you have
* a SQLite table called fruit_color with two columns (id,color) * an MS-Access table called fruit_name with two columns (id,name)
#!perl -w
use strict;
use DBI;
my %dsn = ( odbc => "dbi:ODBC(RaiseError=1):msaccess"
, sqlite => "dbi:SQLite(RaiseError=1):test.sl"
, anydata => 'dbi:AnyData(RaiseError=1):'
);
my $dbh = DBI->connect( $dsn{anydata} );
$dbh->ad_import('fruit_name' ,'DBI',DBI->connect( $dsn{odbc} ));
$dbh->ad_import('fruit_color','DBI',DBI->connect( $dsn{sqlite} ));
my $sth = $dbh->prepare("
SELECT name, color
FROM fruit_name N, fruit_color C
WHERE N.id = C.id
");
$sth->execute;
$sth->dump_results;
__END__Eventually I will release a DBD::AnyData version I have that performs actual heterogeneous queries (e.g. joins and subselects on tables from completely different RDBMSs without the need for the in-memory tables).
-- Jeff
