Thread: Re: Problem with updateRow() -- Revisited

Re: Problem with updateRow() -- Revisited

From
"David Hooker"
Date:
My table names are all uppercase.  Is that a problem?

-----Original Message-----
From: Barry Lind [mailto:blind@xythos.com]
Sent: Tuesday, March 25, 2003 12:45 PM
To: David Hooker
Cc: Dave Cramer; pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] Problem with updateRow() -- Revisited


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


Re: Problem with updateRow() -- Revisited

From
Dave Cramer
Date:
David,

It is for the test case, but it shouldn't be for the updateRow ?

can you rerun the test case and call toLower on the tableName

Dave
On Tue, 2003-03-25 at 14:55, David Hooker wrote:
> My table names are all uppercase.  Is that a problem?
>
> -----Original Message-----
> From: Barry Lind [mailto:blind@xythos.com]
> Sent: Tuesday, March 25, 2003 12:45 PM
> To: David Hooker
> Cc: Dave Cramer; pgsql-jdbc@postgresql.org
> Subject: Re: [JDBC] Problem with updateRow() -- Revisited
>
>
> 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
>
--
Dave Cramer <Dave@micro-automation.net>