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
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 |