Scala anorm running query to postgreSQL with set role command

Question

I need to execute query in PostgreSQL using anorm. In order to handle authorization I have to set role along with query. My query is as follows. set role myuser; select country from country_list This works fine when executed directly in postgress. But when tried via anorm gives me no result found exception PSQLException: No results were returned by the query. But this is working fine when set role command is removed.

Code working fine

import anorm.SqlParser._
SQL("select country from country_list").as(str("name").*)

Code throwing exception

import anorm.SqlParser._
SQL("set role myuser; select country from country_list").as(str("name").*)

Exception

org.postgresql.util.PSQLException: No results were returned by the query.
        at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:115)
        at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
        at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
        at anorm.Sql.$anonfun$resultSet$2(Anorm.scala:78)

Answer 1

Did you try by running the 2 queries one after the other? Usually libraries like Anorm expect one query at a time.

SQL("set role myuser").execute()
SQL("select country from country_list").as(str("name").*)