Hi Brian,
What was the return value of execute()?
Was there a DBI error message set afterward? ($sth_upd[$_]->errstr)
You're right that passing undef should work just fine as one of the
execution params should translate into a NULL on the database side.
Yeah, as long as it's in the 1st or 2nd position (part of the update not
the "where") you should be fine.
Of course "x = NULL" is always false in SQL, but you know that won't work.
I'd lay money that you have an error message explaining things, there
isn't a row with that value at that point, or one of your triggers is at
play.
Best,
Scott
Brian H. Oak wrote:
I *think* this used to work, but recently stopped working. I have a program
that is designed to work on MS SQL Server. My program prepares an array of
statement handle references by repeatedly interpolating several scalar
variables with placeholders:
for ( 0..$#uptabcol ) {
$sqlcommand = "update $uptab set $upcol = ?";
$sqlcommand .= ", $paqcol = ?";
$sqlcommand .= " where $lucol = ?";
$sth_upd[ $_ ] = $dbh->prepare( $sqlcommand );
}
Later, it executes the update statement handles using calculated and
looked-up placeholder values (which are sometimes NULL):
for ( 0..$#uptabcol ) {
my @exargs = ( $linkedname );
my ( $paqstatus ) = $sth_paq[ $_ ]->fetchrow_array;
push( @exargs, $paqstatus eq "" ? undef : $paqstatus );
push( @exargs, $luval );
$sth_upd[ $_ ]->execute( @exargs );
}
Please note that for reasons of brevity I have not shown preparation of the
$sth_paq statement handle, but it is correctly defined and prepared. Also,
the where clause is never NULL, so that is not a problem here. I'm simply
trying to update the values of the $upcol and $paqcol. The $upcol value is
never NULL, but the $paqcol value is frequently NULL.
I'm pretty sure that passing a list, one of the values of which is undef,
used to work for setting the desired column value to NULL. But I installed
my program on a new server last week and ran it over the weekend, only to
find that it skipped updating the $paqcol any time the update value was
NULL. It didn't drop $exargs[2] down into the undefined position -- that
would have messed the whole thing up. But it's definitely not updating the
value of $paqcol, because a trigger that I'm trying to avoid setting off by
updating $paqcol with *any* value is getting tripped every time $paqcol is
supposed to be NULL.
Any ideas? Has something changed in the handling of undef/NULL? Have I
been doing something wrong since the first release of my program, but dumb
luck has blinded me?
-Brian
