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

Actually I meant the doc
https://jdbc.postgresql.org/documentation/94/update.html

On Wednesday, June 15, 2016 3:56:07 PM EDT Alexander Farber wrote:

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 ?

You are looking at the 7.4 documentation. That page is so old it can apply for
a driver's license in some jurisdictions.

Looking at the 9.4 documentation, I see something completely different:

https://jdbc.postgresql.org/documentation/94/callproc.html

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hello Jan,

On Wed, Jun 15, 2016 at 4:17 PM, Jan de Visser <jan@de-visser.net> wrote:

On Wednesday, June 15, 2016 3:56:07 PM EDT Alexander Farber wrote:

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.".

Looking at the 9.4 documentation, I see something completely different:

https://jdbc.postgresql.org/documentation/94/callproc.html

your doc talks about calling stored functions which return SETOF or cursor.

But my function is returning VOID, so according to
https://jdbc.postgresql.org/documentation/94/update.html
I was thinking I should call executeUpdate()?

Regards
Alex

On Wednesday, June 15, 2016 4:21:47 PM EDT Alexander Farber wrote:

Hello Jan,

On Wed, Jun 15, 2016 at 4:17 PM, Jan de Visser <jan@de-visser.net> wrote:

On Wednesday, June 15, 2016 3:56:07 PM EDT Alexander Farber wrote:

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.".

Looking at the 9.4 documentation, I see something completely different:

https://jdbc.postgresql.org/documentation/94/callproc.html

your doc talks about calling stored functions which return SETOF or cursor.

Example 6.1 doesn't.

What I was trying to indicate that the page you referred to has undergone, um,
significant changes over the years.

But my function is returning VOID, so according to
https://jdbc.postgresql.org/documentation/94/update.html
I was thinking I should call executeUpdate()?

Regards
Alex

Point is that you're doing a SELECT. A SELECT returns a result, which can be
empty. I would use executeQuery and ignore the result.

There is a bit of a mismatch between the JDBC stored procedure model and the
pgsql function model, because pgsql doesn't have true stored procedures.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Wed, Jun 15, 2016 at 10:30 AM, Jan de Visser <jan@de-visser.net> wrote:

Point is that you're doing a SELECT. A SELECT returns a result, which can
be
empty. I would use executeQuery and ignore the result.

There is a bit of a mismatch between the JDBC stored procedure model and
the
pgsql function model, because pgsql doesn't have true stored procedures.

Can you point to docs, JDBC and/or PG, that describe what it means to
"RETURN void"?

At a high-level SQL returns SETs and the empty set is a valid SET. I take
it from your comment that JDBC considers the empty set "a result", whose
record count is zero.

​David J.​

On Wednesday, June 15, 2016 10:43:13 AM EDT David G. Johnston wrote:

On Wed, Jun 15, 2016 at 10:30 AM, Jan de Visser <jan@de-visser.net> wrote:

Point is that you're doing a SELECT. A SELECT returns a result, which can
be
empty. I would use executeQuery and ignore the result.

There is a bit of a mismatch between the JDBC stored procedure model and
the
pgsql function model, because pgsql doesn't have true stored procedures.

Can you point to docs, JDBC and/or PG, that describe what it means to
"RETURN void"?

At a high-level SQL returns SETs and the empty set is a valid SET. I take
it from your comment that JDBC considers the empty set "a result", whose
record count is zero.

That's what I assume.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Wednesday, June 15, 2016 10:43:13 AM EDT David G. Johnston wrote:

On Wed, Jun 15, 2016 at 10:30 AM, Jan de Visser <jan@de-visser.net> wrote:

Point is that you're doing a SELECT. A SELECT returns a result, which can
be
empty. I would use executeQuery and ignore the result.

There is a bit of a mismatch between the JDBC stored procedure model and
the
pgsql function model, because pgsql doesn't have true stored procedures.

Can you point to docs, JDBC and/or PG, that describe what it means to
"RETURN void"?

At a high-level SQL returns SETs and the empty set is a valid SET. I take
it from your comment that JDBC considers the empty set "a result", whose
record count is zero.

Hrm...

jan=# create or replace function foo() returns void as $$
begin
raise notice 'foo() called';
end
$$ language plpgsql;
CREATE FUNCTION
jan=# select foo();
NOTICE: foo() called
foo
-----

(1 row)

So there's a row. Don't know what that row would contain, and how it would map
to JDBC.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Wed, Jun 15, 2016 at 11:24 AM, Jan de Visser <jan@de-visser.net> wrote:

On Wednesday, June 15, 2016 10:43:13 AM EDT David G. Johnston wrote:

On Wed, Jun 15, 2016 at 10:30 AM, Jan de Visser <jan@de-visser.net>

wrote:

Point is that you're doing a SELECT. A SELECT returns a result, which

can

be
empty. I would use executeQuery and ignore the result.

There is a bit of a mismatch between the JDBC stored procedure model

and

the
pgsql function model, because pgsql doesn't have true stored

procedures.

Can you point to docs, JDBC and/or PG, that describe what it means to
"RETURN void"?

At a high-level SQL returns SETs and the empty set is a valid SET. I

take

it from your comment that JDBC considers the empty set "a result", whose
record count is zero.

Hrm...

jan=# create or replace function foo() returns void as $$
begin
raise notice 'foo() called';
end
$$ language plpgsql;
CREATE FUNCTION
jan=# select foo();
NOTICE: foo() called
foo
-----

(1 row)

So there's a row. Don't know what that row would contain, and how it would
map
to JDBC.

​select r is null from foo() f (r); => false ...
select pg_typeof(r) from foo() f (r); => void ...

Because "void" is a Java keyword this is not that easy to Google...​

Is "void" in the SQL standard​?

I'd say one should simply avoid "void" and chose some meaningful value to
return from most/all PostgreSQL functions. As you say they are not proper
stored procedures in the first place so trying to pretend they are is just
going to lead to frustration. Work with the system.

David J.

I only understand a quarter of what you guys are writing,
but to me the JDBC driver throwing SQLException
"A result was returned when none was expected"
when my stored function is declared as "void" with

CREATE OR REPLACE FUNCTION words_skip_game(
IN in_uid integer,
IN in_gid integer)
RETURNS void AS
$func$
BEGIN

is a strange decision. Why throw the exception, what's the benefit?

Even if PostgreSQL does not have stored functions (???),
why does not JDBC driver workaround that fact?

Regards
Alex

On Wed, Jun 15, 2016 at 3:07 PM, Alexander Farber <
alexander.farber@gmail.com> wrote:

I only understand a quarter of what you guys are writing,
but to me the JDBC driver throwing SQLException
"A result was returned when none was expected"
when my stored function is declared as "void" with

CREATE OR REPLACE FUNCTION words_skip_game(
IN in_uid integer,
IN in_gid integer)
RETURNS void AS
$func$
BEGIN

is a strange decision. Why throw the exception, what's the benefit?

Even if PostgreSQL does not have stored functions (???),
why does not JDBC driver workaround that fact?

​As far as JDBC is concerned is see a single row of data, with a single
column, whose contents include a datum of type void. It interprets this as
being "a result" and thus asks you to put it somewhere. Its doesn't treat
"void" any different than, say "integer". Whether or not it should might
be worthy of a discussion but that is how it works today.

​As for the exception - I suppose its a bit of looking over the
programmer's shoulder. You wrote a query that returns data but decided to
ignore the data - the driver assumes a mistake was made and throws an
exception informing you of that fact. How much of this is our decision and
how much of it is specified by JDBC I do not know.​

​While void "indicates that a function returns no value​" it is itself a
value; hence the dilemma.

Patches are welcome if you think there is a better way for us to operate.

David J.
​

On 06/15/2016 12:07 PM, Alexander Farber wrote:

I only understand a quarter of what you guys are writing,
but to me the JDBC driver throwing SQLException
"A result was returned when none was expected"
when my stored function is declared as "void" with

Because as I see it you used executeUpdate(), which is for doing INSERT,
UPDATE, DELETE directly. You are actually running function that hides
the UPDATE and returns a void resultset, which executeUpdate() does not
know what to do with. As was mentioned upstream try using executeQuery()
instead.

CREATE OR REPLACE FUNCTION words_skip_game(
IN in_uid integer,
IN in_gid integer)
RETURNS void AS
$func$
BEGIN

is a strange decision. Why throw the exception, what's the benefit?

Even if PostgreSQL does not have stored functions (???),
why does not JDBC driver workaround that fact?

Because JDBC is meant to be used against many database engines and is
meant to be fairly generic?

Regards
Alex

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Wednesday, June 15, 2016 9:07:10 PM EDT Alexander Farber wrote:

I only understand a quarter of what you guys are writing,
but to me the JDBC driver throwing SQLException
"A result was returned when none was expected"
when my stored function is declared as "void" with

CREATE OR REPLACE FUNCTION words_skip_game(
IN in_uid integer,
IN in_gid integer)
RETURNS void AS
$func$
BEGIN

is a strange decision. Why throw the exception, what's the benefit?

Even if PostgreSQL does not have stored functions (???),
why does not JDBC driver workaround that fact?

In addition to what Adrian and David had to say, I'll reiterate what I said
upthread: use PreparedStatement.executeQuery instead of
PreparedStatement.executeUpdate, and ignore the result. You are executing a
SELECT after all, and 'void' is a result, albeit a not very informative one.

And if you only understand a quarter of what we are writing, you may want to
read up on both jdbc and pgsql. Porting from PHP to java involves more than
mechanical replacing statements. Understanding the technologies is important.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Alexander Farber schrieb am 15.06.2016 um 15:56:

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 ?

A SELECT statement needs to be run using `executeQuery()`.

You can also `execute()` if you want to get the update count using Statement.getUpdateCount() but I am not sure if that is actually populated through the JDBC driver for SELECT statements calling functions.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general