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

----- Original Message -----
From: "Barry Lind" <blind(at)xythos(dot)com>
To: "Rich Cullingford" <rculling(at)sysd(dot)com>
Cc: "pgsql-jdbc" <pgsql-jdbc(at)postgresql(dot)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();