Passing Current user Hash Variable in JDBC SQL Custom Query

 
1
0
-1

  • SQL Query works fine on SQL Developer

    Select c_routeStatus, c_systemRouteId, c_Route_ID, c_businessUnit, c_qa_date, c_prod_date, c_currentUser, datemodified

    from <Table_Name> where c_status like 'Draft%' 

    and c_businessUnit in (Select name from dir_group where id=ANY(Select groupId from dir_user_group where userId='andy_sa'))



  • SQL Query doesn't work on JDBC customer SQL DataList

    Select c_routeStatus, c_systemRouteId, c_Route_ID, c_businessUnit, c_qa_date, c_prod_date, c_currentUser, datemodified
    from <Table_Name>
    where c_status like 'Draft%'
    and c_businessUnit in (Select name from dir_group where id=ANY(Select groupId from dir_user_group where userId='#currentUser.id#'))

 

Can we pass Current User Hash Variable in JDBC Custom Queries like the Userview Key variables?

  1. Julian

    It should be possible to use the Hash Variables in the query, so it might be something else. What's the error that's showing up in the logs?

  2. Rahul Katara

    Am I not using the correct syntax to pass the hash variable? Which log will show the error while executing this query?

  3. Hugo

    Try to look for joget.log under Tomcat's logs folder.

  4. Rahul Katara

    Hi Hugo, 

    This is the Error that I get in the Logs when I execute the above query from JDBC DataList Builder:

    ERROR 15 Jun 2015 11:17:15 org.joget.plugin.enterprise.JdbcDataListBinder - java.sql.SQLSyntaxErrorException: ORA-00904: "TEMP"."ID": invalid identifier

    java.sql.SQLSyntaxErrorException: ORA-00904: "TEMP"."ID": invalid identifier

    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:225)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:53)
    at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:774)
    at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:925)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1111)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:4798)
    at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:4845)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1501)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96).........................................................

     

     

    However, a hardcoded value of the UserID like 'andy_sa' doesn't work in the JDBC DataList Builder:

    Select c_routeStatus, c_systemRouteId, c_Route_ID, c_businessUnit, c_qa_date, c_prod_date, c_currentUser, datemodified

    from <Table_Name> 

    where c_status like 'Draft%' and c_businessUnit in (Select name from dir_group where id=ANY(Select groupId from dir_user_group where userId='andy_sa'))

     

    This above query works fine in SQL Developer but not in JDBC DataList Builder.

CommentAdd your comment...

1 answer

  1.  
    1
    0
    -1

    Try this.

    Select id, c_routeStatus, c_systemRouteId, c_Route_ID, c_businessUnit, c_qa_date, c_prod_date, c_currentUser, datemodified 
from <Table_Name> 
where c_status like 'Draft%' 
and c_businessUnit in (Select name from dir_group where id=ANY(Select groupId from dir_user_group where userId='#currentUser.id#'))
    1. Rahul Katara

      Thanks Walter, This worked perfectly fine.

    CommentAdd your comment...