O$ - 6.0 - Unicode $insert error.

Bastiaan Olij bastiaan at basenlily.me
Mon Mar 9 17:47:09 EDT 2015


Hi Andrew,

My guess is that either MySQL is counting the number of bytes of storage
reserved with a character field or that its counting the zero terminator
at the end (making your string 256 characters).

In the first case the field has a maximum length of 255 bytes, not 255
characters. As in UTF-8 characters can be anything from 1 to 6 bytes
your string can be anything from 255 to 1530 characters long. As your
sample data suggest the - character is one of the upper unicode 1 pixel
longer then the standard ascii - because some graphics artist decided
that the extra pixel makes all the difference character, it takes up 2
bytes instead of one.

In the latter case you have to leave room for the zero terminating
character and your storage should thus be 256 characters to hold a 255
character string.

Just out of interest, what is your reason for limiting the number of
characters? In the olden days it made a difference because a 255
character field would take up 256 bytes of memory regardless of which
string you put in there. With todays variable string length storage of
strings there generally isn't a reason to be very strict with the
character size.

Cheers,

Bas

On 10/03/2015 1:38 am, Andrew Stolarz wrote:
> Hello Everyone, I have an urgent issue here
>
>
> I have a schema with a character limit of 255 characters. The MySQL
> database column is 255 characters (varchar). When I try to do an insert
> with 255 characters in omnis, it reports back:
>
>  “Insert Error
> The statement could not be executed. Data too long for column ‘testing’ at
> row 1”
>
>
>
> When I look at the variable in the $insert() command, I can see the text
> and its 255 characters long. (see sample 2 below...only showing first
> snippet of text with the unicode character issue). However what I believe
> is happening is that when its being saved into the database, omnis is
> taking sample 2, and converting it to sample 1…causing the data too long
> error.
>
>
>
> *Sample text in quotes below shortened for simplicity:*
>
> Sample 1: Original text from another column omnis is reading from the
> database: "This is a test – 2 acre"
>
> Sample 2: Whats being shown in the omnis variable to save in $insert()
> command "This is a test – 2 acre"
>
>
>
> I did verify to make sure that the database, tables, and columns are all
> set to “utf8”
>
>
>
> I do have the following set for the MySQL dam:
>
> Calculate tSessionObject.$unicode as kTrue
>
> Calculate tSessionObject.$validateutf8 as kTrue
>
> Calculate tSessionObject.$encoding as kSessionEncodingUtf8
>
> Calculate tSessionObject.$codepage as kUniTypeUTF8
>
>
>
>
> When I manually do an insert into MySQL using mysql workbench with the same
> 255 character string that Omnis is using, I have no issues and it inserts
> the record into the database, leading me to believe that no issues are on
> the MySQL side.
>
>
>
> Any ideas here?
>
>
>
> Andrew
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com
>
>


-- 
Kindest Regards,

Bastiaan Olij
e-mail: bastiaan at basenlily.me
web: http://www.basenlily.me
Skype: Mux213
http://www.linkedin.com/in/bastiaanolij




More information about the omnisdev-en mailing list