Beefy Boxes and Bandwidth Generously Provided by pair Networks
Don't ask to ask, just ask
 
PerlMonks  

class-dbi-mysql and null values

by davidj (Priest)
on Nov 05, 2006 at 07:35 UTC ( #582289=perlquestion: print w/ replies, xml ) Need Help??
davidj has asked for the wisdom of the Perl Monks concerning the following question:

Hey everyone,
I've just started using class-dbi for some work stuff and I'm still trying to get used to some of its functionality. The problem I'm having is getting a column value updated to a NULL value.

In the webapp update form, when a value from a particular field is removed, I need the corresponding value for that field value in the database to be set to NULL. I'm updating the value as per class-dbi:
$case->owner( $q->param('owner') ); ..... $case->update;
However, this sets the value to an empty, non-null value which will cause a search on NULL to fail. If I put NULL as a value in the code, I get the bareword error:
$case->owner(NULL); ..... $case->update;
I've made the following changes in the class file to hack a workaround:
Handoff::Cases->set_sql('unowned', qq{SELECT * FROM cases WHERE owner IS NULL
to
Handoff::Cases->set_sql('unowned', qq{SELECT * FROM cases WHERE owner IS NULL OR owner=''
This hack will make it work for my application, but it breaks other applications that require a NULL value in the field since the field does not, in fact, have a NULL value. There's got to be a way to do it. I just don't know what it is.

As always your assistance is much appreciated.
davidj

Comment on class-dbi-mysql and null values
Select or Download Code
Re: class-dbi-mysql and null values
by Anonymous Monk on Nov 05, 2006 at 09:08 UTC
    ...I get the bareword error
    NULL is not a perl keyword. Try $case->owner('NULL');
      Yeah, when I tried  $case->owner(NULL) I quickly realized why it didn't work. I also tried  $case->owner('NULL') and it sets he value of the field to the string NULL.
      davidj
Re: class-dbi-mysql and null values
by tinita (Parson) on Nov 05, 2006 at 11:01 UTC
    $case->owner( $q->param('owner') );
    try
      $case->owner( scalar $q->param('owner') );
    because param() returns the empty list (in list context) if a parameter isn't defined.
      I thought about doing something like that, also; to get an undefined value in there. However, even if $q->param('owner') is empty, it is still defined.
      davidj
Re: class-dbi-mysql and null values
by fmerges (Chaplain) on Nov 05, 2006 at 11:36 UTC

    Hi,

    Don't do that, don't use a param directly to update the DB. There's something called tainting and validation. Take a look at Data::FormValidator.

    Regards,

    fmerges at irc.freenode.net
      I'm familiar with taintedness and validation. I cut that part out of the code I supplied since it didn't seem relevant to the specific problem. Good catch, though.
      davidj
Re: class-dbi-mysql and null values
by davidj (Priest) on Nov 05, 2006 at 12:04 UTC
    I found a resolution. Its actually quite simple: just test for the length of the owner parameter and explicity asign undef if the length is 0.
    if (length($owner) > 0) { $case->owner( $q->param('owner') ); } else { $case->owner( undef ); }
    Thanks for you assistance,
    davidj

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://582289]
Approved by friedo
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others imbibing at the Monastery: (8)
As of 2014-08-28 12:28 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The best computer themed movie is:











    Results (259 votes), past polls