String valiables in procedure may be truncated to 4000 bytes

Solution Unverified - Updated -

Issue

When a procedure executes long SQL via string variables, the query processing may be failed with QueryProcessingException as TEIID30168 and TEIID31100. Then it can be seen like the following stacktrace.

WARN  [org.teiid.PROCESSOR] (Worker5_QueryProcessorQueue9) TEIID30020 Processing exception for request jODqhcOBIDbO.0 'TEIID30168 Couldn't execute the dynamic SQL command "EXECUTE IMMEDIATE VARIABLES.sql AS XXXXX string, XXXXX string INTO XXXXX" with the SQL statement "VARIABLES.sql" due to: TEIID31100 Parsing error: Encountered ", [*]'ABCDE'[*], 'FGHIJ'" at line 11, column 517.
Was expecting: <STRINGVAL>'. Originally QueryProcessingException QueryParser.java:214.: org.teiid.api.exception.query.QueryProcessingException: TEIID30168 Couldn't execute the dynamic SQL command "EXECUTE IMMEDIATE VARIABLES.sql AS XXXXX string, YYYYY string INTO #TEMP" with the SQL statement "VARIABLES.sql" due to: TEIID31100 Parsing error: Encountered ", [*]'ABCDE'[*], 'FGHIJ'" at line 11, column 517.
Was expecting: <STRINGVAL>
    at org.teiid.query.processor.proc.ExecDynamicSqlInstruction.process(ExecDynamicSqlInstruction.java:227) [teiid-engine-8.7.5.6_2-redhat-2.jar:8.7.5.6_2-redhat-2]
    at org.teiid.query.processor.proc.ProcedurePlan.processProcedure(ProcedurePlan.java:362) [teiid-engine-8.7.5.6_2-redhat-2.jar:8.7.5.6_2-redhat-2]
    at org.teiid.query.processor.proc.ProcedurePlan.nextBatchDirect(ProcedurePlan.java:289) [teiid-engine-8.7.5.6_2-redhat-2.jar:8.7.5.6_2-redhat-2]
    at org.teiid.query.processor.proc.ProcedurePlan.nextBatch(ProcedurePlan.java:263) [teiid-engine-8.7.5.6_2-redhat-2.jar:8.7.5.6_2-redhat-2]
    at org.teiid.query.processor.relational.PlanExecutionNode.nextBatchDirect(PlanExecutionNode.java:118) [teiid-engine-8.7.5.6_2-redhat-2.jar:8.7.5.6_2-redhat-2]
    at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:278) [teiid-engine-8.7.5.6_2-redhat-2.jar:8.7.5.6_2-redhat-2]
    at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:146) [teiid-engine-8.7.5.6_2-redhat-2.jar:8.7.5.6_2-redhat-2]
    at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:278) [teiid-engine-8.7.5.6_2-redhat-2.jar:8.7.5.6_2-redhat-2]
    at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:136) [teiid-engine-8.7.5.6_2-redhat-2.jar:8.7.5.6_2-redhat-2]
    at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:151) [teiid-engine-8.7.5.6_2-redhat-2.jar:8.7.5.6_2-redhat-2]
    at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:114) [teiid-engine-8.7.5.6_2-redhat-2.jar:8.7.5.6_2-redhat-2]
    at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:159) [teiid-engine-8.7.5.6_2-redhat-2.jar:8.7.5.6_2-redhat-2]
    at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:141) [teiid-engine-8.7.5.6_2-redhat-2.jar:8.7.5.6_2-redhat-2]
    at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:444) [teiid-engine-8.7.5.6_2-redhat-2.jar:8.7.5.6_2-redhat-2]
    at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:326) [teiid-engine-8.7.5.6_2-redhat-2.jar:8.7.5.6_2-redhat-2]
    at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51) [teiid-engine-8.7.5.6_2-redhat-2.jar:8.7.5.6_2-redhat-2]
    at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:254) [teiid-engine-8.7.5.6_2-redhat-2.jar:8.7.5.6_2-redhat-2]
    at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:274) [teiid-engine-8.7.5.6_2-redhat-2.jar:8.7.5.6_2-redhat-2]
    at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) [teiid-engine-8.7.5.6_2-redhat-2.jar:8.7.5.6_2-redhat-2]
    at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210) [teiid-engine-8.7.5.6_2-redhat-2.jar:8.7.5.6_2-redhat-2]
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [rt.jar:1.8.0_111]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [rt.jar:1.8.0_111]
    at java.lang.Thread.run(Thread.java:745) [rt.jar:1.8.0_111]
Caused by: org.teiid.api.exception.query.QueryParserException: TEIID31100 Parsing error: Encountered ", [*]'ABCDE'[*], 'FGHIJ'" at line 11, column 517.
Was expecting: <STRINGVAL>
    at org.teiid.query.parser.QueryParser.convertParserException(QueryParser.java:214) [teiid-engine-8.7.5.6_2-redhat-2.jar:8.7.5.6_2-redhat-2]
    at org.teiid.query.parser.QueryParser.parseCommand(QueryParser.java:164) [teiid-engine-8.7.5.6_2-redhat-2.jar:8.7.5.6_2-redhat-2]
    at org.teiid.query.parser.QueryParser.parseCommand(QueryParser.java:140) [teiid-engine-8.7.5.6_2-redhat-2.jar:8.7.5.6_2-redhat-2]
    at org.teiid.query.parser.QueryParser.parseCommand(QueryParser.java:112) [teiid-engine-8.7.5.6_2-redhat-2.jar:8.7.5.6_2-redhat-2]
    at org.teiid.query.processor.proc.ExecDynamicSqlInstruction.process(ExecDynamicSqlInstruction.java:146) [teiid-engine-8.7.5.6_2-redhat-2.jar:8.7.5.6_2-redhat-2]
    ... 22 more

Environment

  • Red Hat JBoss Data Virtualization
    • 6.x

Subscriber exclusive content

A Red Hat subscription provides unlimited access to our knowledgebase of over 48,000 articles and solutions.

Current Customers and Partners

Log in for full access

Log In