[teiid-issues] [JBoss JIRA] (TEIID-5763) GENERATED_KEY returns NULL if used in INSTEAD OF INSERT Triggers in DDL files

[Christoph John (Jira)]

    [ https://issues.jboss.org/browse/TEIID-5763?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13746104#comment-13746104 ] 

Christoph John edited comment on TEIID-5763 at 6/12/19 3:55 PM:
----------------------------------------------------------------

Hello Steven,
I am sorry, but I do not have enough Java background to be able to build and remotely debug the sources in an Eclipse environment. I hope the following will help you

https://c.gmx.net/@328076122686231878/aW-zSH_GTzaajkuYGZPtbQ

In the TeiidMySQL package you can find a docker-compose file with a mysql db and a teiid server with yesterdays git sources. In the dockerfile you can also see the mysql driver configuration. I tested with both variants XA and non-XA. The mysql container will be initialized with three tables which are from the simplified example in our previous discussions. The teiid container will be initialized with the relevant vdb for these tables. I also attached a curl script which sends a POST request to show the error. And finally I also added the Teiid Web Console to the container to show you the issue with missing runtime information. The second bug we discussed this week. More usage details are given in the README. Let me know if you have questions or I can be of further help!


was (Author: cjohn001):
Hello Steven,
I am sorry, but I do not have enough Java background to be able to build and remotely debug the sources in an Eclipse environment. I hope the following will help you

https://c.gmx.net/@328076122686231878/aW-zSH_GTzaajkuYGZPtbQ

In the TeiidMySQL package you can find a docker-compose file with a mysql db and a teiid server with yesterdays git sources. In the dockerfile you can also see the mysql driver configuration. I tested with both variants XA and non-XA. The mysql container will be initialized with three tables which are from the simplified example in our previous discussions. The teiid container will be initialized with the relevant vdb for these tables. I also attached a curl script which sends a POST request to show the error. And finally I also added the Teiid Web Console to the container to show you the issue with missing runtime information. The second bug we discussed this week.

> GENERATED_KEY returns NULL if used in INSTEAD OF INSERT Triggers in DDL files
> -----------------------------------------------------------------------------
>
>                 Key: TEIID-5763
>                 URL: https://issues.jboss.org/browse/TEIID-5763
>             Project: Teiid
>          Issue Type: Bug
>    Affects Versions: 12.2
>            Reporter: Christoph John
>            Assignee: Steven Hawkins
>            Priority: Blocker
>
> The issue arrised in:
> https://developer.jboss.org/message/989700#989700
> Following sceanarios are  given:
> Variant 1:
> Table Product(id), primary key = autoincrement;
> Table QuicklyAddedProduct, primary key is foreign key on Product.id
> Table Diary_SRC
> View Diary on Diary_SRC
> INSTEAD OF INSERT Trigger on Diary should :
> -create new record on Product, 
> -get autoincremented Product.id of new record,
> - create new QuicklyAddedProduct with returned Product.id as primary key
> - create new Diary record with QuicklyAddedProduct 
> {
>    INSERT a new record on Product;
>    idProduct = CONVERT(GENERATED_KEY('idProduct'),long); // fails
> }
> Variant 2:
> INSTEAD OF INSERT trigger on Diary should :
> - create new QuicklyAddedProduct 
> - add Quickly added prodcut to Diary
> additionally a INSTEAD OF trigger on QuicklyAddedProduct exists which:
> - creates new Product record
> - uses returned GENERATED_KEY(Product.id) to add record on QuicklyAddedProduct with Product.id as primary key



--
This message was sent by Atlassian Jira
(v7.12.1#712002)