Handling Stored Procedure Datasets

Connectivity

Handling Stored Procedure Datasets

Hi,

I am using TD .NET provider 12.x and have stored procedures. All my SPs have IN & OUT parameters. In my understanding, TD has a strange way to returning the OUT params as a dataset & it works fine. When I have SP that delete records from a table, and also returns OUT params.

When I execute this Sp from .NET, I get a empty dataset (tables = 0). On checking the data, it actually deletes the data correctly. But how can I know that records are deleted? I don't want to unnecessarily check the data for delete confirmation.

any help is appreciated. Thanks.

SP Code:

REPLACE STORED PROCEDURE spDelete
(
IN pCodeToDelete INTEGER,
OUT pStatus VARCHAR(10)
)
BEGIN
...
DELETE from db.Table WHERE code = :pCodeToDelete;
..
pStatus = 'SUCCESS';
END;
4 REPLIES
Teradata Employee

Re: Handling Stored Procedure Datasets

.NET Data Provider returns Output Parameters as "Output Parameters". That is the TdCommand.Parameters collection must have one or more instances of TdParameter with Parameter.Direction set to ParameterDirection.Output. Of course these TdParameter instance corresponds to the SP definition. For example in the case above:

TdCommand cmd = cn.CreateCommand();
cmd.CommandText = "spDelete";
cmd.CommandType = System.Data.CommandType.StoredProcedure;

cmd.Parameters.Add(new TdParameter("pCodeToDelete", TdType.Integer));
cmd.Parameters.Add(new TdParameter("pStatus", TdType.VarChar, 10));
cmd.Parameters["pStatus"].Direction = System.Data.ParameterDirection.Output;

cmd.Parameters["pCodeToDelete"].Value = 10;
cmd.ExecuteNonQuery();

Console.WriteLine(cmd.Parameters["pStatus"].Value);

Re: Handling Stored Procedure Datasets

Thanks very much! It worked. But for Some SPs, DataAdapter.Fill works for output params. Can't understand the reason.. :(
Teradata Employee

Re: Handling Stored Procedure Datasets

.NET Data Provider for Teradata has a custom feature for ad-hoc query tools.

For example given the following Stored Procedure:

REPLACE PROCEDURE spDelete(IN pCodeToDelete INTEGER, OUT pStatus VARCHAR(10))
BEGIN
IF pCodeToDelete = 1 THEN
SET pStatus='OK';
ELSE
SET pStatus='FAILED';
END IF;
END;

Following code will return the output parameter as the very first result set:

using (TdConnection cn = new TdConnection(conStrBuilder.ConnectionString))
{
cn.Open();

TdCommand cmd = cn.CreateCommand();
cmd.CommandText = "Call spDelete(?, pStatus)";
cmd.CommandType = System.Data.CommandType.Text;

cmd.Parameters.Add(new TdParameter("pCodeToDelete", TdType.Integer));
cmd.Parameters["pCodeToDelete"].Value = 2;

TdDataAdapter adapter = new TdDataAdapter(cmd);
DataTable result = new DataTable();
adapter.Fill(result);

Console.WriteLine(result.Columns.Count);
Console.WriteLine(result.Columns[0].Caption);
Console.WriteLine(result.Rows.Count);
Console.WriteLine(result.Rows[0][0]);
}

Note that in this case the CommandType is set to TEXT not StoredProcedure.
Again this is a custom feature for ad-hoc query tools that cannot correctly set the CommandType per ADO.NET specification. This feature is compatible with TERADATA Session-Mode.

I cannot recreate the issue you mentioned. I will look into it if you can tell me how to recreate the issue.
Teradata Employee

Re: Handling Stored Procedure Datasets

I forgot to mention that this custom feature is documented in the help file; see "Returning Data of Output Parameters in a Result Set" in "Stored Procedure Dynamic Result Set" page.

This page also mentions: "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."