Thread: NetBeans (3.5 Dev), Postgresql 7.4dev jdbc

NetBeans (3.5 Dev), Postgresql 7.4dev jdbc

From
snpe
Date:
Hello,
I try Database explorer with Netbeans and Postgresql (it is same with forte and postgresql 7.3.2 jdbc)
I can't get tables, views , procedures.
Problem is schema name - btw. when Database explorer call DatabaseMetadata.getTables use only upper case
(no matter if schema lower case) and don't find anything.
I think that problem is Netbeans, because lower case schema name is allowed.
- schema (and user,tables, procedures etc)  don't be set in upper case

BTW. I have user (schema) snpe :
Netbeans Database Explorer call next query for tables (query is ugly, but PostgreSQL haven't friendly catalog) :

SELECT NULL AS TABLE_CAT, n.nspname AS TABLE_SCHEM, c.relname AS TABLE_NAME,
CASE n.nspname LIKE 'pg\\_%'
WHEN true THEN
    CASE n.nspname
    WHEN 'pg_catalog' THEN
    CASE c.relkind
        WHEN 'r' THEN 'SYSTEM TABLE'
    WHEN 'v' THEN 'SYSTEM VIEW'
        WHEN 'i' THEN 'SYSTEM INDEX'
ELSE NULL         END
WHEN 'pg_toast' THEN CASE c.relkind
        WHEN 'r' THEN 'SYSTEM TOAST TABLE'
        WHEN 'i' THEN 'SYSTEM TOAST INDEX'
    ELSE NULL
END
ELSE CASE c.relkind
        WHEN 'r' THEN 'TEMPORARY TABLE'
        WHEN 'i' THEN 'TEMPORARY INDEX'
        ELSE NULL
        END
END
 WHEN false THEN
CASE c.relkind     WHEN 'r' THEN 'TABLE'     WHEN 'i' THEN 'INDEX'     WHEN 'S' THEN 'SEQUENCE'     WHEN 'v' THEN
'VIEW'    ELSE NULL     END  ELSE NULL  END 
 AS TABLE_TYPE,
    d.description AS REMARKS
 FROM pg_catalog.pg_namespace n, pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_description d ON (c.oid = d.objoid AND d.objsubid = 0)
     LEFT JOIN pg_catalog.pg_class dc ON (d.classoid=dc.oid AND dc.relname='pg_class')
     LEFT JOIN pg_catalog.pg_namespace dn ON (dn.oid=dc.relnamespace AND dn.nspname='pg_catalog')
 WHERE c.relnamespace = n.oid
** AND n.nspname LIKE 'SNPE'
 AND c.relname LIKE '%'
 AND (false  OR ( c.relkind = 'r' AND n.nspname NOT LIKE 'pg\\_%' ) )
 ORDER BY TABLE_TYPE,TABLE_SCHEM,TABLE_NAME

Row with ** make problem
I try < AND n.nspname like 'snpe' > and it work.

Who will resolve : Netbeans or Postgresql JDBC people ?

regards
Haris Peco


Re: NetBeans (3.5 Dev), Postgresql 7.4dev jdbc

From
Charlie Woloszynski
Date:
It is my understanding that SQL92 calls for all names to be
CASE-INSENSITIVE and that PostgreSQL is broken in this respect.
Perhaps we can get some code introduced to munge cases in the parser
(as a configuration option to avoid the performance penalty for those
who care to be case sensitive).



Charlie


On Thursday, April 3, 2003, at 08:23 PM, snpe wrote:

> Hello,
> I try Database explorer with Netbeans and Postgresql (it is same with
> forte and postgresql 7.3.2 jdbc)
> I can't get tables, views , procedures.
> Problem is schema name - btw. when Database explorer call
> DatabaseMetadata.getTables use only upper case
> (no matter if schema lower case) and don't find anything.
> I think that problem is Netbeans, because lower case schema name is
> allowed.
> - schema (and user,tables, procedures etc)  don't be set in upper case
>
> BTW. I have user (schema) snpe :
> Netbeans Database Explorer call next query for tables (query is ugly,
> but PostgreSQL haven't friendly catalog) :
>
> SELECT NULL AS TABLE_CAT, n.nspname AS TABLE_SCHEM, c.relname AS
> TABLE_NAME,
> CASE n.nspname LIKE 'pg\\_%'
> WHEN true THEN
>     CASE n.nspname
>     WHEN 'pg_catalog' THEN
>     CASE c.relkind
>         WHEN 'r' THEN 'SYSTEM TABLE'
>     WHEN 'v' THEN 'SYSTEM VIEW'
>         WHEN 'i' THEN 'SYSTEM INDEX'
> ELSE NULL         END
> WHEN 'pg_toast' THEN CASE c.relkind
>         WHEN 'r' THEN 'SYSTEM TOAST TABLE'
>         WHEN 'i' THEN 'SYSTEM TOAST INDEX'
>     ELSE NULL
> END
> ELSE CASE c.relkind
>         WHEN 'r' THEN 'TEMPORARY TABLE'
>         WHEN 'i' THEN 'TEMPORARY INDEX'
>         ELSE NULL
>         END
> END
>  WHEN false THEN
> CASE c.relkind     WHEN 'r' THEN 'TABLE'     WHEN 'i' THEN 'INDEX'     WHEN 'S'
> THEN 'SEQUENCE'     WHEN 'v' THEN 'VIEW'     ELSE NULL     END  ELSE NULL  END
>  AS TABLE_TYPE,
>     d.description AS REMARKS
>  FROM pg_catalog.pg_namespace n, pg_catalog.pg_class c
>      LEFT JOIN pg_catalog.pg_description d ON (c.oid = d.objoid AND
> d.objsubid = 0)
>      LEFT JOIN pg_catalog.pg_class dc ON (d.classoid=dc.oid AND
> dc.relname='pg_class')
>      LEFT JOIN pg_catalog.pg_namespace dn ON (dn.oid=dc.relnamespace AND
> dn.nspname='pg_catalog')
>  WHERE c.relnamespace = n.oid
> ** AND n.nspname LIKE 'SNPE'
>  AND c.relname LIKE '%'
>  AND (false  OR ( c.relkind = 'r' AND n.nspname NOT LIKE 'pg\\_%' ) )
>  ORDER BY TABLE_TYPE,TABLE_SCHEM,TABLE_NAME
>
> Row with ** make problem
> I try < AND n.nspname like 'snpe' > and it work.
>
> Who will resolve : Netbeans or Postgresql JDBC people ?
>
> regards
> Haris Peco
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster


Re: NetBeans (3.5 Dev), Postgresql 7.4dev jdbc

From
Dave Cramer
Date:
SQL92 is quite old, what does sql99 say about case sensitivity?

also I'm not sure what exactly the issue is here?

as long as the names are not surrounded by ' then postgres is case
insensitive.

Dave
On Fri, 2003-04-04 at 08:53, Charlie Woloszynski wrote:
> It is my understanding that SQL92 calls for all names to be
> CASE-INSENSITIVE and that PostgreSQL is broken in this respect.
> Perhaps we can get some code introduced to munge cases in the parser
> (as a configuration option to avoid the performance penalty for those
> who care to be case sensitive).
>
>
>
> Charlie
>
>
> On Thursday, April 3, 2003, at 08:23 PM, snpe wrote:
>
> > Hello,
> > I try Database explorer with Netbeans and Postgresql (it is same with
> > forte and postgresql 7.3.2 jdbc)
> > I can't get tables, views , procedures.
> > Problem is schema name - btw. when Database explorer call
> > DatabaseMetadata.getTables use only upper case
> > (no matter if schema lower case) and don't find anything.
> > I think that problem is Netbeans, because lower case schema name is
> > allowed.
> > - schema (and user,tables, procedures etc)  don't be set in upper case
> >
> > BTW. I have user (schema) snpe :
> > Netbeans Database Explorer call next query for tables (query is ugly,
> > but PostgreSQL haven't friendly catalog) :
> >
> > SELECT NULL AS TABLE_CAT, n.nspname AS TABLE_SCHEM, c.relname AS
> > TABLE_NAME,
> > CASE n.nspname LIKE 'pg\\_%'
> > WHEN true THEN
> >     CASE n.nspname
> >     WHEN 'pg_catalog' THEN
> >     CASE c.relkind
> >         WHEN 'r' THEN 'SYSTEM TABLE'
> >     WHEN 'v' THEN 'SYSTEM VIEW'
> >         WHEN 'i' THEN 'SYSTEM INDEX'
> > ELSE NULL         END
> > WHEN 'pg_toast' THEN CASE c.relkind
> >         WHEN 'r' THEN 'SYSTEM TOAST TABLE'
> >         WHEN 'i' THEN 'SYSTEM TOAST INDEX'
> >     ELSE NULL
> > END
> > ELSE CASE c.relkind
> >         WHEN 'r' THEN 'TEMPORARY TABLE'
> >         WHEN 'i' THEN 'TEMPORARY INDEX'
> >         ELSE NULL
> >         END
> > END
> >  WHEN false THEN
> > CASE c.relkind     WHEN 'r' THEN 'TABLE'     WHEN 'i' THEN 'INDEX'     WHEN 'S'
> > THEN 'SEQUENCE'     WHEN 'v' THEN 'VIEW'     ELSE NULL     END  ELSE NULL  END
> >  AS TABLE_TYPE,
> >     d.description AS REMARKS
> >  FROM pg_catalog.pg_namespace n, pg_catalog.pg_class c
> >      LEFT JOIN pg_catalog.pg_description d ON (c.oid = d.objoid AND
> > d.objsubid = 0)
> >      LEFT JOIN pg_catalog.pg_class dc ON (d.classoid=dc.oid AND
> > dc.relname='pg_class')
> >      LEFT JOIN pg_catalog.pg_namespace dn ON (dn.oid=dc.relnamespace AND
> > dn.nspname='pg_catalog')
> >  WHERE c.relnamespace = n.oid
> > ** AND n.nspname LIKE 'SNPE'
> >  AND c.relname LIKE '%'
> >  AND (false  OR ( c.relkind = 'r' AND n.nspname NOT LIKE 'pg\\_%' ) )
> >  ORDER BY TABLE_TYPE,TABLE_SCHEM,TABLE_NAME
> >
> > Row with ** make problem
> > I try < AND n.nspname like 'snpe' > and it work.
> >
> > Who will resolve : Netbeans or Postgresql JDBC people ?
> >
> > regards
> > Haris Peco
> >
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
--
Dave Cramer <Dave@micro-automation.net>


Re: NetBeans (3.5 Dev), Postgresql 7.4dev jdbc

From
Tom Lane
Date:
Charlie Woloszynski <chw@clearmetrix.com> writes:
> It is my understanding that SQL92 calls for all names to be
> CASE-INSENSITIVE and that PostgreSQL is broken in this respect.

The spec calls for unquoted names to be case insensitive.  They are,
in Postgres.  The spec also calls for quoted names to be case sensitive.
They are, in Postgres.  The place where we diverge from the spec is in
what happens when you write what you intend to be the same name both
with and without quotes at different times.  I'd suggest that that is
bad style and you'd best not do it.  For one thing, the implied case
conversion is locale-specific (yes, that's per spec too) --- so SQL
that works in one locale may fail in another.  The only way to defend
agains the locale risk is to consistently write any given name either
always with quotes, or always without.  If you do that then Postgres'
deviation from the spec isn't going to affect you.

For the record, unquoted foo (regardless of case) equates to "FOO" per
spec, but "foo" in Postgres.

            regards, tom lane


Re: NetBeans (3.5 Dev), Postgresql 7.4dev jdbc

From
snpe
Date:
There are methods in DatabaseMetadata like storesLowerCaseIdentifiers(), storesMixedCaseInetifiers()
(postgresql return corect value for this) and netbeans module db need use this methods

regards
Haris Peco
On Friday 04 April 2003 03:09 pm, Tom Lane wrote:
> Charlie Woloszynski <chw@clearmetrix.com> writes:
> > It is my understanding that SQL92 calls for all names to be
> > CASE-INSENSITIVE and that PostgreSQL is broken in this respect.
>
> The spec calls for unquoted names to be case insensitive.  They are,
> in Postgres.  The spec also calls for quoted names to be case sensitive.
> They are, in Postgres.  The place where we diverge from the spec is in
> what happens when you write what you intend to be the same name both
> with and without quotes at different times.  I'd suggest that that is
> bad style and you'd best not do it.  For one thing, the implied case
> conversion is locale-specific (yes, that's per spec too) --- so SQL
> that works in one locale may fail in another.  The only way to defend
> agains the locale risk is to consistently write any given name either
> always with quotes, or always without.  If you do that then Postgres'
> deviation from the spec isn't going to affect you.
>
> For the record, unquoted foo (regardless of case) equates to "FOO" per
> spec, but "foo" in Postgres.
>
>             regards, tom lane