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

[Ramesh Reddy (JIRA)]

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

Ramesh Reddy commented on TEIID-5397:
-------------------------------------

You need to define "id" column on "_view_workflow_data" as "AUTO_INCREMENT", and whatever column you are backing up with the "id" (here "public"."form_shoes_2"."id") must also be defined with autogeneration. 

If the source does not support the autogeneration, then you can write a function in the view layer, that does the sequence generation and use that in "instead of trigger" code for insert of that view to insert the value of "id" when "id" is not provided.

> 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:+*
> {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)