Geek Answers Handbook

As in JDBC, you can call a stored procedure when you set only some parameters

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

If I just used SQL, I could set the parameters in the SQL name in SQL to call sproc. For instance. 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 (in particular, jConnect 6.0), it seems that when using CallableStatement you need to set the parameters by their integer index, and not by name. If I try to create a CallableStatement for the above stored procedure, with 9 parameters and only set parameters 1,2 and 9, for example:

 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 that I am trying to do, I need to create a process that receives product information from the IBM MQ thread and then creates the product in a third application. A third-party application uses Sybase to store data, and to create a product I need to call a stored procedure containing about 130 parameters. For the type of product I need to create, I need to install only about 15 of these parameters, the rest will remain by default.

The options I reviewed are as follows:

  • Creating a custom stored procedure that sets only those values ​​that I need and then calls a third-party sproc product.
  • Setting default values ​​for all parameters in Java.

Could there really be an easier way to do this?

+4
source share
4 answers

This feature is not supported by JDBC. You will need to create an SQL string and execute this:

 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 does not try to understand the SQL that you send to the database, with the exception of some special characters, such as {} and ? . I once wrote a JDBC database that would accept JavaScript fragments as "SQL": I just implemented a DataSource , Connection and ResultSet , and I could query the application's memory model using the JDBC interface, but with JavaScript as the query language.

+6
source

You can try hardcoding empty or empty in a string

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

0
source 
 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();
0
source

//abobjects.com The following 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

 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
0
source

More articles:


All Articles