Win a copy of Transfer Learning for Natural Language Processing (MEAP) this week in the Artificial Intelligence and Machine Learning forum!
  • 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 all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • Paul Clapham
  • Devaka Cooray
  • Bear Bibeault
Sheriffs:
  • Junilu Lacar
  • Knute Snortum
  • Liutauras Vilda
Saloon Keepers:
  • Ron McLeod
  • Stephan van Hulst
  • Tim Moores
  • Tim Holloway
  • Piet Souris
Bartenders:
  • salvin francis
  • Carey Brown
  • Frits Walraven

Hibernate sql command not properly ended - Bad SQL Grammar

 
Greenhorn
Posts: 17
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm using Hibernate on Oracle 19c. The hibernate config uses  <property name="hibernate.dialect">org.hibernate.dialect.Oracle12cDialect</property>

Issue: Retrieving data through a session.get(Class, Id) throws an SQLGrammarException   .

Details: The first half of the code inserts a record into the table APP_USER successfully. However, retrieving a random record (second half ) throws this Exception.





Trace:
Hibernate: insert into APP_USER (EMAIL_ADDRESS, INSERT_DATE, ACTIVE, ADMIN, LOGIN_ID, NAME, PASSWORD, UPDATE_DATE, id) values (?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: select a1_0.id, a1_0.EMAIL_ADDRESS, a1_0.INSERT_DATE, a1_0.ACTIVE, a1_0.ADMIN, a1_0.LOGIN_ID, a1_0.NAME, a1_0.PASSWORD, a1_0.UPDATE_DATE from APP_USER as a1_0 where a1_0.id = ?
Mar 06, 2020 12:17:36 AM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
WARN: SQL Error: 933, SQLState: 42000
Mar 06, 2020 12:17:36 AM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
ERROR: ORA-00933: SQL command not properly ended

Mar 06, 2020 12:17:36 AM org.hibernate.event.internal.DefaultLoadEventListener doOnLoad
INFO: HHH000327: Error performing load command : org.hibernate.exception.SQLGrammarException: JDBC exception executing SQL [select a1_0.id, a1_0.EMAIL_ADDRESS, a1_0.INSERT_DATE, a1_0.ACTIVE, a1_0.ADMIN, a1_0.LOGIN_ID, a1_0.NAME, a1_0.PASSWORD, a1_0.UPDATE_DATE from APP_USER as a1_0 where a1_0.id = ?]
Exception in thread "main" org.hibernate.exception.SQLGrammarException: JDBC exception executing SQL [select a1_0.id, a1_0.EMAIL_ADDRESS, a1_0.INSERT_DATE, a1_0.ACTIVE, a1_0.ADMIN, a1_0.LOGIN_ID, a1_0.NAME, a1_0.PASSWORD, a1_0.UPDATE_DATE from APP_USER as a1_0 where a1_0.id = ?]
at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:63)
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99)
at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.executeQuery(DeferredResultSetAccess.java:111)
…….
…..
..

I reran the query on SQL*plus and got the same issue. It looks like Oracle doesn't like the alias qualifier "AS" against the table name APP_USER. As you know Hibernate auto-generates this SQL query. Any help is greatly appreciated.

select a1_0.id, a1_0.EMAIL_ADDRESS, a1_0.INSERT_DATE, a1_0.ACTIVE, a1_0.ADMIN, a1_0.LOGIN_ID, a1_0.NAME, a1_0.PASSWORD, a1_0.UPDATE_DATE from APP_USER as a1_0 where a1_0.id=1
                                                                                                                                                                                                                                                          *
ERROR at line 1:
ORA-00933: SQL command not properly ended
 
Bartender
Posts: 1259
39
IBM DB2 Netbeans IDE Spring Java
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
First, welcome to the Ranch! I'm not an expert Oracle user, but googling a bit I found that APP_USER is an Oracle's reserved word.This may lead to the issue you posted.Try to use a different tablename.
 
Izanami Caster
Greenhorn
Posts: 17
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Claude!

The problem appears not to be the table itself. Removing the word AS from the auto-generated Hibernate query and running it manually on SQL*Plus does make it work.

select a1_0.id, a1_0.EMAIL_ADDRESS, a1_0.INSERT_DATE, a1_0.ACTIVE, a1_0.ADMIN, a1_0.LOGIN_ID, a1_0.NAME, a1_0.PASSWORD, a1_0.UPDATE_DATE from APP_USER as a1_0 where a1_0.id = ?

Having said that, I took your suggestion. No luck though (APP_USER was renamed to APPLICATION_USER). Hibernate's get still generates a bad query









 
Marshal
Posts: 25452
65
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Giridhar Perumal wrote: I'm using Hibernate on Oracle 19c. The hibernate config uses  <property name="hibernate.dialect">org.hibernate.dialect.Oracle12cDialect</property>



I too am not an Oracle user, but is it possible that "Oracle 12c" and "Oracle 19c" are different Hibernate dialects?
 
Izanami Caster
Greenhorn
Posts: 17
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Paul,

That shouldn't be the problem, Oracle 19c's dialect isn't different from that of Oracle 12c.

Having said that Hibernate doesn't offer a 19c. That still shouldn't be the problem though.

Thanks
 
Paul Clapham
Marshal
Posts: 25452
65
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
No, I didn't think it was likely to be the problem either. Unlikely that Oracle would change its SQL syntax in that way. Too bad.

I was going to ask whether it's possible that Hibernate isn't seeing the hibernate.dialect property, but that's a long shot too.
 
Rancher
Posts: 4549
47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What versions of everything do you have?
That is, JDBC driver, Hibernate, anything else I haven't thought of.
 
Claude Moore
Bartender
Posts: 1259
39
IBM DB2 Netbeans IDE Spring Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Izanami Caster wrote:



What if you run



from SQL interpreter ?

Try also to run the exact query Hibernate generates, setting a1:0.id manually.



 
Izanami Caster
Greenhorn
Posts: 17
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
@Dave Tolls:

1) Here's my POM :



@Claude Moore

II)   I did run the Hibernate generated query without the "AS"; worked with a hardcoded ID just fine.
 
Claude Moore
Bartender
Posts: 1259
39
IBM DB2 Netbeans IDE Spring Java
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Izanami, try to use the statement Hibernate generates as is, 'as' keyword included, and try to execute it with SQL+. That is the only way to verify that the problem doesn't like elsewhere - in a jdbc driver mismatch, as Dave suggested.A driver mismatch may cause heavy headaches, believe me.
If it doesn't work, try to use hibernate 5, a stable release, and see if the SQL statement Hibernate generates is different. You're using an Alfa release, it may have problems.
 
Izanami Caster
Greenhorn
Posts: 17
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hi @Claude,

THANK YOU! Changing the hibernate release version worked.


Summary:

1) I ran into an issue with a Hibernate-generated Oracle SQL query in which the table alias in the FROM clause was being prefixed, incorrectly, with AS.
2) This was reverified by running the SQL query on SQL+ both with (didn't work) and without (worked) the keyword AS.
3) The problem seems to be with version  6.0.0.Alpha4. Changing that dependency to <version>5.4.12.Final</version> worked.

Output:





Thanks again!
 
Claude Moore
Bartender
Posts: 1259
39
IBM DB2 Netbeans IDE Spring Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Izanami Caster wrote:hi @Claude,
THANK YOU! Changing the hibernate release version worked.



You're welcome, I'm really happy I could help you. If you want, you may file the issue you found with Hibernate 6 and Oracle 19g on their forum, it may help them to fix the problem.
 
Izanami Caster
Greenhorn
Posts: 17
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
@Claude,

Done.

Thanks,
IC
 
So it takes a day for light to pass through this glass? So this was yesterday's tiny ad?
Two software engineers solve most of the world's problems in one K&R sized book
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
    Bookmark Topic Watch Topic
  • New Topic