On 10 January 2017 at 11:10, Akalanka Pagoda Arachchi <darsha...@wso2.com>
wrote:
> Replacing an empty string with a space is generally a bad practice in
> database perspective due to few reasons.
>
> 1. Issues with visibility (a DBA cannot tell the difference by directly
> looking at it)
> 2. As Madhawa pointed out, space is a different character and when we
> really want to represent a space in the column and differentiate between
> the empty and the space, it will be impossible.
> 3. Adds processing complexity
>
> The suggestion to use a space for empty strings is actually to denote an
> empty string by a special character since empty string is not supported in
> Oracle. Therefore instead of using a meaningless space character which
> introduces more complexity why not use a special string such as NULL or
> EMPTY?
>
These are good points, but by using a special string to represent an empty
string we don't avoid complexity to the code because we need to check the
value of what has been read from the DB and explicitly replace that with an
empty string before sending back to the client. This logic also breaks if
the user by chance sends that special string explicitly.
We have not found an elegant way of doing this that balances all sides. Its
a pity Oracle deviates from the standard practice of other DBs in this
regard.
>
> Thanks,
> Akalanka.
>
> On Tue, Jan 10, 2017 at 11:01 AM, Madhawa Gunasekara <madha...@wso2.com>
> wrote:
>
>> So what will happen if the user sends a space? So It's better to add a
>> configuration to avoid these situations. then user can handle this. WDYT ?
>>
>> On Tue, Jan 10, 2017 at 10:54 AM, Uvindra Dias Jayasinha <
>> uvin...@wso2.com> wrote:
>>
>>> To be more precise, if a user explicitly sends "" then we will set the
>>> value to space, enabling us to convert back to "".
>>>
>>> But if the field is not set(ignored by the user) then the default NULL
>>> will be saved. This will make things consistant across all DB's.
>>>
>>> On 10 January 2017 at 10:41, Uvindra Dias Jayasinha <uvin...@wso2.com>
>>> wrote:
>>>
>>>> Note that there is a clear break in the UX of the REST API if we allow
>>>> pass empty strings to Oracle(due to the conversion to NULL). Oracle treats
>>>> "" as NULL but this is incorrect in the REST/JSON world.
>>>>
>>>> If a user enters an empty string "" they will expect to get "" back,
>>>> which will not happen with Oracles default behaviour. Therefore in order to
>>>> keep consistency of the REST API I dont see an alternative other than
>>>> having space as the default value. We can get rid of the space when
>>>> returning by simply trimming the String so we don't need to have any
>>>> special filtering logic.
>>>>
>>>> On 10 January 2017 at 10:34, Akalanka Pagoda Arachchi <
>>>> darsha...@wso2.com> wrote:
>>>>
>>>>> +1 to keep default as NULL instead of a space.
>>>>>
>>>>> Having a space will require adding trimming logic to the underlying
>>>>> code and methods like 'isNullOrEmpty' will bypass this string if there's a
>>>>> space.
>>>>>
>>>>> Thanks,
>>>>> Akalanka.
>>>>>
>>>>> On Tue, Jan 10, 2017 at 10:23 AM, Lahiru Cooray <lahi...@wso2.com>
>>>>> wrote:
>>>>>
>>>>>>
>>>>>>
>>>>>> On Mon, Jan 9, 2017 at 7:54 AM, Isuru Haththotuwa <isu...@wso2.com>
>>>>>> wrote:
>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> On Fri, Jan 6, 2017 at 5:19 PM, Uvindra Dias Jayasinha <
>>>>>>> uvin...@wso2.com> wrote:
>>>>>>>
>>>>>>>> Setting a default value for empty fields being sent seems to be
>>>>>>>> best.
>>>>>>>>
>>>>>>>> Furthermore we can have default values set in our DTO objects in
>>>>>>>> case a given field is omitted altogether.
>>>>>>>>
>>>>>>>> So handling both the above scenarios can overcome the limitation in
>>>>>>>> Oracle.
>>>>>>>>
>>>>>>>> But I dont know if this is good for the REST API user experience,
>>>>>>>> when retrieving data that contains these default values.
>>>>>>>>
>>>>>>> Additionally this could affect the user experience in UIs as well.
>>>>>>>
>>>>>>> How about we just keep a default value to NULL in DB level and then
>>>>>>> filter it from UI? Since anyway Oracle treats zero length String as
>>>>>>> NULLs,
>>>>>>> even if the user enters an empty String it will then be automatically.
>>>>>>> The
>>>>>>> rest API will still return the default value if invoked directly though.
>>>>>>>
>>>>>>
>>>>>> +1 to keep default as NULL which is more natural
>>>>>> Further rather than filtering in the UI, how about directly do it in
>>>>>> the query itself using COALESCE() built-in function (which is an ANSI
>>>>>> standard and better performing than IS NULL)
>>>>>>
>>>>>> eg: SELECT COALESCE(field_name,'') as field_name //if the field
>>>>>> value is null it will map to empty
>>>>>>
>>>>>>
>>>>>>
>>>>>>>> On 6 January 2017 at 15:28, Tharindu Dharmarathna <
>>>>>>>> tharin...@wso2.com> wrote:
>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> On Fri, Jan 6, 2017 at 3:26 PM, Tharindu Dharmarathna <
>>>>>>>>> tharin...@wso2.com> wrote:
>>>>>>>>>
>>>>>>>>>> Hi All,
>>>>>>>>>>
>>>>>>>>>> I faced $Subject in Oracle database while running integration
>>>>>>>>>> test on C5 on top.
>>>>>>>>>>
>>>>>>>>>> *Observation*
>>>>>>>>>>
>>>>>>>>>> when insert empty string ("") it was save as null in database.
>>>>>>>>>>
>>>>>>>>>> While going through SO I had found [1] , which did happen in
>>>>>>>>>> oracle database.
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> We have come up with several ways to handle empty strings which
>>>>>>>>>> user sends through the rest api.
>>>>>>>>>>
>>>>>>>>>> 1. Validate the request and send error when giving empty strings
>>>>>>>>>> 2. Set default value like "N/A" into the fields which send as
>>>>>>>>>> empty.
>>>>>>>>>>
>>>>>>>>>> Is there any other way to handle this problem ?.
>>>>>>>>>>
>>>>>>>>>> [1] - http://stackoverflow.com/questions/13278773/null-vs-empty-
>>>>>>>>>> string-in-oracle
>>>>>>>>>>
>>>>>>>>>> Thanks
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> *Tharindu Dharmarathna*Software Engineer
>>>>>>>>>> WSO2 Inc.; http://wso2.com
>>>>>>>>>> lean.enterprise.middleware
>>>>>>>>>>
>>>>>>>>>> mobile: *+94779109091 <+94%2077%20910%209091>*
>>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> --
>>>>>>>>>
>>>>>>>>> *Tharindu Dharmarathna*Software Engineer
>>>>>>>>> WSO2 Inc.; http://wso2.com
>>>>>>>>> lean.enterprise.middleware
>>>>>>>>>
>>>>>>>>> mobile: *+94779109091 <+94%2077%20910%209091>*
>>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> --
>>>>>>>> Regards,
>>>>>>>> Uvindra
>>>>>>>>
>>>>>>>> Mobile: 777733962
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> --
>>>>>>> Thanks and Regards,
>>>>>>>
>>>>>>> Isuru H.
>>>>>>> +94 716 358 048 <+94%2071%20635%208048>* <http://wso2.com/>*
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> _______________________________________________
>>>>>>> Dev mailing list
>>>>>>> Dev@wso2.org
>>>>>>> http://wso2.org/cgi-bin/mailman/listinfo/dev
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>> --
>>>>>> *Lahiru Cooray*
>>>>>> Software Engineer
>>>>>> WSO2, Inc.;http://wso2.com/
>>>>>> lean.enterprise.middleware
>>>>>>
>>>>>> Mobile: +94 715 654154 <+94%2071%20565%204154>
>>>>>>
>>>>>> _______________________________________________
>>>>>> Dev mailing list
>>>>>> Dev@wso2.org
>>>>>> http://wso2.org/cgi-bin/mailman/listinfo/dev
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> *Darshana Akalanka Pagoda Arachchi,*
>>>>> *Senior Software Engineer, WSO2*
>>>>> *+94777118016 <+94%2077%20711%208016>*
>>>>>
>>>>> _______________________________________________
>>>>> Dev mailing list
>>>>> Dev@wso2.org
>>>>> http://wso2.org/cgi-bin/mailman/listinfo/dev
>>>>>
>>>>>
>>>>
>>>>
>>>> --
>>>> Regards,
>>>> Uvindra
>>>>
>>>> Mobile: 777733962
>>>>
>>>
>>>
>>>
>>> --
>>> Regards,
>>> Uvindra
>>>
>>> Mobile: 777733962
>>>
>>> _______________________________________________
>>> Dev mailing list
>>> Dev@wso2.org
>>> http://wso2.org/cgi-bin/mailman/listinfo/dev
>>>
>>>
>>
>>
>> --
>> *Madhawa Gunasekara*
>> Software Engineer
>> WSO2 Inc.; http://wso2.com
>> lean.enterprise.middleware
>>
>> mobile: +94 719411002 <+94+719411002>
>> blog: *http://madhawa-gunasekara.blogspot.com
>> <http://madhawa-gunasekara.blogspot.com>*
>> linkedin: *http://lk.linkedin.com/in/mgunasekara
>> <http://lk.linkedin.com/in/mgunasekara>*
>>
>
>
>
> --
> *Darshana Akalanka Pagoda Arachchi,*
> *Senior Software Engineer, WSO2*
> *+94777118016 <+94%2077%20711%208016>*
>
--
Regards,
Uvindra
Mobile: 777733962
_______________________________________________
Dev mailing list
Dev@wso2.org
http://wso2.org/cgi-bin/mailman/listinfo/dev
