Win a copy of Building Blockchain Apps this week in the Cloud/Virtualization 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
  • Paul Clapham
  • Liutauras Vilda
  • Knute Snortum
  • Bear Bibeault
Sheriffs:
  • Devaka Cooray
  • Jeanne Boyarsky
  • Junilu Lacar
Saloon Keepers:
  • Ron McLeod
  • Stephan van Hulst
  • Tim Moores
  • Carey Brown
  • salvin francis
Bartenders:
  • Tim Holloway
  • Piet Souris
  • Frits Walraven

Hibernate sql command not properly ended - Bad SQL Grammar

 
Izanami Caster
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
 
Claude Moore
Bartender
Posts: 1251
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









 
Paul Clapham
Marshal
Posts: 25215
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: 25215
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.
 
Dave Tolls
Rancher
Posts: 4493
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: 1251
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: 1251
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: 1251
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
 
I found some pretty shells, some sea glass and this lovely tiny ad:
Java file APIs (DOC, XLS, PDF, and many more)
https://products.aspose.com/total/java
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!