Null value problem with "Update" function

Null value problem with "Update" function

  

Hello,

I am using "Update" function from entity to update records into my Oracle table.

I have 82 attributes in my entity. When I create record first time, all 82 fields will have null value but if I edit some fields and update, rest columns having null value are being replaced by space. This happens when I use Update function. To overcome this, I need to use Advance Query for update operation, pass all 82 parameters and trim those 82 parameters inside query.

Please advice how I can avoid this.


Thanks and Regards,

Suraj Borade

The expected behavior is that they shouldn't have a space, they should have an empty string.


J.Ja

Hi Justin,

Thanks.

Yes there is an empty string.

But why is it happening?

I don't want my null values to be replaced by empty string because application developed in OutSystems is being used by Informatica and Informatica needs null values from Oracle and not empty string because of null values are being replaced by empty string, Informatica is giving an error.

Please advice.

Thanks and Regards,

Suraj Borade

Justin James wrote:

The expected behavior is that they shouldn't have a space, they should have an empty string.


J.Ja

er, no?

In Oracle it will be a space.

http://www.outsystems.com/help/servicestudio/9.1/index.htm#t=Using_Data%2FDefault_Values_on_database.htm


and about null versus empty string.

there is a highly heated discussion about null versus empty-string. which both have advantages and disadvantages. OutSystems decided a long time ago to join the empty-string camp.


Hi J,

I could see it as a space but Justin referred it as a string so I though it might be a string.

When I use Advance Query for update operation and use trim to input parameters, values in oracle are null but when I use update function, values in oracle are space.

Thanks and Regards,

Suraj Borade

Hi Suraj,


I haven't tried it, but maybe you could treat the OutSystems entity as an external entity, and then use "Default Value Behavior" http://www.outsystems.com/help/IntegrationStudio/9.1/index.htm#t=Managing_Extensions%2FEntity_Properties.htm to make sure the Update action leaves NULLs.


I'm not sure if this is easier than what you were doing...


Joao

did you set updated behaviour to changed attributes only?

Hi J,

I am accessing this table through extension and Update behavior is set to All Attributes.

Can we change update behavior of entities referenced through extension?

Hi Suraj,


Check João Santos reply. Pretty sure that it's what you need.


Regards,
João Rosado

Hi Suraj Borade, Have you checked the default value of these attributes, it might be space, i have faced the same problem and it got fixed when i changed the default value of these attributes.



Thanks 
Rajendra Singh

Hi Joao Santos and Joao Rosado,

Thanks for the replies. 

As per that link by Joao Santos, at the time of retrieving value from oracle, null value must be retrieved as a space.( Default Value ) but our downstream application (developed in Informatica) needs 'null' datatype instead of space. 

Whenever I am writing into Oracle from OutSystems application, empty values are being converted into space and it is not accepted by Informatica application.

I managed this through Advance Query. Now if there is any empty attribute, I have applied trim function which will convert space into null datatype.

@Rajendra: There is no default constraint on my attributes.

Thanks  a lot guys.

Best Regards,

Suraj Borade

Hi Suraj,


I have the impression you didn't fully understand our suggestions.

Let me try again.

When you are writing to an entity defined in an extension, and set "Default Value Behavior" to "Convert to/from Null Value in Database"...

1 - Whenever you are writing into Oracle from OutSystems application, attributes with the OutSystems default value are converted to NULL in the database.


So, if your empty value is converted into a space when you write into Oracle from OutSystems application, that could mean one of the following:

- your "empty value" is not the default value for the attribute;

- you didn't set the "Default Value Behavior" correctly, for the external entity you are writing to.


Joao

Hi Joao,

I don't have knowledge how entities are created through extension, it is created by some other person who sits in USA.

But from you your answer, I come to know that at the time of defining entity in extension I need to set "Default Value Behavior" to "Convert to/from Null Value in Database" for external entity. Correct?

Thanks and Regards,

Suraj Borade

Hi Suraj,


Yes, the "Default Value Behavior" is set in Integration Studio, which is the same application where you create or update the extension where you define the connection to the external entity,


Joao

Hi Joao,

Thanks.

After setting "Default Value Behavior" in Integration Studio, can I use update function in Service Studio which will set empty values as null and not space?

Thanks and Regards,

Suraj Borade

Yes it should.

Just try it and tell us if it worked for you.


Regards

João Rosado