Set Null a field to INSERT into DB

Applications Forms
#1

Hi all.
I have a field in a table with NULL property. In the Form, in this Field I don?t write anything. When I try to save the data, the insert command is: INSERT into TABLE_XX ( my_field) VALUES (’’) , but it?s not fine for my. How can I do when I?dont write anything into the field that the Insert command be like this : INSERT into TABLE_XX ( my_field) VALUES ( Null) . Thanks for Help.

#2

Re: Set Null a field to INSERT into DB

Are you issuing the INSERT command yourself, or are you talking about when you submit a form and SC saves the data?
How is the field setup, does it allow blanks or NULL?

Have you tried to validate your fields before you allow save?

Regards,
Scott.

#3

Re: Set Null a field to INSERT into DB

Hi Scott.

  • The Insert command is when I submit the form and SC saves the data
  • I?m using MySql DB. It is a varchar field with NULL property. In the Form it is a Read Only field, like a label.
  • If in the Form I insert a row and fill some basic fields, then I don?t need to save blanks into this field because this field will be fill when other process run (like a store procedure).
  • I think that is not necessary to validate the field before save, because the User can?t write anything there.

Thanks.
JG

#4

Re: Set Null a field to INSERT into DB

I will have to test this after I reinstall SC in my VM.

If SC is deciding to add “” into the field instead of NULL, you can try:

onBeforeInsert:
{fieldname} = NULL;

or
onAfterInsert:
sc_exec_sql(‘UPDATE … WHERE …’) to set field to null

I would report this as a potential bug, as a NULL field should not be changed to “” when the field was not edited.

Regards,
Scott.

#5

Re: Set Null a field to INSERT into DB

Thanks Scott.
I?m going to try with the command {fieldname} = NULL; into the onBeforeInsert event.

thanks again.
JG

#6

Re: Set Null a field to INSERT into DB

Hi Scott.
Sorry, but I had try using {fieldname} = NULL; onBeforeInsert event and using $var = Null; {fieldname} = $var; but nothing to assign Null to the field when the INSERT command goes to save the data form.
Maybe this a SC bug.

Do you have another idea about this?

Thanks
JG

#7

Re: Set Null a field to INSERT into DB

No thoughts right away. I am not sure why your form is touching a NULL field with a blank. Is it readonly field, or can you make it a field label to where submit will not try to update the field?
The other option is what I mention of using UPDATE to null out the field onAfterInsert/onAfterUpdate.

Regards,
Scott.

#8

Re: Set Null a field to INSERT into DB

Hi Scott.
The field has Label property.
About Update the field after Insert/update isn`t possible, because when try insert a new data record the foreing key constraint shows error with blanks data ("") at the INSERT command.

Rgds
JG

#9

Re: Set Null a field to INSERT into DB

I think this is a SCBug too. Empty Textfields was stored as ‘’ by SC.

Try This to store NULL in Textfields in onAfterInsert or :

Set textfield as maagend by the Database
Then:
if (empty({textfield}) OR {textfield} = ‘’)
{
$textfield= NULL;
}
ELSE
{
$textfield = {textfield};
$textfield = “’$textfield’”
}
if (!empty({{primarykeyfield})
{
$sql = "UPDATE table SET column = $textfield WHERE primarykeyfield = ". {primarykeyfield};
sc_exec_sql($sql);
}

#10

Re: Set Null a field to INSERT into DB

I can?t do this because the problem is when I try to insert a NEW record, using a Form and after I press the Save button (SC save button). When I do that then SC sends this command to DB: INSERT INTO TableX (Field1, Field2,…) VALUES ( ‘abcd’, ‘’, …), being Field2 a Label Type field into the Form with NULL property at DB and with a Foreing key to another table.

Rgds
JG

#11

Re: Set Null a field to INSERT into DB

does the field have a default value in the form, or in the database?

If it’s a SC bug, maybe a database trigger is a workaround?

delimiter $$
CREATE TRIGGER triggernamef BEFORE INSERT ON tablename
 FOR EACH ROW BEGIN
  If new.columnname = ''
  then set new.columnname = NULL; 
 END;
$$

(or BEFORE UPDATE ON...)
#12

Re: Set Null a field to INSERT into DB

Yes, the trigger method of freezer goes. Is probably even better than the release in SC (PHP).

#13

Re: Set Null a field to INSERT into DB

Thanks all for your help. It?s fixed now.

JG