--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@t...>
wrote:

> At 03:54 PM 31/05/2004 +1200, you 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");
> >
> >However an exception occurs on the last line:
> >"org.firebirdsql.jdbc.FBSQLException: Column name PK_SN not found in
> >result set"
> >This in itself is not a suprise because there are no rows... the

problem

> >is that rs.next() should return false (indicating that there is not a
> >'next' row).
> >
> >Can anyone confirm that this is a bug and suggest the 'firebird' way of
> >doing it? (This sort of thing works properly with SQLserver )
>
> Ahem, the output of an expression is *never* the column name of the

column

> that was operated on. Last time I used SQL Server it wasn't the case
> there, either.

Of course the output isn't the column name. But for each data row in a
result set, there may be multiple columns to retrieve data values for
(obviously not in this example). You can retrieve each of these column
values by column number (eg rs.getInt(1), rs.getInt(2),
rs.getInt(3)...) OR by column name (eg rs.getInt("MyNumber"),
rs.getInt("SomeOtherNum"), rs.getInt("AThirdColumn")...).
I prefer the latter because it doesn't break if you insert extra
columns in the database/query (as long as you don't rename them ;-) ).
Anyway, see the API reference - yes you can get column values by name
- and I did this exhaustively last year with SQL server (I guess it
hasn't changed since then :-) ). And yes it works with Firebird once
there's at least one row.
This still doesn't explain why rs.next() should return true because
the API reference states that next returns "true if the new current
row is valid; false if there are no more rows". So the first call to
rs.next() should return false because there are no rows (and this is
indeed how it works with SQLserver).

> Recode this as:
>
> ...
> java.sql.Statement s = myConnection.createStatement();
> ResultSet rs = s.executeQuery("select max(PK_SN) as RESULT from

MYTABLE");

> if (rs.next())
> // Then the row exists - return the value:
> return rs.getInt("RESULT");
>
> I can't make sense of the Subject of your message. SELECT <an

expression>

> should return a result. In this case, it will be NULL.

Don't quite agree - the returned ResultSet should be empty, and so a
call to rs.next() should not be true. I just posted a followup;
essentially if you change the SQL todo:
"select PK_SN from MYTABLE order by PK_SN desc"
If there are no rows then rs.next() returns false (good, what I
expect!). Seems a little inconsistent, doesn't it?

> An even more life-saving piece of advice is: in a multi-transactional
> system, never use this as a way to decide what's going to be the next
> number in a series, ***especially*** if you're doing it to keys.

Yes it is a dangerous area, however our system is not necessarily
multi-transactional. Regardless, we'll have to employ optimistic
locking techniques if we're doing stuff like this. Also it's
multiplatform and needs to be database-independent, so we want to
avoid auto-incrementing keys (the implementation of those can be
dependant on the database used).

Thanks