Db2 function call with IDBCommand

Question

i have trouble calling a function from VB with IDBcommand which returns a integer value

        param1 = dbfactory.CreateParameter("param1", DbType.AnsiString)
        param1.Value = sIban
        param1.Direction = ParameterDirection.Input
        param1.Size = 1000
        param2 = dbfactory.CreateParameter("param2", DbType.AnsiString)
        param2.Value = sNid
        param2.Direction = ParameterDirection.InputOutput
        param2.Size = 1000
        param3 = ´dbfactory.CreateParameter("param3", DbType.AnsiString)
        param3.Value = sKto
        param3.Direction = ParameterDirection.InputOutput
        param3.Size = 1000
        ret_value = dbfactory.CreateParameter(Nothing, DbType.Int16)
        ret_value.Size = 1000
        ret_value.Direction = ParameterDirection.ReturnValue
        cmd.CommandText = "Func1"
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.Add(param1)
        cmd.Parameters.Add(param2)
        cmd.Parameters.Add(param3)
        cmd.Parameters.Add(ret_value)
        cmd.Connection = conn
        cmd.ExecuteNonQuery()

The header from my function looks like this:

CREATE OR REPLACE FUNCTION Func1( param1 VARCHAR(50), param2 VARCHAR(50), param 3 VARCHAR(50))RETURNS INTEGER

I get an exception that no procedure could be found.

Does anybody have any idea? I have been searching for a while now.

Thanks

score 0 · Answer 1 · answered Aug 29 '13 at 14:59

You create a function with 3 parameters, but call a stored procedure with 4 parameters -- no wonder it fails, as these are different objects and you invoke them differently.

I'm not very familiar with .Net stuff, but generally speaking you should invoke UDFs in the context of a query:

select Func1(?, ?, ?) from sysibm.sysdummy1

or a VALUES statement

values Func1(?, ?, ?)

In both cases you get a result set from which you can fetch the value returned by the function. Check the code samples in the manual.

Db2 function call with IDBCommand

1 Answers1