Subject Re: Query returning row when it shouldn't...
Author phil_sorry_trouble_with_profile
--- In Firebird-Java@yahoogroups.com, Stefan Mühlemann <smue@k...> wrote:
> Phil wrote:
>
> > Hi, I'm a new Firebird user, using Firebird-1.5.0.4306-Win32 and
> > Firebird-JDBC-SQL-1.5.0Beta3JDK_1.4.
> >
> > I have a new database , no data in it yet. When my application
starts it
> > looks for a value in the database, returning it if found, i.e
something
> > like:
> >
> > ...
> > java.sql.Statement s = myConnection.createStatement();
> > ResultSet rs = s.executeQuery("select max(PK_SN) from MYTABLE");
> > if (rs.next())
> > // Then the row exists - return the value:
> > return rs.getInt("PK_SN");
> >
> This statement will return one row with one field with content "null".

But my original statement should return zero rows! If you change the
SQL to:
"select PK_SN from MYTABLE order by PK_SN desc"
then if there are no rows, then rs.next() returns false (good, what I
expect!).

>
> Try
>
> ResultSet rs = s.executeQuery("select max(PK_SN) maxPKSN from
MYTABLE");
> if (rs.next())
> // Then the row exists - return the value:
> return rs.getInt("maxPKSN");
>
> or
>
> ResultSet rs = s.executeQuery("select max(PK_SN) maxPKSN from
MYTABLE");
> if (rs.next())
> // Then the row exists - return the value:
> return rs.getInt(1);
>
> Stefan