Saving an empty date

Databases
1

Hi all,

I have a MySql database with a date column in a table. I would like to update the date column according to user’s entry, here is my code:

if len(FieldDate.text) > 0 then
  rec.DateColumn("Expiry") = app.FormatStringToDate(FieldDate.text)
else
  dim d as new date
  d = nil
  rec.DateColumn("Expiry") = d
end if

But the date column seems does not update with a nil date, it remains the original value if there is one.

2

You might have the field defined as NOT NULL

Some software uses a dummy date of 31 DEC 9999 to mean ‘no date in here’

3

this is typical use of NULL

4

Jeff,

No, the field didn’t set to NOT NULL.

5

If I recall that is a “bug” (feature?) in mySQL.
I was the webmaster of a large site years ago, and I had to define a “dummy” date to use, as it would not allow date datatypes to be assigned NIL… but then it was “years ago” :slight_smile:

6

If I really need to use a dummy date to represent nil, do you suggest to use a very old date, e.g 01-01-1901 or a far future date say 31-12-2099?

7

Assuming MySQL doesn’t allow for empty string dates in date fields, why not just return that field in the database to NULL, as suggested by Antonio above? Doesn’t Xojo set a database field to NULL by setting its value to Nil? Like this:

rec.DateColumn("Expiry").Value = Nil

I’m still learning Xojo, so bear with me if this is naive.

8

The issue is under certain circumstances, you cannot set a mySQL date to anything other than a legit date…I do not know if this is by design or a bug in mySQL… but check Google and you will find tons of discussions about this…

(a mySQL issue, not an Xojo one)

seems 13-Dec-1901 is the smallest date that mySQL will accept (poor design in my opinion)…
If you have the option… I would change it to a TEXT field, and store your own SQLDate formatted string (oh and then NULL will work!)

9

Take a look at this discussion, where MySQL date fields are set to NULL:

https://stackoverflow.com/questions/1691117/how-to-store-null-values-in-datetime-fields-in-mysql

I have no experience with MySQL, though.

10

Thanks all, I have read that thread also. Hoping I did not mislead you. MySQL do accept null for date fields during insert record. Just if I want to empty a date field during update, it does nothing, the code is:

D = nil
Rs.edit
Rs.field(“Expiry”).datevalue = d
Rs.update

11

It doesn’t seem that mySQL does not accept null value for date column during update, the following SQL works fine in mySQL workbench:

UPDATE Table SET Expiry = null WHERE UID=1

Does this mean there is a bug in the Xojo mySQL database plugin?

12

I think you need to set NULL via Value and not DateValue (or StringValue or or or …). Like this:

rst.Edit() rst.Field("Expiry").Value = Nil rst.Update()

13

That’s what I was trying to say earlier.

Not

D = nil Rs.edit Rs.field("Expiry").datevalue = d Rs.update

But

Rs.edit Rs.field("Expiry").Value = Nil Rs.update

But I have no experience with MySQL, so am not sure that makes a difference. I thought the Value needs to be nulled, not the date.

14

Thanks Eli, it works now. But why? What is the difference between .value and .datevalue, .stringvalue …etc?

15

Because the Xojo framework was programmed that way.

rst.DateValue("aFieldName") = Nil // in database –> '00:00:00' rst.Value("aFieldName") = Nil // in database –> NULL
This belongs into docs IMHO.

16

With respect to a Date Field, it appears that MySQL differentiates between these three:

"" 0 NULL

The first throws an error.
The second is captured by Xojo as dateField.DateValue=Nil or dateField.DateValue=0
The third is captured by Xojo as dateField.Value=Nil

I think that Xojo is interpreting “dateField.DateValue=Nil” as saying there’s a Value there, and that Value is Nil, and the only Value that makes sense as Nil is 0. Whereas, “dateField.Value=Nil” says there’s no Value there at all, so the database field is NULL.

17

Also note this in the docs:

[quote]The properties that get and set the values of specific data types are recommended over Value.
Set Value to Nil to set the field to NULL.[/quote]

https://documentation.xojo.com/index.php/DatabaseField.Value

18

So it is in the wrong place in the docs. Because you need to already know about Value to set NULL.

This is what I assume somebody does in Tony’s situation:
Go to the docs and start with RecordSet
https://documentation.xojo.com/index.php/RecordSet
NULL is not mentioned, so click on the property Field
https://documentation.xojo.com/index.php/RecordSet.Field
NULL is not mentioned, so click on the property DatabaseField
https://documentation.xojo.com/index.php/DatabaseField
NULL is not mentioned, so click on the property DateValue
https://documentation.xojo.com/index.php/DatabaseField.DateValue
NULL is not mentioned, so … what do I do now?
Especially since when doing a SELECT query, DateValue returns Nil if the database carries a NULL value. And one would assume that in the opposite direction it is the same.

Weak docs. Bad. Sad.

19

< 5 minutes and its added to the notes for DatabaseField which makes it easier to find

20

Quick, I have to look up the antonyms of weak, bad and sad…