1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Save NULL in datawriter

Discussion in 'XenForo Development Discussions' started by Marcus, Oct 28, 2012.

  1. Marcus

    Marcus Well-Known Member

    My datawriter stores NULL always as 0.
    PHP:
    $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:
    PHP:
    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),

                )
            );
        }
     
  2. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    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.
     
    James likes this.
  3. Marcus

    Marcus Well-Known Member

    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.
     
  4. Marcus

    Marcus Well-Known Member

    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.
     
  5. Chris D

    Chris D XenForo Developer Staff Member

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

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

    Jake Bunce XenForo Moderator Staff Member

    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.
     
  7. Uniphix

    Uniphix Active Member

    I would highly recommend the Devs to do this:

    PHP:
    /**
        * 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...
     
    KenSmith and Marcus like this.
  8. Uniphix

    Uniphix Active Member

    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.
     
    Marcus likes this.
  9. Alteran Ancient

    Alteran Ancient Well-Known Member

    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:
    PHP:
    /**
        * 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.
     
    Marcus likes this.
  10. Chris D

    Chris D XenForo Developer Staff Member

    I'll say what I said before.

    Why can't you use 0?
     
  11. Alteran Ancient

    Alteran Ancient Well-Known Member

    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.
     
  12. KenSmith

    KenSmith Active Member

    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:
    Code:
      /*
        * 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.
     
    rour and Marcus like this.
  13. Uniphix

    Uniphix Active Member

    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
     

Share This Page