class-dbi-mysql and null values

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;
[download]

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;
[download]

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
[download]

Handoff::Cases->set_sql('unowned', qq{SELECT * FROM cases 
                                      WHERE owner IS NULL OR owner=''
[download]

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

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 ); }` [download] Thanks for you assistance, davidj	[reply] [d/l]
Re^2: class-dbi-mysql and null values by davidrw (Prior) on Nov 05, 2006 at 13:19 UTC
Depending on your data (e.g. if it's ok to null out a 0), you could simply do: `$case->owner( $owner \|\| undef );` [download] This is great writeup on what counts as false: True or False? A Quick Reference Guide	[reply] [d/l]
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.	[reply] [d/l] [select]
Re^2: class-dbi-mysql and null values by davidj (Priest) on Nov 05, 2006 at 11:46 UTC
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	[reply] [d/l]
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	[reply]
Re^2: class-dbi-mysql and null values by davidj (Priest) on Nov 05, 2006 at 12:08 UTC
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	[reply]
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');`	[reply] [d/l]
Re^2: class-dbi-mysql and null values by davidj (Priest) on Nov 05, 2006 at 09:52 UTC
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	[reply] [d/l] [select]