Sequences and return values

[Dan Di Spaltro <da...@gmail.com>]

This may be my misunderstanding of how JDBC works and what the contract is,
but is there a way to return the sequence value after issuing an upsert?
Seems like that would be a common operation but I may be missing something
because I can't figure out how...

Thanks!

-Dan

-- 
Dan Di Spaltro

Re: Sequences and return values

[Dan Di Spaltro <da...@gmail.com>]

I was also thinking I could do something like give every table that I want
to know the future value a transaction_id.  Then I could index the
transaction_id and query that after insertion but it just seems backwards.
Is it impossible to support the getGeneratedKeys call?  I guess I have a
fundamental lack of understanding of how sequences are implemented to
understand if its possible.
This is a bit tricky, since your atomic sequence may be being changed by
other clients as well. You have a couple of options:
1) Do a SELECT NEXT VALUE FOR my_sequence first. You could pretty much
issue this against any known table with a LIMIT of 1 and get your sequence
value back.
2) Do an UPSERT INTO foo SELECT NEXT VALUE FOR my_sequence FROM bar to
populate another table with your sequence values. If you can identify the
new rows you've upserted, you can get your sequence values back. If you
need to, you could create a temporary sequence right before this, purely
for assigning an position_number column value for ordering and query them
back from the table you upserted into. We're doing something along these
lines to implement a "query-more" across any query capability.

Thanks,
James


On Sun, Mar 30, 2014 at 6:00 PM, Dan Di Spaltro <da...@gmail.com>wrote:

> This may be my misunderstanding of how JDBC works and what the contract
> is, but is there a way to return the sequence value after issuing an
> upsert? Seems like that would be a common operation but I may be missing
> something because I can't figure out how...
>
> Thanks!
>
> -Dan
>
> --
> Dan Di Spaltro
>

Re: Sequences and return values

[James Taylor <ja...@apache.org>]

This is a bit tricky, since your atomic sequence may be being changed by
other clients as well. You have a couple of options:
1) Do a SELECT NEXT VALUE FOR my_sequence first. You could pretty much
issue this against any known table with a LIMIT of 1 and get your sequence
value back.
2) Do an UPSERT INTO foo SELECT NEXT VALUE FOR my_sequence FROM bar to
populate another table with your sequence values. If you can identify the
new rows you've upserted, you can get your sequence values back. If you
need to, you could create a temporary sequence right before this, purely
for assigning an position_number column value for ordering and query them
back from the table you upserted into. We're doing something along these
lines to implement a "query-more" across any query capability.

Thanks,
James


On Sun, Mar 30, 2014 at 6:00 PM, Dan Di Spaltro <da...@gmail.com>wrote:

> This may be my misunderstanding of how JDBC works and what the contract
> is, but is there a way to return the sequence value after issuing an
> upsert? Seems like that would be a common operation but I may be missing
> something because I can't figure out how...
>
> Thanks!
>
> -Dan
>
> --
> Dan Di Spaltro
>