CODEWITHSUNDEEP
javasqlhibernatehql
Wires77
Wires77

Reputation: 351

SQL Error: 933, SQLState: 42000 and ORA-00933: SQL command not properly ended

I'm using Hibernate for database access. I'm using the following query in my code to fetch the data I need:

SELECT proasset
FROM com.company.claims.participant.AbstractBeneficiary bene
JOIN bene.approvals approval
JOIN bene.proassetkey proasset
join proasset.relatedparties proassetparties
WHERE approval.user_dt > :currentDate
AND approval.user_type = :userType

I'm using it as query in the following:

Query q = this.getSessionFactory().getCurrentSession().createSQLQuery(query.toString())
q.setDate("currentDate", new Date());
q.setString("userType", APPROVER_USER_TYPE);
List<ProAsset> proassets = q.list();

However, I encounter the following when trying to run it:

SQL Error: 933, SQLState: 42000
ORA-00933: SQL command not properly ended

If it matters, the query is being constructed using a StringBuilder and it uses \n to break the lines

Any thoughts on the problem?

Upvotes: 0

Views: 29922

Answers (3)

Wires77
Wires77

Reputation: 351

Alright, so I used createSQLQuery() instead of createQuery() and I was using the column names instead of the variable names.

Here's what my code looks like now:

StringBuilder query = new StringBuilder();

query.append("SELECT proasset\n" +
            "FROM com.avivausa.claims.participant.AbstractBeneficiary bene\n" +
            "JOIN bene.approvals approval\n" +
            "JOIN bene.proAsset proasset\n" +
            "join proasset.additionalParties proassetparties\n" +
            "WHERE approval.userDate < current_date()\n");
query.append("AND approval.userType = ").append(UserAuditType.APPROVER);

Query q = this.getSessionFactory().getCurrentSession().createQuery(query.toString());

List<ProAsset> proassets = q.list();

Obviously I made some refactoring changes too, but the main changes were what I stated above. Thank you for your responses though!

Upvotes: 0

Sai Ye Yan Naing Aye
Sai Ye Yan Naing Aye

Reputation: 6740

Correct the syntax by removing the inappropriate clauses. It may be possible to duplicate the removed clause with another SQL statement. For example, to order the rows of a view, do so when querying the view and not when creating it. This error can also occur in SQL*Forms applications if a continuation line is indented. Check for indented lines and delete these spaces.

Upvotes: 0

Bill Rosmus
Bill Rosmus

Reputation: 3011

It looks like you are trying to mix ORM with a native (plain old SQL) query.

createSQLQuery requires native SQL. You are using classes instead of table names. Try a read of this:

http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/querysql.html

In short you need to write a query like:

select fu
from bar
where situation = 'snafu'

Perhaps you are really wanting to write a namedQuery? They use ORM syntax where you join entities as it seems you are doing in your example.

Check these examples out:

http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/querysql.html#querysql-namedqueries

Upvotes: 4

Related Questions