DB2 Store procedure error in VB.NET

GuyThiebaut

Can you show us the first few lines of the stored procedure? The thing I am interested in seeing is everything from the name of the stored procedure to the closing parenthesis after the last parameter.

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

sudevsu

weird thing is it is exceuting the Stored procedure and inserting data into table. after insertion it throws an exception saying overload parameters. How can solve this?

Your help is much appreciated. Thanks Happy Coding!

sudevsu

Here is my SP

SET PATH "QSYS","QSYS2","SYSPROC","SYSIBMADM","SUSHDEV" ;

CREATE PROCEDURE DEVSUSH.SP_CUSTINFO (
IN PRMCICID INTEGER ,
IN PRMCIFN VARCHAR(30) ,
IN PRMCILN VARCHAR(30) ,
IN PRMCIADDR VARCHAR(90) ,
IN PRMCICITY VARCHAR(20) ,
IN PRMCISTATE VARCHAR(20) ,
IN PRMCIZIP NUMERIC(9, 0) ,
IN PRMCIPHONE NUMERIC(10, 0) ,
IN PRMCIEMAIL VARCHAR(30) ,
IN PRMCICLDATE DATE )
LANGUAGE SQL
SPECIFIC DEVSUSH.SP_CUSTINFO
NOT DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT
SET OPTION ALWBLK = *ALLREAD ,
ALWCPYDTA = *OPTIMIZE ,
COMMIT = *NONE ,
DECRESULT = (31, 31, 00) ,
DFTRDBCOL = *NONE ,
DYNDFTCOL = *NO ,
DYNUSRPRF = *USER ,
SRTSEQ = *HEX
P1 : BEGIN
INSERT INTO DEVSUSH . CLCUSINF ( CICID , CIFN , CILN , CIADDR , CICITY , CISTATE , CIZIP , CIPHONE , CIEMAIL , CICLDATE ) VALUES ( PRMCICID , PRMCIFN , PRMCILN , PRMCIADDR , PRMCICITY , PRMCISTATE , PRMCIZIP , PRMCIPHONE , PRMCIEMAIL , PRMCICLDATE ) ;
END P1 ;

GRANT ALTER , EXECUTE
ON SPECIFIC PROCEDURE DEVSUSH.SP_CUSTINFO
TO SUSHDEV ;

GRANT EXECUTE
ON SPECIFIC PROCEDURE DEVSUSH.SP_CUSTINFO
TO VAIGROUP ;

Your help is much appreciated. Thanks Happy Coding!

GuyThiebaut

Thanks - so it looks like there are 10 parameters for the stored procedure. My next question is - have you checked that the code that is calling the stored procedure is only passing in 10 parameters(no more no less) and that the parameter names are exactly as they are in the stored procedure?

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

sudevsu

well, I have 10 parameters and I am passing 10 parameters. Without names being correct in SP it wont insert data into table. But it is inserting the data in to table and still going to exception in catch block and throws an exception saying overload.

Your help is much appreciated. Thanks Happy Coding!

GuyThiebaut

Can you show me an example call to the stored procedure? I would like to see what you call the parameters from the calling end. Have you tried calling the stored procedure from an SQL client? If you have did, was the call successful?

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

Jorgen Andersson

DB2 != SQL Server DB2 doesn't use @ as a parameter marker, it uses ? as a positional marker or : for named markers. But this isn't a prepared statement so just try to remove all @ Disclaimer, I'm not working with DB2 either.

Wrong is evil and must be defeated. - Jeff Ello

sudevsu

This is how I am calling SP

Dim Cn As iDB2Connection = CreateConnection()
Try
Dim cm As New iDB2Command("CALL DEVSUSH.SP_CUSTINFO(@PrmCICID,@PrmCIFN,@PrmCILN,@PrmCIADDR,@PrmCICITY,@PrmCISTATE,@PrmCIZIP,@PrmCIPHONE,@PrmCIEMAIL,@PrmCICLDATE)", Cn)
strCIPHONE = strCIPHONE.Replace("-", "")
cm.Parameters.Add("@PRMCICID", iDB2DbType.iDB2Numeric).Value = Convert.ToInt32(strCICID)
cm.Parameters.Add("@PrmCIFN", iDB2DbType.iDB2VarChar).Value = strCIFN
cm.Parameters.Add("@PrmCILN", iDB2DbType.iDB2VarChar).Value = strCILN
cm.Parameters.Add("@PrmCIADDR", iDB2DbType.iDB2VarChar).Value = strCIADDR
cm.Parameters.Add("@PrmCICITY", iDB2DbType.iDB2VarChar).Value = strCICITY
cm.Parameters.Add("@PrmCISTATE", iDB2DbType.iDB2VarChar).Value = strCISTATE
cm.Parameters.Add("@PrmCIZIP", iDB2DbType.iDB2Numeric).Value = Convert.ToInt32(strCIZIP)
cm.Parameters.Add("@PrmCIPHONE", iDB2DbType.iDB2Numeric).Value = Convert.ToInt64(strCIPHONE)
cm.Parameters.Add("@PrmCIEMAIL", iDB2DbType.iDB2VarChar).Value = strCIEMAIL
cm.Parameters.Add("@PrmCICLDATE", iDB2DbType.iDB2Date).Value = strCICLDATE
cm.ExecuteNonQuery()
Catch ex As Exception
LogError.LogErrorIntoTextFile(ex, "InsertCust")
End Try

Your help is much appreciated. Thanks Happy Coding!

sudevsu

You mean just remove all @. Do I need to add ? or something instead of @?

Your help is much appreciated. Thanks Happy Coding!

GuyThiebaut

Have you tried calling the stored procedure from an SQL client? If you have did, was the call successful? I noticed that the stored procedure declaration does not contain @ in front of the parameter names after the procedure name, is this correct?

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

sudevsu

If I remove @ in front of parameters, it throw an error saying no such parameter. If I keep it @ then it just inserts. I am using ibm series.

Your help is much appreciated. Thanks Happy Coding!

sudevsu

How to check calling a stored procedure from SQL Client?

Your help is much appreciated. Thanks Happy Coding!

GuyThiebaut

Judging by this:DB2 stored procedure examples[^] you don't need any @s so try removing them from the stored procedure and your call. Disclaimer - I have never used DB2.

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

sudevsu

I removed from Stored procedure. It inserts the data into table but sill throws exception. But if I remove from my call in VB.NET code, it says no such parameter found and so no insertion of data.

Your help is much appreciated. Thanks Happy Coding!

GuyThiebaut

You will need something like Toad [^]then call the stored procedure from there. I always test stored procedures form a client before running them from .Net.

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

GuyThiebaut

Instead of this:

Dim cm As New iDB2Command("CALL DEVSUSH.SP_CUSTINFO(@PrmCICID,@PrmCIFN,@PrmCILN,@PrmCIADDR,@PrmCICITY,@PrmCISTATE,@PrmCIZIP,@PrmCIPHONE,@PrmCIEMAIL,@PrmCICLDATE)", Cn)

Try this:

Dim cm As New iDB2Command("CALL DEVSUSH.SP_CUSTINFO(?,?,?,?,?,?,?,?,?,?)", Cn)

and read this[^] [edit] ? added

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

sudevsu

Ok Thank you. I did that now, it is perfectly ok there. But from code it goes into catch and throws same error no matter what. :(

Your help is much appreciated. Thanks Happy Coding!

sudevsu

I tried the one which you said and Now this is throwing

SQL0440 Routine QZ9AFC1C3BBACE2001 in not found with specified parameters.

Your help is much appreciated. Thanks Happy Coding!

GuyThiebaut

sudevsu wrote:

I tried the one which you said and Now this is throwing

The one with the "?" question marks for parameters?

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

sudevsu

with ? I get this error

SQL0104 Token PRMCICID was not valid. Valid tokens: ) ,.

Your help is much appreciated. Thanks Happy Coding!