How in JDBC can you call a stored procedure when only setting some parameters

Question

What is the best way to make a call to a stored procedure using JDBC if you only want to set some of the parameters?

If I was just using SQL, I could set the paramerers by name in the SQL to call the sproc. E.g. if I have a stored procedure with nine parameters and I wanted to set parameters 1,2 and 9, leaving the rest to their default values, I can run this SQL:

exec my_stored_procedure
    @parameter_1       = "ONE",
    @parameter_2       = "TWO",
    @parameter_9       = "NINE"

Using JDBC (Specifically jConnect 6.0), it seems that when using a CallableStatement, you have to set the parameters by their integer index, not their name. If I try the to create a CallableStatement for the above stored procedure, with 9 parameters, and only set parameters 1,2 and 9, like this:

myStoredProcedureCall = 
  sybConn.prepareCall("{call my_stored_procedure (?, ?, ?, ?, ?, ? , ?, ?, ?)}");
myStoredProcedureCall.setString(1, "ONE");
myStoredProcedureCall.setString(2, "TWO");
myStoredProcedureCall.setString(9, "NINE");
ResultSet paramResults = myStoredProcedureCall.executeQuery();

Then I get this SQLException thrown:

*** SQLException caught ***
SQLState: JZ0SA
Message:  JZ0SA: Prepared Statement: Input parameter not set, index: 2.
Vendor:   0

For some background on what I am trying to do, I need to create a process that receives information about products from a IBM MQ stream, and then creates a product in a 3rd application. The 3rd party application uses Sybase to store it's data, and to create a product I need to call a stored procedure that has about 130 parameters. For the type of product I need to create, only about 15 of these parameters need to be set, the rest will be left to the default values.

Options I have considered are:

• Creating a custom stored procedure that sets only the values that I need, then calls the 3rd party product sproc.
• Setting default values for all parameters in the Java.

Surely there must be an easier way to do this?

Solution

This feature isn't supported by JDBC. You will have to create an SQL string and execute that:

String sql = "exec my_stored_procedure\n@parameter_1 = ?,\n@parameter_2 = ?,\n@parameter_9 = ?";

PreparedStatement stmt = ...
stmt.setString( 1, "ONE" );
stmt.setString( 2, "TWO" );
stmt.setString( 3, "NINE" );
stmt.execute();

Remember: JDBC doesn't try to understand the SQL that you're sending to the database except for some special characters like {} and ?. I once wrote a JDBC "database" which would accept JavaScript snippets as "SQL": I simply implemented DataSource, Connection and ResultSet and I could query my application's memory model using the JDBC interface but with JavaScript as query language.

Comments

[JonnyWizz]

If I specify null, the parameter doesn't get set to the default value in the Sybase stored procedure.