PostgreSQL: Call Function WIth JDBC

Question

As seen here: http://www.postgresql.org/docs/7.4/static/jdbc-callproc.html

// Turn transactions off.
con.setAutoCommit(false);
// Procedure call.
CallableStatement upperProc = con.prepareCall("{ ? = call upper( ? ) }");
upperProc.registerOutParameter(1, Types.VARCHAR);
upperProc.setString(2, "lowercase to uppercase");
upperProc.execute();
String upperCased = upperProc.getString(1);
upperProc.close();

Can I instead do:

// Turn transactions off.
con.setAutoCommit(false);
// Procedure call.
CallableStatement upperProc = con.prepareCall("{call upper( ? ) }");
upperProc.setString(2, "lowercase to uppercase");
upperProc.execute();
upperProc.close();

I just remove the ? before call upper. My query just inserts so I dont need a value. I want to keep it in this forma becuase mysql also uses this format thus I can reuse a lot of code in JAVA.

In general, you can call functions with SELECT without the need for CallableStatement (that's how they would be called in psql). — Bruno
– Bruno, Commented Jul 14, 2014 at 16:35
Just use select upper(?). No need for CallableStatement in Postgres — user330315
– user330315, Commented Jul 14, 2014 at 16:51
I would like to use call because I also have the sampe procedure name in MySQL. Thus I can reuse code. Postgresql uses the first code snipet as an example. — wwjdm
– wwjdm, Commented Jul 14, 2014 at 19:05

Craig Ringer · Accepted Answer · 2014-07-15 01:34:07Z

2

The JavaDoc for CallableStatement shows a syntax without a return value:

   {call <procedure-name>[(<arg1>,<arg2>, ...)]}

so if PgJDBC doesn't accept it that's a bug.

You can just leave out the ? =.

answered Jul 15, 2014 at 1:34

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

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

2 Comments

Mark Rotteveel Over a year ago

Not necessarily, if there is a return value, than AFAIK it is an error not to declare a parameter to retrieve it.

wwjdm Over a year ago

I would have a r return value but I return null. Basically I just want to do INSERT statements.

Collectives™ on Stack Overflow

PostgreSQL: Call Function WIth JDBC

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