Thread: Num of returned ROWS

    I'm using Postgres 8.0.3 and JDBC build 311 (tried 400 too) and using
executeUpdate to send the following command to DB:

set datestyle to ISO,DMY;
create table table1 as select F1, sum(F10) from table where F1 between
'1/1/2001' and '31/12/2004' group by F1;
select count(*) from table1

    But the executeUpdate always return 0, even when the cont(*) return a
number other than 0.

    Any ideas ?

Fernando Hartmann wrote:

>     I'm using Postgres 8.0.3 and JDBC build 311 (tried 400 too) and
> using executeUpdate to send the following command to DB:
>
> set datestyle to ISO,DMY;
> create table table1 as select F1, sum(F10) from table where F1 between
> '1/1/2001' and '31/12/2004' group by F1;
> select count(*) from table1
>
>     But the executeUpdate always return 0, even when the cont(*) return
> a number other than 0.
>
>     Any ideas ?

The backend doesn't return the number of rows affected for a CREATE
TABLE .. AS, so the driver can't give you a useful number.

-O

Oliver Jowett wrote:
> Fernando Hartmann wrote:
>
>
>>    I'm using Postgres 8.0.3 and JDBC build 311 (tried 400 too) and
>>using executeUpdate to send the following command to DB:
>>
>>set datestyle to ISO,DMY;
>>create table table1 as select F1, sum(F10) from table where F1 between
>>'1/1/2001' and '31/12/2004' group by F1;
>>select count(*) from table1
>>
>>    But the executeUpdate always return 0, even when the cont(*) return
>>a number other than 0.
>>
>>    Any ideas ?
>
>
> The backend doesn't return the number of rows affected for a CREATE
> TABLE .. AS, so the driver can't give you a useful number.
>
> -O
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
    Ok, but and the last line ? The select count ? They don't return the
number of rows too!

executeUpdate() returns the number of rows affected.  There are zero
rows affected by a SELECT COUNT(*) FROM ...

You'll need to use an executeQuery instead.

-- Mark Lewis

On Wed, 2005-06-08 at 08:38 -0300, Fernando Hartmann wrote:
> Oliver Jowett wrote:
> > Fernando Hartmann wrote:
> >
> >
> >>    I'm using Postgres 8.0.3 and JDBC build 311 (tried 400 too) and
> >>using executeUpdate to send the following command to DB:
> >>
> >>set datestyle to ISO,DMY;
> >>create table table1 as select F1, sum(F10) from table where F1 between
> >>'1/1/2001' and '31/12/2004' group by F1;
> >>select count(*) from table1
> >>
> >>    But the executeUpdate always return 0, even when the cont(*) return
> >>a number other than 0.
> >>
> >>    Any ideas ?
> >
> >
> > The backend doesn't return the number of rows affected for a CREATE
> > TABLE .. AS, so the driver can't give you a useful number.
> >
> > -O
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
> >
>     Ok, but and the last line ? The select count ? They don't return the
> number of rows too!
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

Fernando Hartmann wrote:

>>> set datestyle to ISO,DMY;

BTW, you shouldn't fiddle with DateStyle; the driver relies on it being
ISO (so this particular datestyle is OK, but consider using
PreparedStatement.setDate() instead).

>>> create table table1 as select F1, sum(F10) from table where F1 between
>>> '1/1/2001' and '31/12/2004' group by F1;
>>> select count(*) from table1
>>>
>>>    But the executeUpdate always return 0, even when the cont(*) return
>>> a number other than 0.

>     Ok, but and the last line ? The select count ? They don't return the
> number of rows too!

Ah, I thought you meant you were looking at the resultset generated by
the SELECT and it was non-zero..

There is no updated-row-count associated with a SELECT query. But that's
not why you see a 0 returned; it's because there are two sets of results
returned by the above multi-statement query, and you're only seeing the
results of the first query if you use executeUpdate().

You should be using getMoreResults() to step forward to the results of
the second query (the SELECT) and then inspect the returned resultset
(getResultSet()). getUpdateCount() after getMoreResults() should return
-1 ("no update count, this has a ResultSet"); see the JDBC javadoc.

(this assumes you're using a recent driver which follows the spec;
earlier drivers were a bit fuzzier about how they handled multiple results)

-O