Forums

Find answers, ask questions, and connect with our
community all around the world.

Home Forum Omnis forum Omnis Studio with DB2 and unicode

  • Omnis Studio with DB2 and unicode

    Posted by Rudolf Bargholz on October 18, 2018 at 2:32 pm

    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 replied 3 years, 10 months ago 2 Members · 4 Replies
  • 4 Replies
  • asmith

    Administrator
    October 18, 2018 at 3:04 pm

    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

    Member
    October 19, 2018 at 8:08 am

    Hi Andrew,
    Thank you for responding. I will put together a detailed description and contact support.
    Regards
    Rudolf

  • Rudolf Bargholz

    Member
    October 19, 2018 at 9:55 am

    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

    Administrator
    October 19, 2018 at 11:49 am

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

Log in to reply.