How to avoid garbage character for following situation when use the character set UTF-8 in Web system? (From DBMR 1847)
1. Web AP insert/select UTF-8 data.
2. Web AP insert data and update the data by Ms Access and so on.
3. MS Access or dmSQL insert data into DB and Web AP select the data.
1. Web AP operating UTF-8 data is no problem, DBMaker only support U16LE.
2. If storing data as nclob/nvarchar/nchar, customer can use MS Access to access data by ODBC Driver Manager (MS Driver Manager internal is U16LE).
3. User can create column type as nclob/nvarchar/nchar to store Unicode data, but should translate from U8 <=> U16LE by themselves for insert/fetch.
4. before inserting data by dmSQL it should convert data to Unicode first, then use 'xxxx'u to insert data into database. It means They can not directly insert UTF-8 data by dmSQL, before inserting, should convert UTF-8 to UTF-16, and then insert UTF-16 data.
There are two methods:
A).DBMaker will provide customer UDF - U8toU16/U16toU8 to help user to translate data, but they should declare column type as nclob/nchar/nvarchar.
select u8tou16(c1) from t1;
The data will be correct and no garbage character; dmSQL cannot see the readable data.
B). Like SQL Server, store UTF8 data into binary column type, but cannot use db search function (like...etc), this is SQL Server solution.
Using UDF should modify their program, but use binary to store does not modify.