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)
SELECT COUNT(ITEM) INTO outputnumber
WHERE ITEM = inputnumber;
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("outputnumber").Direction = ParameterDirection.Output
ResultSet = GetArrayList(cmd.ExecuteReader, True)
cmd.CommandText = "SPNAME";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters["P2"].Direction = ParameterDirection.Output;
cmd.Parameters["P1"].value = X;
See the .NET Data Provider developers Guide: http://developer.teradata.com/doc/connectivity/tdnetdp/13.11/webhelp/StoredProcedureOverview.html
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?