I have an address2 field on my form.
I want to insert NULL value into the database if the field is empty.
I can’t seem to figure out a way to do that. It will put the empty string only.
I am using SC V5 and MySQL 5.1.
I have tried putting something like an “if” statement and then setting
{address2} = NULL;
but it doesn’t seem to do anything.
Any thoughts?
Re: Insert Null values into field
did u do the if in the onBeforeInsert??? What type of DB are you using??? If you’re using MySQL, you should be able to set the default value for address2 to NULL.
Re: Insert Null values into field
Using MySQL. When I go into my form, the value in the database for Address2 field is null. I actually added my code to the beforeupdate section, but it didn’t appear to work. So if I go into my form application to make changes to the data, and don’t even change Address2, it replaces the Null value with an empty string.
I think the default value would only be used for inserts, correct?
Re: Insert Null values into field
you are correct on that one, but if someone inserts a record without filling in 'Address2", it defaults to null. If they update the same record and don’t change ‘Address2’, it should still be null, correct?? Or are you saying that even if its null, onUpdate makes it an empty string???
Re: Insert Null values into field
If I show the field, don’t make any changes to it, then update the record, it gets changed from null to empty string.
Re: Insert Null values into field
so put a check in the onBeforeUpdate:
if(!sc_changed({Address2}){ {Address2} = NULL; }
Re: Insert Null values into field
If I just do a
{Address2} = NULL;
just to try to even force it to Null, it doesn’t work.
If you put quotes around it, it just puts the literal value of ‘NULL’ into the field.
Re: Insert Null values into field
then execute ur own sql statement, in the onAfterUpdate:
sc_exec_sql("UPDATE table SET Address2=NULL WHERE id='$id'");
or something like that…
Re: Insert Null values into field
I am sure I could create a sql statement to run in the afterupdate area and use if statements so that if the field = ‘’ then make it null,
but that sure seems like a pain.
There should be some method of saying that if the field is blank make it null versus make it an empty string.
Re: Insert Null values into field
It is my understanding that if your field is set to NOT NULL and you perform an update, even if updating another field, MySQL will automatically set VARCHAR/TEXT fields to blank instead of NULL.
In a new table with 2 fields, 1 intfield, 1 charfield
insert into table (intfield) values (1);
select * from table where charfield is null; 0 results
select * from table where charfield = ‘’; 1 result
Regards,
Scott.
Re: Insert Null values into field
So what are you saying Scott? That he has the field set to ‘Not NULL’? So if he sets the address2 field to NULL, then he’ll be ok?
Re: Insert Null values into field
If a column is set to NOT NULL in mysql, then that simply means that you cannot have a Null value in that column.
You could put ‘’ which is an empty string but you can’t actually put Null.
If the column is set to NULL, then you can have it’s value set to Null just fine.
I do have Address2 column set as NULL in the database, if that was not true I could not put a Null value in it.
Scott, You probably have a Default of ‘’ set on that column in your database, so if you don’t insert anything it then defaults to an empty string. If you set your charfield as NULL and remove the default, if you don’t put a value when you do the insert, it will have a Null value.
Correction/Clarification: If you have a field that is NOT NULL, and you don’t give a value nor do you have a default, the row will still insert (though it will raise a MySQL warning), and it will have an empty string, but this doesn’t help me because my column is definitely allowing Nulls
Re: Insert Null values into field
It would be nice if you didnt have to, but you just might need to execute your own SQL, that way you know it will have the null value if it should be null.
Re: Insert Null values into field
Under field -> database value, you can select “null” for insert and update properties. When you leave a field empty then it will actually insert null values into the database. This works for my integer fields. Is this what you were looking for?
Re: Insert Null values into field
Freezer, I had tried that before I thought and it hadn’t worked, but it’s working now, so that should work.
Thanks,
Alan
@rienkdevries - You saved the day for me. Still valid in 2016 for SC8 for a VARCHAR field. Even works using a SELECT field and SQL to build a dynamic picklist!
Brad