Java Stored Proc Not Executing

Question

I have a stored procedure call in my java code which uses sybase database as follows:

CallableStatement cstmt=sigmaConnection.prepareCall("{call dbo.sp_insRadEntry(?, ?, ?, ?, ?, ?, ?)}");
cstmt.setString(1,"Nappa");
cstmt.setInt(2,40);
cstmt.setString(3,"Vegeta");
cstmt.setString(4,"Saiyan");
cstmt.setString(7,"Hello"); 

cstmt.execute();

As you can see i have deliberately not set the 5th and 6th parameter as my stored procedure has default values provided if any values are not passed and in this call call dbo.sp_insRadEntry(?, ?, ?, ?, ?, ?, ?) i have 7 parameteres as it was not allowing me to set the 7 th parameter without having 7 commas here.

But I'm getting the following exception even though I HAVE SET THE 4TH PARAMETER:

'java.sql.SQLException: JZ0SA: Prepared Statement: Input parameter not set, index: 4.'

Can you post the top portion of the create procedure statement (with the variables, default values, etc) — Hotel, Nov 06 '12 at 05:11

score 0 · Answer 1 · answered Oct 10 '12 at 13:17

0

It looks like 'index 4' refers to the fifth parameter (index is zero-based) so I think the problem is that the default parameters are not being recognised by your code.

answered Oct 10 '12 at 13:17

codebox

19,927
9
63
81

But I have to use only the defaults specified in the stored proc.So how do i modify my code so that only the values specified in the stored proc are used. – gautam vegeta Oct 10 '12 at 13:20

dan · Answer 2 · 2012-10-10T13:45:07.043

0

You should try set the missing 5th and 6th parameter too, using setNull methods.

edited Oct 10 '12 at 13:45

answered Oct 10 '12 at 13:20

dan

13,132
3
38
49

Tried that too but it gives another exception saying that "column does not allow nulls" – gautam vegeta Oct 10 '12 at 13:25
Yes tried that too.Retrns the exception that null values are not allowed. – gautam vegeta Oct 10 '12 at 15:20

Aman J · Answer 3 · 2012-10-11T13:03:20.963

0

You need to set value at Index 5 & 6 also

int type = TYPE
pst.setNull(index, type);

For TYPE refer here : http://www.tutorialspoint.com/jdbc/jdbc-data-types.htm

Edit: If you have Not Null constraints you have no way but set some dummy or blank values at those position which you will ignore while reading data(ehh..have to write some extra code for that check) and if you really don't want to do that then why have you set Not Null constraint in first place think of it. :-)

edited Oct 11 '12 at 13:03

answered Oct 10 '12 at 13:27

Aman J

1,825
1
16
30

I tried the foll and it still return an exception as null values not allowed. `cstmt.setNull(5,java.sql.Types.CHAR); cstmt.setNull(6,java.sql.Types.VARCHAR);` – gautam vegeta Oct 10 '12 at 15:24
@gautamvegeta check your DB table does it have any `not null` constraints for column 5 & 6 . – Aman J Oct 11 '12 at 03:51
Yes it does have those constraints.But that is why in my stored proc i have mentioned default parameteres.All i want to do is skip that paramter in the stored proc call in java. – gautam vegeta Oct 11 '12 at 10:20

score 0 · Answer 4 · answered Oct 11 '12 at 14:22

I'm not sure this will work in your DB, but what about omitting the question marks for the parameters you don't want to specify? That is:

CallableStatement cstmt=sigmaConnection.prepareCall(
        "{call dbo.sp_insRadEntry(?, ?, ?, ?, , , ?)}");
cstmt.setString(1,"Nappa");
cstmt.setInt(2,40);
cstmt.setString(3,"Vegeta");
cstmt.setString(4,"Saiyan");
cstmt.setString(5,"Hello"); 

cstmt.execute();

Java Stored Proc Not Executing

4 Answers4