Thanks for the re-assurance. The specific error message is DBD::Pg::st execute failed: ERROR:  parser: parse error at or near "," Every time I've run into that error before and I haven't misplaced a comma, it has been this roadblock.

The prepared statement is:

$st{ 'UpdDoc' } = $dbh->prepare( <<ENDSQL );
UPDATE
  img_Printer_Doc
SET
  prd_Copies    = ?,
  prd_Tray      = ?,
  prd_Size      = ?,
  prd_Duplex    = ?,
  prd_NUp       = ?,
  prd_Landscape = ?,
  prd_Scaling   = ?,
  prd_Redirect  = ?
WHERE
  prt_Number = ?
AND
  ref_IndexCode = ?;
ENDSQL
[download]



I'm executing it with the following: (Don't worry, the %cgiparms are strictly filtered)


    $st{ 'UpdDoc' }->execute(
                 $cgiparms{ 'copies' },
                 $cgiparms{ 'tray' },
                 $cgiparms{ 'size' },
                 $cgiparms{ 'duplex' },
                 $cgiparms{ 'nup' },
                 $cgiparms{ 'orientation' } eq 'Landscape',
                 $cgiparms{ 'scaling' },
                 $cgiparms{ 'redirect' },
                 $cgiparms{ 'NUMBER' },
                 $cgiparms{ 'DOCTYPE' }
                );
[download]

(prd_Landscape is a boolean, hence the eq.)

I'm on DBI 1.30, DBD::Pg 1.13. I didn't see anything that seemed relevant in the DBI or DBD::Pg changelog, so I'm assuming that I've done something stupid at this point.

--
Spring: Forces, Coiled Again!

What version of PostgreSQL is your database? 7.3 changed the handling of booleans. Booleans can only be 't', 'true', 'y', '1', or 'f', 'false', 'n', '0'. The quotes around the string are required: 0 and 1 are not allowed.

This creates a problem for DBD::Pg since it detects Perl "booleans" as integers, and passes them without quotes. I remember that 7.3 would sometimes produce a parse error when it got an integer literal instead of giving a type error.

One solution is to make the string explicit:

$cgiparms{ 'orientation' } eq 'Landscape' ? 't' : 'f'
[download]

   Null Values

   Undefined values, or "undef", can be used to indicate null values. 
+ However, care must
   be taken in the particular case of trying to use null values to qua
+lify a "SELECT"
   statement. Consider:

     SELECT description FROM products WHERE product_code = ?

   Binding an "undef" (NULL) to the placeholder will not select rows
   which have a NULL "product_code"! Refer to the SQL manual for your 
+database
   engine or any SQL book for the reasons for this.  To explicitly sel
+ect
   NULLs you have to say ""WHERE product_code IS NULL"" and to make th
+at
   general you have to say:

     ... WHERE (product_code = ? OR (? IS NULL AND product_code IS NUL
+L))

   and bind the same value to both placeholders. Sadly, that more gene
+ral syntax
   doesn't work for Sybase and MS SQL Server. However on those two ser
+vers
   the original ""product_code = ?"" syntax works for binding nulls.
[download]

This applies to the WHERE clause of a SELECT, UPDATE, or DELETE. Not the data in an UPDATE or INSERT. For UPDATEs, this should work:

my $sql = "UPDATE foo SET bar = ?";
$dbh->do($sql, {}, undef);
[download]

Once the placeholders are substituted, this should be the same as:

UPDATE foo SET bar = NULL;
[download]

my %col;
$col{foo}=1;
$col{bar}=undef;


my %vbph;
my @vbval;
foreach(sort keys %arg){
  if(defined $arg{$_}){
     $vbph{$_}='?';
     push @vbval,$arg{$_};    
  }else{
     $vbph{$_}='NULL';
  }
}
my $sql='update baz set '.
          join ', ',(map{$_.'='.$vb{$_}} sort keys %arg));
my $sth=$dbh->prepare_cached($sql);
$sth->execute(@vbval);
[download]

Thanks for the suggestion, I'll read up on prepare_cached. I know I can build the statement dynamically, that is what I've done on at least one previous occasion, but I thought that it really should work this way, so I'm trying to figure out what I'm doing wrong.

--
Spring: Forces, Coiled Again!