[teiid-issues] [JBoss JIRA] Commented: (TEIID-1131) Document/expand sequence support

[Steven Hawkins (JIRA)]

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

Steven Hawkins commented on TEIID-1131:
---------------------------------------

in looking at sequence options, the clearest choice is to only support next value.  curval is problematic since it is generally session dependent and we may not be the same source connection.  This is also somewhat consistent with sql 2006, which only has "NEXT VALUE FOR <sequence>"

- whatever support is added,  it would be good to add support for the sequence generator in hibernate, which would suggest that it needs to be a standalone retriveable value and not just used as a default (currently supported in a workaround fashion only for oracle)

- currently with Hibernate the workaround is to use the native support - which would default to the table generator or support for the GUIDGenerator was just added via the uuid function.

Possible design choices are:

1. proper metadata support
   - won't work in designer until a metamodel update
   - ideally needs import support

2. function approach, e.g. nextval('x')
   - needs a notion of what are valid sequence names

In any case there are significant calling differences among sources:
- h2 nextval([<schema>],<sequencename>)
- postgres nextval(<sequencename>)
- hsql NEXT VALUE FOR <sequencename>
- d2/oracle <sequencename>.nextval
etc.

which may need slightly modified sql to select a standalone value.  We can use the Hibernate Dialects to determine what should be called.

On a related note, we should also add enhancement to support generated keys.



> Document/expand sequence support
> --------------------------------
>
>                 Key: TEIID-1131
>                 URL: https://issues.jboss.org/browse/TEIID-1131
>             Project: Teiid
>          Issue Type: Feature Request
>          Components: JDBC Connector, Query Engine
>    Affects Versions: 7.0
>            Reporter: Steven Hawkins
>            Assignee: Steven Hawkins
>             Fix For: 7.4
>
>
> Currently sequence workaround logic only exists for oracle and is undocumented.  We should look at expanding sequence support - even for dynamic vdbs, see SQuriel's handling of system queries for retrieving sequence metadata.
> At least allowing the workaround logic to work for all sources that support sequences (Postgres, DB2, etc.) would be good.

-- 
This message is automatically generated by JIRA.
-
For more information on JIRA, see: http://www.atlassian.com/software/jira