Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-1780

Stored procedure cannot be called using "Procedural Relational Command"

XMLWordPrintable

    • Icon: Bug Bug
    • Resolution: Done
    • Icon: Major Major
    • 7.4.1, 7.6
    • 7.4.1
    • Server
    • None

      I wasn't able to call stored procedure using procedural relation command (SOA Platform docs , teiid docs ). I was trying to do so on MySql 5.1 using this simple stored procedure:

      Stored procedure
      CREATE PROCEDURE getEmployeeSurnames
BEGIN
  SELECT surname FROM employees
END

      The VDB file contains following (I am attaching the vdb file):

      DbData.xmi
       <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>

      The Java code I used to invoke the procedure:

      Java snippet
      	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;
	}

      The exception on server was:

      sever.log
      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)

      The exception on client was:

      test.log
      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

              rhn-engineering-shawkins Steven Hawkins
              fnguyen_jira Filip Nguyen (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

                Created:
                Updated:
                Resolved: