O$ - 6.0 - Unicode $insert error.

Doug Easterbrook doug at artsman.com
Thu Mar 12 13:32:14 EDT 2015


hi Andrew:

at the risk of ... well anything.

the two strings are NOT the same.   The one in omnis is unicode.   The one you pasted into SQL workbench may have been cleansed by the clipboard and LOOK the same, but realyl isnt



and 255 unicode characters will generally not fit into an ASCII 255 character string.



its one of the reasons we define our encoding of the database in postgres to be UTF8 (I know you are using mysql) -- and with the encoding of the strings and the encoding of the database -- and the way the dam works --- everything is good.  255 characters goes into 255 characters.   Never had a problem.


so,

you could have a dam problem -- or
you could have a database encoding problem --or
you could have a problem with the dam --or
you could have a mysql bug -or
you could have a mysql feature (eg, it might be tracking a bom marker on the text string -- if it notices it is unicode -- I don't know)


don't know which....

but just note that, as a developer, you have to think at the byte level when dealing with data.     and that copy/paste affects the data as the various tools (which think at the byte level) may be fixing things for you.


so shorten the string in omnive by lopping off a character at a time to find out how many characters you can actually stuff into 255 mysql string.

once you find the length you can save...  it might illuminate what your issue is.



Doug Easterbrook
Arts Management Systems Ltd.
mailto:doug at artsman.com
http://www.artsman.com
Phone (403) 536-1205    Fax (403) 536-1210

> On Mar 12, 2015, at 9:54 AM, Andrew Stolarz <stolarz at gmail.com> wrote:
> 
> Hi Bas,
> 
> 
> Thank you for your response, however I’m not too sure we are talking about
> the same “issue” here. Below is a sample full string of text (255
> characters in length)…less the quotes.
> 
> “This is a test – 2 acre this is a test this is a test this is a test this
> is a test this is a test this is a test this is a test this is a test this
> is a test this is a test this is a test this is a test this is a test this
> is a test this is a test this i”
> 
> 
> When I try to use this string of text and manually run a SQL update
> directly from within MySQL Workbench, I do not have any issues and I am
> able to insert the data without any problems.
> 
> When I try to use the same string of text and allow Omnis to insert it into
> the MySQL database using the $insert()  command as mentioned previously, I
> get the error.
> 
> 
> Why does this work outside of Omnis, but not with Omnis?
> 
> 
> Andrew
> 
> 
> 
> On 9 March 2015 at 17:47, Bastiaan Olij <bastiaan at basenlily.me> wrote:
> 
>> 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
>> 
>> _____________________________________________________________
>> Manage your list subscriptions at http://lists.omnis-dev.com
>> 
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com



More information about the omnisdev-en mailing list