CODEWITHSUNDEEP
oracle-databasegrailsplsqlgrails-orm
Topera
Topera

Reputation: 12389

How to get next id in GRAILS?

I need to get the next available id in a sequence, using GORM or native query. How can I do this?

I'm using oracle.

UPDATE:

I need this value BEFORE insert, because my row will use this value. I have table called IMAGE, with column FILE_NAME, like this:

- ID | FILE_NAME
- 123 | foo_bar_123.png
- 124 | xxx_yyy_124.png

Tks a lot.

Upvotes: 3

Views: 4004

Answers (2)

Topera
Topera

Reputation: 12389

I adapted @Cave tips. So, my solution is:

Change my mapping from:

class Image {
...
id column: "ID", generator: "sequence", params: [sequence:"MY_SEQUENCE"]
...
}

to:

class Image {
...
id column: "ID", generator: "assigned"
...
}

And set id mannualy using this:

def getLastImageId(){
  def sql = "SELECT MY_SEQUENCE.nextval FROM dual"
  def query = sessionFactory.currentSession.createSQLQuery(sql);
  def result = query.list()
  return result[0]
}
...
newImage.id = getLastImageId()
newImage.fileName = "foo_bar_${newImage.id}.png"

Upvotes: 1

Justin Cave
Justin Cave

Reputation: 231691

Why do you need the value before the insert? Could you get the information as part of the insert using the RETURNING clause, i.e.

INSERT INTO table_name( id, file_name )
  VALUES( some_sequence.nextval, <<bind variable>> )
  RETURNING id INTO <<bind variable>>

Or access it after the insert using

SELECT sequence_name.currval
  FROM dual

The currval of a sequence returns the most recent value of the sequence produced in the current session so it is actually thread-safe. Since sequences are designed to provide numbers in a highly concurrent environment, you cannot in general find out what the nextval is going to be unless you actually fetch the nextval. Even if you could, there is no guarantee that another thread won't come along and get the value you peeked at before your INSERT took place so it wouldn't be safe to peek at the nextval in a multi-user environment.

Upvotes: 2

Related Questions