Re: Fixes for JDBC's getColumns() in Postgresql 7.2 &

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(at)postgresql(dot)org)
> > > >
> > > >
> > >
> > >
> > >
> > >
> > >---------------------------(end of broadcast)---------------------------
> > >TIP 2: you can get off all lists at once with the unregister command
> > > (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)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(dot)Outinen(at)vertex(dot)fi
> >
> >