Prepared statement – with SET of run-time configuration parameters – not working

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"

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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?

>Solution :

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()
  }