Re: Re[2]: Question using jdbc/jt400

[Carl <c.groner@xxxxxxxxx>]

On 8/30/07, Joe Pluta <joepluta@xxxxxxxxxxxxxxxxx> wrote:
> Actually, it does address this specific issue.  You just aren't seeing it.
> Let's take the simplest of functions, the customer lookup.  A user begins a
> lookup by getting a list of records in order.  They key in the first few
> letters of the name and ask the list to be repositioned.  This is a
> SETLL/READ.  However, they realize that they are actually just PAST the name
> they wanted, so they do a page backward.
>
> THIS CANNOT BE DONE IN SQL WITHOUT TWO SEPARATE RESULT SETS.  Just can't be
> done, because YOU CAN'T POSITION BY KEY.  It amazes me how this simple
> capability is still not even in the newest SQL ANSI recommendations.

It certainly can be done. Simply load a result set with all relevant
records, seek to the position by page and display x number of results.

For example, to page back in java just call
java.sql.ResultSet#relative(currentPosition - 10)
to move back 10 rows, assuming you are displaying 10 records at a time
to the user. Voilà! One result set, no need to position "by key".

http://java.sun.com/j2se/1.5.0/docs/api/java/sql/ResultSet.html#relative(int)

Also, even _if_ you needed to use two queries, what is the big deal?
I've worked on web applications backed by MySQL where each page
request would make several dozen queries and consistently provide
subsecond response times.

You seem to be leaning towards a RPG vs SQL discussion, which is like
comparing apples and orangutans. FWIW, I concede that RPG record level
access is more efficient, and provides nice granular access vs. SQL, I
also maintain that SQL has many strengths over RPG records level
access. A discussion about RPG vs. Java would be... unproductive, I
imagine.

> Until you understand this fundamental lack in SQL, it's really not worth
> discussing.

What you consider a "fundamental lack in SQL" I think is nicely
handled by a record set or record cache, as I've demonstrated above.
At the _most_ basic, SQL simply selects data, and it is the
programmers responsibility to use that data. Languages with good SQL
support simplify this process, as is the case with Java and
java.sql.ResultSet, for example.

> (And on a personal note, phrases such as "your bounds checking
> needs work" which both miss the point of the problem AND make a backhanded
> insult to my programming skills disincent me to continue the discussion.
> But maybe I'm just a thin-skinned wussie.)

I'd like to clarify that my comment about bounds checking was
certainly not directed at you, and is no way a comment about your code
quality. I've never seen a line of your code and am in no position to
comment either way.

I think we've gone a bit of topic...and I've refrained from asking any
questions in this post. If you feel that there is something that needs
clarifying, let's start another thread or feel free to contact me
directly

Cheers,
Carl.