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: some questions about collations
Date Fri, 16 Dec 2011 22:17:00 GMT
Hi Rick,

Yes, I think the script provided by you exhibites Derby behavior.

thanks,
Mamta

On Fri, Dec 16, 2011 at 12:58 PM, Rick Hillegas
<rick.hillegas@oracle.com> wrote:
> Thanks, Mamta. That was very helpful. I think I am beginning to understand
> this better. At the end of this message I include a script which I believe
> demonstrates the following:
>
> 1) The collation of string literals changes depending on your compilation
> schema.
>
> 2) However, the collation of columns, routine args, and routine return types
> is invariant, regardless of your compilation schema.
>
> 3) Collation is only consulted when comparing two string values. The values
> must have the same collation.
>
> 4) Collation is unimportant when passing a string value as a routine
> argument or inserting a string value into a column. The string value and the
> argument type can have different collations.
>
> Thanks,
> -Rick
>
> connect
> 'jdbc:derby:memory:db;create=true;collation=TERRITORY_BASED;user=test_dbo;password=test_dbopassword';
>
> create table t( a varchar( 128 ) );
> create function f( a varchar( 128 ) ) returns int language java parameter
> style java no sql external name 'java.lang.Integer.parseInt';
>
> --
> -- Test return types of system function  in territory based schema
> --
>
> -- fails because 'foo' is territory based but the function returns a
> UCS_BASIC value
> select a from test_dbo.t where 'foo' =
> syscs_util.syscs_get_database_property( 'derby.database.collation' );
>
> -- succeeds because the column and function return value are both UCS_BASIC
> select tablename from sys.systables where tablename =
> syscs_util.syscs_get_database_property( 'derby.database.collation' );
>
> -- fails because the column is territory based but the return value is
> UCS_BASIC
> select a from test_dbo.t where a = syscs_util.syscs_get_database_property(
> 'derby.database.collation' );
>
> --
> -- Test argument types of system function  in territory based schema
> --
> --
> -- All of these succeed. Collation of argument type doesn't matter.
> --
> select a from test_dbo.t where syscs_util.syscs_get_database_property( a )
> is not null;
> select tablename from sys.systables where
> syscs_util.syscs_get_database_property( tablename ) is not null;
> select a from test_dbo.t where syscs_util.syscs_get_database_property(
> 'derby.database.collation' ) is null;
>
> --
> -- Test argument types of user function  in territory based schema
> --
> --
> -- All of these succeed. Collation of argument type doesn't matter.
> --
> select a from test_dbo.t where test_dbo.f( '1' ) is null;
> select tablename from sys.systables where tablename is null and test_dbo.f(
> tablename ) is null;
> select a from test_dbo.t where a is null and test_dbo.f( a ) is null;
>
> --
> -- Test insertions into territory based column in territory based schema
> --
> -- All of these succeed. Collation of inserted string doesn't matter.
> --
> insert into test_dbo.t( a ) values ( 'foo' );
> insert into test_dbo.t( a ) select tablename from sys.systables;
> insert into test_dbo.t( a ) select a from t;
>
> set schema sys;
>
> --
> -- Test return types of system function  in UCS_BASIC schema
> --
>
> -- succeeds because the string and return value are both UCS_BASIC
> select a from test_dbo.t where 'foo' =
> syscs_util.syscs_get_database_property( 'derby.database.collation' );
>
> -- succeeds because the column and return value are both UCS_BASIC
> select tablename from sys.systables where tablename =
> syscs_util.syscs_get_database_property( 'derby.database.collation' );
>
> -- fails because the column is territory based but the return value is
> UCS_BASIC
> select a from test_dbo.t where a = syscs_util.syscs_get_database_property(
> 'derby.database.collation' );
>
> --
> -- Test argument types of system function  in UCS_BASIC schema
> --
> --
> -- All of these succeed. Collation of argument type doesn't matter.
> --
> select a from test_dbo.t where syscs_util.syscs_get_database_property( a )
> is not null;
> select tablename from sys.systables where
> syscs_util.syscs_get_database_property( tablename ) is not null;
> select a from test_dbo.t where syscs_util.syscs_get_database_property(
> 'derby.database.collation' ) is null;
>
> --
> -- Test argument types of user function  in UCS_BASIC schema
> --
> --
> -- All of these succeed. Collation of argument type doesn't matter.
> --
> select a from test_dbo.t where test_dbo.f( '1' ) is null;
> select tablename from sys.systables where tablename is null and test_dbo.f(
> tablename ) is null;
> select a from test_dbo.t where a is null and test_dbo.f( a ) is null;
>
> --
> -- Test insertions into territory based column in UCS_BASIC schema
> --
> --
> -- All of these succeed. Collation of inserted string doesn't matter.
> --
> insert into test_dbo.t( a ) values ( 'foo' );
> insert into test_dbo.t( a ) select tablename from sys.systables;
> insert into test_dbo.t( a ) select a from test_dbo.t;
>

Mime
View raw message