5 Replies Latest reply on Jul 5, 2012 9:39 AM by Steven Hawkins

    Database stored procedures

    Sergey Kim Newbie

      Hi,

       

      I am having a problem calling a database stored procedure from Teiid. Here are the steps showing how I build VDB:

       

      1. I have created source model from JDBC datasource and in the wizard I have selected the database stored procedure

      2. Then I have created a view model from the source model created in the step 1

      3. The transformation script is:

      CREATE VIRTUAL PROCEDURE

      BEGIN

                EXEC PrepFundExchangeFromPifia.prepFundExchange(PrepFundExchangeView.prepFundExchange."date", PrepFundExchangeView.prepFundExchange.type_close, PrepFundExchangeView.prepFundExchange.regim, PrepFundExchangeView.prepFundExchange.debug, PrepFundExchangeView.prepFundExchange.CheckRegim);

      END

      4. After that I have created VDB from the view model and deployed to Teiid server

       

      When I call virtual procedure from the client I get the followin error:

       

      Error: TEIID30270 Remote org.teiid.api.exception.query.QueryPlannerException: TEIID30270 No valid criteria specified for procedure parameter PrepFundExchangeFromPifia.Pythoness_buf.dbo.prepFundExchange."date"

       

      Could you please help me to understand what is it that I am doing wrong.

       

      Much appreciated for your time and efforts!

       

      I have also attached a printscreen

      Screenshot.jpg

        • 1. Re: Database stored procedures
          Ramesh Reddy Master

          Sergey,

           

          It is indicating that you have not provided the "date" as the input parameter for execution of the procedure. Also make you use proper escaping when providing the date.

           

          Ramesh..

          • 2. Re: Database stored procedures
            Sergey Kim Newbie

            Hi Ramesh,

             

            Thank you for your response.

             

            Here is the client code:

             

            CallableStatement cs = connection.prepareCall("{call PrepFundExchangeView.Pythoness_buf.dbo.prepFundExchange(?,?,?,?,?)}");

            cs.setTimestamp(1, new java.sql.Timestamp(System.currentTimeMillis()));

            cs.setByte(2, p1);

            cs.setByte(3, p2);

            cs.setByte(4, p3);

            cs.setByte(5, p4);

            cs.execute();

             

            So I don't understand why it is complaining that the "date" is not being provided. My be there is a problem with the transformation script?

            • 3. Re: Database stored procedures
              Steven Hawkins Master

              Sergey,

               

              That exception should only happen using the procedural relational style invocation, such as SELECT * FROM PrepFundExchangeFromPifia WHERE ...

               

              Just calling PrepFundExchangeFromPifia through an EXEC/CALL should work as you expect.  Is there anything else going on in this scenario other than what is shown here?  If not, then you'll want to log a JIRA.

               

              Thanks,

              Steve

              • 4. Re: Database stored procedures
                Sergey Kim Newbie

                Hi Steve,

                 

                I have used relational style before but then moved to EXEC approach. I have cleaned all caches in JBOSS and it solved the problem.

                 

                Now I have a different problem. Although the procedure is being executed without problem it does not return the result set. Do I have to explicitly specify the result set that the database stored procedure returns?

                • 5. Re: Database stored procedures
                  Steven Hawkins Master

                  Sergey,

                   

                  In many instances vendor's JDBC DatabaseMetadata does not show the columns for returned resultsets, in which case yes you do have to hand model what you expect.  If you are using a common DBMS and there is a way to use their system tables to get the result set information, then please open a designer JIRA to enhance the import process.

                   

                  Steve