Save NULL in datawriter

[Marcus]

My datawriter stores NULL always as 0.

$var=NULL;
$dwData = array('user_id' => $userId,	'var' => $var);		
		);
$dw = XenForo_DataWriter::create('MyDatawriter_Location');
$dw->bulkSet($dwData);
$dw->save();

The value should be an integer value, but also capable of being just NULL. This is my datawriter:

protected function _getFields()
	{
		return array(
			'abc' => array(
				'this_id'    => array('type' => self::TYPE_UINT,   'autoIncrement' => true),
				'user_id'    => array('type' => self::TYPE_UINT,   'required' => true),
				'var'    => array('type' => self::TYPE_UINT,   'default' => NULL),

			)
		);
	}

 

[Jake Bunce]

If I wanted a column to be null I would simply not set that value when using the datawriter (omit 'var' from $dwData). Then make sure the field is nullable in the table definition.

I have never tried explicitly setting a null value in an insert. That may be possible, but it's not necessary. The field will be null if you do not specify it in the insert.

 

[Marcus]

Yes, the values gets NULL if I don't specify them as all. I hope this is also true for Datawriter Updates, when there is already an existing item in the database.

 

[Marcus]

Whenever I save NULL, it is changed to 0, even if the field is checked as NULL compatible in mysql database. The default value is NULL in the datawriter.

When I try to save "new Zend_Db_Expr('NULL')", I get an error this value can't be translated into integer.

 

[Chris D]

Why does it have to be null? What's wrong with 0 or blank?

It's usually not something you have to worry about.

 

[Jake Bunce]

The default value is NULL in the datawriter.

Ignoring the datawriter... make sure it's nullable in the table definition. Also make sure it has no default value in the table definition. Then don't specify that field when using the datawriter if you don't want to write any value to it.

 

[Uniphix]

I would highly recommend the Devs to do this:

/**
    * Casts the field value based on the specified type (TYPE_* constants).
    *
    * @param string $fieldType Type to cast to
    * @param mixed $value Value to cast
    * @param string $fieldName Name of the field being cast
    * @param array Array of all field data information, for extra options
    *
    * @return mixed
    */
    protected function _castValueToType($fieldType, $value, $fieldName, array $fieldData)
    {
            if( $value == null && isset($fieldData['canBeNull']) ){
            return $null;
    }
 
    // ... Other bit of code
 
}

This way if we do $dw->set('some_value',null); it will nullify the value IF it can be null or not...

 

[Uniphix]

Why does it have to be null? What's wrong with 0 or blank?

It's usually not something you have to worry about.

Lets say that a column is only required if the end-user assigns that value. For example we have a column where it is UNIQUE if we leave it "BLANK" which is optional it will error out that the values are unique because two or more entries are "" string rather than NULL value. The fix I just did allows null values to be passed to be set, so in this case if they set it to be "" when save, I can just easily do a check for empty string, if it is empty then set that value as null before passing it to the DataWriter. Then the datawriter will still process the value and then NULL the value in the database.

Like I said, a value may become blank by the end-user, and that value may NEED to be null in the database because of UNIQUE value purpose, in this case I ran into this problem.

 

[Alteran Ancient]

Massive Necro-Post, I know, but there is a solution if you are (like me) trying to explicitly set a value as NULL on an UPDATE function.

In your DataWriter definition file, set your field's type to TYPE_UNKNOWN - this will mean XenForo will pass the value as-is without attempting to sanitise it for the database. Only do this if you're sure a funky value cannot make its way into your field (say, if it's being set programmatically and not by an end-user).

Her's an example:

/**
    * Gets the fields that are defined for the table. See parent for explanation.
    *
    * @return array
    */
    protected function _getFields()
    {
        return array(
            'some_ban' => array(
                'id'    => array('type' => self::TYPE_UINT,  'autoIncrement' => true),
                'name'    => array('type' => self::TYPE_STRING, 'required' => true, 'requiredError' => 'please_enter_valid_name'),
                'banned_by'    => array('type' => self::TYPE_UINT,  'required' => true),
                'ban_reason'    => array('type' => self::TYPE_STRING, 'required' => true, 'requiredError' => 'please_enter_valid_ban_reason'),
                'banned_on'  => array('type' => self::TYPE_UINT,  'required' => false),
                'duration'    => array('type' => self::TYPE_UINT,  'required' => true),
                'unbanned_by'    => array('type' => self::TYPE_UINT,  'required' => false),
                'unban_reason'    => array('type' => self::TYPE_STRING, 'required' => false),
                'unbanned_on'  => array('type' => self::TYPE_UNKNOWN,  'required' => false, 'default' => NULL),
                'ban_state'  => array('type' => self::TYPE_STRING,  'required' => false, 'default' => 'active'),
            )
        );
    }

The 'unbanned_on' field in this example uses the UNKNOWN type, allowing me to set the value as NULL.

 

[Chris D]

I'll say what I said before.

Why can't you use 0?

 

[Alteran Ancient]

I'll say what I said before.

Why can't you use 0?

Personally, I was using a STRING type to set a timestamp field in the database. If I inserted a row as new and wanted to leave the "unbanned_on" field as NULL, I could just neglect to set the field in the first place via the DataWriter method. This NULL value would represent a permanent ban that hasn't been unbanned.

However, if I had a timed ban that I then wanted to update to make permanent, there was no clear way to UPDATE the existing record so I could place the already defined timestamp with a NULL value - passing-in "0", an empty string and passing NULL when using TYPE_STRING didn't work. Using the UNKNOWN type for the DataWriter seemed to solve this problem for me. This table is being queried by another system (the system I am banning people from), so I need the data to be represent in a certain way. Zero dates didn't cut it for me, so I had to be able to distinguish between a date and a null in a particular field.

 

[KenSmith]

I needed this idea as well, to save latitude and longitude as null instead of 0.0 when geo-lookup fails on an address.

Using the excellent idea from @Uniphix above, I added this to my DataWriter class:

  /*
    * Override the native DataWriter _castValueToType to allow nulls,
    * as described at http://xenforo.com/community/threads/save-null-in-datawriter.39833/
    *
    */
   protected function _castValueToType($fieldType, $value, $fieldName, array $fieldData)
   {
     if( $value == null && !empty($fieldData['canBeNull']) )
     {
       return null;
     } else {
       return parent::_castValueToType($fieldType, $value, $fieldName, $fieldData);
     }
   }

Ah, the magic of inheritance.

I agree that this would be an excellent addition to the core DataWriter class, but this workaround will suffice until then.

 

[Uniphix]

I'll say what I said before.

Why can't you use 0?

I'll try to give you a good example:

MySQL on a UNIQUE Column in the database, setting it to 0 on two records will throw a "unique record issue" when setting it to NULL in the database IE allowing NULL on a specific column...

Records:

column_id, column1

where column1 = allow NULL, and is UNIQUE

1, NULL
2, NULL << CAN be made because NULL
3, 0
4, 0 <<< Cannot be made because of the UNIQUE Error


We have a list of clients with a column field name "domain" and the domain field is UNIQUE... When trying to clear it out when the client decides NOT to setup a domain...

IF I empty it with a blank string it will fire the UNIQUE error message if I NULL it then it won't error out it don't matter how many null unique record within that specific column I have...

U