1 2 Previous Next 23 Replies Latest reply on Sep 1, 2010 9:52 AM by rareddy

    Using the INPUTS qualifier in Update Procedure

    rakeshsagar

      Hi,

       

      I am writing a Update Procedure and struck in it.

       

      I am unable to use the INPUTS qualifier.

       

      I am having a column in my view named Job_Id and I want to use the value entered to update the base tables and I am getting the following error

       

      Symbol INPUTS.Job_Id is specified with an unknown group context.

       

       

      Please suggest on using the INPUTS qualifier.

       

      Thanks

      Rakesh

        • 1. Re: Using the INPUTS qualifier in Update Procedure
          rareddy

          Rakesh,

           

          In all the "update", "insert" and "delete" tabs in the Designer, you can define a Teiid procedure that will is reflective of the actual insert, update and delete you want to do.

           

          "INPUT" is implicit context that defines all the values you are supplying from your user query.

           

          For example you have "source" model called A and has tables X and Y, and they both have "col1" and "col2" columns. Now you are creating a "view" model B, with view table "foo"

           

          Let's say "foo" has "select" transformation like below

           

          SELECT A.X.col1 as col1, A.Y.col2 as col2 FROM A.X, A.Y WHERE A.X.col1 = A.Y.col2
          

           

          For this table now you can write the "INSERT" as

           

          CREATE PROCEDURE
          BEGIN
               INSERT INTO TABLE A.X(col1) VALUES (INPUT.col1);
               VARIABLES.ROWS_UPDATED = INSERT INTO TABLE A.Y(col1, col2) VALUES (INPUT.col1, INPUT.col2);
          END
          

           

          You can do the same procedure syntax for the "update" and "delete". Now, after you deploy you VDB, and then issue a user query like

           

          INSERT INTO FOO (col1, col2) VALUES ("value-1", "value-2");

           

          in your application, then at runtime the values "value-1" and "value-2" will be referenced in the proceducre created above as INPUT.col1, INPUT.col2 respectively.

           

          Take look at the Teiid Procedure Language for all the available commands at http://docs.jboss.org/teiid/7.0.0/reference/en-US/html_single/#procedures

           

          You can also look for specifics about "update" procedures in the same chapter at http://docs.jboss.org/teiid/7.0.0/reference/en-US/html_single/#d0e4885.

           

          Besure to read about "TRANSLATE CRITERIA" as to how ca =n you can pass on your user criteria on to the procedures.

           

          Hope this helps.

           

          Ramesh..

          • 2. Re: Using the INPUTS qualifier in Update Procedure
            rakeshsagar

            Hi Ramesh,

             

            Thanks for the info. It solved my problem.

             

            I was using INPUTS instead of INPUT. In the documentation it is mentioned as INPUTS.

            • 3. Re: Using the INPUTS qualifier in Update Procedure
              rakeshsagar

              Hi Ramesh,

               

              I am getting the following error when I try to insert into a View which contains tables from different sources

               

              Error Code:0 Message:Remote org.teiid.core.TeiidProcessingException: Error Code:0 Message:Error Code:0 Message:Could not enlist in transaction on entering meta-aware object!; - nested throwable: (javax.transaction.SystemException: java.lang.Throwable: Unabled to enlist resource, see the previous warnings. tx=TransactionImple < ac, BasicAction: a8606b7:d6b:4c56d648:6b status: ActionStatus.ABORT_ONLY >); - nested throwable: (org.jboss.resource.JBossResourceException: Could not enlist in transaction on entering meta-aware object!; - nested throwable: (javax.transaction.SystemException: java.lang.Throwable: Unabled to enlist resource, see the previous warnings. tx=TransactionImple < ac, BasicAction: a8606b7:d6b:4c56d648:6b status: ActionStatus.ABORT_ONLY >))

              • 4. Re: Using the INPUTS qualifier in Update Procedure
                rareddy

                In 6.2 and prior releases the context was "INPUT", this has been deprecated and changed to "INPUTS" in 7.0. However, 7.0 still supports INPUT for time being, and will be removed in future releases. Also, the Desinger should have changed "input" to "inputs" automatically when it resolved, we will take look once you answer the below question.

                 

                Which version of Teiid are you using?

                 

                Your error seems to indicate you are using >= Teiid 7.0 version, then "INPUTS" should have been supported.

                 

                The exception seems to indicate you are updating a view in a XA transaction and one of your data sources is not XA data source. When you create -ds.xml file for the the data source make sure you create XA data source. Look in the "jboss-5.1.0.GA/docs/examples/jca" directory for examples.

                 

                Ramesh..

                • 5. Re: Using the INPUTS qualifier in Update Procedure
                  rakeshsagar

                  Hi Ramesh,

                   

                  I am using Teiid 7.0 and after resolving the query the INPUT is not being changed to INPUTS.

                   

                  I am not updating the view in XA Transaction. By default Teiid does the transaction in XA if we are using two different sources ?

                   

                  If it is the case like if by default Teiid does the transaction in XA then I need to modify my data source as XA Data Sources.

                   

                  Please suggest.

                   

                  Thanks

                  Rakesh

                  • 6. Re: Using the INPUTS qualifier in Update Procedure
                    rareddy

                    By default we do not require it. However, it seems to be in the JBoss container environment if you have the following XML fragment inside your -ds.xml file

                     

                    <metadata>
                             <type-mapping>DB2</type-mapping>
                    </metadata>

                     

                    It behaves the way you are describing. Try removing this from your -ds.xml files and check it. Providing the XA specific data sources also will work.

                     

                    We are verifying the the "input" vs 'inputs" to figure out where the issue lies, once we know will add a JIRA on it to fix.

                     

                    Thanks.


                    Ramesh..

                    • 7. Re: Using the INPUTS qualifier in Update Procedure
                      rareddy
                      I am using Teiid 7.0 and after resolving the query the INPUT is not being changed to INPUTS.

                      Logged and resolved https://jira.jboss.org/browse/TEIID-1180, Thanks SteveH.

                       

                      Ramesh..

                      • 8. Re: Using the INPUTS qualifier in Update Procedure
                        rakeshsagar

                        Hi Ramesh,

                         

                        The -ds.xml files does not contain the metadata tags.

                         

                        Please find the attached -ds.xml files I am using.

                        • 9. Re: Using the INPUTS qualifier in Update Procedure
                          rareddy

                          What kind of user operation you doing when this exection happens? Like are you issuing a "select" on a view table?

                          • 10. Re: Using the INPUTS qualifier in Update Procedure
                            rakeshsagar

                            Hi Ramesh,

                             

                            I am issuing an Insert into the View and the Update procedure inserts into the respective source tables.

                             

                            Thanks

                            Rakesh

                            • 11. Re: Using the INPUTS qualifier in Update Procedure
                              rareddy

                              Ok, the next question is what kind of client you are using?

                               

                              For example are using Teiid as data source in an EJB, in Servlet, Hibernate application or simple Java client?

                               

                              Except for Simple Java client all other frameworks use some kind of transaction wrapping, that will invoke the transaction manager to participate in the distributed transaction across both your sources.

                               

                              In the simple Java client scenario, if "autocommit=true", then Teiid takes on job of wrapping the transaction around the both sources. However, depending upon your needs you can configure transaction wrapping behavior with a property called "autoCommitTxn" property. Read more about transactions in the Reference Guide

                               

                              Also, look at this document on JBoss WIKI, about the specific exception. It seems to indicate that your first insert/update into source is in aborted state. Check the transformation. Try to run the same SQL statement from tools like SquirreL directly to the source and see if they are succeeding.

                               

                              Ramesh..

                              • 12. Re: Using the INPUTS qualifier in Update Procedure
                                rakeshsagar

                                Hi Ramesh,

                                 

                                I am using the simple Java client which uses JDBC connection.

                                The autocommit is set to true.

                                 

                                I tried executing the insert queries to individual sources via the VDB and they have succeed.

                                 

                                Thanks

                                Rakesh

                                • 13. Re: Using the INPUTS qualifier in Update Procedure
                                  rakeshsagar

                                  Hi Ramesh,

                                   

                                  Please help me in solving this problem.

                                   

                                  Thanks

                                  Rakesh

                                  • 14. Re: Using the INPUTS qualifier in Update Procedure
                                    rareddy

                                    Rakesh,

                                     

                                    *Read* the documentation I pointed out about the "autoCommitTxn". If you set this property to "OFF" Teiid will not start a XA transaction. You can also create XA data sources that will also solve the issue.

                                     

                                    As far as working as it is, I am going to re-create the issue and see if that is possible scenario by Teiid, our assumption was JBoss AS will simply ignore to add the data sources in the on going transaction if they are not XA data sources. I would need to investigate more as to why this situation is occurring, and this assumption is wrong.

                                     

                                    Ramesh..

                                    1 2 Previous Next