[NIFI-5643] QueryDatabaseTable custom query generates invalid Oracle queries

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 1.7.1
• Component/s: Extensions
• Assignee: Unassigned
• Reporter: Diego Queiroz
• Created: 27/Sep/18 21:28
• Updated: 27/Sep/18 21:35

Description

When using "Custom Query" to generate a query for Oracle, it wraps the query with:

SELECT * FROM ( your_query ) AS TABLE_NAME WHERE 1 = 0

 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:

SELECT * FROM ( your_query ) TABLE_NAME WHERE 1 = 0

Apparently, this is already solved by NIFI-5471 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:

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