Chapter 4 Project Crud Create New Project Fails In Yii 1.1.13

MartindeGroot · July 7, 2013, 7:54pm

In Chapter 4 "Project CRUD" section "Create a new project" page 68 of 304 the author reports a successful save of a new project when only the required fields "name" and "description" are filled in.

However, I met with the following error, I am using version 1.1.13 of Yii Framework:

CDbException

CDbCommand failed to execute the SQL statement: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: ‘’ for column ‘create_time’ at row 1. The SQL statement executed was: INSERT INTO tbl_project (name, description, create_time, create_user_id, update_time, update_user_id) VALUES (:yp0, :yp1, :yp2, :yp3, :yp4, :yp5)

M:\Martin\yiiframework\framework\db\CDbCommand.php(357)

As can be seen from the error message, the invalid datetime value is ‘’ i.e. the empty string. It is the value of parameter yp2, which is the value for the column create_time.

Because the book reports a successful save with version 1.1.12 and I find this error with version 1.1.13 I conclude that something in the code has changed and the change has led to this bug.

I have debugged the code execution and found that things go wrong in file CDbColumnSchema.php in the public function typecast($value)

I believe that I have found a bugfix, but as I am quite a beginner with Yii, I would appreciate comments on this bugfix, and maybe there are better ways to fix this, or maybe my fix has some undesired side-effects.

Here follows my changed code for CDBColumnSchema.php public function typecast($value) :

public function typecast($value)

{


  // MartindeGroot 2013-07-07		


  // problem description: a MySQL datetime column has property dbType=='datetime' but it has  property type=='string'


  // and with the unchanged code here a &#036;value== '' (empty string) is typecast to '' but it should be typecast to null


  // a &#036;value=='' in the unchanged code meets the first condition (gettype(&#036;value)===&#036;this-&gt;type, both are type 'string' and so this &#036;value '' is immediately returned


  // this leads to the error &quot;invalid datetime value '' for a datetime column. the value to be returned in this case should be null, not ''


 // MdG begin of OLD CODE

// if(gettype($value)===$this->type || $value===null || $value instanceof CDbExpression)

// return $value;

// if($value===’’ && $this->allowNull)

// return $this->type===‘string’ ? ‘’ : null;

 // MdG end of OLD CODE


 // MdG begin of NEW CODE


if( (gettype(&#036;value)===&#036;this-&gt;type &amp;&amp; &#036;value &#33;='') || &#036;value===null || &#036;value instanceof CDbExpression)


  return &#036;value;


    if(&#036;value==='' &amp;&amp; &#036;this-&gt;allowNull)		


  return (&#036;this-&gt;type==='string' &amp;&amp; &#036;this-&gt;dbType &#33;= 'datetime') ? '' : null;


 // MdG end of my changed code


switch(&#036;this-&gt;type)


{ .... rest of function unchanged

If it is more appropriate to put this post in another secion of the forum, or maybe it should be a bug report, I kindly ask a forum administrator to place this post in the proper section.

Hope this is useful to other readers of this book who may have come across this same error when following along with this Chapter 4 section Create a new Project.

Martin de Groot

jbusciglio · September 8, 2013, 12:45am

I ran into this issue too. I’m using 1.1.14.

I inspected your solution, and while that probably works for that one case, I didn’t make the same change to resolve the issue.

I don’t think this is a bug in the core code, and here is why.

Take a look at CDbCommand class reference.

As you can see, CDbCommand already supports statement preparations and parameter binding for data types via these two methods:

bindParam()

and

bindValue()

So, what is the issue? What was not covered in the book was the installation and configuration of the database.

The database is enforcing strict adherence to its datatype, so that means that ‘’ != null.

In the example given in the book, what the book is not telling you is that their database configuration is not enforcing strict adherence to the datatype.

So it is allowing ‘’ to be passed in the query. If you notice in the example, the fields that are mapped to text type field in the database are showing a value of “not set”.

But look at our date fields, those values are being set to 0000-00-00 00:00:00. Did we at any point enter that value in? No, we did not. Did YII programatically enter that value in? I will save you the trouble of looking and tell you that no it did not.

You can see the value that YII passed was definitely an empty text string ‘’.

So what happened? The database loosely interpreted the empty text string and set a default value for us!

So, I am going to assume that the op in that issue, like me was using mySql. in that case, you simply need to find your my sql config file and comment out the following line to continue following along the example with no code changes.


# Set the SQL mode to strict

#sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Later on in the book, they deal with setting dates and data validation stuff, so if you need to turn strict mode back on you will know what to do to make sure that Yii isn’t trying to pass an unbound data type in a string query.

This is a database config issue, not a code issue. The book did say that they assumed you knew all that already.

To be honest, I did know that already, and I racked my brain trying to understand why my YII setup wasn’t working. It probably wouldn’t hurt for the author of the book to make a quick note that the only way his example worked was because he had a specific sql server setting, and that the example may not work on all settings.

It’s just one of those gotcha things that could have been easily avoided. If you are like me, you were wrapped up in the class api and navigating your PHP IDE and heavily immersed in wrapping your head around the yii framework. The last thing you expected was a database setting to cause YII to cut up.

I suppose in time I will be comfortable enough with the framework to trust it, but like the OP, I was so zoomed in on the stack trace trying to learn, that it didn’t occur to me until hours later that the issue might be environmental rather than framework related.

It did help me get deep into the code, cause there was no way I was glossing that one over.

Also, this issue kind of highlights the shortcomings of scaffolding out of the box. It should be obvious that my schema had a date field in it, and the scaffold could have IMO easily stubbed a check to see if that field was not required user input on a new create then set the attribute to now().

W/e be one of the first things I write when I get done with the tute!