Обсуждение: A result was returned by the statement, when none was expected.

    Hello all.

    I'm using PostgreSQL 7.2.2, with the latest JDBC driver, JDK 1.4.1, under
linux.

    In my database, I have several functions defined, and I use them to
insert & update rows in the database.  Here is an example.
create function insert_artist (varchar, int) returns bigint as '
        insert into artist (name, genre_loid)
                values ($1, $2);
        select currval(\'loid_seq\');'
        language 'sql';
create function update_artist (varchar, bigint, bigint) returns integer as
'
        update artist
                set name = $1, genre_loid = $2
                where loid = $3;
        select 1;'
        language 'sql';

    The insert_artist function returns the last value assigned by a sequence,
and update_artist simply returns the integer 1.

    When executed from psql with: select x_artist(...); all is as expected.
When I execute it from Java via:
Statement statement = connection.createStatement();
int result = statement.executeUpdate("select x_artist(...)");
the row is updated or inserted, as is the case, but I get the follwoing
exception.
A result was returned by the statement, when none was expected.
    at org.postgresql.jdbc2.Statement.executeUpdate(Statement.java:75)
    at com.silverfields.dbAccess.DBObject.save(DBObject.java:205)


    What have I done incorrectly?  Both functions are defined to return a
single value, and Statement.executeUpdate(String) returns a single value.

    I finally figured this out.  For anyone that might want to know, what I
nneded was this:
if (statement.execute(sqlStatementString)) {
    //ResultSet Available
    ResultSet rs = statement.getResultSet();
    rs.next();
    result = rs.getInt(1);
}else{
    //Update count or no result available
    result = statement.getUpdateCount();
}

    For my functions, a ResultSet is returned, which does NOT make sence as
the functions were declared to return type integer.  Oh well.


On Sun, 3 Nov 2002 17:20:18 -0500
Timothy Reaves <treaves@silverfields.com> wrote:

>     Hello all.
>
>     I'm using PostgreSQL 7.2.2, with the latest JDBC driver, JDK
>     1.4.1, under
> linux.
>
>     In my database, I have several functions defined, and I use them
>     to
> insert & update rows in the database.  Here is an example.
> create function insert_artist (varchar, int) returns bigint as '
>         insert into artist (name, genre_loid)
>                 values ($1, $2);
>         select currval(\'loid_seq\');'
>         language 'sql';
> create function update_artist (varchar, bigint, bigint) returns integer
> as'
>         update artist
>                 set name = $1, genre_loid = $2
>                 where loid = $3;
>         select 1;'
>         language 'sql';
>
>     The insert_artist function returns the last value assigned by a
>     sequence,
> and update_artist simply returns the integer 1.
>
>     When executed from psql with: select x_artist(...); all is as
>     expected.
> When I execute it from Java via:
> Statement statement = connection.createStatement();
> int result = statement.executeUpdate("select x_artist(...)");
> the row is updated or inserted, as is the case, but I get the follwoing
> exception.
> A result was returned by the statement, when none was expected.
>     at org.postgresql.jdbc2.Statement.executeUpdate(Statement.java:75)
>     at com.silverfields.dbAccess.DBObject.save(DBObject.java:205)
>
>
>     What have I done incorrectly?  Both functions are defined to
>     return a
> single value, and Statement.executeUpdate(String) returns a single
> value.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html

Your problem is that you are calling executeUpdate while passing in a
select statement.  Even though the select statement only returns 1 row of
1 column, it is still a ResultSet while the driver does not expect one to
be returned because you claimed to be doing an update.

Kris Jurka

On Sun, 3 Nov 2002, Timothy Reaves wrote:

>     I finally figured this out.  For anyone that might want to know, what I
> nneded was this:
> if (statement.execute(sqlStatementString)) {
>     //ResultSet Available
>     ResultSet rs = statement.getResultSet();
>     rs.next();
>     result = rs.getInt(1);
> }else{
>     //Update count or no result available
>     result = statement.getUpdateCount();
> }
>
>     For my functions, a ResultSet is returned, which does NOT make sence as
> the functions were declared to return type integer.  Oh well.
>
>
> On Sun, 3 Nov 2002 17:20:18 -0500
> Timothy Reaves <treaves@silverfields.com> wrote:
>
> >     Hello all.
> >
> >     I'm using PostgreSQL 7.2.2, with the latest JDBC driver, JDK
> >     1.4.1, under
> > linux.
> >
> >     In my database, I have several functions defined, and I use them
> >     to
> > insert & update rows in the database.  Here is an example.
> > create function insert_artist (varchar, int) returns bigint as '
> >         insert into artist (name, genre_loid)
> >                 values ($1, $2);
> >         select currval(\'loid_seq\');'
> >         language 'sql';
> > create function update_artist (varchar, bigint, bigint) returns integer
> > as'
> >         update artist
> >                 set name = $1, genre_loid = $2
> >                 where loid = $3;
> >         select 1;'
> >         language 'sql';
> >
> >     The insert_artist function returns the last value assigned by a
> >     sequence,
> > and update_artist simply returns the integer 1.
> >
> >     When executed from psql with: select x_artist(...); all is as
> >     expected.
> > When I execute it from Java via:
> > Statement statement = connection.createStatement();
> > int result = statement.executeUpdate("select x_artist(...)");
> > the row is updated or inserted, as is the case, but I get the follwoing
> > exception.
> > A result was returned by the statement, when none was expected.
> >     at org.postgresql.jdbc2.Statement.executeUpdate(Statement.java:75)
> >     at com.silverfields.dbAccess.DBObject.save(DBObject.java:205)
> >
> >
> >     What have I done incorrectly?  Both functions are defined to
> >     return a
> > single value, and Statement.executeUpdate(String) returns a single
> > value.
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/users-lounge/docs/faq.html
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

Timothy,

What actually happens is that the select returns the values of the
functions.

So simply if you have a select foo() where foo returns 1 then you will
get the following a column named foo, and a value of 1 for it,
regardless of what foo does internally.

The result set is from the select, not the function.

Dave
On Sun, 2002-11-03 at 21:04, Timothy Reaves wrote:
>     I finally figured this out.  For anyone that might want to know, what I
> nneded was this:
> if (statement.execute(sqlStatementString)) {
>     //ResultSet Available
>     ResultSet rs = statement.getResultSet();
>     rs.next();
>     result = rs.getInt(1);
> }else{
>     //Update count or no result available
>     result = statement.getUpdateCount();
> }
>
>     For my functions, a ResultSet is returned, which does NOT make sence as
> the functions were declared to return type integer.  Oh well.
>
>
> On Sun, 3 Nov 2002 17:20:18 -0500
> Timothy Reaves <treaves@silverfields.com> wrote:
>
> >     Hello all.
> >
> >     I'm using PostgreSQL 7.2.2, with the latest JDBC driver, JDK
> >     1.4.1, under
> > linux.
> >
> >     In my database, I have several functions defined, and I use them
> >     to
> > insert & update rows in the database.  Here is an example.
> > create function insert_artist (varchar, int) returns bigint as '
> >         insert into artist (name, genre_loid)
> >                 values ($1, $2);
> >         select currval(\'loid_seq\');'
> >         language 'sql';
> > create function update_artist (varchar, bigint, bigint) returns integer
> > as'
> >         update artist
> >                 set name = $1, genre_loid = $2
> >                 where loid = $3;
> >         select 1;'
> >         language 'sql';
> >
> >     The insert_artist function returns the last value assigned by a
> >     sequence,
> > and update_artist simply returns the integer 1.
> >
> >     When executed from psql with: select x_artist(...); all is as
> >     expected.
> > When I execute it from Java via:
> > Statement statement = connection.createStatement();
> > int result = statement.executeUpdate("select x_artist(...)");
> > the row is updated or inserted, as is the case, but I get the follwoing
> > exception.
> > A result was returned by the statement, when none was expected.
> >     at org.postgresql.jdbc2.Statement.executeUpdate(Statement.java:75)
> >     at com.silverfields.dbAccess.DBObject.save(DBObject.java:205)
> >
> >
> >     What have I done incorrectly?  Both functions are defined to
> >     return a
> > single value, and Statement.executeUpdate(String) returns a single
> > value.
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/users-lounge/docs/faq.html
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>