|
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!
|
|
|
|
|
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
|
|
|
|
|
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!
|
|
|
|
|
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!
|
|
|
|
|
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
|
|
|
|
|
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!
|
|
|
|
|
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
|
|
|
|
|
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!
|
|
|
|
|
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
|
|
|
|
|
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!
|
|
|
|
|
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
|
|
|
|
|
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!
|
|
|
|
|
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
|
|
|
|
|
with ?
I get this error
SQL0104 Token PRMCICID was not valid. Valid tokens: ) ,.
Your help is much appreciated.
Thanks
Happy Coding!
|
|
|
|
|
Read this[^] article - I think it should help.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
Thanks Guy. I wish this should resolve ASAP. It is taking my whole lot of time. 
Your help is much appreciated.
Thanks
Happy Coding!
|
|
|
|
|
How to check calling a stored procedure from SQL Client?
Your help is much appreciated.
Thanks
Happy Coding!
|
|
|
|
|
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
|
|
|
|
|
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!
|
|
|
|
|
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.
|
|
|
|
|
You mean just remove all @. Do I need to add ? or something instead of @?
Your help is much appreciated.
Thanks
Happy Coding!
|
|
|
|
|
Not in the procedure just remove them like this:
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 ) ;
But your call from vb.net to the database is a prepared statement so that should be changed to:
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
Should note that the error message you're getting is not from DB2 but from VB.Net, stating that there is a mismatch in the parameters. Or rather that it can't find an overload of the procedure having the right parameters.
The reason it works is that it tries to apply the parameters in the order they've been added like as if you had used ? as a marker. Like this:
Dim cm As New iDB2Command("CALL DEVSUSH.SP_CUSTINFO(?,?,?,?,?,?,?,?,?,?)", Cn)
|
|
|
|
|
A thought, have you checked that there are no triggers spit attached to the table and the error is generated from the trigger instead of your procedure (this is the reason I loathe triggers spit)?
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Remember, when you fix this post back under your original post with how you fixed it 
(this can be very useful to other people who come to this forum looking for a solution to the same issue you had)
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
Sure. But I didn't fix this yet.
Your help is much appreciated.
Thanks
Happy Coding!
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
