Обсуждение: Moving from PHP to Java: A result was returned when none was expected.
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));
}
$sth = $dbh->prepare('SELECT words_skip_game(?, ?)');
$sth->execute(array($uid, $gid));
}
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 ?
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;
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;
Re: Moving from PHP to Java: A result was returned when none was expected.
От
Alexander Farber
Дата:
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
Re: Moving from PHP to Java: A result was returned when none was expected.
От
Alexander Farber
Дата:
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
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.
Re: Moving from PHP to Java: A result was returned when none was expected.
От
"David G. Johnston"
Дата:
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.
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.
Re: Moving from PHP to Java: A result was returned when none was expected.
От
"David G. Johnston"
Дата:
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.
Re: Moving from PHP to Java: A result was returned when none was expected.
От
Alexander Farber
Дата:
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
Re: Moving from PHP to Java: A result was returned when none was expected.
От
"David G. Johnston"
Дата:
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" withCREATE OR REPLACE FUNCTION words_skip_game(IN in_uid integer,IN in_gid integer)RETURNS void AS$func$BEGINis 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
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.
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
isactually populated through the JDBC driver for SELECT statements calling functions.
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