Deleting a date from a record

[BenFranske]

How can you delete a date (and leave it blank) for a record?

If you try to do this on a mariadb field with type DATE you will get an error:

Code: Select all

SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect date value: '' for column...

This appears to be a correct error. The SQL:

Code: Select all

UPDATE individuals SET `death_date` = '' WHERE `ID` = '3be018e16a985';

Is not valid. If the date is set to nothing the SQL should be like:

Code: Select all

UPDATE individuals SET `death_date` = NULL WHERE `ID` = '3be018e16a985';

Is there a way to get nuBuilder to create the correct SQL? Maybe a custom code PHP Before Save that can re-write the SQL statement?

Thanks!

[kev1n]

Hi,

Use the PHP AS event and set the date to null if it is blank.
See here for an example: https://forums.nubuilder.cloud/viewtopic. ... 04&p=17522

[BenFranske]

Because that's an AS event though nuBuilder will still try to set the date to an empty string and get an error back from SQL though won't it? That will end up in the nuDebug log each time, etc.?

Is there a way to check and modify the SQL query in a BS event so that the incorrect SQL never even hits the server? I'm unclear if there is a way to modify the SQL that is going to be sent in a BS event but it seems like that would be a better fix if it could be done.

Or maybe a way to have nuBuilder add the EMPTY_STRING_IS_NULL option to sql_mode it's using before running the query?

[kev1n]

You could try this fix:

https://github.com/smalos/nuBuilder4-Bu ... e_types.md

Basically, it checks whether the column is of type date and if the field value is empty, null is written to the DB.

[kev1n]

Please let me know if it works for you.

[admin]

kev1n,

I have added your fix.

Thanks


Steven