develooper Front page | perl.dbi.users | Postings from February 2019

Re: Translate between DBI and SQL

Thread Previous
John Scoles
February 8, 2019 23:43
Re: Translate between DBI and SQL
Message ID:
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

 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<>  or alike

From: Mike Martin <>
Sent: February 8, 2019 5:37 PM
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


Thread Previous Perl Programming lists via nntp and http.
Comments to Ask Bjørn Hansen at | Group listing | About