Search code examples
javasqlhibernatessmshql

QuerySyntaxException: unexpected token: FROM

I'm trying to write a query that will UPDATE a table generated in hibernate, with the result of another hibernate generated table.

I've written this query in ssms, in ssms it runs great.

UPDATE DMVOUCHER set VOUCHERPARTYID = EXTERNALID                 
from
(
select 
EXTERNALID
,row_number() over (partition by dv.VoucherUid order by dc.PRIMARYCOMPANY desc, dc.ExternalId) row_num
,dv.VoucherUid as vUid from DMVOUCHER dv
join DMCOMPANY dc on dv.VOUCHERPARTYID = dc.REGISTRATION
where VOUCHERTYPE = 'R03') as t1
where t1.row_num = 1
and VoucherUid = vUid
and VOUCHERTYPE = 'R03'

row_num is to make it prefer the result if primaryCompany is equal to 1 (PrimaryCompany is a boolean), if that doesn't exist then it can do it based on any externalId. in my query I just prefer the lowest externalId

however the problem then comes when I'm trying to execute this in my application. here I am trying to execute the equivelant string which can be seen below:

String queryString =
                "UPDATE DMVOUCHER \n"
                        + "SET VOUCHERPARTYID = EXTERNALID \n"
                        + "FROM ( \n"
                        + "         SELECT EXTERNALID \n"
                        + "              , row_number() over (partition by dv.VoucherUid order by dc.PRIMARYCOMPANY desc, dc.ExternalId) row_num \n"
                        + "              , dv.VoucherUid as                                                                              vUid \n"
                        + "         FROM DMVOUCHER dv \n"
                        + "                  JOIN DMCOMPANY dc ON dv.VOUCHERPARTYID = dc.REGISTRATION \n"
                        + "         WHERE VOUCHERTYPE = 'R03') as t1 \n"
                        + "WHERE t1.row_num = 1 \n"
                        + "  AND VoucherUid = vUid \n"
                        + "  AND VOUCHERTYPE = 'R03'";

however I then end up with this error message:

2021-06-21 13:23:27.173. Error: TopDmCompanyGtiNumberEntityAfterMapper failed: QuerySyntaxException: unexpected token: FROM near line 3, column 1 [UPDATE DMVOUCHER 
SET VOUCHERPARTYID = EXTERNALID 
FROM ( 
         SELECT EXTERNALID 
              , row_number() over (partition by dv.VoucherUid order by dc.PRIMARYCOMPANY desc, dc.ExternalId) row_num 
              , dv.VoucherUid as                                                                              vUid 
         FROM DMVOUCHER dv 
                  JOIN DMCOMPANY dc ON dv.VOUCHERPARTYID = dc.REGISTRATION 
         WHERE VOUCHERTYPE = 'R03') as t1 
WHERE t1.row_num = 1 
  AND VoucherUid = vUid 
  AND VOUCHERTYPE = 'R03']
org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:74)
org.hibernate.hql.internal.ast.ErrorTracker.throwQueryException(ErrorTracker.java:93)
org.hibernate.hql.internal.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:301)
org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:189)
org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:144)
org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:113)
org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:73)
org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:162)
org.hibernate.internal.AbstractSharedSessionContract.getQueryPlan(AbstractSharedSessionContract.java:604)
org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:716)
org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:113)
com.schantz.generallife.dm.logic.writer.DmUtil.executeQuery(DmUtil.java:16)
com.schantz.generallife.dm.logic.datamart.DatamartWriter.handleSynchronousRequest(DatamartWriter.java:211)
com.schantz.generallife.dm.logic.datamart.DatamartWriter.write(DatamartWriter.java:151)
com.schantz.generallife.dm.logic.datamart.DatamartWriter$1.execute(DatamartWriter.java:107)
com.schantz.foundation.config.hibernate.AbstractDatabaseTargetThreadLogic$DatabaseTargetThread.execute(AbstractDatabaseTargetThreadLogic.java:170)
com.schantz.foundation.config.hibernate.AbstractDatabaseTargetThreadLogic$DatabaseTargetThread.run(AbstractDatabaseTargetThreadLogic.java:159)
java.base/java.lang.Thread.run(Thread.java:834)

I've tried to look around on stackoverflow for similiar errors, however I havn't been able to identify any queries like mine. or where the problem is that the unexpected token is the FROM

I think a possible issue is that ssms uses a different sql language than my application does. I must admit I don't know the actual sql language either that my ssms uses or my application does. which also makes debugging the problem a bit harder. so if you are able to identify it based on what I've written, that information would also be appriciated.

edit following the method call where I execute my queryString, it reaches a new method which calls another method. doing that a few times I finally get this method:

public static Integer executeQuery(PersistenceManager persistenceManager, Class<? extends AbstractEntity> clazz, String queryString, Map<String, Object> parameters) {
        Session session = (Session) ((PersistenceManagerImpl) persistenceManager).getTransactionalEntityManager().getDelegate();
        Query<?> query = session.createQuery(queryString);

        for (Entry<String, Object> entry : parameters.entrySet()) {
            query.setParameter(entry.getKey(), entry.getValue());
        }

        return query.executeUpdate();
    }

where executeUpdate() is from jakarta.persistence-api-2.2.3.jar

--edit-- I messed around a bit and tried to use the way it would written in mysql instead. therefore I now get this error message instead.

2021-06-22 11:44:10.496. Error: TopDmCompanyGtiNumberEntityAfterMapper failed: QuerySyntaxException: expecting IDENT, found '(' near line 2, column 1 [UPDATE 
(SELECT EXTERNALID 
              , row_number() over (partition by dv.VoucherUid order by dc.PRIMARYCOMPANY desc, dc.ExternalId) row_num 
              , dv.VoucherUid as                                                                              vUid 
         FROM DMVOUCHER dv 
                  JOIN DMCOMPANY dc ON dv.VOUCHERPARTYID = dc.REGISTRATION 
         WHERE VOUCHERTYPE = 'R03') as t1 SET VOUCHERPARTYID = EXTERNALID
WHERE t1.row_num = 1 
  AND VoucherUid = vUid 
  AND VOUCHERTYPE = 'R03']
org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:74)
org.hibernate.hql.internal.ast.ErrorTracker.throwQueryException(ErrorTracker.java:93)
org.hibernate.hql.internal.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:301)
org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:189)
org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:144)
org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:113)
org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:73)
org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:162)
org.hibernate.internal.AbstractSharedSessionContract.getQueryPlan(AbstractSharedSessionContract.java:604)
org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:716)
org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:113)
com.schantz.generallife.dm.logic.writer.DmUtil.executeQuery(DmUtil.java:16)
com.schantz.generallife.dm.logic.datamart.DatamartWriter.handleSynchronousRequest(DatamartWriter.java:211)
com.schantz.generallife.dm.logic.datamart.DatamartWriter.write(DatamartWriter.java:151)
com.schantz.generallife.dm.logic.datamart.DatamartWriter$1.execute(DatamartWriter.java:107)
com.schantz.foundation.config.hibernate.AbstractDatabaseTargetThreadLogic$DatabaseTargetThread.execute(AbstractDatabaseTargetThreadLogic.java:170)
com.schantz.foundation.config.hibernate.AbstractDatabaseTargetThreadLogic$DatabaseTargetThread.run(AbstractDatabaseTargetThreadLogic.java:159)
java.base/java.lang.Thread.run(Thread.java:834)

I believe it makes the problem a bit clearer, I need to covert my query query so its in hql instead. I will answer the post if I figure out how to do that, as I'm not so familiar with hql

Solution

  • the problem was indeed that the queryString was supposed to be in hql. I've rewritten the queryString so it now works, it now looks like this:

            String queryString =
                "UPDATE DmVoucher SET voucherPartyId = (SELECT externalId \n"
                        + "         FROM DmCompany dc  \n"
                        + "         WHERE voucherType = 'R03' \n"
                        + "And dc.primaryCompany = CASE WHEN (SELECT count(dcp) FROM DmCompany dcp WHERE primaryCompany = 1 and voucherPartyId = dcp.registration) > 0 THEN 1 else 0 END\n"
                        + "and voucherPartyId = dc.registration)\n";