Re: Translate between DBI and SQL
Tie::Function can be used to bind $dbh->quote to a syntactical hash, so you
can interpolate arbitrary strings easier. When I do that I name the hash
%Q, and then it's safe to do things like
$sql_text = "select id from mytable where foo=$Q{$foo}";
rather than counting placeholders.
On Fri, Feb 8, 2019 at 4:37 PM Mike Martin wrote:
> 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
>
>
--
"I don't know about that, as it is outside of my area of expertise." --
competent specialized practitioners, all the time
Re: Translate between DBI and SQL
Short answer, assuming you are including hard-coded SQL into a Perl script:
Place the textual SQL, without any additional backslashes or extra quotes or
whatsoever,
within the following:
my $sql_text = q(
);
Unless you have a closing ")" without a preceding opening "(" in that SQL text
(which would normally be invalid SQL)
the above should be valid Perl, resulting in a valid SQL statement in $sql_text
(to be passed to a DBI prepare or so).
In the unlikely event that you would have unbalanced quoted parentheses in your
SQL,
you can replace the delimiters for the q operator by something else, viz.
something not occurring in your SQL text, e.g.:
my $sql_text = q#
SELECT ')', "col_a"
FROM tblc
ORDER BY "col_a"
#;
-- Peter.
8 februari 2019 23:37:17 +01:00, skrev Mike Martin :
> 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
>
>
RE: Translate between DBI and SQL
P.S. This may also help: http://www.dispersiondesign.com/articles/perl/perl_escape_characters Short answer: Use single-quoted strings whenever possible - they have the fewest characters to escape - only backslash and single quote - and you can escape both by preceding with a backslash. Use dot operator and double-quoted strings for special characters and new-lines. Use the dot and dot-equals operators for multi-line sql. something like this: my($my_sql_string); $my_sql_string = 'SELECT \'column name with a space\' ' . "\n"; $my_sql_string .= 'FROM table_b' ; Ref the docs for anything really fancy (like non-english / non-printable-ascii Unicode or unusual character encodings). Brian Fennell The information contained in this electronic mail transmission is intended only for the use of the individual or entity named in this transmission. If you are not the intended recipient of this transmission, you are hereby notified that any disclosure, copying or distribution of the contents of this transmission is strictly prohibited and that you should delete the contents of this transmission from your system immediately. Any comments or statements contained in this transmission do not necessarily reflect the views or position of Radial or its subsidiaries and/or affiliates.
Re: Translate between DBI and SQL
On Friday 08 February 2019 22:37:17 Mike Martin wrote: > 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 Hi! Maybe following module could be interesting: https://metacpan.org/pod/DBIx::Perlish
RE: Translate between DBI and SQL
P.P.S. My last answer used single-quote for a column with a space in it in PostgreSQL - this is wrong, it should have been a double-quote for the name of the column with a space and a single quote for a quoted string literal in PostgreSQL. Better example: (Adapted from answer to question here https://dba.stackexchange.com/questions/118059/quoting-columns-with-spaces-in-postgresql ) $my_perl_sql_string= 'SELECT' . "\n"; $my_perl_sql_string.= ' tab."This IS My Column EXACTLY" AS col' . "\n"; $my_perl_sql_string.= 'FROM "My TabLE Name Contains Spaces Too!" tab' . "\n"; $my_perl_sql_string.= 'WHERE tab."ANOTHER UGLY COLUMN name" = \'MyFilterString\'' I don't know if PostgreSQL requires a trailing semi-colon when used in DBI/DBD or not. I know that Oracle SQL requires a trailing semicolon in sqlplus but not in DBI/DBD. I don't have a PostgreSQL database to try it out on. Try it both ways, and the one that works is the one to use. Brian Fennell The information contained in this electronic mail transmission is intended only for the use of the individual or entity named in this transmission. If you are not the intended recipient of this transmission, you are hereby notified that any disclosure, copying or distribution of the contents of this transmission is strictly prohibited and that you should delete the contents of this transmission from your system immediately. Any comments or statements contained in this transmission do not necessarily reflect the views or position of Radial or its subsidiaries and/or affiliates.
RE: Translate between DBI and SQL
Mike, If you have a complete example of what you are starting with and what you want to end up with your question would be clearer. If I understand you correctly: You start with raw SQL such as you might enter into a Postgres command line tool (for example psql). What you want is the equivalent Perl source code to define the identical SQL as a string literal. Please look at the docs here: https://metacpan.org/pod/perlop https://metacpan.org/pod/perlop#Quote-and-Quote-like-Operators https://metacpan.org/pod/perlop#Gory-details-of-parsing-quoted-constructs https://metacpan.org/pod/perlop#Additive-Operators(Especially the “.” Operator) https://metacpan.org/pod/perlop#Assignment-Operators(Especially the “.=” Assignment Operator) https://metacpan.org/pod/perlfunc#ord-EXPR https://metacpan.org/pod/perlfunc#chr-NUMBER And see if that helps. The information contained in this electronic mail transmission is intended only for the use of the individual or entity named in this transmission. If you are not the intended recipient of this transmission, you are hereby notified that any disclosure, copying or distribution of the contents of this transmission is strictly prohibited and that you should delete the contents of this transmission from your system immediately. Any comments or statements contained in this transmission do not necessarily reflect the views or position of Radial or its subsidiaries and/or affiliates.
Re: Translate between DBI and SQL
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 = "";
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 or alike
Cheers
From: Mike Martin
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
