The SAP Sybase IQ server as a web service client may interface to a web service using a function or a procedure.
A string representation within a result set may suffice for simple return data types. The use of a stored procedure may be warranted in this case.
The use of web service functions are a better choice when returning complex data such as arrays or structures. For function declarations, the RETURN clause can specify an XML data type. The returned XML can be parsed using OPENXML to extract the elements of interest.
A return of XML data such as dateTime is rendered within the result set verbatim. For example, if a TIMESTAMP column was included within a result set, it would be formatted as an XML dateTime string (2006-12-25T12:00:00.000-05:00) not as a string (2006-12-25 12:00:00.000).
The SAP Sybase IQ XML data type is supported for use as a parameter within web service functions and procedures. For simple types, the parameter element is automatically constructed when generating the SOAP request body. However, for XML parameter types, this cannot be done since the XML representation of the element may require attributes that provide additional data. Therefore, when generating the XML for a parameter whose data type is XML, the root element name must correspond to the parameter name.
<inputHexBinary xsi:type="xsd:hexBinary">414243</inputHexBinary>
The XML type demonstrates how to send a parameter as a hexBinary XML type. The SOAP endpoint expects that the parameter name (or in XML terms, the root element name) is "inputHexBinary".
Knowledge of how SAP Sybase IQ references namespaces is required to construct complex structures and arrays. The prefixes listed here correspond to the namespace declarations generated for an SAP Sybase IQ SOAP request envelope.
| SAP Sybase IQ XML Prefix | Namespace |
|---|---|
| xsd | http://www.w3.org/2001/XMLSchema |
| xsi | http://www.w3.org/2001/XMLSchema-instance |
| SOAP-ENC | http://schemas.xmlsoap.org/soap/encoding/ |
| m | namespace as defined in the NAMESPACE clause |
The following three examples demonstrate how to create web service client functions taking parameters that represent an array, a structure, and an array of structures. The web service functions will communicate to SOAP operations (or RPC function names) named echoFloatArray, echoStruct, and echoStructArray respectively. The common namespace used for Interoperability testing is http://soapinterop.org/, allowing a given function to test against alternative Interoperability servers simply by changing the URL clause to the chosen SOAP endpoint.
The examples are designed to issue requests to the Microsoft SOAP ToolKit 3.0 Round 2 Interoperability test server at http://mssoapinterop.org/stkV3.
All the examples use a table to generate the XML data. The following shows how to set up that table.
CREATE LOCAL TEMPORARY TABLE SoapData
(
seqno INT DEFAULT AUTOINCREMENT,
i INT,
f FLOAT,
s LONG VARCHAR
) ON COMMIT PRESERVE ROWS;
INSERT INTO SoapData (i,f,s)
VALUES (99,99.999,'Ninety-Nine');
INSERT INTO SoapData (i,f,s)
VALUES (199,199.999,'Hundred and Ninety-Nine');
The following three functions send SOAP requests to the Interoperability server. This sample issues requests to the Microsoft Interop server:
CREATE FUNCTION echoFloatArray(inputFloatArray XML) RETURNS XML URL 'http://mssoapinterop.org/stkV3/Interop.wsdl' HEADER 'SOAPAction:"http://soapinterop.org/"' NAMESPACE 'http://soapinterop.org/'; CREATE FUNCTION echoStruct(inputStruct XML) RETURNS XML URL 'http://mssoapinterop.org/stkV3/Interop.wsdl' HEADER 'SOAPAction:"http://soapinterop.org/"' NAMESPACE 'http://soapinterop.org/'; CREATE FUNCTION echoStructArray(inputStructArray XML) RETURNS XML URL 'http://mssoapinterop.org/stkV3/Interop.wsdl' HEADER 'SOAPAction:"http://soapinterop.org/"' NAMESPACE 'http://soapinterop.org/';
Finally, the three example statements along with the XML representation of their parameters are presented:
The parameters in the following example represent an array.
SELECT echoFloatArray(
XMLELEMENT( 'inputFloatArray',
XMLATTRIBUTES( 'xsd:float[2]' as "SOAP-ENC:arrayType" ),
(
SELECT XMLAGG( XMLELEMENT( 'number', f ) ORDER BY seqno )
FROM SoapData
)
)
);
The stored procedure echoFloatArray will send the following XML to the Interoperability server.
<inputFloatArray SOAP-ENC:arrayType="xsd:float[2]"> <number>99.9990005493164</number> <number>199.998992919922</number> </inputFloatArray>
The response from the Interoperability server is shown below.
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<SOAP-ENV:Envelope
xmlns:SOAPSDK1="http://www.w3.org/2001/XMLSchema"
xmlns:SOAPSDK2="http://www.w3.org/2001/XMLSchema-instance"
xmlns:SOAPSDK3="http://schemas.xmlsoap.org/soap/encoding/"
xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">
<SOAP-ENV:Body
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<SOAPSDK4:echoFloatArrayResponse
xmlns:SOAPSDK4="http://soapinterop.org/">
<Result SOAPSDK3:arrayType="SOAPSDK1:float[2]"
SOAPSDK3:offset="[0]"
SOAPSDK2:type="SOAPSDK3:Array">
<SOAPSDK3:float>99.9990005493164</SOAPSDK3:float>
<SOAPSDK3:float>199.998992919922</SOAPSDK3:float>
</Result>
</SOAPSDK4:echoFloatArrayResponse>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>
If the response was stored in a variable, then it can be parsed using OPENXML.
SELECT * FROM OPENXML( resp,'//*:Result/*' ) WITH ( varFloat FLOAT 'text()' );
| varFloat |
|---|
| 99.9990005493 |
| 199.9989929199 |
The parameters in the following example represent a structure.
SELECT echoStruct(
XMLELEMENT('inputStruct',
(
SELECT XMLFOREST( s as varString,
i as varInt,
f as varFloat )
FROM SoapData
WHERE seqno=1
)
)
);
The stored procedure echoStruct will send the following XML to the Interoperability server.
<inputStruct> <varString>Ninety-Nine</varString> <varInt>99</varInt> <varFloat>99.9990005493164</varFloat> </inputStruct>
The response from the Interoperability server is shown below.
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<SOAP-ENV:Envelope
xmlns:SOAPSDK1="http://www.w3.org/2001/XMLSchema"
xmlns:SOAPSDK2="http://www.w3.org/2001/XMLSchema-instance"
xmlns:SOAPSDK3="http://schemas.xmlsoap.org/soap/encoding/"
xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">
<SOAP-ENV:Body
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<SOAPSDK4:echoStructResponse
xmlns:SOAPSDK4="http://soapinterop.org/">
<Result href="#id1"/>
</SOAPSDK4:echoStructResponse>
<SOAPSDK5:SOAPStruct
xmlns:SOAPSDK5="http://soapinterop.org/xsd"
id="id1"
SOAPSDK3:root="0"
SOAPSDK2:type="SOAPSDK5:SOAPStruct">
<varString>Ninety-Nine</varString>
<varInt>99</varInt>
<varFloat>99.9990005493164</varFloat>
</SOAPSDK5:SOAPStruct>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>
If the response was stored in a variable, then it can be parsed using OPENXML.
SELECT * FROM OPENXML( resp,'//*:Body/*:SOAPStruct' ) WITH ( varString LONG VARCHAR 'varString', varInt INT 'varInt', varFloat FLOAT 'varFloat' );
| varString | varInt | varFloat |
|---|---|---|
| Ninety-Nine | 99 | 99.9990005493 |
The parameters in the following example represent an array of structures.
SELECT echoStructArray(
XMLELEMENT( 'inputStructArray',
XMLATTRIBUTES( 'http://soapinterop.org/xsd' AS "xmlns:q2",
'q2:SOAPStruct[2]' AS "SOAP-ENC:arrayType" ),
(
SELECT XMLAGG(
XMLElement('q2:SOAPStruct',
XMLFOREST( s as varString,
i as varInt,
f as varFloat )
)
ORDER BY seqno
)
FROM SoapData
)
)
);
The stored procedure echoFloatArray will send the following XML to the Interoperability server.
<inputStructArray xmlns:q2="http://soapinterop.org/xsd"
SOAP-ENC:arrayType="q2:SOAPStruct[2]">
<q2:SOAPStruct>
<varString>Ninety-Nine</varString>
<varInt>99</varInt>
<varFloat>99.9990005493164</varFloat>
</q2:SOAPStruct>
<q2:SOAPStruct>
<varString>Hundred and Ninety-Nine</varString>
<varInt>199</varInt>
<varFloat>199.998992919922</varFloat>
</q2:SOAPStruct>
</inputStructArray>
The response from the Interoperability server is shown below.
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<SOAP-ENV:Envelope
xmlns:SOAPSDK1="http://www.w3.org/2001/XMLSchema"
xmlns:SOAPSDK2="http://www.w3.org/2001/XMLSchema-instance"
xmlns:SOAPSDK3="http://schemas.xmlsoap.org/soap/encoding/"
xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">
<SOAP-ENV:Body
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<SOAPSDK4:echoStructArrayResponse
xmlns:SOAPSDK4="http://soapinterop.org/">
<Result xmlns:SOAPSDK5="http://soapinterop.org/xsd"
SOAPSDK3:arrayType="SOAPSDK5:SOAPStruct[2]"
SOAPSDK3:offset="[0]" SOAPSDK2:type="SOAPSDK3:Array">
<SOAPSDK5:SOAPStruct href="#id1"/>
<SOAPSDK5:SOAPStruct href="#id2"/>
</Result>
</SOAPSDK4:echoStructArrayResponse>
<SOAPSDK6:SOAPStruct
xmlns:SOAPSDK6="http://soapinterop.org/xsd"
id="id1"
SOAPSDK3:root="0"
SOAPSDK2:type="SOAPSDK6:SOAPStruct">
<varString>Ninety-Nine</varString>
<varInt>99</varInt>
<varFloat>99.9990005493164</varFloat>
</SOAPSDK6:SOAPStruct>
<SOAPSDK7:SOAPStruct
xmlns:SOAPSDK7="http://soapinterop.org/xsd"
id="id2"
SOAPSDK3:root="0"
SOAPSDK2:type="SOAPSDK7:SOAPStruct">
<varString>Hundred and Ninety-Nine</varString>
<varInt>199</varInt>
<varFloat>199.998992919922</varFloat>
</SOAPSDK7:SOAPStruct>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>
If the response was stored in a variable, then it can be parsed using OPENXML.
SELECT * FROM OPENXML( resp,'//*:Body/*:SOAPStruct' ) WITH ( varString LONG VARCHAR 'varString', varInt INT 'varInt', varFloat FLOAT 'varFloat' );
| varString | varInt | varFloat |
|---|---|---|
| Ninety-Nine | 99 | 99.9990005493 |
| Hundred and Ninety-Nine | 199 | 199.9989929199 |