Native Postgresql uses the dollar-integer syntax for placeholders in prepared queries and doesn't support question marks at all, that is emulated by DBD::Pg. That is why you have to take care when you use question marks in SQL comments or as an operator, you have to escape them.
| [reply] |
for ?-containing operators, of course.
But I can't find a way via DBI to let a postgres server complain about a questionmark in comments. (tried server versions 14, 15, 16). This kinda makes sense because '--'-style comments used to be removed, although '/* */'-style comments were kept. (btw, this will be changed (to both included) in next Pg version, 15)
(It would be another good reason to use $dbh->{pg_placeholder_dollaronly} = 1;. To me the operator-with-questionmark case is reason enough to use it)
| [reply]
[d/l] |
Hmm, indeed, it seems that DBD::Pg handles question marks in comments correctly now (3.16.0).
#!/usr/bin/perl
use strict;
use warnings;
use DBI;
use feature qw/say/;
use Data::Dumper;
my %c = (
dbname => 'test',
host => 'localhost',
port => 5432,
user => 'test',
pw => 'test',
);
my $dbh = DBI->connect(
"dbi:Pg:dbname=$c{dbname};host=$c{host};port=$c{port};",
$c{user},
$c{pw}
);
die $DBI::errstr if $DBI::err;
my $sth = $dbh->prepare(
'select * from users
/* is this a comment
?
*/
where id = /* ? or */ ?'
);
my $rv = $sth->execute(1);
my $res = $sth->fetchall_arrayref({});
print Dumper $DBI::errstr, $rv, $res;
I distinctly remember that this used to be a problem a few years ago.
But you're right about the operator-with-questionmark case: there is actually a "?" operator in postgres (to test for a key in a jsonb object), and you have to escape it if you want to use it in a query with DBD::Pg (which makes the SQL incompatible if you want to reuse it with e.g. raw psql or Go/sqlc).
| [reply]
[d/l] |
as far as I understand you will still need to do
$sth->execute( $lastname, $firstname );
hence I don't know how this is easier, apart of (probably) being able to change the order?
| [reply]
[d/l] |