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

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