in reply to RFC: Placeholder creation for SQL statements

#prepare
my $sth = $dbh->prepare('SELECT * FROM people WHERE lastname = ? AND f
+irstname = ?');

#execute with list of bindvars
$sth->execute( $lastname, $firstname );
[download]

But it's a bit cumbersome to adjust the bind values if the order chang
+es.

[download]

Re placeholders: PostgreSQL's DBD::Pg has an attribute 'pg_placeholder_dollaronly' which when set to 1 makes placeholdering (ordering or repeating) a little easier (fsvo easy):


$dbh->{pg_placeholder_dollaronly} = 1; # default is 0
my $sth = $dbh->prepare('SELECT * FROM people WHERE lastname = $1 AND 
+firstname = $2');

[download]

Replies are listed 'Best First'.
Re^2: RFC: Placeholder creation for SQL statements
by kikuchiyo (Hermit) on Aug 29, 2022 at 12:23 UTC
    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).

        <Reveal this spoiler or all in this thread>

        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]
Re^2: RFC: Placeholder creation for SQL statements
by LanX (Saint) on Aug 29, 2022 at 21:19 UTC
    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?

    Cheers Rolf
    (addicted to the Perl Programming Language :)
    Wikisyntax for the Monastery

[reply]
[d/l]

In Section Meditations