Stored Procedures are able to return Dynamic Result Sets beginning with the release of Teradata Database Version 12.0. To support this new feature, the .Net Data Provider for Teradata, beginning with Version 12.0, also contains support for dynamic result sets.

Behaviorial Changes to Provider When Executing Stored Procedures

Some behavioral changes to the provider were necessary with the introduction of Dynamic Result Sets. These changes are as follows:

• The data reader must remain open to retrieve the data of LOBs returned in out/inout parameters. See section LOB Handling and Dynamic Result Sets.

• A result set will be returned that contains the data of output parameters if specific conditions are met. See section on Output Parameter Handling and Result Sets.

• If the CommandBehavior is set to Sequential Access, any LOB data returned as out/inout parameters and in the result set must be returned inlined. See the section on LOBs and Sequential Access.

• The schema of the dynamic result sets returned by a stored procedure cannot be retrieved by setting the Command Behavior to CommandBehavior.SchemaOnly. See the section on Specifying CommandBehavior.SchemaOnly for more information.

Executing Stored Procedure that returns a Dynamic Result set

Retrieving the data of a stored procedure that returns a dynamic result is similar to retrieving the data of a SELECT statement.

Example

// The stored procedure that will be executed is
//
// REPLACE PROCEDURE TeradataInfo(in p1 varchar(30))
// DYNAMIC RESULT SETS 2
// BEGIN
// DECLARE CURSOR cur1 WITH RETURN ONLY TO CLIENT FOR
// SELECT partitioninfo FROM dbc.SessionInfo where username = p1;
//
// DECLARE CURSOR cur2 WITH RETURN ONLY TO CLIENT FOR
// SELECT SUM(currentperm) FROM dbc.DiskSpace where username = p1;
//
// OPEN cur1;
// OPEN cur2;
// END;
//
public static void GetInfo(TdCommand cmd, String username)
{ 
    command.Parameters.Clear();

    // Setting up command to make call to stored procedure
    command.CommandText = @"TeradataInfo";
    command.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.Add(null, TdType.Varchar, username.Length,
        ParameterDirection.Input, true, 0, 0,
        null, DataRowVersion.Default, username);

    // Going to execute stored procedure
    TdDataReader dr = cmd.ExecuteReader();

    // Data is retrieved in the same manner as retrieving data
    // for a SELECT statement.

    // Retrieving data for first result set.
    while (true == dr.Read())
    {
        Console.WriteLine("PartionInfo = {0}", dr.GetString(0));
    }

    // Moving the data reader to the second result set.
    dr.NextResult();

    // Retrieving data for the second result data.
    Console.WriteLine("Current Permspace = {0}, User = {0}",
        dr.GetInt32(0), username);

    dr.Close();
}

ExecuteScalar and Dynamic Result Sets

When ExecuteScalar is used to execute a stored procedure that returns a result set, the object that is returned originates from the first column, from the first row, of the first result set. This is the same behavior as when an SQL statement that contains multiple SELECT statements is executed by calling TdCommand.ExecuteScalar.

Specifying CommandBehavior.SchemaOnly

The schema information on dynamic results cannot be retrieved by setting the command behavior to SchemaOnly when calling TdCommand.ExecuteReader. If ExecuteReader is called with the command behavior set to SchemaOnly, a null will be returned.

The reason for this is that when the CommandBehavior is set to SchemaOnly, the provider sends the CALL statement to Teradata and specifies that the statement is to be prepared. The information that is returned is NOT the schema information on the result sets. Therefore, the provider will return null.

LOB Handling and Dynamic Result Sets

When a Stored Procedure is executed and it returns the following items:

• A Rowset is returned.
• A LOB is returned in an inout/out parameter.

The data reader that is returned cannot be closed before the data of the LOB parameters are read. If an application closes the data reader, the provider will send a request to Teradata to cleanup. When this occurs, all the spool files associated with the execution of the Stored Procedure will be dropped. When an attempt is made to access the data of the LOB parameter the provider will throw an exception that indicates that the data is not available.

In the following examples, the stored procedure that is specified returns a rowset and contains an out parameter that is a CLOB.

In this example an exception will be thrown when an attempt to read the data of the CLOB is made.

public static void ExampleExceptionThrown(TdCommand cmd)
{
   cmd.Parameters.Clear();

   cmd.Parameters.Add(null, TdType.Clob, 2000000000, null);
   cmd.Parameters[0].Direction = ParameterDirection.Output;

   cmd.CommandText = "SP_ReturnRowsetAndClobOutParameter";
   cmd.CommandType = CommandType.StoredProcedure;

   TdDataReader dr = cmd.ExecuteReader();

   dr.Read();

   // The data contained in the rowset is retrieved

   dr.Close();

   TdClob clob = (TdClob)cmd.Parameters[0].Value;

   // The following call will throw an exception because the
   // data reader has been closed.

   clob.Read(1000);
}

This example shows one of the correct ways to retrieve the data of the out parameter and the rowset.

public static void ExampleReadClobDataAndRowset(TdCommand cmd)
{
   cmd.Parameters.Clear();

   cmd.Parameters.Add(null, TdType.Clob, 2000000000, null);
   cmd.Parameters[0].Direction = ParameterDirection.Output;

   cmd.CommandText = "exec SP_ReturnRowsetAndClobOutputParameter";

   TdDataReader dr = cmd.ExecuteReader();

   // Going to process data of output parameter
   TdClob clob = (TdClob)cmd.Parameters[0].Value;

   // Process the data of the CLOB
   clob.Close();

   // Going to process data of the rowset
   dr.Read();

   // Retrieve the data of the rowset
   dr.Close();
}

LOBs and CommandBehavior.SequentialAccess

When the command behavior is set to Sequential Access to execute an command that will return LOBs, the application is indicating that it expects the LOBs to be returned inlined --the data of the LOBs will be returned as a binary array, char array, or string.

If SequentialAccess is specified when executing a stored procedure, the behavior will affect LOBs that are returned as out/inout parameters as well as in any result sets. If an attempt to retrieve an output parameter that is a LOB is attempted, the provider will throw an exception indicating that this operation is illegal. For example, suppose the TdParameter.TdType of an output parameter that is a LOB is set to TdType.TdClob --data will be returned in deferred mode using a TdClob object. The stored procedure is executed with the CommandBehavior set to SequentialAccess. After the stored procedure has been executed and the provider begins to process the data of the output parameters an exception will be thrown when the provider attempts to create the deferred LOB object .

If Sequential Access is specified when executing an external stored procedure, DBS will return "6950 - Returning LOB inline in multipart Record mode is not supported for XSP" as external stored procedures do not support inline return of LOB data for INOUT or OUT parameters. Refer General Guidelines for Using Stored Procedures for additional details.

Out Parameters and Dynamic Result Sets

Depending upon what command type is used to execute the stored procedure and how the output parameters are specified the data of the output parameters can be retrieved two different ways. However, before discussing the different ways of retrieving the data of output parameters the concept of Bound and Unbound parameters must be explained.

What Are Bound and Unbound Parameters?

The concept of Bound and Unbound output parameters are discussed in the following sections.

Bound Parameters

In releases of the provider before 12.0, a TdParameter had to be created for all output parameters. The provider would return the corresponding result back to the application using TdParameter.Value. Creating a TdParameter and specifying information about the output parameter is called Binding the parameter.

Unbound Parameters

A stored procedure parameter is considered to be unbounded if it has no corresponding TdParameter. In the call to the stored procedure, "name markers" are used in place of the "?". For example, suppose the call signature of a stored procedure is

    REPLACE PROCEDURE spExample(out p1 Integer)

The TdCommand.CommandText is set to

    CALL spExample(p1)

p1 is the parameter name marker of the output parameter. When this stored procedure is to be executed, no TdParameter object is created for the out parameter.

Recommended Way of Returning Data of Output Parameters

When executing a stored procedure that contains output parameters it is recommended that all the output parameters get bound. This will remove the confusion of where the data of the output parameters will be returned.

The following is an example of executing a stored procedure that contains two output parameters and returns 1 result sets.

// The stored procedure that will be executed is
//
// REPLACE PROCEDURE TeradataInfo(
// in username VARCHAR(30),
// out permspace INTEGER,
// out maxpermspace) dynamic result sets 1
// BEGIN
// DECLARE cur1 CURSOR WITH RETURN ONLY TO CLIENT FOR
// SELECT DatabaseName, sum(CurrentPerm), sum(MaxPerm), sum(CurrentSpool)
// FROM dbc.DiskSpace GROUP BY 1 ORDER BY 2 DESC;
//
// FOR cur2 AS info CURSOR FOR
// SELECT SUM(CurrentPerm) as currentdisk, SUM(MaxPerm) as maxdisk
// FROM dbc.DiskSpace
// WHERE DatabaseName = :username
// DO
// set permspace = cur2.currentdisk;
// set maxpermspace = cur2.maxdisk;
// END FOR;
//
// OPEN cur1;
// END;
//
public static void GetDatabaseInfo(TdCommand cmd, String username)
{
   cmd.Parameters.Clear();

   // Setting up command to make call to stored procedure
   cmd.CommandText = "TeradataInfo"
   cmd.CommandType = CommandType.StoredProcedure;

   cmd.Parameters.Add(null, TdType.Varchar, username.Length,
   ParameterDirection.Input, true, 0, 0,
   null, DataRowVersion.Default, username);

   // Going to Bind the output parameters
   cmd.Parameters.Add(null, TdType.Integer, 0,
      ParameterDirection.OUtput, true, 0, 0,
      null, DataRowVersion.Default, null);
   cmd.Parameters.Add(null, TdType.Integer, 0,
      ParameterDirection.Output, true, 0, 0,
      null, DataRowVersion.Default, null);

   // Going to execute stored procedure
   TdDataReader dr = cmd.ExecuteReader();

   // Getting parameter data
   Int32 currentPerm = (Int32)cmd.Parameters[1].Value;
   Int32 maxPerm = (Int32)cmd.Parameters[2].Value;
   // Going to print out information to the console.
   Console.WriteLine("Diskspace information for {0}:", username);
   Console.WriteLine(" Current diskspace usage is {0}", currentPerm);
   Console.WriteLine(" Maximum diskspace that can be allocated: {0}", maxPerm);

   // Retrieving data for result set.
   while (true == dr.Read())
   {
      Console.WriteLine("{0} {1} {2} {3} ",
         dr.GetString(0), dr.GetInt32(1), dr.GetInt32(2), dr.GetInt32(3));
   }

   dr.Close();
}

Returning Data of Output Parameters in a Result Set

Under specific conditions, the data of output parameters can be returned in a result set. This feature is very helpful to adhoc query tools that use the .NET Data Provider for Teradata.

In order to return the data of output parameters in a result set the following conditions must be met:

1. CommandType is set to CommandType.Text and the CALL statement is specified in the CommandText. In the CommandText, "name markers" must be used for the output parameters. For example, the CALL statement that will be used to call the stored procedure in the previous example will be:

       CALL TeradataInfo(?, p2, p3)
   

2. The stored procedure only contains input and output parameters.

3. All the output parameters are Unbound.

To execute the CALL statement in auto-commit mode, the SessionMode of the connection must be set to Teradata. If the SessionMode is set to ANSI an explicit transaction (refer to TdConnection.BeginTransaction for more information) must be started before the CALL statement is executed.

The following example uses the stored procedure contained in the previous example.

public static void GetDatabaseInfo(TdCommand cmd, String username)
{
   cmd.Parameters.Clear();

   // Setting up command to make call to stored procedure

   // Going to specify the call statement.
   // Name markers are used to specify the output parameters.
   cmd.CommandText = "CALL TeradataInfo(?, permSpace, maxPermSpace)";
   // The command type must be set to Text
   cmd.CommandType = CommandType.Text;

   cmd.Parameters.Add(null, TdType.Varchar, username.Length,
      ParameterDirection.Input, true, 0, 0,
      null, DataRowVersion.Default, username);

   // The output parameter are unbound. Do not need to create TdParameters for them.

   // Going to execute stored procedure
   TdDataReader dr = cmd.ExecuteReader();

   // The first result set contains the data for the output parameters
   dr.Read();

   Int32 currentPerm = dr.GetInt32(0);
   Int32 maxPerm = dr.GetInt32(1);

   Console.WriteLine("Diskspace information for {0}:", username);
   Console.WriteLine(" Current diskspace usage is {0}", currentPerm);
   Console.WriteLine(" Maximum diskspace that can be allocated: {0}", maxPerm);

   // Going to the next result set to get the result set that contains
   // information about other databases
   dr.NextResult();

   // Retrieving data for result set.
   while (true == dr.Read())
   {
      Console.WriteLine("{0} {1} {2} {3} ",
         dr.GetString(0), dr.GetInt32(1), dr.GetInt32(2), dr.GetInt32(3));
   }

   dr.Close();
}

Specifying both Bound and Unbound parameters

If both Bound and Unbound output parameters are used in the call to the stored procedure a result set containing the data of the Unbound output parameters will NOT be returned. In addition, the data returned for the Bound parameters may be incorrect. For example, a stored procedure has the following parameter list:

    REPLACE PROCEDURE CreditLimit(in ssn VARCHAR(9), out creditLimit integer, output creditScore integer)

If the parameter to creditlimit is Unbound, and the parameter to creditscore is Bound the following will occur after the stored procedure has been executed:

• The data associated with the creditLimit will be returned in the Bound creditScore parameter.

• No result set is returned that contains the data of the Unbound parameter.

• The data associated with the creditScore is unavailable.

This same behavior will occur if the parameter list of the stored procedure contains both InputOutput and Output parameters.

The following example uses the CreditLimit stored procedure.

public static void GetInfo(TdCommand cmd, String ssn)
{
   cmd.Parameters.Clear();

   cmd.CommandText = "CALL CreditLimit(?, CreditLimit, ?)";
   cmd.CommandType = CommandType.Text;

   // Parameter for ssn
   cmd.Parameters.Add(null, TdType.Varchar, 0,
      ParameterDirection.Input, true, 0, 0,
      null, DataRowVersion.Default, ssn);

   // Parameter for CreditScore
   cmd.Parameters.Add(null, TdType.Integer, 0,
      ParameterDirection.Output, true, 0, 0,
      null, DataRowVersion.Default, null);

   TdDataReader dr = cmd.ExecuteReader();

   // At this point, there was no result set that contains the data for the output
   // parameters returned. dr.Read() will return false.

   // When the data for CreditScore is retrieved from the parameter list
   // it will have the data associated with the CreditLimit.
   Int32 data = cmd.Parameters[1].Value;

}