MySQL attempts to insert nulls for blank strings

[andybellenie]

Integer column, not null, default 0.

Try to create a new record, with that property as a blank string (e.g. an empty form field):

<cffunction name="new">
	<cfset company = model("Company").new()>
	<cfif StructKeyExists(params, "company")>
		<cfset company.setProperties(params.company)>
		<cfset company.testinteger = "">
		<cfset company.save()>
	</cfif>
</cffunction>

This will result in a MySQL crash: Column 'testinteger' cannot be null

Why include that property during insert if it is blank? The database default is essentially useless in this situation.

[perdjurner]

I agree, there's no reason for Wheels to pass NULL (which is what a blank string translates to in the SQL) to a column that does not accept NULLs.

Maybe we can delete the object property when all of the below are true:

1. it's a blank string.
2. it's an insert.
3. column does not allow nulls.
4. column has a default value.

In theory we could do it any time the string is blank (like on an update for example) but that might be too much framework "magic" perhaps?

[andybellenie]

This will still work without 3 and 4. Basically, we never try to set a blank string during insert.

If you try and set a blank string on a non-nullable column you won't get past validation.

The only scenario when you might want 3 and 4 is if you a) skip validation and b) really want your column to have an empty string in it.

As it is a Wheels principle that NULL and blank string are considered equivalent, I don't see it as required. What do you think?

[andybellenie]

I agree about updates, this is only an issue because it stops database defaults from working when inserting

[perdjurner]

Agreed, it'll probably be fine without 3 and 4 like you said.

[andybellenie]

Ok, it's an easy fix - however it does break the test "Columns that are not null should allow for blank string during create"

...which sounds suspiciously like my way of writing...

[perdjurner]

To play it safe (and to keep that test passing) maybe we only remove the object property when the column has a default value set then?

[perdjurner]

@andybellenie

Can this be closed?