Wonderful. It's always nice when you find out that, not only does your
idea have merit, but someone else has already figured out the details.
<grin>
Time to start refactoring...
--TWH
On Jul 29, 2004, at 6:06 PM, Jeff Zucker wrote:
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
