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

Does any one know how other database's jdbc drivers handle this problem?
  Since the SQL92 spec says that sql identifiers are case insensitive
(unless quoted), the problem we face here shouldn't be unique to
postgres.  Oracle and the other jdbc drivers out there should have the
same issues.  How do they deal with this?

thanks,
--Barry



Panu Outinen wrote:
> 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
>> >
>> >
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
>