Client transforms

Client transforms exchange structured types with client application programs.

For example, assume that you want to run the following SQL statement:

   ...
   SQL TYPE IS Address_t AS VARCHAR(150) addhv;
   ...

   EXEC SQL SELECT Address
      FROM Person
      INTO :addhv
      WHERE AGE > 25
   END EXEC;
Figure 1 shows the process of binding out that address to the client program.
Figure 1. Binding out a structured type to a client application
The diagram shows the 3 steps involved in binding out a structured type to a client.
  1. The object must first be passed to the FROM SQL function transform to decompose the object into its base type attributes.
  2. Your FROM SQL client transform must encode the value into a single built-in type, such as a VARCHAR or BLOB. This enables the client program to receive the entire value in a single host variable.

    This encoding can be as simple as copying the attributes into a contiguous area of storage (providing for required alignments as necessary). Because the encoding and decoding of attributes cannot generally be achieved with SQL, client transforms are usually written as external UDFs.

  3. The client program processes the value.
Figure 2 shows the reverse process of passing the address back to the database.
Figure 2. Binding in a structured type from a client
This diagram shows the steps taken on the client and the server to bind in a structured type from a client.
  1. The client application encodes the address into a format expected by the TO SQL client transform.
  2. The TO SQL client transform decomposes the single built-in type into a set of its base type attributes, which is used as input to the TO SQL function transform.
  3. The TO SQL function transform constructs the address and returns it to the database.

Include the TRANSFORM GROUP clause to tell Db2® which set of transforms to use in processing the address type in the given function.