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 — user330315
– user330315, Commented Nov 24, 2014 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
– Mark Rotteveel, Commented Nov 24, 2014 at 16:26

Jon Skeet · Accepted Answer · 2014-11-24 15:25:28Z

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, 2014 at 15:25

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

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

1 Comment

Mark Rotteveel Over a year ago

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 mail.openjdk.java.net/pipermail/jdbc-spec-discuss/2013-January/…

Luiggi Mendoza · Accepted Answer · 2014-11-24 15:27:26Z

2

I use prepareCall to call stored procedures.

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

edited Nov 24, 2014 at 15:27

Luiggi Mendoza

85.9k16 gold badges159 silver badges354 bronze badges

answered Nov 24, 2014 at 15:26

prsutar

4292 gold badges4 silver badges17 bronze badges

Collectives™ on Stack Overflow

Calling a Postgres stored function SQL error

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related