Perl DBI escape reserved word in Ingres database

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";
[download]

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";
[download]

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

'open'
'\open\'

"open"
"\open\"

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

'"open"'
"'open'"

\'\"open\"\'
\"\'open\'\"
[download]

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

Comment on Perl DBI escape reserved word in Ingres database Select or Download Code

Replies are listed 'Best First'.
Re: Perl DBI escape reserved word in Ingres database by choroba (Cardinal) 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') . ' = ?';` [download] `map{substr$_->[0],$_->[1]\|\|0,1}[\\|\|{},3],[[]],[ref qr-1,-,-1],[{}],[sub{}^ARGV,3]`	[reply] [d/l] [select]
Re^2: Perl DBI escape reserved word in Ingres database by jtech (Sexton) on Feb 22, 2019 at 14:02 UTC
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.	[reply]
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";` [download] poj	[reply] [d/l]
Re^2: Perl DBI escape reserved word in Ingres database by jtech (Sexton) on Feb 22, 2019 at 14:10 UTC
It has returned "1", btw this is a boolean type column, intrigued. Got it! Solution: `my $ref = $dbh->selectall_arrayref($sql,undef,'1');`	[reply] [d/l]
Re^3: Perl DBI escape reserved word in Ingres database by poj (Abbot) on Feb 22, 2019 at 15:05 UTC
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";` [download] poj	[reply] [d/l]
Re^4: Perl DBI escape reserved word in Ingres database by jtech (Sexton) on Feb 22, 2019 at 15:23 UTC
Re^2: Perl DBI escape reserved word in Ingres database by jtech (Sexton) on Feb 22, 2019 at 14:15 UTC
The line 104 is this one: `my $sql = "SELECT DISTINCT id FROM mytable WHERE open = ?"`	[reply] [d/l]
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');`	[reply] [d/l]
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`	[reply] [d/l] [select]
Re^2: Perl DBI escape reserved word in Ingres database by jtech (Sexton) on Feb 22, 2019 at 14:11 UTC
the same problem, sadly it didn't change anything	[reply]


P is for Practical
	PerlMonks