[teiid-issues] [JBoss JIRA] (TEIID-5397) RETURN_GENERATED_KEYS not working

[Lukáš Svačina (JIRA)]

Lukáš Svačina created TEIID-5397:
------------------------------------

             Summary: RETURN_GENERATED_KEYS not working
                 Key: TEIID-5397
                 URL: https://issues.jboss.org/browse/TEIID-5397
             Project: Teiid
          Issue Type: Bug
    Affects Versions: 10.2.1
            Reporter: Lukáš Svačina
            Assignee: Steven Hawkins


*+VDB:+*
<vdb name="_GENERATED_form_shoes" version="2"><model name="_INTERNAL_internalModel" type="PHYSICAL"><source name="internal_postgresql" translator-name="postgresql" connection-jndi-name="java:/internal"/></model><model name="GEN_view" type="VIRTUAL"><metadata type="DDL"><![CDATA[ CREATE VIEW "_view_workflow_data" OPTIONS (UPDATABLE 'true') AS SELECT "public"."form_shoes_2"."size" AS "size", "public"."form_shoes_2"."model" AS "model", "public"."form_shoes_2"."id" AS "id" FROM "public"."form_shoes_2" ]]></metadata></model></vdb>

*+TABLE:+*
name: form_shoes_2
columns: id (SERIAL) | size (INTEGER *NULLABLE*) | model (VARCHAR *NULLABLE*)

*+PROBLEM:+*
Connected into VDB using JDBC like:

{code:java}
final PreparedStatement statement = c.prepareStatement(...INSERT..., Statement.RETURN_GENERATED_KEYS);
statement.executeUpdate();
final ResultSet generatedKeys = statement.getGeneratedKeys();
{code}

*generatedKeys is empty if:*
# INSERT INTO "form_shoes_2" ( "model" ) VALUES ( 'adidas x1' ) ...... e.g. not all columns are enumerated ... if so, you can provide NULL values to optional columns and generated keys WORK!
# INSERT INTO "_view_workflow_data" (id, name, size) VALUES (42, 'adidas x2', 12 ) ....... e.g. insering into view (1:1, no joins involved) even when all columns ARE enumerated ... probably no way how to get generated keys here?


*+QUESTIONS:+*
# How to get last_insert_id() when inserting into foreign table/views (with/without joins)?
# How to get updated rows (UPDATE ... RETURNING *)? At least primary keys of affected rows?

Thanks for fixing/adding this functionality.



--
This message was sent by Atlassian JIRA
(v7.5.0#75005)