Oracle Unicode Support in Cincom® ObjectStudio® and Cincom® VisualWorks®
In ObjectStudio 8.4 and VisualWorks 7.9, Cincom has added full Unicode support to their Oracle connects. From now on, 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 their Oracle functions and procedures.
In the new implementation, we start to use Oracle function OCIEnvNlsCreate, in which users can set the initial client-side character set and national character set for their current environment handle. However, which function to use will be automatically decided based upon what version of Oracle client is loaded. If the loaded Oracle library supports OCIEnvNlsCreate, it will be used; otherwise, OCIEnvInit will be used.
Here is how the new implementation works for you:
- If you don’t set the main encoding to Unicode, it will work as before, and all existing applications will continue to work.
- If you don’t set the main encoding to Unicode but want to use Unicode columns, you can do so as well. However, 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.
- If you set the main encoding to Unicode, no binding template is necessary, and all connect strings, SQL statements, error messages and insert and retrieved Strings will be treated as Unicode.
Oracle Unicode Support in VisualWorks
First, see the following VisualWorks code example:
“Connect to the Oracle database.”
conn := OracleConnection new.
“Set encoding to WE8MSWIN1252”
conn oracleEncodingId: 178.
“Set UTF8 as unicode encoding.”
conn oracleUnicodeEncodingId: 871.
conn
username: ‘username’;
password: ‘password’;
environment: ‘oracleDB’.
conn connect.
From the above example, we can see that oracleEncodingId and oracleUnicodeEncodingId are used to indicate what encodings you want for encoding and unicodeEncoding. Apparently in this particular example, we want the “mixed” usage. Use WE8MSWIN1252 as the main encoding for metadata and normal string columns and UTF8 as encoding for Unicode string columns.
Also, users can set both encoding and unicodeEncoding to Unicode; see the example below:
“Connect to the Oracle database.”
conn := OracleConnection new.
“Set UTF16 as encoding.”
conn oracleEncodingId: 1000.
conn encoding: #utf_16.
“Set UTF16 as unicodeEncoding.”
conn oracleUnicodeEncodingId: 1000.
conn
username: ‘username’;
password: ‘password’;
environment: ‘oracleDB’.
conn connect.
While setting the main encoding ID, users have to set the value of encoding (#utf_16 in the example above) initially as well. The reason is that after the environment handle is created using the Unicode encoding ID, everything (e.g., table and column names, error messages, etc.) exchanged between the client and server will be in Unicode. We have to use that initial encoding to get the right encoding and Unicode encoding names. For the single-byte character set IDs like 178, users don’t have to do it.
Please note: do not set encoding to AL16UTF16 (2000), because AL16UTF16 is the national character set for the server. Use OCI_UTF16ID (1000) instead. Also, when sending data to the Oracle server, you have to take the server-side character set into consideration, otherwise, you may experience data loss. For example, if you send two-byte characters to a server whose database character set only allows a single-byte character, the data will be lost.
Some users may have difficulties in finding the right character set IDs or names. Here are some code samples to find character set names from IDs, and vice-versa:
“Find character set name from an ID”
……
sess := conn getSession.
sess prepare: ‘SELECT NLS_CHARSET_NAME(178) FROM dual’ ;
execute.
ans := sess answer.
ans upToEnd.
……
“Find character set ID from a name”
……
sess := conn getSession.
sess prepare: ‘select NLS_CHARSET_ID(”UTF8”) from dual’ ;
execute.
ans := sess answer.
ans upToEnd.
……
In the following Workspace script, we’ll demonstrate different ways of using Unicode strings and bind templates. (Please note, in the code examples, we use some Chinese characters.)
“Connect to the Oracle database and set initial encoding and unicode encoding IDs.”
conn := OracleConnection new.
“Set UTF16 as main encoding.”
conn oracleEncodingId: 1000.
conn encoding: #utf_16.
“Set UTF16 as unicode encoding.”
conn oracleUnicodeEncodingId: 1000.
conn
username: ‘username’;
password: ‘password’;
environment: ‘oracleDB’.
conn connect.
“Drop the test table if existed.”
sess := conn getSession.
sess prepare: ‘drop table test_unicode’;
execute;
answer.
“Create a test table.”
sess prepare: ‘create table test_unicode (cid number, cc char(100), cuc nchar(100), cname varchar2(100), cname1 nvarchar2(100), cl clob, ncl nclob)’;
execute;
answer.
“Insert test data.”
sess := conn getSession.
sess prepare: ‘insert into test_unicode values(10, ”€”, ”中国”, ”€1”, ”中国人民”, ”€2”, ”中国各省”)’;
execute;
answer.
“Insert test data without using template; all strings will be encoded using the main encoding.”
sess := conn getSession.
sess prepare: ‘insert into test_unicode values(?, ?, ?, ?, ?, ?, ?)’;
bindInput: #(1 ‘a’ ‘€’ ‘b’ ‘€123456’ ‘cc’ ‘€1234567’);
execute;
answer.
“Insert test data using template to indicate which strings are unicode strings. For this example, it is not necessary because the main encoding is UTF16.”
sess := conn getSession.
sess prepare: ‘insert into test_unicode values(?, ?, ?, ?, ?, ?, ?)’;
bindInput: #(1 ‘€’ ‘中国’ ‘€1’ ‘中国人民’ ‘€2’ ‘中国各省’) template: #(nil nil #UnicodeString nil #UnicodeString nil #UnicodeString);
execute;
answer.
“Retrieve the test data.”
sess := conn getSession.
sess prepare: ‘select * from test_unicode’ ;
execute.
ans := sess answer.
ans upToEnd.
“Test LOB retrieval and updates.”
conn begin.
“Retrieve the test data.”
sess := conn getSession.
sess answerLobAsProxy.
sess prepare: ‘select * from test_unicode where cid = 10 for update’ ;
execute.
ans := sess answer.
res := ans upToEnd.
nclob := (res at: 1) at: 7.
“See the original value.”
nclob readAll.
“Update the LOB value.”
nclob writeFrom: 1 with: (‘€1234567’ asByteArrayEncoding: conn unicodeEncoding).
conn commit.
“Verify the change.”
sess := conn getSession.
sess prepare: ‘select * from test_unicode’ ;
execute.
ans := sess answer.
ans upToEnd.
“The following examples demonstrate the usage of Unicode strings in procedures and functions.”
“Connect to the Oracle database without setting encodings.”
conn := OracleConnection new.
conn
username: ‘username’;
password: ‘password’;
environment: ‘oracleDB’.
conn connect.
“Drop the test table if existed.”
sess := conn getSession.
sess prepare: ‘drop table test_unicode’;
execute;
answer.
“Create a test table.”
sess prepare: ‘create table test_unicode (cid number, cc char(100), cuc nchar(100), cname varchar2(100), cname1 nvarchar2(100), cl clob, ncl nclob)’;
execute;
answer.
“Create a test procedure for inserting a record.”
sess := conn getSession.
sess prepare: ‘create or replace procedure test_insert_unicode (
cid in integer,
cc char,
cuc nchar,
cname varchar2,
cname1 nvarchar2,
cl clob,
ncl nclob
) is
begin
insert into test_unicode values (cid, cc, cuc, cname, cname1, cl, ncl);
end;
‘;
execute;
answer.
“Calling the procedure to insert test data.”
sess := conn getSession.
sess preparePLSQL: ‘
BEGIN
test_insert_unicode(:cid, :cc, :cuc, :cname, :cname1, :cl, :ncl);
END;
‘.
“We have to define a binding template since we want the strings to be encoded differently.”
template := Dictionary new.
template at: #cid put: nil;
at: #cc put: nil;
at: #cuc put: #UnicodeString;
at: #cname put: nil;
at: #cname1 put: #UnicodeString;
at: #cl put: #nil;
at: #ncl put: #UnicodeString.
sess bindTemplate: template.
sess bindVariable: #cid value: 1.
sess bindVariable: #cc value: ‘a’.
sess bindVariable: #cuc value: ‘中国’.
sess bindVariable: #cname value: ‘ab’.
sess bindVariable: #cname1 value: ‘中国人民’.
sess bindVariable: #cl value: ‘abc’.
sess bindVariable: #ncl value: ‘中国各省’.
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.
ans upToEnd.
“Create a test procedure to retrieve part of the data.”
sess := conn getSession.
sess prepare: ‘create or replace procedure test_select_unicode (
vCid in integer,
vCname out varchar2,
vCname1 out nvarchar2
) is
begin
select cname, cname1 into vCname, vCname1 from test_unicode where cid=vCid;
end;
‘;
execute;
answer.
“Calling the retrieval procedure.”
sess := conn getSession.
sess preparePLSQL: ‘
BEGIN
test_select_unicode(:cid, :cname, :cname1);
END;
‘.
“We have to define a binding template since we want the strings to be encoded differently.”
template := Dictionary new.
template at: #cid put: nil;
at: #cname put: nil;
at: #cname1 put: #UnicodeString.
sess bindTemplate: template.
sess bindVariable: #cid value: 1.
sess bindVariable: #cname value: ‘0000000000000000’.
sess bindVariable: #cname1 value: ‘0000000000000000’.
sess execute.
answer := sess answer.
answer := sess answer.
“Verify the retrieved strings.”
normalString := sess bindVariable: #cname.
unicodeString := sess bindVariable: #cname1.
“Function example.”
“Create a function to test.”
sess := conn getSession.
sess prepare: ‘create or replace function testFunction (inID number)
return nvarchar2
is
begin
declare
ret_name nvarchar2(30);
begin
select cname1 into ret_name from test_unicode where cid=inID;
return ret_name;
end;
end;
‘;
execute;
answer.
“Calling the function.”
sess := conn getSession.
sess preparePLSQL: ‘
BEGIN
:res := testFunction(:cid);
END;
‘.
“We have to define a binding template since we want the return value to be encoded correctly.”
template := Dictionary new.
template at: #cid put: nil;
at: #res put: #UnicodeString.
sess bindTemplate: template.
sess bindVariable: #cid value: 1.
sess bindVariable: #res value: ‘0000000000000000’.
sess execute.
answer := sess answer.
answer := sess answer.
“Verify the returned string.”
returnedString := sess bindVariable: #res.
Oracle Unicode Support in ObjectStudio
In ObjectStudio, you have to set initial Oracle encoding IDs to OracleDatabase before connecting.
The following ObjectStudio examples will show you how to use Unicode strings, binding templates in SQL statement executions, procedures and functions:
“A complete ObjectStudio example.”
“Set encoding to WE8MSWIN1252”
OracleDatabase oracleEncodingId: 178.
“Set UTF8 as unicode encoding.”
OracleDatabase oracleUnicodeEncodingId: 871.
“Logon to the Oracle server.”
Res:=OracleDatabase logOnServer: #’OracleDB’ user: #’username’ password: #’password’ alias: #’OracleDB’.
“Get the database instance.”
DB := OracleDatabase accessName: #’OracleDB’.
“Drop the test table if existed.”
Res := DB execSql:’drop table test_unicode’.
“Create the test table.”
Res := DB execSql: ‘create table test_unicode (cid number, cc char(100), cuc nchar(100), cname varchar2(100), cname1 nvarchar2(100), cl clob, ncl nclob)’.
“Insert first row of test data.”
Res := DB execSql: ‘insert into test_unicode values(1, ”a”, ”€”, ”b”, ”€1”, ”cc”, ”€2”)’.
“Insert data using host veriable support.”
SqlString := ‘insert into test_unicode values(?, ?, ?, ?, ?, ?, ?)’.
Vars := {2 ‘a’ ‘€’ ‘b’ ‘€123456’ ‘cc’ ‘€1234567’}.
Res := DB execSql: SqlString vars: Vars.
“Insert Chinese characters using host veriable support.”
SqlString := ‘insert into test_unicode values(?, ?, ?, ?, ?, ?, ?)’.
Vars := {3 ‘a’ ‘中国’ ‘b’ ‘中国人民’ ‘cc’ ‘中国各省’}.
aTemplate := #(nil nil #UnicodeString nil #UnicodeString nil #UnicodeString).
Res := DB execSql: SqlString vars: Vars template: aTemplate.
“Verify the data inserted.”
Res := DB execSql: ‘select * from test_unicode’.
“Testing procedures.”
“Create a procedure to insert a row of data.”
DB execSql: ‘create or replace procedure test_insert_unicode (
cid in integer,
cc char,
cuc nchar,
cname varchar2,
cname1 nvarchar2,
cl clob,
ncl nclob
) is
begin
insert into test_unicode values (cid, cc, cuc, cname, cname1, cl, ncl);
end;
‘.
“Creating a bind template.”
aTemplate := Dictionary new.
aTemplate add: (Association key: #cid value: nil).
aTemplate add: (Association key: #cc value: nil).
aTemplate add: (Association key: #cuc value: #UnicodeString).
aTemplate add: (Association key: #cname value: nil).
aTemplate add: (Association key: #cname1 value: #UnicodeString).
aTemplate add: (Association key: #cl value: nil).
aTemplate add: (Association key: #ncl value: #UnicodeString).
“Execute the insert procedure.”
DB execProc: ‘test_insert_unicode(:cid, :cc, :cuc, :cname, :cname1, :cl, :ncl)’ vars: #(4 ‘€’ ‘中国’ ‘€1’ ‘中国人民’ ‘€2’ ‘中国各省’) template: aTemplate.
“Verify the data inserted.”
Res := DB execSql: ‘select * from test_unicode’.
“Create a procedure to retrieve some of the data.”
DB execSql: ‘ create or replace procedure test_select_unicode (
vCid in integer,
vCname out varchar2,
vCname1 out nvarchar2
) is
begin
select cname, cname1 into vCname, vCname1 from test_unicode where cid=vCid;
end;
‘.
“Creating a bind template.”
aTemplate := Dictionary new.
aTemplate add: (Association key: #cid value: nil).
aTemplate add: (Association key: #cname value: nil).
aTemplate add: (Association key: #cname1 value: #UnicodeString).
“Execute the select procedure.”
resultArray := DB execProc: ‘test_select_unicode(:cid, :cname, :cname1)’ vars: {4 ‘0000000000000000’ ‘0000000000000000’} template: aTemplate paramTypes: { nil #VARCHAR2 #VARCHAR2}.
“Create a function to test.”
res := DB execSql: ‘create or replace function
testFunction(inID number)
return nvarchar2
is
begin
declare
ret_name nvarchar2(30);
begin
select cname1 into ret_name from test_unicode where cid=inID;
return ret_name;
end;
end;
‘.
“Creating a bind template.”
aTemplate := Dictionary new.
aTemplate add: (Association key: #R value: #UnicodeString).
aTemplate add: (Association key: #a value: nil).
“Invoke the function. You should get an array of return values.”
res := DB execFunc: ‘testFunction(:a)’ vars: {‘0000000000000’ 4} template: aTemplate paramTypes: {}.