-
1. Re: Using the INPUTS qualifier in Update Procedure
rareddy Jul 28, 2010 11:54 AM (in response to rakeshsagar)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 Aug 2, 2010 10:13 AM (in response to rareddy)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 Aug 2, 2010 10:39 AM (in response to rareddy)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 Aug 2, 2010 10:50 AM (in response to rakeshsagar)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 Aug 2, 2010 11:17 AM (in response to rareddy)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 Aug 2, 2010 11:55 AM (in response to rakeshsagar)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 Aug 2, 2010 12:19 PM (in response to rakeshsagar)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 Aug 3, 2010 2:50 AM (in response to rareddy)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 Aug 3, 2010 4:44 PM (in response to rakeshsagar)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 Aug 4, 2010 2:26 AM (in response to rareddy)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 Aug 4, 2010 11:20 AM (in response to rakeshsagar)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 Aug 5, 2010 4:46 AM (in response to rareddy)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 Aug 6, 2010 2:38 AM (in response to rareddy)Hi Ramesh,
Please help me in solving this problem.
Thanks
Rakesh
-
14. Re: Using the INPUTS qualifier in Update Procedure
rareddy Aug 6, 2010 2:07 PM (in response to rakeshsagar)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..