Adobe Community: Message List - Incorrect string value

Re: Incorrect string value

forums_noreply@adobe.com — Tue, 01 Jul 2014 11:00:17 GMT

Thanks. Good to know. Please mark it as the correct answer.

Re: Incorrect string value

forums_noreply@adobe.com — Tue, 01 Jul 2014 09:04:17 GMT

turns out the issue was the charset in db was set to latin_sweedish

i changed it to utf8_general_ci and that sorted it.

there was one additional twist to this which i solved by looking here MySQL Bugs: #33475: Incorrect string value when inserting unicode codepoint \xC2\x92

the key part being at the bottom "

[9 Dec 2011 0:07] Arkadiy Kulev

This problem solves easily. Don't forget to not only set the database, table and collation to utf8, BUT THE COLUMNS ALSO!

That's what cause the problem for me. I created the table in latin, then switch to utf8, but forgot to also change the columns."

that caught me out but got there in the end.

HTH anyone else who has this.

Re: Incorrect string value

forums_noreply@adobe.com — Mon, 09 Jun 2014 18:05:27 GMT

I have answered your question in your other thread.

Re: Incorrect string value

forums_noreply@adobe.com — Mon, 09 Jun 2014 17:27:34 GMT

It is now available.

Re: Incorrect string value

forums_noreply@adobe.com — Mon, 09 Jun 2014 15:20:54 GMT

How is it now? just got an email to say post has gone live

Re: Incorrect string value

forums_noreply@adobe.com — Mon, 09 Jun 2014 14:40:16 GMT

It is unauthorized.

Re: Incorrect string value

forums_noreply@adobe.com — Mon, 09 Jun 2014 14:34:22 GMT

Thanks Dan - seems ok from my end, think that's because it's a post I've put in here (getting started forum) that hasn't yet been approved for publication by the moderators - hopefully link will work shortly..

Re: Incorrect string value

forums_noreply@adobe.com — Mon, 09 Jun 2014 14:24:46 GMT

Your link broke.

Re: Incorrect string value

forums_noreply@adobe.com — Mon, 09 Jun 2014 14:19:06 GMT

FYR I am hoping that this How to turn this Java into something I can use in CF? will eventually provide anyone who's interested with a way to remove special characters from a string before inserting in the db

Re: Incorrect string value

forums_noreply@adobe.com — Thu, 29 May 2014 13:49:56 GMT

There are thousands of special characters, and infinitely many combinations of them. To be practical and efficient, therefore, you have to focus on removing just the ones that are troublesome.

Re: Incorrect string value

forums_noreply@adobe.com — Thu, 29 May 2014 13:34:27 GMT

Hi again - yes I know but that only removes that one particular special character; I believe there are quite a few that cause problems if stored in the db. I'm wondering if there is a function that removes all the various troublesome special characters in one go. Failing that I wonder if there is a list of all these characters that i can loop through (using the replace as you say)?..

Re: Incorrect string value

forums_noreply@adobe.com — Thu, 29 May 2014 13:22:20 GMT

I suggested one, single line for the job: <cfset myString = replace(myString,chr(8203),"","all")>

Re: Incorrect string value

forums_noreply@adobe.com — Thu, 29 May 2014 12:46:55 GMT

Thanks Dan - yes that was what I was hoping to do (filter out the characters before inserting to the db). I'm just trying to find the most efficient way to remove those characters; I had been using safetext.cfm but that seems a bit out of date now.. http://www.cflib.org/udf/SafeText

It'd be great if there was an inbuilt cf function like RemoveOddCharacters() for use on strings before inserting them into dbs - maybe there is one.. anyone?

Thanks

Nick

Re: Incorrect string value

forums_noreply@adobe.com — Mon, 26 May 2014 19:11:21 GMT

You could filter out the characters as I suggested, before storing the string in the database.

Re: Incorrect string value

forums_noreply@adobe.com — Mon, 26 May 2014 10:26:28 GMT

Thanks Dan.

Is there an inbuilt cf function I can use to strip out any weird characters like that do you happen to know? Or alternatively maybe I need to tell my database (mysql) not to be so fussy and accept those chrs?

Hope you're well - thanks for the help, much appreciated.

Nick

Re: Incorrect string value

forums_noreply@adobe.com — Wed, 14 May 2014 09:29:38 GMT

The Hex value 0xE2 0x80 0x8B is the representation in UTF-8 of the 'zero-width space' character. You may remove it from the string - for example, by replacing it with a blank space - before the string enters the query.

Assuming that chr(8203) represents the zero-width character, you could replace it as follows:

Incorrect string value

forums_noreply@adobe.com — Mon, 12 May 2014 13:25:46 GMT

Hi - we've just upgraded from cf8 to cf9 and are getting a fair few of these errors - "Error Executing Database Query. Incorrect string value: '\xE2\x80\x8BWe ...' for column" as we try to insert a row in the db.

The query is inserting the following string "We are seeking a" - nothing innocuous in the text and certainly no instance of this string '\xE2\x80\x8B' (despite what the error message says).

Has anyone else run into this, any ideas what's going on?

Thanks very much for any help you can offer as I'm completely stuck.

Thank you.

Kind regards,

Nick