Thread: Help: ResultSet..insertRow() not coping with explicit oid
Hi,
I have some code which I think effectively does this:
statement = connection.createStatement(
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE));
resultSet = statement.executeQuery(
"SELECT oid, * FROM " + table.ID);
resultSet.moveToInsertRow();
resultSet.updateXXX(...);
...
resultSet.insertRow();
At which point I get the SQLException:
ERROR: Relation "character" has no column "oid"
(Sorry about the confusing table name)
I have tried both with and without updateLong()
for the oid.
This looks like a bug. If I believe the manual
then result sets are readonly but I assume the
code is ahead of the docs.
I have postgresql-7.3.1-6.src.rpm under Red Hat
8.0 and pg73jdbc3.jar.
So what is the story. Am I doing it wrong? Is this
fixed in a later release?
Help much appreciated,
Jim Wright
--
Recently completed - Child Brain Injury Trust
Admin System
http://cbitdemo.paneris.org/
Urgently seeking paid work
Java, Linux, XML and much more.
http://be.webz.cz/
Jim, can you send me a test case which demonstrates this? Dave On Mon, 2003-09-22 at 04:06, Jim Wright wrote: > Hi, > > I have some code which I think effectively does this: > > statement = connection.createStatement( > ResultSet.TYPE_SCROLL_SENSITIVE, > ResultSet.CONCUR_UPDATABLE)); > resultSet = statement.executeQuery( > "SELECT oid, * FROM " + table.ID); > resultSet.moveToInsertRow(); > resultSet.updateXXX(...); > ... > resultSet.insertRow(); > > At which point I get the SQLException: > > ERROR: Relation "character" has no column "oid" > > (Sorry about the confusing table name) > > I have tried both with and without updateLong() > for the oid. > > This looks like a bug. If I believe the manual > then result sets are readonly but I assume the > code is ahead of the docs. > > I have postgresql-7.3.1-6.src.rpm under Red Hat > 8.0 and pg73jdbc3.jar. > > So what is the story. Am I doing it wrong? Is this > fixed in a later release? > > Help much appreciated, > > Jim Wright -- Dave Cramer <Dave@micro-automation.net>
Hi Dave,
Dave Cramer wrote:
>Jim,
>
>can you send me a test case which demonstrates this?
>
>
I have edited the JDBC test suite in my cvs working directory.
I have not done a cvs update recently so note the version number.
Additional comments follow:
---
src/interfaces/jdbc/org/postgresql/test/jdbc2/UpdateableResultTest.java.~1.7.~
2002-09-11 07:38:45.000000000 +0200
+++
src/interfaces/jdbc/org/postgresql/test/jdbc2/UpdateableResultTest.java
2003-09-24 07:19:39.000000000 +0200
@@ -66,6 +66,17 @@
rs.close();
+ rs = st.executeQuery( "select oid, * from updateable");
+
+ assertNotNull( rs );
+ rs.moveToInsertRow();
+ rs.updateInt( 1, 1 );
+ rs.updateString( 2, "jim" );
+ rs.updateString( 3, "4gotenit" );
+ rs.insertRow();
+
+ rs.close();
+
rs = st.executeQuery("select id1, id, name, name1 from
updateable, second" );
try
{
My postgres installation was not built from this source:
>On Mon, 2003-09-22 at 04:06, Jim Wright wrote:
>
>
>>I have postgresql-7.3.1-6.src.rpm under Red Hat
>>8.0 and pg73jdbc3.jar.
>>
>>
so it might conceivably work for you. Ant output:
runtest:
[junit] Testsuite: org.postgresql.test.jdbc2.Jdbc2TestSuite
[junit] Tests run: 76, Failures: 1, Errors: 5, Time elapsed: 11.217 sec
[junit] Testcase:
testUpdateable(org.postgresql.test.jdbc2.UpdateableResultTest): FAILED
[junit] ERROR: Relation "updateable" has no column "oid"
[junit] junit.framework.AssertionFailedError: ERROR: Relation
"updateable" has no column "oid"
[junit] at
org.postgresql.test.jdbc2.UpdateableResultTest.testUpdateable(UpdateableResultTest.java:143)
[junit] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native
Method)
[junit] at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
[junit] at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
[junit] Testcase:
testGetDouble(org.postgresql.test.jdbc2.CallableStmtTest): Caused an
ERROR
[junit] ERROR: language "plpgsql" does not exist
[junit] java.sql.SQLException: ERROR: language "plpgsql" does not exist
[junit] at
org.postgresql.core.QueryExecutor.executeV2(QueryExecutor.java:286)
[junit] at
org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:104)
[junit] at
org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:42)
[junit] at
org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.java:437)
[junit] at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:49)
[junit] at
org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.java:275)
[junit] at
org.postgresql.test.jdbc2.CallableStmtTest.setUp(CallableStmtTest.java:29)
[snip 4 irrelevant errors similar to the last]
[junit] TEST org.postgresql.test.jdbc2.Jdbc2TestSuite FAILED
[junit] Testsuite: org.postgresql.test.jdbc2.optional.OptionalTestSuite
[junit] Tests run: 34, Failures: 0, Errors: 0, Time elapsed: 8.065 sec
[junit] Testsuite: org.postgresql.test.jdbc3.Jdbc3TestSuite
[junit] Tests run: 38, Failures: 0, Errors: 0, Time elapsed: 8.997 sec
Hope that's all OK and if this is a valid test you have my permission
to include it in the suite under the same license etc.
BTW Why is an ERROR not a FAILURE?
Regards,
Jim Wright
--
Recently completed - Child Brain Injury Trust Admin System
http://cbitdemo.paneris.org/
Urgently seeking paid work
Java, Linux, XML and much more.
http://be.webz.cz/
On Wed, 24 Sep 2003, Jim Wright wrote:
> ---
> src/interfaces/jdbc/org/postgresql/test/jdbc2/UpdateableResultTest.java.~1.7.~
> 2002-09-11 07:38:45.000000000 +0200
> +++
> src/interfaces/jdbc/org/postgresql/test/jdbc2/UpdateableResultTest.java
> 2003-09-24 07:19:39.000000000 +0200
> @@ -66,6 +66,17 @@
>
> rs.close();
>
> + rs = st.executeQuery( "select oid, * from updateable");
> +
> + assertNotNull( rs );
> + rs.moveToInsertRow();
> + rs.updateInt( 1, 1 );
> + rs.updateString( 2, "jim" );
> + rs.updateString( 3, "4gotenit" );
> + rs.insertRow();
> +
> + rs.close();
> +
> rs = st.executeQuery("select id1, id, name, name1 from
> updateable, second" );
> try
> {
>
Here the updateInt(1,1) is trying to update the oid column which is not
allowed and giving the error you see.
Kris Jurka
Kris,
Thanks for finding that, considering I'm the one that put that code in I
should have picked it up. Anyways, yes, the oid bit is a postgres hack
that will make updateable result sets work if you don't have a primary
key.
Dave.
On Wed, 2003-09-24 at 20:08, Kris Jurka wrote:
> On Wed, 24 Sep 2003, Jim Wright wrote:
> > ---
> > src/interfaces/jdbc/org/postgresql/test/jdbc2/UpdateableResultTest.java.~1.7.~
> > 2002-09-11 07:38:45.000000000 +0200
> > +++
> > src/interfaces/jdbc/org/postgresql/test/jdbc2/UpdateableResultTest.java
> > 2003-09-24 07:19:39.000000000 +0200
> > @@ -66,6 +66,17 @@
> >
> > rs.close();
> >
> > + rs = st.executeQuery( "select oid, * from updateable");
> > +
> > + assertNotNull( rs );
> > + rs.moveToInsertRow();
> > + rs.updateInt( 1, 1 );
> > + rs.updateString( 2, "jim" );
> > + rs.updateString( 3, "4gotenit" );
> > + rs.insertRow();
> > +
> > + rs.close();
> > +
> > rs = st.executeQuery("select id1, id, name, name1 from
> > updateable, second" );
> > try
> > {
> >
>
> Here the updateInt(1,1) is trying to update the oid column which is not
> allowed and giving the error you see.
>
> Kris Jurka
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>
--
Dave Cramer <Dave@micro-automation.net>
Jim,
Your test case is flawed. Your select is essentially:
select oid, id, name, notselected from updateable;
Then you are binding the values for your insert positionally at
positions 1,2,3. Which means you are binding value 1 to the oid column,
'jim' to the id column, '4gotenit' to the name column and nothing to the
notselected column. If you change your indexes to be 2,3,4 everything
works correctly. Or you could alternatively 'select *, oid from
updateable and continue binding from postion 1.
thanks,
--Barry
Jim Wright wrote:
> Hi Dave,
>
> Dave Cramer wrote:
>
>> Jim,
>>
>> can you send me a test case which demonstrates this?
>>
>>
> I have edited the JDBC test suite in my cvs working directory.
> I have not done a cvs update recently so note the version number.
> Additional comments follow:
>
> ---
> src/interfaces/jdbc/org/postgresql/test/jdbc2/UpdateableResultTest.java.~1.7.~
> 2002-09-11 07:38:45.000000000 +0200
> +++
> src/interfaces/jdbc/org/postgresql/test/jdbc2/UpdateableResultTest.java
> 2003-09-24 07:19:39.000000000 +0200
> @@ -66,6 +66,17 @@
>
> rs.close();
>
> + rs = st.executeQuery( "select oid, * from updateable");
> +
> + assertNotNull( rs );
> + rs.moveToInsertRow();
> + rs.updateInt( 1, 1 );
> + rs.updateString( 2, "jim" );
> + rs.updateString( 3, "4gotenit" );
> + rs.insertRow(); +
> + rs.close();
> +
> rs = st.executeQuery("select id1, id, name, name1 from
> updateable, second" );
> try
> {
>
> My postgres installation was not built from this source:
>
>> On Mon, 2003-09-22 at 04:06, Jim Wright wrote:
>>
>>
>>> I have postgresql-7.3.1-6.src.rpm under Red Hat 8.0 and pg73jdbc3.jar.
>>>
>
> so it might conceivably work for you. Ant output:
>
> runtest:
> [junit] Testsuite: org.postgresql.test.jdbc2.Jdbc2TestSuite
> [junit] Tests run: 76, Failures: 1, Errors: 5, Time elapsed: 11.217 sec
>
> [junit] Testcase:
> testUpdateable(org.postgresql.test.jdbc2.UpdateableResultTest): FAILED
> [junit] ERROR: Relation "updateable" has no column "oid"
> [junit] junit.framework.AssertionFailedError: ERROR: Relation
> "updateable" has no column "oid"
> [junit] at
> org.postgresql.test.jdbc2.UpdateableResultTest.testUpdateable(UpdateableResultTest.java:143)
>
> [junit] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native
> Method)
> [junit] at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
>
> [junit] at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
>
>
>
> [junit] Testcase:
> testGetDouble(org.postgresql.test.jdbc2.CallableStmtTest): Caused an
> ERROR
> [junit] ERROR: language "plpgsql" does not exist
> [junit] java.sql.SQLException: ERROR: language "plpgsql" does not exist
> [junit] at
> org.postgresql.core.QueryExecutor.executeV2(QueryExecutor.java:286)
> [junit] at
> org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:104)
> [junit] at
> org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:42)
> [junit] at
> org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.java:437)
>
> [junit] at
> org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:49)
>
> [junit] at
> org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.java:275)
>
> [junit] at
> org.postgresql.test.jdbc2.CallableStmtTest.setUp(CallableStmtTest.java:29)
>
> [snip 4 irrelevant errors similar to the last]
>
> [junit] TEST org.postgresql.test.jdbc2.Jdbc2TestSuite FAILED
> [junit] Testsuite: org.postgresql.test.jdbc2.optional.OptionalTestSuite
> [junit] Tests run: 34, Failures: 0, Errors: 0, Time elapsed: 8.065 sec
>
> [junit] Testsuite: org.postgresql.test.jdbc3.Jdbc3TestSuite
> [junit] Tests run: 38, Failures: 0, Errors: 0, Time elapsed: 8.997 sec
>
> Hope that's all OK and if this is a valid test you have my permission
> to include it in the suite under the same license etc.
>
> BTW Why is an ERROR not a FAILURE?
>
> Regards,
>
> Jim Wright
>