Bug #63124 [Opn->Nab]: PDO::ATTR_ORACLE_NULLS not respected

Edit report at https://bugs.php.net/bug.php?id=63124&edit=1 ID: 63124 Updated by: willfitch@php.net Reported by: bugzilla at lourdas dot name Summary: PDO::ATTR_ORACLE_NULLS not respected -Status: Open +Status: Not a bug Type: Bug Package: PDO related Operating System: Gentoo Linux PHP Version: 5.3.17 Block user comment: N Private report: N New Comment: Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://www.php.net/manual/ and the instructions on how to report a bug at http://bugs.php.net/how-to-report.php Please note - it's much easier for us to address issues with examples in simple PHP code. It should not be assumed that those addressing the reported bugs will be familiar with the vast number of frameworks out there. That said, the ATTR_ORACLE_NULLS attribute, IIRC, is on read. On write, there are dependencies such as whether emulation is on. Take the following example: $dsn = 'mysql:host=localhost;dbname=test;'; $pdo = new PDO($dsn); $pdo->setAttribute(PDO::ATTR_ORACLE_NULLS, PDO::NULL_EMPTY_STRING); $sql = 'INSERT INTO blah (id, name) VALUES (:id, :name)'; $query = $pdo->prepare($sql); $query->bindValue('id', '1'); $query->bindValue('name', ''); $query->execute(); $sql = 'SELECT * FROM blah'; $result = $pdo->query($sql); foreach ($result as $row) { var_dump($row); } On insert, this is the row that gets generated: mysql> select * from blah; +------+------+ | id | name | +------+------+ | 1 | | +------+------+ 1 row in set (0.00 sec) You can see the value of the "name" column is actually an empty string (console mysql would show NULL if applicable). However, on the result set returned, the empty strings *are* indeed converted as requested: array(4) { ["id"]=> string(1) "1" [0]=> string(1) "1" ["name"]=> NULL [1]=> NULL } It is up to the individual drivers to implement this functionality on write (again, keeping emulation in mind). So, this could either be considered a documentation issue (assuming PDO wants this functionality *only* on read), or a feature request for the MySQL PDO driver. Previous Comments: ------------------------------------------------------------------------ [2012-09-20 19:05:01] bugzilla at lourdas dot name Description: ------------ I use the latest stable version of the Yii PHP framework (v.1.1.12). I also use the MySQL PDO driver to create a new model. The data model is taken from a HTML form. For empty values, I get an empty string during POST. Some of the empty values are meant to be used as foreign keys to a master table. The corresponding MySQL database columns are nullable, so they can either have NULL or the id of the referencing table (master). I have set the nullConversion property of the CDbConnection class (see http://www.yiiframework.com/doc/api/1.1/CDbConnection) which correspondingly sets the PDO::ATTR_ORACLE_NULLS property as needed in order to convert these empty string values to NULL before saving the model. I use the PDO::NULL_EMPTY_STRING value in nullConversion property to do this, however, no matter which PDO::ATTR_ORACLE_NULLS value I use, the result is always the same. The empty strings are not converted to NULLs, so the row insertion in MySQL fails because of foreign key constraint violation. Not to mention that if I run the same query from the mysql client with the corresponding empty string database columns converted to NULL, the insertion works fine. I have already reported this as a bug to the Yii repository (https://github.com/yiisoft/yii/issues/1442), however one of the core developers explains that the framework simply sets the value of this PDO property, so if the code does not work, this is a PDO bug, that's why I'm opening this bug. Test script: --------------- In Yii's main configuration file: 'db'=>array( // this is an instance of the CDbConnection class 'connectionString' => 'mysql:host=localhost;dbname=mydb', 'emulatePrepare' => true, 'username' => 'myuser', 'password' => 'mypass', 'charset' => 'utf8', 'enableProfiling' => true, 'enableParamLogging' => true, 'nullConversion' => PDO::NULL_EMPTY_STRING, ), and in the controller class: if (isset($_POST['Visitors'])) { // get values from form $visitor->attributes = $_POST['Visitors']; $visitor->date_registered = new CDbExpression('CURDATE()'); if ($visitor->save()) // save the database model, instance of CActiveRecord Yii::app()->user->setFlash('userCreated', 'You have successfully joined.'); else { Yii::app()->user->setFlash('userCreated', 'There was a problem creating your user account.'); } } Expected result: ---------------- Since some database columns are nullable, the empty strings should be converted by the PDO driver to NULLs, so that the insert would work. Instead, I get a foreign key constraint error, since the driver tries to insert empty strings at these database columns, instead of NULLs, which are allowed. ------------------------------------------------------------------------ -- Edit this bug report at https://bugs.php.net/bug.php?id=63124&edit=1