PDOException: Incorrect decimal value for a field which is legitimately null

Question

I am creating a new composite field type that represents financial debt information. Each field consists of several sub-fields for values such as Amount owed, Interest rate, Repayment amount etc. This field is added to a content type as a multiple-instance field and users can add an unlimited number of these items.

I have built the settings forms, widgets, validators and formatters and it all works great when I fill out all the fields and submit the node. The data is written into the custom field table OK and the values persist when the node is edited again. The multi-instance field creation works as it should.

The problem is that some of the sub-fields are optional, but if they are left empty then I get a PDO error when the node is submitted:

PDOException: SQLSTATE[HY000]: General error: 1366 Incorrect decimal value: '' for column 'field_cc_repayment_amount'

repayment_amount is an optional numeric sub-field. It is declared in the .install file like this:

    'repayment_amount' => array(
       'type' => 'numeric',
       'precision' => 11,
       'scale' => 2,
       'not null' => FALSE,
    ),

When I look at the database schema in my SQL editor I can see that field_cc_repayment_amount is declared as type DECIMAL with length 11,2, the Allow Null checkbox is checked and the Default value is set to NULL.

I can run a manual SQL insert with the optional fields explicitly set to NULL and that works fine. So it doesn't seem to be an issue with the database table itself.

I can set the optional fields to have a default value of 0 which works but is really not ideal. First, I would prefer an unused value to be NULL rather than 0 since 0 is not a valid value for some of these fields - if they have a value it has to be between a preset minimum and maximum which I'm validating against, and if the value is 0 it fails validation. But if the value is NULL then it passes validation on the grounds that it is not being used at all.

Second, using 0 affects the UI for adding a new instance of the field - if all the sub-fields are blank then Drupal does not attempt to create a new field when the node is submitted. But if a default value is used then it assumes the user is submitting a new field instance and it triggers validation on all the other sub-fields making it impossible for a user to edit the node without creating a new field entry each time.

So the question seems to be: How do I allow Drupal to accept NULL values when NULL values are a valid option?

UPDATE 2011-11-28 in response to testinni

I believe the field system is handling the actual saving of the data for me. The form for the field elements is defined in hook_field_widget_form() and include definitions like this:

$element['repayment_amount'] = array(  
   '#type' => 'textfield',  
   '#title' => 'Repayment amount',  
   '#default_value' => $values['repayment_amount'],  
   '#size' => 12,  
   '#maxlength' => 10,  
   '#number_type' => 'integer',  
   '#min' => 1,
   '#max' => 10000,
   '#field_prefix' => '$',
   '#element_validate' => array('number_field_widget_validate', 'combofield_range_validate'),
   '#weight' => 35,
) + $element_base;

where $element_base is the prepopulated $element array that gets passed into hook_field_widget_form() which has field properties like #field_name, #description, #title, and #required filled in, and is used as a base for all sub-fields.

#default_value takes on any previously set value for a saved field, and will naturally default to NULL if it doesn't exist. I have also tried making an explicit check here and supplying NULL or '' as a fallback.

The #element_validate functions work, and once these have passed there is only one more hook before Drupal saves the field: hook_field_presave(). I'm using this to round the decimal value of the main value field. I copied this (along with much of the settings and widget forms) from number.module which provides the standard Drupal numerical input field types.

So for the sake of completeness, here are hook_field_presave and hook_field_is_empty:

function combofield_field_presave($entity_type, $entity, $field, $instance, $langcode, &$items) {
  if ($field['type'] == 'combofield_expense' || $field['type'] == 'combofield_debt') {
    // Let PHP round the value to ensure consistent behavior across storage backends.
    foreach ($items as $delta => $item) {
      if (isset($item['value']) && isset($field['settings']['scale'])) {
        $items[$delta]['value'] = round($item['value'], $field['settings']['scale']);
      }
    }
  }
}

function combofield_field_is_empty($item, $field) {
  switch ($field['type']) {
    case 'combofield_expense':
      $empty_check = array('name', 'value');
    break;
    case 'combofield_debt':
      $empty_check = array('value', 'interest_rate');
    break;
    case 'combofield_goal':
      $empty_check = array('name', 'date');
    break;
  }

  if ($empty_check) {
    foreach ($empty_check as $subfield) {
      if (!empty($item[$subfield])) return FALSE;
    }
    return TRUE;
  }
  return FALSE;
}

I wonder if I have to catch the submitted results in hook_field_presave and convert any empty strings to NULLs? I'll give that a go and report back.

Answer 1

Can you post the code you use to fill-out the values of your columns ?
I suspect you're passing '' (empty string) which, for MySQL, is different from NULL and is incompatible with the numeric format you set up for your column.

Answer 2

I had to loop over all the values in hook_field_presave() and convert all the empty strings to NULLs.

Odd that the Field API doesn't do that as a matter of course.

@Scott

Answer 3

I had to loop over all the values in hook_field_presave() and convert all the empty strings to NULLs.

Odd that the Field API doesn't do that as a matter of course.