CODEWITHSUNDEEP
javapostgresqlhibernatejdbc
Kaushik Patel
Kaushik Patel

Reputation: 239

Syntax error at or near "$1" jdbc postgres -> SET var=?

Trying to execute the following code :

this.getSessionFactory().getCurrentSession()
        .createNativeQuery("SET app.variable = :variable")
        .setParameter("variable","variableValue")
        .executeUpdate();

It is giving me the following error :

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

I'm not sure if this is a bug in JDBC or PostgreSQL.

It seems not a PostgreSQL issue as when I use the DBeaver using bind variables the query works fine.

Tried the same query by creating a prepared statement as well and the Result was same.

Upvotes: 0

Views: 277

Answers (2)

Andreas Degele
Andreas Degele

Reputation: 1

I tried the same thing, here is my solution. Instead of SET I use set_config, which is equivalent according to the Postgres-Docs, Section "Notes".

entityManager.createNativeQuery("select set_config('app.variable', :var, false);")
    .setParameter("var", variableValue)
    .getSingleResult();

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 246848

The SQL statement SET accepts no parameters. Only SELECT, VALUES, INSERT, UPDATE, DELETE and MERGE do.

Upvotes: 3

Related Questions