PDO datetime exception when saving a Computed Field (but no datetime in my query)

Question

This one has me stumped. I get the following error when trying to create/edit a node with a computed field, which is an INT:

PDOException: SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect integer value: '' for column 'field_film_civicrm_event_code_value' at row 1: INSERT INTO {field_data_field_film_civicrm_event_code} (entity_type, entity_id, revision_id, bundle, delta, language, field_film_civicrm_event_code_value) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4, :db_insert_placeholder_5, :db_insert_placeholder_6); Array ( [:db_insert_placeholder_0] => node [:db_insert_placeholder_1] => 363 [:db_insert_placeholder_2] => 364 [:db_insert_placeholder_3] => film [:db_insert_placeholder_4] => 0 [:db_insert_placeholder_5] => und [:db_insert_placeholder_6] => ) in field_sql_storage_field_storage_write() (line 451 of /data/disk/o1/static/drupal-7.23/modules/field/modules/field_sql_storage/field_sql_storage.module).

Here is my code for the computed field:

list($id, $vid, ) = entity_extract_ids($entity_type, $entity);

$result = db_query("SELECT
                    ce.id AS CiviEventID
                    FROM node n
                    INNER JOIN civicrm_event ce ON n.title = ce.title COLLATE utf8_unicode_ci 
                    WHERE n.nid = :nid
                    AND n.vid = :vid 
                                        LIMIT 1", 
                  array(':vid' => $vid, ':nid' => $id));

if ($result->fetchField() === NULL)
{
$entity_field[0]['value'] = 0;
}
else
{
/*$entity_field[0]['value'] = 0; */
$entity_field[0]['value'] = $result->fetchField();
}

I'm using a computed field to get a CiviCRM event code where its event name matches my node title. It previously matched the Civi event date to a node date field as well, but I removed that when troubleshooting. This worked normally two weeks ago. Since then I've applied updates to Ubuntu and PHP, and the Entity and Date modules, but haven't changed my computed field code (until troubleshooting for this post). If I hard-code the value to 0, the node saves without error.

The error shows the correct nid and vid, but also shows empty for my computed field (I'm assuming due to this datetime error). When I run the query directly against the DB I get the correct result, so my query syntax isn't broken.

I'm running this all on Linode using the BOA stack from Omega8cc. Drupal is 7.23 currently. Any advice for troubleshooting or a solution?

UPDATE: I've modified the db_query to its simplest form ("SELECT 1 FROM NODE LIMIT 1") and the fetchField() still doesn't return a value. But if I set the value without using a query at all, it saves.

Answer 1

In the end, something in the BOA or Entity update affected fetchField(). I resolved it by explicitly setting the index like so: fetchField(0).

I added a comment to api.drupal.org to hopefully help the next person who has the problem.

https://api.drupal.org/api/drupal/includes%21database%21database.inc/function/DatabaseStatementBase%3A%3AfetchField/7

Answer 2

it's saying that field_film_civicrm_event_code_value expects an integer in the format datetime and there is no value: [:db_insert_placeholder_6] => )

If that is not the correct format, you can change the field settings in the db, and/or make sure the field allows NULL values and the default is NULL

http://www.php.net/manual/en/class.datetime.php