php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #63124 PDO::ATTR_ORACLE_NULLS not respected
Submitted: 2012-09-20 19:05 UTC Modified: 2012-09-21 06:58 UTC
From: bugzilla at lourdas dot name Assigned:
Status: Not a bug Package: PDO related
PHP Version: 5.3.17 OS: Gentoo Linux
Private report: No CVE-ID: None
 [2012-09-20 19:05 UTC] 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.


Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2012-09-21 02:00 UTC] willfitch@php.net
-Status: Open +Status: Not a bug
 [2012-09-21 02:00 UTC] willfitch@php.net
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.
 [2012-09-21 06:58 UTC] bugzilla at lourdas dot name
So, this attribute has only effect when used for reading, as you say. This indeed is the case, testing your code. Before the setAttribute() method, I tried to use

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

and run the code. No change during write, the database always saves an empty string. I suppose that this is something that the PDO MySQL driver does not implement...? So, in my case (the Yii application) I need to watch for attributes that could be NULL and happen to reference other tables and before saving them, I must change those to NULL.

Could this bug be changed to a feature request?
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Tue Apr 23 21:01:31 2024 UTC