Well not really much DBI can do for you. You usually start from scratch trying
to write SQL that is not Driver Specific though that can be hard.
you are usually stuck with something like this
sub edit_sql {
my ($self, $sql) = @_;
if ($self->isPostgres) {
return InformixToPgsSQL::modify($sql);
} else {
return PgsToInformixSQL::modify($sql);
}
}
sub prepare {
my ($this, $sql) = @_;
$sql = $this->edit_sql($sql);
if (0 && exists $this->{'cursors'}->{$sql} && $sql !~ /ref_cron_exec/si) {
return ($this->{'cursors'}->{$sql});
}
else {
my $start = Time::HiRes::time;
my $qry;
eval { $qry = $this->{'dbh'}->prepare($sql); };
if ($qry) {
$this->{'cursors'}->{$sql} = $qry;
$this->{'stmts'}->{$qry} = $sql;
$this->report("Prepared", $sql, [], $start, Time::HiRes::time)
if $$this{'monitor'};
}
else {
my $err = "<no dbh>";
eval { $err = $this->{'dbh'}->errstr(); };
$this->log($err, $sql);
}
return ($qry);
}
}
where in this case I have a Module that does the 'prepare' and there I check
the SQL and make the required changes
the 'InformixToPgsSQL' and 'PgsToInformixSQL' just use regex to swap out any
SQL that is not compatible like
this
if ($sql =~ /\btoday\b/i) {
$sql =~ s/\btoday\b/ current_date /gi;
}
today vs current_date
One way around this situation is to write custom functions on the RDBMS side to
mimic the functionality ie a 'current_date' function to mimic 'today'
Many here will say start afresh and use an ORM like DBIx::Class or
Fey::ORM<http://search.cpan.org/dist/Fey-ORM> or alike
Cheers
________________________________
From: Mike Martin <[email protected]>
Sent: February 8, 2019 5:37 PM
To: [email protected]
Subject: Translate between DBI and SQL
Has anyone done any work on converting SQL queries between RDBMS and perl?
My particular interest is DBD::Pg but anything would be of use
It would be very useful when I am testing complex SQL, it's very easy to miss a
\ or quote between the two
Thanks
Mike
