RE: Java code help... -- JAVA400-L

Ok, without, getting into it to any great degree, I have a question that
perhaps you can help me see the light on.

Why do people's ResultSet columns 'move around' so much?  I can see
additional functions requiring new data to be
returned, but people seem to portray it as 'my SQL statements that I use in
my application randomly grab rows
each release... if I couldn't use the column name, I'd be totally sunk'.

Doesn't any one or several of the following all but eliminate this issue:

a) Never code with select * to get your columns.  That in itself is a very
bad practice because of a lot of wasted
energy in a program to bind and retrieve columns, move data, allocate
storage, etc.  Not to mention it opens
you up to more problems like we are talking about here where the same
column name is in the table multiple
times.

b) Never change your SQL statement to add columns in the middle of a list.
If you change
          SELECT COL1, COL4, COL8 FROM TABLEX
     to
          SELECT COL1, COL4, COL8, COL3 FROM TABLEX
     you will never mess up your existing code's indexes.

c) Encapsulate your SQL statements in their own code that understands the
structure and nature of the request.
If its anything that would be/could be used over, you should do this
anyway.  The business logic should be
talking to a database access object, not the database 'column name'.  This
sets you up for great reuse of
existing code (and more importantly already completed work at runtime).
This also allows you to handle the
case where you really need to pull out a column from a query by only fixing
up indexes in one place/component.

------------------

I apologize, but the comparison to RPG/II and externally vs. internally
described files is totally lost on me as I know
nothing about either subject.  I do take exception to you suggesting that I
advocate poor programming practices.
It's not like I'm suggesting assembly language programming because we can
really squeeze a couple milliseconds
out of our apps.  I must believe that you see my suggestions as such
because you are failing to think about encapsulating
your business logic from your database layout at any higher level that what
the JDBC API provides for you.

---------

And finally:

So with that argument in mind, it seems that JDBC driver writers are
writing the driver for application writers to use and should attempt to
make access by column name work efficiently.  Why not create an temporary
index of the fields using a hash of the name-- I don't see why you need to
do a linear search.

Believe me, we do work to make it efficient.  You are not seeing the full
picture.
Consider two things:

1) You have to account for the fact that columns could be in the ResultSet
multiple
times.  I looked back at the code and found that I actually do use a
HashTable for storage
and retrieval, but you still have these issues to think about.  Also, any
way you slice it, if you
had 100 columns... we create 100 strings to place a hashtable (at a
minimum) and 100 Integer
objects (because you can't stick primitive types in a collection.  You can
say "do it fast"...
but you have to remember, you are comparing figuring out a column name to
column
index resolution to the price of returning an integer (as an example).
Getting an int when
you don't have to do column lookup is so fast that you can't reliably
measure doing as few as
100 of them.

2) You may not realize this, but we can't just take every column name you
pass
in and do a hash lookup on it.  Well, we could, but it would have several
bugs (most of which
have already come in from the field - I really wasn't forward thinking
enough to think of them
all myself).  We have to follow the rules of how the system names columns.
That means you send
me a name.  I check to see if its null.  Check to see if it starts with a
quote character.
Then either uppercase the name or strip the quotes.  Guess what?  I just
created a string for
every string you created to hand to the driver.


The bottom line:  I don't care how you get at your ResultSet columns.  If I
was writing an
application where performance was either not critical or there was no
competition to seriously
worry about, I probably wouldn't worry about it.  But if I were designing
for the long term,
I would:
     1) insulate myself from the database better than just getting data by
column name can allow
          anway.
     2) lay down the encapsulation layer that allows for reuse of data
access objects instead of
          concerning myself about the individual JDBC calls.

Richard D. Dettinger
AS/400 Java Data Access Team

Democracy's enemies have always underestimated the courage of the American
people.
It was true at Concord Bridge.  It was true at Pearl Harbor.  And it was
true today.

         Rochester Post-Bulletin
         Tuesday September 11, 2001


marshall@dpslink.com@midrange.com on 11/16/2001 07:24:47 AM

Please respond to java400-l@midrange.com

Sent by:  java400-l-admin@midrange.com


To:   java400-l@midrange.com
cc:
Subject:  RE: Java code help...



>>BTW:  Your app will perform better if you use the column indexes instead
of
>>the column names.  Part of why using names instead of column indexes is
so
>>expensive is because you have to do a linear search of the names of the
>>columns in the ResultSet for just this reason.  If your ResultSet has
100
>>rows and you are fetching the 100th row with a column name, you could be
>>spending an order of magnitude more time in the driver figuring out what
>>the column index is over actually doing the work of getting the data.

>>Sorry, dude... I'm a JDBC driver writer... I have to say those types of
>>things to the application programmers.  :-)

As application developer I have to say that I would never advocate for my
team to access fields in a result set by index.  It may perform better,
but it is bad programming practice.  I never want to hard code in my
application where fields are sequentially  located in a result set.  My
team is currently working on release 4.0 of our application.  Fields may
move around from release to release and I'm not changing hard-coded field
indexes every release.  Additionally, SQL statements are stored in
property files and the application code does not know or care about the
details of the SQL as long as the fields it is looking for are in the
result set.

This is really similar to saying for RPG/400 and RPG/ILE that application
programmers should use internally described files (hard coding field
positions like RPG/II) because it performs better.  There are way too many
advantages to externally described files.

So with that argument in mind, it seems that JDBC driver writers are
writing the driver for application writers to use and should attempt to
make access by column name work efficiently.  Why not create an temporary
index of the fields using a hash of the name-- I don't see why you need to
do a linear search.

===
Marshall Dunbar
DPS, Inc.
marshall@dpslink.com
(317)574-4300 (800)654-4689
====
_______________________________________________
This is the Java Programming on and around the iSeries / AS400 (JAVA400-L)
mailing list
To post a message email: JAVA400-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/java400-l
or email: JAVA400-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/java400-l.