Beefy Boxes and Bandwidth Generously Provided by pair Networks
Think about Loose Coupling

DBI Prepared Update and NULLs

by paulbort (Hermit)
on Jul 28, 2004 at 19:57 UTC ( #378183=perlquestion: print w/replies, xml ) Need Help??

paulbort has asked for the wisdom of the Perl Monks concerning the following question:

I'm quite happy with DBI except one gaping hole, and I am hoping that someone has a solution for this. I would like to prepare an SQL UPDATE statement, and be able to pass undef as a parameter when I execute that statement, and have the underlying database (PostgreSQL) set that field to NULL. It seems a reasonable request, given that a SELECT will return a NULL field as undef.

I've searched and super-searched, and read through the docs in perldoc and CPAN, and only found one good match: 137005 suggests using '' (empty string) instead of undef, but '' and NULL are different values.

I know I can construct an UPDATE statement and $dbh->do() it, but a prepared statement makes for much more readable code, IMHO.

Any ideas? I would be very happy to find that I'm missing something obvious.

UPDATE: Problem Solved. PEBCAK (Problem Exists Between Chair And Keyboard.) Once I reduced the update to one column , the undef to NULL mapping works for that column, so one of my other columns is doing something stupid. Thanks to all who helped.

Spring: Forces, Coiled Again!

Replies are listed 'Best First'.
Re: DBI Prepared Update and NULLs
by diotalevi (Canon) on Jul 28, 2004 at 20:00 UTC
    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.
      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

      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' } );
      (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'
Re: DBI Prepared Update and NULLs
by gaal (Parson) on Jul 28, 2004 at 20:04 UTC
    Sounds like you're out of luck:
    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.
    -- DBI
      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);
      Once the placeholders are substituted, this should be the same as:
      UPDATE foo SET bar = NULL;
Re: DBI Prepared Update and NULLs
by lhoward (Vicar) on Jul 28, 2004 at 20:09 UTC
    You could build your update statment dynamically, and prepare it using prepare_cached. That way its only prepared once (for each variation), and you can still take good advantage of placeholders. This is not the tightest example, and is untested, but should serve its purpose:
    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);
      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!

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://378183]
Approved by Corion
Front-paged by grinder
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others examining the Monastery: (5)
As of 2020-12-04 22:53 GMT
Find Nodes?
    Voting Booth?
    How often do you use taint mode?

    Results (63 votes). Check out past polls.