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

> >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).
>
> You're still not grokking the difference between selecting a set of

rows

> and selecting the result of an expression.

I don't know what grokking is, but I fully understand the difference
between selecting 0 or more rows and getting the result of an expression.
This is not an SQL issue but a jdbc/java issue, i.e we are working
with an API (which is encapsulating SQL behaviour) in a java
programming environment.

> >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.
>
> What does "interactively" mean? All queries are submitted by clients -
> even those in stored procedures, indirectly. There really is no

such thing

> as "an interactive query". All DSQL queries pass through the API,

one way

> or the other.

Sorry, I don't know what the correct term is for 'interactively' - I
mean when you type & run SQL directly on the command-line of in the
default (native) database tool. I.e when we are running simply SQL...
as opposed to the jdbc/java environment where jdbc/java is running the
SQL, and can encapsulate the SQL results, etc. In this latter case
java can potentially 'do more' with an SQL result... in this case IMHO
with SQLserver 'less is more' when it returns an empty ResultSet when
the result of the query is null. An empty ResultSet is implicitly
obvious, whereas if the ResultSet always contains something then we
must take a look at it and decide if it's valid.

> >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?
>
> Really? Break the valid, expected behaviour to make it fit with

what you

> thought you had learned from using SQL Server?

This is valid, expected behaviour in an SQL sense, but I hesitate to
agree that jdbc/java should wrap this perfectly 'as-is' without
attempting to do something better with it.
Maybe I *have* been living in a dreamworld working with SQLserver for
the last 2 projects ;-)

> SQL is a standard and JDBC is a standard. Consistency will lie in the
> accord between the two; not in trying to force Microsoft's idea of

SQL and

> Java onto standard implementations. Mainline for the standard and

branch

> for the Microsoft stuff.

hear hear, I use very little microsoft stuff myself - no IE or
outlook, etc., long-live open source... anyway one thing that nobody
has suggested (unless I missed it) - if this returning empty ResultSet
behaviour is unique to SQLserver, maybe it's specific to the jdbc
driver we've always used?

Anyway there is another way to always get a row.... "isnull(max(x),
0)". Ok for some purposes anyway.

Thanks, it's been fun ;-)