Adding operations that are based on SQL scripts

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

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.

About this task:

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:

• If the SQL script already exists in a project in your workspace, in the Data Project Explorer drag the SQL script and drop it onto the Web service. If you drag from one project to another, either of these conditions must be true:

  • Both projects are associated with the same datasource.

  • One project is associated with a snapshot of the database that is associated with the other project.
  Be sure to use either named parameters or positional parameters if the operation requires client applications to pass values.

• If you want to create a new SQL script, create it directly in the

  New Operation wizard. In the wizard, name the operation and create the SQL script to base the operation on. There are two different ways to open the wizard.

  • In the Data Project Explorer, right-click on the Web service that you want to add the operation to. Select

    New Operation. Opening the wizard this way lets you add the operation to one Web service.

  • Select

    File | New | Other. In the

    New wizard, expand Data and select Operation. Opening the wizard this way lets you add the operation to one or more Web services that are defined in a data development project.

  Be sure to use either named parameters or positional parameters if the operation requires client applications to pass values.

 

Related tasks

Adding operations that are based on calls to stored procedures

Developing Web services with the workbench

Related information

SQL editor

---