in reply to DBI Prepared Update and NULLs

undef is the correct way to indicate NULL to DBI when writing. If it didn't work for you, you should share the error or unexpected behaviour your got.

Replies are listed 'Best First'.
Re^2: DBI Prepared Update and NULLs
by paulbort (Hermit) on Jul 28, 2004 at 20:53 UTC
    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!
[reply]
[d/l]
[select]
      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]
[reply]
[d/l]

In Section Seekers of Perl Wisdom