Announcement Announcement Module
No announcement yet.
springjdbccall parameter caching Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • springjdbccall parameter caching


    We are using springjdbccall to execute stored procedures in oracle database. I was reusing the same springjdbccall to call multiple stored procedures. For example:

    stored proc 1 - createCustomer
    parameter - customer_type (oracle type)

    stored proc 2 - loadCustomer
    parameter - id (number)

    The issue we were facing is, when executing the second stored procedure (load_customer), the simplejdbccall was still holding on to the parameter (customer_type) from the first stored proc call (load_customer). So we would get an exception saying in or out parameter missing on the second stored proc call.

    I want to know how to clear the parameters before making a call to another stored procedure.

    I worked around this by instantiating a new simplejdbccall for every stored proc call. But this is costly.

    Please let me know your suggestions.

    Reply With Quote

  • #2
    You should create one SimpleJDbcCall for each stored procedure you are calling. You can do that in your initialization code and each time you call you just pass in a new set of parameter values.


    • #3
      simplejdbccall parameter caching

      Thanks for your reply. This approach of creating a new simplejdbc call for every stored proc is affecting performance. I guess it would be great if we can clear the parameters and reuse it.



      • #4
        How many different stored procedures do you call?

        If you want to create the calls dynamically then you should use the directly since that has less overhead. Each SimpleJdbcCall does some metadata look-up (unless you disable it) so it's recommended to create each SimpleJdbcCall once in your initialization code.


        • #5
          Hi trisberg,
          Regarding caching SimpleJdbcCalls - I wanted to implement a Cache that caches these objects per function name (using pgsql).
          However, I see that doExecute calls "checkCompiled" which in turns performs the compilation ,and needs the passed parameter for the call. If this is the case , and I'm using a multi threaded program where thread A would like to make a call like "select * from foo('hello')" and thead B would like to make a call like "select * from foo('world')" - both ese calls shoudl use the same cached SimpleJdbcCall but they need to use different parmaeters. Can you elaborate more on how a proper caching should be done in this case?

          Kind regards,


          • #6
            You really should use a bind variable using something like "select * from foo(?)" passing in the parameter value for each call. But this doesn't look like a stored procedure call, it looks like a query call using the JdbcTemplate.