Thread: Update ResultSet
Hi everyone,
I have a query including an inner join, which I execute to obtain a
ResultSet, but when I execute the following piece of code I get an error
message saying that it could not update the table because it could not
find the primary key for a table.
rs.first();
rs.updateString("gene", "TestGen");
rs.updateRow();
Here is my query:
SELECT gene_entropy.gene, gene_entropy.entropy, gene_entropy.iteration,
snp_entropy.snp_id
FROM clustering.gene_entropy INNER JOIN clustering.snp_entropy
USING(gene) limit 5;
And here is the stacktrace:
org.postgresql.util.PSQLException: Für die Tebelle
(clustering.gene_entropy konnte kein Primärschlüssel gefunden werden.
at
org.postgresql.jdbc2.AbstractJdbc2ResultSet.isUpdateable(AbstractJdbc2ResultSet.java:1547)
at
org.postgresql.jdbc2.AbstractJdbc2ResultSet.checkUpdateable(AbstractJdbc2ResultSet.java:2550)
at
org.postgresql.jdbc2.AbstractJdbc2ResultSet.updateValue(AbstractJdbc2ResultSet.java:2796)
at
org.postgresql.jdbc2.AbstractJdbc2ResultSet.updateString(AbstractJdbc2ResultSet.java:1309)
at
org.postgresql.jdbc2.AbstractJdbc2ResultSet.updateString(AbstractJdbc2ResultSet.java:1394)
at
myapp.ui.event.DBTableFrameListener.actionPerformed(DBTableFrameListener.java:406)
at
javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1995)
at
javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2318)
at
javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:387)
at
javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:242)
at
javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:236)
at
java.awt.AWTEventMulticaster.mouseReleased(AWTEventMulticaster.java:272)
at java.awt.Component.processMouseEvent(Component.java:6038)
at
javax.swing.JComponent.processMouseEvent(JComponent.java:3260)
at java.awt.Component.processEvent(Component.java:5803)
at java.awt.Container.processEvent(Container.java:2058)
at java.awt.Component.dispatchEventImpl(Component.java:4410)
at java.awt.Container.dispatchEventImpl(Container.java:2116)
at java.awt.Component.dispatchEvent(Component.java:4240)
at
java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4322)
at
java.awt.LightweightDispatcher.processMouseEvent(Container.java:3986)
at
java.awt.LightweightDispatcher.dispatchEvent(Container.java:3916)
at java.awt.Container.dispatchEventImpl(Container.java:2102)
at java.awt.Window.dispatchEventImpl(Window.java:2429)
at java.awt.Component.dispatchEvent(Component.java:4240)
at java.awt.EventQueue.dispatchEvent(EventQueue.java:599)
at
java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:273)
at
java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:183)
at
java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:173)
at
java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:168)
at
java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:160)
at
java.awt.EventDispatchThread.run(EventDispatchThread.java:121)
I am more than happy and thankful for any advice!
Christian Rengstl M.A.
Klinik und Poliklinik für Innere Medizin II
Kardiologie - Forschung
Universitätsklinikum Regensburg
B3 1.388
Franz-Josef-Strauss-Allee 11
93053 Regensburg
Tel.: +49-941-944-7230
Just to clarify: there is of course a primary key and it's included in
the select.
Anyway, I'm still happy for any advice ;-)
Christian Rengstl M.A.
Klinik und Poliklinik für Innere Medizin II
Kardiologie - Forschung
Universitätsklinikum Regensburg
B3 1.388
Franz-Josef-Strauss-Allee 11
93053 Regensburg
Tel.: +49-941-944-7230
>>> On Wed, Oct 10, 2007 at 2:26 PM, in message
<470CE177.90AD.0080.0@klinik.uni-regensburg.de>, "Christian Rengstl"
<Christian.Rengstl@klinik.uni-regensburg.de> wrote:
> Hi everyone,
>
> I have a query including an inner join, which I execute to obtain a
> ResultSet, but when I execute the following piece of code I get an
error
> message saying that it could not update the table because it could
not
> find the primary key for a table.
> rs.first();
> rs.updateString("gene", "TestGen");
> rs.updateRow();
>
> Here is my query:
> SELECT gene_entropy.gene, gene_entropy.entropy,
gene_entropy.iteration,
> snp_entropy.snp_id
> FROM clustering.gene_entropy INNER JOIN clustering.snp_entropy
> USING(gene) limit 5;
>
> And here is the stacktrace:
> org.postgresql.util.PSQLException: Für die Tebelle
> (clustering.gene_entropy konnte kein Primärschlüssel gefunden
werden.
> at
>
org.postgresql.jdbc2.AbstractJdbc2ResultSet.isUpdateable(AbstractJdbc2Result
> Set.java:1547)
> at
>
org.postgresql.jdbc2.AbstractJdbc2ResultSet.checkUpdateable(AbstractJdbc2Res
> ultSet.java:2550)
> at
>
org.postgresql.jdbc2.AbstractJdbc2ResultSet.updateValue(AbstractJdbc2ResultS
> et.java:2796)
> at
>
org.postgresql.jdbc2.AbstractJdbc2ResultSet.updateString(AbstractJdbc2Result
> Set.java:1309)
> at
>
org.postgresql.jdbc2.AbstractJdbc2ResultSet.updateString(AbstractJdbc2Result
> Set.java:1394)
> at
>
myapp.ui.event.DBTableFrameListener.actionPerformed(DBTableFrameListener.jav
> a:406)
> at
>
javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1995)
> at
>
javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2318)
> at
>
javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:3
> 87)
> at
>
javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:242)
> at
>
javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener
> .java:236)
> at
>
java.awt.AWTEventMulticaster.mouseReleased(AWTEventMulticaster.java:272)
> at java.awt.Component.processMouseEvent(Component.java:6038)
> at
> javax.swing.JComponent.processMouseEvent(JComponent.java:3260)
> at java.awt.Component.processEvent(Component.java:5803)
> at java.awt.Container.processEvent(Container.java:2058)
> at java.awt.Component.dispatchEventImpl(Component.java:4410)
> at java.awt.Container.dispatchEventImpl(Container.java:2116)
> at java.awt.Component.dispatchEvent(Component.java:4240)
> at
>
java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4322)
> at
>
java.awt.LightweightDispatcher.processMouseEvent(Container.java:3986)
> at
> java.awt.LightweightDispatcher.dispatchEvent(Container.java:3916)
> at java.awt.Container.dispatchEventImpl(Container.java:2102)
> at java.awt.Window.dispatchEventImpl(Window.java:2429)
> at java.awt.Component.dispatchEvent(Component.java:4240)
> at java.awt.EventQueue.dispatchEvent(EventQueue.java:599)
> at
>
java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java
> :273)
> at
>
java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:18
> 3)
> at
>
java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java
> :173)
> at
>
java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:168)
> at
>
java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:160)
> at
> java.awt.EventDispatchThread.run(EventDispatchThread.java:121)
>
> I am more than happy and thankful for any advice!
>
>
>
> Christian Rengstl M.A.
> Klinik und Poliklinik für Innere Medizin II
> Kardiologie - Forschung
> Universitätsklinikum Regensburg
> B3 1.388
> Franz-Josef-Strauss-Allee 11
> 93053 Regensburg
> Tel.: +49-941-944-7230
>
>
>
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do
not
> match
Only simple result sets coming from a single table, with no joins, are
updateable. Granted, the error message is misleading. The driver has to
parse the query, to extract the name of the table to update or throw an
error if there's more than one table involved. In this case, it doesn't
understand the INNER JOIN, and therefore you get the misleading error
message instead of this: "ResultSet is not updateable. The query that
generated this result set must select only one table, and must select
all primary keys from that table. See the JDBC 2.1 API Specification,
section 5.6 for more details."
Christian Rengstl wrote:
> Hi everyone,
>
> I have a query including an inner join, which I execute to obtain a
> ResultSet, but when I execute the following piece of code I get an error
> message saying that it could not update the table because it could not
> find the primary key for a table.
> rs.first();
> rs.updateString("gene", "TestGen");
> rs.updateRow();
>
> Here is my query:
> SELECT gene_entropy.gene, gene_entropy.entropy, gene_entropy.iteration,
> snp_entropy.snp_id
> FROM clustering.gene_entropy INNER JOIN clustering.snp_entropy
> USING(gene) limit 5;
>
> And here is the stacktrace:
> org.postgresql.util.PSQLException: Für die Tebelle
> (clustering.gene_entropy konnte kein Primärschlüssel gefunden werden.
> at
> org.postgresql.jdbc2.AbstractJdbc2ResultSet.isUpdateable(AbstractJdbc2ResultSet.java:1547)
> at
> org.postgresql.jdbc2.AbstractJdbc2ResultSet.checkUpdateable(AbstractJdbc2ResultSet.java:2550)
> at
> org.postgresql.jdbc2.AbstractJdbc2ResultSet.updateValue(AbstractJdbc2ResultSet.java:2796)
> at
> org.postgresql.jdbc2.AbstractJdbc2ResultSet.updateString(AbstractJdbc2ResultSet.java:1309)
> at
> org.postgresql.jdbc2.AbstractJdbc2ResultSet.updateString(AbstractJdbc2ResultSet.java:1394)
> at
> myapp.ui.event.DBTableFrameListener.actionPerformed(DBTableFrameListener.java:406)
> at
> javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1995)
> at
> javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2318)
> at
> javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:387)
> at
> javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:242)
> at
> javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:236)
> at
> java.awt.AWTEventMulticaster.mouseReleased(AWTEventMulticaster.java:272)
> at java.awt.Component.processMouseEvent(Component.java:6038)
> at
> javax.swing.JComponent.processMouseEvent(JComponent.java:3260)
> at java.awt.Component.processEvent(Component.java:5803)
> at java.awt.Container.processEvent(Container.java:2058)
> at java.awt.Component.dispatchEventImpl(Component.java:4410)
> at java.awt.Container.dispatchEventImpl(Container.java:2116)
> at java.awt.Component.dispatchEvent(Component.java:4240)
> at
> java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4322)
> at
> java.awt.LightweightDispatcher.processMouseEvent(Container.java:3986)
> at
> java.awt.LightweightDispatcher.dispatchEvent(Container.java:3916)
> at java.awt.Container.dispatchEventImpl(Container.java:2102)
> at java.awt.Window.dispatchEventImpl(Window.java:2429)
> at java.awt.Component.dispatchEvent(Component.java:4240)
> at java.awt.EventQueue.dispatchEvent(EventQueue.java:599)
> at
> java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:273)
> at
> java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:183)
> at
> java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:173)
> at
> java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:168)
> at
> java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:160)
> at
> java.awt.EventDispatchThread.run(EventDispatchThread.java:121)
>
> I am more than happy and thankful for any advice!
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
And I suppose there is no reasonable workaround, is there?
Christian Rengstl M.A.
Klinik und Poliklinik für Innere Medizin II
Kardiologie - Forschung
Universitätsklinikum Regensburg
B3 1.388
Franz-Josef-Strauss-Allee 11
93053 Regensburg
Tel.: +49-941-944-7230
>>> On Wed, Oct 10, 2007 at 2:53 PM, in message
<470CCB67.80106@enterprisedb.com>,
"Heikki Linnakangas" <heikki@enterprisedb.com> wrote:
> Only simple result sets coming from a single table, with no joins,
are
> updateable. Granted, the error message is misleading. The driver has
to
> parse the query, to extract the name of the table to update or throw
an
> error if there's more than one table involved. In this case, it
doesn't
> understand the INNER JOIN, and therefore you get the misleading
error
> message instead of this: "ResultSet is not updateable. The query
that
> generated this result set must select only one table, and must
select
> all primary keys from that table. See the JDBC 2.1 API
Specification,
> section 5.6 for more details."
>
> Christian Rengstl wrote:
>> Hi everyone,
>>
>> I have a query including an inner join, which I execute to obtain a
>> ResultSet, but when I execute the following piece of code I get an
error
>> message saying that it could not update the table because it could
not
>> find the primary key for a table.
>> rs.first();
>> rs.updateString("gene", "TestGen");
>> rs.updateRow();
>>
>> Here is my query:
>> SELECT gene_entropy.gene, gene_entropy.entropy,
gene_entropy.iteration,
>> snp_entropy.snp_id
>> FROM clustering.gene_entropy INNER JOIN clustering.snp_entropy
>> USING(gene) limit 5;
>>
>> And here is the stacktrace:
>> org.postgresql.util.PSQLException: Für die Tebelle
>> (clustering.gene_entropy konnte kein Primärschlüssel gefunden
werden.
>> at
>>
>
org.postgresql.jdbc2.AbstractJdbc2ResultSet.isUpdateable(AbstractJdbc2ResultS
> et.java:1547)
>> at
>>
>
org.postgresql.jdbc2.AbstractJdbc2ResultSet.checkUpdateable(AbstractJdbc2Resu
> ltSet.java:2550)
>> at
>>
>
org.postgresql.jdbc2.AbstractJdbc2ResultSet.updateValue(AbstractJdbc2ResultSe
> t.java:2796)
>> at
>>
>
org.postgresql.jdbc2.AbstractJdbc2ResultSet.updateString(AbstractJdbc2ResultS
> et.java:1309)
>> at
>>
>
org.postgresql.jdbc2.AbstractJdbc2ResultSet.updateString(AbstractJdbc2ResultS
> et.java:1394)
>> at
>>
>
myapp.ui.event.DBTableFrameListener.actionPerformed(DBTableFrameListener.java
> :406)
>> at
>>
javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1995)
>> at
>>
javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2318)
>> at
>>
>
javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:38
> 7)
>> at
>>
javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:242)
>> at
>>
>
javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.
> java:236)
>> at
>>
java.awt.AWTEventMulticaster.mouseReleased(AWTEventMulticaster.java:272)
>> at
java.awt.Component.processMouseEvent(Component.java:6038)
>> at
>> javax.swing.JComponent.processMouseEvent(JComponent.java:3260)
>> at java.awt.Component.processEvent(Component.java:5803)
>> at java.awt.Container.processEvent(Container.java:2058)
>> at
java.awt.Component.dispatchEventImpl(Component.java:4410)
>> at
java.awt.Container.dispatchEventImpl(Container.java:2116)
>> at java.awt.Component.dispatchEvent(Component.java:4240)
>> at
>>
java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4322)
>> at
>>
java.awt.LightweightDispatcher.processMouseEvent(Container.java:3986)
>> at
>> java.awt.LightweightDispatcher.dispatchEvent(Container.java:3916)
>> at
java.awt.Container.dispatchEventImpl(Container.java:2102)
>> at java.awt.Window.dispatchEventImpl(Window.java:2429)
>> at java.awt.Component.dispatchEvent(Component.java:4240)
>> at java.awt.EventQueue.dispatchEvent(EventQueue.java:599)
>> at
>>
>
java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:
> 273)
>> at
>>
>
java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:183
> )
>> at
>>
>
java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:
> 173)
>> at
>>
java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:168)
>> at
>>
java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:160)
>> at
>> java.awt.EventDispatchThread.run(EventDispatchThread.java:121)
>>
>> I am more than happy and thankful for any advice!
>
>
> --
> Heikki Linnakangas
> EnterpriseDB http://www.enterprisedb.com
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do
not
> match
Christian Rengstl wrote: > And I suppose there is no reasonable workaround, is there? Well, you can always run a good old UPDATE statement yourself. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
Well, that's not a big problem. It's just because in my app i am offering the option to execute stored queries and display and update the fields using a JTable. The problem is just that some users said it would be nice if a joined query with fields from two or more tables would be updateable as well. Christian Rengstl M.A. Klinik und Poliklinik für Innere Medizin II Kardiologie - Forschung Universitätsklinikum Regensburg B3 1.388 Franz-Josef-Strauss-Allee 11 93053 Regensburg Tel.: +49-941-944-7230 >>> On Wed, Oct 10, 2007 at 3:02 PM, in message <470CCD6A.4090008@enterprisedb.com>, "Heikki Linnakangas" <heikki@enterprisedb.com> wrote: > Christian Rengstl wrote: >> And I suppose there is no reasonable workaround, is there? > > Well, you can always run a good old UPDATE statement yourself. > > -- > Heikki Linnakangas > EnterpriseDB http://www.enterprisedb.com > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend
Hi. I'd create an updatable server-side view and issue select against it. Not sure if primary key information will be propagated, but you can try. Christian Rengstl wrote: > And I suppose there is no reasonable workaround, is there? > > > >>>> On Wed, Oct 10, 2007 at 2:53 PM, in message >>>> > <470CCB67.80106@enterprisedb.com>, > "Heikki Linnakangas" <heikki@enterprisedb.com> wrote: > >> Only simple result sets coming from a single table, with no joins, >> > are > >> updateable. Granted, the error message is misleading. The driver has >> > to > >> parse the query, to extract the name of the table to update or throw >> > an > >> error if there's more than one table involved. In this case, it >> > doesn't > >> understand the INNER JOIN, and therefore you get the misleading >> > error > >> message instead of this: "ResultSet is not updateable. The query >> > that > >> generated this result set must select only one table, and must >> > select > >> all primary keys from that table. See the JDBC 2.1 API >> > Specification, > >> section 5.6 for more details." >>
Wednesday, October 10, 2007, 5:39:47 AM, you wrote: > Hi. > I'd create an updatable server-side view and issue select against it. > Not sure if primary key information will be propagated, but you can try. I've found that a column in a view named "oid" is automatically assumed to be a primary key. (You can see the special case in the JDBC code.) So I include such a column in an updatable view. -- Best regards, Andrew mailto:drlaz@attglobal.net