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

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