Adding operations that are based on SQL scripts

You can create operations that run any SQL statement that is supported by your database.

About this task

Restrictions:
  • You can include only one query per operation. Namespaces are fully supported for both SQL/XML and XQuery
  • Each operation within a Web service must have a unique name.

In the SQL statements, you can use all data types that are supported by JDBC 3.0, except for ARRAY, DISTINCT, JAVA_OBJECT, OTHER, REF, and STRUCT. You can also use the DB2® XML data type and the ROWID data type that DB2 for z/OS® supports.

Use named parameters or positional parameters in WHERE clauses and statements that accept values.

Named parameters
There are two advantages to using named parameters:
  • Names make the default XML schema more verbose and can describe the meaning of the parameter to the client applications.
  • You can assign a parameter to more than one host variable, which is useful for UNION ALL views.
Named parameters begin with a colon and are typically named after their corresponding host variable, as in this example where :empno is the parameter and empno is the host variable:
SELECT * FROM employee where empno=:empno
In the resulting XML schema, the entry for the parameter might look like this:
<element name="empno" type="xsd:string"/>
The names are case sensitive and must be valid XML tag names. If the name of a parameter contains characters or character sequences that are not legal in XML, the workbench automatically applies SQL/XML escaping rules to the name. For example, a parameter called xml is transformed to _xFFFF_xml. So, if you were to use this parameter as an input parameter in an HTTP GET request, the URL would have to look similar to this example:
http://localhost:8080/ContextRoot/rest/MyService?_xFFFF_xml=1234
Positional parameters
These parameters are question marks that represent host variables. Values are assigned by position rather than names. This INSERT statement makes use of positional parameters:
INSERT INTO employee VALUES (?, ?, ?, ?)
In the resulting XML schema, the entries for the parameters might look like this:
<element name="p1" type="xsd:int"/>
<element name="p2" type="xsd:string"/>
<element name="p3" type="xsd:string"/>
<element name="p4" type="xsd:anyType" nillable="true"/>

Procedure

To add to a Web service an operation that is based on an SQL script:


Feedback