[teiid-issues] [JBoss JIRA] (TEIID-5397) Make RETURN_GENERATED_KEYS work with views

[Steven Hawkins (JIRA)]

     [ https://issues.jboss.org/browse/TEIID-5397?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Steven Hawkins updated TEIID-5397:
----------------------------------
    Component/s: Query Engine


> Make RETURN_GENERATED_KEYS work with views
> ------------------------------------------
>
>                 Key: TEIID-5397
>                 URL: https://issues.jboss.org/browse/TEIID-5397
>             Project: Teiid
>          Issue Type: Feature Request
>          Components: Query Engine
>    Affects Versions: 10.2.1
>            Reporter: Lukáš Svačina
>            Assignee: Steven Hawkins
>             Fix For: 11.1
>
>
> *+VDB:+*
> {code}
> <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>
> {code}
> *+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)