[cfaussie] Re: CFMX - special characters

Sarah Atkinson Mon, 06 Jan 2003 16:29:01 -0800

Title: RE: [cfaussie] Re: CFMX - special characters

Thanks for your replies Mark and Grant.

After a bit more poking around, we are beginning to think that it is in fact the MySQL ODBC driver that comes with CFMX which is causing the problem, and not supporting the Unicode.

When using the exact same code with a combo of CFMX and MS SQL 2000 the problem does not arise. However CFMX and MySQL results in problems. Meaning ...

- CFMX is not the problem - as it works fine with MS SQL 2000
- MySQL is not the problem - as it works fine with CF5

... leaving the ODBC driver.

Does this sound like a possible cause? Or should we tackle it from the angle you have outlined Mark?

-----Original Message-----
From: Mark Lynch [mailto:[EMAIL PROTECTED]]
Sent: Monday, 6 January 2003 6:00 PM
To: CFAussie Mailing List
Subject: [cfaussie] Re: CFMX - special characters

This didn't appear when I replied to the email I'll post is manually.

Hi Sarah,

I had similar problems to this and discovered after much heartache that is
Microsoft problem. quel suprise? Is is also due to the different page
encodings and the fact that CFMX correctly supports unicode while CF5 did
not.

What was probably happeing in CF5 was that they chars were being converted
into the Windows Codepage (which is a windows only standard) and contains
some of these extra characters ( in a area of the codepage reserved for
control characters). So when CFMX converts them back is goes, what are
these? I don't know so I'll just output them as '?'.

The fix for this it so go through the database and convert all these chars
from windows codepage to there correct unicode equivalents. I have some
scripts around this but focussed mostly on "Smart Quotes".

This is the guts of the code - very ugly:

function FixMSCrap(cleantext){
        cleantext=replace(cleantext,"#chr(128)#","#chr(8364)#","all");
        cleantext=replace(cleantext,"#chr(130)#","#chr(8218)#","all");
        cleantext=replace(cleantext,"#chr(131)#","#chr(402)#","all");
        cleantext=replace(cleantext,"#chr(132)#","#chr(8222)#","all");
        cleantext=replace(cleantext,"#chr(133)#","#chr(8230)#","all");
        cleantext=replace(cleantext,"#chr(134)#","#chr(8224)#","all");
        cleantext=replace(cleantext,"#chr(135)#","#chr(8225)#","all");
        cleantext=replace(cleantext,"#chr(136)#","#chr(710)#","all");
        cleantext=replace(cleantext,"#chr(137)#","#chr(8240)#","all");
        cleantext=replace(cleantext,"#chr(138)#","#chr(352)#","all");
        cleantext=replace(cleantext,"#chr(139)#","#chr(8249)#","all");
        cleantext=replace(cleantext,"#chr(140)#","#chr(338)#","all");
        cleantext=replace(cleantext,"#chr(142)#","#chr(381)#","all");
        cleantext=replace(cleantext,"#chr(145)#","#chr(8216)#","all");
        cleantext=replace(cleantext,"#chr(146)#","#chr(8217)#","all");
        cleantext=replace(cleantext,"#chr(147)#","#chr(8220)#","all");
        cleantext=replace(cleantext,"#chr(148)#","#chr(8221)#","all");
        cleantext=replace(cleantext,"#chr(149)#","#chr(8226)#","all");
        cleantext=replace(cleantext,"#chr(150)#","#chr(8211)#","all");
        cleantext=replace(cleantext,"#chr(151)#","#chr(8212)#","all");
        cleantext=replace(cleantext,"#chr(152)#","#chr(732)#","all");
        cleantext=replace(cleantext,"#chr(153)#","#chr(8482)#","all");
        cleantext=replace(cleantext,"#chr(154)#","#chr(353)#","all");
        cleantext=replace(cleantext,"#chr(155)#","#chr(8250)#","all");
        cleantext=replace(cleantext,"#chr(156)#","#chr(339)#","all");
        cleantext=replace(cleantext,"#chr(158)#","#chr(382)#","all");
        cleantext=replace(cleantext,"#chr(159)#","#chr(376)#","all");
        cleantext=replace(cleantext,"#chr(160)#"," ","all");
Return cleantext;
}

Mark Lynch
Development Manager
Direct: +61 (2) 9248 4038
Mobile: 0421 050 695

> This message is in MIME format. Since your mail reader does not understand
> this format, some or all of this message may not be legible.
>
> ------_=_NextPart_001_01C2B52A.9E58F880
> Content-Type: text/plain
>
>
> Hi All,
>
> While in the throws of converting an application over to CFMX, we have found
> some niggly SQL issues which didn't previously occur with CF5, in particular
> when dealing with special characters.
>
> 1. Reading Characters from db - Some of the content contained within the db
> is French, therefore contains their native characters. The majority have
> been stored as ascii characters, therefore are not causing grief. But in
> particular the "forward tick" ' is causing problems. CFMX is outputting it
> as a ? due to not recognising it. Does anyone have any suggestions, on the
> fly conversion prior to displaying, or possibly prior to insertion into db.
>
> 3. Writing Characters to the db - as per no. 1 above - the database contains
> special characters, which have been converted into ascii and stored.
> However, when updating these fields and therefore re-setting these values,
> the ascii characters are causing the query to escape. E.g.
>
> update table
> set blah = 'here is my é update enjoy'
>
> ... would cut off and update blah to be 'here is my'. Any ideas on how to
> preserve this?
>
> Hopefully this makes sense and someone can shed some light. I am relaying
> this problem on behalf of another developer, so if more info is required,
> let me know.
>
> Thanks,
> Sarah.
>
> Sarah Atkinson
> ColdFusion Programmer
>
> Lonely Planet Publications, Aust.
> Telephone: +61 3 8379 8000
> Fax: +61 3 8379 8111
> [EMAIL PROTECTED]
>
>
> ------_=_NextPart_001_01C2B52A.9E58F880
> Content-Type: text/html
> Content-Transfer-Encoding: quoted-printable
>
> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
> <HTML>
> <HEAD>
> <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
> charset=3Dus-ascii">
> <META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
> 5.5.2653.12">
> <TITLE>CFMX - special characters</TITLE>
> </HEAD>
> <BODY>
> 
>
> Hi All,
> 
>
> While in the throws of converting an application over =
> to CFMX, we have found some niggly SQL issues which didn't previously =
> occur with CF5, in particular when dealing with special =
> characters.
>
> 1. Reading Characters from db - Some of the content =
> contained within the db is French, therefore contains their native =
> characters. The majority have been stored as ascii characters, =
> therefore are not causing grief. But in particular the "forward =
> tick" ' is causing problems. CFMX is outputting it as a ? due to =
> not recognising it. Does anyone have any suggestions, on the fly =
> conversion prior to displaying, or possibly prior to insertion into =
> db.
>
> 3. Writing Characters to the db - as per no. 1 above =
> - the database contains special characters, which have been converted =
> into ascii and stored. However, when updating these fields and =
> therefore re-setting these values, the ascii characters are causing the =
> query to escape. E.g.
>
> update table
> set blah =3D 'here is my &eacute; update =
> enjoy'
> 
>
> ... would cut off and update blah to be 'here is my'. =
> Any ideas on how to preserve this?
> 
>
> Hopefully this makes sense and someone can shed some =
> light. I am relaying this problem on behalf of another developer, so if =
> more info is required, let me know.
>
> Thanks,
> Sarah.
> 
>
> Sarah Atkinson
> ColdFusion Programmer
> 
>
> Lonely Planet Publications, Aust.
> Telephone: +61 3 8379 8000
> Fax: +61 3 8379 8111
> [EMAIL PROTECTED]
> 
>
>
--- 
You are currently subscribed to cfaussie as: [EMAIL PROTECTED] 
To unsubscribe send a blank email to [EMAIL PROTECTED] 
 
MX Downunder AsiaPac DevCon - http://mxdu.com/
</BODY>
> </HTML>
> ------_=_NextPart_001_01C2B52A.9E58F880--

[cfaussie] Re: CFMX - special characters

Reply via email to