INSERT/UPDATE NULL WHEN empty value

[eluser]drewbee[/eluser]
Hi All,
I have a table that is of datatype float and it allows NULLS.

I would normally expect if an empty string is passed into the database, it sets it to null. At least this is the behavior with MS SQL. When doing an insert with MySQL, it ends up inserting a '0' (assuming an empty string as well for varchar datatype), even with the DEFAULT VALUE set to NULL.

You have to explicity set null as the value to be inserted

Code:

$insert = array('column' => null);
$this->db->insert('table', $insert);

While I havn't decided exactly where I wanted to put this, I threw it into the form helper even though it should be with the database. Unfortunately, we can't extend the database classes (smoothly)... and I automatically load the form helper anytime the form validation class is loaded so it only seemed logical.

Has anyone else run into this?

Helper function:

Code:

function set_null($value = '', $force_null = FALSE)
{
    if ($force_null == TRUE)
    {
        return NULL;    
    }
    return $value == '' ? NULL : $value;    
}

Note: I automatically trim() all POST data. It would be wise to do a trim here as well if you don't.

controller:

Code:

$insert = array('col1' => set_null($this->input->post('field1')),
                'col2' => set_null($this->input->post('field2')));
$this->db->insert('table', $insert);
$this->db->update('table', $insert);

Thoughts?

[eluser]Evil Wizard[/eluser]
An empty string by very definition is not null I find it hard that MSSQL converts the "" to NULL, but hey it's MS. I think with your field being a float and the input being a string the data is being type juggled, the string is converted to a numeric, but because the string is empty and so equates to boolean false which equals integer 0. This doesn't help you setting the NULL value for your field though, I personally use Doctrine for my ORM

[eluser]drewbee[/eluser]
Hi Evil,

Sorry, I should have been more clear and what the general practice, and more importantly what I use with MS SQL, and that is Cold Fusion. Components that are talking to the the database and have a allowable null type are passed arguments via the cfparam tag. The cfparam tag has a null attribute that when set to yes will force the value to go into the database as null. In this case, if the string length is 0, the value null="yes" so anytime an empty string is passed it, null is set.

Code:

<cfparam value="#arguments.name#" cfsqltype="cf_sql_float" null="#YesNoFormat(NOT LEN(TRIM(arguments.name)))#">

My apologies, I wasn't thinking clearly on this one. I do agree there is some datatype juggling, however each of these records are going to be used to gather stats, and anytime the form is submitted for these various stats, a minimum of 1 field is required. 0's would obviously skew the results.

For now, the set_null function should provide decent functionality for what I need.