my $csv_dbh = DBI->connect( 'dbi:CSV...);
my $ora_dbh = DBI->connect( 'dbi:Oracle...);
my $fir_dbh = DBI->connect( 'dbi:Firebird...);
my $ora_sth = $ora_dbh->prepare(...);
my $fir_sth = $fir_dbh->prepare(...);
my $csv_sth = $csv_dbh->prepare("
        SELECT $cols
          FROM IMPORT(?) AS T1
  NATURAL JOIN IMPORT(?) AS T2 ...
");
$csv_sth->execute( $fir_sth, $ora_sth );
[download]

Thank you both for your answers, I will dig in and start reading up on both options.

I don't believe a true cross-server join is possible. Depending on your reporting requirements, or how you are going to interact with the data, you could get at the data with a lot less coding work by using something like Class::DBI. You would essential create two classes, on for each DB, then declare the has_a/many relationships between the two. The underlying code would take care of joins the data when accessing the relationships.

From the CDBI pod:

NOTE: The two classes in a relationship do not have to be in the same database, on the same machine, or even in the same type of database! It is quite acceptable for a table in a MySQL database to be connected to a different table in an Oracle database, and for cascading delete etc to work across these. This should assist greatly if you need to migrate a database gradually.

All of the above suggestions are excellent. Let me give you the most basic form of a generic solution. This is probably not the best solution, but hopefully seeing some code will whet your mind a bit. I've left out gobs of error handling, since you said you are already familiar with the basics of DBI.

use strict; use warnings;
use DBI;

#replace these with the correct connect strings, of course
my $usage_db  = DBI->connect('dbi:ODBC:usage','','');
my $detail_db = DBI->connect('dbi:ODBC:detail','','');

my $usage_st = $usage_db->prepare(q'
  SELECT uid,description,err_code FROM errors
  WHERE err_code = ?
');

my $detail_st = $detail_db->prepare(q'
  SELECT * FROM error_details
  WHERE uid = ?
');

#- CORE -#   

   my $fatals = get_general('FATAL'); # get all fatal errors;
   foreach my $record (@$fatals) {
     my $uid = shift @$record;
     my $detail = get_detail($uid);
  
     #output result and its detail
     print join('||',$uid, @$record, @$detail), "\n";
   }

#- END CORE -#

# this sub finds all records with a given err_code
sub get_general {
   my $code = shift;
   $usage_st->execute($code);
   my $res = $sth->fetchall_arrayref;
   return $res;
}

# this sub gets the details for a given uid
sub get_detail {
   my $uid = shift;
   $detail_st->execute($uid);
   my $res = $sth->fetchall_arrayref;
   return $res;
}
[download]

Terce,

Thanks for the reply. To clairify, I've already writen a few reporting scripts based on DBI and GD::Graph, I will be modifying them for this task, so yes the end-result needs to be perl accesable.

I've read about SQLite in sysadmin journal, it sounds cool, but I'm going to try the perl methods posted here first.