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

Question

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?

Please don't post screenshots of exception stacktraces, post them as code formatted text. — Mark Rotteveel
– Mark Rotteveel, Commented Nov 24, 2022 at 15:16

user330315 · Accepted Answer · 2022-11-24 15:14:23Z

3

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

answered Nov 24, 2022 at 15:14

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related