Just to mention a point - you said you would have liked the JDBC tutorial to mention the need for a primary key.

I think it may have been omitted because almost every non-IBM i DBMS strongly assumes there will be a primary key - it's possible not to, but that is a really strong assumption, supported in all their tools.

I suspect someone has recommended adding a primary key constraint to the table in question. That might need a new column, but all that is relatively easy using SQL DDL.

Naturally there are possible side effects to consider! :)


On 1/28/2013 11:01 AM, James H. H. Lampert wrote:
On 1/4/13 1:17 AM, Dave wrote:
Keep going James, you are doing some good work and we are all getting the

At this point, I know that (at least in MySQL, which has no concept of
RRNs; hopefully I'll know about DB2/400 later this week) you literally
are not allowed to obtain an update cursor on a file that lacks a
primary key. (You ask for an updateable result set, and it gives you a
read-only one anyway).

And it turned out that the tutorial Mark recommended *did* mention that
you needed a primary key to get through the update-by-result-set
example. But that remark was so inconspicuous that I didn't even find it
until after I'd already come to that conclusion, and had spent over an
hour trying to find out how to set one up. (You'd think that if a
primary key were that important, the JDBC tutorial would have included
it in the environment set-up section, and both the MySQL docs and the
Sequel Pro helptext would make a point of telling you how to set one up,
as well.)

This morning, I went into MySQL again, with Sequel Pro, and created a
"multiple identical records" situation, then tried to change one through
Sequel Pro. Unlike my previous test with Squirrel on DB2/400, I was
actually able to change exactly one out of three identical records, and
when I looked at the underlying requests in the console, I found that it
had done an UPDATE request with WHERE clause on every field in the
record (much the same as Squirrel had done), but also added a "LIMIT 1"

Further research tells me that LIMIT is not standard SQL, any more than
RRN is.

Ye vish!


amazon.com ads help fund midrange.com operations

This thread ...


Return to Archive home page | Return to MIDRANGE.COM home page