1 2 Previous Next 21 Replies Latest reply on Mar 1, 2013 3:21 PM by kchen007

    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.

        • 1. Re: How teiid 8.1Final support stored procedure with out parameters?
          rareddy

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

          • 2. Re: How teiid 8.1Final support stored procedure with out parameters?
            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

             

            teiid-sp.JPG

            • 3. Re: How teiid 8.1Final support stored procedure with out parameters?
              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..

              • 4. Re: How teiid 8.1Final support stored procedure with out parameters?
                kchen007

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

                teiid-sp.JPG

                • 5. Re: How teiid 8.1Final support stored procedure with out parameters?
                  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..

                  • 6. Re: How teiid 8.1Final support stored procedure with out parameters?
                    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?

                    • 7. Re: How teiid 8.1Final support stored procedure with out parameters?
                      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.

                      • 8. Re: How teiid 8.1Final support stored procedure with out parameters?
                        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

                        • 9. Re: How teiid 8.1Final support stored procedure with out parameters?
                          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.

                          • 10. Re: How teiid 8.1Final support stored procedure with out parameters?
                            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]

                            • 11. Re: How teiid 8.1Final support stored procedure with out parameters?
                              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.

                              • 12. Re: How teiid 8.1Final support stored procedure with out parameters?
                                kchen007

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

                                 

                                thanks Steven and Ramesh.

                                • 13. Re: How teiid 8.1Final support stored procedure with out parameters?
                                  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

                                  • 14. Re: How teiid 8.1Final support stored procedure with out parameters?
                                    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.

                                    1 2 Previous Next