Calling Teradata sp from .Net

Connectivity
Fan

Calling Teradata sp from .Net

Hi,

I am new to using Teradata and am trying to use it with vb.net to build a user app.

Can anyone please tell me how to call a teradata sp from vb.net (Visual Studio 2005)

I have a simple sp with input and output parameters, something like:

REPLACE PROCEDURE dbname.spname (IN inputnumber Integer, OUT outputnumber Integer)

BEGIN

SELECT COUNT(ITEM) INTO outputnumber

FROM dbname.tablename

WHERE ITEM = inputnumber;

END

In teradta sql assitant I can call this using: call dbname.spname ('10',outputnum);

However in .Net I have tried different ways to call this sp but keep getting errors such as:

[Teradata Database] Invalid session mode for procedure execution.

Here is a sample function similar to what I am using, am I doing something wrong here?

Public Function TestFunction(ByVal TestNum) As Boolean

Dim Sql As String

Dim ResultSet As New ArrayList

Sql = "CALL dbname.spname(?,?);"

Using cn As OleDbConnection = New OleDbConnection(TeradataApp.ConnectionString)

Dim cmd As OleDbCommand = New OleDbCommand(Sql, cn)

cmd.CommandType = CommandType.Text

cmd.Parameters.AddWithValue("inputnumber", OleDbType.Integer).Value = TestNum

cmd.Parameters.AddWithValue("outputnumber", SqlDbType.Int)

cmd.Parameters("outputnumber").Direction = ParameterDirection.Output

cn.Open()

 ResultSet = GetArrayList(cmd.ExecuteReader, True)

End Using

2 REPLIES
Teradata Employee

Re: Calling Teradata sp from .Net

  1. The CommandText must be set to the Stored Procedure Name.
  2. The CommandType must be set to CommandType.StoredProcedure.
  3. You must use the same Session Mode. That is the Session Mode you used to create the Stored Procedure must match the Session Mode you use to invoke the Stored Procdure.
  4. Use the .NET Data Provider for Teradata instead of "Microsoft .NET Data Provider for OLE DB"; you can download the .NET Data Provider for Teradata from http://downloads.teradata.com/download/connectivity/ dot-net-data-provider
  5. You are mixing the ".NET Data Provider for SQL Server" types (i.e. SqlDbType) with the ".NET Data Provider for OLE DB" types (OleDbType).
  6. Use ExecuteNonQuery given that SP does not return Dynamic Result Set.

cmd.CommandText = "SPNAME";

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("P1", OleDbType.Integer);

cmd.Parameters.Add("P2", OleDbType.Integer);

cmd.Parameters["P2"].Direction = ParameterDirection.Output;

cmd.Parameters["P1"].value = X;

cm.ExecuteNonQuery();

 See the .NET Data Provider developers Guide:  http://developer.teradata.com/doc/connectivity/tdnetdp/13.11/webhelp/StoredProcedureOverview.html

Fan

Re: Calling Teradata sp from .Net

Thank you very much NetFx...

I have downloaded the Terdata .Net provider and it is working for me now thanks to your help.

I wonder, could you advise on using cursors in stored procedures in Teradata.

I have read that cursors are not good to use however, from what I have read this seems to be the only way to retrieve a result set from Teradata?