4

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?

kassak
  • 3,974
  • 1
  • 25
  • 36
JonnyWizz
  • 170
  • 1
  • 2
  • 8

4 Answers4

7

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.

Aaron Digulla
  • 321,842
  • 108
  • 597
  • 820
  • 2
    Just for anyone else needing the example above, the '\n' in the String sql can be replaced with just a space. Sybase doesn't need the parameters to be listed on a separate line, they only have to be separated by a comma. – alaniane Dec 06 '17 at 23:05
  • @alaniane Correct. I copied the example from some code where I also log the statements. The newlines are just to help humans undrestand the SQL. – Aaron Digulla Dec 08 '17 at 14:36
0

You can try hard coding null or blank in the String

"{call my_stored_procedure (?, ?, null, null, null, null , null, null, ?)}"

Subir Kumar Sao
  • 8,171
  • 3
  • 26
  • 47
  • 1
    If I specify null, the parameter doesn't get set to the default value in the Sybase stored procedure. – JonnyWizz Aug 09 '12 at 10:06
0
myStoredProcedureCall.setString(9, "NINE");

in above code index no 9 but it will be 3 because your parameter sequence start from 1 ;
Alhrough  you can another sql exception.

I think you should use 

myStoredProcedureCall = 
  sybConn.prepareCall("{call my_stored_procedure (?, ?, null, null, null, null , null, null, ?)}");
myStoredProcedureCall.setString(1, "ONE");
myStoredProcedureCall.setString(2, "TWO");
myStoredProcedureCall.setString(3, "NINE");
ResultSet paramResults = myStoredProcedureCall.executeQuery();
Mohammod Hossain
  • 4,134
  • 2
  • 26
  • 37
0

//abobjects.com Below works for me def getIDGEN( sTableName ):
proc = db.prepareCall("{ ? = call DB.dbo.usp_IDGEN_string_v6(?, ?) }");

proc.registerOutParameter(3, types.INTEGER)
proc.setString(2, sTableName)
proc.setInt(3, 0)   
proc.execute()
li_RI_ID = proc.getInt(3)
print str(li_RI_ID) + " obtained from usp_IDGEN_string_v6"
return li_RI_ID

my sproc is 

create proc usp_IDGEN_string_v6 (@tablename  varchar(32) , @ID  numeric )
as
declare @seq     numeric
declare @nextID  numeric
select @nextID=IDGEN_ID from DB_IDGEN where IDGEN_TableName = @tablename
select @seq=@nextID + 1 from DB_IDGEN where IDGEN_Table
 Name = @tablename
update DB_IDGEN set IDGEN_ID = @seq where IDGEN_TableName = @tablename
select @ID  = @seq
return @ID
abobjects.com
  • 131
  • 5