PostgreSQL {call Update Set ...} getting "syntax error at or near SET"

Question

I'm changing queries from an Oracle Database to PostgreSQL, and in this query I am getting this error:

 ERROR: syntax error at or near "SET"

the query is:

{call UPDATE alarm_instance SET last_update_time=default, wait_expire_time=null, core_number=nextval(SEQ_ALRM_NUMBR) 
where wait_time <= current_date RETURNING alarm_instance_id bulk collect INTO ?}

I am using JDBC to connect to the database and here is the call code

try (CallableStatement cs = super.prepareCall_(query)) {
    cs.registerOutParameter(1, Types.ARRAY);
    cs.execute();
    ...

I have taken a long look at Postgres documentation and cannot find what is wrong and didn't find any answer to this specific situation

user330315 · Accepted Answer · 2018-10-12 11:23:53Z

4

An UPDATE statement can't be executed with a CallableStatement. A CallableStatement is essentially only intended to call stored procedures. In case of Oracle that includes anonymous PL/SQL blocks.

And bulk collect is invalid in Postgres to begin with.

It seems you want something like this:

String sql = 
  "UPDATE alarm_instance " + 
  "   SET last_update_time=default, " +
  "       wait_expire_time=null, "
  "       core_number=nextval('SEQ_ALRM_NUMBR') " + 
  " where wait_time <= current_date RETURNING alarm_instance_id";

Statement stmt = connection.createStatement();
stmt.execute(sql);
int rowsUpdated = stmt.getUpdateCount();
ResultSet rs = stmt.getResultSet();
while (rs.next() {
  // do something with the returned IDs
}

answered Oct 12, 2018 at 11:23

user330315

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

2 Comments

Mark Rotteveel Over a year ago

Interesting, technically the fact Oracle allows an anonymous block within the call-escape seems to violate the JDBC specification for the call-escape (which is just syntax to allow driver-independence in executing stored procedure). They could just have documented that people should use the Oracle specific syntax in this case, instead of extending the escape.

user330315 Over a year ago

@MarkRotteveel: I didn't (want to) imply you could use {call } to run a PL/SQL - only that you need a CallableStatement to run such a block e.g. prepareCall("declare ... begin .... end;") - at least if you want to get OUT parameters back

Collectives™ on Stack Overflow

PostgreSQL {call Update Set ...} getting "syntax error at or near SET"

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related