DB2 Version 9.7 for Linux, UNIX, and Windows

NEXT_ITEM_TYPE function - Return the data type code of the next item

The NEXT_ITEM_TYPE function returns an integer code that identifies the data type of the next data item in a received message.

The received message is stored in the session's local message buffer. Use the UNPACK_MESSAGE procedure to move each item off of the local message buffer, and then use the NEXT_ITEM_TYPE function to return the data type code for the next available item. A code of 0 is returned when there are no more items left in the message.

Syntax


>>-DBMS_PIPE.NEXT_ITEM_TYPE------------------------------------><

Return value

This function returns one of the following codes that represents a data type.

Table 1. NEXT_ITEM_TYPE data type codes
Type code	Data type
0	No more data items
6	INTEGER
9	VARCHAR
12	DATE
23	BLOB

Authorization

EXECUTE privilege on the DBMS_PIPE module.

Example

In proc1, pack and send a message. In proc2, receive the message and then unpack it by using the NEXT_ITEM_TYPE function to determine its type.

SET SERVEROUTPUT ON@ 

CREATE PROCEDURE proc1()
BEGIN
  DECLARE status INT;
  SET status = DBMS_PIPE.CREATE_PIPE( 'pipe1' );
  SET status = DBMS_PIPE.PACK_MESSAGE('message1');
  SET status = DBMS_PIPE.SEND_MESSAGE( 'pipe1' );
END@

CREATE PROCEDURE proc2()
BEGIN
  DECLARE status   INT;
  DECLARE num1     DECFLOAT;
  DECLARE date1    DATE;
  DECLARE raw1     BLOB(100);
  DECLARE varchar1 VARCHAR(100);
  DECLARE itemType INTEGER;
  
  SET status = DBMS_PIPE.RECEIVE_MESSAGE( 'pipe1' );
  IF( status = 0 ) THEN
    SET itemType = DBMS_PIPE.NEXT_ITEM_TYPE();
    CASE itemType
      WHEN 6 THEN
        CALL DBMS_PIPE.UNPACK_MESSAGE_NUMBER( num1 );
        CALL DBMS_OUTPUT.PUT_LINE( 'num1: ' || num1 );
      WHEN 9 THEN
        CALL DBMS_PIPE.UNPACK_MESSAGE_CHAR( varchar1 );
        CALL DBMS_OUTPUT.PUT_LINE( 'varchar1: ' || varchar1 );
      WHEN 12 THEN
        CALL DBMS_PIPE.UNPACK_MESSAGE_DATE( date1 );
        CALL DBMS_OUTPUT.PUT_LINE( 'date1:' || date1 );
      WHEN 23 THEN
        CALL DBMS_PIPE.UNPACK_MESSAGE_RAW( raw1 );
        CALL DBMS_OUTPUT.PUT_LINE( 'raw1: ' || VARCHAR(raw1) );
      ELSE
        CALL DBMS_OUTPUT.PUT_LINE( 'Unexpected value' );
    END CASE;
  END IF;
  SET status = DBMS_PIPE.REMOVE_PIPE( 'pipe1' );
END@

CALL proc1@

CALL proc2@

This example results in the following output:

varchar1: message1