[
https://issues.apache.org/jira/browse/NIFI-5643?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Diego Queiroz updated NIFI-5643:
--------------------------------
Description:
When using "Custom Query" to generate a query for Oracle, it wraps the query
with:
{noformat}
SELECT * FROM ( your_query ) AS TABLE_NAME WHERE 1 = 0{noformat}
The problem is that the " AS " sintax is invalid in Oracle (see
[https://www.techonthenet.com/oracle/alias.php]).
The correct syntax for Oracle is:
{noformat}
SELECT * FROM ( your_query ) TABLE_NAME WHERE 1 = 0{noformat}
Apparently, this is already solved by
*[NIFI-5471|https://git-wip-us.apache.org/repos/asf?p=nifi.git;a=blobdiff;f=nifi-nar-bundles/nifi-standard-bundle/nifi-standard-processors/src/main/java/org/apache/nifi/processors/standard/AbstractDatabaseFetchProcessor.java;h=3da8a73f184d4622bf969a3986804b531eac7f31;hp=e13f9de8369a6885e8e59b7ca875dd516ba3dc2a;hb=9742dd2fac42d4afb8b1e009535ed50c02373493;hpb=e97ae921f759e51ee1709ef0884fc029bd40d26b]*
in master, but if there are plans for a new release of 1.7.x, a fix for this
would be great.
I'll drop the log here to help people to find this thread:
{noformat}
2018-09-27 17:41:45,293 ERROR [Timer-Driven Process Thread-82]
o.a.n.p.standard.QueryDatabaseTable
QueryDatabaseTable[id=5dfe3f5d-69b8-1df7-1379-140a9e518ac3] Failed to process
session due to org.apache.nifi.processor.exception.ProcessException: Unable to
communicate with database in order to determine column types:
org.apache.nifi.processor.exception.ProcessException: Unable to communicate
with database in order to determine column types
org.apache.nifi.processor.exception.ProcessException: Unable to communicate
with database in order to determine column types
at
org.apache.nifi.processors.standard.AbstractDatabaseFetchProcessor.setup(AbstractDatabaseFetchProcessor.java:309)
at
org.apache.nifi.processors.standard.AbstractDatabaseFetchProcessor.setup(AbstractDatabaseFetchProcessor.java:232)
at
org.apache.nifi.processors.standard.QueryDatabaseTable.onTrigger(QueryDatabaseTable.java:232)
at
org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1165)
at
org.apache.nifi.controller.tasks.ConnectableTask.invoke(ConnectableTask.java:203)
at
org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:117)
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:1149)
at
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
Caused by: java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not
properly ended
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:195)
at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:876)
at
oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1175)
at
oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1296)
at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1498)
at
oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:406)
at
org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
at
org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
at
org.apache.nifi.processors.standard.AbstractDatabaseFetchProcessor.setup(AbstractDatabaseFetchProcessor.java:269)
... 12 common frames omitted{noformat}
was:
When using "Custom Query" to generate a query for Oracle, it wraps the query
with:
{noformat}
SELECT * FROM ( your_query ) AS TABLE_NAME WHERE 1 = 0{noformat}
The problem is that the " AS " sintax is invalid in Oracle (see
[https://www.techonthenet.com/oracle/alias.php]).
The correct syntax for Oracle is:
{noformat}
SELECT * FROM ( your_query ) TABLE_NAME WHERE 1 = 0{noformat}
Apparently, this is already solved in
*[NIFI-5471|https://github.com/apache/nifi/commit/9742dd2fac42d4afb8b1e009535ed50c02373493#diff-23f5f3a6652c943e1824efb3b9528d0d]*
(so master seems to be OK), but if there are plans for a new release of 1.7.x,
a fix for this would be great.
I'll drop the log here to help people to find this thread:
{noformat}
2018-09-27 17:41:45,293 ERROR [Timer-Driven Process Thread-82]
o.a.n.p.standard.QueryDatabaseTable
QueryDatabaseTable[id=5dfe3f5d-69b8-1df7-1379-140a9e518ac3] Failed to process
session due to org.apache.nifi.processor.exception.ProcessException: Unable to
communicate with database in order to determine column types:
org.apache.nifi.processor.exception.ProcessException: Unable to communicate
with database in order to determine column types
org.apache.nifi.processor.exception.ProcessException: Unable to communicate
with database in order to determine column types
at
org.apache.nifi.processors.standard.AbstractDatabaseFetchProcessor.setup(AbstractDatabaseFetchProcessor.java:309)
at
org.apache.nifi.processors.standard.AbstractDatabaseFetchProcessor.setup(AbstractDatabaseFetchProcessor.java:232)
at
org.apache.nifi.processors.standard.QueryDatabaseTable.onTrigger(QueryDatabaseTable.java:232)
at
org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1165)
at
org.apache.nifi.controller.tasks.ConnectableTask.invoke(ConnectableTask.java:203)
at
org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:117)
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:1149)
at
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
Caused by: java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not
properly ended
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:195)
at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:876)
at
oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1175)
at
oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1296)
at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1498)
at
oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:406)
at
org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
at
org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
at
org.apache.nifi.processors.standard.AbstractDatabaseFetchProcessor.setup(AbstractDatabaseFetchProcessor.java:269)
... 12 common frames omitted{noformat}
> QueryDatabaseTable custom query generates invalid Oracle queries
> ----------------------------------------------------------------
>
> Key: NIFI-5643
> URL: https://issues.apache.org/jira/browse/NIFI-5643
> Project: Apache NiFi
> Issue Type: Bug
> Components: Extensions
> Affects Versions: 1.7.1
> Reporter: Diego Queiroz
> Priority: Major
>
> When using "Custom Query" to generate a query for Oracle, it wraps the query
> with:
> {noformat}
> SELECT * FROM ( your_query ) AS TABLE_NAME WHERE 1 = 0{noformat}
> The problem is that the " AS " sintax is invalid in Oracle (see
> [https://www.techonthenet.com/oracle/alias.php]).
> The correct syntax for Oracle is:
> {noformat}
> SELECT * FROM ( your_query ) TABLE_NAME WHERE 1 = 0{noformat}
> Apparently, this is already solved by
> *[NIFI-5471|https://git-wip-us.apache.org/repos/asf?p=nifi.git;a=blobdiff;f=nifi-nar-bundles/nifi-standard-bundle/nifi-standard-processors/src/main/java/org/apache/nifi/processors/standard/AbstractDatabaseFetchProcessor.java;h=3da8a73f184d4622bf969a3986804b531eac7f31;hp=e13f9de8369a6885e8e59b7ca875dd516ba3dc2a;hb=9742dd2fac42d4afb8b1e009535ed50c02373493;hpb=e97ae921f759e51ee1709ef0884fc029bd40d26b]*
> in master, but if there are plans for a new release of 1.7.x, a fix for this
> would be great.
>
> I'll drop the log here to help people to find this thread:
> {noformat}
> 2018-09-27 17:41:45,293 ERROR [Timer-Driven Process Thread-82]
> o.a.n.p.standard.QueryDatabaseTable
> QueryDatabaseTable[id=5dfe3f5d-69b8-1df7-1379-140a9e518ac3] Failed to process
> session due to org.apache.nifi.processor.exception.ProcessException: Unable
> to communicate with database in order to determine column types:
> org.apache.nifi.processor.exception.ProcessException: Unable to communicate
> with database in order to determine column types
> org.apache.nifi.processor.exception.ProcessException: Unable to communicate
> with database in order to determine column types
> at
> org.apache.nifi.processors.standard.AbstractDatabaseFetchProcessor.setup(AbstractDatabaseFetchProcessor.java:309)
> at
> org.apache.nifi.processors.standard.AbstractDatabaseFetchProcessor.setup(AbstractDatabaseFetchProcessor.java:232)
> at
> org.apache.nifi.processors.standard.QueryDatabaseTable.onTrigger(QueryDatabaseTable.java:232)
> at
> org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1165)
> at
> org.apache.nifi.controller.tasks.ConnectableTask.invoke(ConnectableTask.java:203)
> at
> org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:117)
> 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:1149)
> at
> java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
> at java.lang.Thread.run(Thread.java:748)
> Caused by: java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not
> properly ended
> at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
> at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
> at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951)
> at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)
> at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227)
> at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
> at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:195)
> at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:876)
> at
> oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1175)
> at
> oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1296)
> at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1498)
> at
> oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:406)
> at
> org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
> at
> org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
> at
> org.apache.nifi.processors.standard.AbstractDatabaseFetchProcessor.setup(AbstractDatabaseFetchProcessor.java:269)
> ... 12 common frames omitted{noformat}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
