Re: Can't update rows in tables qualified with schema names

Rich,

Yes this is a bug. It should be simple to fix. It is a little bit more
work than just parsing on the period. The logic needs to handle the
following cases:

schema.table
"Schema"."Table"
"Schema.name"."Table.name"

basically you need to account for the fact that the names could be
quoted or not and if quoted they could contain the period character.

The code below from org.postgresql.jdbc2.AbstractJdbc2ResultSet.java
would need to be fixed up.

String quotelessTableName;
if (tableName.startsWith("\"") && tableName.endsWith("\"")) {
quotelessTableName = tableName.substring(1,tableName.length()-1);
} else {
quotelessTableName = tableName.toLowerCase();
}
java.sql.ResultSet rs = ((java.sql.Connection)
connection).getMetaData().getPrimaryKeys("", "", quotelessTableName);

Do you, or someone else on the list want to take a stab at fixing this up?

thanks,
--Barry

Rich Cullingford wrote:
> 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
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>