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

error in handling return parameters in db functions

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Resolved
    • Major
    • Resolution: Fixed
    • 2.19.1
    • 2.18.5, 2.19.3, 2.20.0
    • camel-sql
    • None
    • 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

            acosentino Andrea Cosentino
            valdar Andrea Tarocchi
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: