I have this procedure in the database:
CREATE OR REPLACE FUNCTION replacePageRelevance(id INT, value REAL) RETURNS VOID AS $$
BEGIN
INSERT INTO pageRelevance VALUES (id,value);
EXCEPTION WHEN unique_violation THEN
UPDATE pageRelevance SET relevance = value WHERE pageId = id;
END
$$
LANGUAGE plpgsql;
And this code that calls this function:
private final String PAGE_RELEVANCE_SQL = "SELECT replacePageRelevance(?,?::REAL)";
try (CallableStatement cstm = conn.prepareCall(PAGE_RELEVANCE_SQL)) {
for (Map.Entry<Integer, Double> entry : weightMap.entrySet()) {
cstm.setInt(1, entry.getKey());
cstm.setDouble(2, entry.getValue());
cstm.addBatch();
}
cstm.executeBatch();
} catch (SQLException e) {
LOGGER.error("Error discovering pages relevance: " + e.getNextException());
}
}
When I execute the batch, the values are inserted or replaced in the table, but after that, I'm getting an exception informing that A result was returned when none was expected.
I don't know what is wrong, if the way I call the procedure or the procedure itself. What can be the problem and how to solve it?
Call a procedure with SELECT
is the right/only way?
From what I can tell you are using SELECT
when call
should be used.
An example from the PostgreSQL documentation on the JDBC interface:
// 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();
Note that the ? = call
syntax for the result is unnecessary in your case - you will want to use just call replacePageRelevance(?,?::REAL)
The reason that this syntax differs from actual PostgreSQL is because this is part of the JDBC specification.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With