Uploaded image for project: 'Camel'
  1. Camel
  2. CAMEL-11716

error in handling return parameters in db functions

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 2.19.1
    • Fix Version/s: 2.18.5, 2.19.3, 2.20.0
    • Component/s: camel-sql
    • Labels:
      None
    • Estimated Complexity:
      Unknown

      Description

      Let's consider for instance the following db function:

      create or replace function sum_two_numbers_function
      
        (a in number, b in number)
      
      return number deterministic is
      
      begin
      
       return a + b;
      
      end;
      

      One might think to run this camel route in order to call the mentioned db function:

      sql-store:sum_two_numbers_function(INTEGER ${body[0]}, INTEGER ${body[1]})?function=true
      

      but SqlCall build incorect query:

      ? = call sum_two_numbers_function(?)
      

      which fail on wrong number of arguments. After an investigation is found out that first parameter was skipped for function calls.
      https://github.com/spring-projects/spring-framework/blob/v4.3.3.RELEASE/spring-jdbc/src/main/java/org/springframework/jdbc/object/SqlCall.java#L135
      So one might try to add one parameter which should be used as output for return statement. But the only valid format of parameter (for db functions) seems to be InputParameter which is not possible to use as output for result.

      sql-store:sum_two_numbers_function(INTEGER ${body[2]}, INTEGER ${body[0]}, INTEGER ${body[1]})?function=true
      

      If OutputParameter are used than this https://github.com/apache/camel/blob/master/components/camel-sql/src/main/java/org/apache/camel/component/sql/stored/TemplateStoredProcedure.java#L77 will force to create SQL procedure call (instead of a function one), for instance this:

      sql-store:sum_two_numbers_function(OUT INTEGER result, INTEGER ${body[0]}, INTEGER ${body[1]})?function=true
      

      results in this query call:

      call sum_two_numbers_function(?, ?, ?)
      

        Attachments

          Activity

            People

            • Assignee:
              acosentino Andrea Cosentino
              Reporter:
              valdar Andrea Tarocchi
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: