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;
