Re: Problem with updateRow() -- Revisited - Mailing list pgsql-jdbc

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>