• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Switching from MySQL to PostgreSQL: SQLGrammarException: could not get next sequence value

 
Russell Bateman
Ranch Hand
Posts: 69
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Folks, I have just switched the underlying database from MySQL to PostgreSQL (corporate requirement) and now my reads work fine, but my writes do not. In other words:
...throws
org.hibernate.exception.SQLGrammarException: could not get next sequence value

In the Eclipse debugger, examining HibernateException, I see:

(exception) SQLGrammarException
(cause) PSQLException
(detailMessage) "could not get next sequence value"
(sql) "select nextval( 'hibernate_sequence' )"


Now, I am able to throw anything I try into the database by hand (via psql). There is no problem of privilege. And, as I say, I can read out (via Hibernate Criteria) anything I like and do same using psql. I will confess I have no experience so far in switching Hibernate dialects and this is the first time I've used PostgreSQL.

I have created a very minimal table, Fun, to see if I'm using some columns wrong in the eyes of PostgreSQL, but no matter if I reduce the object to just a single, integer field, I still get this, so it's not about using some bizarre thing like DATE, TIMESTAMP or even some OneToOne, OneToMany, etc. construct incorrectly.

(FYI) hibernate.cfg.xml:

(used to be)

I thought there wouldn't be too much to do beyond this configuration switcheroo. I'd be grateful if anyone could point me somewhere to go.

Much thanks,

Russ Bateman
 
chris webster
Bartender
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
No idea about Hibernate, but have you checked that the SQL to fetch the sequence value actually works in your SQL shell, in case there's some problem with the sequence definition/grants?





 
Bogdan Baraila
Ranch Hand
Posts: 43
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you use "native" as your objects id generation class then you need a sequence in the database that will generate your ids. By default hibernate is looking for a sequence named "hibernate_sequence". If you don't have it then you can generate it using a query like this:

CREATE SEQUENCE hibernate_sequence
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 5;
 
Russell Bateman
Ranch Hand
Posts: 69
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Profuse thanks. Both these responses were useful to me and it all makes complete sense now. I had only direct experience in using Hibernate atop MySQL, apparently an easier thing to do. Thanks again, friends!
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic