I played around with ML 16.0 and found something strange. SA16.0 with ebf 1512 Produkter DbSrv16.exe, Mlsrv16.exe, Dbsync.exe, Ultralite. Converting a system that works from 10,12 to version 16.0.0. The system works in 10 and 12 but when synchronizing data between x64 server and x64 ml client columns that contains Swedish characters is marked as errors in MobiLink. It seems like Swedish chars are expanded to multicharater bytes so the columns are some bytes larger than defined and this is detected by ML and reported as an error. It’s not the Swedish chars themselves it’s the width of the raw column before shown because I have other columns containg Swedish chars and they work fine. %PATH%\ulinit -p "4096" -z "1252SWEFIN" -S "0" --max_hash_size=4 --timestamp_increment=1 -y %ULFILE% ... create table utCampaign ( CampaignCode char(10) NOT NULL , Description varchar(30) NULL , StartDate datetime NULL , StartingWeek integer NULL , EndingDate datetime NULL , NumberSeries char(10) NULL , UpdateDate datetime NULL , primary key ( CampaignCode)); Column with error : Description varchar(30) … I. 2013-05-24 10:07:45. <8> Insert/Update row [utCampaign]: I. 2013-05-24 10:07:45. <8> 410904 I. 2013-05-24 10:07:45. <8> ICA Nära, v 25-26 2012 I. 2013-05-24 10:07:45. <8> 2012-06-18 00:00:00.000000 I. 2013-05-24 10:07:45. <8> 25 I. 2013-05-24 10:07:45. <8> 2012-07-01 00:00:00.000000 I. 2013-05-24 10:07:45. <8> I. 2013-05-24 10:07:45. <8> 2012-06-02 10:12:31.872000 E. 2013-05-24 10:07:45. <8> [-10038] A downloaded value for table 'utCampaign' (column #2) was either too big or invalid for the remote schema type I. 2013-05-24 10:07:45. <8> Insert/Update row [utCampaign]: I. 2013-05-24 10:07:45. <8> 410906 I. 2013-05-24 10:07:45. <8> Coop Nord Konsum,Nära,v21 2012 I. 2013-05-24 10:07:45. <8> 2012-05-21 00:00:00.000000 I. 2013-05-24 10:07:45. <8> 21 I. 2013-05-24 10:07:45. <8> 2012-05-27 00:00:00.000000 I. 2013-05-24 10:07:45. <8> I. 2013-05-24 10:07:45. <8> 2012-06-02 10:12:31.887000 I. 2013-05-24 10:07:45. <8> Error Context: I. 2013-05-24 10:07:45. <8> Remote ID: 145fb4d0-d591-4602-bcb4-fdddd0762721 I. 2013-05-24 10:07:45. <8> User Name: 12:22: I. 2013-05-24 10:07:45. <8> Modified User Name: 12:22: I. 2013-05-24 10:07:45. <8> Transaction: Download Kindly SQL Anywhere Information Utility Version 12.0.1.3873 Page size : 4096 Encrypted : No Strings padded with blanks for comparisons: No CHAR collation sequence: 1252LATIN1(CaseSensitivity=Ignore) CHAR character set encoding: windows-1252 NCHAR collation sequence: UCA (CaseSensitivity=Ignore;AccentSensitivity=Ignore;PunctuationSensitivity=Primary) NCHAR character set encoding: UTF-8 Database checksums enabled: Yes Encrypted tables supported: No The string that is marked wrong is exactly 30 chars wide ... Coop Nord Konsum,Nära,v21 2012 CREATE TABLE "DBA"."tCampaign" ( "CompanyCode" "CompanyCode" NOT NULL, "CampaignCode" "CodeType" NOT NULL, "Description" "DescriptionShort" NULL, "StartDate" "DateType" NULL, "StartingWeek" INTEGER NULL, "EndingDate" "DateType" NULL, "NumberSeries" "NumberSeries" NULL, "UpdateDate" "DateType" NULL, PRIMARY KEY ( "CompanyCode" ASC, "CampaignCode" ASC ) ) IN "system"; CREATE DOMAIN "DescriptionShort" VARCHAR(30); ALTER PROCEDURE "DBA"."sync_dl_tCampaign" (@last_download timestamp default timestamp) RESULT ( CampaignCode CHAR(10), Description VARCHAR(30), StartDate TIMESTAMP, StartingWeek INTEGER, EndingDate TIMESTAMP, NumberSeries CHAR(10), UpdateDate TIMESTAMP ) BEGIN SELECT "CampaignCode", "Description", "StartDate", "StartingWeek", "EndingDate", "NumberSeries", "UpdateDate" FROM tCampaign WHERE CompanyCode = @FTID AND UpdateDate >= @last_download and endingdate > DATEADD( year, -1, current timestamp) END The working system is 10.0.1 and 12 is not working ... this is the result on 12 ... I. 2013-06-19 12:54:11. <1>This must be an error anyway ... What happens if I stuff 30 'Ä' in a varchar(30) ? what will then be the length in ul of the column ... I tried inserting the following in ultralite from sql central insert into utCampaign(CampaignCode,Description,StartDate,StartingWeek,EndingDate,NumberSeries,UpdateDate) values ( 'ROW1','ÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄ','2013-06-18',10,'2013-06-28',1.0,GETDATE()); and a select CampaignCode,Description,BYTE_LENGTH(description) from utCampaign returns CampaignCode,Description, 'ROW1','ÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄ',30 The string is 15 chars and insert into utCampaign(CampaignCode,Description,StartDate,StartingWeek,EndingDate,NumberSeries,UpdateDate) values ( 'ROW2','ÄÄÄ','2013-06-18',10,'2013-06-28',1.0,GETDATE()); CampaignCode,Description, 'ROW2','ÄÄÄ',6 The string is 3 but the length is said 6 !! chars so there must be some problem with Swedish chars or something ... SQL Anywhere Information Utility Version 12.0.1.3873 Log mirror: none Page size : 4096 Encrypted : No Strings padded with blanks for comparisons: No CHAR collation sequence: 1252LATIN1(CaseSensitivity=Ignore) CHAR character set encoding: windows-1252 NCHAR collation sequence: UCA (CaseSensitivity=Ignore;AccentSensitivity=Ignore;PunctuationSensitivity=Primary) NCHAR character set encoding: UTF-8 Database checksums enabled: No Encrypted tables supported: No SQL Anywhere UltraLite Information Utility Version 12.0.1.3873 Collation sequence: 1252SWEFIN Character set encoding: UTF8 Page size: 4096 Default index maximum hash size: 4 Checksum level: 0 Global database ID: 2147483647 Global autoincrement usage: 0% MobiLink Remote ID: null Encryption: None Respect letter-case when comparing: No ('A' equal to 'a') Date format: YYYY-MM-DD Date order: YMD Nearest century: 50 Numeric precision: 30 Numeric scale: 6 Time format: HH:NN:SS.SSS Timestamp format: YYYY-MM-DD HH:NN:SS.SSS Timestamp with time zone format: YYYY-MM-DD HH:NN:SS.SSS+HH:NN Timestamp increment: 1 Database has not yet been synchronized asked 18 Jun '13, 01:29 ogunnars Volker Barth |
Both UL and SQL Anywhere use BYTE semantics for CHAR and VARCHAR types by default. That means that a VARCHAR(30) column can hold up to 30 bytes. If a character is multibyte, fewer characters can be stored in a byte semantic VARCHAR than its defined size. In your example of the character Ä, it appears that it requires 2 bytes and that means a VARCHAR(30) column can host 15 Ä characters - a change between v10 and v11 and later. You can either increase the length in bytes or use character semantics for the column as in VARCHAR( 30 CHAR ) or VARCHAR( 30 CHARACTERS ). answered 19 Jun '13, 13:44 Chris Keating 1
In addition to what Chris said, here's some interesting reading...
(20 Jun '13, 07:34)
Breck Carter
|
So in ultralite i need to double my varchar(30) to varchar(60)as the varchar(30 char) gives me an error ? answered 24 Jun '13, 07:34 ogunnars Comment Text Removed
Yes. Ultralite does not support character-length semantics so you can't code VARCHAR ( 30 CHARACTER ). Generally speaking it doesn't hurt to declare larger-than-necessary maximum lengths because the actual length of each column value determines how much space is allocated. There are some second-order effects on the optimizer etc, if the maximum length reaches 255, and then again if LONG VARCHAR is used, but for ordinary strings the "need 30? give it 100!" approach works fine.
(24 Jun '13, 09:04)
Breck Carter
Replies hidden
1
Note that it can take up to 4 bytes for some UTF-8 characters, so the safest UL equivalent for VARCHAR( n CHARACTER ) is VARCHAR( 4n ).
(24 Jun '13, 10:14)
Graham Hurst
|
I can see that 1252SWEFIN is the collation of the UltraLite database. What is the collation of the SQL Anywhere consolidated database?
For the second column in utCampaign, what is the data type on the SQL Anywhere side? It's a VARCHAR(30) on UltraLite.
In UL, the query select BYTE_LENGTH( 'Coop Nord Konsum,Nära,v21 2012' ) returns 31 but returns 30 from an SQL Anywhere engine. This is why this error is being reported. But it is odd that UL shows this length also in v12. I can also see this behaviour in v12. Can you run the SELECT noted above on your working environment and get the ULINFO and DBINFO outputs from the working and failing environments.