DB2 UDB BYREF Stored Procedure Example

The following example shows how to create, register and invoke a database procedure with BYREF parameters using the DB2 UDB gateway.

Note: The user creating the DB2 UDB stored procedure must have create procedure permissions. The register procedure requires insert permissions on the gateway procedure catalogs. The execute procedure statement requires execute permissions for the DB2 UDB stored procedure and select permissions on the gateway procedure catalogs.

1. Create the DB2 UDB stored procedure from the Ingres terminal monitor; there are trailing spaces after each line that is required to successfully create the procedure.

remove procedure order_byref;
\p\g

direct execute immediate 'drop procedure order_byref';
\p\g

direct execute immediate '
CREATE PROCEDURE order_byref
( INOUT id_number INTEGER,
  INOUT name VARCHAR(20),
  INOUT status CHAR(20),
  INOUT order_date TIMESTAMP)
language sql
BEGIN
           INSERT INTO orders values (id_number, name, status, order_date);
           SELECT id_number, name, status, order_date
                   INTO id_number, name, status, order_date
                   FROM orders
                   WHERE orders.id_number=0;
           DELETE FROM orders WHERE orders.id_number=0;
END';
\p\g

register procedure order_byref
    (  id_number   int byref,
       name        varchar(20) byref,
       status      char(20) byref,
       order_date  date byref)
     as import
from 'order_byref';
\p\g

col1=10;
sprintf(col2, "UpdateVarChar" );
sprintf(col3, "UpdateVarChar" );
sprintf(col4, "23-feb-2001" );

printf( "values before:\n col1: %d\n col2: %s\n col3: %s\n col4: %s\n\n", col1, col2, col3, col4 );

EXEC SQL execute procedure order_byref
            (    id_number   = BYREF(:col1),
                 name        = BYREF(:col2),
                 status      = BYREF(:col3),
                 order_date  = BYREF(:col4));

printf( "values after:\n col1: %d\n col2: %s\n col3: %s\n col4: %s\n\n", col1, col2, col3, col4 );