Re: DB, prepare() and NULL values

On 4/3/06, Justin Patrin <papercrane@gmail.com> wrote: > On 4/3/06, Chris Boget <chris.boget@wild.net> wrote: > > All morning I've been trying to somehow work it so that prepare() recognizes > > and accounts for NULL values. Such that if I set a variable as NULL and > > NULL being a valid value for a field in the table, it would prepare the > > query so that it generates it using NULL. > > > > For example, say I had the following: > > > > $sth = $db->prepare('INSERT INTO numbers (number) VALUES (?)'); > > $res =& $db->execute($sth, 1); > > > > PEAR DB would generate a query looking like: > > > > INSERT INTO numbers (number) VALUES ('1') > > > > Now, let's say that the number column was set to allow null values. Here is > > the same code, slighly altered: > > > > $sth = $db->prepare('INSERT INTO numbers (number) VALUES (?)'); > > $res =& $db->execute($sth, NULL); > > > > Now the generated query would look like: > > > > INSERT INTO numbers (number) VALUES ('') > > > > when I need it to look like: > > > > INSERT INTO numbers (number) VALUES (NULL) > > > > I've tried changing the placeholder from a ? to a ! but all that did was > > make it so that there was no value set making the query look like: > > > > INSERT INTO numbers (number) VALUES () > > > > would would cause an error, particularly if there was more than just 1 > > column set up in the query. And changing the placeholder to a & only > > resulted in getting back an error object saying something about insufficient > > permissions. > > > > So I'm curious, is what I am trying to do even possible? I've looked at the > > source code in DB_common and it appears that I'm SOL. Has anyone else had > > any success in doing what I'm trying to do? > > > > As a workaround....try not preparing fields which need to be set to > NULL perhaps? > Oh, and I also suggest looking into MDB2, perhaps it can do what you want. -- Justin Patrin