Problem writing strings to database

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

Yesterday I asked about a problem writing empty string to a database. 
It turns out the problem is bigger than that.  Sometimes when inserting
or updating Class::DBI leaves quotation marks out when they are needed. 
Other times it does use quotation marks.

For example I call create with value=>'NOW_PLAYING', and (thanks to
DBI_TRACE=2) I see the following:

      Binding parameters: INSERT INTO prefs (value, name, mac)
VALUES (NOW_PLAYING, 'menuItem-0', '00:04:20:05:5d:20')

There's no quote around 'NOW_PLAYING' so the database assumes its a
reference to a column which does not exist and I get an error.

Other times, the same code results in this SQL:
      Binding parameters: INSERT INTO prefs (value, name, mac)
VALUES ('NOW_PLAYING', 'menuItem-0', '00:04:20:05:5d:20')

And everything works just fine.

I can consistantly reproduce the problem by removing all data from my
table.  My code then starts inserting data to the table, and the first
value is '0', the second is 'NOW_PLAYING' and then I get the error.  Its
as if Class::DBI sees that value is 0 the first time around, and assumes
it will always be a number.  (The column is varchar).  So can I somehow
force Class::DBI to treat my values as strings all the time?

Thanks for any help,

-Dave


On Wed, 22 Dec 2004 01:56:19 +0000 (GMT), cdbi@xxxxxxxxxx.xxx said:
> 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