On handling NULL values

burnchar

When developing a multi-database solution, Editor seems to be ambivalent with NULL.
This conversation mentions that as of 1.7, Editor treats NULL as an empty string: https://datatables.net//forums/discussion/comment/9706/#Comment_9706

Empty strings don't really work for many situations. Take for example the nullable integer field, "FavoriteNumber".

• User opens an edit form with "FavoriteNumber" and 50 other fields. "FavoriteNumber" and other null (and empty but not null) values are shown as an empty string.
• User changes some fields but does not modify "FavoriteNumber".
• When user submits, Oracle interprets the empty string as NULL and writes a NULL. This non-standard and quirky behavior is actually useful in this one case.
• When user submits to Microsoft SQL, the empty string is interpreted as 0 (zero), so the user has changed the value of the field even though they did not intend to.
  This to me is not ideal, and I can't really find an easy way to deal with it. For example, if I set fields.defto null, an empty string still appears to be sent.

I think Editor should handle NULLs, which are absolutely a reality of database interaction.
This begs the question, "How can Editor handle NULLs?!"
Some ways that come to mind include:

1. For nullable fields (specified in Editor init or something), generate a checkbox for NULL. I kind of dislike this.
2. def:null sets the value to null instead of an empty string (or maybe def:undefined or similar)
3. Interpret some probably user-specified sentinel value as NULL, for example, "(null)", and set the field to this value when the incoming JSON for that entry is null.

I am sure it's more complex than this, for example, how can null be specified in HTML or over an HTTP POST or GET.
Perhaps an internal sentinal value of Ø, which is HTML Ø or URL-encoded as %C3%98 (not to be confused with ø, ∅, or ⌀ ...Ah, Unicode).

Any thoughts on how to handle NULLs, anyone?

allan

Editor treats NULL as an empty string

Not really. Unlike the Oracle developers I'm very much of the opinion that null !== ''! The main issue with nulls and Editor is the use of HTTP parameters to send the information from an edit to the server (much as you indicate) - it is basically a string value and you are left to infer on the server-side what the type is. Consider for example:

data[132][myVar]: ''

Is that an empty string or null? If an empty string, how do we represent null - like this:

data[132][myVar]: 'null'

But is that now null or null?

For this reason I recommend the use of formatters, specifically the IfEmpty formatter. That way you can tell Editor's server-side libraries exactly what you want it to do.

Editor should, and as far as I am aware, full handle nulls.

def:null sets the value to null instead of an empty string (or maybe def:undefined or similar)

This is difficult, because of what I describe above, but also because there is no null value in an HTML input type. You can't set an input type to null.

Now there are exceptions of course - we could use ajax.data to submit JSON data to the server, in which case basic typing is available and null could be correctly represented. I've actually wished many times that I'd taken that route when I first started working on Editor and that might happen with v2 whenever that happens. We could also use a custom field type that stores information in Javascript rather than the DOM, again allowing basic Javascript typing.

Does that all make sense?

Allan

burnchar

Editor treats NULL as an empty string

Not really. Unlike the Oracle developers I'm very much of the opinion that null !== ''! The main issue with nulls and Editor is the use of HTTP parameters to send the information from an edit to the server.

Now there are exceptions of course - we could use ajax.data to submit JSON data to the server, in which case basic typing is available and null could be correctly represented

I do use ajax.data to submit JSON. What I meant by "treats NULL as an empty string" is in part that when Editor gets NULL from JSON data, then the user clicks the submit button without changing anything, the submitted value is (at least with my current application) an empty string, even when submitting JSON.

For this reason I recommend the use of formatters, specifically the IfEmpty formatter.

The server-side libraries are great for many things, but we have switch entirely to all-custom server-side code because there are too many corner cases in our software that the libraries don't handle, especially when we are stuck using Oracle with its many limitations.

What I have done in my personal project application is to store the data type name, database type, and nullability of each column, along with an "emptyToNull" flag, which server-side code uses to replace '' with NULL. This is not at all ideal of course, not only because of its complexity, but because it guarantees that empty strings for those types are always null, even if empty string is the appropriate value. This application has certain unusual needs though so it was a reasonable choice.
It would be better still, I think, to have Editor's data JSON serialize nulls as nulls (when using e.g. data: JSON.stringify(data) in $.ajax) or to internally replace the null with some user-specified sentinel, which would resolve the problem of submitting NULLs with the downside of not allowing the sentinel value to be submitted without some kind of escape logic.
The latter can of course be done by looping through data in JavaScript, but not in every application.

allan

the submitted value is (at least with my current application) an empty string, even when submitting JSON.

Is this with the custom server-side libraries that you mention or the Editor defaults? If the client-side is submitting null (is it?) it would be up to the server-side to handle that.

Or have I missed the point? Is it that you can't submit null from the client-side that is the issue?

Thanks,
Allan

burnchar

Correct, I can't seem to submit a null from client-side even when using JSON.

JSON like the following is sent to DT:

///... "draw":1 and all that...
"data" : [["213", 1, "Charles", true, false, null, null]]
...

Some fields come in as null and DataTables displays them as such, "null" (using defaultContent).

However when I edit a field and submit, even without making any changes to null fields, those fields are submitted as empty strings:

{
    "action" : "edit",
    "data" :
    {
        "213" :
        {
            "SomeInt" : 1,
            "Fname" : "Charles",
            "Enabled" : "true",
            "Admin" : "false",
            "Inlinable" : "false",
            "Notes" : "",
            "Bio" : ""
        }
    }
}

Using the following for the ajax function:

function restAjaxCall(data, successCallback, errorCallback) {
    $.ajax({
            method: data.action === 'create' ? 'POST' : data.action === 'edit' ? 'PUT' : 'DELETE',
            url: restUrl,
            data: JSON.stringify(data),
            dataType: 'json',
            contentType: "application/json"
        })
        .done(function(json) { successCallback(json); });
};

A nullable field in editor.fields is defined like:

{"name":"Notes","data":6,"label":"Notes"}

The DT column is defined like:

{"data":6,"defaultContent":"null","searchable":false},

At some point between DT's GET request and Editor's POST, possibly at the browser's JSON.stringify(data) function, nullity is lost (or replaced with empty string).

allan

Interesting. I'm reasonably sure that it will because Editor's fields don't really allow a null value since they write the value to an input element (and thus all typing information is lost).

To check that, could you add console.log( data ); as the first line in your restAjaxCall function and then in your browser's console inspect the data. I suspect that it will be "null" there as well rather than null.

Allan

burnchar

Using console.log( data );, values that came into Editor as a null were submitted as "";
I understand that HTML forms have no concept of null, but is does seems significant that one can change data that they did not modify by submitting the form.
Is there some way to use Javascript's more detailed knowledge of the types to set null fields in data to null rather than ""?

allan

Its a very good point, and I agree that this is a form of data corruption.

Its certainly possible to have Editor submit null rather than an empty string by modifying the data submitted in preSubmit. But that doesn't really solve the issue, because you still don't know if the original data was null or an empty string.

I think what is possibly required is something a bit deeper in Editor to let the fields have knowledge of null, sorting that separately from the DOM value. I've tried to avoid that as it means that there are two different data stores for the value and we need to determine which of the two to use.

I'm afraid I don't have an immediate solution for this, but I will dig into it and see what options are available.

Allan

burnchar

Empty arrays are sent as null (I'll have to look again as this is from memory, but some values are definitely serialized as NULL). I wonder if this could be used somehow.
In any case, thanks for taking a look.

allan

Empty arrays are sent as null

From select? An empty array shouldn't actually be sent at all. There was a bug in jQuery (which they fixed in v3) where a multi-select would return null rather than an empty array, but Editor should be abstracting that out.

Allan

lester.holt

I am having this exact problem. I am trying to change the value back to null in preSubmit, but it does not seem to be working. I call editor.set('rank', null); but the subsequent editor.get() shows that it is still storing an empty string.
Any advice?

allan

I hate to say it, but not as such at the moment. All of Editor's fields basically use input or similar to store the value (its a form editor after all). input can't hold a null value since that is typed, it will only hold an empty string - which is why it appears to get converted.

It would be possible to have a custom field type plug-in that would store typed values if passed in, or to replace that with the user entered value, if keyup (or similar) is triggered in the input.

Allan

burnchar

This seems to be a fairly profound limitation of web-based forms, so I think all Editor can do is handle it with a sentinel value or with a layer on top of web forms' limited capability.

I think it can be argued that support for null is important in a library that deals so much with database tables because null does exist, and aside from the Oracle-specific inability to distinguish empty string types from NULL, it is distinct from an empty string, especially when sending a PK for a joined field.
I think one long-term solution is to have a nullable flag for each field. When using a data transfer method that supports null, such as JSON, send and receive nulls directly (sending may be a trick because as I recall, all submissions are via a form post). When using a method that does not support null, support a configurable sentinel that internally maps to null, e.g. if(val == sentinel) val = null.

On the UI side, DataTables can display the sentinel or just "null" (I believe it already does with JSON data). The Editor is where it gets tricky.
A few thoughts on this:

• For text fields, date, datetime, password, textarea, others: reduce the width of the field and fill the empty text with an isNull checkbox.
• For checkbox fields, use a tri-state checkbox where the dimmed state is null
• For radio buttons, either don't support null at all or create an additional radio button entry for null
• For vanilla select boxes with nullable set, add a checkbox entry for Null or when a placeholder is enabled, make that placeholder submit a null.
• For plugins like select2, I'm not sure since I haven't even been able to make select2 work to allow an empty selection at all. I'll probably post about this later. Probably per-plugin bespoke support.

For now, what we have decided to do is code in a sentinel value, "(null)", and handle it manually server-side and client-side.

allan

A null state for a field is certainly an option, but its not something that I think would be very user friendly. I can't imagine trying to explain the difference between an empty string and a null value to a non-techy - I can just see the shrug when saying one is empty data and the other is the absence of data. For all that I think its nonsense the Oracle treats them as the same, it does hide some of the technical implementation.

On the server-side Editor has ifEmpty() formatters which can be used to convert from an empty string to null - that can be useful for dates for example, where null would make sense over an empty string. But a text field which could be null or an empty string is a little more tricky.

I think the only way to do that at the moment would be to create a plug-in field type method which allows a field to be marked as null (phpMyAdmin does that with a checkbox) as burnchar says and then send the data to the server as JSON which can be done with ajax.data.

Allan

burnchar

Certainly explaining the finer points of nullity to users is not a good target, but there are real cases where null support is quite important:

1. Joined tables where empty refers to an empty value in the foreign table but null means "no reference to that table".
2. Unintended edits: Opening an edit form which has multiple null values, changing an unrelated field, and submitting -- the user has now made unintended changes.

I am not sure if either have great solutions or if there are other more significant ones, but these two have definitely bitten me.

allan

Good points!

Joined tables where empty refers to an empty value in the foreign table but null means "no reference to that table".

For that I would suggest a select input (select) where you are restricted to the values available in the joined table, and you can use placeholder and placeholderValue to set a null value if no item should be selected.

Unintended edits: Opening an edit form which has multiple null values, changing an unrelated field, and submitting -- the user has now made unintended changes.

Yup - I can see this one as being tricky. I think this probably circles back to what to do about how a field should be shown as null or an empty string. In this case it sounds like a field was null, the edit happened and empty strings were written to the database. But if you were intentionally editing that field, what would happen - would it be written as an empty string, or null? How do you show that distinction to the end user (in the table, not the form)?

Allan

pushkin

I came to something like this for my own app:
1. When editing, I always have a reference to an original entity, which can have nulls.
2. On preSubmit I check and merge properties from original entity to the new entity so that nulls substitute new values when the new value of a field is "" and the old one is null (with some other extra checks).
3. I use json for transferring data.

burnchar

Allan,

This is a surprisingly annoying problem because of the confounding factors of HTML limitations, user knowledge of NULL, and availability of null in the data (both as a source and during submission).
I still think it is worth pursuing. I can see a business avoiding Editor (or web forms altogether) because of this, and I am sure that user lester.holt and I are not the only ones to run into this issue -- it is trivially easy to think of use cases, e.g. a user's middle name where null means "user did not enter their middle name" but empty string means "User explicitly has no middle name."

I spoke at length with colleague Paul Minner about this and worked a text field solution that doesn't feel immediately wrong.

• DataTables and Editor must remember null state when available
• Data must be transferred both ways in a manner that supports null such as JSON. I think it is sufficient to explain that HTML simply lacks the capacity for null support. Perhaps Editor can later support HTML forms using a sentinel like %00.
• DataTables gets a language option for null. Default is, e.g., "null", "unknown", "unspecified"
• Perhaps another language option for Editor (see below).

Q: How does the user determine whether a field is empty or null in DataTables?
A: The init option specifies text to show for null. DataTables should display that text by default in italics, a lighter font color, or some other means to help distinguish null from "null", perhaps using a configurable CSS class.

Q: How does a user determine whether a field is null in Editor?
A: Paul Minner suggested using placeholder text, perhaps HTML placeholders, containing the aforementioned language, so unspecified would appear as dimmed text over the empty text field. Empty fields which are not null have no placeholder text.
I think this is about as intuitive as it can get, especially if the text can be specified for each application or (better yet) each field.

Q: How does a user set or unset null?
A: My latest refinement is still to use a checkbox, but hide it until the field in question has focus.
For example, user clicks or tabs to a nullable text field and sees a control pop-up, much like the DateTime field's calendar, that shows, "Mark as unspecified: ☑"

This specific implementation implies a special nullable text field plugin. Ideally an ordinary text field could optionally have this intelligence, though the popup would have to go since it is at high risk of conflict with other text field enhancements.

How does this sound?

allan

Excellent suggestions. I think you are right - a checkbox is probably the way to handle this, and its all to do with the wording of that label, which you have nailed there!

I'm wondering if it might be possible to provide a wrapper function that will take each of the field types from Editor and make them nullable - e.g. text-nullable, datetime-nullable, etc. That won't be in the 1.7 release that is up coming, but it is something that I will look into as I agree, I can see that being useful.

Allan

Gaissmayer

Hello Allan,

your last comment on this topic is from 2017, when you wrote to look into the topic of nullable data-fields in editor. May I ask, what your actual status is on this?

Christoph

allan

Hi Christoph,

We haven't progressed this yet I'm sorry to say.

Allan

sbsu

Hi Allan and team,

The fact that I found this thread after searching means that I too have run up against these issues in my projects.

Admittedly, my understanding of datatables is still far too limited but so far in my use cases, users will clear out a form field and submit the form, thinking that they are not submitting a value in a field. However, clearing a date field and submitting the form is actually submitting an empty string into the database.

In my case, even though SQL Server (glad I don't have to use Oracle anymore) allows null values, as you know, submitting an empty string into this nullable field will cause SQL Server to store a value of 1900-01-01 00:00:00.000 into the field. Users are surprised and annoyed to see 1900-01-01 00:00:00.000 in all of the table cells which used to be empty.

Of course it's possible to format the cells for display but this also causes its own problems. For example, if the cell value is actually 1900-01-01 00:00:00.000 but I change it to an empty string on pre render, this cleans up the table view but contributes more confusion to the end user. It also complicates table sorting.

At the moment, I don't have any original ideas to offer. Thanks for such awesome software. I'm off to study the .NET documentation on formatting again but this problem has also significantly affected my projects as well.

If anyone would like to share, it could be quite helpful to see some code from those who have devised their own workarounds to this issue.

Thank you.

sbsu

I have been experimenting with some back-end methods, such as database triggering, firing off a corrective method, or signaling an API method to reactively touch the database after datatables inserts an empty string value into a DateTime Field.

Since datatables is the only piece of code I have which is allowing empty strings into database date fields, I can trigger some code that looks for changes in said date fields and can use a native data type in an update command to transform the data as intended:

Cmd.Parameters.Add("@dateTimeFieldNullValue", SqlDbType.DateTime).Value = DBNull.Value;

It is a compromise situation to me and definitely incurs a cost penalty but it will work until I code something differently.

allan

Hi,

Are you using our .NET libraries for Editor? If so, then the ifEmpty() formatter will do what you are looking for in this case - e.g.:

new Field( 'myDateField' ).SetFormatter( Format.IfEmpty( null ) );

Then if an empty string is submitted, a null will be written to the database.

The discussion in this thread is really what to do about cases where a field can be either an empty string or null - a text field is a good example of that. Dates and times however don't really have that problem - they can't be an empty string. They either have a value or are null.

Allan

sbsu

Thanks!

Maybe we can move my replies to a new thread.