Mobilink strange behäviour ...

[Former Member]

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
/Ola Gunnars

---

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> 

I. 2013-06-19 12:54:11. <1>   2012-06-04 11:30:11.514000
E. 2013-06-19 12:54:11. <1> [-10038] A downloaded value for table 'utCampaign' (column #2) was either too big or invalid for the remote schema type
I. 2013-06-19 12:54:11. <1> Insert/Update row [utCampaign]:
I. 2013-06-19 12:54:11. <1>   410963
I. 2013-06-19 12:54:11. <1>   Coop Nord Konsum+Nära,v26 2012
I. 2013-06-19 12:54:11. <1>   2012-06-25 00:00:00.000000
I. 2013-06-19 12:54:11. <1>   26
I. 2013-06-19 12:54:11. <1>   2012-07-01 00:00:00.000000
I. 2013-06-19 12:54:11. <1> 

I. 2013-06-19 12:54:11. <1>   2012-06-04 11:30:11.577000
I. 2013-06-19 12:54:11. <1> Error Context:
I. 2013-06-19 12:54:11. <1> Remote ID: aaf5e6bd-e44e-441d-9d11-a7e7ecca3760
I. 2013-06-19 12:54:11. <1> User Name: 12:22:
I. 2013-06-19 12:54:11. <1> Modified User Name: 12:22:
I. 2013-06-19 12:54:11. <1> Transaction: download
I. 2013-06-19 12:54:11. <1> Table Name: utCampaign

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

[jeff_albion]

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.

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.

[Breck_Carter]

It's better to edit your original question to add information, rather than posting it as an answer.

You can also post new information as a comment.

[chris_keating]

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.

[chris_keating]

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 ).

[Breck_Carter]

In addition to what Chris said, here's some interesting reading...

What's new in version 12.0.0 - CHAR, NCHAR, NVARCHAR, and VARCHAR data types support 32767 character...

VARCHAR [ ( max-length [ CHAR | CHARACTER ] ) ]

[Former Member]

So in ultralite i need to double my varchar(30) to varchar(60)as the varchar(30 char) gives me an error ?

[Breck_Carter]

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.

[Former Member]

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 ).