JezK
Edit File: Localization.html
<?xml version="1.0" encoding="UTF-8" standalone="no"?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>Localization and TDS 7.0</title><link rel="stylesheet" type="text/css" href="userguide.css" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="home" href="index.html" title="FreeTDS User Guide" /><link rel="up" href="configs.html" title="Chapter 5. Advanced Configurations" /><link rel="prev" href="configs.html" title="Chapter 5. Advanced Configurations" /><link rel="next" href="domains.html" title="Domain Logins" /></head><body><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="3" align="center">Localization and <acronym class="acronym">TDS</acronym> 7.0</th></tr><tr><td width="20%" align="left"><a accesskey="p" href="configs.html">Prev</a> </td><th width="60%" align="center">Chapter 5. Advanced Configurations</th><td width="20%" align="right"> <a accesskey="n" href="domains.html">Next</a></td></tr></table><hr /></div><div class="sect1"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a id="Localization"></a>Localization and <acronym class="acronym">TDS</acronym> 7.0</h2></div></div></div><p><acronym class="acronym">TDS</acronym> 7.0 uses 2-byte Unicode (technically, <acronym class="acronym">UCS-2</acronym>, recently <acronym class="acronym">UTF-16</acronym>) to transfer character data between servers and clients. Included in <span class="quote">“<span class="quote">character data</span>”</span> are query text (i.e., <acronym class="acronym">SQL</acronym>), metadata (table names and such), and <span class="foreignphrase"><em class="foreignphrase">bona fide</em></span> data of datatypes <code class="literal">nchar</code>, <code class="literal">nvarchar</code>, and <code class="literal">ntext</code>. (Background information on Unicode and how it affects <span class="productname">FreeTDS</span> can be found in the <a class="link" href="Unicode.html" title="Unicode: East meets West">appendix</a>.)</p><p>Because most Unix tools and environments do not support <acronym class="acronym">UCS-2</acronym>, <span class="productname">FreeTDS</span> provides for conversion by the client to other character sets. The mechanism used is determined by the <code class="filename">configure</code> script, which looks for a <code class="function">iconv(3)</code> function, an implementation of the <a class="ulink" href="http://www.opengroup.org/onlinepubs/7908799/xsh/iconv.html" target="_top">iconv</a> standard. If no <code class="function">iconv</code> library is found, or if it is explicitly disabled, <span class="productname">FreeTDS</span> will use its built-in <code class="function">iconv</code> substitute, and will be capable of converting among only <acronym class="acronym">ISO 8859-1</acronym>, <acronym class="acronym">UTF-8</acronym>, <acronym class="acronym">UCS-2</acronym>, <acronym class="acronym">UTF-16</acronym> and <acronym class="acronym">UTF-16</acronym>.</p><p>To learn what character set the client wants, <span class="productname">FreeTDS</span> prefers the applicable <a class="link" href="freetdsconf.html#clientcharset"><code class="filename">freetds.conf</code></a> <code class="literal">client charset</code> property. If that is not set, it parses the <code class="envar">LANG</code> environment variable. In either case, the found string is passed to <code class="function">iconv</code>(3) (or its built-in replacement). <a href="#ftn.idm2413" class="footnote" id="idm2413"><sup class="footnote">[17]</sup></a>. If neither is found, <acronym class="acronym">UCS-2</acronym> data are converted to <acronym class="acronym">ISO 8859-1</acronym>.</p><p>To list all supported iconv character sets try <span class="command"><strong>iconv</strong></span>(1). GNU's does:</p><pre class="screen"> <code class="prompt">$ </code><strong class="userinput"><code>iconv --list</code></strong></pre><p>For other systems, consult your documentation (most likely <span class="command"><strong>man iconv</strong></span> will give you some hints).</p><p>In this example a server named <code class="literal">mssql</code> will return data encoded in the GREEK character set.</p><div class="example"><a id="e.g.GREEK"></a><p class="title"><strong>Example 5.2. Configuring for GREEK <code class="filename">freetds.conf</code> setting</strong></p><div class="example-contents"><pre class="programlisting"> [mssql] host = ntbox.mydomain.com port = 1433 client charset = GREEK </pre></div></div><br class="example-break" /><p>If <span class="productname">FreeTDS</span> runs into a character it can not convert, its behavior varies according to the severity of the problem. On retrieving data from the server, <span class="productname">FreeTDS</span> substitutes an <acronym class="acronym">ASCII</acronym> '?' in the character's place, and emits a warning message stating that some characters could not be converted. On sending data to the server, <span class="productname">FreeTDS</span> aborts the query and emits an error message. It is well to ensure that the data contained in the database is representable in the client's character set.</p><p>If you have a mix of character data that can not be contained in a single-byte character set, you may wish to use <acronym class="acronym">UTF-8</acronym>. <acronym class="acronym">UTF-8</acronym> is a variable length unicode encoding that is compatible with <acronym class="acronym">ASCII</acronym> in the range 0 to 127. With <acronym class="acronym">UTF-8</acronym>, you are guaranteed to never have an unconvertible character.</p><div class="important" style="margin-left: 0.5in; margin-right: 0.5in;"><table border="0" summary="Important"><tr><td rowspan="2" align="center" valign="top" width="25"><img alt="[Important]" src="../images/important.gif" /></td><th align="left">Important</th></tr><tr><td align="left" valign="top"><p><span class="productname">FreeTDS</span> is not fully compatible with multi-byte character sets such as <acronym class="acronym">UCS-2</acronym>. You must use an ASCII-extension charset (e.g., UTF-8, ISO-8859-*)<a href="#ftn.idm2447" class="footnote" id="idm2447"><sup class="footnote">[18]</sup></a>. Great care should be taken testing applications using these encodings. Specifically, many applications do not expect the number of characters returned to exceed the column size (in bytes).</p></td></tr></table></div><p>In the following example, a server named <code class="literal">mssql</code> will return data encoded in the <acronym class="acronym">UTF-8</acronym> character set.</p><div class="example"><a id="e.g.UTF8"></a><p class="title"><strong>Example 5.3. Configuring for <acronym class="acronym">UTF-8</acronym> <code class="filename">freetds.conf</code> setting</strong></p><div class="example-contents"><pre class="programlisting"> [mssql] host = ntbox.mydomain.com port = 1433 client charset = UTF-8 </pre></div></div><br class="example-break" /><p>It is also worth clarifying that <acronym class="acronym">TDS 7.0</acronym> and above do not accept any specified character set during login, as 4.2 does. A <acronym class="acronym">TDS 7.0</acronym> login packet uses <acronym class="acronym">UCS-2</acronym>.</p><div class="sect2"><div class="titlepage"><div><div><h3 class="title"><a id="localization.servernote"></a>Microsoft Server Note</h3></div></div></div><p>String literals in SQL must be prefixed with 'N' unless the enclosed string can be represented in the server's <span class="emphasis"><em>single-byte</em></span> character set, irrespective of the column's datatype. For example, in the SQL statement </p><div class="informalexample"><pre class="screen"> INSERT INTO tablename (greeting) VALUES ('Hallå')</pre></div><p> the string is subject to somewhat surprising treatment by the server.</p><p>When the server parses the SQL, it extracts the data values for insertion (or update, or comparison, etc.) Unprefixed strings are converted to the single-byte character set of the server/database.<a href="#ftn.idm2468" class="footnote" id="idm2468"><sup class="footnote">[19]</sup></a> Inserted data are then of course stored in the column. In the case of UCS-2 columns — <code class="literal">nchar</code>, <code class="literal">nvarchar</code>, and <code class="literal">ntext</code> — the value stored is that which results from a <span class="emphasis"><em>second</em></span> conversion: from the single-byte form to the <acronym class="acronym">UCS-2</acronym> form.</p><p>The <span class="emphasis"><em>only</em></span> safe way to enclose strings in SQL text is with an 'N' prefix: </p><div class="informalexample"><pre class="screen"> INSERT INTO tablename (greeting) VALUES (N'Hallå')</pre></div><p> </p><h4><a id="idm2479"></a>Commentary</h4><p>What's surprising about this? Versions 7.0 and later of the TDS protocol use UCS-2 to send SQL text. No matter how your local client is configured — with <acronym class="acronym">UCS-2</acronym> or <acronym class="acronym">ISO 8859-1</acronym> or anything else — it's converted to <acronym class="acronym">UCS-2</acronym> before it's sent to the server. And obviously arrives at the server as <acronym class="acronym">UCS-2</acronym>. If the column into which it's being inserted is also <acronym class="acronym">UCS-2</acronym>, there's no need of <span class="emphasis"><em>any</em></span> conversion, much less two, and <span class="emphasis"><em>certainly</em></span> no need to lose information.</p><p>Why this happens is anyone's guess. Here's one: it makes the datatype of the column unimportant. Regardless of whether you use char/varchar/text or nchar/nvarchar/ntext or a mixture of the two, the arriving SQL (if naïvely written) will store exactly the same characters.</p></div><div class="footnotes"><br /><hr style="width:100; text-align:left;margin-left: 0" /><div id="ftn.idm2413" class="footnote"><p><a href="#idm2413" class="para"><sup class="para">[17] </sup></a>The built-in replacement expects GNU iconv names: <code class="literal">ISO-8859-1</code>, <code class="literal">US-ASCII</code>, or <code class="literal">UTF-8</code>.</p></div><div id="ftn.idm2447" class="footnote"><p><a href="#idm2447" class="para"><sup class="para">[18] </sup></a>not EBCDIC or other weird charsets</p></div><div id="ftn.idm2468" class="footnote"><p><a href="#idm2468" class="para"><sup class="para">[19] </sup></a>The precise rules are unknown to the author.</p></div></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="configs.html">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="configs.html">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="domains.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">Chapter 5. Advanced Configurations </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> Domain Logins</td></tr></table></div></body></html>