O$ - 6.0 - Unicode $insert error.

Andrew Stolarz stolarz at gmail.com
Thu Mar 12 11:54:31 EDT 2015


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
>



More information about the omnisdev-en mailing list