Re: Fixes for JDBC's getColumns() in Postgresql 7.2 & - Mailing list pgsql-jdbc

At 07:51 16.4.2002 -0400, Dave Cramer wrote:
>Panu,
>
>Well, the missing line is a no brainer and I will fix it ASAP, thanks
>for finding it. What we really need are better test cases ;(
>
>As far as the case sensitivity/insensitivity goes, I think I want to
>think about that. What about making it a configurable parameter? I'm
>sure there are good reasons for making it case insensitive, but if
>someone really wants to use upper case???

Then he has to quote these column/table names all the time (=pain in the *ss).

One possible option is to use case insensitive ILIKE operator instead of
LIKE in the corresponding clauses in order to get also the upper case
results. But this has the obvious minor performance penalty from not been
able to use indeces with pg_class.relname

Another option:

Current postgresql driver answers like this:

   supportsMixedCaseIdentifiers  false
   storesUpperCaseIdentifiers    false
   storesLowerCaseIdentifiers    true
   storesMixedCaseIdentifiers    false
   supportsMixedCaseQuotedIdentifiers    true
   storesUpperCaseQuotedIdentifiers      false
   storesLowerCaseQuotedIdentifiers      false
   storesMixedCaseQuotedIdentifiers      false
   getIdentifierQuoteString      "

I don't know about the true jdbc specs (Sun's guys propably didn't spec
this at all!) but shouldn't it be so that if someone truly want's to use
upper/mixed case identifiers with Postgresql he should quote them all the
time INCLUDING when calling DatabaseMetaData.getColumns() ie.

getColumns("","","\"FOOTABLE\"","\"FOOCOLUMN\"");

This could be coded then efficiently just checking for the first quote char !!

   - Panu


>Dave
>On Tue, 2002-04-16 at 07:47, Panu Outinen wrote:
> > At 06:34 16.4.2002 -0400, Dave Cramer wrote:
> > >Panu,
> > >
> > >What happens if I actually do have a column with an uppercase name?
> >
> > It doesn't work BUT then all the other methods (in DatabaseMetaData) like:
> >
> > DatabaseMetaData.getColumnPrivileges()
> > DatabaseMetaData.getIndexInfo()
> > DatabaseMetaData.getProcedures()
> > DatabaseMetaData.getTables()
> >
> > should be coded case insensitively, too !!
> >
> > Now they're all using toLowerCase() with table/column names and 1.33
> > version of DatabaseMetaData.java used to have it also
> >
> >    - Panu
> >
> >
> >
> > >Dave
> > >On Tue, 2002-04-16 at 06:17, Panu Outinen wrote:
> > > >
> > > > Following patches are against the current CVS version (HEAD) as of
> > > 2002-04-16.
> > > >
> > > > Especially the current CVS version of DatabaseMetaData.getColumns()
> > > doesn't
> > > > work at all because the CVS version 1.50 of
> > > > org/postgresql/jdbc2/DatabaseMetaData.java has accidentally wiped out
> > > the row:
> > > >
> > > > v.addElement(tuple);
> > > >
> > > >    - Panu
> > > >
> > > > PS. Only the JDBC2 version was tested !!
> > > >
> > > >
> ---------------------------------------------------------------------------
> > > >
> > > > --- org/postgresql/jdbc2/DatabaseMetaData.java.org    Tue Apr 16
> > > 11:17:34 2002
> > > > +++ org/postgresql/jdbc2/DatabaseMetaData.java        Tue Apr 16
> > > 11:32:12 2002
> > > > @@ -2053,12 +2053,12 @@
> > > >
> > > >               if ((tableNamePattern != null) && !
> > > tableNamePattern.equals("%"))
> > > >               {
> > > > -                     sql.append(" and c.relname like \'" +
> > > tableNamePattern + "\'");
> > > > +                     sql.append(" and c.relname like \'" +
> > > tableNamePattern.toLowerCase() +
> > > > "\'");
> > > >               }
> > > >
> > > >               if ((columnNamePattern != null) && !
> > > columnNamePattern.equals("%"))
> > > >               {
> > > > -                     sql.append(" and a.attname like \'" +
> > > columnNamePattern + "\'");
> > > > +                     sql.append(" and a.attname like \'" +
> > > columnNamePattern.toLowerCase() +
> > > > "\'");
> > > >               }
> > > >
> > > >               sql.append(
> > > > @@ -2146,6 +2146,7 @@
> > > >                       tuple[16] =
> > > r.getBytes(5);                              // ordinal position
> > > >                       tuple[17] = (nullFlag.equals("f") ? "YES" :
> > > "NO").getBytes();   // Is
> > > > nullable
> > > >
> > > > +                     v.addElement(tuple);
> > > >               }
> > > >               r.close();
> > > >
> > > >
> > > >
> > > > --- org/postgresql/jdbc1/DatabaseMetaData.java.org    Tue Mar  5
> > > 05:02:47 2002
> > > > +++ org/postgresql/jdbc1/DatabaseMetaData.java        Tue Apr 16
> > > 11:45:15 2002
> > > > @@ -1953,12 +1953,12 @@
> > > >
> > > >               if ((tableNamePattern != null) && !
> > > tableNamePattern.equals("%"))
> > > >               {
> > > > -                     sql.append("                and c.relname like
> > > \'" + tableNamePattern +
> > > > "\'");
> > > > +                     sql.append("                and c.relname
> like \'" +
> > > > tableNamePattern.toLowerCase() + "\'");
> > > >               }
> > > >
> > > >               if ((columnNamePattern != null) && !
> > > columnNamePattern.equals("%"))
> > > >               {
> > > > -                     sql.append("                and a.attname like
> > > \'" + columnNamePattern
> > > > + "\'");
> > > > +                     sql.append("                and a.attname
> like \'" +
> > > > columnNamePattern.toLowerCase() + "\'");
> > > >               }
> > > >
> > > >               sql.append(
> > > >
> > > >
> > > >
> > > > ---------------------------(end of
> broadcast)---------------------------
> > > > TIP 2: you can get off all lists at once with the unregister command
> > > >     (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
> > > >
> > > >
> > >
> > >
> > >
> > >
> > >---------------------------(end of broadcast)---------------------------
> > >TIP 2: you can get off all lists at once with the unregister command
> > >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> >
> > ------
> > Panu Outinen                           Tel. +358 9 2706 8132
> > Vertex Systems Oy                      GSM  +358 400 627 800
> > Pitkänsillanranta 3 A, 5.krs           http://www.vertex.fi
> > 00530 Helsinki, FINLAND                mailto:Panu.Outinen@vertex.fi
> >
> >