Beefy Boxes and Bandwidth Generously Provided by pair Networks
laziness, impatience, and hubris
 
PerlMonks  

Perl DBI escape reserved word in Ingres database

by jtech (Sexton)
on Feb 22, 2019 at 12:51 UTC ( #1230378=perlquestion: print w/replies, xml ) Need Help??

jtech has asked for the wisdom of the Perl Monks concerning the following question:

Hi ladies and gentlemen

Can you help me to scape a reserved double quoted column name from an Ingres database, please?

The difference is the column name open is between double quotes, probably because it is a reserved word in the Ingres database. The SQL query works fine using double quotes or not, in the SQL terminal.

The Perl code below works great:

my $sql = "SELECT DISTINCT id FROM mytable WHERE active = ?"; my $ref = $dbh->selectall_arrayref($sql,undef,'yes'); @id = map{ $_->[0] } @$ref; die join (", ", @id), "\n";

But, the Perl code below returns the error: "Argument "true" isn't numeric in subroutine entry at ./new.pl line 104."

my $sql = "SELECT DISTINCT id FROM mytable WHERE open = ?"; my $ref = $dbh->selectall_arrayref($sql,undef,'true'); @id = map{ $_->[0] } @$ref; die join (", ", @id), "\n";

So, I have escaped the quotes using the below chars without success:

'open' '\open\' "open" "\open\" '\"open\"' \"'open'\" '"open"' "'open'" \'\"open\"\' \"\'open\'\"

Also, it has been combined with q// and qq//

Replies are listed 'Best First'.
Re: Perl DBI escape reserved word in Ingres database
by choroba (Archbishop) on Feb 22, 2019 at 13:19 UTC
    The quote_identifier method should handle the correct quoting for you:
    my $sql = 'SELECT DISTINCT id FROM mytable WHERE ' . $dbh->quote_ident +ifier('open') . ' = ?';
    map{substr$_->[0],$_->[1]||0,1}[\*||{},3],[[]],[ref qr-1,-,-1],[{}],[sub{}^*ARGV,3]

      Curiously after applied "quote_ident" it doesn't return anything, not even an error, the @id is empty/undef. It works just fine in other columns but, not with a reserved word between double quotes. Also, I have double checked the data by SQL command and it is in there.

      I haven't played with substring yet and this little monster scares me tbh.

Re: Perl DBI escape reserved word in Ingres database
by poj (Abbot) on Feb 22, 2019 at 13:54 UTC

    Which is line 104 ?

    Do a test without the placeholder

    my $sql = q! SELECT DISTINCT "open" FROM mytable !; my $ref = $dbh->selectall_arrayref($sql); @id = map{ $_->[0] } @$ref; print join (", ", @id), "\n";
    poj
      It has returned "1", btw this is a boolean type column, intrigued.

      Got it!

      Solution: my $ref =  $dbh->selectall_arrayref($sql,undef,'1');

        It doesn't look like BOOLEAN is mentioned in DBD::Ingres so try

        my $sql = "SELECT DISTINCT id FROM mytable WHERE open = CAST(? AS BOOL +EAN)"; my $ref = $dbh->selectall_arrayref($sql,undef,'true'); @id = map{ $_->[0] } @$ref; die join (", ", @id), "\n";
        poj
      The line 104 is this one: my $sql = "SELECT DISTINCT id FROM mytable WHERE open = ?"
Re: Perl DBI escape reserved word in Ingres database
by jtech (Sexton) on Feb 22, 2019 at 15:08 UTC

    Got it!

    The column is a boolean type and the SQL select command returns "true" or "false" on terminal but, the BDI only accept "1" or "0" for the boolean type.

    Solution: my $ref =  $dbh->selectall_arrayref($sql,undef,'1');

Re: Perl DBI escape reserved word in Ingres database
by hdb (Monsignor) on Feb 22, 2019 at 13:48 UTC

    Just a wild guess: replace open with mytable.open

      the same problem, sadly it didn't change anything

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://1230378]
Approved by Discipulus
Front-paged by haukex
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others examining the Monastery: (4)
As of 2022-06-29 10:48 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    My most frequent journeys are powered by:









    Results (96 votes). Check out past polls.

    Notices?