Null and empty strings are not the same thing

[adamcameron]

We've just fallen foul of this:

Blank Strings and NULL Values
Since there is no concept of null / nil in CFML (at least not in the earlier versions of it which we still support), CFWheels will assume that when you save a blank string to the database it should be converted to NULL.

For this reason we recommend that you avoid having blank strings stored in the database (since there is no way to distinguish them from NULL values once they've been mapped to a CFWheels object / result set).

To quote John McEnroe: "You cannot be serious".

I would request - with all due respect - that in CFWheels 3.x that you get rid of this "less than ideal" design decision.

CFML might not handle null values as well as it perhaps could (partial Lucee implementations notwithstanding), but databases and the real world do. Empty strings are not the equivalent of nulls, and are entirely valid values to store in a non-nullable field.

If CFWheels wants to implement storage tier handling between app code and the the DB, then it's actually its job to deal with stuff like this. that's what it's for. Not go "erm... tricky... let's pretend they're the same".

I can't really see how it would be so difficult. Property set to an empty string? It's an empty string. Property not set at all. Null. [shrug]. This is - indeed - how CFML has always dealt with this (at least as far back as CF10...): https://trycf.com/gist/d42f20da49016ebabf2de32d6d35ea42/acf?theme=monokai

---

Tangential:

at least not in the earlier versions of it which we still support

There's no need to support any version of CF older than 2021. Even Adobe doesn't support versions older than that (extended support for 2018 ends in July 2024). Lucee doesn't even actively support Lucee 5 any more, so you only really need to target Lucee 6 there.

Don't burden yourself with "but lots of ppl use unsupported versions...". Yes. Unsupported. Without support. You don't need to support them. It's on them to deal with that, not you ;-)

[neokoenig]

Yeah wheels 3.x sounds like a good time to address this

[timsayshey]

Something like set(blankStringToNullConversionFlag=true);?

[neokoenig]

Sure, if you want to put a flag behind it (which maybe the way to go, who knows).
But having worked in the Node world for the last year, it is something I think needs addressing.

I suspect it would require "some work" though - don't all these things ? :)

[chapmandu]

I'm wondering how this would work.. would any properties not passed in be set to null, or would one need to explicitly pass in every property with it's existing value?

// users table: id, name, email, likes, dislikes, createdAt

user = model("User").findByKey(1);
user.update(name = "Foo"); // would email, likes, dislikes be set to null?

// or would I need to do this? (consider a table with 100 columns)
user.update(name = user.name, email = user.email, likes = "Long Walks", dislikes = user.dislikes);

// likes & dislikes would still be set to null
user.update(name = "Foo", email = NullValue());

[neokoenig]

The way I see it is:

If you do user = model("User").findByKey(1); = that should return the entire "User" object. Some of those columns may be null, others might have a value, and others might be set to a "" (blank) string.

When you do user.update(name = "Foo") it should then set that single property and ignore all the others.

So taking it back a step. Let's assume your user model has no defaults.

user = model("user").new();

... results in:

user: {
id: null, 
firstname: null,
lastname: null
}

If you have an autoincrementing id, or a afterNew hook which sets some values, you might get the id as a UUID etc.

The point it is if you save that object in the DB, then firstname should not be a blank string, it should be null.

However, if I set

user.update(firstname = "") then that should set it as a blank string (and not null).

Wheels at the moment will set firstname as null in that context.

If I wanted to explicitedly set firstname as null, I should have to pass user.update(firstname = IsNull() ) (or whatever the CFML equivalent is these days).

If the value is simply omitted, then it shouldn't be touched (If it's never been set, it should thus still be null).

it's a bit late here so I may have got that wrong lol.

[chapmandu]

I may be wrong here but:

user.update(firstname = NullValue()) is the same as user.update() because in the update method, IsNull(arguments.firstname) will be true for both.