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

Stored procedure cannot be called using "Procedural Relational Command"

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Done
    • 7.4.1
    • 7.4.1, 7.6
    • Server
    • None

    Description

      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
      

      Attachments

        Activity

          People

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

            Dates

              Created:
              Updated:
              Resolved: