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

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

From
Panu Outinen
Date:
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(



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

From
Dave Cramer
Date:
Panu,

What happens if I actually do have a column with an uppercase name?

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




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

From
Panu Outinen
Date:
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


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

From
Panu Outinen
Date:
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
> >
> >


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

From
Barry Lind
Date:
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
>
>