Can't update rows in tables qualified with schema names
От | Rich Cullingford |
---|---|
Тема | Can't update rows in tables qualified with schema names |
Дата | |
Msg-id | 3E5D2478.1020008@sysd.com обсуждение исходный текст |
Ответы |
Re: Can't update rows in tables qualified with schema names
|
Список | 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@sysd.com
В списке pgsql-jdbc по дате отправления: