Thread: Re: Problem with updateRow() -- Revisited
I added this code to my program:
ResultSet r = conn.getMetaData().getPrimaryKeys("", "",
tableName);
System.out.println(r.wasNull() ? "Was Null" : "Not
Null");
while (r.next())
{
System.out.print(r.getString(1) + "\t");
System.out.print(r.getString(2) + "\t");
System.out.print(r.getString(3) + "\t");
System.out.print(r.getString(4) + "\t");
System.out.print(r.getShort(5) + "\t");
System.out.print(r.getString(6) + "\t");
System.out.println("\r\n-----------");
}
All it printed was "Not Null". It didn't print anything else, so I
figure it didn't go into the loop.
-----Original Message-----
From: Dave Cramer [mailto:Dave@micro-automation.net]
Sent: Tuesday, March 25, 2003 11:45 AM
To: David Hooker
Cc: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] Problem with updateRow() -- Revisited
David,
I had a look at the source, and it does a
"getPrimaryKeys("","",tablename);
can you get me the results of this on your table?
The above code is obviously flawed, now that we have schema's so we'll
fix that at the same time.
Dave
On Tue, 2003-03-25 at 12:24, David Hooker wrote:
> Geez... I'm *still* having this problem. So I downloaded
devpgjdbc2.jar
> again today, and it's *still* there.
>
> My select looks like this now:
> String sql = "SELECT NAME, CONTEXT, FILENAME, BOUNDARY FROM " +
> tableName + " WHERE NAME = '" + name + "' AND CONTEXT = '" + context +
> "'";
>
> Everything else in the below emails is the same.
>
> This time, I got a stack trace:
>
> java.sql.SQLException: No Primary Keys
> at
>
org.postgresql.jdbc2.AbstractJdbc2ResultSet.isUpdateable(AbstractJdbc2Re
> sultSet.java:1356)
> at
>
org.postgresql.jdbc2.AbstractJdbc2ResultSet.updateValue(AbstractJdbc2Res
> ultSet.java:1455)
> at
>
org.postgresql.jdbc2.AbstractJdbc2ResultSet.updateString(AbstractJdbc2Re
> sultSet.java:1099)
> at
>
org.postgresql.jdbc2.AbstractJdbc2ResultSet.updateString(AbstractJdbc2Re
> sultSet.java:1188)
> at
>
com.a4networks.db.LocalFileStorage.updateDocumentEvent(LocalFileStorage.
> java:334)
> at
> com.a4networks.server.Dispatcher.dispatch(Dispatcher.java:328)
> at
> com.a4networks.server.Dispatcher.dispatch(Dispatcher.java:171)
> at
com.a4networks.server.Dispatcher.doWork(Dispatcher.java:147)
> at com.a4networks.server.QueueWorker.run(QueueWorker.java:110)
> at
> com.a4networks.server.ThreadPool$PooledThread.run(ThreadPool.java:55)
>
> If the driver just doesn't support compound keys with updateable
result
> sets, I guess I can work around that. But this DID work for me last
> week, and this code DOES work with Oracle and MSSQL.
>
> -----Original Message-----
> From: David Hooker
> Sent: Thursday, March 20, 2003 11:47 AM
> To: David Hooker; Dave Cramer
> Cc: pgsql-jdbc@postgresql.org
> Subject: Re: [JDBC] Problem with updateRow()
>
>
> Update:
>
> I updated to the development driver devpgjdbc2.jar, and the problem
> seems to have gone away.
>
> -----Original Message-----
> From: David Hooker
> Sent: Thursday, March 20, 2003 11:08 AM
> To: Dave Cramer
> Cc: pgsql-jdbc@postgresql.org
> Subject: Re: [JDBC] Problem with updateRow()
>
>
> Adding columns NAME and CONTEXT (the two parts of the key) did not
help.
>
> Adding the oid column makes it work.
>
> I have a problem with this, however, since this code has to also run
> against Oracle and MSSQL.
>
> Is there another answer?
>
> -----Original Message-----
> From: Dave Cramer [mailto:Dave@micro-automation.net]
> Sent: Wednesday, March 19, 2003 7:19 PM
> To: David Hooker
> Cc: pgsql-jdbc@postgresql.org
> Subject: Re: [JDBC] Problem with updateRow()
>
>
> David,
>
> The updateable result set is really only supported for very simple
> tables, and keys, I suppose it could deal with composite keys, but
this
> isn't the intention AFAIK. At the very least you would have to select
> both columns of the key to have it work though.
>
> There is a simple solution for you however, add the oid to your
select.
>
> ie select oid, filename, ...
>
> and let me know if it works.
>
> Dave
> On Wed, 2003-03-19 at 19:58, David Hooker wrote:
> > Oh, I forgot to mention that I AM creating my Statement correctly I
> > think:
> >
> > Statement stmt =
> > conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
> > ResultSet.CONCUR_UPDATABLE);
> >
> > -----Original Message-----
> > From: David Hooker
> > Sent: Wednesday, March 19, 2003 6:55 PM
> > To: pgsql-jdbc@postgresql.org
> > Subject: [JDBC] Problem with updateRow()
> >
> >
> > I'm using PostgreSQL 7.3.1 server, and the pg73jdbc2.jar file from
the
> > website (file dated 2/13/2003 on my machine).
> >
> > I have in my code a select statement like this:
> >
> > String sql = "SELECT FILENAME, BOUNDARY FROM " + tableName + "
> > WHERE NAME = '" + name + "' AND CONTEXT = '" + context + "'";
> > logger.finest("SQL: " + sql);
> > ResultSet result = stmt.executeQuery(sql);
> >
> > Later in my code I have this:
> >
> > while (result.next())
> > {
> > // ...
> > result.updateString("BOUNDARY", event.getBoundary());
> > result.updateRow();
> > updated = true;
> > }
> >
> > Here's the error I get:
> >
> > java.sql.SQLException: No Primary Keys
> >
> > Here's what the tables look like in psql:
> >
> > simpletest=# \d lfs_mappings_559
> > Table "lfs_mappings_559"
> > Attribute | Type | Modifier
> > ----------------+------------------------+----------
> > name | character varying(40) | not null
> > context | character varying(80) | not null
> > filename | character varying(300) | not null
> > boundary | character varying(50) |
> > insertion_time | real | not null
> > can_purge | character varying(8) |
> > Index: lfs_mappings_559_pkey
> >
> > simpletest=# \d lfs_mappings_559_pkey
> > Index "lfs_mappings_559_pkey"
> > Attribute | Type
> > -----------+-----------------------
> > name | character varying(40)
> > context | character varying(80)
> > unique btree (primary key)
> >
> > simpletest=#
> >
> > Why is this happening? Are updateable resultSets not supported?
> >
> >
>
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
--
Dave Cramer <Dave@micro-automation.net>
David,
What value are you passing for tableName? You are passing the value as
all lower case, correct? Postgres generally expects all object names
folded to lower case.
--Barry
David Hooker wrote:
> I added this code to my program:
>
> ResultSet r = conn.getMetaData().getPrimaryKeys("", "",
> tableName);
> System.out.println(r.wasNull() ? "Was Null" : "Not
> Null");
> while (r.next())
> {
> System.out.print(r.getString(1) + "\t");
> System.out.print(r.getString(2) + "\t");
> System.out.print(r.getString(3) + "\t");
> System.out.print(r.getString(4) + "\t");
> System.out.print(r.getShort(5) + "\t");
> System.out.print(r.getString(6) + "\t");
> System.out.println("\r\n-----------");
> }
>
> All it printed was "Not Null". It didn't print anything else, so I
> figure it didn't go into the loop.
>
> -----Original Message-----
> From: Dave Cramer [mailto:Dave@micro-automation.net]
> Sent: Tuesday, March 25, 2003 11:45 AM
> To: David Hooker
> Cc: pgsql-jdbc@postgresql.org
> Subject: Re: [JDBC] Problem with updateRow() -- Revisited
>
>
> David,
>
> I had a look at the source, and it does a
> "getPrimaryKeys("","",tablename);
>
> can you get me the results of this on your table?
>
> The above code is obviously flawed, now that we have schema's so we'll
> fix that at the same time.
>
> Dave
> On Tue, 2003-03-25 at 12:24, David Hooker wrote:
>
>>Geez... I'm *still* having this problem. So I downloaded
>
> devpgjdbc2.jar
>
>>again today, and it's *still* there.
>>
>>My select looks like this now:
>> String sql = "SELECT NAME, CONTEXT, FILENAME, BOUNDARY FROM " +
>>tableName + " WHERE NAME = '" + name + "' AND CONTEXT = '" + context +
>>"'";
>>
>>Everything else in the below emails is the same.
>>
>>This time, I got a stack trace:
>>
>>java.sql.SQLException: No Primary Keys
>> at
>>
>
> org.postgresql.jdbc2.AbstractJdbc2ResultSet.isUpdateable(AbstractJdbc2Re
>
>>sultSet.java:1356)
>> at
>>
>
> org.postgresql.jdbc2.AbstractJdbc2ResultSet.updateValue(AbstractJdbc2Res
>
>>ultSet.java:1455)
>> at
>>
>
> org.postgresql.jdbc2.AbstractJdbc2ResultSet.updateString(AbstractJdbc2Re
>
>>sultSet.java:1099)
>> at
>>
>
> org.postgresql.jdbc2.AbstractJdbc2ResultSet.updateString(AbstractJdbc2Re
>
>>sultSet.java:1188)
>> at
>>
>
> com.a4networks.db.LocalFileStorage.updateDocumentEvent(LocalFileStorage.
>
>>java:334)
>> at
>>com.a4networks.server.Dispatcher.dispatch(Dispatcher.java:328)
>> at
>>com.a4networks.server.Dispatcher.dispatch(Dispatcher.java:171)
>> at
>
> com.a4networks.server.Dispatcher.doWork(Dispatcher.java:147)
>
>> at com.a4networks.server.QueueWorker.run(QueueWorker.java:110)
>> at
>>com.a4networks.server.ThreadPool$PooledThread.run(ThreadPool.java:55)
>>
>>If the driver just doesn't support compound keys with updateable
>
> result
>
>>sets, I guess I can work around that. But this DID work for me last
>>week, and this code DOES work with Oracle and MSSQL.
>>
>>-----Original Message-----
>>From: David Hooker
>>Sent: Thursday, March 20, 2003 11:47 AM
>>To: David Hooker; Dave Cramer
>>Cc: pgsql-jdbc@postgresql.org
>>Subject: Re: [JDBC] Problem with updateRow()
>>
>>
>>Update:
>>
>>I updated to the development driver devpgjdbc2.jar, and the problem
>>seems to have gone away.
>>
>>-----Original Message-----
>>From: David Hooker
>>Sent: Thursday, March 20, 2003 11:08 AM
>>To: Dave Cramer
>>Cc: pgsql-jdbc@postgresql.org
>>Subject: Re: [JDBC] Problem with updateRow()
>>
>>
>>Adding columns NAME and CONTEXT (the two parts of the key) did not
>
> help.
>
>>Adding the oid column makes it work.
>>
>>I have a problem with this, however, since this code has to also run
>>against Oracle and MSSQL.
>>
>>Is there another answer?
>>
>>-----Original Message-----
>>From: Dave Cramer [mailto:Dave@micro-automation.net]
>>Sent: Wednesday, March 19, 2003 7:19 PM
>>To: David Hooker
>>Cc: pgsql-jdbc@postgresql.org
>>Subject: Re: [JDBC] Problem with updateRow()
>>
>>
>>David,
>>
>>The updateable result set is really only supported for very simple
>>tables, and keys, I suppose it could deal with composite keys, but
>
> this
>
>>isn't the intention AFAIK. At the very least you would have to select
>>both columns of the key to have it work though.
>>
>>There is a simple solution for you however, add the oid to your
>
> select.
>
>>ie select oid, filename, ...
>>
>>and let me know if it works.
>>
>>Dave
>>On Wed, 2003-03-19 at 19:58, David Hooker wrote:
>>
>>>Oh, I forgot to mention that I AM creating my Statement correctly I
>>>think:
>>>
>>> Statement stmt =
>>>conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
>>>ResultSet.CONCUR_UPDATABLE);
>>>
>>>-----Original Message-----
>>>From: David Hooker
>>>Sent: Wednesday, March 19, 2003 6:55 PM
>>>To: pgsql-jdbc@postgresql.org
>>>Subject: [JDBC] Problem with updateRow()
>>>
>>>
>>>I'm using PostgreSQL 7.3.1 server, and the pg73jdbc2.jar file from
>
> the
>
>>>website (file dated 2/13/2003 on my machine).
>>>
>>>I have in my code a select statement like this:
>>>
>>> String sql = "SELECT FILENAME, BOUNDARY FROM " + tableName + "
>>>WHERE NAME = '" + name + "' AND CONTEXT = '" + context + "'";
>>> logger.finest("SQL: " + sql);
>>> ResultSet result = stmt.executeQuery(sql);
>>>
>>>Later in my code I have this:
>>>
>>> while (result.next())
>>> {
>>> // ...
>>> result.updateString("BOUNDARY", event.getBoundary());
>>> result.updateRow();
>>> updated = true;
>>> }
>>>
>>>Here's the error I get:
>>>
>>> java.sql.SQLException: No Primary Keys
>>>
>>>Here's what the tables look like in psql:
>>>
>>> simpletest=# \d lfs_mappings_559
>>> Table "lfs_mappings_559"
>>> Attribute | Type | Modifier
>>> ----------------+------------------------+----------
>>> name | character varying(40) | not null
>>> context | character varying(80) | not null
>>> filename | character varying(300) | not null
>>> boundary | character varying(50) |
>>> insertion_time | real | not null
>>> can_purge | character varying(8) |
>>> Index: lfs_mappings_559_pkey
>>>
>>> simpletest=# \d lfs_mappings_559_pkey
>>> Index "lfs_mappings_559_pkey"
>>> Attribute | Type
>>> -----------+-----------------------
>>> name | character varying(40)
>>> context | character varying(80)
>>> unique btree (primary key)
>>>
>>> simpletest=#
>>>
>>>Why is this happening? Are updateable resultSets not supported?
>>>
>>>
>>
>>
>>---------------------------(end of
>
> broadcast)---------------------------
>
>>TIP 3: if posting/reading through Usenet, please send an appropriate
>>subscribe-nomail command to majordomo@postgresql.org so that your
>>message can get through to the mailing list cleanly
David,
I just tried to reproduce the results with:
create table simpletest ( name char varying(40), context char
varying(80), insertion_time real, primary key( name, context)) ;
and I get the primary keys??
Dave
On Tue, 2003-03-25 at 13:03, David Hooker wrote:
> I added this code to my program:
>
> ResultSet r = conn.getMetaData().getPrimaryKeys("", "",
> tableName);
> System.out.println(r.wasNull() ? "Was Null" : "Not
> Null");
> while (r.next())
> {
> System.out.print(r.getString(1) + "\t");
> System.out.print(r.getString(2) + "\t");
> System.out.print(r.getString(3) + "\t");
> System.out.print(r.getString(4) + "\t");
> System.out.print(r.getShort(5) + "\t");
> System.out.print(r.getString(6) + "\t");
> System.out.println("\r\n-----------");
> }
>
> All it printed was "Not Null". It didn't print anything else, so I
> figure it didn't go into the loop.
>
> -----Original Message-----
> From: Dave Cramer [mailto:Dave@micro-automation.net]
> Sent: Tuesday, March 25, 2003 11:45 AM
> To: David Hooker
> Cc: pgsql-jdbc@postgresql.org
> Subject: Re: [JDBC] Problem with updateRow() -- Revisited
>
>
> David,
>
> I had a look at the source, and it does a
> "getPrimaryKeys("","",tablename);
>
> can you get me the results of this on your table?
>
> The above code is obviously flawed, now that we have schema's so we'll
> fix that at the same time.
>
> Dave
> On Tue, 2003-03-25 at 12:24, David Hooker wrote:
> > Geez... I'm *still* having this problem. So I downloaded
> devpgjdbc2.jar
> > again today, and it's *still* there.
> >
> > My select looks like this now:
> > String sql = "SELECT NAME, CONTEXT, FILENAME, BOUNDARY FROM " +
> > tableName + " WHERE NAME = '" + name + "' AND CONTEXT = '" + context +
> > "'";
> >
> > Everything else in the below emails is the same.
> >
> > This time, I got a stack trace:
> >
> > java.sql.SQLException: No Primary Keys
> > at
> >
> org.postgresql.jdbc2.AbstractJdbc2ResultSet.isUpdateable(AbstractJdbc2Re
> > sultSet.java:1356)
> > at
> >
> org.postgresql.jdbc2.AbstractJdbc2ResultSet.updateValue(AbstractJdbc2Res
> > ultSet.java:1455)
> > at
> >
> org.postgresql.jdbc2.AbstractJdbc2ResultSet.updateString(AbstractJdbc2Re
> > sultSet.java:1099)
> > at
> >
> org.postgresql.jdbc2.AbstractJdbc2ResultSet.updateString(AbstractJdbc2Re
> > sultSet.java:1188)
> > at
> >
> com.a4networks.db.LocalFileStorage.updateDocumentEvent(LocalFileStorage.
> > java:334)
> > at
> > com.a4networks.server.Dispatcher.dispatch(Dispatcher.java:328)
> > at
> > com.a4networks.server.Dispatcher.dispatch(Dispatcher.java:171)
> > at
> com.a4networks.server.Dispatcher.doWork(Dispatcher.java:147)
> > at com.a4networks.server.QueueWorker.run(QueueWorker.java:110)
> > at
> > com.a4networks.server.ThreadPool$PooledThread.run(ThreadPool.java:55)
> >
> > If the driver just doesn't support compound keys with updateable
> result
> > sets, I guess I can work around that. But this DID work for me last
> > week, and this code DOES work with Oracle and MSSQL.
> >
> > -----Original Message-----
> > From: David Hooker
> > Sent: Thursday, March 20, 2003 11:47 AM
> > To: David Hooker; Dave Cramer
> > Cc: pgsql-jdbc@postgresql.org
> > Subject: Re: [JDBC] Problem with updateRow()
> >
> >
> > Update:
> >
> > I updated to the development driver devpgjdbc2.jar, and the problem
> > seems to have gone away.
> >
> > -----Original Message-----
> > From: David Hooker
> > Sent: Thursday, March 20, 2003 11:08 AM
> > To: Dave Cramer
> > Cc: pgsql-jdbc@postgresql.org
> > Subject: Re: [JDBC] Problem with updateRow()
> >
> >
> > Adding columns NAME and CONTEXT (the two parts of the key) did not
> help.
> >
> > Adding the oid column makes it work.
> >
> > I have a problem with this, however, since this code has to also run
> > against Oracle and MSSQL.
> >
> > Is there another answer?
> >
> > -----Original Message-----
> > From: Dave Cramer [mailto:Dave@micro-automation.net]
> > Sent: Wednesday, March 19, 2003 7:19 PM
> > To: David Hooker
> > Cc: pgsql-jdbc@postgresql.org
> > Subject: Re: [JDBC] Problem with updateRow()
> >
> >
> > David,
> >
> > The updateable result set is really only supported for very simple
> > tables, and keys, I suppose it could deal with composite keys, but
> this
> > isn't the intention AFAIK. At the very least you would have to select
> > both columns of the key to have it work though.
> >
> > There is a simple solution for you however, add the oid to your
> select.
> >
> > ie select oid, filename, ...
> >
> > and let me know if it works.
> >
> > Dave
> > On Wed, 2003-03-19 at 19:58, David Hooker wrote:
> > > Oh, I forgot to mention that I AM creating my Statement correctly I
> > > think:
> > >
> > > Statement stmt =
> > > conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
> > > ResultSet.CONCUR_UPDATABLE);
> > >
> > > -----Original Message-----
> > > From: David Hooker
> > > Sent: Wednesday, March 19, 2003 6:55 PM
> > > To: pgsql-jdbc@postgresql.org
> > > Subject: [JDBC] Problem with updateRow()
> > >
> > >
> > > I'm using PostgreSQL 7.3.1 server, and the pg73jdbc2.jar file from
> the
> > > website (file dated 2/13/2003 on my machine).
> > >
> > > I have in my code a select statement like this:
> > >
> > > String sql = "SELECT FILENAME, BOUNDARY FROM " + tableName + "
> > > WHERE NAME = '" + name + "' AND CONTEXT = '" + context + "'";
> > > logger.finest("SQL: " + sql);
> > > ResultSet result = stmt.executeQuery(sql);
> > >
> > > Later in my code I have this:
> > >
> > > while (result.next())
> > > {
> > > // ...
> > > result.updateString("BOUNDARY", event.getBoundary());
> > > result.updateRow();
> > > updated = true;
> > > }
> > >
> > > Here's the error I get:
> > >
> > > java.sql.SQLException: No Primary Keys
> > >
> > > Here's what the tables look like in psql:
> > >
> > > simpletest=# \d lfs_mappings_559
> > > Table "lfs_mappings_559"
> > > Attribute | Type | Modifier
> > > ----------------+------------------------+----------
> > > name | character varying(40) | not null
> > > context | character varying(80) | not null
> > > filename | character varying(300) | not null
> > > boundary | character varying(50) |
> > > insertion_time | real | not null
> > > can_purge | character varying(8) |
> > > Index: lfs_mappings_559_pkey
> > >
> > > simpletest=# \d lfs_mappings_559_pkey
> > > Index "lfs_mappings_559_pkey"
> > > Attribute | Type
> > > -----------+-----------------------
> > > name | character varying(40)
> > > context | character varying(80)
> > > unique btree (primary key)
> > >
> > > simpletest=#
> > >
> > > Why is this happening? Are updateable resultSets not supported?
> > >
> > >
> >
> >
> > ---------------------------(end of
> broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org so that your
> > message can get through to the mailing list cleanly
--
Dave Cramer <Dave@micro-automation.net>