Error org.hibernate.exception.SQLGrammarException: could not extract ResultSet

[Mason Wyatt]

I have the hql query that I am executing but receive an error that I don't really understand the cause of.

This is my code:

@Override
public List<StaffRequest> getStaffLeaveRequest(String userID, Date startDate, Date endDate) 
{
    Session currentSession = sessionFactory.getCurrentSession();        

    List<StaffRequest> results = 
    currentSession.createQuery("select new com.timesheet_Webservice.CustomEntity.StaffRequest(lr.leave_ID, lr.leave_Employee, concat(s.staff_First_Name, ' ', s.staff_Last_Name), "
            + "(lr.leave_Days*8.5), lr.leave_Comments, '1805', concat(pro.project_Pastel_Prefix, ' - ', pro.project_Description), lr.leave_Start, lr.leave_End, lr.leave_IsApproved, "
            + "(select lt.leaveType_Description from LeaveType lt where lt.leaveType_ID = lr.leave_Type)) "
            + "from Staff s, Project pro, Leave lr "
            + "where lr.leave_Employee = s.staff_Code and pro.project_Code = 1805 and lr.leave_Approved = :userID and lr.leave_IsApproved = 0 and s.staff_IsEmployee <> 0 "
            + "and lr.leave_Start between :startDate and :endDate "
            + "order by concat(s.staff_First_Name, ' ', s.staff_Last_Name)")
            .setParameter("userID",userID).setParameter("startDate", startDate).setParameter("endDate", endDate).getResultList();   

    return results;
}

I get his error on the webpage when trying to execute it:

org.hibernate.exception.SQLGrammarException: could not extract ResultSet

And also this console error:

ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'Leave leave2_ where leave2_.Leave_Employee=staff0_.Staff_Code and project1_.Proj' at line 1

[Campbell Ritchie]

Please tell us what the error messge and number are, and what that number means in the MariaDB handbook.

Moving to our databases forum.

[Tim Holloway]

Sorry, Campbell, error message numbers are almost universally useless. Especially Oracle ones, IBM jdbc codes, and, alas MariaDB/MySQL. Although at least that last will give a half-way useful message text.

May I suggest a bit of tidying:

const String SQL_COMMAND =
"select new com.timesheet_Webservice.CustomEntity.StaffRequest("
            + "lr.leave_ID, lr.leave_Employee, concat(s.staff_First_Name, ' ', s.staff_Last_Name), "
            + "(lr.leave_Days*8.5), "
            + "lr.leave_Comments, '1805',"
            + " concat(pro.project_Pastel_Prefix, ' - ', pro.project_Description),"
            + " lr.leave_Start, lr.leave_End, lr.leave_IsApproved, "
            + "(SELECT lt.leaveType_Description FROM LeaveType lt "
            + " WHERE lt.leaveType_ID = lr.leave_Type)) "
            + " FROM Staff s, Project pro, Leave lr "
            + "  WHERE lr.leave_Employee = s.staff_Code "
            + "  AND pro.project_Code = 1805 "
            + "  AND lr.leave_Approved = :userID"
            +  " AND lr.leave_IsApproved = 0"
            + "  AND s.staff_IsEmployee <> 0 "
            + "  AND lr.leave_Start BETWEEN :startDate AND :endDate "
            + " ORDER BY concat(s.staff_First_Name, ' ', s.staff_Last_Name)";

      List<StaffRequest> results = 
           currentSession.createQuery(SQL_COMMAND);

Mostly what I've done is make things not stretch halfway to Mars, but I also prefer to capitalize my keywords to make them more visible. And, incidentally, I would give my usual caution about using upper-case letters in table and column names, but I'll leave it to the MariaDB manual.

Having said that, it looks like there's a (yuck!) stored procedure being created/called here and a VERY complex query. Usually when a query has that many constants in it, I prefer to define a View, myself. It simplifies the actual query and you get a benefit that if your criteria change, you can re-define the View instead of changing program logic.

And what's with the "concat" in the ORDER BY. Why not simply "ORDER BY s.staff_First_Name, s.staff_Last_Name"?

The error message, incidentally, seems to be referencing a "leave2" which isn't even in this query, so I suspect we're not being told everything!

[Dave Tolls]

It's apparently HQL, which Hibernate will translate (and stick all those underscores etc in eg leave2_ as a table alias).
I would suggest getting Hibernate to print out what SQL it has generated.

I know next to nothing about MariaDB, so I have no idea whether there's some dialect mismatch somewhere?

[Tim Holloway]

Dave Tolls wrote:It's apparently HQL, which Hibernate will translate (and stick all those underscores etc in eg leave2_ as a table alias).
I would suggest getting Hibernate to print out what SQL it has generated.

I know next to nothing about MariaDB, so I have no idea whether there's some dialect mismatch somewhere?

Mostly MariaDB is SQL standard, although I don't know about that function-looking thing up front.

This segment:

where lr.leave_Employee = s.staff_Code and pro.project_Code

ALMOST matches the error message:

'Leave leave2_ where leave2_.Leave_Employee=staff0_.Staff_Code and project1_.Proj'

But notice that the original HQL is "leave_Employee and the generated SQL is "Leave_Employee", likewise "staff_Code" versus "Staff_Code".

As I've said, mixing case in SQL is extremely hazardous, even when you're doing your own SQL without a translator applying its own standards. I think that may be what's wrong here.

I prefer JPA/JPQL myself. Probably just as likely to pull a stunt like that, but legacy Hibernate is best left to legacy apps.