Andrea Tarocchi created CAMEL-11716:
---------------------------------------
Summary: error in handling return parameters in db functions
Key: CAMEL-11716
URL: https://issues.apache.org/jira/browse/CAMEL-11716
Project: Camel
Issue Type: Bug
Components: camel-sql
Reporter: Andrea Tarocchi
Let's consider for instance the following db function:
{code}
create or replace function sum_two_numbers_function
(a in number, b in number)
return number deterministic is
begin
return a + b;
end;
{code}
One might think to run this camel route in order to call the mentioned db
function:
{code}
sql-store:sum_two_numbers_function(INTEGER ${body[0]}, INTEGER
${body[1]})?function=true
{code}
but SqlCall build incorect query:
{code}
? = call sum_two_numbers_function(?)
{code}
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.
{code}
sql-store:sum_two_numbers_function(INTEGER ${body[2]}, INTEGER ${body[0]},
INTEGER ${body[1]})?function=true
{code}
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:
{code}
sql-store:sum_two_numbers_function(OUT INTEGER result, INTEGER ${body[0]},
INTEGER ${body[1]})?function=true
{code}
results in this query call:
{code}
call sum_two_numbers_function(?, ?, ?)
{code}
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
