[teiid-issues] [JBoss JIRA] (TEIID-1780) Stored procedure cannot be called using "Procedural Relational Command"

Stored procedure cannot be called using "Procedural Relational Command" ----------------------------------------------------------------------- Key: TEIID-1780 URL: https://issues.jboss.org/browse/TEIID-1780 Project: Teiid Issue Type: Bug Components: Server Affects Versions: 7.4.1 Reporter: Filip Nguyen Assignee: Steven Hawkins Fix For: 7.4.1, 7.6 I wasn't able to call stored procedure using procedural relation command ([SOA Platform docs | http://documentation-stage-01.lab.eng.bne.redhat.com/docs/en-US/JBoss_Ent...], [teiid docs | http://docs.jboss.org/teiid/7.5.0.Final/reference/en-US/html_single/#proc...]). I was trying to do so on MySql 5.1 using this simple stored procedure: {code:title=Stored procedure|borderStyle=solid} CREATE PROCEDURE getEmployeeSurnames BEGIN SELECT surname FROM employees END {code} The VDB file contains following (I am attaching the vdb file): {code:title=DbData.xmi|borderStyle=solid} <relational:Procedure xmi:uuid="mmuuid:c4ebdcbb-87d9-4737-8107-f34481cb8406" name="getEmployeeSurnames" nameInSource="getEmployeeSurnames"> <result xmi:uuid="mmuuid:7620c452-417e-478b-a6bf-ee1529c98048" name="ResultSet"> <columns xmi:uuid="mmuuid:33f34ec9-4f4f-4d23-8cad-cc6184928b0d" name="surname" length="255"> <type href="http://www.w3.org/2001/XMLSchema#string"/> </columns> </result> </relational:Procedure> {code} The Java code I used to invoke the procedure: {code:title=Java snippet|borderStyle=solid} protected List<String> callGetEmployeeSurnames(boolean useCache) throws Exception { List<String> surnameList = new ArrayList<String>(); String[] connectionProps = useCache ? new String[] {"resultSetCacheMode=true"} : new String[] {}; String query = "select * from getEmployeeSurnames"; Connection conn = getTeiidConnection(VDBNAME, connectionProps); Statement cs = conn.createStatement(); cs.execute(query); ResultSet rs = cs.executeQuery(query); while (rs.next()) { surnameList.add(rs.getString(1)); } conn.close(); return surnameList; } public Connection getTeiidConnection(String vdbName, String ... props) throws Exception { StringBuilder connectionProps = new StringBuilder(); for (String p : props) { connectionProps.append(";" + p); } String url = "jdbc:teiid:" + vdbName + "@mm://127.0.0.1:31000" + connectionProps.toString(); Class.forName("org.teiid.jdbc.TeiidDriver"); Connection conn = DriverManager.getConnection(url, "admin", "teiid"); return conn; } {code} The exception on server was: {code:title=sever.log|borderStyle=solid} java.lang.NullPointerException at org.teiid.query.processor.relational.DependentProcedureAccessNode.prepareNextCommand(DependentProcedureAccessNode.java:95) at org.teiid.query.processor.relational.AccessNode.open(AccessNode.java:144) at org.teiid.query.processor.relational.DependentProcedureAccessNode.open(DependentProcedureAccessNode.java:86) at org.teiid.query.processor.relational.RelationalNode.open(RelationalNode.java:251) at org.teiid.query.processor.relational.RelationalNode.open(RelationalNode.java:251) at org.teiid.query.processor.relational.RelationalNode.open(RelationalNode.java:251) at org.teiid.query.processor.relational.RelationalPlan.open(RelationalPlan.java:153) at org.teiid.query.processor.QueryProcessor.init(QueryProcessor.java:182) at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:126) at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:105) at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:147) at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:357) at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:282) at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:49) at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:210) at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:194) at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:118) at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:288) at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908) at java.lang.Thread.run(Thread.java:662) {code} The exception on client was: {code:title=test.log|borderStyle=solid} org.teiid.jdbc.TeiidSQLException: org.teiid.core.TeiidException at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:113) at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:70) at org.teiid.jdbc.StatementImpl.postReceiveResults(StatementImpl.java:611) at org.teiid.jdbc.StatementImpl.access$000(StatementImpl.java:70) at org.teiid.jdbc.StatementImpl$2.onCompletion(StatementImpl.java:530) at org.teiid.client.util.ResultsFuture.done(ResultsFuture.java:130) at org.teiid.client.util.ResultsFuture.access$200(ResultsFuture.java:37) at org.teiid.client.util.ResultsFuture$1.receiveResults(ResultsFuture.java:75) at org.teiid.net.socket.SocketServerInstanceImpl.receivedMessage(SocketServerInstanceImpl.java:218) at org.teiid.net.socket.SocketServerInstanceImpl.read(SocketServerInstanceImpl.java:253) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at org.teiid.net.socket.SocketServerConnectionFactory$ShutdownHandler.invoke(SocketServerConnectionFactory.java:110) at $Proxy17.read(Unknown Source) at org.teiid.net.socket.SocketServerInstanceImpl$RemoteInvocationHandler$1.get(SocketServerInstanceImpl.java:347) at org.teiid.net.socket.SocketServerInstanceImpl$RemoteInvocationHandler$1.get(SocketServerInstanceImpl.java:333) at org.teiid.jdbc.StatementImpl.executeSql(StatementImpl.java:539) at org.teiid.jdbc.StatementImpl.execute(StatementImpl.java:315) at org.jboss.soa.esb.samples.quickstart.edscache.EDSResultCacheConnectionPropertyTest.callGetEmployeeSurnames(EDSResultCacheConnectionPropertyTest.java:114) at org.jboss.soa.esb.samples.quickstart.qa.EDSResultCacheBaseTest.testStoredProcedure(EDSResultCacheBaseTest.java:136) at org.jboss.soa.esb.samples.quickstart.edscache.EDSResultCacheConnectionPropertyTest.testStoredProcedure(EDSResultCacheConnectionPropertyTest.java:81) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at org.testng.internal.MethodHelper.invokeMethod(MethodHelper.java:641) at org.testng.internal.InvokeMethodRunnable.runOne(InvokeMethodRunnable.java:49) at org.testng.internal.InvokeMethodRunnable.run(InvokeMethodRunnable.java:40) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:441) at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303) at java.util.concurrent.FutureTask.run(FutureTask.java:138) at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908) at java.lang.Thread.run(Thread.java:662) Caused by: [TeiidException] 1 [NullPointerException] at org.teiid.client.ResultsMessage.setException(ResultsMessage.java:177) at org.teiid.dqp.internal.process.RequestWorkItem.sendError(RequestWorkItem.java:689) at org.teiid.dqp.internal.process.RequestWorkItem.attemptClose(RequestWorkItem.java:439) at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:323) at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:49) at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:210) at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:194) at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:118) at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:288) ... 3 more Caused by: java.lang.NullPointerException at org.teiid.query.processor.relational.DependentProcedureAccessNode.prepareNextCommand(DependentProcedureAccessNode.java:95) at org.teiid.query.processor.relational.AccessNode.open(AccessNode.java:144) at org.teiid.query.processor.relational.DependentProcedureAccessNode.open(DependentProcedureAccessNode.java:86) at org.teiid.query.processor.relational.RelationalNode.open(RelationalNode.java:251) at org.teiid.query.processor.relational.RelationalNode.open(RelationalNode.java:251) at org.teiid.query.processor.relational.RelationalNode.open(RelationalNode.java:251) at org.teiid.query.processor.relational.RelationalPlan.open(RelationalPlan.java:153) at org.teiid.query.processor.QueryProcessor.init(QueryProcessor.java:182) at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:126) at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:105) at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:147) at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:357) at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:282) ... 8 more {code}