How teiid 8.1Final support stored procedure with out parameters?

[kchen007]

I am trying to call a stored procedure with OUT parameter from eclipselink, I got the following exceptions:

        org.teiid.core.TeiidProcessingException: TEIID30490 Remote org.teiid.api.exception.query.QueryValidatorException: TEIID30490 The query does not return an update count.

        at org.teiid.dqp.internal.process.Request.createCommandContext(Request.java:211) [teiid-engine-8.1.0.Final.jar:8.1.0.Final]

        at org.teiid.dqp.internal.process.Request.validateAccess(Request.java:469) [teiid-engine-8.1.0.Final.jar:8.1.0.Final]

        at org.teiid.dqp.internal.process.Request.generatePlan(Request.java:390) [teiid-engine-8.1.0.Final.jar:8.1.0.Final]

        at org.teiid.dqp.internal.process.PreparedStatementRequest.generatePlan(PreparedStatementRequest.java:155) [teiid-engine-8.1.0.Final.jar:8.1.0.Final]

        at org.teiid.dqp.internal.process.Request.processRequest(Request.java:453) [teiid-engine-8.1.0.Final.jar:8.1.0.Final]

        at org.teiid.dqp.internal.process.RequestWorkItem.processNew(RequestWorkItem.java:532) [teiid-engine-8.1.0.Final.jar:8.1.0.Final]

        at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:280) [teiid-engine-8.1.0.Final.jar:8.1.0.Final]

        at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:49) [teiid-engine-8.1.0.Final.jar:8.1.0.Final]

        at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:219) [teiid-engine-8.1.0.Final.jar:8.1.0.Final]

        at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:249) [teiid-engine-8.1.0.Final.jar:8.1.0.Final]

        at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:123) [teiid-engine-8.1.0.Final.jar:8.1.0.Final]

        at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:298) [teiid-engine-8.1.0.Final.jar:8.1.0.Final]

        at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886) [rt.jar:1.6.0_32]

        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908) [rt.jar:1.6.0_32]

The stored procedure is a simple dummy one, it will take no input parameter, has only one output parameter as Integer, and I just put a number into it, when running from SQL server studio, it would give out value 77.

     CREATE PROCEDURE [dbo].[CustOrdersDetail] @OrderID int OUTPUT          AS SELECT @OrderID = COUNT(*) FROM PRODUCTS

But If I called the same stored procedure from eclipselink, before it reached the SQL Server, the teiid validataion throws the above exception, the eclipselink code is like this:

     StoredProcedureCall spcall = new StoredProcedureCall();

     spcall.setProcedureName("CustOrdersDetail");

     spcall.addNamedOutputArgument("OrderID", "OrderID", Integer.class);

     ValueReadQuery query = new ValueReadQuery();

     query.setCall(spcall);

     Object orderId = em.getActiveSession().executeQuery(query);

The place that throw the exception is from Request.createCommandContext(), in this case the returnsResultSet=false, returunsUpdateCount=fase becasue the command is StoredProcedure, the ResultsMode is UPDATECOUNT

      protected void createCommandContext(Command command) throws QueryValidatorException {

           boolean returnsResultSet = command.returnsResultSet();

           this.returnsUpdateCount = !(command instanceof StoredProcedure) && !returnsResultSet;

           if ((this.requestMsg.getResultsMode() == ResultsMode.UPDATECOUNT && !returnsUpdateCount)

                 || (this.requestMsg.getResultsMode() == ResultsMode.RESULTSET && !returnsResultSet)) {

               throw new QueryValidatorException(QueryPlugin.Event.TEIID30490

From the 8.1 documentatin it mentions it supports OUT parameters, how can I get it to work with eclipselink?

Thanks.

[rareddy]

Is that way you defined the stored procedure in your vdb.xml file for Dynamic VDB, or did you use Designer?

[kchen007]

The procedure is defined in the SQL server. I have imported it into teiid via teiid designer, here is the definition of the Stored Procedure

[rareddy]

There should be property called "updateCount" on the procedure, it should be set to auto or some number, do you see that? Also make sure the Name in source is set correctly.

Ramesh..

[kchen007]

yes, it is there and it is set to AUTO:

[rareddy]

This support started in Teiid 8.1, I am not sure we have the Designer support for this yet. However you could use Dynamic VDB and define a foreign procedure and try call through eclipse link. That is where I started going, and side tracked with other questions.

BTW, if it still fails with Dynamic VDB, you can log a JIRA.

Ramesh..

[shawkins]

Out parameters are supported.  However it does look like there is an integration issue here with EclipseLink.  The exception is being thrown because the client must be calling a varient of Statement/PreparedStatement.executeUpdate - which we expect to be a update statement.  Teiid expects instead executeQuery or execute to be called. 

>There should be property called "updateCount" on the procedure, it should be set to auto or some number, do you see that? Also make sure the Name in source is set correctly.

That property is to determine transactional behavior.  It does not influence the exception you're seeing.

>This support started in Teiid 8.1, I am not sure we have the Designer support for this yet. However you could use Dynamic VDB and define a foreign procedure and try call through eclipse link. That is where I started going, and side tracked with other questions.

What support are you thinking of Ramesh?

[kchen007]

Steven:

That is what happened. In eclipselink, even I called executeQuery() but once when it saw the out parameter, it will set the ResulMode to UPDATECOUNT, eventually it will PreparedStatement.executeUpdate();

Do you have examples on how to get the OUT parameter result on TEIID level? I may be able to inject code between teiid and eclipselink.

Also, how to support to get both resultSet and out parameters? in the 8.1 documents,  it says it is possible but I cannot find any more information to do it.

[shawkins]

An out parameter can be accessed in several ways.

If the procedure does not return a result set and you issue a query without callable statement syntax, i.e. "call proc(1)", then the out/return parameters will be returned as a single row result.  This is Teiid specific behavior mostly for backwards compatibility and convinence.

If the procedure does return a result set, you should use a CallableStatement, i.e.

CallableStatement cs = teiidConnection.prepareCall("{call proc(1, ?)}");

cs.registerOutParameter(1, Types.INTEGER);

ResultSet rs  = cs.executeQuery();

int outValue = cs.getInteger(1);

Note that Teiid also has return parameters, which is used, must be the initial parameter in the call - "{? = call proc(?, ?)}"

Steve

[rareddy]

>This support started in Teiid 8.1, I am not sure we have the Designer support for this yet. However you could use Dynamic VDB and define a foreign procedure and try call through eclipse link. That is where I started going, and side tracked with other questions.

What support are you thinking of Ramesh?

I think I swapped the return and out parameter support in the Designer. Designer only supports the return as resultset not as parameter. Also, I was not sure if there was any additional metadata that is needed for this support that Designer may not be providing.

With your explanation, looks like the exception message text is confusing, may be it should be clarified more.

[kchen007]

Steven:

Thanks for the sample code. I modify my code to use teiid directly but still got an exception, the same place but this time it complains that the SP does not return a result set

here is the code:

     // the SP does not have any INPUT parameter, if I put (1,?) it will complain it expect 0 parameter but got 1, so I remove it.

     // the accessor.getConnection() will return a TeiidConnectionImpl

     CallableStatement cs = accessor.getConnection().prepareCall(

"{call CustOrdersDetail()}");

     // this line call has no effect since all the registerOutParameter() function do nothing.

     cs.registerOutParameter(1, Types.INTEGER);

     // exception thrown from here.

     ResultSet rs = cs.executeQuery();

     int outValue = cs.getInt(1);

the exception is:

       org.teiid.core.TeiidProcessingException: TEIID30490 Remote org.teiid.api.exception.query.QueryValidatorException: TEIID30490 The query does not return a result set.
        at org.teiid.dqp.internal.process.Request.createCommandContext(Request.java:211) [teiid-engine-8.1.0.Final.jar:8.1.0.Final]
        at org.teiid.dqp.internal.process.Request.validateAccess(Request.java:469) [teiid-engine-8.1.0.Final.jar:8.1.0.Final]
        at org.teiid.dqp.internal.process.Request.generatePlan(Request.java:390) [teiid-engine-8.1.0.Final.jar:8.1.0.Final]
        at org.teiid.dqp.internal.process.PreparedStatementRequest.generatePlan(PreparedStatementRequest.java:155) [teiid-engine-8.1.0.Final.jar:8.1.0.Final]
        at org.teiid.dqp.internal.process.Request.processRequest(Request.java:453) [teiid-engine-8.1.0.Final.jar:8.1.0.Final]
        at org.teiid.dqp.internal.process.RequestWorkItem.processNew(RequestWorkItem.java:532) [teiid-engine-8.1.0.Final.jar:8.1.0.Final]
        at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:280) [teiid-engine-8.1.0.Final.jar:8.1.0.Final]
        at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:49) [teiid-engine-8.1.0.Final.jar:8.1.0.Final]
        at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:219) [teiid-engine-8.1.0.Final.jar:8.1.0.Final]
        at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:249) [teiid-engine-8.1.0.Final.jar:8.1.0.Final]
        at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:123) [teiid-engine-8.1.0.Final.jar:8.1.0.Final]
        at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:298) [teiid-engine-8.1.0.Final.jar:8.1.0.Final]
        at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886) [rt.jar:1.6.0_32]

[shawkins]

The code example is for if there is a result set.  Otherwise the standard call if the procedure does not return a result set is to use the JDBC execute method - which actually works for any kind of query and then you subsequently call getResultSet or getUpdateCount if needed.  By calling executeQuery you are indicating that you expect the procedure to return a result set, which by the exception it must not.

[kchen007]

After change it cs.execute(), I am able to get the out parameter value.

thanks Steven and Ramesh.

[kchen007]

Steven:

Is this check in Request.java correct? If I have a stored procedure which will not return anything, then the JPA will call executeUpdate() and the requestMsg.getResultMode() is UPDATECOUNT, but since the commond is the StoredProcedure, then the returnsUpdateCount will be false. then it will thow an exception.

this.returnsUpdateCount = !(command instanceof

StoredProcedure)  &&  !returnsResultSet;

if ((this.requestMsg.getResultsMode() == ResultsMode.UPDATECOUNT && !returnsUpdateCount)

  || (this.requestMsg.getResultsMode() == ResultsMode.RESULTSET

&& !returnsResultSet)) {

//$NON-NLS-1$ //$NON-NLS-2$

}

throw new QueryValidatorException(QueryPlugin.Event.TEIID30490, QueryPlugin.Util.getString(this.requestMsg.getResultsMode()==ResultsMode.RESULTSET?"Request.no_result_set":"Request.result_set"));

Thanks

Kevin

[shawkins]

By the Statement javadoc, yes you could make the case that our check goes too far.   All we need to check for is that the query does not return a resultset, not that it does return an update count.