[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=13745569#comment-13745569 ] 

Christoph John edited comment on TEIID-5763 at 6/11/19 6:09 PM:
----------------------------------------------------------------

Hello Steven, I got Teiid running now with wildfly 17. The keycloak plugin for wildfly 16 also seems to work with Wildfly 17. Unfortunately I am still getting the same error.  

 POST on 'QuicklyAddedProducts' failed; will be repeated automatically - Error: TEIID16016 Insert into QuicklyAddedProducts success, but failed to retrieve auto generated keys from source, thus failed to show result entity; Supply the key values.

I tried both variants:

vIdProduct = generated_key(); 
vIdProduct = CONVERT(GENERATED_KEY('idProduct'),long);

>From the different flavors I described, I now tested the INSERT Trigger on the QuicklyAddedProducts view. As I mentioned previously, I am not sure where the distinction between the 3 variants is. However, as you mentioned that variant 1 and 3 should work according to your fix, I assume I need variant 2 :) Is there something I can debug to ensure that your fix is running as expected?

Or might there be some driver configuration that I should check?


was (Author: cjohn001):
Hello Steven, I got Teiid running now with wildfly 17. The keycloak plugin for wildfly 16 also seems to work with Wildfly 17. Unfortunately I am still getting the same error.  

 POST on 'QuicklyAddedProducts' failed; will be repeated automatically - Error: TEIID16016 Insert into QuicklyAddedProducts success, but failed to retrieve auto generated keys from source, thus failed to show result entity; Supply the key values.

I tried both variants:

vIdProduct = generated_key(); 
vIdProduct = CONVERT(GENERATED_KEY('idProduct'),long);

>From the different flavors I described, I now tested the INSERT Trigger on the QuicklyAddedProducts view. As I mentioned previously, I am not sure where the distinction between the 3 variants is. However, as you mentioned that variant 1 and 3 should work according to your fix, I assume I need variant 2 :) Is there something I can debug to ensure that your fix is running as expected?

> 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)