Re: need cursor info
On 27.11.2006 02:35, Martin Gregorie wrote:
Robert Klemme wrote:
I am sorry, but you are wrong here. Nothing will break if the table
layout changes as long as you enumerate columns explicitly. Accessing
columns by index is as safe as using the name and usually faster than
by name. Doing so is perfectly sane since you create the query. When
doing ResultSet rs = st.executeQuery("select foo, bar from tab") you
always know that column 1 is "foo" and column 2 is "bar". The bad
practice is this:
Not entirely. Your example is, of course, correct. However, its also
legal to write various equivalents to "select 1,3,5,6 from foo..." which
is just as unstable as "select * from ..." and, unfortunately, equally
legal SQL.
I completely forgot about *that* option. Thanks for pointing it out.
That seems even less good practice to me - and I cannot remember the
last time I have actually seen that.
> The OP's code fragment is equivalent to "insert 1,2,3
values("AINSWORTH", 35, true) into foo". That may map onto a properly
enumerated column list but we just can't tell from the quoted code
fragment. Hence my warning.
Since we did not see the SELECT you're formally correct. But given the
rareness of actually using SELECT 1,2,3 you can assume that the SELECT
actually enumerated column names. And in that case it's perfectly safe
to use indexes.
If the select statement came from some other piece of code (i.e. not
within the same method) then it might actually be better to use column
names. But in that case a more robust approach should be taken anyway.
Typically you would check that the columns you expect are there and
throw some kind of exception if they are not because that would be a
violation of the method's contract. Or you go completely meta like
generic SQL tools do and just print / present / output all columns that
are in the ResultSet.
Btw, indexing by name and findColumn() also break if columns are renamed.
Of course, but at least using column names should give a more meaningful
error message AND scanning the source code will at least flag up all the
statements that need to be changed - which won't happen if column
numbers are used.
You can still search for the table name. This seems the safer debugging
technique to me since it will catch all occurrences of that table and
not find places in code that access similarly named columns in other tables.
Adding and deleting columns are much more likely changes than renaming
an existing column, simply because we *know* the latter causes problems.
Right.
If all the column numbers still fall within the table the query using
them will at best produce data conversion errors, which can waste a lot
of time if the bug chaser makes wrong assumptions about the cause of the
bug, and at worst will silently do incorrect value conversions and
produce wrong results.
Maybe. This certainly depends on the size of the code base and the team.
Thanks for the fruitful discussion!
Kind regards
robert