You can develop CLR procedures that return result sets
to a calling routine or application. Result sets cannot be returned
from CLR functions (UDFs).
Before you begin
Attention: With the release of Db2®
11.5.9, support for Microsoft
.Net common language runtime (CLR) routines is deprecated and might be removed
in the future. If you are currently running routines that have a dependency on .NET CLR, rewrite the
routine logic in a supported language and then recreate the routines.
The
.NET representation of a result set is a DB2DataReader object which can be returned
from one of the various execute calls of a DB2Command object. Any
DB2DataReader object whose Close() method has not explicitly been
called prior to the return of the procedure, can be returned. The order in which result sets are
returned to the caller is the same as the order in which the DB2DataReader objects
were instantiated. No additional parameters are required in the function definition in order to
return a result set.
An understanding of how to create CLR routines will help you to perform the
steps in the following procedure for returning results from a CLR procedure.
Procedure
To return a result set from a CLR procedure:
- In the CREATE PROCEDURE statement for the CLR routine you
must specify along with any other appropriate clauses, the DYNAMIC
RESULT SETS clause with a value equal to the number of result sets
that are to be returned by the procedure.
- No parameter marker is required in the procedure declaration
for a result set that is to be returned to the caller.
- In the .NET language implementation of your CLR routine,
create a
DB2Connection object, a DB2Command object,
and a DB2Transaction object. A DB2Transaction object
is responsible for rolling back and committing database transactions.
- Initialize the Transaction property of the
DB2Command object
to the DB2Transaction object.
- Assign a string query to the
DB2Command object's CommandText
property that defines the result set that you want to return.
- Instantiate a
DB2DataReader, and assign
to it, the result of the invocation of the DB2Command object
method ExecuteReader. The result set of the query
will be contained in the DB2DataReader object.
- Do not execute the
Close() method of the DB2DataReader
object at any point prior to the procedure's return to the caller.
The still open DB2DataReader object will be returned
as a result set to the caller. When more than one DB2DataReader is
left open upon the return of a procedure, the DB2DataReaders are
returned to the caller in the order of their creation. Only the
number of result sets specified in the CREATE PROCEDURE statement
will be returned to the caller.
- Compile your .NET CLR language procedure and install the
assembly in the location specified by the EXTERNAL clause in the
CREATE PROCEDURE statement. Execute the CREATE PROCEDURE statement
for the CLR procedure, if you have not already done so.
- Once the CLR procedure assembly has been installed in the
appropriate location and the CREATE PROCEDURE statement has successfully
been executed, you can invoke the procedure with the CALL statement
to see the result sets return to the caller.