Re: Can't update rows in tables qualified with schema names - Mailing list pgsql-jdbc

----- 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();