|
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 |
Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.
Follow this question
By Email:Once you sign in you will be able to subscribe for any updates here
By RSS:Markdown Basics
- *italic* or _italic_
- **bold** or __bold__
- link:[text](http://url.com/ "title")
- image?
- numbered list: 1. Foo 2. Bar
- to add a line break simply add two spaces to where you would like the new line to be.
- basic HTML tags are also supported
Question tags:
question asked: 27 Mar, 09:08
question was seen: 340 times
last updated: 28 Mar, 14:10
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
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'";
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