This might be a total newbee question, but I've been searching for hours and can't seem to find anything on this.

Were doing a test project on moving from all Oracle SP to NHibernate.
In our insert_customer(v_firstname, v_lastname, etc.) we use a sequence(CUSTOMER_ID_SEQ) to generate the pk(customer_id).

Problem is:

The sequence is not used directly, its appended a matchcode.
The sequence is also used to set the external_id column if external_id is not supplied.

Per now i have:



But i cant seem to figure out how to use the sequence to update external_id without generating a new number(obviously wrong) or how to append to the sequence number before inserting.

I'm thinking this can be implemented either by calling a SP or possibly by querying the db for sequence.nextval() and updating the properties before persisting, as .net does not have the Sequence value at the time the sql is generated.

But i was hoping hibernate supplied a more clean solution as mine sort of makes the move to hibernate kinda a mute.

You can specify custom sql statements for insert/update:

http://www.hibernate.org/hib_docs/nhibernate/1.2/reference/en/html/querysql.html#querysql-cud

Thanks for the replay, but i already know that.
It might be possible to solve the matchcode problem with this, but how does it solve the sequence problem?

I would use a trigger for that. If you don't want that, you probably can write a update statement that calls some thing like external_id = NVL(?, sequence.nextval()).

another way could be in the EO classes, by changing the propperty for your external_id column, to return the customer_id, when the external_id is still 0.

when i understan hibernate correctly, it will first check if the customer_id is 0, when yes, then a new id will be taken from the sequence and pushed to the variable. After this, hibernate will get all the values from the variables and place them into an insert statement. At this time, the customer_id is already filled, and when hibernate asks your property for the external_id, it will return the new customer_id (when the external_id was 0) and the insert should contain the value that you want.

As I haven't tested this right now, I can not guarantee this works, but should.

I'm afraid that won't work that way. When you use a database based identity, hibernate will do the insert as soon as you call session.Save() in order to obtain the id. But it also inserts the other values with that call, because otherwise you wouldn't be able to apply any not null constraints.

But the approach might still work. Map external_id with access="field" and a default of 0 or -1 (whatever suits your logic). In the getter you can check it external_id is 0/-1 and return the id in that case.

This would work as long as you only access your database through the application and you don't have any joins involing external_id.