Teiid exception handling

[cjohn001]

Hello together,

I am currently trying to understand how to deal with exception handling and would like to ask for some advice as it seems I do not get exceptions from the underlaying datasource forwarded.

Here is an example from a view in which I have tried to implement exception handling

CREATE TRIGGER ON Diary INSTEAD OF DELETE AS

   FOR EACH ROW

   BEGIN ATOMIC

   BEGIN -- begin try block

            DELETE FROM Diary_SRC WHERE idDiaryEntry = -1;--OLD.idDiaryEntry;

     EXCEPTION e

           RAISE e.exception;

    END

   END;

My current assumption is, that all exceptions from statements in the marked try block above should be catched in the EXCEPTION block. And I further assume, if a RAISE the caught exception again, it shall be forwarded to the calling client and moreover let the transaction rollback. However,  ( DELETE FROM Diary_SRC WHERE idDiaryEntry = -1;) should always result in an exception as -1 is an invalid id. Unfortunately it seems like I do not get an exception forwarded from the data source or I cannot forward it to the calling client (calling via odata) via RAISE e.exception like I thought I would do .

If I instead create an exception by hand

DECLARE EXCEPTION e = SQLEXCEPTION 'this is bad' SQLSTATE 'xxxxx';

        RAISE e;

the calling client gets an exception. So I assume, there is nothing wrong with the odata layer.

So please tell me what I am doing wrong. Thanks for your help!

Best regards,

Christoph

[shawkins]

> So please tell me what I am doing wrong. Thanks for your help!

All of your expectations over exceptions are correct.  With or without the block shown above an exception from the statement would get raised to the client.  The issue is that a delete that results in no rows being deleted does not raise an exception.  It would just report an update count of 0.

[cjohn001]

Hello Steven,

thanks, then things seem to make sense

I have one further question related to the use of Triggers. In an INSERT trigger of a Table B, I need to create an item. However, for creation of this B.item I first need to create another A.item in a Table A which is subsequently referenced by the newly created B.item. A.item thereby has an auto-increment primary key assigned which I need to obtain in the INSERT trigger once I have created  A.item.

In mysql I could simply create A.item in the insert trigger of Table B and than get A.items id (auto-increment) via a function LAST_INSERT_ID()

Unfortunately, I have not found such a function in Teiid yet. How can I obtain the id of the newly created item A.item in the INSERT trigger of Table B?

Thanks for your help.

[shawkins]

> How can I obtain the id of the newly created item A.item in the INSERT trigger of Table B?

See the generated_key system function: System Functions · GitBook

[cjohn001]

Hello Steven,

would be great if there would be more examples in the documentation. I am still stuck with the generated_key function. My code is the following:

CREATE TRIGGER ON Diary INSTEAD OF INSERT AS

   FOR EACH ROW

   BEGIN ATOMIC

       -- a product from the fdb database is simply added

       IF(new.fkDatabaseKey = 1)

       BEGIN

           IF(CHANGING.fkProfile AND CHANGING.AddedDateTime AND CHANGING.fkProduct AND CHANGING.MealNumber AND CHANGING.AmountInG)

                INSERT INTO

                    Diary_SRC(fkProfile, AddedDateTime, fkProduct, MealNumber, AmountInG)

                VALUES

                    (new.fkProfile, new.AddedDateTime, new.fkProduct, new.MealNumber, new.AmountInG);

        END

        -- for a quickly added product, we first need to create a new product record

        ELSE IF (new.fkDatabaseKey = 2)

        BEGIN

            IF(CHANGING.fkProfile AND CHANGING.AddedDateTime AND CHANGING.MealNumber AND

             CHANGING.product_name AND CHANGING.energy_100g AND CHANGING.carbohydrates_100g AND

             CHANGING.proteins_100g AND CHANGING.fat_100g)

            BEGIN

                -- frist we create a new record for the quickly added product

                INSERT INTO

                        QuicklyAddedProducts(fkProduct, fkProfile, product_name, energy_100g, carbohydrates_100g, proteins_100g, fat_100g)

                    VALUES

                        (0, new.fkProfile, new.product_name, new.energy_100g, new.carbohydrates_100g, new.proteins_100g, new.fat_100g);

                INSERT INTO

                    Diary_SRC(fkProfile, AddedDateTime, fkProduct, MealNumber, AmountInG)

                VALUES

                    (new.fkProfile, new.AddedDateTime, CONVERT(GENERATED_KEY('fkProduct'),long), new.MealNumber, new.AmountInG);

            END

            ELSE

            BEGIN

                DECLARE EXCEPTION e = SQLEXCEPTION 'Incomplete argument list for quickly added item specified' SQLSTATE '45000';

                RAISE e;

            END

        END

    END;

The relevant line is in the else if branch.

     (new.fkProfile, new.AddedDateTime, CONVERT(GENERATED_KEY('fkProduct'),long), new.MealNumber, new.AmountInG);

It seems like the CONVERT function here always returns NULL. At least the odata response reads as:

{"error":{"code":"TEIID30504","message":"TEIID30504 my_nutri_diary: 1048 TEIID11013:TEIID11004 Error executing statement(s): [Prepared Values: [1, {ts '2019-06-05 17:37:32.0'}, NULL, '5', 60.900000000000006] SQL: INSERT INTO \"Diary\" (\"fkProfile\", \"AddedDateTime\", \"fkProduct\", \"MealNumber\", \"AmountInG\") VALUES (?, ?, ?, ?, ?)]"}}

The first insert is working if I use it without the second one. Hence, my problem definitely derives from the CONVERT(GENERATED_KEY('fkProduct'),long) here.

Could you please tell me how the correct syntax would have to look like?

Thanks for your help!

Best regards,

Christoph

[shawkins]

> Could you please tell me how the correct syntax would have to look like?

It's not a syntax issue.  In the prior insert you are explicitly providing a value for fkProduct, so it may not be returned as a generated key.

INSERT INTO

                        QuicklyAddedProducts(fkProduct, ...)

                    VALUES

                        (0, ...

Typically you would leave the column out of the insert list for a value to be generated.

[cjohn001]

Hello Steven,

I already suspected that this might be the issue. My problem with it is the following, when inserting the product to the QuicklyAddedProducts an insert trigger is activated on this table in the underlaying mysql database which creates a new record in a Products table and uses its primary key as its own primary key. hence the primary key of QuicklyAddedProducts is also a foreign key to table products rather than an autoincrement. Teiid does not allow me to insert an element with an empty key in this case. See following picture for my db architecture, I think this makes my problem more apparent. The only option would be to also shift the insert trigger logic from the QuicklyAddedProducts to Teiid. I would prefer to not do this due to involved logic. Do you see an alternative option?

[shawkins]

> I already suspected that this might be the issue.

The translator logic will ask for the generated keys if it's supported by the driver and if the insert table has a primary key.  Some sources, like oracle, require you to specify the column names ahead of time.  When retrieving we then assume that we're matching the primary key columns.  So Teiid will ask for the generated key for fkProduct based upon the above diagram regardless of whether it's in the insert list or not.

However it's up to the source driver whether or not to return anything via the standard jdbc interface for generated keys.  More than likely the mysql is not returning anything in this case.

> Teiid does not allow me to insert an element with an empty key in this case.

It's probably marked as non-null in the metadata.  You could set the auto_increment option in the metadata:

create foreign table QuicklyAddedProduct (fkProduct long options (AUTO_INCREMENT true) ...

That will allow it to be omitted from the insert list.

[cjohn001]

Hello Steven,

I just changed the source table definition to the following:

CREATE FOREIGN TABLE QuicklyAddedProducts (

    fkProduct long AUTO_INCREMENT OPTIONS(NAMEINSOURCE '"fkProduct"', NATIVE_TYPE 'BIGINT'),

--  fkProduct long NOT NULL OPTIONS(NAMEINSOURCE '"fkProduct"', NATIVE_TYPE 'BIGINT'),

    fkProfile long NOT NULL OPTIONS(NAMEINSOURCE '"fkProfile"', NATIVE_TYPE 'BIGINT'),

    product_name string(20) NOT NULL OPTIONS(NAMEINSOURCE '"product_name"', NATIVE_TYPE 'VARCHAR'),

    energy_100g double  NOT NULL OPTIONS(NAMEINSOURCE '"energy_100g"', NATIVE_TYPE 'DOUBLE'),

    carbohydrates_100g double  NOT NULL OPTIONS(NAMEINSOURCE '"carbohydrates_100g"', NATIVE_TYPE 'DOUBLE'),

    proteins_100g double  NOT NULL OPTIONS(NAMEINSOURCE '"proteins_100g"', NATIVE_TYPE 'DOUBLE'),

    fat_100g double  NOT NULL OPTIONS(NAMEINSOURCE '"fat_100g"', NATIVE_TYPE 'DOUBLE'),

    CONSTRAINT "PRIMARY" PRIMARY KEY(fkProduct),

    CONSTRAINT fkQuicklyAddedProductsToProducts FOREIGN KEY(fkProduct) REFERENCES Products(idProduct),

    CONSTRAINT fkQuicklyAddedProductsToAccount FOREIGN KEY(fkProfile) REFERENCES Account(idProfile),

    CONSTRAINT fkQuicklyAddedProductsToAccount_idx INDEX(fkProfile)

) OPTIONS(NAMEINSOURCE '"QuicklyAddedProducts"', UPDATABLE 'TRUE', CARDINALITY '2');

You are right, I can now insert a new record without specifying a dummy key. However, I still obtain NULL from CONVERT(GENERATED_KEY('fkProduct'),long)

>> Some sources, like oracle, require you to specify the column names ahead of time.  When retrieving we then assume that we're matching the primary key columns. 

I did not understand how you mean this. How can I specify the column names ahead of time? Maybe this will solve my issue.

I googled reagrding the jdbc capabilities of my jdbc driver and assume that it should be supported given the info here.

MySQL :: MySQL Connector/J 8.0 Developer Guide :: 7.4 Retrieving AUTO_INCREMENT Column Values through JDBC

So do you know if the functionality is implemented in Teiid? I am not sure if getGeneratedKeys() is mysql specific and therefore may not be used from teiid yet.

So in case the retrieving of the generated key is not available, this would probably also denote that I will not be able to implement a solution like I suggested above. Hence, my moving the insert trigger logic into teiid as well. I would have the same issue than for the created record in the product table. Is my assumption correct?

I am wondering, when I do a normal insert via odata, as result I get the created primary key of the item back. This was an issue we fixed about half a year ago as far as I remember. Hence, I assume, it is not a constraint with the jdbc interface.

[shawkins]

> I did not understand how you mean this. How can I specify the column names ahead of time? Maybe this will solve my issue.

I did not mean for that to be an actionable statement on your part, just to highlight that the translator expects there to be a primary key on the table to retrieve the generated keys.

> So do you know if the functionality is implemented in Teiid? I am not sure if getGeneratedKeys() is mysql specific and therefore may not be used from teiid yet.

Yes the functionality is implemented: https://github.com/teiid/teiid/blob/06b9828a3fd6dafa358ff93f0a4803e69a3bfdf4/connectors/jdbc/translator-jdbc/src/main/java/org/teiid/translator/jdbc/JDBCUpdateExecution.java

It may still be an issue with the mysql driver expecting to only return generated keys specifically for auto increment columns.  Your situation may require the use of a mysql autoincrement column for this to work as Teiid is expecting through JDBC.  Alternatively you could check directly against mysql if last_insert_id() works the way you expect in this situation and log an enhancement around using that in the Teiid translator.

[cjohn001]

Well,

I will  try then to move the insert trigger to Teiid, than I should be able to go with autoincrement on the product table. If CONVERT(GENERATED_KEY('fkProduct'),long) than returns the key, I would have a solution as I use this key to add the record in QuicklyAddedProducts and the diary as well.

In my current trigger implementation in mysql I use last_insert_id(). Hence, there it is working like I expected. So I will add a feature request for teiid and  the topic as well.

[cjohn001]

Hello Steven,

I feel quite lost in the meantime as I seem to run into a real showstopper here. I now also tried to shift the insert trigger logic into Teiid, again without success. The CONVERT(GENERATED_KEY('fkProduct'),long) seems to be broken in TEIID.

Here is a very reduced example of what I am trying. I have now created a View QuicklyAddedProducts on the foreign table QuicklyAddedProducts_SRC. In its insert trigger the View shall first create a new record in a foreign table called Products  and use the primary key, which is autoincrement, of this  record as the primary key for the new record that shall be created via the QuicklyAddedProducts View. Code:

CREATE FOREIGN TABLE Products (

    idProduct long NOT NULL AUTO_INCREMENT OPTIONS(NAMEINSOURCE '"idProduct"', NATIVE_TYPE 'BIGINT'),

    fkDatabaseKey short NOT NULL OPTIONS(NAMEINSOURCE '"fkDatabaseKey"', NATIVE_TYPE 'TINYINT UNSIGNED'),

    CONSTRAINT "PRIMARY" PRIMARY KEY(idProduct),

    CONSTRAINT fkProductToDatabaseKeys FOREIGN KEY(fkDatabaseKey) REFERENCES DatabaseKeys(idDatabaseKey),

    CONSTRAINT fkProductToDatabaseKeys_idx INDEX(fkDatabaseKey)

) OPTIONS(NAMEINSOURCE '"Products"', UPDATABLE 'TRUE', CARDINALITY '657736');

CREATE FOREIGN TABLE QuicklyAddedProducts_SRC (

    fkProduct long AUTO_INCREMENT OPTIONS(NAMEINSOURCE '"fkProduct"', NATIVE_TYPE 'BIGINT'),

    fkProfile long NOT NULL OPTIONS(NAMEINSOURCE '"fkProfile"', NATIVE_TYPE 'BIGINT'),

    product_name string(20) NOT NULL OPTIONS(NAMEINSOURCE '"product_name"', NATIVE_TYPE 'VARCHAR'),

    energy_100g double  NOT NULL OPTIONS(NAMEINSOURCE '"energy_100g"', NATIVE_TYPE 'DOUBLE'),

    carbohydrates_100g double  NOT NULL OPTIONS(NAMEINSOURCE '"carbohydrates_100g"', NATIVE_TYPE 'DOUBLE'),

    proteins_100g double  NOT NULL OPTIONS(NAMEINSOURCE '"proteins_100g"', NATIVE_TYPE 'DOUBLE'),

    fat_100g double  NOT NULL OPTIONS(NAMEINSOURCE '"fat_100g"', NATIVE_TYPE 'DOUBLE'),

    CONSTRAINT "PRIMARY" PRIMARY KEY(fkProduct),

    CONSTRAINT fkQuicklyAddedProductsToProducts FOREIGN KEY(fkProduct) REFERENCES Products(idProduct),

    CONSTRAINT fkQuicklyAddedProductsToAccount FOREIGN KEY(fkProfile) REFERENCES Account(idProfile),

    CONSTRAINT fkQuicklyAddedProductsToAccount_idx INDEX(fkProfile)

) OPTIONS(NAMEINSOURCE '"QuicklyAddedProducts"', UPDATABLE 'TRUE', CARDINALITY '2');

CREATE VIEW QuicklyAddedProducts (

    fkProduct long AUTO_INCREMENT,

    fkProfile long NOT NULL,

    product_name string(20) NOT NULL,

    energy_100g double  NOT NULL,

    carbohydrates_100g double  NOT NULL,

    proteins_100g double  NOT NULL,

    fat_100g double  NOT NULL, 

    CONSTRAINT "PRIMARY" PRIMARY KEY(fkProduct),

    CONSTRAINT fkQuicklyAddedProductsToProducts FOREIGN KEY(fkProduct) REFERENCES Products(idProduct),

    CONSTRAINT fkQuicklyAddedProductsToAccount FOREIGN KEY(fkProfile) REFERENCES Account(idProfile),

    CONSTRAINT fkQuicklyAddedProductsToAccount_idx INDEX(fkProfile)

)

OPTIONS(UPDATABLE 'TRUE')

AS

SELECT

    qap.fkProduct as fkProduct,

    qap.fkProfile as fkProfile,

    qap.product_name as product_name,

    qap.energy_100g as energy_100g,

    qap.carbohydrates_100g as carbohydrates_100g,

    qap.proteins_100g as proteins_100g,

    qap.fat_100g as fat_100g

FROM QuicklyAddedProducts_SRC as qap;

CREATE TRIGGER ON QuicklyAddedProducts INSTEAD OF INSERT AS

    FOR EACH ROW

    BEGIN ATOMIC

       DECLARE long vIdProduct;

        INSERT INTO

            Products(fkDatabaseKey)

        VALUES

            (2);

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

        -- create a new record for the quickly added product

        INSERT INTO

            QuicklyAddedProducts_SRC(fkProduct, fkProfile, product_name, energy_100g, carbohydrates_100g, proteins_100g, fat_100g)

        VALUES

            (vIdProduct, new.fkProfile, new.product_name, new.energy_100g, new.carbohydrates_100g, new.proteins_100g, new.fat_100g);

    END;

I end up with the following error statement:

{"error":{"code":null,"message":"TEIID16016 Insert into QuicklyAddedProducts success, but failed to retrieve auto generated keys from source, thus failed to show result entity; Supply the key values. "}}

Do you see anything which I am doing wrong? Or is my assumption correct, that I run into an error in Teiid?

[shawkins]

> Do you see anything which I am doing wrong?

What is the source definition of Products?  Is idProduct defined as the primary key and auto increment?

> Or is my assumption correct, that I run into an error in Teiid?

I do see a couple of issues.  As it currently exists the feature works end to end only if the originating client statement is an eligible insert with the return generated keys directive set.

For example, with a pg source table defined as "create table auto (col serial primary key, col1 varchar)" the following happens with Teiid queries

s.execute("insert into auto (col1) values ('a');", Statement.RETURN_GENERATED_KEYS);

ResultSet rs = s.getGeneratedKeys(); //works

...

The generated key is returned

Issue 1:

ResultSet rs = s.executeQuery("begin insert into auto (col1) values ('a'); select cast(generated_key('col') as integer); end");

...

Returns a null value - not a top level insert nor is the Statement directive to return generated keys set

Issue 2:

s.execute("insert into auto (col1) values ('a');", Statement.RETURN_GENERATED_KEYS);

ResultSet rs = s.executeQuery("select cast(generated_key('col') as integer)");

...

Returns a null value - the generated_keys are accessible only in the scope for the current query, shown below.

If I create a view and trigger:

create view auto_v (col serial primary key, col1 varchar) options (updatable true) as select col, col1 from auto;

CREATE TRIGGER ON auto_v INSTEAD OF INSERT AS for each row

begin

insert into auto (col1) values (new.col1);

insert into auto (col1) values (cast(generated_key('col') as integer));

end;

Then:

s.execute("insert into auto_v (col1) values ('a');", Statement.RETURN_GENERATED_KEYS);

Will succeed and two rows will be inserted into auto.  The second one will successfully reference the generated key from the first insert.

Issue 3:

s.execute("insert into auto_v (col1) values ('a');", Statement.RETURN_GENERATED_KEYS);

ResultSet rs = s.getGeneratedKeys();

...

Returns an empty result.  By the current logic the generated keys are not allowed to escape a procedure invocation.

We'd need to relax several of the assumptions to make this more generally usable and more like just a last key reference, rather than a very specific generated key.

Can you log an issue to capture which of these you may need addressed?

[cjohn001]

Hello Steven,

thanks for your help!

>>What is the source definition of Products?  Is idProduct defined as the primary key and auto increment?

Yes it is a primary key and autoincrement. The below command creates the table in the mysql database

-- -----------------------------------------------------

-- Table `my_nutri_diary`.`Products`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `my_nutri_diary`.`Products` (

  `idProduct` BIGINT(18) NOT NULL AUTO_INCREMENT,

  `fkDatabaseKey` TINYINT(8) UNSIGNED NOT NULL,

  PRIMARY KEY (`idProduct`),

  INDEX `fkProductToDatabaseKeys_idx` (`fkDatabaseKey` ASC) VISIBLE,

  UNIQUE INDEX `idProductInProducts_UNIQUE` (`idProduct` ASC) VISIBLE,

  CONSTRAINT `fkProductToDatabaseKeys`

    FOREIGN KEY (`fkDatabaseKey`)

    REFERENCES `my_nutri_diary`.`DatabaseKeys` (`idDatabaseKey`)

    ON DELETE NO ACTION

    ON UPDATE NO ACTION)

ENGINE = InnoDB;

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

>>  if the originating client statement is an eligible insert with the return generated keys directive set.

I have not explicitely set a directive in DML yet. How would the equivalent directive have to look like in a DDL file?

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Regarding the issues you mentioned I am not 100% sure if I understand the differences correctly. The commands you provided are from Java code not DDL?

I assume I run into issue 3 and maybe also 2. Maybe you could check if I am right with my assumption.

So what I like to do:

Variant 1:

Is inserting into a view which aggregates data from 3 different tables, see ddl attached (foreign tables in the definition exactly match the source table definitions in the underlaying mysql database). The relevant view is called Diary. The insert trigger will always create a single new record in Diary for a Product that shall be added to the Diary. Depending on the type of Product (QuicklyAddedProduct in my current situation) a base Product record in table Products needs to be created first, then its primary key is used in table QuicklyaddedProducts as a foreign key. I am trying to implement inheritance with this kind of table structure where certain types of products derive their primary key from a base record in Product table.

So the insert trigger looks as follows, the relevant part is given in the  ELSE IF (new.fkDatabaseKey = 2) clause:

As I need to use the generated key in a select on a second table it is variant 2 or 3 regarding your explanation?

Please note the following code already results in a NULL response for generated key""

                INSERT INTO

                    Products(fkDatabaseKey)

                VALUES

                    (new.fkDatabaseKey);

                -- retrieve generated key

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

The full insert trigger code is given as below:

CREATE TRIGGER ON Diary INSTEAD OF INSERT AS

  FOR EACH ROW

  BEGIN ATOMIC

      DECLARE long vIdProduct;

      -- a product from the fdb database is simply added

      IF(new.fkDatabaseKey = 1)

      BEGIN

          IF(CHANGING.fkProfile AND CHANGING.AddedDateTime AND CHANGING.fkProduct AND CHANGING.MealNumber AND CHANGING.AmountInG)

                INSERT INTO

                    Diary_SRC(fkProfile, AddedDateTime, fkProduct, MealNumber, AmountInG)

                VALUES

                    (new.fkProfile, new.AddedDateTime, new.fkProduct, new.MealNumber, new.AmountInG);

        END

        -- for a quickly added product, we first need to create a new product record

        ELSE IF (new.fkDatabaseKey = 2)

        BEGIN

            IF(CHANGING.fkProfile AND CHANGING.AddedDateTime AND CHANGING.MealNumber AND

            CHANGING.product_name AND CHANGING.energy_100g AND CHANGING.carbohydrates_100g AND

            CHANGING.proteins_100g AND CHANGING.fat_100g)

            BEGIN

                -- create a new record in products, as this is this results in a common primary key

                -- for all product databases

                INSERT INTO

                    Products(fkDatabaseKey)

                VALUES

                    (new.fkDatabaseKey);

                -- retrieve generated key 

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

                -- first we create a new record for the quickly added product

                INSERT INTO

                        QuicklyAddedProducts(fkProduct, fkProfile, product_name, energy_100g, carbohydrates_100g, proteins_100g, fat_100g)

                VALUES

                        (vIdProduct, new.fkProfile, new.product_name, new.energy_100g, new.carbohydrates_100g, new.proteins_100g, new.fat_100g);

/*              INSERT INTO

                    Diary_SRC(fkProfile, AddedDateTime, fkProduct, MealNumber, AmountInG)

                VALUES

                    (new.fkProfile, new.AddedDateTime, vIdProduct, new.MealNumber, new.AmountInG);

*/

            END

            ELSE

            BEGIN

                DECLARE EXCEPTION e = SQLEXCEPTION 'Incomplete argument list for quickly added item specified' SQLSTATE '45000';

                RAISE e;

            END

        END

    END;

Variant 2:

Would be according to the implementation I added in my previous comment for QuicklyAddedProduct. I could shift the creation of a Product into a INSERT trigger for QuicklyAddedProdut. Than the INSERT trigger from QuicklyAddedProduct would be called from the INSERT trigger on  Diary. This would be my preferred approach as it looks more clean to me.

As the generated key is asked for in a select in a second indirectly called insert trigger  it is variant 3 regarding your explanation?

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Further question: As I am currently locked by this issue I plan to switch to a nightly build as soon as the required change moved into the teiid code. To use it I would need to build a Teiid Wildfly overlay like you provide it as Download on the Teiid homepage. I have not found a description on how to build it myself from sources. Is this documented somewhere? Or could you give me an explanation how to proceed?

[shawkins]

> I have not explicitely set a directive in DML yet. How would the equivalent directive have to look like in a DDL file?

There isn't one.  It's strictly a JDBC construct - that's what I'm highlighting in the issue list that it makes usage of generated_key in a procedure difficult as you only have access to it in a very narrow circumstance.

> As I need to use the generated key in a select on a second table it is variant 2 or 3 regarding your explanation?

It's not your procedure code that's the issue.  It's that the retrieval of generated keys it tied to having the Teiid JDBC client request their retrieval:

s.execute("insert into auto (col1) values ('a');", Statement.RETURN_GENERATED_KEYS);

And that only works if you are executing an insert that has a auto increment primary key.

> I have not found a description on how to build it myself from sources. Is this documented somewhere? Or could you give me an explanation how to proceed?

See GitHub - teiid/teiid: Teiid is a data virtualization system that allows applications to use data from multiple, heteroge…

The build target listed in that readme is the simpliest:  mvn clean install -P dev -s settings.xml

If you don't have maven installed, we could add an option to use the maven wrapper mvnw instead.

The readme should also call out that the wildfly related artifacts have been completely separated into their own module: https://github.com/teiid/teiid/tree/master/wildfly

Once that top level build has been run, the wildfly release artifacts are actually located under wildfly/wildfly-build/target