Thread: DBD::Pg: Placeholders not working

Greetings-

Recently I upgraded to PostgreSQL 7.2.1, running under debian's
"woody" release. Since then, perl scripts using DBD::Pg and placeholders
aren't working. The equivalent SQL statements, sent through DBD::Pg, work
fine, so I know it's a problem with the placeholders.  I've upgraded to
the most recent DBD::Pg (1.13).

Here's the symptom:

my $put = $dbh->prepare('UPDATE letters SET sc_auth_sum = ?, ' .
    'sc_anti_auth_sum = ?, ' .
    'sc_pro_auth_sum = ?, sc_auth_valence = ? WHERE letterid = ' .
    '?');

...

I generate the hash %out, which contains:
  DB<2> x %out
0  'sc_auth_sum'
1  '-1'
2  'letterid'
3  73012
4  'sc_auth_valence'
5  7
6  'sc_anti_auth_sum'
7  4
8  'sc_pro_auth_sum'
9  3


and then do:
    $put->execute($out{sc_auth_sum}, $out{sc_anti_auth_sum},
          $out{sc_pro_auth_sum}, $out{sc_auth_valence},
          $out{sc_letterid});
    $dbh->commit;

so at this point the four columns (sc_auth_sum, sc_auth_valence,
sc_anti_auth_sum, and sc_pro_auth_sum) should be calculated. But:

auth=# select sc_auth_sum, sc_auth_valence, sc_anti_auth_sum,
sc_pro_auth_sum from letters where letterid=73012;
 sc_auth_sum | sc_auth_valence | sc_anti_auth_sum | sc_pro_auth_sum
-------------+-----------------+------------------+-----------------
             |                 |                  |
(1 row)

HOWEVER, if I do:
  DB<3> $dbh->do('UPDATE letters SET sc_auth_sum=-1, sc_auth_valence=7,
sc_anti_auth_sum=4, sc_pro_auth_sum=3 WHERE letterid=73012')

  DB<4> $dbh->commit


it works fine:
auth=# select sc_auth_sum, sc_auth_valence, sc_anti_auth_sum,
sc_pro_auth_sum from letters where letterid=73012;
 sc_auth_sum | sc_auth_valence | sc_anti_auth_sum | sc_pro_auth_sum
-------------+-----------------+------------------+-----------------
          -1 |               7 |                4 |               3
(1 row)


Any advice will be most welcome.


----------------------------------------------------------------------
Andrew J Perrin - http://www.unc.edu/~aperrin
Assistant Professor of Sociology, U of North Carolina, Chapel Hill
clists@perrin.socsci.unc.edu * andrew_perrin (at) unc.edu

On Sun, May 19, 2002 at 10:48:42PM -0400, Andrew Perrin wrote:
> Greetings-
>
> Recently I upgraded to PostgreSQL 7.2.1, running under debian's
> "woody" release. Since then, perl scripts using DBD::Pg and placeholders
> aren't working. The equivalent SQL statements, sent through DBD::Pg, work
> fine, so I know it's a problem with the placeholders.  I've upgraded to
> the most recent DBD::Pg (1.13).
>
> Here's the symptom:
>
> my $put = $dbh->prepare('UPDATE letters SET sc_auth_sum = ?, ' .
>     'sc_anti_auth_sum = ?, ' .
>     'sc_pro_auth_sum = ?, sc_auth_valence = ? WHERE letterid = ' .
>     '?');
>


I have been using 7.2.1 almost as soon as it came out
and don't experience your problem.  My DBD::Pg is 1.12 however.

I really wonder why you don't have a much more readable statement like

----------------------------
 my $put = $dbh->prepare('UPDATE letters SET sc_auth_sum = ?,
       sc_anti_auth_sum = ?,
       sc_pro_auth_sum = ?, sc_auth_valence = ? WHERE letterid =
       ?');
---------------------------

Postgres does not care much if you put in line breaks.

Andrew Perrin <clists@perrin.socsci.unc.edu> writes:
> Any advice will be most welcome.

I have no idea what the problem is, but gathering more data seems
like a good plan.  Try turning on query logging at the postmaster
so you can see exactly what query the Perl code is sending ...

            regards, tom lane

Thanks - it was my mistake in the programming, not a postgres problem.

ap

----------------------------------------------------------------------
Andrew J Perrin - http://www.unc.edu/~aperrin
Assistant Professor of Sociology, U of North Carolina, Chapel Hill
clists@perrin.socsci.unc.edu * andrew_perrin (at) unc.edu


On Sun, 19 May 2002, Tom Lane wrote:

> Andrew Perrin <clists@perrin.socsci.unc.edu> writes:
> > Any advice will be most welcome.
>
> I have no idea what the problem is, but gathering more data seems
> like a good plan.  Try turning on query logging at the postmaster
> so you can see exactly what query the Perl code is sending ...
>
>             regards, tom lane
>