--- In Firebird-Java@yahoogroups.com, Helen Borrie <helebor@t...> wrote:

> Yes it does - but it still returns a singleton result, containing

NULL in

> rsname.
>
> This guy is really hard to convince that, if you ask a question in

SQL that

> returns a result, you'll get a result. Here and in support, he

insists that

> there should be NO result if the answer is null.

Not really what I'm saying - and remember I started this thread by
describing what goes on in the Java/JDBC realm, not purely SQL - the
key here is not that 'the answer is null' but that we are working with
zero rows, so if we are getting a Java ResultSet back, should this not
also have zero rows?

Do appreciate your comments, I'm just trying to deduce what is the
'correct' behaviour. :-)
If an SQL query should produce zero rows, surely the ResultSet should
also contain zero rows (which means rs.next() should return false when
called the first time).

1) Query "select PK_SN from MYTABLE order by desc" produces zero rows
and it appears the ResultSet is empty because rs.next() returns false
(correct).
2) The result of "select max(PK_SN) from MYTABLE" is messy without any
rows - yes if running such a query 'interactively' the result should
be null. But I'm not comfortable (when done via JDBC/Java) with the
ResultSet 'having a row' which has this null in it - if an empty
ResultSet is not returned then maybe it'd be better if an Exception
was thrown?

I'd never normally bet money on microsoft doing something right, but
in scenario (2) with SQLserver when you call rs.next(), it returns
false (i.e no row in the ResultSet)... IMHO more correct since there
are no rows for the SQL max(...) function to work with. Whereas with
FB we always get a row in the ResultSet - but you must also check if
it is 'real' or not.
There are many other cases where you may wish to get a max value (not
just when creating a new pk). Another example adding two columns
(within the SQL) and taking the max(...) of that (unlikely to try that
one ;-) ) - again probably subject to the same thing. oops, I'm rambling.

Anyway, that's my 2c worth... if this is the way FB works then I'll
accept it ;-)
But I have to use approach (1) -yuk- because the technique used in (2)
with FB (finding a row in the ResultSet when there are no rows) will
most likely not work with other databases which we must support (eg
SQLserver which I know does not work like FB).

> He ought to use whatever it takes to get
>
> select (what you want) from atable
> where exists (select 1 from atable)

What a tick - That's a good possibility, will try in the morning.
Thanks Helen

>
> That does both tasks in a single query, in the proper manner for SQL,
> without wasting a second hit on the database.
>
> H.