Problems with Hibernate and 10.2B

Posted by sedge on 25-May-2011 00:17

We want to use JDBC and Hibernate with an OpenEdge 10.2B Database. We are adopting the usual approach with Hibernate and having the database generate Record IDs as keys. With OpenEdge this is implemented by Sequences. Mostly it is working except for a couple of problems with Sequences.

1) Can't insert into an empty table

If the table is empty Hibernate cannot insert a new record. Once a record has been inserted from another source we can insert records from Hibernate without problems.

Here are the error details:

25/05/2011 1:48:24 PM org.hibernate.util.JDBCExceptionReporter logExceptions

WARNING: SQL Error: 0, SQLState: 24000

25/05/2011 1:48:24 PM org.hibernate.util.JDBCExceptionReporter logExceptions

SEVERE: [DataDirect][OpenEdge JDBC Driver]Invalid operation for the current cursor position.

org.hibernate.exception.GenericJDBCException: could not get next sequence value

org.hibernate.exception.GenericJDBCException: could not get next sequence value

at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:140)

at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:128)

2) Record IDs don't match the Sequence.
Hibernate has big gaps in the record IDs: 400, 550, 750, 800
And these don't have any relationship with the DB Sequence. If I execute

select pub.sonicMessage_sequence.nextval from pub.Message;

It returns numbers like 19 and 20.

We had to do a fair bit of digging to get this far. The default Dialect for OpenEdge didn't work and  we've done our own Hibernate build with a modified Dialect, based on Hibernate 3.6.0. The Dialect is attached.

Can anyone give us any insight into these two issues?

Thanks

Steve

ProgressDialectUpdated.java.zip

All Replies

Posted by Admin on 26-May-2011 07:00

1) Can't insert into an empty table

If the table is empty Hibernate cannot insert a new record. Once a record has been inserted from another source we can insert records from Hibernate without problems.

this is because the getSequenceNextValString returns a string that does a  select on the table itself (for which you want to get the sequence next  value) which if the table is empty won't return anything.

not having any experience with hibernate I'll say you can try to change that to simple return sequence.nextval

public String getSequenceNextValString(String sequenceName) throws MappingException {
  // TODO Auto-generated method stub
    return getSelectSequenceNextValString( sequenceName );
}

or make a 'dual' table with a single record to be sure you get the sequence value if you do a select on it

public String getSequenceNextValString(String sequenceName) throws MappingException {
  // TODO Auto-generated method stub
    return "select " + getSelectSequenceNextValString( sequenceName ) + " from dual";
}

2) Record IDs don't match the Sequence.

Hibernate has big gaps in the record IDs: 400, 550, 750, 800

that could be very much related to the first issue... on each insert the  sequence number is increased not by one but by the number of records  found in that table (it's a select on that table with no clause, no  limit)

Posted by Thomas Mercer-Hursh on 26-May-2011 10:51

it's a select on that table with no clause, no  limit

If that means what I think it does, it sounds like a performance problem.

Posted by Admin on 26-May-2011 11:49

tamhas wrote:

it's a select on that table with no clause, no  limit

If that means what I think it does, it sounds like a performance problem.

well yes, doing a full table scan on each insert might add-up something  but not really noticeable at first sight... the largest id mentioned was  800 and given the almost exponential grow of sequence value (because of  the way it was retrieved) means that there weren't too many records to  start experiencing performance problems

just for the record, seems that each table got his own sequence  "

_sequence" and the statement used to get the next sequence  value was something like "select
_sequence.nextval from 
"

but there is something else that bugs me... the simple use of hibernate  makes me think that the business logic is not in ABL but in Java, is  that something that uses the Progress database only as an SQL one, is  that the first case of this kind, and (the obviously) WHY???

Posted by Thomas Mercer-Hursh on 26-May-2011 12:08

Tom Bascom did a search a while back for anyone doing substantial update activity from SQL against an OE database and my impression is that he didn't find much and that performance was a potential problem.

Posted by sedge on 26-May-2011 17:01

"but there is something else that bugs me... the simple use of hibernate  makes me think that the business logic is not in ABL but in Java, is  that something that uses the Progress database only as an SQL one, is  that the first case of this kind, and (the obviously) WHY???  "

(Sorry, couldn't figure out how the Quote works)

Yes, we're working with an OpenEdge DB from Java via Hibernate. Not our first choice of DB for this task (and seems justified given the problems we're having). The background is:

- We are a Progress shop with an ERP written in Progress.

- We are replacing an existing ESB with Sonic

- We are loosely coupling the ESB with our ERP (which isn't Progress marketing policy)

- The ESB needs a DB to support Conitnuation, Logging and Message control support

- Our Progress Sales Rep went ballistic when we looked like using another DB for the job.

- It made some sense for the ESB to use an OpenEdge DB because we'll be able to access and maintain it from the ERP.

- No, we are not writing Business logic in Java. The ESB will only communicate with the ERP via messaging.

- We have good reasons for not passing the tasks off to an AppServer

Posted by sedge on 27-May-2011 01:07

Thanks Marian

The method you suggested appears to have worked. We created a dummy file with a single column and a single record. and modified the Hibernate Dialect.

To insert a record into a file called LogHeader the SQL for the sequence generation is now:

SELECT pub.LogHeader_Sequeuce.nextval from pub.dummyFile

I guess a better solution would be to refer to an OpenEdge dictionary file that we knew would only ever have exactly one record so that the Hibernate Dialect would be robust and not specific to our implementation. Any suggestions?

I note that OpenEdge has a Column type of RECID, although the documentation suggests that this is only for backward compatibility. This sounds to be more like the MySQL auto-generated ID that Hibernate implements as "IDENTITY", rather than "SEQUENCE". We didn't go down that path because:

- We didn't have any idea whether OpenEdge would honour the SQL to update it.

- It would have meant  changing the Dialect to implement IDENTITY support.

If anyone wants to try out that path we's be more than interested in the outcome.

Regards

Steve

Posted by Admin on 27-May-2011 01:24

SELECT pub.LogHeader_Sequeuce.nextval from pub.dummyFile

have you tried to use only pub.LogHeader_Sequeuce.nextval instead of that select from 'dual'?

not sure how hibernate will build the insert statement but instead of

insert into table values (select seq.nextval from dual, val, val...)

will be something like

insert into table values (seq.nextval, val, val...)

Record IDs are now being generated in steps of 50, which is still a little odd.

how are the sequences defined, there might just have a 'step' set to 50

I note that OpenEdge has a Column type of RECID, although the documentation suggests that this is only for backward compatibility. This sounds to be more like the MySQL auto-generated ID that Hibernate implements as "IDENTITY", rather than "SEQUENCE". We didn't go down that path because:

- We didn't have any idea whether OpenEdge would honour the SQL to update it.

- It would have meant  changing the Dialect to implement IDENTITY support.

If anyone wants to try out that path we's be more than interested in the outcome.

you should never use recid or rowid as identifier... it will blow everything in case of a dump/load.

Posted by Admin on 27-May-2011 01:48

Yes, we're working with an OpenEdge DB from Java via Hibernate. Not our first choice of DB for this task (and seems justified given the problems we're having). The background is:

- We are a Progress shop with an ERP written in Progress.

- We are replacing an existing ESB with Sonic

- We are loosely coupling the ESB with our ERP (which isn't Progress marketing policy)

- The ESB needs a DB to support Conitnuation, Logging and Message control support

- Our Progress Sales Rep went ballistic when we looked like using another DB for the job.

- It made some sense for the ESB to use an OpenEdge DB because we'll be able to access and maintain it from the ERP.

- No, we are not writing Business logic in Java. The ESB will only communicate with the ERP via messaging.

- We have good reasons for not passing the tasks off to an AppServer

all this means that the OE database is used for persistence by the ESB? which is ESB Sonic or something else?

if that is true then it might make sense for the Progress Sales Rep to use a Progress DB but not as much for me, the ESB will probably require a very fast update rate and I don't think Progress SQL engine is the best choice for that

Posted by sedge on 27-May-2011 02:37

Solved the mystery of the steps in the sequence:

http://stackoverflow.com/questions/2243091/hibernate-and-strange-behavior-with-db2-sequences


It seems that Hibernate does that as a performance saver. I ran a test inserting one Header and two Detail records:

- The Header ID was 50 after the previous one
- The first Detail ID was 50 after the previous one.
- The second Detail ID was incremented by 1.

The SQL command log shows that Hibernate only did one nextval for the Header and one nextval for the Detail sequences.


This could be something to watch for if we were updating with anything but Hibernate.


Regards

Steve

Posted by Thomas Mercer-Hursh on 27-May-2011 11:38

RECID and ROWID are values which relate to the physical position of the record on disk, not sequential identifiers.

I think the 50 interval comes to support adding batches of records without having to get a new sequence for each one.

Posted by sedge on 29-May-2011 18:05

Solved the mystery of the steps in the sequence:

http://stackoverflow.com/questions/2243091/hibernate-and-strange-behavior-with-db2-sequences


It seems that Hibernate does that as a performance saver. I ran a test inserting one Header and two Detail records:

- The Header ID was 50 after the previous one
- The first Detail ID was 50 after the previous one.
- The second Detail ID was incremented by 1.

The SQL command log shows that Hibernate only did one nextval for the Header and one nextval for the Detail sequences.


This could be something to watch for if we were updating with anything but Hibernate.


Regards

Steve

Posted by sedge on 29-May-2011 18:07

The only remaining question is whether we can use a file from the catalogue which will always contain exactly one record, instead of creating our own dummy table.

Posted by Admin on 30-May-2011 01:02

sedge wrote:

The only remaining question is whether we can use a file from the catalogue which will always contain exactly one record, instead of creating our own dummy table.

I'll go for _Db system table, it should have only one record but just to be safe use top 1

select top 1 * from PUB."_Db"

Posted by sedge on 30-May-2011 03:29

I'll go for _Db system table, it should have only one record but just to be safe use top 1

select top 1 * from PUB."_Db"

Didn't work but from the Progress SQL Manual

SYSCALCTABLE   A single row with a single column set to the value 100.

so it looks like we can use: SELECT * FROM sysprogress.syscalctable

This thread is closed