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

[Lukáš Svačina (JIRA)]

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

Lukáš Svačina commented on TEIID-5397:
--------------------------------------

Thanks for your response. 

With "PRIMARY KEY (id)" defined it works (AUTO_INCREMENT is not necessary).
However i'm working with dynamic data sources where i can leverage using syntax "CREATE VIEW xxx AS query" i.e. I don't have to know column names and  their TEIID data types (very important for me because i don't have to do any explicit data type conversion to teiid types like implicit type conversion does).

Is there any other syntax allowing to specify PRIMARY KEY for view WITHOUT need for enumerating all columns and their data types (teiid non standard data types)?

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