SA9 to SA11 migration: right truncation of string data.

[Former Member]

I'm trying to migrate an SA9 database to version 11 (maybe I'll change to 12). I did unload at 9 using external options. When I run reload.sql I get "right truncation of string data" in several tables. Ok, that's fine and it is a expected behavior. But I need to solve that setting bigger varchar type for my columns.

My question is: what's the easiest way to know what columns is generation this error?

I'm planning to write some app, that select column size of syscolumns table and compares it with select max(length(column)) but it will be my last shoot.

Thanl you

[VolkerBarth]

So you are saying the data was valid in v9 and is too long in v11? (Otherwise I would not know why a an error should occur.) Wouldn't that imply that the schema is different in both databases, and you just have to compare that?

[VolkerBarth]

Another quick shot: Have you tried to set option string_rtruncation to off (the v9 default)?

[Former Member]

I'm saying that my data/database is valid in v9 and isn't when I try to reload it in v11.

I'm planning to not set string_rtrunction to off since I want to correct my schema. Set this option, in my mind, is a work-around.

[VolkerBarth]

@Zote: I think of string_rtruncation as a workaround, too. However, it might help you to find out about the offending values. In my experience, it's wayyy easier to find out which values are longer than expected after they have been (provisorically) imported compared to diagnosing reload errors...

[VolkerBarth]

@Zote: I should add that setting the option to off here is only useful when doing data exports from the v9 and the provisorically loaded v11 db and compare which data has changed by the silent truncation. Otherwise, you just won't notice which values have been truncated, and that won't help much:)

[VolkerBarth]

@Zote: Another attempt may be useful when only a few tables are affected: Import into a temporary table with same schema, but (much) wider columns and then find out about the offending lines. That's somewhat manual work, I admit:(

[MarkCulp]

If the schemas have not changed and the data has not changed then there should be no error. Since you are getting an error I am thinking that perhaps there is an uncorrect character set conversion happening during one of your steps and hence some strings are appearing as longer. Can you confirm that all steps have used the correct character set? BTW: Have not tried doing an upgrade to v11 directly from the v9 database using the v11 s/w?

[Former Member]

@Mark, didn't try using direct upgrade. v9 db is using iso_1 as character set encoding and iso1latin1 as collation sequence. v11 is utf-8 and utf8bin. But in my mind, it should not afftect it since I think length should be "number of characters" and not "number of bytes". I'll try again changing charset of v11.

[graeme_perrow]

I have nothing to add, I just wanted to say that I enjoyed the irony of the "right-truncation-of-strin" tag.

[Former Member · Accepted solution]

I have change v11 database character set encoding to iso_1 and collation sequence iso1latin1 (same settings that v9 db) and it solves my problem.

It is an expected behavior? (read my reply to Mark in question comments)

[MarkCulp · Accepted solution]

Yes, this is expected behavior. char(n) and varchar(n) declares character strings which are up to n bytes long. SA 10 introduced CHAR length semantics so you can declare CHAR(10) if you want 10 characters (instead of 10 bytes). Read more about this at http://dcx.sybase.com/index.html#1200en/dbreference/char-character-wsqltype1.html

[VolkerBarth · Accepted solution]

@Mark: I guess it should be "CHAR (10 CHAR)" when using character-length semantics.

[MarkCulp · Accepted solution]

@Volker: Correct.... my previous comment should have been: "... you can declare CHAR( 10 CHAR ) if you want 10 characters ...".

[former_SQLA_member1694890]

You can try - SET OPTION PUBLIC.ANSI_SUBSTRING = 'Off'; In this case the behavior of the SUBSTRING function is the same as in previous releases of SQL Anywhere

[Former Member]

example: 1> select cast(Number as varchar(10)), 2> cast(PropNum as varchar(10)), 3> cast(PropName as varchar(25)), 4> -- cast(PropDescription as varchar(30)), 5> cast(Value as varchar(25)) 6> from sa_conn_properties() 7> where PropName like '%ncation%' 8> go Number PropNum PropName Value

---

5 492 string_rtruncation On (1 row affected)