In ObjectStudio 8.5 and VisualWorks 7.10, Cincom has added full Unicode support to their DB2 connects.  This means that from those versions, users will be able to use Unicode table and column names, include Unicode strings in their SQL statements, bind Unicode strings to host variables and even input and output Unicode strings to and from stored procedures.

Here is how the new implementation works for you:

  1. If you don’t set the main encoding to Unicode (either UTF16 or UCS_2), it will work as before; all existing applications will continue to work.
  2. If you don’t set the main encoding to Unicode but want to use Unicode columns, you can do so as well, but when binding String values, you have to set binding templates for the binding values to indicate which Strings should be encoded as Unicode.  This will provide the flexibility for users to develop “mixed” applications.
  3. If you set the main encoding to Unicode (either UTF16 or UCS_2), no binding template is necessary and all connect strings, SQL statements, error messages, insert and retrieved Strings will be treated as Unicode.

DB2 Unicode Support Examples in ObjectStudio

The following is an example in Unicode mode:

” Logon to the database server.”

ESDatabase defaultEncoding: #UCS_2.
ESDatabase logOnServer: #DB2 user: #'username' password:
#'password'.

“Get access to the database needed.”

db := ESDatabase accessName: #DB2.

“Drop the test table if existed.”

db execSql: 'drop table test_unicode'.

“Create a test table.”

res := db execSql: 'create table test_unicode 

    (cid integer, cc char(100), cuc graphic(100),
cname varchar(100), cname1 VARGRAPHIC(100), 

cl CLOB(8m), ncl DBCLOB(4m))'.

“Insert first row of data.”

sqlString := 'insert into test_unicode 

     values(1, ''€'', ''中国'', ''€1'', ''中国人民'',
     ''€2'', ''中国各省'')'.
db execSql: sqlString.

“Insert second row of data using host variables.”

sqlString := 'insert into test_unicode values 

       (?, ?, ?, ?, ?, ?, ?)'.
vars := Array new: 7.
vars at: 1 put: 2.
vars at: 2 put: 'aa'.
vars at: 3 put: '中国'.
vars at: 4 put: 'bb'.
vars at: 5 put: '中国人民'.
vars at: 6 put: 'cc'.
vars at: 7 put: '中国各省'.
db execSql: sqlString vars: vars template: nil.

“Insert third row of data using host variables.”

sqlString := 'insert into test_unicode values (?, ?, ?, ?, ?, ?, ?)'.
vars := Array new: 7.
vars at: 1 put: 3.
vars at: 2 put: '中国'.
vars at: 3 put: '中国'.
vars at: 4 put: '中国人民'.
vars at: 5 put: '中国人民'.
vars at: 6 put: '中国各省'.
vars at: 7 put: '中国各省'.
db execSql: sqlString vars: vars.

“Verify the data inserted.”

db execSql: 'SELECT * FROM test_unicode'.

“Test executing stored procedures.”

“Delete the contents in the table.”

db execSql: 'delete from test_unicode'.

“Drop the stored procedure if it exists.”

res := db execSql: 'DROP PROCEDURE testBindUnicode'.

“Create a stored procedure for insert.”

db execSql: 'CREATE PROCEDURE testBindUnicode(cid int, cc char(100), cuc graphic(100), cname varchar(100), cname1 vargraphic(100), cl clob, ncl dbclob)
language SQL
                begin
                           insert into test_unicode values (cid, cc, cuc, cname, cname1, cl, ncl);
                end
                '.

“Create the binding parameter array with initial values.”

vars := {3 '中国' '中国' '中国人民' '中国人民' '中国各省' '中国各省'}.

“Execute the procedure.”

resultTables := db execProc: 'testBindUnicode' params: vars template: nil paramTypes: {#in #in #in #in #in #in #in}.

“verify the data inserted.”

db execSql: 'select * from test_unicode'.

“Drop the stored procedure if it exists.”

res := db execSql: 'DROP PROCEDURE testBindUnicode1'.

“Drop the stored procedure if it exists.”

res := db execSql: 'CREATE PROCEDURE testBindUnicode1(out name varchar(100))
language SQL
                begin
                         select cname into name from test_unicode where cid=3;
                end
                '.

“Create the binding parameter array with initial values.”

vars := {'test123456'}.

“Execute the procedure.”

resultTable := db execProc: 'testBindUnicode1' params: vars template: nil paramTypes: {#out }.

“Drop the stored procedure if it exists.”

res := db execSql: 'DROP PROCEDURE testBindUnicode2'.

“Create a stored procedure to get data from a Unicode column.”

db execSql:'CREATE PROCEDURE testBindUnicode2(out name vargraphic(100))
language SQL
                begin
                           select cname1 into name from test_unicode where cid=3;
                end
                '.

“Create the binding parameter array with initial values.”

vars := {'test1234567890'}.

“Execute the procedure.”

resultTable := db execProc: 'testBindUnicode2' params: vars template: nil paramTypes: {#out }.

“Drop the stored procedure if it exists.”

res := db execSql: 'DROP PROCEDURE testBindUnicode3'.

“Create a select stored procedure.”

db execSql: 'CREATE PROCEDURE testBindUnicode3(in v_cid int, out name varchar(100))
language SQL
                begin
                          select cname into name from test_unicode where cid=v_cid;
                end
                '.

“Create the binding parameter array with initial values.”

vars := {3 'test1234567890'}.

“Execute the procedure.”

resultTable := db execProc: 'testBindUnicode3' params: vars template: nil paramTypes: {#in #out }.

“Drop the stored procedure if it exists.”

res := db execSql: 'DROP PROCEDURE testBindUnicode4'.

“Create a select stored procedure.”

db execSql: 'CREATE PROCEDURE testBindUnicode4(in v_cid int, out name varchar(100))
language SQL
                begin
                         select cname1 into name from test_unicode where cid=v_cid;
                end
                '.

“Create the binding parameter array with initial values.”

vars := {3 'test1234567890'}.

“Execute the procedure.”

resultTable := db execProc: 'testBindUnicode4' params: vars template: nil paramTypes: {#in #out }.

“Drop the stored procedure if it exists.”

res := db execSql: 'DROP PROCEDURE testBindUnicode5'.

“Create a select stored procedure.”

db execSql: 'CREATE PROCEDURE testBindUnicode5(in v_cid int, out v_cl clob)
language SQL
                begin
                         select cl into v_cl from test_unicode where cid=v_cid;
                end
                '.

“Create the binding parameter array with initial values.”

vars := {3 'test1234567890'}.

“Execute the procedure.”

resultTable := db execProc: 'testBindUnicode5' params: vars template: {nil nil} paramTypes: {#in #out }.

“Drop the stored procedure if it exists.”

res := db execSql: 'DROP PROCEDURE testBindUnicode6'.

“Create a select stored procedure.”

db execSql: 'CREATE PROCEDURE testBindUnicode6(in v_cid int, out v_cl dbclob)
language SQL
                begin
                         select ncl into v_cl from test_unicode where cid=v_cid;
                end
                '.

“Create the binding parameter array with initial values.”

vars := {3 'test1234567890'}.

“Execute the procedure.”

resultTable := db execProc: 'testBindUnicode6' params: vars template: {nil #UnicodeString} paramTypes: {#in #out }.

Here is an example in “mixed” mode:

” Logon to the database server.”

ESDatabase defaultEncoding: nil.
ESDatabase logOnServer: #DB2 user: #username password: #password.

“Get access to the database needed.”

db := ESDatabase accessName: #DB2.

“Drop the test table if existed.”

db execSql: 'drop table test_unicode'.

“Create a test table.”

res := db execSql: 'create table test_unicode (cid integer, cc char(100), cuc graphic(100), cname varchar(100), cname1 VARGRAPHIC(100), cl CLOB(8m), ncl DBCLOB(4m))'.

“Insert first row of data using host variables.”

sqlString := 'insert into test_unicode values (1, ''aa'', ?, ''bb'', ?, ''dd'', ?)'.
vars := Array with: '中国' with: '中国人民' with: '中国各省'.
db execSql: sqlString vars: vars template: #(UnicodeString #UnicodeString #UnicodeString).

“Insert second row of data using host variables.”

sqlString := 'insert into test_unicode values (?, ?, ?, ?, ?, ?, ?)'.
vars := Array new: 7.
vars at: 1 put: 2.
vars at: 2 put: 'aa'.
vars at: 3 put: '中国'.
vars at: 4 put: 'bb'.
vars at: 5 put: '中国人民'.
vars at: 6 put: 'cc'.
vars at: 7 put: '中国各省'.
db execSql: sqlString vars: vars template: #(nil nil UnicodeString nil #UnicodeString nil #UnicodeString).

“Verify the data inserted.”

db execSql: 'SELECT * FROM test_unicode'.

“Test executing stored procedures.”

“Delete the contents in the table.”

db execSql: 'delete from test_unicode'.

“Drop the stored procedure if it exists.”

res := db execSql: 'DROP PROCEDURE testBindUnicode'.

“Create a stored procedure for insert.”

db execSql: 'CREATE PROCEDURE testBindUnicode(cid int, cc char(100), cuc graphic(100), cname varchar(100), cname1 vargraphic(100), cl clob, ncl dbclob)
language SQL
                begin
                         insert into test_unicode values (cid, cc, cuc, cname, cname1, cl, ncl);
                end
                '.

“Create the binding parameter array with initial values.”

vars := {3 'aa' '中国' 'bb' '中国人民' 'cc' '中国各省'}.

“Execute the procedure.”

resultTables := db execProc: 'testBindUnicode' params: vars template: {nil nil #UnicodeString nil #UnicodeString nil #UnicodeString} paramTypes: {#in #in #in #in #in #in #in}.

“verify the data inserted.”

db execSql: 'select * from test_unicode'.

“Drop the stored procedure if it exists.”

res := db execSql: 'DROP PROCEDURE testBindUnicode1'.

“Drop the stored procedure if it exists.”

res := db execSql: 'CREATE PROCEDURE testBindUnicode1(out name varchar(100))
language SQL
                begin
                         select cname into name from test_unicode where cid=3;
                end
                '.

“Create the binding parameter array with initial values.”

vars := {'test123456'}.

“Execute the procedure.”

resultTable := db execProc: 'testBindUnicode1' params: vars template: {nil} paramTypes: {#out }.

“Drop the stored procedure if it exists.”

res := db execSql: 'DROP PROCEDURE testBindUnicode2'.

“Create a stored procedure to get data from a Unicode column.”

db execSql:'CREATE PROCEDURE testBindUnicode2(out name vargraphic(100))
language SQL
                begin
                          select cname1 into name from test_unicode where cid=3;
                end
                '.

“Create the binding parameter array with initial values.”

vars := {'test1234567890'}.

“Execute the procedure.”

resultTable := db execProc: 'testBindUnicode2' params: vars template: {#UnicodeString} paramTypes: {#out }.

“Drop the stored procedure if it exists.”

res := db execSql: 'DROP PROCEDURE testBindUnicode3'.

“Create a select stored procedure.”

db execSql: 'CREATE PROCEDURE testBindUnicode3(in v_cid int, out name varchar(100))
language SQL
                begin
                          select cname into name from test_unicode where cid=v_cid;
                end
                '.

“Create the binding parameter array with initial values.”

vars := {3 'test1234567890'}.

“Execute the procedure.”

resultTable := db execProc: 'testBindUnicode3' params: vars template: nil paramTypes: {#in #out }.

“Drop the stored procedure if it exists.”

res := db execSql: 'DROP PROCEDURE testBindUnicode4'.

“Create a select stored procedure.”

db execSql: 'CREATE PROCEDURE testBindUnicode4(in v_cid int, out name varchar(100))
language SQL
                begin
                          select cname1 into name from test_unicode where cid=v_cid;
                end
                '.

“Create the binding parameter array with initial values.”

vars := {3 'test1234567890'}.

“Execute the procedure.”

resultTable := db execProc: 'testBindUnicode4' params: vars template: {nil #UnicodeString} paramTypes: {#in #out }.

“Drop the stored procedure if it exists.”

res := db execSql: 'DROP PROCEDURE testBindUnicode5'.

“Create a select stored procedure.”

db execSql: 'CREATE PROCEDURE testBindUnicode5(in v_cid int, out v_cl clob)
language SQL
                begin
                         select cl into v_cl from test_unicode where cid=v_cid;
                end
                '.

“Create the binding parameter array with initial values.”

vars := {3 'test1234567890'}.

“Execute the procedure.”

resultTable := db execProc: 'testBindUnicode5' params: vars template: {nil nil} paramTypes: {#in #out }.

“Drop the stored procedure if it exists.”

res := db execSql: 'DROP PROCEDURE testBindUnicode6'.

“Create a select stored procedure.”

db execSql: 'CREATE PROCEDURE testBindUnicode6(in v_cid int, out v_cl dbclob)
language SQL
                begin
                         select ncl into v_cl from test_unicode where cid=v_cid;
                end
                '.

“Create the binding parameter array with initial values.”

vars := {3 'test1234567890'}.
"Execute the procedure."
resultTable := db execProc: 'testBindUnicode6' params: vars template: {nil #UnicodeString} paramTypes: {#in #out }.

DB2 Unicode Support Examples in VisualWorks

First, let’s start from a full Unicode example.  In this example, we’ll demonstrate how to use Unicode characters in insert, retrieval and in procedures (please note, in the code examples, some Chinese characters are used):

“Connect to DB2 with initial Unicode encoding.”

conn := DB2Connection new.
conn encoding: #UCS_2.
conn      username: 'username';
                                password: 'password';
                                environment: 'DB2'.
conn connect.

“Drop the test table if existed.”

sess := conn getSession.
sess prepare: 'drop table test_unicode'.
sess execute.
sess answer.
sess answer.

“Create the test table.”

sess := conn getSession.
sess prepare:  'create table test_unicode (cid integer, cc char(100), cuc graphic(100), cname varchar(100), cname1 VARGRAPHIC(100), cl CLOB(8m), ncl DBCLOB(4m))';
                execute;
     answer;
                answer.

“Insert some test data.”

sess := conn getSession.
sess prepare:   'insert into test_unicode values(1, ''€'', ''中国'', ''€1'', ''中国各省'', ''€2'', ''中国人民'')';
                execute;
     answer;
     answer.

“Insert second row of test data.”

sess := conn getSession.
sess prepare:   'insert into test_unicode values(?, ?, ?, ?, ?, ?, ?)';
bindInput: #(2 'ab' '中国' 'abcd' '中国各省' 'cdef' '中国人民');
                execute;
     answer;
     answer.

“Retrieve the test data.”

sess := conn getSession.
sess prepare: 'select * from test_unicode' ;
                execute.
ans := sess answer.
res := ans upToEnd inspect.
sess answer.

“Test Stored Procedures.”

“Delete the test data.”

sess := conn getSession.
sess prepare: 'delete from test_unicode' ;
                execute.
ans := sess answer.
sess answer.

“Drop the procedure if existed.”

sess := conn getSession.
sess prepare: 'DROP PROCEDURE testBindUnicode'.
sess execute.
answer := sess answer.
sess answer.

“Create a test procedure.”

sess := conn getSession.
sess prepare: 'CREATE PROCEDURE testBindUnicode(cid int, cc char(100), cuc graphic(100), cname varchar(100), cname1 vargraphic(100), cl clob, ncl dbclob)
language SQL
                begin
                          insert into test_unicode values (cid, cc, cuc, cname, cname1, cl, ncl);
                end
                '.
sess execute.
answer := sess answer.
sess answer.

“Calling the procedure with non-Null values.”

sess := conn getSession.
sess prepareCall: '{ call testBindUnicode(?, ?, ?, ?, ?, ?, ?)}'.
sess bindTemplate: #(nil nil #UnicodeString nil #UnicodeString nil #UnicodeString).
sess bindValue: 3 at: 1.
sess bindValue:  '中国1' at: 2.
sess bindValue:  '中国' at: 3.
sess bindValue: '中国各省1' at: 4.
sess bindValue: '中国各省' at: 5.
sess bindValue: '中国人民1' at: 6.
sess bindValue: '中国人民' at: 7.
sess execute.
answer := sess answer.
answer := sess answer.

“Retrieve the test data.”

sess := conn getSession.
sess prepare: 'select * from test_unicode' ;
                execute.
ans := sess answer.
res := ans upToEnd inspect.
sess answer.

“Drop the procedure if existed.”

sess := conn getSession.
sess prepare: 'DROP PROCEDURE testBindUnicode2'.
sess execute.
answer := sess answer.
answer := sess answer.

“Create a test procedure.”

sess := conn getSession.
sess prepare: 'CREATE PROCEDURE testBindUnicode2(out name varchar(100))
language SQL
                begin
                         select cname into name from test_unicode where cid=3;
                end
                '.
sess execute.
answer := sess answer.
answer := sess answer.

“Calling the procedure with non-Null values.”

sess := conn getSession.
sess prepareCall: '{ call testBindUnicode2(:outName)}'.
sess bindVariable: #outName value: '00000000' kind: #out.
sess execute.
answer := sess answer.
answer := sess answer.
sess bindVariable: #outName.

“Drop the procedure if existed.”

sess := conn getSession.
sess prepare: 'DROP PROCEDURE testBindUnicode21'.
sess execute.
answer := sess answer.
answer := sess answer.

“Create a test procedure.”

sess := conn getSession.
sess prepare: 'CREATE PROCEDURE testBindUnicode21(out name varchar(100))
language SQL
                begin
                          select cname1 into name from test_unicode where cid=3;
                end
                '.
sess execute.
answer := sess answer.
answer := sess answer.

“Calling the procedure with non-Null values.”

sess := conn getSession.
sess prepareCall: '{ call testBindUnicode21(:outName)}'.
sess bindVariable: #outName value: '00000000' kind: #out.
sess execute.
answer := sess answer.
answer := sess answer.
sess bindVariable: #outName.

“Drop the procedure if existed.”

sess := conn getSession.
sess prepare: 'DROP PROCEDURE testBindUnicode3'.
sess execute.
answer := sess answer.
answer := sess answer.

“Create a test procedure.”

sess := conn getSession.
sess prepare: 'CREATE PROCEDURE testBindUnicode3(in v_cid int, out name varchar(100))
language SQL
                begin
                         select cname into name from test_unicode where cid=v_cid;
                end
                '.
sess execute.
answer := sess answer.
answer := sess answer.

“Calling the procedure with non-Null values.”

sess := conn getSession.
sess prepareCall: '{ call testBindUnicode3(:inCid, :outName)}'.
sess bindVariable: #inCid value: 3 kind: #in.
sess bindVariable: #outName value: '00000000' kind: #out.
sess execute.
answer := sess answer.
answer := sess answer.

sess bindVariable: #outName.

“Drop the procedure if existed.”

sess := conn getSession.
sess prepare: 'DROP PROCEDURE testBindUnicode31'.
sess execute.
answer := sess answer.
answer := sess answer.

“Create a test procedure.”

sess := conn getSession.
sess prepare: 'CREATE PROCEDURE testBindUnicode31(in v_cid int, out name varchar(100))
language SQL
                begin
                          select cname1 into name from test_unicode where cid=v_cid;
                end
                '.
sess execute.
answer := sess answer.
answer := sess answer.

“Calling the procedure with non-Null values.”

sess := conn getSession.
sess prepareCall: '{ call testBindUnicode31(:inCid, :outName)}'.
sess bindVariable: #inCid value: 3 kind: #in.
sess bindVariable: #outName value: '00000000' kind: #out.
sess execute.
answer := sess answer.
answer := sess answer.
sess bindVariable: #outName.

“Drop the procedure if existed.”

sess := conn getSession.
sess prepare: 'DROP PROCEDURE testBindUnicode4'.
sess execute.
answer := sess answer.
answer := sess answer.

“Create a test procedure.”

sess := conn getSession.
sess prepare: 'CREATE PROCEDURE testBindUnicode4(in v_cid int, out v_cl clob)
language SQL
                begin
                          select cl into v_cl from test_unicode where cid=v_cid;
                end
                '.
sess execute.
answer := sess answer.
answer := sess answer.

“Calling the procedure with non-Null values.”

sess := conn getSession.
sess prepareCall: '{ call testBindUnicode4(:inCid, :outClob)}'.
sess bindVariable: #inCid value: 3 kind: #in.
sess bindVariable: #outClob value: '00000000' kind: #out.
sess execute.
answer := sess answer.
answer := sess answer.
sess bindVariable: #outClob.

“Drop the procedure if existed.”

sess := conn getSession.
sess prepare: 'DROP PROCEDURE testBindUnicode41'.
sess execute.
answer := sess answer.
answer := sess answer.

“Create a test procedure.”

sess := conn getSession.
sess prepare: 'CREATE PROCEDURE testBindUnicode41(out v_cl clob)
language SQL
                begin
                          select cl into v_cl from test_unicode where cid=3;
                end
                '.
sess execute.
answer := sess answer.
answer := sess answer.

“Calling the procedure with non-Null values.”

sess := conn getSession.
sess prepareCall: '{ call testBindUnicode41(:outClob)}'.
sess bindVariable: #outClob value: '00000000' kind: #out.
sess execute.
answer := sess answer.
answer := sess answer.
sess bindVariable: #outClob.

“Drop the procedure if existed.”

sess := conn getSession.
sess prepare: 'DROP PROCEDURE testBindUnicode42'.
sess execute.
answer := sess answer.
answer := sess answer.

“Create a test procedure.”

sess := conn getSession.
sess prepare: 'CREATE PROCEDURE testBindUnicode42(out v_cl dbclob)
language SQL
                begin
                          select ncl into v_cl from test_unicode where cid=3;
                end
                '.
sess execute.
answer := sess answer.
answer := sess answer.

“Calling the procedure with non-Null values.”

sess := conn getSession.
sess prepareCall: '{ call testBindUnicode42(:outClob)}'.
sess bindVariable: #outClob value: '00000000' kind: #out.
sess execute.
answer := sess answer.
answer := sess answer.
sess bindVariable: #outClob.

Finally, we’ll demonstrate how to use Unicode characters in a “mixed” mode: (Please note, in this example, some Chinese characters are used too)

“Connect to DB2 without initial Unicode encoding.”

conn := DB2Connection new.
conn      username: 'username';
                password: 'password';
                environment: 'DB2'.
conn connect.

“Drop the test table if existed.”

sess := conn getSession.
sess prepare: 'drop table test_unicode'.
sess execute.
sess answer.
sess answer.

“Create the test table.”

sess := conn getSession.
sess prepare:  'create table test_unicode (cid integer, cc char(100), cuc graphic(100), cname varchar(100), cname1 VARGRAPHIC(100), cl CLOB(8m), ncl DBCLOB(4m))';
                execute;
     answer;
                answer.

“Insert some test data.”

sess := conn getSession.
sess prepare:   'insert into test_unicode values(?, ?, ?, ?, ?, ?, ?)';
bindTemplate: #(nil nil #UnicodeString nil #UnicodeString nil #UnicodeString);
bindInput: #(2 'ab' '中国' 'abcd' '中国各省' 'cdef' '中国人民');
                execute;
     answer;
     answer.

“Retrieve the test data.”

sess := conn getSession.
sess prepare: 'select * from test_unicode' ;
                execute.
ans := sess answer.
res := ans upToEnd inspect.
sess answer.

“Test Stored Procedures.”

“Delete the test data.”

sess := conn getSession.
sess prepare: 'delete from test_unicode' ;
                execute.
ans := sess answer.
sess answer.

“Drop the procedure if existed.”

sess := conn getSession.
sess prepare: 'DROP PROCEDURE testBindUnicode'.
sess execute.
answer := sess answer.
sess answer.

“Create a test procedure.”

sess := conn getSession.
sess prepare: 'CREATE PROCEDURE testBindUnicode(cid int, cc char(100), cuc graphic(100), cname varchar(100), cname1 vargraphic(100), cl clob, ncl dbclob)
language SQL
                begin
                          insert into test_unicode values (cid, cc, cuc, cname, cname1, cl, ncl);
                end
                '.
sess execute.
answer := sess answer.
sess answer.

“Calling the procedure with non-Null values.”

sess := conn getSession.
sess prepareCall: '{ call testBindUnicode(?, ?, ?, ?, ?, ?, ?)}'.
sess bindTemplate: #(nil nil #UnicodeString nil #UnicodeString nil #UnicodeString).
sess bindValue: 3 at: 1.
sess bindValue:  'ab' at: 2.
sess bindValue:  '中国' at: 3.
sess bindValue: 'abc' at: 4.
sess bindValue: '中国各省' at: 5.
sess bindValue: 'edf' at: 6.
sess bindValue: '中国人民' at: 7.
sess execute.
answer := sess answer.
answer := sess answer.

“Retrieve the test data.”

sess := conn getSession.
sess prepare: 'select * from test_unicode' ;
                execute.
ans := sess answer.
res := ans upToEnd inspect.
sess answer.

“Drop the procedure if existed.”

sess := conn getSession.
sess prepare: 'DROP PROCEDURE testBindUnicode2'.
sess execute.
answer := sess answer.
answer := sess answer.

“Create a test procedure.”

sess := conn getSession.
sess prepare: 'CREATE PROCEDURE testBindUnicode2(out name varchar(100))
language SQL
                begin
                          select cname into name from test_unicode where cid=3;
                end
                '.
sess execute.
answer := sess answer.
answer := sess answer.

“Calling the procedure with non-Null values.”

sess := conn getSession.
sess prepareCall: '{ call testBindUnicode2(:outName)}'.
sess bindVariable: #outName value: '00000000' kind: #out.
sess execute.
answer := sess answer.
answer := sess answer.
sess bindVariable: #outName.

“Drop the procedure if existed.”

sess := conn getSession.
sess prepare: 'DROP PROCEDURE testBindUnicode21'.
sess execute.
answer := sess answer.
answer := sess answer.

“Create a test procedure.”

sess := conn getSession.
sess prepare: 'CREATE PROCEDURE testBindUnicode21(out name varchar(100))
language SQL
                begin
                         select cname1 into name from test_unicode where cid=3;
                end
                '.
sess execute.
answer := sess answer.
answer := sess answer.

“Calling the procedure with non-Null values.”

sess := conn getSession.
sess prepareCall: '{ call testBindUnicode21(:outName)}'.
sess bindTemplate: #(#UnicodeString).
sess bindVariable: #outName value: '00000000' kind: #out.
sess execute.
answer := sess answer.
answer := sess answer.
sess bindVariable: #outName.

“Drop the procedure if existed.”

sess := conn getSession.
sess prepare: 'DROP PROCEDURE testBindUnicode3'.
sess execute.
answer := sess answer.
answer := sess answer.

“Create a test procedure.”

sess := conn getSession.
sess prepare: 'CREATE PROCEDURE testBindUnicode3(in v_cid int, out name varchar(100))
language SQL
                begin
                         select cname into name from test_unicode where cid=v_cid;
                end
                '.
sess execute.
answer := sess answer.
answer := sess answer.

“Calling the procedure with non-Null values.”

sess := conn getSession.
sess prepareCall: '{ call testBindUnicode3(:inCid, :outName)}'.
sess bindVariable: #inCid value: 3 kind: #in.
sess bindVariable: #outName value: '00000000' kind: #out.
sess execute.
answer := sess answer.
answer := sess answer.
sess bindVariable: #outName.

“Drop the procedure if existed.”

sess := conn getSession.
sess prepare: 'DROP PROCEDURE testBindUnicode31'.
sess execute.
answer := sess answer.
answer := sess answer.

“Create a test procedure.”

sess := conn getSession.
sess prepare: 'CREATE PROCEDURE testBindUnicode31(in v_cid int, out name varchar(100))
language SQL
                begin
                         select cname1 into name from test_unicode where cid=v_cid;
                end
                '.
sess execute.
answer := sess answer.
answer := sess answer.

“Calling the procedure with non-Null values.”

sess := conn getSession.
sess prepareCall: '{ call testBindUnicode31(:inCid, :outName)}'.
sess bindTemplate: #(nil #UnicodeString).
sess bindVariable: #inCid value: 3 kind: #in.
sess bindVariable: #outName value: '00000000' kind: #out.
sess execute.
answer := sess answer.
answer := sess answer.
sess bindVariable: #outName.

“Drop the procedure if existed.”

sess := conn getSession.
sess prepare: 'DROP PROCEDURE testBindUnicode4'.
sess execute.
answer := sess answer.
answer := sess answer.

“Create a test procedure.”

sess := conn getSession.
sess prepare: 'CREATE PROCEDURE testBindUnicode4(in v_cid int, out v_cl clob)
language SQL
                begin
                          select cl into v_cl from test_unicode where cid=v_cid;
                end
                '.
sess execute.
answer := sess answer.
answer := sess answer.

“Calling the procedure with non-Null values.”

sess := conn getSession.
sess prepareCall: '{ call testBindUnicode4(:inCid, :outClob)}'.
sess bindVariable: #inCid value: 3 kind: #in.
sess bindVariable: #outClob value: '00000000' kind: #out.
sess execute.
answer := sess answer.
answer := sess answer.
sess bindVariable: #outClob.

“Drop the procedure if existed.”

sess := conn getSession.
sess prepare: 'DROP PROCEDURE testBindUnicode41'.
sess execute.
answer := sess answer.
answer := sess answer.

“Create a test procedure.”

sess := conn getSession.
sess prepare: 'CREATE PROCEDURE testBindUnicode41(out v_cl clob)
language SQL
                begin
                          select cl into v_cl from test_unicode where cid=3;
                end
                '.
sess execute.
answer := sess answer.
answer := sess answer.

“Calling the procedure with non-Null values.”

sess := conn getSession.
sess prepareCall: '{ call testBindUnicode41(:outClob)}'.
sess bindVariable: #outClob value: '00000000' kind: #out.
sess execute.
answer := sess answer.
answer := sess answer.
sess bindVariable: #outClob.

“Drop the procedure if existed.”

sess := conn getSession.
sess prepare: 'DROP PROCEDURE testBindUnicode42'.
sess execute.
answer := sess answer.
answer := sess answer.

“Create a test procedure.”

sess := conn getSession.
sess prepare: 'CREATE PROCEDURE testBindUnicode42(out v_cl dbclob)
language SQL
                begin
                           select ncl into v_cl from test_unicode where cid=3;
                end
                '.
sess execute.
answer := sess answer.
answer := sess answer.

“Calling the procedure with non-Null values.”

sess := conn getSession.
sess prepareCall: '{ call testBindUnicode42(:outClob)}'.
sess bindTemplate: #(#UnicodeString).
sess bindVariable: #outClob value: '00000000' kind: #out.
sess execute.
answer := sess answer.
answer := sess answer.
sess bindVariable: #outClob.