Largest Provider of Commercial Smalltalk
Cincom is one of the largest commercial providers of Smalltalk, with twice as many customers and partners as other commercial providers.

Tom Nies

Get Started

Menu

ODBC Unicode Support in Cincom® VisualWorks® and Cincom® ObjectStudio®

Posted on by Author Yuwei Li in Categories Cross-Platform, ObjectStudio, VisualWorks, Windows-Centric

In VisualWorks 7.9 and ObjectStudio 8.4, Cincom has added full Unicode Support to their ODBC 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 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, and 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.  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.
  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 and insert and retrieved Strings will be treated as Unicode.

ODBC 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, we use some Chinese characters, and all of the examples work with SQL Server):

“Connect to SQL Server with initial Unicode encoding.”

conn := ODBCConnection new.
conn encoding: #UTF16.
conn      username: 'username';
               password: 'password';
               environment: 'UnicodeDSN'.
conn connect.

“Drop the test table if existed.”

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

“Create the test table.”

sess := conn getSession.
sess prepare:  'create table test_unicode (cid int, cc char(100), cuc nchar(100), cname varchar(100), cname1 nvarchar(100), cl varchar(max), ncl nvarchar(max))';
               execute;
     answer.

“Insert some test data.”

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

“Insert second row of test data.”

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

“Retrieve the test data.”

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

“Drop the procedure if existed.”

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

“Create an insert procedure.”

sess := conn getSession.
sess prepare: 'CREATE PROCEDURE testBindUnicode @cid int, @cc char(100), @cuc nchar(100), @cname varchar(100), @cname1 nvarchar(100), @cl varchar(max), @ncl nvarchar(max)  AS
               insert into test_unicode values (@cid, @cc, @cuc, @cname, @cname1, @cl, @ncl)
               '.
sess execute.
answer := sess answer.
answer := sess answer.

“Calling the insert procedure.”

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

“Retrieve the test data to verify.”

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

“Drop the procedure if existed.”

sess := conn getSession.
sess prepare: 'DROP PROC testBindUnicode1'.
sess execute.
answer := sess answer.
answer := sess answer.

“Create a test procedure.”

sess := conn getSession.
sess prepare: 'CREATE PROCEDURE testBindUnicode1 @cname varchar(100) OUTPUT AS
               select @cname=cname from test_unicode where cid=1
               return
               '.
sess execute.
answer := sess answer.
answer := sess answer.

“Calling the procedure with non-Null values.”

sess := conn getSession.
sess preparePROC: '{ call testBindUnicode1(?)}'.
"There is no need to provide a bind template since the encoding in Unicode."
sess bindVariable: '00000000' at: 1.
sess execute.
answer := sess answer.
answer := sess answer.

“Get the value in the variable.”

sess bindVariableAt: 1.

“Drop the procedure if existed.”

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

“Create a test procedure.”

sess := conn getSession.
sess prepare: 'CREATE PROCEDURE testBindUnicode2 @cname1 nvarchar(100) OUTPUT AS
               select @cname1=cname1 from test_unicode where cid=1
               return
               '.
sess execute.
answer := sess answer.
answer := sess answer.

“Calling the procedure.”

sess := conn getSession.
sess preparePROC: '{ call testBindUnicode2(?)}'.
"There is no need to provide a bind template since the encoding in Unicode."
sess bindVariable: '00000000' at: 1.
sess execute.
answer := sess answer.
answer := sess answer.
"Get the value in the variable."
sess bindVariableAt: 1.

“Drop the procedure if existed.”

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

“Create a select procedure.”

sess := conn getSession.
sess prepare: 'CREATE PROCEDURE testBindUnicode3 AS
               select * from test_unicode
               '.
sess execute.
answer := sess answer.
answer := sess answer.

“Calling the select procedure.”

sess := conn getSession.
sess preparePROC: '{ call testBindUnicode3}'.
sess execute.
answer := sess answer.
res := answer upToEnd.

Second, we’ll demonstrate how to use Unicode characters in a “mixed” mode. Please note, in this example, we’ll use the procedure created in the previous one, and, we’ll also use some Chinese characters):

“Connect to SQL Server without initial Unicode encoding.”

conn := ODBCConnection new.
conn      username: 'username';
               password: 'password';
               environment: 'nonUnicodeDSN'.
conn connect.

“Drop the test table if existed.”

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

“Create the test table.”

sess := conn getSession.
sess prepare:  'create table test_unicode (cid int, cc char(100), cuc nchar(100), cname varchar(100), cname1 nvarchar(100), cl varchar(max), ncl nvarchar(max))';
               execute;
     answer.

“Insert some test data.”

sess := conn getSession.
sess prepare: 'insert into test_unicode values(?, ?, ?, ?, ?, ?, ?)'.
"Bind template is necessary since the main encoding is not Unicode."
sess bindTemplate: #(#Integer #String #UnicodeString #String #UnicodeString #String #UnicodeString).
sess bindInput: #(1 'ab' '€1' 'cd' '€2' 'efg' '€123').
sess execute.
sess answer.

“Insert another row of test data.”

sess := conn getSession.
sess prepare: 'insert into test_unicode values(?, ?, ?, ?, ?, ?, ?)'.
"Bind template is necessary since the main encoding is not Unicode."
sess bindInput: #(2 'ab' '中国' 'cd' '中国人民' 'efg' '中国各省') template: #(nil nil #UnicodeString nil #UnicodeString nil #UnicodeString).
sess execute.
sess answer.

“Retrieve the test data.”

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

“Calling the insert procedure.”

sess := conn getSession.
sess preparePROC: '{ 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: 'cd' at: 4.
sess bindValue: '中国人民' at: 5.
sess bindValue: 'efg' at: 6.
sess bindValue: '中国各省' at: 7.
sess execute.
answer := sess answer.
answer := sess answer.

“Calling the procedure.”

sess := conn getSession.
sess preparePROC: '{ call testBindUnicode1(?)}'.
sess bindVariable: '00000000' at: 1.
sess execute.
answer := sess answer.
answer := sess answer.
"Get the value in the variable."
sess bindVariableAt: 1.

“Calling the procedure.”

sess := conn getSession.
sess preparePROC: '{ call testBindUnicode2(?)}'.
"Bind template is necessary since the main encoding is not Unicode."
sess bindTemplate: #(#UnicodeString).
sess bindVariable: '00000000' at: 1.
sess execute.
answer := sess answer.
answer := sess answer.
"Get the value in the variable."
sess bindVariableAt: 1.

“Calling the select procedure.”

sess := conn getSession.
sess preparePROC: '{ call testBindUnicode3}'.
sess execute.
answer := sess answer.
res := answer upToEnd.

ODBC Unicode Support Examples in ObjectStudio

In ObjectStudio, the ODBC wrapper provides APIs at both connection and database levels.  In the following examples, we’ll show you how to use Unicode characters at both levels.

Let’s start from a full Unicode example at the connection level.  In the example, we’ll demonstrate how to use Unicode characters in insert, retrieval and in procedures (please note, in the code examples, we use some Chinese characters):

ODBCConnection defaultEncoding: #UTF16.
conn := ODBCConnection connect: #测试 uid: #username pwd: #password.

“Drop the test table if existed.”

(stmt := conn addStatement) notNil ifTrue: [
"Prepare the statement."
stmt prepare: 'drop table test_unicode'.
stmt execute.
].

“Create a test table.”

(stmt := conn addStatement) notNil ifTrue: [
"Prepare the statement."
stmt prepare:'create table test_unicode (cid int, cc char(100), cuc nchar(100), cname varchar(100), cname1 nvarchar(100), cl varchar(max), ncl nvarchar(max))'.
stmt execute.
].

“Insert first row of test data.”

(stmt := conn addStatement) notNil ifTrue: [

“Prepare the statement.”

stmt prepare: 'insert into test_unicode values(1, ''€'', ''中国'', ''€1'', ''中国人民'', ''€2'', ''中国各省'')'.
stmt execute.
].

“Insert second row of data using host variables.”

“Open a statement within the scope of the connection.”

(stmt := conn addStatement).

“Prepare the statement.”

stmt prepare: 'insert into test_unicode values (2, ?, ?, ?, ?, ?, ?)'.

“Get and set binding parameters.”

“Get the first parameter.”

p1 := stmt describeParam: 1.

“Set parameter value.”

p1 value: '€'.

“Set parameter description.”

stmt setParamDescr: 1 to: p1.

“Get the second parameter.”

p2 := stmt describeParam: 2.

“Set parameter value.”

p2 value: '中国'.

“Set parameter description.”

stmt setParamDescr: 2 to: p2.

“Get the third parameter.”

p3 := stmt describeParam: 3.

“Set parameter value.”

p3 value: '€1'.

“Set parameter description.”

stmt setParamDescr: 3 to: p3.

“Get the fourth parameter.”

p4 := stmt describeParam: 4.

“Set parameter value.”

p4 value: '中国人民'.

“Set parameter description.”

stmt setParamDescr: 4 to: p4.

“Get the fifth parameter.”

p5 := stmt describeParam: 5.

“Set parameter value.”

p5 value: '€2'.

“Set parameter description.”

stmt setParamDescr: 5 to: p5.

“Get the sixth parameter.”

p6 := stmt describeParam: 6.

“Set parameter value.”

p6 value: '中国妇女干部'.

“Set parameter description.”

stmt setParamDescr: 6 to: p6.

“Send the statement to the data source for execution.”

stmt execute.

“Retrieve the test data to verify”

stmt := conn addStatement.

“Prepare the statement.”

stmt prepare: 'select * from test_unicode'.

“Assign a cursor name to the statement (optional).”

stmt setCursorName: #测试.

“Get the cursor name back.”

stmt getCursorName.
stmt execute.

“fetch the records and send it to Transcript.”

[ stmt fetch = 0 ] whileTrue: [
               stmt recordAsArray out.
].

“Test calling procedures.”

“Delete the existing records in the test table.”

(stmt := conn addStatement) notNil ifTrue: [
"Prepare the statement."
stmt prepare: 'delete from test_unicode'.
stmt execute.
].

“Drop the insert procedure if existed.”

(stmt := conn addStatement) notNil ifTrue: [

“Prepare the statement.”

stmt prepare:'DROP PROC testBindUnicode'.
stmt execute.
].

“Create an insert procedure.”

(stmt := conn addStatement) notNil ifTrue: [

“Prepare the statement.”

stmt prepare:  'CREATE PROCEDURE testBindUnicode @cid int, @cc char(100), @cuc nchar(100), @cname varchar(100), @cname1 nvarchar(100), @cl varchar(max), @ncl nvarchar(max)  AS
               insert into test_unicode values (@cid, @cc, @cuc, @cname, @cname1, @cl, @ncl)
               '.
stmt execute.
].

“Calling the insert procedure.”

(stmt := conn addStatement).

“Prepare the statement.”

stmt prepare:'{ call testBindUnicode(1, ?, ?, ?, ?, ?, ?)}'. "Get and set binding parameters."

“Get the first parameter.”

p1 := stmt describeParam: 1.

“Set parameter value.”

p1 value: '€'.

“Set parameter description.”

stmt setParamDescr: 1 to: p1.

“Get the second parameter.”

p2 := stmt describeParam: 2.

“Set parameter value.”

p2 value: '中国'.

“Set parameter description.”

stmt setParamDescr: 2 to: p2.

“Get the third parameter.”

p3 := stmt describeParam: 3.

“Set parameter value.”

p3 value: '€1'.

“Set parameter description.”

stmt setParamDescr: 3 to: p3.

“Get the fourth parameter.”

p4 := stmt describeParam: 4.

“Set parameter value.”

p4 value: '中国人民'.

“Set parameter description.”

stmt setParamDescr: 4 to: p4.

“Get the fifth parameter.”

p5 := stmt describeParam: 5.

“Set parameter value.”

p5 value: '€2'.

“Set parameter description.”

stmt setParamDescr: 5 to: p5.

“Get the sixth parameter.”

p6 := stmt describeParam: 6.

“Set parameter value.”

p6 value: '中国各省'.

“Set parameter description.”

stmt setParamDescr: 6 to: p6.

“Send the statement to the data source for execution.”

stmt execute.

“Retrieve the test data to verify”

stmt := conn addStatement.

“Prepare the statement.”

stmt prepare: 'select * from test_unicode'.
stmt execute.

“fetch the records and send it to Transcript.”

[ stmt fetch = 0 ] whileTrue: [
stmt recordAsArray out.
].

“Drop the procedure if existed.”

(stmt := conn addStatement) notNil ifTrue: [
"Prepare the statement."
stmt prepare:'DROP PROC testBindUnicode1'.
stmt execute.
].

“Create the procedure.”

(stmt := conn addStatement) notNil ifTrue: [

“Prepare the statement.”

stmt prepare: 'CREATE PROCEDURE testBindUnicode1 @cname varchar(100) OUTPUT AS
select @cname=cname from test_unicode where cid=1
return
'.
stmt execute.
].

“Call the procedure.”

(stmt := conn addStatement).

“Prepare the statement.”

stmt prepare:'{ call testBindUnicode1(?)}'.

“Prepare the output parameter.”

p1 := stmt describeParam: 1.

“Set parameterinital value.”

p1 value: '11111111'.

“Set parameter description.”

stmt setParamDescr: 1 to: p1 output: true.

“Send the statement to the data source for execution.”

stmt execute.

“Make sure there is no more results.”

rc := stmt moreResults == 100 ifTrue: [
"Check the parameter OUTPUT value."
p1 value out.
].

“Drop the procedure if existed.”

(stmt := conn addStatement) notNil ifTrue: [

“Prepare the statement.”

stmt prepare:'DROP PROC testBindUnicode2'.
stmt execute.
].

“Create the procedure.”

(stmt := conn addStatement) notNil ifTrue: [

“Prepare the statement.”

stmt prepare:'CREATE PROCEDURE testBindUnicode2 @cname1 nvarchar(100) OUTPUT AS
select @cname1=cname1 from test_unicode where cid=1
return
'.
stmt execute.
].

“Call the procedure.”

(stmt := conn addStatement).

“Prepare the statement.”

stmt prepare:'{ call testBindUnicode2(?)}'.

“Prepare the output parameter.”

p1 := stmt describeParam: 1.

“Set parameter value.”

p1 value: '1111111111'.

“Set parameter description.”

stmt setParamDescr: 1 to: p1 output: true.

“Send the statement to the data source for execution.”

stmt execute.

“Make sure there is no more results.”

rc := stmt moreResults == 100 ifTrue: [

“Check the parameter OUTPUT value.”

p1 value out.
].

“Drop the select procedure if existed.”

(stmt := conn addStatement) notNil ifTrue: [

“Prepare the statement.”

stmt prepare:'DROP PROC testBindUnicode3'.
stmt execute.
].

“Create the select procedure.”

(stmt := conn addStatement) notNil ifTrue: [

“Prepare the statement.”

stmt prepare:'CREATE PROCEDURE testBindUnicode3 AS
select * from test_unicode
'.
stmt execute.
].

“Call the select procedure.”

(stmt := conn addStatement).

“Prepare the statement.”

stmt prepare:'{ call testBindUnicode3}'.

“Check whether describe parameter function is supported by a driver.”

“Send the statement to the data source for execution.”

stmt execute.
[ stmt fetch = 0 ] whileTrue: [
stmt recordAsArray out.
].

“Disconnect the connection”

conn disconnect.

The second example demonstrates the usage in “mixed” mode at the connection level:

“Establish a connection to a data source with initial encoding set to nil, so the connection will get #default as encoding.”

ODBCConnection defaultEncoding: nil.
conn := ODBCConnection connect: #nonUnicodeDSN uid: #username pwd: #password.

“Drop the test table if existed.”

(stmt := conn addStatement) notNil ifTrue: [

“Prepare the statement.”

stmt prepare: 'drop table test_unicode'.
stmt execute.
].

“Create a test table.”

(stmt := conn addStatement) notNil ifTrue: [

“Prepare the statement.”

stmt prepare:'create table test_unicode (cid int, cc char(100), cuc nchar(100), cname varchar(100), cname1 nvarchar(100), cl varchar(max), ncl nvarchar(max))'.
stmt execute.
].

“Insert first row of data using host variables.”

“Open a statement within the scope of the connection.”

(stmt := conn addStatement).

“Prepare the statement.”

stmt prepare: 'insert into test_unicode values (2, ?, ?, ?, ?, ?, ?)'.

“Get and set binding parameters.”

“Get the first parameter.”

p1 := stmt describeParam: 1.

“Set parameter value.”

p1 value: 'aa'.

“Set parameter description.”

stmt setParamDescr: 1 to: p1.

“Get the second parameter.”

p2 := stmt describeParam: 2.

“Set parameter value.”

p2 value: '中国'.

“Set parameter description.”

stmt setParamDescr: 2 to: p2.

“Get the third parameter.”

p3 := stmt describeParam: 3.

“Set parameter value.”

p3 value: 'bb'.

“Set parameter description.”

stmt setParamDescr: 3 to: p3.

“Get the fourth parameter.”

p4 := stmt describeParam: 4.

“Set parameter value.”

p4 value: '中国人民'.

“Set parameter description.”

stmt setParamDescr: 4 to: p4.

“Get the fifth parameter.”

p5 := stmt describeParam: 5.

“Set parameter value.”

p5 value: 'cc'.

“Set parameter description.”

stmt setParamDescr: 5 to: p5.

“Get the sixth parameter.”

p6 := stmt describeParam: 6.

“Set parameter value.”

p6 value: '中国各省'.

“Set parameter description.”

stmt setParamDescr: 6 to: p6.

“Send the statement to the data source for execution.”

stmt execute.

“Retrieve the test data to verify”

stmt := conn addStatement.

“Prepare the statement.”

stmt prepare: 'select * from test_unicode'.

“Assign a cursor name to the statement (optional).”

stmt setCursorName: #cursorname1.

“Get the cursor name back.”

stmt getCursorName.
stmt execute.

“fetch the records and send it to Transcript.”

[ stmt fetch = 0 ] whileTrue: [
stmt recordAsArray out.
].

“Test calling procedures.”

“Delete the existing records in the test table.”

(stmt := conn addStatement) notNil ifTrue: [

“Prepare the statement.”

stmt prepare: 'delete from test_unicode'.
stmt execute.
].

“Drop the insert procedure if existed.”

(stmt := conn addStatement) notNil ifTrue: [

“Prepare the statement.”

stmt prepare:'DROP PROC testBindUnicode'.
stmt execute.
].

“Create an insert procedure.”

(stmt := conn addStatement) notNil ifTrue: [

“Prepare the statement.”

stmt prepare:  'CREATE PROCEDURE testBindUnicode @cid int, @cc char(100), @cuc nchar(100), @cname varchar(100), @cname1 nvarchar(100), @cl varchar(max), @ncl nvarchar(max)  AS
insert into test_unicode values (@cid, @cc, @cuc, @cname, @cname1, @cl, @ncl)
'.
stmt execute.
].

“Calling the insert procedure.”

(stmt := conn addStatement).

“Prepare the statement.”

stmt prepare:'{ call testBindUnicode(1, ?, ?, ?, ?, ?, ?)}'. "Get and set binding parameters."

“Get the first parameter.”

p1 := stmt describeParam: 1.

“Set parameter value.”

p1 value: 'aa'.

“Set parameter description.”

stmt setParamDescr: 1 to: p1.

“Get the second parameter.”

p2 := stmt describeParam: 2.

“Set parameter value.”

p2 value: '中国'.

“Set parameter description.”

stmt setParamDescr: 2 to: p2.

“Get the third parameter.”

p3 := stmt describeParam: 3.

“Set parameter value.”

p3 value: 'bb'.

“Set parameter description.”

stmt setParamDescr: 3 to: p3.

“Get the fourth parameter.”

p4 := stmt describeParam: 4.

“Set parameter value.”

p4 value: '中国人民'.

“Set parameter description.”

stmt setParamDescr: 4 to: p4.

“Get the fifth parameter.”

p5 := stmt describeParam: 5.

“Set parameter value.”

p5 value: 'cc'.

“Set parameter description.”

stmt setParamDescr: 5 to: p5.

“Get the sixth parameter.”

p6 := stmt describeParam: 6.

“Set parameter value.”

p6 value: '中国各省'.

“Set parameter description.”

stmt setParamDescr: 6 to: p6.

“Send the statement to the data source for execution.”

stmt execute.

“Close the statement.”

stmt close.

“Retrieve the test data to verify”

stmt := conn addStatement.

“Prepare the statement.”

stmt prepare: 'select * from test_unicode'.
stmt execute.

“fetch the records and send it to Transcript.”

[ stmt fetch = 0 ] whileTrue: [
stmt recordAsArray out.
].

“Close the statement.”

stmt close.

“Drop the procedure if existed.”

(stmt := conn addStatement) notNil ifTrue: [

“Prepare the statement.”

stmt prepare:'DROP PROC testBindUnicode1'.
stmt execute.
].

“Create the procedure.”

(stmt := conn addStatement) notNil ifTrue: [

“Prepare the statement.”

stmt prepare: 'CREATE PROCEDURE testBindUnicode1 @cname varchar(100) OUTPUT AS
select @cname=cname from test_unicode where cid=1
return
'.
stmt execute.
].

“Call the procedure.”

(stmt := conn addStatement).

“Prepare the statement.”

stmt prepare:'{ call testBindUnicode1(?)}'.

“Prepare the output parameter.”

p1 := stmt describeParam: 1.

“Set parameterinital value.”

p1 value: '11111111'.

“Set parameter description.”

stmt setParamDescr: 1 to: p1 output: true.

“Send the statement to the data source for execution.”

stmt execute.

“Make sure there is no more results.”

rc := stmt moreResults == 100 ifTrue: [

“Check the parameter OUTPUT value.”

p1 value out.
].

“Drop the procedure if existed.”

(stmt := conn addStatement) notNil ifTrue: [

“Prepare the statement.”

stmt prepare:'DROP PROC testBindUnicode2'.
stmt execute.
].

“Create the procedure.”

(stmt := conn addStatement) notNil ifTrue: [

“Prepare the statement.”

stmt prepare:'CREATE PROCEDURE testBindUnicode2 @cname1 nvarchar(100) OUTPUT AS
select @cname1=cname1 from test_unicode where cid=1
return
'.
stmt execute.
].

“Call the procedure.”

(stmt := conn addStatement).

“Prepare the statement.”

stmt prepare:'{ call testBindUnicode2(?)}'.

“Prepare the output parameter.”

p1 := stmt describeParam: 1.

“Set parameter value.”

p1 value: '1111111111'.

“Set parameter description.”

stmt setParamDescr: 1 to: p1 output: true.

“Send the statement to the data source for execution.”

stmt execute.

“Make sure there is no more results.”

rc := stmt moreResults == 100 ifTrue: [

“Check the parameter OUTPUT value.”

p1 value out.
].

“Drop the select procedure if existed.”

(stmt := conn addStatement) notNil ifTrue: [

“Prepare the statement.”

stmt prepare:'DROP PROC testBindUnicode3'.
stmt execute.
].

“Create the select procedure.”

(stmt := conn addStatement) notNil ifTrue: [

“Prepare the statement.”

stmt prepare:'CREATE PROCEDURE testBindUnicode3 AS
select * from test_unicode
'.
stmt execute.
].

“Call the select procedure.”

(stmt := conn addStatement).

“Prepare the statement.”

stmt prepare:'{ call testBindUnicode3}'.
"Check whether describe parameter function is supported by a
driver."

“Send the statement to the data source for execution.”

stmt execute.
[ stmt fetch = 0 ] whileTrue: [
stmt recordAsArray out.
].

“Disconnect the connection”

conn disconnect.

The third example shows the usage in Unicode mode at the database level:

” Logon to the database server.”

ODBCDatabase defaultEncoding: #UTF16.
ODBCDatabase logOnServer: #unicodeDSN user: #username password: #password.

“Get access to the database needed.”

db := ODBCDatabase accessName: #uni_sql.
db execSql: 'drop table test_unicode'.

“Create a test table.”

res := db execSql: 'create table test_unicode (cid int, cc char(100), cuc nchar(100), cname varchar(100), cname1 nvarchar(100), cl varchar(max), ncl nvarchar(max))'.

“Insert first row of test data.”

res := db execSql: 'insert into test_unicode values(0, ''€'', ''中国'', ''€1'', ''中国人民'', ''€2'', ''中国各省'')'.

“Insert second 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.

“Insert third 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.

“Insert fourth 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.

“Retrieve the data to verify.”

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 nchar(100), @cname varchar(100), @cname1 nvarchar(100), @cl varchar(max), @ncl nvarchar(max)  AS
insert into test_unicode values (@cid, @cc, @cuc, @cname, @cname1, @cl, @ncl)
'.

“Create the binding parameter array with initial values.”

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

“Execute the procedure.”

resultTables := db execProc: '{call testBindUnicode(1, ?, ?, ?, ?, ?, ?)}' params: vars.

“verify the data inserted.”

db execSql: 'select * from test_unicode'.

“Drop the stored procedure if it exists.”

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

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

db execSql: 'CREATE PROCEDURE testBindUnicode1 @cname varchar(100) OUTPUT AS
select @cname=cname from test_unicode where cid=1
return
'.

“Create the binding parameter array with initial values.”

vars := {'test123456'}.

“Create the binding parameter option array indicating whether the

parameters are for INPUT or OUTPUT."
outputParamArray := {true}.

“Create an error block.”

errorBlock := [ :error | ^ error asMessage. ].

“Execute the procedure.”

resultTable := db execProc: '{call testBindUnicode1(?)}' params: vars outputParams: outputParamArray onError: errorBlock.

“Verify the input and output parameter values.”

vars 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 @cname1 nvarchar(100) OUTPUT AS
select @cname1=cname1 from test_unicode where cid=1
return
'.

“Create the binding parameter array with initial values.”

vars := {'test1234567890'}.

“Create the binding parameter option array indicating whether the

parameters are for INPUT or OUTPUT."
outputParamArray := {true}.

“Create an error block.”

errorBlock := [ :error | ^ error asMessage. ].

“Execute the procedure.”

resultTable := db execProc: '{call testBindUnicode2(?)}' params: vars outputParams: outputParamArray onError: errorBlock.

“Verify the input and output parameter values.”

vars out.

“Drop the stored procedure if it exists.”

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

“Create a select stored procedure.”

db execSql: 'CREATE PROCEDURE testBindUnicode3 AS
select * from test_unicode
'.

“Execute the procedure.”

resultTable := db execProc: '{call testBindUnicode3}' params: {} outputParams: {} onError: errorBlock.

The final example shows the usage in “mixed” mode at the database level:

” Logon to the database server.”

ODBCDatabase defaultEncoding: nil.
ODBCDatabase logOnServer: #nonUnicodeDSN user: #username password: #password.

“Get access to the database needed.”

db := ODBCDatabase accessName: #ylidb1.

“Drop the test table if existed.”

db execSql: 'drop table test_unicode'.

“Create a test table.”

res := db execSql: 'create table test_unicode (cid int, cc char(100), cuc nchar(100), cname varchar(100), cname1 nvarchar(100), cl varchar(max), ncl nvarchar(max))'.

“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.

“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.

“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: '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.

“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 nchar(100), @cname varchar(100), @cname1 nvarchar(100), @cl varchar(max), @ncl nvarchar(max)  AS
insert into test_unicode values (@cid, @cc, @cuc, @cname, @cname1, @cl, @ncl)
'.

“Create the binding parameter array with initial values.”

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

“Execute the procedure.”

resultTables := db execProc: '{call testBindUnicode(1, ?, ?, ?, ?, ?, ?)}' params: vars.

“verify the data inserted.”

db execSql: 'select * from test_unicode'.

“Drop the stored procedure if it exists.”

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

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

db execSql: 'CREATE PROCEDURE testBindUnicode1 @cname varchar(100) OUTPUT AS
select @cname=cname from test_unicode where cid=1
return
'.

“Create the binding parameter array with initial values.”

vars := {'test123456'}.

“Create the binding parameter option array indicating whether the

parameters are for INPUT or OUTPUT."
outputParamArray := {true}.

“Create an error block.”

errorBlock := [ :error | ^ error asMessage. ].

“Execute the procedure.”

resultTable := db execProc: '{call testBindUnicode1(?)}' params: vars outputParams: outputParamArray onError: errorBlock.

“Verify the input and output parameter values.”

vars 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 @cname1 nvarchar(100) OUTPUT AS
select @cname1=cname1 from test_unicode where cid=1
return
'.

“Create the binding parameter array with initial values.”

vars := {'test1234567890'}.

“Create the binding parameter option array indicating whether the

parameters are for INPUT or OUTPUT."
outputParamArray := {true}.

“Create an error block.”

errorBlock := [ :error | ^ error asMessage. ].

“Execute the procedure.”

resultTable := db execProc: '{call testBindUnicode2(?)}' params: vars outputParams: outputParamArray onError: errorBlock.

“Verify the input and output parameter values.”

vars out.

“Drop the stored procedure if it exists.”

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

“Create a select stored procedure.”

db execSql: 'CREATE PROCEDURE testBindUnicode3 AS
select * from test_unicode
'.

“Execute the procedure.”

resultTable := db execProc: '{call testBindUnicode3}' params: {} outputParams: {} onError: errorBlock.

Related posts:

  1. How to Use SQL Server Temporary Tables with Cincom® ObjectStudio® ODBC Wrapper In SQL Server, temporary tables are created in tempdb.  They are backed by physical disk and are even logged into the transaction log. They act like regular tables in that […]...
  2. ODBC 3.0 Support in Cincom® VisualWorks® For historical reasons, most APIs that are used in VisualWorks’ ODBCEXDI belong to ODBC 1.0 and ODBC 2.0. ...
  3. DB2 Unicode Support in Cincom® ObjectStudio® and Cincom® VisualWorks® 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....
  4. Oracle Unicode Support in Cincom® ObjectStudio® and Cincom® VisualWorks® In VisualWorks 7.9 and ObjectStudio 8.4, Cincom has added full Unicode support to their Oracle connects. ...