Re: Moving from PHP to Java: A result was returned when none was expected.

On Wed, 2016-06-15 at 15:56 +0200, Alexander Farber wrote:
> Good afternoon,
>
> at PostgreSQL 9.5.3 I have a stored function (full source code below)
> returning void, which I successfully call with PHP:
>
> function skipGame($dbh, $uid, $gid) {
>         $sth = $dbh->prepare('SELECT words_skip_game(?, ?)');
>         $sth->execute(array($uid, $gid));
> }
>
> Now I am trying to call the same function through JDBC driver
> 9.4.1208.jre7:
>
>     private static final String SQL_SKIP_GAME   = 
>             "SELECT words_skip_game(?, ?)";
>
>         try (PreparedStatement st =
> mDatabase.prepareStatement(SQL_SKIP_GAME)) {
>             st.setInt(1, mUid);
>             st.setInt(2, gid);
>             st.executeUpdate();
>         }
>
> and sadly get the SQLException "A result was returned when none was
> expected.".
>
> Shouldn't I call executeUpdate() method here - according to the doc
> https://www.postgresql.org/docs/7.4/static/jdbc-callproc.html  ?
>
> Below is the stored procedure, thank you for any hints.
> Alex
>
> CREATE OR REPLACE FUNCTION words_skip_game(
>         IN in_uid integer,
>         IN in_gid integer)
>         RETURNS void AS
> $func$
> BEGIN
>         UPDATE words_games
>         SET played1 = CURRENT_TIMESTAMP
>         WHERE gid = in_gid 
>         AND player1 = in_uid
>         /* and it is first player's turn */
>         AND (played1 IS NULL OR played1 < played2);
>
>         IF NOT FOUND THEN
>                 UPDATE words_games
>                 SET played2 = CURRENT_TIMESTAMP
>                 WHERE gid = in_gid 
>                 AND player2 = in_uid
>                 /* and it is second player's turn */
>                 AND (played2 IS NULL OR played2 < played1);
>         END IF; 
> END
> $func$ LANGUAGE plpgsql;
>

Aren't you supposed to use prepareCall?

Also 7.4 documentation is a tad out of date but the method is the same.

HTH,
Rob