db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@oracle.com>
Subject Re: some questions about collations
Date Fri, 16 Dec 2011 20:58:22 GMT
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