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

From: Carl

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".

You seem to be missing the fundamentals of the situation, Carl. Let me
explain the business requirements. Please show us the exact code that would
be executed in response to the following user requests:

1. Show the first ten records of the customer file in alpha sequence. This
is the load of the work with customers program, and is done by the following
ISAM instructions: OPEN, READ (x10).

2. Position to the first ten records of that file starting with the first
record starting with "G". This is the standard "position to" feature that
the user would request. It is executed by SETLL, READ (x10).

3. Give me the PRIOR ten records. This is accomplished by READP (x20)
followed by READ (x10).

At no point is more than three times the page size ever read. In most
cases, there are exactly ten reads. No indices need to be rebuilt. Please,
show us how to do this without three different result sets. Because it's
clear one of us is just plain wrong here, and I'm pretty sure it's not me.

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.

Oh wow! You've used MySQL! On thousands of records, I bet! Sorry for the
sarcasm, but try scaling to a file with 100 million records and then tell me
how your response time goes.

You seem to be leaning towards a RPG vs SQL discussion, which is like
comparing apples and orangutans.

And yet, you were willing to compare the two, and even to say that
SETGT/READP is a workaround ("arose from the inability to dynamically sort
records").

FWIW, I concede that RPG record level
access is more efficient, and provides nice granular access vs. SQL,

Which makes ISAM more appropriate for certain classes of business
problems...

I also maintain that SQL has many strengths over RPG records level
access.

...and SQL more appropriate for others. That's all.

A discussion about RPG vs. Java would be... unproductive, I
imagine.

It depends on your goal. If your goal is to constructively identify the
situations where RPG is best suited and the situations where Java is best
suite, then it would by quite productive. If the idea is to try to prove
Java is better than RPG, then I imagine you'd be disappointed. I use Java
and RPG every day, just as I use SQL and indexed access. I understand the
strengths and the weaknesses, and I have both in my arsenal. I believe in
the right tool for the job.

But that's just me.

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.

It is not. Again, please work out the simple example. Position a cursor at
three points in a view by key. IT CAN'T BE DONE. Yes, you can position by
what is in effect relative record within the view, but that doesn't work on
million records files. Again, please write the code for the example above
and then get back to us.

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

You may not have asked questions, but you've continued to make inaccurate
statements. And you've implied in earlier posts that "record level random
access may not be needed", a point which is flatly wrong.

Joe