core/sql/regress/charsets/EXPECTED003

>>
>>--test the exposure of UCS2 and disabling of UNICODE 
>>--as a character set name.
>>
>>--positive
>>--Table columns can be created to be associated with character set UCS2.
>>create table t002(c char(10) character set UCS2);

--- SQL operation complete.
>>
>>--INVOKE and SHOWDDL work for tables with columns associated with UCS2.
>>invoke t002;

-- Definition of Trafodion table TRAFODION.SCH.T002
-- Definition current  Tue Sep 20 12:51:23 2016

  (
    SYSKEY                           LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
  , C                                CHAR(10) CHARACTER SET UCS2 COLLATE
      DEFAULT DEFAULT NULL
  )

--- SQL operation complete.
>>showddl t002;

CREATE TABLE TRAFODION.SCH.T002
  (
    C                                CHAR(10) CHARACTER SET UCS2 COLLATE
      DEFAULT DEFAULT NULL
  )
 ATTRIBUTES ALIGNED FORMAT
;

--- SQL operation complete.
>>
>>--negative
>>--Table columns can not be created to be assocated with UNICODE.
>>create table t003(c char(10) character set UNICODE);

*** ERROR[15001] A syntax error occurred at or before: 
create table t003(c char(10) character set UNICODE);
                                                 ^ (50 characters from start of SQL statement)

*** ERROR[8822] The statement was not prepared.

>>
>>
>>?section dml
>>
>>--positive
>>insert into t002 values(_ucs2'qwer');

--- 1 row(s) inserted.
>>update t002 set c = _UCS2'xxxx' where c = _UCS2'qwer';

--- 1 row(s) updated.
>>delete from t002 where c = _UCS2'asdf';

--- 0 row(s) deleted.
>>select _UCS2'ucs2', _ISO88591'iso88591', c from t002;

(EXPR)    (EXPR)    C                   
--------  --------  --------------------

ucs2      iso88591  xxxx                

--- 1 row(s) selected.
>>
>>--negative
>>insert into t002 values(_UNICODE'asdf');

*** ERROR[3127] An invalid character was found in identifier _UNICODE.

*** ERROR[15001] A syntax error occurred at or before: 
insert into t002 values(_UNICODE'asdf');
                              ^ (31 characters from start of SQL statement)

*** ERROR[8822] The statement was not prepared.

>>
>>--VARNCHAR disallowed
>>create table t004(c varnchar(10));

*** ERROR[15001] A syntax error occurred at or before: 
create table t004(c varnchar(10));
                           ^ (28 characters from start of SQL statement)

*** ERROR[8822] The statement was not prepared.

>>
>>
>>-------------------------------
>>--test translation name changes
>>-------------------------------
>>
>>create table t001(c char(10));

--- SQL operation complete.
>>insert into t001 values(x'4e');

--- 1 row(s) inserted.
>>
>>?section dml
>>--New valid name. Case insensitive
>>select translate(_kanji'asdf' using KANJITOISO88591) from (values(1))x;

*** ERROR[3010] Character set KANJI is not yet supported.

*** ERROR[15001] A syntax error occurred at or before: 
select translate(_kanji'asdf' using KANJITOISO88591) from (values(1))x;
                      ^ (23 characters from start of SQL statement)

*** ERROR[8822] The statement was not prepared.

>>select translate(_ksc5601'1234' using KSC5601TOISO88591) from (values(1))x;

*** ERROR[3010] Character set KSC5601 is not yet supported.

*** ERROR[15001] A syntax error occurred at or before: 
select translate(_ksc5601'1234' using KSC5601TOISO88591) from (values(1))x;
                        ^ (25 characters from start of SQL statement)

*** ERROR[8822] The statement was not prepared.

>>select translate(_UCS2'asdf' using UCS2TOISO88591) from (values(1))x;

(EXPR)
------

asdf  

--- 1 row(s) selected.
>>insert into t002 
+>  select translate(c using iso88591ToUCS2) from t001 
+>     where c = x'4e';

--- 1 row(s) inserted.
>>
>>--old name not allowed
>>insert into t001 values(translate(_UCS2'qwer' using UToL1));

*** ERROR[4105] Translation name is not recognized.

*** ERROR[8822] The statement was not prepared.

>>insert into t001 values(translate(_UCS2'ewrt' using UNICODE_TO_ISO88591));

*** ERROR[4105] Translation name is not recognized.

*** ERROR[8822] The statement was not prepared.

>>insert into t002 values(translate('asdf' using ISO88591TOUCS2));

--- 1 row(s) inserted.
>>insert into t002 values(translate('qwer' using L1ToU));

*** ERROR[4105] Translation name is not recognized.

*** ERROR[8822] The statement was not prepared.

>>insert into t002 values(translate('ewrt' using ISO88591_TO_UNICODE));

*** ERROR[4105] Translation name is not recognized.

*** ERROR[8822] The statement was not prepared.

>>
>>-------------------------------
>>--test TERMINAL_CHARSETS 
>>-------------------------------
>>
>>set terminal_charset iso88591;
>>
>>-- the following should fail
>>set terminal_charset iso88592;

*** ERROR[15990] Character set name for SQLCI attribute TERMINAL_CHARSET is not valid.

>>set terminal_charset ucs2;

*** ERROR[2038] Character set ucs2 is not supported for SQLCI attribute TERMINAL_CHARSET.

>>set terminal_charset sjis;
>>set terminal_charset x0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789;

*** ERROR[15990] Character set name for SQLCI attribute TERMINAL_CHARSET is not valid.

>>
>>-- the default is ucs2. this is a no-op.
>>control query default national_charset 'ucs2';

--- SQL operation complete.
>>
>>--test CQD NATIONAL_CHARSET (should fail)
>>-------------------------------
>>control query default national_charset 'ios88591';

*** ERROR[4130] NATIONAL_CHARSET is a read-only DEFAULTS attribute and cannot be updated.

*** ERROR[8822] The statement was not prepared.

>>
>>-- an invalid sqlci command, exercise the single-byte version of StoreSyntaxError()
>>bad_sqlci_cmd 1;

*** ERROR[15001] A syntax error occurred at or before: 
bad_sqlci_cmd 1;
              ^ (15 characters from start of SQL statement)

>>
>>?section clnup
>>drop table t001;

--- SQL operation complete.
>>drop table t002;

--- SQL operation complete.
>>
>>log;