Can't update rows in tables qualified with schema names

From: Rich Cullingford <rculling(at)sysd(dot)com>
To: pgsql-jdbc <pgsql-jdbc(at)postgresql(dot)org>
Subject: Can't update rows in tables qualified with schema names
Date: 2003-02-26 20:32:56
Message-ID: 3E5D2478.1020008@sysd.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

All,
As of the Feb 25 version of the .jar, the following problem existed. If
you try to update a row value through a result set that was created with
a schema-qualified table reference, the update fails with a "No Primary
Keys!" error, even though the PK exists. An update through an
unqualified table name works.

The following simple method illustrates this:

*************
Connection conn = null;
try {
DriverManager.registerDriver(new org.postgresql.Driver());
conn =
DriverManager.getConnection("jdbc:postgresql://wonder/rculling",
"rculling", "rculling");
}
catch (SQLException e) {
System.out.println(e.toString());
System.exit(1);
}
/* version 1: qualified name */
String q = "SELECT required_eventin_ulink FROM hawkeye.ts_1";
/* version 2: unqualified name */
String q = "SELECT c1 FROM tab";
Object o = null;
ResultSet rs = null;
try
{
Statement s = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
rs = s.executeQuery(q);
rs.first();
System.out.println("Row num: " + rs.getRow());
o = rs.getObject(1);
System.out.println("Old val: " + o);
rs.updateObject(1, "another value");
rs.updateRow();
}
catch (Exception e)
{
System.out.println(e.toString() + "\n" + e.getMessage());
}
finally
{
try
{
rs.first();
o = rs.getObject(1);
System.out.println("New val: " + o);
}
catch (Exception e)
{
System.out.println("failed to retrieve 'modified' value");
}
}
***************

Version 1 gives:

Row num: 1
Old val: some link
java.sql.SQLException: No Primary Keys
No Primary Keys
New val: some link

Version 2 gives:

Row num: 1
Old val: some value
New val: another value

Looking at the error stack shows that the error is coming from
AbstractJdbc2ResultSet.isUpdateable(), which is going after the table's
PK's without specifying the schema it's in. I was going to try to fix
this, but my local build of the jar file fails to include the jdbc1 and
jdbc2 packages.

It seems like just splitting the tableName at a '.' and using the prior
string as the schema would take care of this. Is this plausible? My
apologies if someone's already fixed this.

Thanks for your help,
Rich Cullingford
rculling(at)sysd(dot)com

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message Takeo Shibata 2003-02-27 00:14:58 Re: SSL for JDBC
Previous Message Michael Adler 2003-02-26 17:51:59 revised patch for COPY