Using Pipes and Cursors
Pipes and cursors can be used separately or in combination to solve a variety of challenges within TDV. They are useful when some preprocessing needs to be done to complex data—for example, stripping out rows where the condition is difficult to express in SQL, or returning a flattened row from an XSLT transformation on an XML JMS message.
Pipes
A pipe is a cursor that can have data inserted into it from many sources. TDV only sees pipes as OUT parameters for a procedure. You can use INSERT statements to get the data into the pipes for processing. For example:
PROCEDURE q(OUT name VARCHAR)
BEGIN
DECLARE c CURSOR (name VARCHAR);
CALL /shared/rtnPipe(c);
FETCH c INTO name;
END
Cursors
For TDV, a cursor is a result set returned from a single data source. A cursor can be thought of as a pointer to one row in a set of rows. The cursor can only reference one row at a time, but can move to other rows of the result set as needed.
Cursors can be used to perform complex logic on individual rows.
To use cursors in SQL procedures, you need to do the following:
• Declare a cursor that defines a result set.
• Open the cursor to establish the result set.
• Fetch the data into local variables as needed from the cursor, one row at a time.
• Close the cursor when done.
Static Cursor Example
DECLARE <cursor-name> CURSOR FOR <select>;
Dynamic Cursor Example
PROCEDURE p(OUT p_name VARCHAR)
BEGIN
DECLARE c CURSOR (name VARCHAR);
OPEN c FOR SELECT name FROM /shared/T;
FETCH c INTO p_name;
CLOSE c;
END
The dynamic cursor can be reopened:
• With the same query or a different query
• Only after being closed
PROCEDURE p(OUT p_name VARCHAR)
BEGIN
DECLARE c CURSOR (name VARCHAR);
OPEN c FOR SELECT name FROM /shared/T;
CLOSE c;
-- Reopen with same query
OPEN c;
CLOSE c;
-- Reopen with new query
OPEN c FOR SELECT name FROM /shared/U WHERE birthdate > ‘2000-01-01’;
CLOSE c;
END