预处理语句-具有运行时配置参数集-不工作

Question

使用PG14, Hikari pool, Kotlin 1.6

在调用SET查询时，我得到以下错误:

org.postgresql.util。PSQLException: ERROR:在"$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)

我试图通过创建准备好的语句并安全地设置其参数，从代码中设置PostgreSQL的运行时配置参数，但我在运行这样的语句时出错。对于创建准备好的语句有什么限制吗?这些限制可能会限制SET语句的创建?

Answer 1

不能使用SET命令传递动态参数。您需要使用set_config()函数:

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