Thread: Can't update rows in tables qualified with schema names
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
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@sysd.com
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
----- Original Message -----
From: "Barry Lind" <blind@xythos.com>
To: "Rich Cullingford" <rculling@sysd.com>
Cc: "pgsql-jdbc" <pgsql-jdbc@postgresql.org>
Sent: Friday, February 28, 2003 3:59 AM
Subject: Re: [JDBC] 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?
Here is my crack at it:
Index: AbstractJdbc2ResultSet.java
===================================================================
RCS file:
/projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc2/Abst
ractJdbc2ResultSet.java,v
retrieving revision 1.14
diff -c -r1.14 AbstractJdbc2ResultSet.java
*** AbstractJdbc2ResultSet.java 2003/02/27 05:56:27 1.14
--- AbstractJdbc2ResultSet.java 2003/02/28 10:25:25
***************
*** 1321,1332 ****
// if the user has supplied a quoted table name
// remove the quotes, but preserve the case.
// otherwise fold to lower case.
! 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);
--- 1321,1327 ----
// if the user has supplied a quoted table name
// remove the quotes, but preserve the case.
// otherwise fold to lower case.
! String quotelessTableName = quotelessTableName(tableName);
java.sql.ResultSet rs = ((java.sql.Connection)
connection).getMetaData().getPrimaryKeys("", "", quotelessTableName);
***************
*** 1361,1368 ****
return updateable;
}
-
public void parseQuery()
{
String[] l_sqlFragments =
((AbstractJdbc2Statement)statement).getSqlFragments();
--- 1356,1392 ----
return updateable;
}
+ /**
+ * Returns unquoted table name, stripped of optional schema qualifier.
+ * If it was unquoted then the name is folded to lowercase. Test
cases:<br>
+ * Table: table<br>
+ * "Table": Table<br>
+ * Schema.Table: table<br>
+ * "Schema"."Table": Table<br>
+ * "Schema"."Dot.Table": Dot.Table
+ */
+ private static String quotelessTableName(String fullname) {
+ String result = null;
+ if (fullname.startsWith("\"") && fullname.endsWith("\"")) {
+ StringBuffer buf = new StringBuffer(fullname);
+ buf.deleteCharAt(buf.length() - 1); // delete trailing
quote
+ // No need to test result of lastIndexOf() due to enclosing
if.
+ result = buf.substring(buf.lastIndexOf("\"") + 1);
+ }
+ else {
+ int dot = fullname.lastIndexOf(".");
+ if (dot >= 0) {
+ result = fullname.substring(dot + 1);
+ }
+ else {
+ result = fullname;
+ }
+ result = result.toLowerCase();
+ }
+ return result;
+ }
+
public void parseQuery()
{
String[] l_sqlFragments =
((AbstractJdbc2Statement)statement).getSqlFragments();
Attachment
It is also possibly to have a quote within a quoted name. The quote is
escaped by another quote, for example
"schema""name"."tablename"
Kris Jurka
On Fri, 28 Feb 2003, Paul Sorenson wrote:
>
> ----- Original Message -----
> From: "Barry Lind" <blind@xythos.com>
> To: "Rich Cullingford" <rculling@sysd.com>
> Cc: "pgsql-jdbc" <pgsql-jdbc@postgresql.org>
> Sent: Friday, February 28, 2003 3:59 AM
> Subject: Re: [JDBC] 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?
>
> Here is my crack at it:
> Index: AbstractJdbc2ResultSet.java
> ===================================================================
> RCS file:
> /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc2/Abst
> ractJdbc2ResultSet.java,v
> retrieving revision 1.14
> diff -c -r1.14 AbstractJdbc2ResultSet.java
> *** AbstractJdbc2ResultSet.java 2003/02/27 05:56:27 1.14
> --- AbstractJdbc2ResultSet.java 2003/02/28 10:25:25
> ***************
> *** 1321,1332 ****
> // if the user has supplied a quoted table name
> // remove the quotes, but preserve the case.
> // otherwise fold to lower case.
> ! 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);
>
>
> --- 1321,1327 ----
> // if the user has supplied a quoted table name
> // remove the quotes, but preserve the case.
> // otherwise fold to lower case.
> ! String quotelessTableName = quotelessTableName(tableName);
> java.sql.ResultSet rs = ((java.sql.Connection)
> connection).getMetaData().getPrimaryKeys("", "", quotelessTableName);
>
>
> ***************
> *** 1361,1368 ****
>
> return updateable;
> }
> -
>
> public void parseQuery()
> {
> String[] l_sqlFragments =
> ((AbstractJdbc2Statement)statement).getSqlFragments();
> --- 1356,1392 ----
>
> return updateable;
> }
>
> + /**
> + * Returns unquoted table name, stripped of optional schema qualifier.
> + * If it was unquoted then the name is folded to lowercase. Test
> cases:<br>
> + * Table: table<br>
> + * "Table": Table<br>
> + * Schema.Table: table<br>
> + * "Schema"."Table": Table<br>
> + * "Schema"."Dot.Table": Dot.Table
> + */
> + private static String quotelessTableName(String fullname) {
> + String result = null;
> + if (fullname.startsWith("\"") && fullname.endsWith("\"")) {
> + StringBuffer buf = new StringBuffer(fullname);
> + buf.deleteCharAt(buf.length() - 1); // delete trailing
> quote
> + // No need to test result of lastIndexOf() due to enclosing
> if.
> + result = buf.substring(buf.lastIndexOf("\"") + 1);
> + }
> + else {
> + int dot = fullname.lastIndexOf(".");
> + if (dot >= 0) {
> + result = fullname.substring(dot + 1);
> + }
> + else {
> + result = fullname;
> + }
> + result = result.toLowerCase();
> + }
> + return result;
> + }
> +
> public void parseQuery()
> {
> String[] l_sqlFragments =
> ((AbstractJdbc2Statement)statement).getSqlFragments();
>
>
Try #2 (and 20 pushups)... I collapsed my spaces to tabs too - hope you guys have yours set to 4. ----- Original Message ----- From: "Barry Lind" <blind@xythos.com> To: "Paul Sorenson" <pauls@classware.com.au> Sent: Saturday, March 01, 2003 3:57 AM Subject: Re: [JDBC] Can't update rows in tables qualified with schema names > Paul, > > Thanks for the patch. There are a couple of issues with it that I would > like to mention: > > 1) It doesn't correctly handle this case: schema."Dot.Table" you > return: Table" > > 2) It doesn't completely fix the original bug report. The proper fix > would be to get both the schema and tablename from the input and pass > both to the getPrimaryKeys() method. Your patch just ignores the schema > which is incorrect if you happen to have multiple tables with the same > name in different schemas. > > thanks, > --Barry