cancel
Showing results for 
Search instead for 
Did you mean: 

Upgraded DB SQLANY17 UCA/UTF8, writing long nvarchar string to proxy table, data is converted to UTF-16 Little Endian on proxy table

SVS_CH
Explorer
0 Kudos
991

Hi,

we upgraded a database from version 11 to version 17 build 17.0.11.7672 and from windows1252-charset to UTF8-charset and we come across some problems writing data to a new proxy database (also version 17 build 17.0.11.7672).

Both databases version 17 are using UTF8-charset and UCA-collation.

When inserting a string into a remote column with datatype "long nvarchar", the data is converted on the proxy table into "UTF-16 Little Endian" (this is what Notepad++ is saying) and the data is corrupted for our work. Previously we used the database version 11 (windows1252-charset and 1252LATIN1-collation) to connect to database version 17 proxy and there were no problems inserting long nvarchar strings.

Please follow the parts to reproduce the problem:

--new database version 17 build 17.0.11.7672 (UTF8 / UCA)

create or replace table testSQLANY17_UCA(
    testval long nvarchar not null,
    insertTimestamp timestamp not null default current timestamp
);

--Upgraded database version 17 build 17.0.11.7672 (UTF8 / UCA)

declare local temporary table #a(testval long nvarchar);
declare @txt long nvarchar;

drop table if exists remote_testSQLANY17_UCA;
create existing table remote_testSQLANY17_UCA AT 'SQLANY17_ODBC;;DBA;testSQLANY17_UCA';

set @txt = 'ThisIsOneTestStringWith30Chars';

insert into #a with auto name select @txt as testval;
insert into remote_testSQLANY17_UCA with auto name select @txt as testval;

select testval,length(testval) from #a;
select top 5 testval,length(testval), cast(csconvert(testval,'nchar_charset','utf16') as long nvarchar) y, length(y), insertTimestamp from remote_testSQLANY17_uca order by insertTimestamp desc;

After inserting a long nvarchar string with length of 30 chars into the remote and local table the testval in the remote table is getting length of 60 chars and the length in the local table is staying with 30 chars. As you can see the testval in remote table is converted from utf16 to nchar and the length is correct again.

What is wrong now? Is there an option, which we can set to get the old behavior in version 11 or what can we do, so the string in the proxy-table will get the length of 30 chars, like the inserted string?

Many thanks in advance, Christian

regdomaratzki
Product and Topic Expert
Product and Topic Expert
0 Kudos

Would you be able to provide the following information as well?

1) The dbinit or CREATE DATABASE command used to create both databases. If you're not sure, you can run dbunload -n on the database and the CREATE DATABASE command will be posted near the top of the reload.sql file.

2) The full command when you executed "CREATE SERVER SQLANY17_ODBC" to define the remote server.

3) (just in case) The CREATE EXTERNLOGIN command executed for the DBA user on the SQLANY17_ODBC server. Make sure to XXX-out any passwords.

4) The contents of the testSQLANY17_UCA DSN. Make sure to XXX-out any passwords.

Thanks, Reg

VolkerBarth
Contributor
0 Kudos

If you have changed the default CHAR encoding and collation from Windows1252 to UTF8 on both databases (if my understandig is correct), then why are you using NVARCHAR at all? Your (VAR)CHAR columns are now able to store Unicode directly, so I would assume you would simply use VARCHAR...

Note, you should prefix NCHAR string literals with a N, such as "N'My test string'";

SVS_CH
Explorer
0 Kudos

Hi Reg, thanks a lot for your quick response. Following my answers to your points:

1. Main-DB CREATE DATABASE command: CREATE DATABASE 'D:\\SQLANY17_NCHAR\\MD070\\SQLANY17\\svs.db' LOG ON 'D:\\SQLANY17_NCHAR\\MD070\\SQLANY17\\svs.log' PAGE SIZE 8192 COLLATION 'UCA(CaseSensitivity=Ignore;AccentSensitivity=Ignore;PunctuationSensitivity=Primary)' NCHAR COLLATION 'UCA(CaseSensitivity=Ignore;AccentSensitivity=Ignore;PunctuationSensitivity=Primary)' BLANK PADDING OFF JCONNECT ON CHECKSUM ON DBA USER '' DBA PASSWORD '' SYSTEM PROC AS DEFINER ON

Proxy-DB CREATE DATABASE command: CREATE DATABASE 'D:\\Databases\\SYB17ProxyTest_UTF8\\svs.db' LOG ON 'D:\\Databases\\SYB17ProxyTest_UTF8\\svs.log' PAGE SIZE 8192 COLLATION 'UCA(CaseSensitivity=Ignore;AccentSensitivity=Ignore;PunctuationSensitivity=Primary)' NCHAR COLLATION 'UCA(CaseSensitivity=Ignore;AccentSensitivity=Ignore;PunctuationSensitivity=Primary)' BLANK PADDING OFF JCONNECT ON CHECKSUM ON DBA USER '' DBA PASSWORD '' SYSTEM PROC AS DEFINER ON

2. CREATE SERVER "SQLANY17_ODBC" CLASS 'SAODBC' USING 'SQLANY17_ODBC';

3. Main-DB CREATE EXTERNLOGIN "DBA" TO "SQLANY17_ODBC" REMOTE LOGIN 'remoteServer_ExternalLogin' IDENTIFIED BY 'xxx'; Proxy-DB CREATE USER "remoteServer_ExternalLogin" IDENTIFIED BY 'xxx';

4. [HKEY_LOCAL_MACHINE\\SOFTWARE\\ODBC\\ODBC.INI\\SQLANY17_ODBC] "Driver"="C:\\PROGRA~1\\SQLANY~3\\Bin64\\dbodbc17.dll" "UID"="dba" "PWD"="xxx" "DatabaseName"="svs_syb17_proxy_test_utf8" "ServerName"="svs_syb17_proxy_test_utf8" "Integrated"="NO" "Host"="192.168.0.160:2799"

After creating the database we changed the following db-options. I am not sure, if this is important to mention.

set option public.string_rtruncation = 'Off'; set option public.ansi_substring = 'Off'; set option public.remote_idle_timeout = '720'; set option public.continue_after_raiserror = 'Off'; set option public.extern_login_credentials = 'Login_user';

Thanks again for your work here,

Christian

SVS_CH
Explorer

Hi, just want to let you know that this was recognized as an error by SAP. This error will be fixed in SAP SQL Anywhere version 17.0 Build 7801 (Engineering Case 829485).

Best regards Christian

Accepted Solutions (0)

Answers (0)