Omnis Studio with DB2 and unicode

[Rudolf Bargholz Participant]

Hi,

Is there anyone out there that uses any Unicode Version of Omnis Studio with DB2 and a UTF8 database?

I am having problems with the saving of Unicode data in DB2 using bind variables with numerous versions of Omnis Studio since 4.3.2.1, where saving data with bind variables does not save the data in DB2 correctly, using explicit SQL however does save the data correctly, and I would just like to know if anyone has actually gotten this to work reliably in any version of Omnis Studio.

The unicode character I am trying to save is ‘ń’. The correct unicode representation of this value is xC5 xBA. When using bind variables Omnis stores this as xC4 xB9 xE2 x80 x9E in the database.

UPDATE TEST SET TESTCOL = @[MyVar] <- this stores incorrect data on the database
UPDATE TEST SET TESTCOL= ‘ń’ <- this stores the data in the DB2 table correctly

SELECT TESTCOL FROM TESTTABLE <- this fetches the incorrectly stored data and displays the bad unicode unicode database string in Omnis correctly, but the correctly stored data in the database is not fetched correctly

If this really is a bug, I really cannot believe no one has seen this in the past 12 years. Perhaps we are the only ones out there using DB2, or we are doing something wrong. It would help to know that someone is actually not having problems.

Regards

Rudolf Bargholz

[asmith Keymaster]

I think you are the first customer to have reported any issue with this DAM in approximately 12 years, so it is likely that you are the only developer using DB2(!)

If you would care to tell us (by contacting tech support) which database version, and which version of DB2 runtime client you are using, we will try to reproduce this issue.

Have you tried using Studio 8.1.6 (Windows 32 or 64-bit)?

[Rudolf Bargholz Participant]

Hi Andrew,

Thank you for responding. I will put together a detailed description and contact support.

Regards

Rudolf

[Rudolf Bargholz Participant]

Hi Andrew,

I have sent support details how to reproduce the problem. Hopefully I have not missed something obvious in reporting this.

In case someone sees this topic, needs test code, and does not want to redo everything I have done ….

Here code to create a DB2 database and write unicode data to a test table

Create the database

Open the db2cmd, and enter the following to create a new DB2 database called UNITEST:

create database unitest automatic storage yes on ‘c:’ pagesize 4096 autoconfigure using isolation cs apply db only

restart database unitest

connect to UNITEST user db2admin using PasswordHere

get db cfg for unitest

…
Database territory = DE
Database code page = 1208
Database code set = UTF-8
Database country/region code = 49
Database collating sequence = SYSTEM_1252
…

Create the table and insert valid data outside of Omnis

DROP TABLE DB2ADMIN.UNICODETEST;
CREATE TABLE DB2ADMIN.UNICODETEST (TESTCOL VARCHAR(20));

DELETE FROM DB2ADMIN.UNICODETEST;

INSERT INTO DB2ADMIN.UNICODETEST (TESTCOL) VALUES (‘ńą’);

INSERT INTO DB2ADMIN.UNICODETEST (TESTCOL) VALUES (x’C584′ || x’C485′);

SELECT TESTCOL, hex(TESTCOL) FROM DB2ADMIN.UNICODETEST;

Omnis code to write and read unicode data

If db2sess.$logon(hostname,username,password,’UNITEST’)
Else
OK message DB2 {Logon ERROR}
Quit all methods
End If

Do db2sess.$newstatement() Returns db2stat

Do db2stat.$execdirect(” drop table UNICODETEST “)
Do db2stat.$execdirect(” create table UNICODETEST ( TESTCOL VARCHAR(20) ) “)

Do db2stat.$execdirect(” delete from UNICODETEST “)
Do db2stat.$execdirect(” insert into UNICODETEST (TESTCOL) values (‘ńą’) “)

Do row.$definefromsqlclass($schemas.sUNICODETEST)
Do row.$sessionobject.$assign(db2sess)
Do row.$select()
Do row.$fetch()

Calculate hexval as ”
Do db2stat.$execdirect(” select hex(TESTCOL) from UNICODETEST “)
Do db2stat.$fetchinto(hexval)
OK message direct insert WITHOUT bind variables {Insert: ńą//Result: [row.TESTCOL]//HEX: [hexval] (ought to be c584c485)}

Do db2stat.$execdirect(” delete from UNICODETEST “)
Do row.$definefromsqlclass($schemas.sUNICODETEST)
Do row.$sessionobject.$assign(db2sess)
Calculate row.c1 as ‘ńą’
Do row.$insert()

Do row.$definefromsqlclass($schemas.sUNICODETEST)
Do row.$sessionobject.$assign(db2sess)
Do row.$select()
Do row.$fetch()

Calculate hexval as ”
Do db2stat.$execdirect(” select hex(TESTCOL) from UNICODETEST “)
Do db2stat.$fetchinto(hexval)
OK message direct insert WITH bind variables {Insert: ńą//Result: [row.TESTCOL]//HEX: [hexval] (ought to be c584c485)}

Breakpoint

Data written from Omnis is not stored as valid UTF-8 on the database. In some cases Omnis can write data and not read the data written, in others Omnis can read the incorrectly written dta correctly back again.

Regards

Rudolf

[asmith Keymaster]

Thank you very much Rudolf, for taking time to describe everything and for sending to support.

[Author]

Posts