db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta Satoor" <msat...@gmail.com>
Subject Re: more on system schema vs. user schema and character constants.
Date Thu, 31 May 2007 05:55:04 GMT
Mike, you can rewrite the query as shown below using CAST
select COLUMNNAME from SYS.SYSCOLUMNS where
CAST(SYS.SYSCOLUMNS.COLUMNNAMEas CHAR(10) = 'fred';

The result of CAST will take the collation of the current schema which will
be same the collation taken by 'fred' which is the collation of the current
schema. This way, the query will work in any schema.

Please let me know if this does not answer your question. Just an FYI, there
are examples of such queries in CollationTest.java

HTH,
Mamta


On 5/30/07, Mike Matrigali <mikem_app@sbcglobal.net> wrote:
>
> I am sort of confused at this point, what we expect to be working
> now and what should be.  What is the right way to write the
> following query against a the system schema?  Is there anyway to
> write the query without changing the default schema? :
>
> select COLUMNNAME from SYS.SYSCOLUMNS where SYS.SYSCOLUMNS.COLUMNNAME =
> 'fred';
>
>
> I tried the following:
> ij> connect
> 'jdbc:derby:endb;create=true;territory=en;collation=TERRITORY_BASED'
> ;
>
> ij(CONNECTION1)> set schema APP;
> 0 rows inserted/updated/deleted
> ij(CONNECTION1)> select COLUMNNAME from SYS.SYSCOLUMNS where
> SYS.SYSCOLUMNS.COLU
> MNNAME = 'fred';
> ERROR 42818: Comparisons between 'VARCHAR' and 'CHAR' are not supported.
> ij(CONNECTION1)> select COLUMNNAME from SYS.SYSCOLUMNS where COLUMNNAME
> = 'fred'
> ;
> ERROR 42818: Comparisons between 'VARCHAR' and 'CHAR' are not supported.
> ij(CONNECTION1)> select COLUMNNAME from SYS.SYSCOLUMNS where
> SYS.SYSCOLUMNS.COLU
> MNNAME = CAST('fred' as CHAR);
> ERROR 42818: Comparisons between 'VARCHAR' and 'CHAR' are not supported.
> ij(CONNECTION1)> select COLUMNNAME from SYS.SYSCOLUMNS where COLUMNNAME
> = CAST('
> fred' as CHAR);
> ERROR 42818: Comparisons between 'VARCHAR' and 'CHAR' are not supported.
> ij(CONNECTION1)> set schema SYS;
> 0 rows inserted/updated/deleted
> ij(CONNECTION1)> select COLUMNNAME from SYS.SYSCOLUMNS where
> SYS.SYSCOLUMNS.COLU
> MNNAME = 'fred';
> COLUMNNAME
>
>
> --------------------------------------------------------------------------------
> ------------------------------------------------
>
> 0 rows selected
> ij(CONNECTION1)> select COLUMNNAME from SYS.SYSCOLUMNS where COLUMNNAME
> = 'fred'
> ;
> COLUMNNAME
>
>
> --------------------------------------------------------------------------------
> ------------------------------------------------
>
> 0 rows selected
> ij(CONNECTION1)> select COLUMNNAME from SYS.SYSCOLUMNS where
> SYS.SYSCOLUMNS.COLU
> MNNAME = CAST('fred' as CHAR);
> COLUMNNAME
>
>
> --------------------------------------------------------------------------------
> ------------------------------------------------
>
> 0 rows selected
> ij(CONNECTION1)> select COLUMNNAME from SYS.SYSCOLUMNS where COLUMNNAME
> = CAST('
> fred' as CHAR);
> COLUMNNAME
>
>
> --------------------------------------------------------------------------------
> ------------------------------------------------
>
> 0 rows selected
>

Mime
View raw message