Codeedoc - Programming Blog

Zend and MySql- Inserts 0 instead of null.

I’ve been working with the Zend Framework a bit and in working with MySQL through PDO_MYSQL. I’ve run into a few problems trying to use parameters of queries.

I’ve narrowed the issue down to PDO itself and not the Zend Framework.

$dbh = new PDO(”mysql:host=localhost;dbname=db”,”user”,”pw”);
$query = “insert into silo_test_data (record_id, fieldname, value) values (3,’stufftest’, :value )”;
$handle = $dbh->prepare($query);
$handle->execute(array(”:value” => ‘crap’));
$dbh = null;

The value gets inserted into the database as an empty string, or sometimes some low-value bytes.
By setting PDO to emulate prepared statements, everything seems to work okay.

$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);

The problem is, the Zend framework encapsulates the actual db connection and connects lazily. So, for now I’m running a query and then set the parameter on the encapsulated connection object.

$db_connect = array( ‘host’ => $config->db->hostname,
‘username’ => $config->db->username,
‘password’ => $config->db->password,
‘dbname’ => $config->db->database );
$db = Zend_Db::factory(’PDO_MYSQL’, $db_connect);
$db->query(’select 1′);//HACK HACK HACK HACK to initiate connection. Can’t I make this a plugin? or extend the class?
$db->getConnection()->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
Zend::register(’db’,$db);