Thread: updatable (J2EE) resultsets
Hi all. I'm new to the list, so first off I'd like to say hello. The reason I've joined the list is that I've tried to use the updatable ResultSet feature with the JDBC drivers for Postgres and found that they won't work if you have columns in your database that have capital letters in it. After a lot of fumbling (which included not downloading the source, but decompiling the driver (I'm not too proud of that :))) I've found the AbstractJdbc2ResultSet.insertRow method, which prepares the query for execution, but ignores the fact that columnames must be included between "- s if they are capitalized. I'd be happy to post a patch, but I'm really a newcomer when it comes to open-source shared development, and haven't got the slightest idea on how to do it. I've tried to look this up in the archives, but haven't found any threads dealing with this issue. Thanks Andras Gerlits
1) why do you want to use mixed case columns. Postgres supports them but not very well. 2) so what you are suggesting is that if a column name has any upper case letters, then it should be quoted? Dave On Tue, 2002-12-10 at 09:04, Gerlits András wrote: > Hi all. > > I'm new to the list, so first off I'd like to say hello. > > The reason I've joined the list is that I've tried to use the updatable > ResultSet feature with the JDBC drivers for Postgres and found that they > won't work if you have columns in your database that have capital letters > in it. After a lot of fumbling (which included not downloading the source, > but decompiling the driver (I'm not too proud of that :))) I've found the > AbstractJdbc2ResultSet.insertRow method, which prepares the query for > execution, but ignores the fact that columnames must be included between "- > s if they are capitalized. > > I'd be happy to post a patch, but I'm really a newcomer when it comes to > open-source shared development, and haven't got the slightest idea on how > to do it. > > I've tried to look this up in the archives, but haven't found any threads > dealing with this issue. > > Thanks > Andras Gerlits > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org -- Dave Cramer <Dave@micro-automation.net>
Dave Cramer wrote: > 1) why do you want to use mixed case columns. Postgres supports them but > not very well. > Dave, Can you be more specific? Don't the PostgreSQL delimited identifiers do the trick? We are already talking about a 7.4 release. is there something that could be done to improve this? Regards, Fernando -- Fernando Nasser Red Hat Canada Ltd. E-Mail: fnasser@redhat.com 2323 Yonge Street, Suite #300 Toronto, Ontario M4P 2C9
The following adds support for updateable ResultSets with a mixed case table name and column names. What could really use some work is the parseQuery method in org.postgresql.jdbc2.AbstractJdbc2ResultSet. When determining if a ResultSet can be updated it does not check that all colums of the result set aren't derived columns. The check to make sure it is a single table is not particularly good. It fails on things like: SELECT CURRENT_TIMESTAMP; SELECT a AS from FROM t; SELECT a FROM t1 LEFT JOIN t2 ON (t1.id=t2.id); SELECT a FROM t1 UNION SELECT a FROM t2; I'm not sure how much this can be improved without building a full blown parser into the driver. On Tue, 10 Dec 2002, Fernando Nasser wrote: > Dave Cramer wrote: > > 1) why do you want to use mixed case columns. Postgres supports them but > > not very well. > > > > Dave, > > Can you be more specific? Don't the PostgreSQL delimited identifiers do > the trick? > > We are already talking about a 7.4 release. is there something that > could be done to improve this? > > Regards, > Fernando > > > > -- > Fernando Nasser > Red Hat Canada Ltd. E-Mail: fnasser@redhat.com > 2323 Yonge Street, Suite #300 > Toronto, Ontario M4P 2C9 > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >
Attachment
I have not seen this patch applied. Was it rejected for some reason? Kris Jurka On Wed, 11 Dec 2002, Kris Jurka wrote: > > The following adds support for updateable ResultSets with a mixed case > table name and column names. > > What could really use some work is the parseQuery method in > org.postgresql.jdbc2.AbstractJdbc2ResultSet. When determining if a > ResultSet can be updated it does not check that all colums of the result > set aren't derived columns. The check to make sure it is a single table > is not particularly good. It fails on things like: > > SELECT CURRENT_TIMESTAMP; > SELECT a AS from FROM t; > SELECT a FROM t1 LEFT JOIN t2 ON (t1.id=t2.id); > SELECT a FROM t1 UNION SELECT a FROM t2; > > I'm not sure how much this can be improved without building a full blown > parser into the driver. > > On Tue, 10 Dec 2002, Fernando Nasser wrote: > > > Dave Cramer wrote: > > > 1) why do you want to use mixed case columns. Postgres supports them but > > > not very well. > > > > > > > Dave, > > > > Can you be more specific? Don't the PostgreSQL delimited identifiers do > > the trick? > > > > We are already talking about a 7.4 release. is there something that > > could be done to improve this? > > > > Regards, > > Fernando > > > > > > > > -- > > Fernando Nasser > > Red Hat Canada Ltd. E-Mail: fnasser@redhat.com > > 2323 Yonge Street, Suite #300 > > Toronto, Ontario M4P 2C9 > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 6: Have you searched our list archives? > > > > http://archives.postgresql.org > > >
Attachment
On 23 Dec 2002, Dave Cramer wrote: > BTW, I only apply to the HEAD, we have never patched to the current > version. Barry and I have talked about it recently, obviously you think > this is "abnormal". What are your opinions about it? > This means that the driver we ship with 7.3.0 will be exactly the same as 7.3.1 and 7.3.2 and so on. The .0 release of the server and the driver will generally bring about the discovery of bugs in both. The server bugs are fixed and a new release is put out .1, but the driver bugs are only fixed in HEAD and our only advice is to run a driver from CVS head. This is something our users don't want to do because it requires a cvs checkout and running untested code. I think the policy for the driver should match that for the server: new features go into HEAD while bug fixes go into both the stable and development branches. Kris Jurka
On Tue, 2002-12-24 at 15:57, Kris Jurka wrote: > > On 23 Dec 2002, Dave Cramer wrote: > > > BTW, I only apply to the HEAD, we have never patched to the current > > version. Barry and I have talked about it recently, obviously you think > > this is "abnormal". What are your opinions about it? > > > > This means that the driver we ship with 7.3.0 will be exactly the same as > 7.3.1 and 7.3.2 and so on. The .0 release of the server and the driver > will generally bring about the discovery of bugs in both. The server bugs > are fixed and a new release is put out .1, but the driver bugs are only > fixed in HEAD and our only advice is to run a driver from CVS head. This > is something our users don't want to do because it requires a cvs checkout > and running untested code. I think the policy for the driver should match > that for the server: new features go into HEAD while bug fixes go into > both the stable and development branches. > > Kris Jurka I vote for backporting bugfixes to the stable branch, like Debian Stable does. -- []'s Daniel Serodio
Kris, I agree the driver should be like the server in this regard. That means that some bugfixes will get put into the 7.3 branch. But just like the server where only a small set of critical but non destabilizing fixes get back patched the same should apply to the jdbc driver. --Barry Kris Jurka wrote: > On 23 Dec 2002, Dave Cramer wrote: > > >>BTW, I only apply to the HEAD, we have never patched to the current >>version. Barry and I have talked about it recently, obviously you think >>this is "abnormal". What are your opinions about it? >> > > > This means that the driver we ship with 7.3.0 will be exactly the same as > 7.3.1 and 7.3.2 and so on. The .0 release of the server and the driver > will generally bring about the discovery of bugs in both. The server bugs > are fixed and a new release is put out .1, but the driver bugs are only > fixed in HEAD and our only advice is to run a driver from CVS head. This > is something our users don't want to do because it requires a cvs checkout > and running untested code. I think the policy for the driver should match > that for the server: new features go into HEAD while bug fixes go into > both the stable and development branches. > > Kris Jurka > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html >
One of the reasons we have done this (patch the head only) is to decouple the release of the driver from releases of the server. Previously we have run into the situation where we were freezing code because of a server code freeze. This caused alot of patches to be lost, and momentum to be lost by many people who used to contribute to the driver. By de-coupling the driver and letting it evolve on it's own time table we are free to apply patches whenever they are available. Also there really isn't a relationship between driver development and server development. What I mean to say is if the server improves the query analyzer it has no effect on the driver. However sometimes there is a correlation, for instance the server side caching of statements, or schema's. From my POV, we are better to not be coupled to server releases. However I do understand the need to back patch sometimes. Dave On Tue, 2002-12-24 at 13:23, Daniel Serodio wrote: > On Tue, 2002-12-24 at 15:57, Kris Jurka wrote: > > > > On 23 Dec 2002, Dave Cramer wrote: > > > > > BTW, I only apply to the HEAD, we have never patched to the current > > > version. Barry and I have talked about it recently, obviously you think > > > this is "abnormal". What are your opinions about it? > > > > > > > This means that the driver we ship with 7.3.0 will be exactly the same as > > 7.3.1 and 7.3.2 and so on. The .0 release of the server and the driver > > will generally bring about the discovery of bugs in both. The server bugs > > are fixed and a new release is put out .1, but the driver bugs are only > > fixed in HEAD and our only advice is to run a driver from CVS head. This > > is something our users don't want to do because it requires a cvs checkout > > and running untested code. I think the policy for the driver should match > > that for the server: new features go into HEAD while bug fixes go into > > both the stable and development branches. > > > > Kris Jurka > > I vote for backporting bugfixes to the stable branch, like Debian Stable > does. -- Dave Cramer <Dave@micro-automation.net>
Dave Cramer <Dave@micro-automation.net> writes:
> By de-coupling the driver and letting it evolve on it's own time table
> we are free to apply patches whenever they are available.
That point of view would be more easily maintained if you separated out
the JDBC driver as its own project (see gborg). As long as JDBC is part
of the server CVS tree and is shipped in server releases, you really
cannot ignore the server release cycle. Or at least, you do so at
peril of alienating users who expect to find a reasonably-maintained
JDBC driver in their downloads. If they have to download JDBC
separately to get important bug fixes, aren't you wasting their time
and bandwidth by including an old JDBC in the server distribution?
regards, tom lane