Request Roles Query not working

[Tezinder]

We are trying to make Enterprise role visible on Access Request system and the HQL query we are using is not working:

and rl.customproperty1 in (SELECT TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(u.customproperty2, ',', n.n), ',', -1)) FROM Users u join (SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7) AS n WHERE CHAR_LENGTH(u.customproperty2) - CHAR_LENGTH(REPLACE(u.customproperty2, ',', '')) >= n.n - 1 and u.id={currentUser})

SQL part of the query is working fine in data analyzer, but somehow HQL query is not working. Can anyone help correcting the query?

Here are the error details:

ERROR-Error : {}|java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: ( near line 1, column 213 [Select rl.id from com.saviynt.ssm.entity.Roles rl where 1=1 and rl.customproperty1 in (SELECT TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(u.customproperty2, ',', n.n), ',', -1)) FROM com.saviynt.ssm.entity.Users u join (SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7) AS n WHERE CHAR_LENGTH(u.customproperty2) - CHAR_LENGTH(REPLACE(u.customproperty2, ',', '')) >= n.n - 1 and u.id=128)]| at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:138) ~[hibernate-core-5.3.18.Final.jar!/:5.3.18.Final]| at 

[Darshanjain]

Hi @Tezinder 

Can you try with the below query

rl.customproperty1 IN (
SELECT
TRIM(SUBSTRING(
SUBSTRING(u.customproperty2, 1, LOCATE(',', u.customproperty2, n.n) - 1),
LENGTH(u.customproperty2) - LENGTH(REPLACE(u.customproperty2, ',', '')) + 1
))
FROM Users u
JOIN (
SELECT 1 AS n UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7
) AS n
WHERE LENGTH(u.customproperty2) - LENGTH(REPLACE(u.customproperty2, ',', '')) >= n.n - 1
AND u.id = {currentUser}
)

HQL and SQL.. some functions we need to change.

Thanks

Darshan

[Tezinder]

Hi @Darshanjain  

Thanks for your reply. Unfortunately, query still didn't worked. I am still getting the error

[Darshanjain]

Can you check the logs and paste the error as well.

Thanks

Darshan

[Tezinder]

Here are the two errors i see:

2023-09-07T20:33:24+05:30-idwms-c.s.ssm.idwms.service.UsersService-http-nio-9091-exec-11-ERROR-Error in fetchUsersList:: |org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet| at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:280) ~[spring-orm-5.1.19.RELEASE.jar!/:5.1.19.RELEASE]| at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:254) ~[spring-orm-5.1.19.RELEASE.jar!/:5.1.19.RELEASE]| at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:528) ~[spring-orm-5.1.19.RELEASE.jar!/:5.1.19.RELEASE]| at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61) ~[spring-tx-5.1.19.RELEASE.jar!/:5.1.19.RELEASE]| at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:242) ~[spring-tx-5.1.19.RELEASE.jar!/:5.1.19.RELEASE]| at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:154) ~[spring-tx-5.1.19.RELEASE.jar!/:5.1.19.RELEASE]|Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet| at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:63) ~[hibernate-core-5.3.18.Final.jar!/:5.3.18.Final]| at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42) ~[hibernate-core-5.3.18.Final.jar!/:5.3.18.Final]| at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113) ~[hibernate-core-5.3.18.Final.jar!/:5.3.18.Final]| at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99) ~[hibernate-core-5.3.18.Final.jar!/:5.3.18.Final]| at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:69) ~[hibernate-core-5.3.18.Final.jar!/:5.3.18.Final]| at org.hibernate.loader.Loader.getResultSet(Loader.java:2265) ~[hibernate-core-5.3.18.Final.jar!/:5.3.18.Final]|Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')) and (account1_.STATUS in ('Manually Provisioned' , 'Active' , '1'))' at line 1| at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) ~[mysql-connector-java-8.0.22.jar!/:8.0.22]| at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[mysql-connector-java-8.0.22.jar!/:8.0.22]| at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-java-8.0.22.jar!/:8.0.22]| at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953) ~[mysql-connector-java-8.0.22.jar!/:8.0.22]| at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:1003) ~[mysql-connector-java-8.0.22.jar!/:8.0.22]| at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52) ~[HikariCP-3.2.0.jar!/:na]|

2023-09-07T20:33:33+05:30-idwms-o.h.hql.internal.ast.ErrorTracker-http-nio-9091-exec-18-ERROR-line 1:174: expecting EOF, found ')'|antlr.MismatchedTokenException: expecting EOF, found ')'| at antlr.Parser.match(Parser.java:211) ~[antlr-2.7.7.jar!/:na]| at org.hibernate.hql.internal.antlr.HqlBaseParser.statement(HqlBaseParser.java:215) ~[hibernate-core-5.3.18.Final.jar!/:5.3.18.Final]| at org.hibernate.hql.internal.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:289) ~[hibernate-core-5.3.18.Final.jar!/:5.3.18.Final]| at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:188) ~[hibernate-core-5.3.18.Final.jar!/:5.3.18.Final]| at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:143) ~[hibernate-core-5.3.18.Final.jar!/:5.3.18.Final]| at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:119) ~[hibernate-core-5.3.18.Final.jar!/:5.3.18.Final]|

[Tezinder]

Error : {}|java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: rl near line 1, column 61 [Select rl.id from com.saviynt.ssm.entity.Roles rl where 1=1 rl.customproperty1 IN ( SELECT TRIM(SUBSTRING( SUBSTRING(u.customproperty2, 1, LOCATE(',', u.customproperty2, n.n) - 1), LENGTH(u.customproperty2) - LENGTH(REPLACE(u.customproperty2, ',', '')) + 1 )) FROM com.saviynt.ssm.entity.Users u JOIN ( SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 ) AS n WHERE LENGTH(u.customproperty2) - LENGTH(REPLACE(u.customproperty2, ',', '')) >= n.n - 1 AND u.id = 3079 )]| at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:138) ~[hibernate-core-5.3.18.Final.jar!/:5.3.18.Final]| at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:181) ~[hibernate-core-5.3.18.Final.jar!/:5.3.18.Final]| at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:188) ~[hibernate-core-5.3.18.Final.jar!/:5.3.18.Final]| at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:729) ~[hibernate-core-5.3.18.Final.jar!/:5.3.18.Final]| at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:104) ~[hibernate-core-5.3.18.Final.jar!/:5.3.18.Final]| at jdk.internal.reflect.GeneratedMethodAccessor125.invoke(Unknown Source) ~[na:na]|Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: rl near line 1, column 61 [Select rl.id from com.saviynt.ssm.entity.Roles rl where 1=1 rl.customproperty1 IN ( SELECT TRIM(SUBSTRING( SUBSTRING(u.customproperty2, 1, LOCATE(',', u.customproperty2, n.n) - 1), LENGTH(u.customproperty2) - LENGTH(REPLACE(u.customproperty2, ',', '')) + 1 )) FROM com.saviynt.ssm.entity.Users u JOIN ( SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 ) AS n WHERE LENGTH(u.customproperty2) - LENGTH(REPLACE(u.customproperty2, ',', '')) >= n.n - 1 AND u.id = 3079 )]| at org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:74) ~[hibernate-core-5.3.18.Final.jar!/:5.3.18.Final]| at org.hibernate.hql.internal.ast.ErrorTracker.throwQueryException(ErrorTracker.java:93) ~[hibernate-core-5.3.18.Final.jar!/:5.3.18.Final]| at org.hibernate.hql.internal.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:296) ~[hibernate-core-5.3.18.Final.jar!/:5.3.18.Final]| at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:188) ~[hibernate-core-5.3.18.Final.jar!/:5.3.18.Final]| at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:143) ~[hibernate-core-5.3.18.Final.jar!/:5.3.18.Final]| at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:119) ~[hibernate-core-5.3.18.Final.jar!/:5.3.18.Final]|