CODEWITHSUNDEEP
javapostgresqlstored-procedures
georgeguitar
georgeguitar

Reputation: 105

A CallableStatement was executed with nothing returned

I have a Java exception when calling a function that returns nothing:

org.postgresql.util.PSQLException: A CallableStatement was executed with nothing returned.

The Java code is similar to this:

// Procedure call.
CallableStatement proc = con.prepareCall("{ ? = call doquery ( ? ) }");
proc.registerOutParameter(1, Types.Other);
proc.setInt(2, 33434);
proc.execute();
ResultSet results = (ResultSet) proc.getObject(1);
while (results.next()) {
  // do something with the results...
}
results.close();
proc.close();

The query is very simple:

select * from table where idTable = 33434;

The query does not return any value because what I'm looking for in postgresql DB does not exist. A sql query is like that, not always we get something in return.

How do you deal with this situations?

PS.- The Postgresql function:

CREATE OR REPLACE FUNCTION doquery(_idTable bigint)
  RETURNS TABLE(idTable bigint, name varchar) AS
$BODY$
DECLARE
    searchsql text := '';
BEGIN
    searchsql := 'SELECT * FROM table
            WHERE idTable = ' || _idTable;

    RETURN QUERY EXECUTE searchsql;
END
$BODY$
  LANGUAGE plpgsql;

Upvotes: 4

Views: 2751

Answers (1)

user330315
user330315

Reputation:

Don't use a CallableStatement. They are intended for stored procedures not functions.

As your function returns a resultset, you need to use a select statement:

PreparedStatement pstmt = con.prepareStatement("select * from doquery(?)");
pstmt.setInt(1, 33434);

ResultSet results = pstmt.executeQuery();
while (results.next()) {
  // do something with the results...
}
results.close();
proc.close();

Note that the use of dynamic SQL or even PL/pgSQL is not needed. You should also not append parameters to queries (the same way you shouldn't do it in Java as well). Use parameter placeholders:

CREATE OR REPLACE FUNCTION doquery(_idTable bigint)
  RETURNS TABLE(idTable bigint, name varchar) AS
$BODY$
BEGIN
    RETURN QUERY 
       SELECT * 
       FROM table
       WHERE idTable = _idTable;
END
$BODY$
  LANGUAGE plpgsql;

Or even simpler as a pure SQL function:

CREATE OR REPLACE FUNCTION doquery(_idTable bigint)
  RETURNS TABLE(idTable bigint, name varchar) AS
$BODY$
   SELECT idtable, name 
   FROM table
   WHERE idTable = _idTable;
$BODY$
  LANGUAGE sql;

If you do need dynamic SQL then use placeholders inside the string and pass the parameters to the execute function. Do not concatenate values:

CREATE OR REPLACE FUNCTION doquery(_idTable bigint)
  RETURNS TABLE(idTable bigint, name varchar) AS
$BODY$
BEGIN
    RETURN QUERY EXECUTE '
       SELECT * 
       FROM table
       WHERE idTable = $1'
    USING _idTable;
END
$BODY$
  LANGUAGE plpgsql;

Upvotes: 2

Related Questions