Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl-Sensitive Sunglasses

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
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.

Replies are listed 'Best First'.
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,
Re: class-dbi-mysql and null values
by tinita (Parson) on Nov 05, 2006 at 11:01 UTC
    $case->owner( $q->param('owner') );
      $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.
Re: class-dbi-mysql and null values
by fmerges (Chaplain) on Nov 05, 2006 at 11:36 UTC


    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.


    fmerges at
      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.
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.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://582289]
Approved by friedo
[choroba]: LanX we used to mock Hare Krishna processions signing "Hare Rama" carrying the margarine conatiners
[choroba]: :-D
[LanX]: What about Czech an Chechen ambessy?
[AnomalousMonk]: LanX: Rama had (has?) a high (much higher than typical asteroid) rate of axial (again, unusual, I think) spin for its artificial gravity.
LanX pity there is no Germs embassy ...
[choroba]: LanX We merged them to save money.

How do I use this? | Other CB clients
Other Users?
Others examining the Monastery: (13)
As of 2017-11-22 17:08 GMT
Find Nodes?
    Voting Booth?
    In order to be able to say "I know Perl", you must have:

    Results (327 votes). Check out past polls.