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

Re: Translate between DBI and SQL

Thread Previous
From:
John Scoles
Date:
February 8, 2019 23:43
Subject:
Re: Translate between DBI and SQL
Message ID:
YQBPR0101MB21967DF79B17EC79B31C5881D0690@YQBPR0101MB2196.CANPRD01.PROD.OUTLOOK.COM
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 <redtux1@gmail.com>
Sent: February 8, 2019 5:37 PM
To: dbi-users@perl.org
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


Thread Previous


nntp.perl.org: Perl Programming lists via nntp and http.
Comments to Ask Bjørn Hansen at ask@perl.org | Group listing | About