Thread: Fixes for JDBC's getColumns() in Postgresql 7.2 & CVS
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(
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)
>
>
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
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
> >
> >
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
>
>