Zend_Db doesn't appear to properly handle enumerated types at all!

In a call to db->update($table,$data,$where) any enumerated values are not written to the database even though the $data array contains values for the enumerated type fields. (the data values have been previously passed through db->quote() )

Same holds true for db->insert(), no enumerated types are written to the database

Database = MySql Driver/Adapter = Pdo_Mysql

You're right to include your comment on this issue, then... MySQL silently ignores values for fields that it doesn't understand or don't match its field definitions... Try setting a too-long value to a VARCHAR field to see what I'm talking about...

Does this only affect ENUM's that contain numeric values for you? I don't notice the same behavior with TINYINT. I have a patch to extract and submit, if it will be reviewed.

Additional note. Actually a blank value is written to the database for an enumerated value. Create a table with a enum value that defaults to one of its allowed values. Insert a record to the table, without setting a value for the enum field. It will default to whatever you set it on the database. Now update the record, this time passing a value for the enum field. It will get overwritten with a blank.

David said "Does this only affect ENUM's that contain numeric values for you"

I tend not to use numeric values for enums so don't experience that particular issue. I use string values e.g.

CREATE TABLE IF NOT EXISTS syst_test ( pid int(11) NOT NULL auto_increment, uVal int(11) NOT NULL, strVal varchar(30) default NULL, dateVal date default NULL, textVal text, floatVal float default NULL, enumVal enum('one','two') default NULL, rowSts enum('active','suspended','defunct') NOT NULL default 'active' COMMENT 'Row Status', rowUid int(11) NOT NULL default '0' COMMENT 'Last Edited By', rowDt timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'Edit Datetime', PRIMARY KEY (pid), UNIQUE KEY uVal (uVal) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

A little further delving seems to reveal that the Zend layer is doing everything you would expect. It properly puts quotes around an enum value. It is when the pdo layer is called to do the update that the value disappears. So it my be PDO, not Zend_Db at fault. Can't debug PDO in Zend Studio, so can get no further :-(

We haven't had the issue you're describing with string values and ENUM fields at all, Ashley, only those fields that contain a numeric ENUM value. Our solution has just been to cast the number to a string value, ie :

-- Sample Schema in Database 'test'
CREATE TABLE IF NOT EXISTS 'enum_test' (
'one_or_zero' ENUM('1','0') DEFAULT '1',
'true_or_false' ENUM('true','false) DEFAULT 'false',
) ENGINE=InnoDB DEFAULT CHARSET=utf8-bin ;

INSERT INTO 'enum_test' VALUES 
( '1', 'true' ),
( '1', 'false' ),
( '0', 'true' ),
( '1', 'false' );

$Adapter = Zend_Db::factory('Pdo_Mysql', array('host' => 'localhost', 'user' => 'root', 'dbname' => 'test');

// Expected - integer: 2
Zend_Debug::dump( 
    $Adapter->select()->from('enum_test', array('count' => 'COUNT(*)'))
        ->where('one_or_zero = ?', 1)
        ->query()->fetchColumn() 
);
// Actual - integer: 0

// Expected - integer: 2
Zend_Debug::dump( 
    $Adapter->select()->from('enum_test', array('count' => 'COUNT(*)'))
        ->where('one_or_zero = ?',  (string) 1)
        ->query()->fetchColumn()
);
// Actual - integer: 2

What you're describing - correct me if wrong - is something like the following:

// Same as above...

// Expected - integer 1 (number of rows affected)
Zend_Debug::dump(
    $Adapter->insert(array('true_or_false' => 'true'))
);
// Actual - integer 1

// Expected - integer 3 (2 originals plus newly inserted value)
Zend_Debug::dump(
    $Adapter->select()->from('enum_test', array('count' => 'COUNT(*)'))
        ->where('true_or_false = ?', 'true')
        ->query()->fetchColumn()
);
// Actual - integer 0 (query got messed up somewhere...!)

What's version of PHP you're using, as this may be the culprit instead.

I had a spare minute so I checked to see if anything different happened by changing the adapter to Mysqli. It didn't - still doen't work.

However just to prove it isn't MySql, I ran the following bare bones mysql query:

    $db = mysql_connect('localhost','MyDb','MyDb');
    $sql = "update syst_test set enumVal = 'two' where pid = 10";
    $ret = mysql_db_query('MyDb',$sql,$db);

That works just fine, the enum value gets updated.

So it's not SQL. If changing adapters proves anything, its not the adapter. Ergo it's Zend_Db.

I'm using PHP 5.2.3

"$Adapter->insert(array('true_or_false' => 'true'))" Yep, that's about right

(got t go now - will catch up 2morrow.)

Reassigning to Ralph since he's the new maintainer of Zend_Db

Can I just submit a patch for this issue?

Yes, I would like to see a patch- do you have one that addresses this issue?

-ralph

I have a patch for this issue, but as there are no tests for the Abstract class, I haven't run the tests on every driver. With some direction there, I could do so, however. Please advise. Thanks.

Can you attache the issue to this ticket?

I don't quite follow, Ralph. What do you mean by "attach the issue to this ticket"...?

Ha, sorry. I mean patch. You said you had a patch for this issue.

David, can you post a patch for this issue, I urgently need it...

Leo,

I'll try to dig that up and send it your way. I think that I isolated the issue to Zend_Db_Table when I last worked on this, since only Table instances have the metadata about the column to know how to coerce the values. I originally tried to mod Zend_Db_Adapter_Mysql, but without the metadata... Anyway. I'll put a patch together this week.

David

Any progress on the patch?

Hello I'm experiencing the same problem with Zend 1.11.4 A temporary solution for users that can't wait for the patch is to use Zend_Db_Expr: $Adapter->insert(array('true_or_false' => new Zend_Db_Expr("'true'")))