Using default values of in parameter of sp through java

[vinayakshukre]

I have a stored procedure in oracle which has 2 in parameter and 1 out parameter. One of the in parameters has default value given.
I want to call this procedure through java without specifying value for the in parameter which has default value given.
Any idea, how to do this ?

I am using classes12.jar and jdk1.5 .

[verruckt24]

This code works for MS-SQL, but you are using Oracle and since the SQL Syntax can be vendor specific you will have to check.

// Here this code shows two parameters being passed to the sp
// Here con = connection,proc = CallableStatement
proc = con.prepareCall("{call sp_SPNAME (?,?) }");

proc.setString(1, var1);

proc.setString(2, var2);

proc.execute();

// Now if one of the parameters has a default value all you need to do is, remove one of the '?' and pass just one parameter.
proc = con.prepareCall("{call spSPNAME (?) }");
proc.setString(1, var1);
proc.execute();

Also you need to take care in the SP, to insert just this one parameter correctly and you do not need to insert the other one since as you say it is default.

[vinayakshukre]

it worked for me....

Thanks a ton.

Only limitation I found .... in parameter which has default value must be the last one in the param list.... I still finding a way to do this when it is not the last one....or if I have two in params with default value and for one of it I have value given in the call and for another I want to use the default value...

[verruckt24]

>parameter which has default value must be the last one in the param list
No not necessarily, if your first argument is default in the procedure just put a ',' without specifying any '?'

proc = con.prepareCall("{call sp_SPNAME (,?) }");

[~s.o.s~]

> Only limitation I found .... in parameter which has default value must
> be the last one in the param list

AFAIK, this is not a limitation but a logically sound decision. If this wasn't the case, it would be difficult to distinguish between an invocation which uses default parameter values and one which doesn't.

[vinayakshukre]

Thanks for spending time on my issue...
Here is what happened when I tried this solution...

My procedure is

create procedure deftest2
@in1 int =5,
@in2 int =4
as
	insert into dummydata values(@in1,@in2,20);

My java code is

String sCallQuery = "{ call deftest2 (,?)}";
CallableStatement cstmt= con.prepareCall(sCallQuery);		
cstmt.setInt(1,100);	
cstmt.execute();

I get following error

java.sql.SQLException: Incorrect syntax near ','.
	at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:365)
	at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2781)
	at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2224)
	at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:628)
	at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:525)
	at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:487)
	at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.execute(JtdsPreparedStatement.java:478)
	at ProcTest.main(ProcTest.java:152)

I am trying this now on MS SQL with JTDS driver.
Here I was expecting it to consider value 100 for in2 and consider default 5 for in1. That does not happen.

Please let me know where I am going wrong.
Is there any document or tutorial which explains about use of default values through java ?

Does same way applies when I have output parameter ?

[vinayakshukre]

Instead of manipulating '?' in the call query, if parameters names are used to set the parameter value then it works.
e.g.
cstmt.setInt("in2",2);

This works for MS SQL + JTDS . But for oracle + classes12 it does not.

Thanks again.

[verruckt24]

No I guess here the problem is not with the name or the integer stuff in the call parameter. What you were doing when calling with the parameter number is this : cstmt.setInt(1,100); which according to code should have been this cstmt.setInt(2,100); I guess if you try this you would be able to sort that out. Just try it.

[vinayakshukre]

No I guess here the problem is not with the name or the integer stuff in the call parameter. What you were doing when calling with the parameter number is this : cstmt.setInt(1,100); which according to code should have been this cstmt.setInt(2,100); I guess if you try this you would be able to sort that out. Just try it.

Even that does not work. I had tried it..