MSSQL getUniqueResult Incorrect syntax near 'P0'

jesse_sweetland · **Posted:** Wed Mar 03, 2010 4:15 pm

Using JBoss EAP AS 5

Named query:

Code:

select ge
from GlobalEnumeration ge
where ge.uuid=:uuid

Code to execute the query:

Code:

Query q = em.createNamedQuery("getGlobalEnumerationByUuid");
q.setParameter("uuid", uuid);
return q.getUniqueResult();

Generated SQL:

Code:

select top ? globalenum0_.id as id672_, globalenum0_.createdDate as createdD3_672_, globalenum0_.deletedDate as deletedD4_672_, globalenum0_.uuid as uuid672_, globalenum0_.version as version672_, globalenum0_.code as code672_, globalenum0_.longDescription as longDesc8_672_, globalenum0_.shortDescription as shortDes9_672_, globalenum0_.DTYPE as DTYPE672_ from GlobalEnumeration globalenum0_ where globalenum0_.uuid=?

In MSSQL this gets translated into something like this:

Code:

declare @p1 int
set @p1=NULL
exec sp_prepexec @p1 output,N'@P0 int, @P1 nvarchar(4000)',N'select top @P0 globalenum0_.id as id14_, globalenum0_.createdDate as createdD3_14_, globalenum0_.deletedDate as deletedD4_14_, globalenum0_.uuid as uuid14_, 
globalenum0_.version as version14_, globalenum0_.code as code14_, globalenum0_.longDescription as longDesc8_14_, globalenum0_.shortDescription as shortDes9_14_, globalenum0_.DTYPE as DTYPE14_ from GlobalEnumeration 
globalenum0_ where globalenum0_.uuid=@P1                ',2, N'804A460E-07B0-4F4E-ABFB-CD502B7A86CC'
select @p1

Which results in the following error:

Code:

13:42:09,094 WARN  [JDBCExceptionReporter] SQL Error: 102, SQLState: S0001
13:42:09,094 ERROR [JDBCExceptionReporter] Incorrect syntax near '@P0'.

When I enter the exact MSSQL sp_prepexec call above into Enterprise Manager, I get the same error. However, if I put parenthesis around the "@P0" in "select top P0" so that it looks like "select top (P0)", then it works:

Code:

declare @p1 int
set @p1=NULL
exec sp_prepexec @p1 output,N'@P0 int, @P1 nvarchar(4000)',N'select top (@P0) globalenum0_.id as id14_, globalenum0_.createdDate as createdD3_14_, globalenum0_.deletedDate as deletedD4_14_, globalenum0_.uuid as uuid14_, 
globalenum0_.version as version14_, globalenum0_.code as code14_, globalenum0_.longDescription as longDesc8_14_, globalenum0_.shortDescription as shortDes9_14_, globalenum0_.DTYPE as DTYPE14_ from GlobalEnumeration 
globalenum0_ where globalenum0_.uuid=@P1                ',2, N'804A460E-07B0-4F4E-ABFB-CD502B7A86CC'
select @p1

Considering the "top ?" is inserted into the query by the entity manager, I can't really control this except to avoid using getUniqueResult().

Is this a known issue? If so, is it a bug in the Hibernat MSSQL dialect, or is it a JDBC driver issue?

mmerder · **Posted:** Wed Mar 03, 2010 5:02 pm

Did you set the right Dialect? As "top" is not used in MySQL but in MSSQL.

mmerder · **Posted:** Wed Mar 03, 2010 5:04 pm

Sorry, just read that you are using MSSQL. So what is the problem with top? Why do you want to get rid of it when using getSingleResult()?

jesse_sweetland · **Posted:** Wed Mar 03, 2010 5:31 pm

I don't want to get rid of it ... I need one of two things to happen:

1. Get Hibernate to somehow generate the prepared statement SQL as "select top (?) ..." (with parenthesis); or
2. Get the MSSQL JDBC driver to somehow interpret "select top ?" as "select top (@P0) ..." (with parenthesis)

The current prepared statement SQL is "select top ? ..." which is getting translated into "select top @P0 ..." (without parenthesis) which is causing a syntax error in MSSQL Server.

In the short term I'm using a work-around:

Code:

public Object getUniqueResult(Query q) throws NoResultException, NonUniqueResultException {
    List<?> results = q.getResultList();
    if(results.isEmpty()) {
         throw new NoResultException();
    } else if(results.size() > 1) {
         throw new NonUniqueResultException();
    } else {
         return results.get(0);
    }
}

Not ideal but it gets me by