[jira] Commented: (OPENJPA-736) Combine insert and select SQL together for generated Id strategy=GenerationType.IDENTITY

["Milosz Tylenda (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/OPENJPA-736?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12707080#action_12707080 ] 

Milosz Tylenda commented on OPENJPA-736:
----------------------------------------

Now when I have finally got SQL Server 2008 at my database pasture (what a glorious day! :), I have successfully tested the proposed solution with DB2, MySQL, PostgreSQL and SQL Server. I have committed the change to the trunk. I have made DBDictionary.convertSchemaCase public as the invocation is needed for PostgreSQL.

A few more things to do:
1. Currently the support for JDBC 3 generated keys retrieval is auto-detected. I think we should also allow users to enable/disable it explicitly. Some people may want the old behaviour (issuing a database-specific query to fetch generated keys) for some reasons. Also, if a bug is lurking somewhere, we have an easy workaround by disabling the feature.
2. TestGenerationType could be extended to test both ways of generated keys retrieval where possible.
3. The user manual might need an update.
4. Apply the changes to 1.3.x.


> Combine insert and select SQL together for generated Id strategy=GenerationType.IDENTITY 
> -----------------------------------------------------------------------------------------
>
>                 Key: OPENJPA-736
>                 URL: https://issues.apache.org/jira/browse/OPENJPA-736
>             Project: OpenJPA
>          Issue Type: Improvement
>    Affects Versions: 1.3.0
>            Reporter: Fay Wang
>            Assignee: Milosz Tylenda
>             Fix For: 1.3.0
>
>         Attachments: openjpa-736.patch
>
>
> Currently if the strategy of the generated id is GenerationType.IDENTITY,  when an entity object is created, openjpa will generate two SQL statements. The following is an example running on DB2:
> (1) INSERT INTO EntityA (col1, col2, col3, version) VALUES (?, ?, ?, ?) 
> [params=(int) 1, (int) 1, (int) 1, (int) 1]
> (2) SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1
> A performance improvement is to take advantage of the "select from final table" feature in DB2 to combine the insert and select statement into a single SQL statement as shown below:
> SELECT id FROM FINAL TABLE (INSERT INTO EntityA (col1, col2, col3, version) VALUES (?, ?, ?, ?) )

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.