Calling Stored Procedure on Teradata from VB

[IHaq]

Hi All:

I'm very new to Teradata world. I've been trying to call a stored procedure on a Teradata Database from VB. I've trie many different ways but no result. I always get errors. I tried setting:

With cmd
.ActiveConnection = cnn
.CommandType = adCmdStoredProc
.CommandText = "testSP"
.Parameters.Append .CreateParameter("str", adInteger, adParamInput, 1, 132)
.Parameters.Append .CreateParameter("outStr", adChar, adParamOutput, 30, "&quot
End With

But I get "mismatched number of parameters" error. While I know, that this sp has only two Parameters.
Then I tried changing the CommandText to include parameters values, as:

.CommandText = "testSP(5,""strout"&quot"
but it didn't work either. I get "no colum found with name 'strout'".

Are ther any "gurus" to help me out.

Thnx

-Inam.

 

[ns1909]

am not very sure, but since the second parameter is Out, so u should specify an variable instead of value while calling the sp.
did u try this option.

 

[ns1909]

Try this to call the procedure -
.CommandText = "CALL testSP(5,""strout"&quot"

 

[IHaq]

Thnx ns1909.

I got it to work. You don't have to build command object. Instead, directly execute the command on connection (do a connection.Execute) and the Teradata ODBC driver returns you a record set having your out params as its fields.
It worked like that:

strCmd = "CALL testSP(5, 'outStr')"
oRS = cnn.Execute(strCmd, nR, -1)

The trick is that in the CALL statement, for the out params, you've to give the same param names as declared in the SP declaration. The resultant oRS has only one field "outStr" with the value returned by the testSP.

Thnx for your time.

Have a good one !!