CODEWITHSUNDEEP
javapostgresqljdbc
Edward
Edward

Reputation: 259

Any way to invoke PostgreSql procedure without return value using java?

Recently, I have created a postgresql's function likes below:

CREATE OR REPLACE FUNCTION POSTPRC(A IN NUMERIC)
**RETURNS VOID**
AS
$$
BEGIN
... ...
END;
$$ LANGUAGE PLPGSQL;

How can I invoke it using java, I have used the code likes below:

CallableStatement upperProc = con.prepareCall("{ ? = call POSTPRC( ? ) }");
upperProc.registerOutParameter(1, Types.NULL);
upperProc.setInt(2,23);
int flag=upperProc.executeUpdate();

but errors will occur every time, how to do can resolved this problem?

The Exception show as:java.lang.ArrayIndexOutOfBoundsException: 2.

The Procedure POSTPRC with correspond parameters can successfully execute at pgAdminIII tool.


The postgresql should use the statement likes-{? = call procedure(?,?,?)}, and this is the only way to invoke through java. I have tested the statement likes {call procedure(?,?,?)} etc. Failed every time. Actually, postgresql's procedure is function, if we want to invoke the procedure of postgresql, we'd better to define the postgresql's procedure with out parameters or return values....


Any comments are welcome!

Thanks

Upvotes: 1

Views: 1552

Answers (1)

agim
agim

Reputation: 1841

I'm not quite sure, why do you need the first ? if your function does not return a value. You can as well write it 

CallableStatement upperProc = con.prepareCall("{call POSTPRC( ? ) }");

Have a look here

By the way, if your function does not return a value, then you don't need to do it with CallableStatements - you can/should just as well use a PreparedStatement:

PreparedStatement pstmt = con.prepareStatement("SELECT * FROM POSTPRC( ? )")
pstmt.setInt(1,23);
pstmt.execute(); // omit the result

Upvotes: 2

Related Questions