Re: [fw-db] Zend_Db and NULL values

Bill/Hector,

Thanks for the replies...I guess more examples and clarification on my part is in order.

In a nutshell, I'm looking to avoid:
if ($search == null) {
    $select->where('column IS NULL')
}
else {
    $select->where($db->quoteInto('column = ?', $search))
}
for inserts, updates, deletes, and selects.  I can't seem to find a unified way of doing that.  Bill, I did some more tests with results below, as well as some inline comments to your reply.



create table testtable (
    column1 text default 'defaultvalue1',
    column2 text default 'defaultvalue2'
);

class testtable extends Zend_Db_Table_Abstract { protected $_name = 'testtable'; protected $_sequence = false; protected $_primary = array('column1', 'column2'); }

$a = array();
$a['column2'] = NULL;
$a['column2'] = '';


$db->insert('testtable', $a); - Works as expected
Result is:
    column1    | column2
---------------+---------
 NULL          | '' (Modified output for clarity)


$testtable = new testtable();
$row = $testtable->createRow($a);
$row->save(); - Doesn't Work
Result is:
    column1    | column2
---------------+---------
 defaultvalue1 | '' (Modified output for clarity)


Trying to find.
$rowset = $testtable->find(NULL, ''); - Doesn't Work
Produces the following query : SELECT "testtable".* FROM "testtable" WHERE ((("column1" = '' AND "column2" = '')))


Trying to update.
$row->column2 = NULL;
$row->save(); - Doesn't Work


$new = array();
$new['column1'] = NULL;
$new['column2'] = NULL;
$testtable->update($new, "column1 ISNULL AND column2=''"); - Works as expected


When using the Zend_Db_Select object, you have to write conditional wheres to account for NULLs (as shown at top).  Anyway to avoid this?

So...Now, I'm a little more confused, as it does seem to work in certain instances (as you've pointed out, Bill), but not in others.  Seems like it is the Zend_Db_Row object, but I'm not positive.  I guess I'm just looking for some clarification so I know what works for what.

Again, if I'm out to lunch, just say so.

BTW.  Didn't know that about Oracle.  I don't know if that is good or bad...  Hmmm...

Arthur



Bill Karwin wrote:

 Arthur Kang wrote:   

Is it just me?  It seems like the Zend_Db classes and gateways do not  handle NULL values very well (or at all).   All databases differentiate  between NULL and ''.        

 FYI, that last statement should be true but it isn't.  In Oracle, NULL and '' are equal.  :-(   Arthur Kang wrote:   

array('key' => NULL) should be valid and should be different than  array('key' => '')          $a = array();         $a['key'] = NULL;         $a['key2'] = '';         Zend_Debug::Dump($a);      

 Your example code above does distinguish between NULL and ''.   (I changed your 'key' to 'key1' to avoid conflict with SQL keyword KEY.)   

I was showing this as an example that PHP could, in fact, differentiate the two and so it should be possible to make automatic adjustments to queries.

 The output of your example is: array(2) {   ["key1"] => NULL   ["key2"] => string(0) "" }  If you use this array $a for data to insert into a database, you get distinct data in the database.  // Adapter method: $db->insert('foo', $a);  // Table class interface: class foo extends Zend_Db_Table_Abstract { } $foo = new foo($db); $row = $foo->createRow($a);  var_dump($row->toArray()); // Output: array(3) {   ["id"]=>   NULL   ["key1"]=>   NULL   ["key2"]=>   string(0) "" }  $row->save();   

This, I believe actually doesn't work.  It is skipping key1, as there is no value and so the database default is set.  See new example above.

 var_dump($db->fetchAll("SELECT * FROM foo")); // Output: array(2) {   [0]=>   array(3) {     ["id"]=>     int(12)     ["key1"]=>     NULL     ["key2"]=>     string(0) ""   }   [1]=>   array(3) {     ["id"]=>     int(13)     ["key1"]=>     NULL     ["key2"]=>     string(0) ""   } }  If there's a scenario other than inserting data that you're concerned about, it would help if you were more specific.  Regards, Bill Karwin