I am using DB2 v. 7 and DB2 Connect (fp5) from Visual Basic (through ADO). The issue is that I only able to call stored procedures from Visual Basic when it has no parameters.
I have created two very simple stored procedures that do exactly the same thing. One has 0 parameters and the other takes 1 parameter (I've tested with char(5), decimal(9,0), or integer).
I have been able to call both of these stored procedures through the IBM DB2 Connect Command Line Processor utility and get the correct results in that environment.
I have tried testing the ADO Connection.Execute method with the stored procedures described above. Using the following statement I get the desired results (with the parameter-less stored proc),
Call adconDBCon.Execute("call TESTPROC(); ")
With the statement,
Call adconDBCon.Execute("call TESTPARM2('296.2'); ")
I get the following error:
[IBM][CLI Driver][DB2] SQL0440N No function by the
name "TSTPARM2" having compatible arguments was found in the
function path. SQLSTATE=42884 (-214746259)
When I fully qualify the name I get this same error. I have also tried different types of parameters but the error perists. In the case above (with the char(5) parameter) the stored procedure is created as:
CREATE PROCEDURE RGOOD.TSTPARM2 ( IN varDiagCod char(5) )
If I pass the SQL statements (i.e. "SELECT ... " ) through the
connection's Execute method I also get the desired results.
Is there some setting that explains why the two procedures behave
differently (especially since the Command Line Processor utility functions
p.s. I have also been able to call them using the ADO Command object.
However, I would rather use the Execute method to allow for batches