Monday, November 19, 2007

Hi Guys!

I am unable to get the extended Latin letters my Polish users are typing into my html form fields to insert correctly into my MS SQL Server table columns.

As a test, I created a little form:

<form
action="#CGI.Script_Name#?RequestTimeout=300"
method="post"
accept-charset="iso-8859-1,utf-8"
><input type="text" name="Alphabet_Sample" size="50" maxlength="50"></form>

and I copied a "word" containing all the derived Latin letters of the French, Polish, and Spanish alphabets into my clipboard:

áàâąæćçéèêëęíîïłńñóôœśúùûüÿźż

and pasted it into the text field and hit the enter key.

Now these characters display correctly in the text field, and even display correctly in the "Form Fields:" and "SQL Queries" Debugging Information output on the next page. This is what is happening to the "word" server side:

<cfif IsDefined('Form.Alphabet_Sample')>
<cfset This.alphabetSample = Trim(Form.Alphabet_Sample)/>
<cfelse>
<cfset This.alphabetSample = ""/>
</cfif>
<cfif Len(This.alphabetSample)><cfquery
name="InsertAlphabetSample"
datasource="#This.dataSource#"
>
-- line 24
INSERT INTO Collation_NVarChar_Fields (
SQL_1xCompat_CP850_CI_AS,
SQL_AltDiction_CP1253_CS_AS,
[snip]
Polish_BIN,
Traditional_Spanish_BIN
)
VALUES (
<cfqueryparam
value="#This.alphabetSample#"
cfsqltype="CF_SQL_VARCHAR"
/>,
<cfqueryparam
value="#This.alphabetSample#"
cfsqltype="CF_SQL_VARCHAR"
/>,
[snip]
<cfqueryparam
value="#This.alphabetSample#"
cfsqltype="CF_SQL_VARCHAR"
/>,
<cfqueryparam
value="#This.alphabetSample#"
cfsqltype="CF_SQL_VARCHAR"
/>
)
</cfquery></cfif>

I'm inserting it into an nvarchar column of every possible collation. Heroic, no? Just to prove it should work, I can launch SQL Enterprise manager and paste my "word" into each one of these columns, save the edit, and it displays correctly.

Yet this is what it looks like in each column:

áàâ?æ?çéèêë?íîï??ñóôœ?úùûüÿ??

In other words, all the letters with the ogonek, kreska, bar, or dot diacritics turned into question marks. Basically all the Latin-2 (ISO 8859-2) stuff. You know, the characters that cannot be mapped using the "Cp1252" character encoding. (If this rant can display html entities I think they would be ą, ć, ę, ł, ń, ó, ś, ź, and ż)

So, all the address labels for stuff shipping to Poland, etc. are ruined. Changing the web pages charset to <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /> has no effect. Adding accept-charset="iso-8859-1,utf-8" to the form tag has no effect. And obviously changing the SQL columns to any collation whatsoever has no effect.

So, how do I get the extended Latin letters that my Polish users are typing into my html form fields to insert correctly into my MS SQL Server table columns? Any ideas?

Thanx

Dave

Labels:

1 Comments:

Anonymous Dan said...

Try setting polish collation in the database.

9:25 AM  

Post a Comment

Subscribe to Post Comments [Atom]

Links to this post:

Create a Link

<< Home