CODEWITHSUNDEEP
postgresqljdbc
jmizv
jmizv

Reputation: 1312

Cannot use parametrization on "set variable = ?" statement but hardcoding works

I want to change the parameter statement_timeout after establishing a JDBC connection. Therefore I have the following code:

PreparedStatement statement = _connection.prepareStatement("set statement_timeout = ?");
statement.setInt(1, (int) _statementTimeout);
statement.execute();

But on execute() this results in a SQL exception clearly stating that I have an error in the syntax at my question mark.

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

I tried searching for this problem but couldn't find any right answers. The pages thinks I refer to the SET of an UPDATE-clause.

My question is simple, why is parametrization not working? If I hardcode the value, e.g.

prepareStatement(String.format("set statement_timeout = %s", _statementTimeout))

everything works fine and as expected. Or is it simply wrong to use parametrization on setting variables?

Upvotes: 0

Views: 301

Answers (1)

user330315
user330315

Reputation:

Using the equivalent function call should work:

prepareStatement("select set_config('statement_timeout', ?, false)");
statement.setString(1, Integer.toString(_statementTimeout));
statement.execute();

Note that you need to pass the value as a String as the function's signature is (text, text, boolean)

Upvotes: 2

Related Questions