Camel
  1. Camel
  2. CAMEL-4725

camel-sql - Add support for Callable statements

    Details

    • Type: New Feature New Feature
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 2.8.3
    • Fix Version/s: 2.17.0
    • Component/s: camel-sql
    • Labels:
      None
    • Estimated Complexity:
      Unknown

      Description

      See Nabble for details.

        Issue Links

          Activity

          Hide
          Christian Müller added a comment -

          We need hsqldb 2.2.0+ to test callable statements

          Show
          Christian Müller added a comment - We need hsqldb 2.2.0+ to test callable statements
          Hide
          James Carman added a comment -

          You could also switch to H2. I have used that to do SPROC testing.

          Show
          James Carman added a comment - You could also switch to H2. I have used that to do SPROC testing.
          Hide
          Claus Ibsen added a comment -

          This would be cool.

          Show
          Claus Ibsen added a comment - This would be cool.
          Hide
          Claus Ibsen added a comment -

          Christian do you work on this?
          If not maybe someone from the community want to help work on this?

          Using stored procedures with IN / OUT and cursor types would be great. So far I think its only camel-mybatis that supports stored procedures "natively" with Camel.

          Show
          Claus Ibsen added a comment - Christian do you work on this? If not maybe someone from the community want to help work on this? Using stored procedures with IN / OUT and cursor types would be great. So far I think its only camel-mybatis that supports stored procedures "natively" with Camel.
          Hide
          Christian Müller added a comment -

          I unassigned the ticket because I cannot work on it at present. Feel free to grab the ticket if you (not only Claus ) want to work on it.

          Show
          Christian Müller added a comment - I unassigned the ticket because I cannot work on it at present. Feel free to grab the ticket if you (not only Claus ) want to work on it.
          Hide
          Diogenes Rettori added a comment -

          Can someone bring this back to life?

          Show
          Diogenes Rettori added a comment - Can someone bring this back to life?
          Hide
          Arno Noordover added a comment - - edited

          Does anybody have an idea how to implement this?
          When I compare it with the donkey-esb you need multiple parts:

          1. the object containing the data to be used in calling the stored procedure;
          2. a template call with bind-variables;
          3. mapping between bind-variables and the object which should contain some kind of EL.

          What part can be skipped?
          How can we map between object and variable in stored-proc?
          Is it readable maintainable when you need to call a complex stored-proc and need to build a complex route using the camel facilities?

          Show
          Arno Noordover added a comment - - edited Does anybody have an idea how to implement this? When I compare it with the donkey-esb you need multiple parts: the object containing the data to be used in calling the stored procedure; a template call with bind-variables; mapping between bind-variables and the object which should contain some kind of EL. What part can be skipped? How can we map between object and variable in stored-proc? Is it readable maintainable when you need to call a complex stored-proc and need to build a complex route using the camel facilities?
          Hide
          Arno Noordover added a comment -

          Would it be a good idea to create a wrapper around a spring jdbc class which can be used to call a stored procedure (http://docs.spring.io/spring/docs/current/javadoc-api/org/springframework/jdbc/object/StoredProcedure.html). This wrapper could be instantiated with the string to be used for calling the stored-proc. Also a hashmap with the bind-variables as a key and a "simple" language to be executed for each variable as the value could be provided.
          The wrapper bean should have a method with the exchange as the input parameter. In this method you can use the simple language to set all the variables.

          For easy usage a userdefined namespace could be created as well.

          Show
          Arno Noordover added a comment - Would it be a good idea to create a wrapper around a spring jdbc class which can be used to call a stored procedure ( http://docs.spring.io/spring/docs/current/javadoc-api/org/springframework/jdbc/object/StoredProcedure.html ). This wrapper could be instantiated with the string to be used for calling the stored-proc. Also a hashmap with the bind-variables as a key and a "simple" language to be executed for each variable as the value could be provided. The wrapper bean should have a method with the exchange as the input parameter. In this method you can use the simple language to set all the variables. For easy usage a userdefined namespace could be created as well.
          Hide
          Claus Ibsen added a comment -

          Yeah as camel-sql uses spring-jdbc, so it seems like a good idea to do that.

          You are surely welcome to dive into the code and attempt to implement this. We love contributions
          http://camel.apache.org/contributing.html

          And this ticket is a popular ticket so the community would like stored procedure support in this component.

          Show
          Claus Ibsen added a comment - Yeah as camel-sql uses spring-jdbc, so it seems like a good idea to do that. You are surely welcome to dive into the code and attempt to implement this. We love contributions http://camel.apache.org/contributing.html And this ticket is a popular ticket so the community would like stored procedure support in this component.
          Hide
          Brad Powell added a comment -

          The following bean is very close to this requested functionality:
          https://github.com/bapowell/camel-stored-procedure

          Show
          Brad Powell added a comment - The following bean is very close to this requested functionality: https://github.com/bapowell/camel-stored-procedure
          Hide
          ASF GitHub Bot added a comment -

          GitHub user snurmine opened a pull request:

          https://github.com/apache/camel/pull/749

          CAMEL-4725 camel-sql - Add support for Callable statements

          Here is demo of how calling stored procedure could work. I call them Simple Stored Procedure Templates. Current solution is just a proof-of-concept and has style, bugs, missing features etc. issues. I provide this PR just for discussion.

          Idea is that instead of SQL-query, a simple template is used to describe stored procedure. From parsed template, subclass of org.springframework.jdbc.object.StoredProcedure can then be constructed by information given in the template. For example

          ```javascript
          sql:sspt:ADDNUMBERS(INTEGER VALUE1 $

          {headers.num1}

          ,INTEGER VALUE2 $

          {headers" + ".num2}

          ,OUT INTEGER RESULT resultofsum)
          ```

          Here Producer would call ADDNUMBERS stored procedure using parameters VALUE1 and VALUE2.
          Values are fetched from headers "num1" and "num2". Output (INTEGER) is stored into header "resultofsum". Here INTEGER refers to java.sql.Types field INTEGER. Parameter order seems to be meaningfull, but names a still given.

          Test org.apache.camel.component.sql.sspt.ProducerTest shows how template is used in Procuder.

          I can polish solution further if this approach seems okey.

          You can merge this pull request into a Git repository by running:

          $ git pull https://github.com/snurmine/camel CAMEL-4725

          Alternatively you can review and apply these changes as the patch at:

          https://github.com/apache/camel/pull/749.patch

          To close this pull request, make a commit to your master/trunk branch
          with (at least) the following in the commit message:

          This closes #749


          commit 281a10a22436a679f2f51c57a2175cdf6535f65f
          Author: Sami Nurminen <snurmine@gmail.com>
          Date: 2015-12-20T21:34:33Z

          CAMEL-4725 camel-sql - Add support for Callable statements


          Show
          ASF GitHub Bot added a comment - GitHub user snurmine opened a pull request: https://github.com/apache/camel/pull/749 CAMEL-4725 camel-sql - Add support for Callable statements Here is demo of how calling stored procedure could work. I call them Simple Stored Procedure Templates. Current solution is just a proof-of-concept and has style, bugs, missing features etc. issues. I provide this PR just for discussion. Idea is that instead of SQL-query, a simple template is used to describe stored procedure. From parsed template, subclass of org.springframework.jdbc.object.StoredProcedure can then be constructed by information given in the template. For example ```javascript sql:sspt:ADDNUMBERS(INTEGER VALUE1 $ {headers.num1} ,INTEGER VALUE2 $ {headers" + ".num2} ,OUT INTEGER RESULT resultofsum) ``` Here Producer would call ADDNUMBERS stored procedure using parameters VALUE1 and VALUE2. Values are fetched from headers "num1" and "num2". Output (INTEGER) is stored into header "resultofsum". Here INTEGER refers to java.sql.Types field INTEGER. Parameter order seems to be meaningfull, but names a still given. Test org.apache.camel.component.sql.sspt.ProducerTest shows how template is used in Procuder. I can polish solution further if this approach seems okey. You can merge this pull request into a Git repository by running: $ git pull https://github.com/snurmine/camel CAMEL-4725 Alternatively you can review and apply these changes as the patch at: https://github.com/apache/camel/pull/749.patch To close this pull request, make a commit to your master/trunk branch with (at least) the following in the commit message: This closes #749 commit 281a10a22436a679f2f51c57a2175cdf6535f65f Author: Sami Nurminen <snurmine@gmail.com> Date: 2015-12-20T21:34:33Z CAMEL-4725 camel-sql - Add support for Callable statements
          Hide
          Claus Ibsen added a comment -

          I wonder how well JavaCC is maintained?

          And we should have a way of generating the source as part of the build, so we are not stuck on some manual procedure to install javacc and run it from command line. Isn't there a maven plugin for javacc ?

          Also I think it should be put in a separate component named sql-stored, or sql-sp etc. It should stay in camel-sql, but as a new component. We can do this later to avoid the code changes to include both at the same time to confuse.

          But interesting idea to use a grammar. So I suggest keep on hacking on this.

          Show
          Claus Ibsen added a comment - I wonder how well JavaCC is maintained? And we should have a way of generating the source as part of the build, so we are not stuck on some manual procedure to install javacc and run it from command line. Isn't there a maven plugin for javacc ? Also I think it should be put in a separate component named sql-stored, or sql-sp etc. It should stay in camel-sql, but as a new component. We can do this later to avoid the code changes to include both at the same time to confuse. But interesting idea to use a grammar. So I suggest keep on hacking on this.
          Hide
          Claus Ibsen added a comment -

          Ah yeah there is a maven plugin, activemq uses javacc for its jms select parser.
          https://github.com/apache/activemq/blob/master/activemq-client/pom.xml#L220

          Show
          Claus Ibsen added a comment - Ah yeah there is a maven plugin, activemq uses javacc for its jms select parser. https://github.com/apache/activemq/blob/master/activemq-client/pom.xml#L220
          Hide
          Claus Ibsen added a comment -

          First work now pushed to master.

          There is new PR's coming with more enhancements to the component but it works now for calling stored procedures as a producer.

          Show
          Claus Ibsen added a comment - First work now pushed to master. There is new PR's coming with more enhancements to the component but it works now for calling stored procedures as a producer.
          Hide
          ASF GitHub Bot added a comment -

          Github user snurmine closed the pull request at:

          https://github.com/apache/camel/pull/749

          Show
          ASF GitHub Bot added a comment - Github user snurmine closed the pull request at: https://github.com/apache/camel/pull/749
          Hide
          ASF GitHub Bot added a comment -

          GitHub user snurmine opened a pull request:

          https://github.com/apache/camel/pull/759

          CAMEL-4725: Batch support. Single Producer for both SQL-components.

          Added batch support for StoredProcedures. It uses Spring API as much as possible.

          StoredProcedure can be read from message body and parameters are read from header.
          Only Map is supported as parameter in this case.

          Refactored SqlProducer to support both CallableStatement and PreparedStatement. New name is DefaultSqlProducer(as it is now more general class). Adapters for PreparedStatement, CallableStatement and CallableStatement batch mode are in "wrapper"-package.

          Moved parameters only sensible for SQL component into SqlComponent and
          left common properties into DefaultSqlComponent.

          There are a lot of changes to avoid duplicate logic in code between CallableStament and PreparedStatement implementations.

          You can merge this pull request into a Git repository by running:

          $ git pull https://github.com/snurmine/camel CAMEL-4725

          Alternatively you can review and apply these changes as the patch at:

          https://github.com/apache/camel/pull/759.patch

          To close this pull request, make a commit to your master/trunk branch
          with (at least) the following in the commit message:

          This closes #759


          commit 2a7ae3c8da7c43543b610638d0f8261d879c1b7c
          Author: Sami Nurminen <snurmine@gmail.com>
          Date: 2016-01-09T10:18:27Z

          CAMEL-4725: Batch support. Single Producer for both SQL-components.


          Show
          ASF GitHub Bot added a comment - GitHub user snurmine opened a pull request: https://github.com/apache/camel/pull/759 CAMEL-4725 : Batch support. Single Producer for both SQL-components. Added batch support for StoredProcedures. It uses Spring API as much as possible. StoredProcedure can be read from message body and parameters are read from header. Only Map is supported as parameter in this case. Refactored SqlProducer to support both CallableStatement and PreparedStatement. New name is DefaultSqlProducer(as it is now more general class). Adapters for PreparedStatement, CallableStatement and CallableStatement batch mode are in "wrapper"-package. Moved parameters only sensible for SQL component into SqlComponent and left common properties into DefaultSqlComponent. There are a lot of changes to avoid duplicate logic in code between CallableStament and PreparedStatement implementations. You can merge this pull request into a Git repository by running: $ git pull https://github.com/snurmine/camel CAMEL-4725 Alternatively you can review and apply these changes as the patch at: https://github.com/apache/camel/pull/759.patch To close this pull request, make a commit to your master/trunk branch with (at least) the following in the commit message: This closes #759 commit 2a7ae3c8da7c43543b610638d0f8261d879c1b7c Author: Sami Nurminen <snurmine@gmail.com> Date: 2016-01-09T10:18:27Z CAMEL-4725 : Batch support. Single Producer for both SQL-components.
          Hide
          ASF GitHub Bot added a comment -

          Github user snurmine closed the pull request at:

          https://github.com/apache/camel/pull/759

          Show
          ASF GitHub Bot added a comment - Github user snurmine closed the pull request at: https://github.com/apache/camel/pull/759
          Hide
          ASF GitHub Bot added a comment -

          GitHub user snurmine opened a pull request:

          https://github.com/apache/camel/pull/771

          CAMEL-4725:Vendor spesific SQL type and removed type converters.

          -Added ability to give SQL type code as integer in template instead of String. This enables use Vendor specific SQL type.
          -Removed SQL type to Java type mappings from Simple expression evaluations.
          Component cannot know exact type per vendor, so value from Simple expression is asked using Object.class.
          -Some minor cleaning

          You can merge this pull request into a Git repository by running:

          $ git pull https://github.com/snurmine/camel CAMEL-4725

          Alternatively you can review and apply these changes as the patch at:

          https://github.com/apache/camel/pull/771.patch

          To close this pull request, make a commit to your master/trunk branch
          with (at least) the following in the commit message:

          This closes #771


          commit 3e94ae2ad843a702ff84edace3fad799dbeb5dfb
          Author: Sami Nurminen <snurmine@gmail.com>
          Date: 2016-01-14T17:30:45Z

          CAMEL-4725:Vendor spesific SQL type. Removed type conversions per SQL type.


          Show
          ASF GitHub Bot added a comment - GitHub user snurmine opened a pull request: https://github.com/apache/camel/pull/771 CAMEL-4725 :Vendor spesific SQL type and removed type converters. -Added ability to give SQL type code as integer in template instead of String. This enables use Vendor specific SQL type. -Removed SQL type to Java type mappings from Simple expression evaluations. Component cannot know exact type per vendor, so value from Simple expression is asked using Object.class. -Some minor cleaning You can merge this pull request into a Git repository by running: $ git pull https://github.com/snurmine/camel CAMEL-4725 Alternatively you can review and apply these changes as the patch at: https://github.com/apache/camel/pull/771.patch To close this pull request, make a commit to your master/trunk branch with (at least) the following in the commit message: This closes #771 commit 3e94ae2ad843a702ff84edace3fad799dbeb5dfb Author: Sami Nurminen <snurmine@gmail.com> Date: 2016-01-14T17:30:45Z CAMEL-4725 :Vendor spesific SQL type. Removed type conversions per SQL type.
          Hide
          ASF GitHub Bot added a comment -

          Github user asfgit closed the pull request at:

          https://github.com/apache/camel/pull/771

          Show
          ASF GitHub Bot added a comment - Github user asfgit closed the pull request at: https://github.com/apache/camel/pull/771
          Hide
          Claus Ibsen added a comment -

          We need to add documentation for this.

          Maybe a new page for sql-stored

          Show
          Claus Ibsen added a comment - We need to add documentation for this. Maybe a new page for sql-stored
          Hide
          Claus Ibsen added a comment -
          Show
          Claus Ibsen added a comment - I added a page for the documentation https://cwiki.apache.org/confluence/display/CAMEL/SQL+Stored+Procedure

            People

            • Assignee:
              Claus Ibsen
              Reporter:
              Christian Müller
            • Votes:
              9 Vote for this issue
              Watchers:
              16 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development