Uploaded image for project: 'Apache NiFi'
  1. Apache NiFi
  2. NIFI-4352

Add CLOB and NCLOB support to PutSQL and ExecuteSQL

    Details

    • Type: Improvement
    • Status: Resolved
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 1.3.0
    • Fix Version/s: 1.4.0
    • Component/s: Extensions
    • Labels:
      None

      Description

      Hello All,

      I am passing sql.args.1.type = 2005 and sql.args.1.value as a CLOB value to PutSQL and it is throwing below error. How do I resolve this error?

      2017-09-04 10:17:24,924 INFO [StandardProcessScheduler Thread-5] o.a.n.c.s.TimerDrivenSchedulingAgent Scheduled PutSQL[id=69534f73-821e-1782-ffff-fffffabb94fb] to run with 1 threads
      2017-09-04 10:17:25,009 ERROR [Timer-Driven Process Thread-4] o.apache.nifi.processors.standard.PutSQL PutSQL[id=69534f73-821e-1782-ffff-fffffabb94fb] org.apache.nifi.processors.standard.PutSQL$$Lambda$653/696434096@771333e failed to process due to org.apache.nifi.processor.exception.ProcessException: Failed to process StandardFlowFileRecord[uuid=14895cdd-3eac-4f2a-adae-f014526e24b1,claim=StandardContentClaim [resourceClaim=StandardResourceClaim[id=1502123289026-125, container=default, section=125], offset=71441, length=39],offset=0,name=5936554321553480,size=39] due to java.lang.ClassCastException: java.lang.String cannot be cast to oracle.sql.CLOB; rolling back session: {}
      org.apache.nifi.processor.exception.ProcessException: Failed to process StandardFlowFileRecord[uuid=14895cdd-3eac-4f2a-adae-f014526e24b1,claim=StandardContentClaim [resourceClaim=StandardResourceClaim[id=1502123289026-125, container=default, section=125], offset=71441, length=39],offset=0,name=5936554321553480,size=39] due to java.lang.ClassCastException: java.lang.String cannot be cast to oracle.sql.CLOB
      at org.apache.nifi.processor.util.pattern.ExceptionHandler.lambda$createOnGroupError$14(ExceptionHandler.java:226)
      at org.apache.nifi.processor.util.pattern.ExceptionHandler.lambda$createOnError$13(ExceptionHandler.java:179)
      at org.apache.nifi.processor.util.pattern.ExceptionHandler$OnError.lambda$andThen$11(ExceptionHandler.java:54)
      at org.apache.nifi.processor.util.pattern.ExceptionHandler$OnError.lambda$andThen$11(ExceptionHandler.java:54)
      at org.apache.nifi.processor.util.pattern.ExceptionHandler.execute(ExceptionHandler.java:148)
      at org.apache.nifi.processors.standard.PutSQL.lambda$new$72(PutSQL.java:283)
      at org.apache.nifi.processors.standard.PutSQL.lambda$new$75(PutSQL.java:324)
      at org.apache.nifi.processor.util.pattern.PutGroup.putFlowFiles(PutGroup.java:91)
      at org.apache.nifi.processor.util.pattern.Put.onTrigger(Put.java:101)
      at org.apache.nifi.processors.standard.PutSQL.lambda$onTrigger$86(PutSQL.java:544)
      at org.apache.nifi.processor.util.pattern.PartialFunctions.onTrigger(PartialFunctions.java:114)
      at org.apache.nifi.processor.util.pattern.RollbackOnFailure.onTrigger(RollbackOnFailure.java:184)
      at org.apache.nifi.processors.standard.PutSQL.onTrigger(PutSQL.java:544)
      at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1120)
      at org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:147)
      at org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:47)
      at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:132)
      at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
      at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308)
      at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180)
      at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294)
      at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
      at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
      at java.lang.Thread.run(Thread.java:748)
      Caused by: java.lang.ClassCastException: java.lang.String cannot be cast to oracle.sql.CLOB
      at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:8874)
      at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8396)
      at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:8980)
      at oracle.jdbc.driver.OraclePreparedStatementWrapper.setObject(OraclePreparedStatementWrapper.java:242)
      at org.apache.commons.dbcp.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:166)
      at org.apache.commons.dbcp.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:166)
      at org.apache.nifi.processors.standard.PutSQL.setParameter(PutSQL.java:888)
      at org.apache.nifi.processors.standard.PutSQL.setParameters(PutSQL.java:677)
      at org.apache.nifi.processors.standard.PutSQL.lambda$null$71(PutSQL.java:285)
      at org.apache.nifi.processor.util.pattern.ExceptionHandler.execute(ExceptionHandler.java:127)
      ... 19 common frames omitted

        Issue Links

          Activity

          Hide
          githubbot ASF GitHub Bot added a comment -

          Github user asfgit closed the pull request at:

          https://github.com/apache/nifi/pull/2145

          Show
          githubbot ASF GitHub Bot added a comment - Github user asfgit closed the pull request at: https://github.com/apache/nifi/pull/2145
          Hide
          jira-bot ASF subversion and git services added a comment -

          Commit 1e70e24267351a9c189ea26ee0536fdc8dbd7bd6 in nifi's branch refs/heads/master from Koji Kawamura
          [ https://git-wip-us.apache.org/repos/asf?p=nifi.git;h=1e70e24 ]

          NIFI-4352: Add CLOB and NCLOB support to PutSQL and ExecuteSQL

          Signed-off-by: Pierre Villard <pierre.villard.fr@gmail.com>

          This closes #2145.

          Show
          jira-bot ASF subversion and git services added a comment - Commit 1e70e24267351a9c189ea26ee0536fdc8dbd7bd6 in nifi's branch refs/heads/master from Koji Kawamura [ https://git-wip-us.apache.org/repos/asf?p=nifi.git;h=1e70e24 ] NIFI-4352 : Add CLOB and NCLOB support to PutSQL and ExecuteSQL Signed-off-by: Pierre Villard <pierre.villard.fr@gmail.com> This closes #2145.
          Hide
          githubbot ASF GitHub Bot added a comment -

          Github user pvillard31 commented on the issue:

          https://github.com/apache/nifi/pull/2145

          +1, ran full build with contrib-check and made few tests with provided template. Merging to master, thanks @ijokarumawak

          Show
          githubbot ASF GitHub Bot added a comment - Github user pvillard31 commented on the issue: https://github.com/apache/nifi/pull/2145 +1, ran full build with contrib-check and made few tests with provided template. Merging to master, thanks @ijokarumawak
          Hide
          githubbot ASF GitHub Bot added a comment -

          Github user ijokarumawak commented on the issue:

          https://github.com/apache/nifi/pull/2145

          Added followings:

          • CLOB and NCLOB support to PutSQL
          • NCLOB support to ExecuteSQL

          Confirmed it can insert and select CLOB and NCLOB columns. For NCLOB, I've tested Japanese characters are inserted and retrieved successfully.

          A template is available here in Gist:
          https://gist.github.com/ijokarumawak/451085b01349c7e6e83bcc42182ecffc

          ```
          CREATE TABLE clob_test (
          i INT not null,
          c CLOB,
          nc NCLOB,
          CONSTRAINT clob_test_pk PRIMARY KEY
          );
          ```

          Show
          githubbot ASF GitHub Bot added a comment - Github user ijokarumawak commented on the issue: https://github.com/apache/nifi/pull/2145 Added followings: CLOB and NCLOB support to PutSQL NCLOB support to ExecuteSQL Confirmed it can insert and select CLOB and NCLOB columns. For NCLOB, I've tested Japanese characters are inserted and retrieved successfully. A template is available here in Gist: https://gist.github.com/ijokarumawak/451085b01349c7e6e83bcc42182ecffc ``` CREATE TABLE clob_test ( i INT not null, c CLOB, nc NCLOB, CONSTRAINT clob_test_pk PRIMARY KEY ); ```
          Hide
          ijokarumawak Koji Kawamura added a comment -

          Removed Fix Version as we should set it when a JIRA is resolved.
          Modified JIRA title so that it describes well what we change.

          Show
          ijokarumawak Koji Kawamura added a comment - Removed Fix Version as we should set it when a JIRA is resolved. Modified JIRA title so that it describes well what we change.
          Hide
          githubbot ASF GitHub Bot added a comment -

          GitHub user ijokarumawak opened a pull request:

          https://github.com/apache/nifi/pull/2145

          NIFI-4352: Add CLOB and NCLOB support to PutSQL

          Thank you for submitting a contribution to Apache NiFi.

          In order to streamline the review of the contribution we ask you
          to ensure the following steps have been taken:

              1. For all changes:
          • [x] Is there a JIRA ticket associated with this PR? Is it referenced
            in the commit message?
          • [x] Does your PR title start with NIFI-XXXX where XXXX is the JIRA number you are trying to resolve? Pay particular attention to the hyphen "-" character.
          • [x] Has your PR been rebased against the latest commit within the target branch (typically master)?
          • [x] Is your initial contribution a single, squashed commit?
              1. For code changes:
          • [ ] Have you ensured that the full suite of tests is executed via mvn -Pcontrib-check clean install at the root nifi folder?
          • [ ] Have you written or updated unit tests to verify your changes?
          • [ ] If adding new dependencies to the code, are these dependencies licensed in a way that is compatible for inclusion under [ASF 2.0](http://www.apache.org/legal/resolved.html#category-a)?
          • [ ] If applicable, have you updated the LICENSE file, including the main LICENSE file under nifi-assembly?
          • [ ] If applicable, have you updated the NOTICE file, including the main NOTICE file found under nifi-assembly?
          • [ ] If adding new Properties, have you added .displayName in addition to .name (programmatic access) for each of the new properties?
              1. For documentation related changes:
          • [ ] Have you ensured that format looks appropriate for the output in which it is rendered?
              1. Note:
                Please ensure that once the PR is submitted, you check travis-ci for build issues and submit an update to your PR as soon as possible.

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

          $ git pull https://github.com/ijokarumawak/nifi nifi-4352

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

          https://github.com/apache/nifi/pull/2145.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 #2145


          commit d520c0041f3c31876de00fc9c1213f5c7d25eeda
          Author: Koji Kawamura <ijokarumawak@apache.org>
          Date: 2017-09-12T02:37:12Z

          NIFI-4352: Add CLOB and NCLOB support to PutSQL


          Show
          githubbot ASF GitHub Bot added a comment - GitHub user ijokarumawak opened a pull request: https://github.com/apache/nifi/pull/2145 NIFI-4352 : Add CLOB and NCLOB support to PutSQL Thank you for submitting a contribution to Apache NiFi. In order to streamline the review of the contribution we ask you to ensure the following steps have been taken: For all changes: [x] Is there a JIRA ticket associated with this PR? Is it referenced in the commit message? [x] Does your PR title start with NIFI-XXXX where XXXX is the JIRA number you are trying to resolve? Pay particular attention to the hyphen "-" character. [x] Has your PR been rebased against the latest commit within the target branch (typically master)? [x] Is your initial contribution a single, squashed commit? For code changes: [ ] Have you ensured that the full suite of tests is executed via mvn -Pcontrib-check clean install at the root nifi folder? [ ] Have you written or updated unit tests to verify your changes? [ ] If adding new dependencies to the code, are these dependencies licensed in a way that is compatible for inclusion under [ASF 2.0] ( http://www.apache.org/legal/resolved.html#category-a)? [ ] If applicable, have you updated the LICENSE file, including the main LICENSE file under nifi-assembly? [ ] If applicable, have you updated the NOTICE file, including the main NOTICE file found under nifi-assembly? [ ] If adding new Properties, have you added .displayName in addition to .name (programmatic access) for each of the new properties? For documentation related changes: [ ] Have you ensured that format looks appropriate for the output in which it is rendered? Note: Please ensure that once the PR is submitted, you check travis-ci for build issues and submit an update to your PR as soon as possible. You can merge this pull request into a Git repository by running: $ git pull https://github.com/ijokarumawak/nifi nifi-4352 Alternatively you can review and apply these changes as the patch at: https://github.com/apache/nifi/pull/2145.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 #2145 commit d520c0041f3c31876de00fc9c1213f5c7d25eeda Author: Koji Kawamura <ijokarumawak@apache.org> Date: 2017-09-12T02:37:12Z NIFI-4352 : Add CLOB and NCLOB support to PutSQL

            People

            • Assignee:
              ijokarumawak Koji Kawamura
              Reporter:
              mayank2711 Mayank Rathi
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development