Prepared statement - with SET of run-time configuration parameters

Using PG14, Hikari pool, Kotlin 1.6

I get the following error when calling, for example, a SET query:

org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1"

val input = "yes"
connection.prepareStatement("SET log_connections TO ?")
  .apply {
    setString(1, input)
  }.use {
    it.execute()
  }

Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1"
  Position: 22
    at app//org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2675)
    at app//org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2365)
    at app//org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:355)
    at app//org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:490)
    at app//org.postgresql.jdbc.PgStatement.execute(PgStatement.java:408)
    at app//org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:167)
    at app//org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:156)
    at app//com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
    at app//com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)

I'm trying to SET run-time configuration parameters of PostgreSQL from code by creating prepared statement and set its parameters safely, but I'm getting error while running such statement. Are there any limitations for creating prepared statements that might be limiting creation of such SET statement?

CodePudding user response：

You can't pass a dynamic parameter using the SET command. You need to use the set_config() function:

val input = "yes"
connection.prepareStatement("select set_config('log_connections', ?, false)")
  .apply {
    setString(1, input)
  }.use {
    it.execute()
  }