• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

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

 
Ranch Hand
Posts: 69
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Bartender
Posts: 2407
36
Scala Python Oracle Postgres Database Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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?





 
Ranch Hand
Posts: 43
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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!
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic