Working with data types

By default, the XML encoding of parameter input is string and the result set output for SOAP service formats contains no information that specifically describes the data type of the columns in the result set. For all formats, parameter data types are string. For the DNET format, within the schema section of the response, all columns are typed as string. CONCRETE and XML formats contain no data type information in the response. This default behavior can be manipulated using the DATATYPE clause.

SQL Anywhere enables data typing using the DATATYPE clause. Data type information can be included in the XML encoding of parameter input and result set output or responses for all SOAP service formats. This simplifies parameter passing from SOAP toolkits by not requiring client code to explicitly convert parameters to Strings. For example, an integer can be passed as an int. XML encoded data types enable a SOAP toolkit to parse and cast the data to the appropriate type.

When using string data types exclusively, the application needs to implicitly know the data type for every column within the result set. This is not necessary when data typing is requested of the web server. To control whether data type information is included, the DATATYPE clause can be used when the web service is defined.

DATATYPE { OFF | ON | IN | OUT }
  • OFF   For DNET output format, SQL Anywhere data types are translated to and from XML Schema string types. For CONCRETE and XML formats, no data type information is emitted.

  • ON   This is the default behavior when the DATATYPE option is not used. Data type information is emitted for both input parameters and result set responses. SQL Anywhere data types are translated to and from XML Schema data types.

  • IN   Data type information is emitted for input parameters only.

  • OUT   Data type information is emitted for result set responses only.

Here is an example of a web service definition that enlists data typing for the result set response.

CREATE SERVICE "SASoapTest/EmployeeList"
TYPE 'SOAP'
AUTHORIZATION OFF
SECURE OFF
USER DBA
DATATYPE OUT
AS SELECT * FROM Employees;

In this example, data type information is requested for result set responses only since this service does not have parameters.

Data typing is applicable to all SQL Anywhere web services defined as type 'SOAP'.

Data typing of input parameters

Data typing of input parameters is supported by simply exposing the parameter data types as their true data types in the WSDL generated by the DISH service.

A typical string parameter definition (or a non-typed parameter) would look like the following:

<s:element minOccurs="0" maxOccurs="1" name="a_varchar" nillable="true" type="s:string" />

The String parameter may be nillable, that is, it may or may not occur.

For a typed parameter such as an integer, the parameter must occur and is not nillable. The following is an example.

<s:element minOccurs="1" maxOccurs="1" name="an_int" nillable="false" type="s:int" />
Data typing of output parameters

All SQL Anywhere web services of type 'SOAP' may expose data type information within the response data. The data types are exposed as attributes within the rowset column element.

The following is an example of a typed SimpleDataSet response from a SOAP FORMAT 'CONCRETE' web service.

<SOAP-ENV:Body>
  <tns:test_types_concrete_onResponse>
   <tns:test_types_concrete_onResult xsi:type='tns:SimpleDataset'>
    <tns:rowset>
      <tns:row>
       <tns:lvc xsi:type="xsd:string">Hello World</tns:lvc>
       <tns:i xsi:type="xsd:int">99</tns:i>
       <tns:ii xsi:type="xsd:long">99999999</tns:ii>
       <tns:f xsi:type="xsd:float">3.25</tns:f>
       <tns:d xsi:type="xsd:double">.555555555555555582</tns:d>
       <tns:bin xsi:type="xsd:base64Binary">AAAAZg==</tns:bin>
       <tns:date xsi:type="xsd:date">2006-05-29-04:00</tns:date>
      </tns:row>
     </tns:rowset>
   </tns:test_types_concrete_onResult>
  <tns:sqlcode>0</tns:sqlcode>
  </tns:test_types_concrete_onResponse>
</SOAP-ENV:Body>

The following is an example of a response from a SOAP FORMAT 'XML' web service returning the XML data as a string. The interior rowset consists of encoded XML and is presented here in its decoded form for legibility.

<SOAP-ENV:Body>
 <tns:test_types_XML_onResponse>
  <tns:test_types_XML_onResult xsi:type='xsd:string'>
   <tns:rowset 
     xmlns:tns="http://localhost/satest/dish" 
     xmlns:xsd="http://www.w3.org/2001/XMLSchema">
     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <tns:row>
     <tns:lvc xsi:type="xsd:string">Hello World</tns:lvc>
     <tns:i xsi:type="xsd:int">99</tns:i>
     <tns:ii xsi:type="xsd:long">99999999</tns:ii>
     <tns:f xsi:type="xsd:float">3.25</tns:f>
     <tns:d xsi:type="xsd:double">.555555555555555582</tns:d>
     <tns:bin xsi:type="xsd:base64Binary">AAAAZg==</tns:bin>
     <tns:date xsi:type="xsd:date">2006-05-29-04:00</tns:date>
    </tns:row>
   </tns:rowset>
  </tns:test_types_XML_onResult>
  <tns:sqlcode>0</tns:sqlcode>
 </tns:test_types_XML_onResponse>
</SOAP-ENV:Body>

Note that, in addition to the data type information, the namespace for the elements and the XML schema provides all the information necessary for post processing by an XML parser. When no data type information exists in the result set (DATATYPE OFF or IN) then the xsi:type and the XML schema namespace declarations are omitted.

An example of a SOAP FORMAT 'DNET' web service returning a typed SimpleDataSet follows:

<SOAP-ENV:Body>
  <tns:test_types_dnet_outResponse>
   <tns:test_types_dnet_outResult xsi:type='sqlresultstream:SqlRowSet'>
    <xsd:schema id='Schema2' 
       xmlns:xsd='http://www.w3.org/2001/XMLSchema' 
       xmlns:msdata='urn:schemas-microsoft.com:xml-msdata'>
     <xsd:element name='rowset' msdata:IsDataSet='true'>
      <xsd:complexType>
       <xsd:sequence>
        <xsd:element name='row' minOccurs='0' maxOccurs='unbounded'>
         <xsd:complexType>
          <xsd:sequence>
           <xsd:element name='lvc' minOccurs='0' type='xsd:string' />
           <xsd:element name='ub' minOccurs='0' type='xsd:unsignedByte' />
           <xsd:element name='s' minOccurs='0' type='xsd:short' />
           <xsd:element name='us' minOccurs='0' type='xsd:unsignedShort' />
           <xsd:element name='i' minOccurs='0' type='xsd:int' />
           <xsd:element name='ui' minOccurs='0' type='xsd:unsignedInt' />
           <xsd:element name='l' minOccurs='0' type='xsd:long' />
           <xsd:element name='ul' minOccurs='0' type='xsd:unsignedLong' />
           <xsd:element name='f' minOccurs='0' type='xsd:float' />
           <xsd:element name='d' minOccurs='0' type='xsd:double' />
           <xsd:element name='bin' minOccurs='0' type='xsd:base64Binary' />
           <xsd:element name='bool' minOccurs='0' type='xsd:boolean' />
           <xsd:element name='num' minOccurs='0' type='xsd:decimal' />
           <xsd:element name='dc' minOccurs='0' type='xsd:decimal' />
           <xsd:element name='date' minOccurs='0' type='xsd:date' />
          </xsd:sequence>
         </xsd:complexType>
        </xsd:element>
       </xsd:sequence>
      </xsd:complexType>
     </xsd:element>
    </xsd:schema>
    <diffgr:diffgram xmlns:msdata='urn:schemas-microsoft-com:xml-msdata' xmlns:diffgr='urn:schemas-microsoft-com:xml-diffgram-v1'>
     <rowset>
      <row>
       <lvc>Hello World</lvc>
       <ub>128</ub>
       <s>-99</s>
       <us>33000</us>
       <i>-2147483640</i>
       <ui>4294967295</ui>
       <l>-9223372036854775807</l>
       <ul>18446744073709551615</ul>
       <f>3.25</f>
       <d>.555555555555555582</d>
       <bin>QUJD</bin>
       <bool>1</bool>
       <num>123456.123457</num>
       <dc>-1.756000</dc>
       <date>2006-05-29-04:00</date>
      </row>
     </rowset>
    </diffgr:diffgram>
   </tns:test_types_dnet_outResult>
  <tns:sqlcode>0</tns:sqlcode>
  </tns:test_types_dnet_outResponse>
</SOAP-ENV:Body>
Mapping SQL Anywhere types to XML Schema types
SQL Anywhere type XML Schema type XML Example
CHAR string Hello World
VARCHAR string Hello World
LONG VARCHAR string Hello World
TEXT string Hello World
NCHAR string Hello World
NVARCHAR string Hello World
LONG NVARCHAR string Hello World
NTEXT string Hello World
XML

This is user defined. A parameter is assumed to be valid XML representing a complex type (for example, base64Binary, SOAP array, struct).

<inputHexBinary xsi:type="xsd:hexBinary"> 414243 </inputHexBinary>

(interpreted as 'ABC')

UNIQUEIDENTIFIERSTR string 12345678-1234-5678-9012-123456789012
BIGINT long -9223372036854775807
UNSIGNED BIGINT unsignedLong 18446744073709551615
BIT boolean 1
DECIMAL decimal -1.756000
DOUBLE double .555555555555555582
FLOAT float 12.3456792831420898
INTEGER int -2147483640
UNSIGNED INTEGER unsignedInt 4294967295
NUMERIC decimal 123456.123457
REAL float 3.25
SMALLINT short -99
UNSIGNED SMALLINT unsignedShort 33000
TINYINT unsignedByte 128
MONEY decimal 12345678.9900
SMALLMONEY decimal 12.3400
VARBIT string 11111111
LONG VARBIT string 00000000000000001000000000000000
DATE date 2006-11-21-05:00
DATETIME dateTime 2006-05-21T09:00:00.000-08:00
SMALLDATETIME dateTime 2007-01-15T09:00:00.000-08:00
TIME time 14:14:48.980-05:00
TIMESTAMP dateTime 2007-01-12T21:02:14.420-06:00
BINARY base64Binary AAAAZg==
IMAGE base64Binary AAAAZg==
LONG BINARY base64Binary AAAAZg==
UNIQUEIDENTIFIER string 12345678-1234-5678-9012-123456789012
VARBINARY base64Binary AAAAZg==
Mapping XML Schema types to Java types
XML Schema Type Java Data Type
xsd:string java.lang.String
xsd:integer java.math.BigInteger
xsd:int int
xsd:long long
xsd:short short
xsd:decimal java.math.BigDecimal
xsd:float float
xsd:double double
xsd:boolean boolean
xsd:byte byte
xsd:QName javax.xml.namespace.QName
xsd:dateTime javax.xml.datatype.XMLGregorianCalendar
xsd:base64Binary byte[]
xsd:hexBinary byte[]
xsd:unsignedInt long
xsd:unsignedShort int
xsd:unsignedByte short
xsd:time javax.xml.datatype.XMLGregorianCalendar
xsd:date javax.xml.datatype.XMLGregorianCalendar
xsd:g javax.xml.datatype.XMLGregorianCalendar
xsd:anySimpleType java.lang.Object
xsd:anySimpleType java.lang.String
xsd:duration javax.xml.datatype.Duration
xsd:NOTATION javax.xml.namespace.QName