Calling a Postgres stored function SQL error

Question

Im calling a stored function like the following:

PreparedStatement deleteAll=connection.prepareStatement("{ call delete_all_data() }");
deleteAll.execute();

And in the logs i see:

15:16:31,950  WARN SqlExceptionHelper:143 - SQL Error: 0, SQLState: 42601
15:16:31,950 ERROR SqlExceptionHelper:144 - ERROR: syntax error at or near "{"
  Position: 1

what is wrong with the prepareStatement??

`connection.prepareStatement("select delete_all_data()")` is enough. No `CallableStatement` needed — , Nov 24 '14 at 15:58
@LuiggiMendoza A JDBC driver should also support the call escape with `Statement` and `PreparedStatement`. This should work as long as the stored procedure doesn't have `OUT` parameters (`PreparedStatement`) or parameter placeholders (for `Statement`). However in practice most drivers only support it with `CallableStatement`. — Mark Rotteveel, Nov 24 '14 at 16:26

score 2 · Accepted Answer · answered Nov 24 '14 at 15:25

2

Change connection.prepareStatement (which expects SQL) to connection.prepareCall. That may very well be the only change you need, as a CallableStatement is a PreparedStatement.

answered Nov 24 '14 at 15:25

Jon Skeet

1,421,763
867
9,128
9,194

1

See my comment on the question itself, although this is probably the solution, it is not according to specification: the call escape should also be supported on `Statement` and `PreparedStatement`. See: section 6.4 of JDBC 4.2 and http://mail.openjdk.java.net/pipermail/jdbc-spec-discuss/2013-January/000030.html – Mark Rotteveel Nov 24 '14 at 16:30

score 2 · Answer 2 · edited Nov 24 '14 at 15:27

2

I use prepareCall to call stored procedures.

String SQL = "{call delete_all_data()}";
cstmt = conn.prepareCall (SQL);

edited Nov 24 '14 at 15:27

Luiggi Mendoza

85,076
16
154
332

answered Nov 24 '14 at 15:26

prsutar

429
2
4
17

Calling a Postgres stored function SQL error

2 Answers2