How WSO2DSS manage the XML mapping of queries which contains some columns with null value
I have a simple query 

    select t.firstname, t.lastname, t.zipcode, t.city 
    from name_data t where name_id = ?

The '?' is the input parameter and is never null. 

The query returns a record looking like one of these:

Case 1 - Individual

     'First Name' 	'Last Name' 	'zipcode' 	'city'
	 -------------------------------------------------------
	 John			Doe				1177		Somewhere

Case 2 - Company

     'First Name' 	'Last Name' 	'zipcode' 	'city'
	 -----------------------------------------------------------
	 <<null>>		ACME Ltd		1199		'Somewhere else'

	 
When I am getting the second record, The DSS throw a NullPointerException.

I guess this is because the <<null>> value in Firstname and I found a way to bypass this by testing the null value in the SQL (Oracle)

"select nvl(t.firstname, ' ') firstname, nvl(t.lastname, ' '), lastname, nvl(t.zipcode, ' '), nvl(t.city, ' ') etc..."

And this for all columns which can be null (my query is a bit longer than this) as they are optionnal.

I know this should not happens (in theory) if the database was correctly designed (like create two different entities for managing people and companies)
but it is a commercial product and then I just cannot change it and I took a simple case to explain instead a long query.

The extract of my .dbs is: 

    <?xml version="1.0" encoding="UTF-8"?>
    <data xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
    	xsi:noNamespaceSchemaLocation="http:///org/wso2/carbonstudio/eclipse/ds" 
    	name="${groupId}.${artifactId}-customeraddress-1" 
    	serviceNamespace="urn:customeraddress.database.mycompany.org/1" 
    	serviceGroup="${groupId}" baseURI="">
      <description>This is the datasource to access the customer address information</description>
      <config id="ambocs">
        <property name="org.wso2.ws.dataservice.driver">@ambocs.driver@</property>
        <property name="org.wso2.ws.dataservice.protocol">@ambocs.url@</property>
        <property name="org.wso2.ws.dataservice.user">@ambocs.user@</property>
        <property name="org.wso2.ws.dataservice.password">@ambocs.password@</property>
        <property name="org.wso2.ws.dataservice.minpoolsize">@ambocs.minpoolsize@</property>
        <property name="org.wso2.ws.dataservice.maxpoolsize">@ambocs.maxpoolsize@</property>
        <property name="org.wso2.ws.dataservice.validation_query">select 1 from dual</property>
      </config>
      <query id="getCustomerAddressByIdRef" useConfig="ambocs" returnRowId="true">
        <sql>
    		select 
    			nvl(nd.first_name, ' ') first_name, 
    			nvl(nd.last_name, ' ') last_name,
    			nvl(nd.adr_zip, ' ') zipcode,
    			nvl(nd.adr_city, ' ') city, 
    			nvl(nd.adr_country, ' ') country 
    		from name_data nd
    		where nd.name_id = ?
    	</sql>
        <properties>
          <property name="org.wso2.ws.dataservice.query_timeout"></property>
          <property name="org.wso2.ws.dataservice.fetch_direction"></property>
          <property name="org.wso2.ws.dataservice.fetch_size"></property>
          <property name="org.wso2.ws.dataservice.max_field_size"></property>
          <property name="org.wso2.ws.dataservice.max_rows"></property>
        </properties>
        <result element="ContactDetails" rowName="ContactDetail" 
    	   defaultNamespace="urn:customeraddress.database.mycompany.org/1" outputType="xml">
          <attribute name="firstName" column="first_name" query-param="" requiredRoles="" xsdType="xs:string"/>
          <attribute name="lastName" column="last_business_name" query-param="" requiredRoles="" xsdType="xs:string"/>
          <attribute name="locality" column="city" query-param="" requiredRoles="" xsdType="xs:string"/>
          <attribute name="postcode" column="zipcode" query-param="" requiredRoles="" xsdType="xs:string"/>
          <attribute name="country" column="country" query-param="" requiredRoles="" xsdType="xs:string"/>      
        </result>
        <param name="nameId" type="IN" sqlType="STRING" defaultValue="" />
      </query>
      <operation name="getCustomerAddressById">
        <call-query href="getCustomerAddressByIdRef">
    	    <with-param name="nameId" column="nameId" query-param="nameId"/>
        </call-query>
      </operation>
    </data>

Do you know if there is an option to put in the .dbs in order to manage the null value returned by the query? 

I tried to find a documentation on the wso2.org but it is very simple examples and nothing about this case.





How WSO2DSS manage the XML mapping of queries which contains some columns with null value
===

I have a simple query 

    select t.firstname, t.lastname, t.zipcode, t.city 
    from name_data t where name_id = ?

The '?' is the input parameter and is never null. 

The query returns a record looking like one of these:

Case 1 - Individual

     'First Name' 	'Last Name' 	'zipcode' 	'city'
	 -------------------------------------------------------
	 John			Doe				1177		Somewhere

Case 2 - Company

     'First Name' 	'Last Name' 	'zipcode' 	'city'
	 -----------------------------------------------------------
	 <<null>>		ACME Ltd		1199		'Somewhere else'

	 
When I am getting the second record, The DSS throw a NullPointerException.

I guess this is because the <<null>> value in Firstname and I found a way to bypass this by testing the null value in the SQL (Oracle)

"select nvl(t.firstname, ' ') firstname, nvl(t.lastname, ' '), lastname, nvl(t.zipcode, ' '), nvl(t.city, ' ') etc..."

And this for all columns which can be null (my query is a bit longer than this) as they are optionnal.

I know this should not happens (in theory) if the database was correctly designed (like create two different entities for managing people and companies)
but it is a commercial product and then I just cannot change it and I took a simple case to explain instead a long query.

The extract of my .dbs is: 

    <?xml version="1.0" encoding="UTF-8"?>
    <data xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
    	xsi:noNamespaceSchemaLocation="http:///org/wso2/carbonstudio/eclipse/ds" 
    	name="${groupId}.${artifactId}-customeraddress-1" 
    	serviceNamespace="urn:customeraddress.database.mycompany.org/1" 
    	serviceGroup="${groupId}" baseURI="">
      <description>This is the datasource to access the customer address information</description>
      <config id="ambocs">
        <property name="org.wso2.ws.dataservice.driver">@ambocs.driver@</property>
        <property name="org.wso2.ws.dataservice.protocol">@ambocs.url@</property>
        <property name="org.wso2.ws.dataservice.user">@ambocs.user@</property>
        <property name="org.wso2.ws.dataservice.password">@ambocs.password@</property>
        <property name="org.wso2.ws.dataservice.minpoolsize">@ambocs.minpoolsize@</property>
        <property name="org.wso2.ws.dataservice.maxpoolsize">@ambocs.maxpoolsize@</property>
        <property name="org.wso2.ws.dataservice.validation_query">select 1 from dual</property>
      </config>
      <query id="getCustomerAddressByIdRef" useConfig="ambocs" returnRowId="true">
        <sql>
    		select 
    			nvl(nd.first_name, ' ') first_name, 
    			nvl(nd.last_name, ' ') last_name,
    			nvl(nd.adr_zip, ' ') zipcode,
    			nvl(nd.adr_city, ' ') city, 
    			nvl(nd.adr_country, ' ') country 
    		from name_data nd
    		where nd.name_id = ?
    	</sql>
        <properties>
          <property name="org.wso2.ws.dataservice.query_timeout"></property>
          <property name="org.wso2.ws.dataservice.fetch_direction"></property>
          <property name="org.wso2.ws.dataservice.fetch_size"></property>
          <property name="org.wso2.ws.dataservice.max_field_size"></property>
          <property name="org.wso2.ws.dataservice.max_rows"></property>
        </properties>
        <result element="ContactDetails" rowName="ContactDetail" 
    	   defaultNamespace="urn:customeraddress.database.mycompany.org/1" outputType="xml">
          <attribute name="firstName" column="first_name" query-param="" requiredRoles="" xsdType="xs:string"/>
          <attribute name="lastName" column="last_business_name" query-param="" requiredRoles="" xsdType="xs:string"/>
          <attribute name="locality" column="city" query-param="" requiredRoles="" xsdType="xs:string"/>
          <attribute name="postcode" column="zipcode" query-param="" requiredRoles="" xsdType="xs:string"/>
          <attribute name="country" column="country" query-param="" requiredRoles="" xsdType="xs:string"/>      
        </result>
        <param name="nameId" type="IN" sqlType="STRING" defaultValue="" />
      </query>
      <operation name="getCustomerAddressById">
        <call-query href="getCustomerAddressByIdRef">
    	    <with-param name="nameId" column="nameId" query-param="nameId"/>
        </call-query>
      </operation>
    </data>

Do you know if there is an option to put in the .dbs in order to manage the null value returned by the query? 

I tried to find a documentation on the wso2.org but it is very simple examples and nothing about this case.




