Bug #6260 Connector/J updateable resultset problem
Submitted: 26 Oct 2004 11:32 Modified: 5 Nov 2004 17:06
Reporter: [ name withheld ] Email Updates:
Status: Not a Bug Impact on me:
Category:Connector / J Severity:S2 (Serious)
Version: OS:
Assigned to: Mark Matthews CPU Architecture:Any

[26 Oct 2004 11:32] [ name withheld ]
In one of my application I had a need for query like this:

select firstname, lastname, {fn concat(lastname, firstname} as searchKey from employees

but the resultset turned to be unapdateable because the driver finds that both fields appear more than once, because it counts the fields inside the escape sequence.

How to repeat:
execute the above query

Suggested fix:
do not count any columns inside an escape sequence.
[5 Nov 2004 16:37] Mark Matthews
I'm confused, are you saying you want an UpdatableResultSet with that query, or you don't?
[5 Nov 2004 17:06] Mark Matthews
Ok, I get you now (after re-reading).

This is not a bug. Result sets that have function calls in them can not be made updatable. The driver sees that the column from '{fn concat(lastname, firstname)}' is a derived column, and bails out, as there is no way to create the statements that would allow that result set to be updatable.
[7 Nov 2004 10:04] [ name withheld ]
Yes, the resultset having derived fields can definitely be updateable. This is the behavior I get with jdts and datadirect MS SQL drivers. The problem with MySQL driver is that it adds the fields inside the function escape sequence to the query field list, and sets the resultset to read only when it detects duplicate fields in that list.
 The same query is updateable inside MySql Control Center, so why should it be r/o through JDBC driver?

I have played with the source code and got ignore any fields inside an escape sequence and it worked well. But I always lose my code fix when I upgrade to a new version. So, May I see an end to such dilemma?