Bug 781002 (SOA-3478)

Summary:

Stored procedure cannot be called using "Procedural Relational Command"

Product:

[JBoss] JBoss Enterprise SOA Platform 5

Reporter:

Filip Nguyen <fnguyen>

Component:

EDS

Assignee:

Van Halbert <vhalbert>

Status:

CLOSED NEXTRELEASE

QA Contact:

Severity:

high

Docs Contact:

Priority:

high

Version:

5.2.0.ER4

CC:

vhalbert

Target Milestone:

---

Target Release:

5.2.0.ER6

Hardware:

Unspecified

OS:

Unspecified

URL:

http://jira.jboss.org/jira/browse/SOA-3478

Whiteboard:

Fixed In Version:

Doc Type:

Bug Fix

Doc Text:

Story Points:

---

Clone Of:

Environment:

Last Closed:

2011-11-03 17:09:07 UTC

Type:

Bug

Regression:

---

Mount Type:

---

Documentation:

---

CRM:

Verified Versions:

Category:

---

oVirt Team:

---

RHEL 7.3 requirements from Atomic Host:

Cloudforms Team:

---

Target Upstream Version:

Attachments:

Description	Flags
textDBVDB.vdb	none
server.log	none

Description Filip Nguyen 2011-10-12 14:07:30 UTC

project_key: SOA

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_Enterprise_Data_Services/5/html/Data_Services_Reference_Guide/sql_support.html#procedural_relational], [teiid docs | http://docs.jboss.org/teiid/7.5.0.Final/reference/en-US/html_single/#procedural_relational]). 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}

Comment 1 Filip Nguyen 2011-10-12 14:08:07 UTC

Attachment: Added: textDBVDB.vdb

Comment 2 Filip Nguyen 2011-10-12 14:08:53 UTC

Attachment: Added: server.log

Comment 3 Van Halbert 2011-10-12 14:14:04 UTC

Link: Added: This issue Cloned to SOA-3479

Comment 4 Filip Nguyen 2011-10-12 14:30:44 UTC

Link: Added: This issue is related to SOA-3480

Comment 8 David Le Sage 2011-10-20 00:03:40 UTC

Release Notes Docs Status: Added: Not Required
Writer: Added: dlesage

Comment 9 Warren Gibson 2011-11-03 17:09:07 UTC

11/3/2011 - Closed.