Re: Prooblem writing empty strings to mySQL db

[prev] [thread] [next] [Date index for 2004/12/22]

Adam,

That's not what I see on mysql...

mysql> update prefs set value='' where name='power';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from prefs where value='';
+-------------------+-------+-------+
| mac               | name  | value |
+-------------------+-------+-------+
| dc:00:00:00:00:02 | power |       |
+-------------------+-------+-------+
1 row in set (0.00 sec)

mysql> select * from prefs where value=NULL;
Empty set (0.00 sec)


However if I'm trying to do something that only works on mysql I need to
reconsider.

Thanks,

-Dave
On Wed, 22 Dec 2004 10:09:25 +0100, "Adam Przygienda" <adam@xxxxxxx.xxx>
said:
> Hi Dave,
> 
> I am using oracle but this should also apply for sybase.
> 
> Empty string '_' is logically equivalent to NULL.
> 
> After executing
> 
>   SET value = ''  WHERE mac='blah' AND name='foo'
> 
> The value of field value in this record should (this is what happends in 
> oracle too) be NULL.
> 
> Means instead of setting value to '_', set it to undef
> 
> my $setting = My::Setting->retrieve(mac => "blah", name => "foo");
> $setting->set(value => undef);
> $setting->update();
> 
> Class::DBI will generate:
> 
> SET value = NULL  WHERE mac='blah' AND name='foo'
> 
> hope this helps
> adam
> 
> 
> 
> cdbi@xxxxxxxxxx.xxx wrote:
> 
> >Hi, I'm new to Class::DBI and I've done some searching and could not figure this problem out.  I'm trying to write an empty string to a column in my database, but I get a SQL syntax error instead.
> >
> >Without giving all the code, I'm essentially doing this:
> >
> >my $setting = My::Setting->retrieve(mac => "blah", name => "foo");
> >$setting->set(value => "");
> >$setting->update();
> >
> >Then I run the code with DBI_TRACE=2, so I see the SQL, and it looks like:
> >
> >SET value =
> >WHERE mac='blah' AND name='foo'
> >
> >And this generates an error.  Whereas this SQL would work fine...
> >
> >SET value = ''
> >WHERE mac='blah' AND name='foo'
> >
> >So how do I get Class::DBI to do the latter?
> >
> >Thanks for any help,
> >
> >-Dave
> >
> >  
> >
>