vb.Net call to Teradata stored procedure

Connectivity
N/A

vb.Net call to Teradata stored procedure

Hey,

The below function won't build for me at the two statements below attempting to add
parameters to a dynamic stored procedure call. It doesn't like the 2nd parameter in each statement. I just cannot find the correct syntax using Teradata. I usually call these in BTEQ like call sp_MISRE_ChkClaimCount('Marc', 10, v_return_code);"
My procedure uses 3 parameters: (IN v_pWho VARCHAR(20), IN v_i_run_job_id INT, OUT v_return_code INT))

Error: "Too many arguments to 'Public MustOverride Function Add(value As
Object) As Integer'."

looks like dbCommand (from .NET Data Provider for Teradata -
Teradata.Client.Provider) can only support 1 parameter. Any ideas how to fix.
This worked fine in SQLServer using VS 2003 and SqlClient.SqlCommand.

cmd.Parameters.Add("v_pWho", UNameRoutine)
cmd.Parameters(0).Direction = ParameterDirection.Input

cmd.Parameters.Add("v_return_code", SqlDbType.Int)
cmd.Parameters(1).Direction = ParameterDirection.Output

-- EXAMPLE CODE
'Vars declarations
'.NET Data Provider for Teradata - Teradata.Client.Provider
Dim cn As DbConnection
Dim cmd As DbCommand
Dim dtr, dtr1 As DbDataReader
Dim strSQL As String
Dim cmdSelect As DbCommand
Dim cnString As String
Dim pf As DbProviderFactory =
DbProviderFactories.GetFactory("Teradata.Client.Provider")

' Function Code
Private Function RunExecRoutines() As Boolean
Try
cnString = "Data Source=" & Server.Text & ";User ID=" &
UserName.Text & ";Password=" + Password.Text
cn = pf.CreateConnection
cn.ConnectionString = cnString
cn.Open()
Catch ex As Exception
sqlCnError = ("Error: Could not establish database connection")
End Try

cmd = pf.CreateCommand
cmd.Connection = cn

cmd.CommandText = ProcToExec
cmd.CommandTimeout = 9000
cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.Add("v_pWho", UNameRoutine)
cmd.Parameters(0).Direction = ParameterDirection.Input

cmd.Parameters.Add("v_return_code", SqlDbType.Int)
cmd.Parameters(1).Direction = ParameterDirection.Output

Try
cmd.ExecuteNonQuery()
Catch ex As Exception
sqlCnError = ("Error: Could not execute " + ProcToExec + "
stored procedure")
MessageBox.Show(sqlCnError, "MIS RECON Engine",
MessageBoxButtons.OK, _
MessageBoxIcon.Error, MessageBoxDefaultButton.Button1)
End Try

cmd.Dispose()
cn.Close()
cn.Dispose()
End Function

5 REPLIES
Teradata Employee

Re: vb.Net call to Teradata stored procedure

Refer to

DbParameterCollection Members

for information on DbParameterCollection.

DbParameterCollection class has one and only one Add method. It simply takes an Object.

TdParameterCollection class has 7 differnt Add methods.

Try the following if you prefer to use DbParameter and DbParameterCollection.

cmd.Parameters.Add(pf.CreateParameter)
cmd.Parameters(0).Direction = ParameterDirection.Input

cmd.Parameters.Add(pf.CreateParameter)
cmd.Parameters(1).Direction = ParameterDirection.Input
cmd.Parameters(1).DbType = DbType.Int32

N/A

Re: vb.Net call to Teradata stored procedure

Thanks Shaw, I am still confused though.
I tried your suggestion as shown in code at the bottom of this thread by passing 3 parameters as per the code snippet you recommended. However, where exactly do you assign the parameters to be used? i.e. UNameRoutine is = 'Marc' (the logon to the application) etc.
My sProc is defined like this REPLACE PROCEDURE sp_MISRE_Pivot (
IN v_pWho VARCHAR(20),
IN v_i_run_job_id INT,
OUT v_return_code INT)

Also when I run the below as is, I get the message 'Teradata.Client.Provider.TdException: [Teradata Database [5495] Stored Procedure 'DEV_DATA_DIMS.DEV_DATA_V.sp_MISRE_Pivot' does not exist at Teradata.Client.Provider.TdCommand.ExecuteNonQuery'.
My sProc exists on DEV_DATA_V but it attaches the default DEV_DATA_DIMS onto it first. When I log into Queryman with no default database set, it will logon to DEV_DATA_DIMS, maybe this is it but how can i change that (i didn't set up the databases).

cmd.Parameters.Add(pf.CreateParameter)
cmd.Parameters(0).Direction = ParameterDirection.Input
cmd.Parameters(0).DbType = DbType.String

cmd.Parameters.Add(pf.CreateParameter)
cmd.Parameters(1).Direction = ParameterDirection.Input
cmd.Parameters(1).DbType = DbType.Int32

cmd.Parameters.Add(pf.CreateParameter)
cmd.Parameters(2).Direction = ParameterDirection.Output
cmd.Parameters(2).DbType = DbType.Int32

MessageBox.Show(ProcToExec)
MessageBox.Show(cmd.CommandText.ToString)
MessageBox.Show(cmd.ToString)

Try
cmd.ExecuteNonQuery()
Catch ex As Exception
MessageBox.Show(ex.ToString)
sqlCnError = ("Error: Could not execute " + ProcToExec + " stored procedure")
MessageBox.Show(sqlCnError, "MIS RECON Engine", MessageBoxButtons.OK, _
MessageBoxIcon.Error, MessageBoxDefaultButton.Button1)
End Try
N/A

Re: vb.Net call to Teradata stored procedure

I've gotten a bit further in that i have the sProc running but the default database it picks up (DEV_DATA_DIMS) when running a sProc is causing issue. The sProc resides on DEV_DATA_V, I have madea copy on DEV_DATA_DIMS until i resolve the issue there. Any idea how this is being picked up as I am not setting it or can you advise how I can get it to choose the database i want it to?

note: (it's not doing this on normal DML or Select Statements).

Teradata Employee

Re: vb.Net call to Teradata stored procedure

To change your default database

DbCommand cmd = cn.CreateCommand();
cmd.CommandText = "Database DEV_DATA_V";
cmd.ExecuteNonQuery();

cmd.CommanText = "sp_MISRE_Pivot";
cmd.CommandType = CommandType.StoredProcedure;

// Set Parameters

cmd.ExecuteNonQuery();
N/A

Re: vb.Net call to Teradata stored procedure

which is fine but the name could and will in my case exceed 30 charaters.
I have used the tools-modify user-default database tool instead. I don't think this will have any issue in dev.
thanks for the help.