[unixODBC-support] Work arround Linux Debian, Microsoft SQL server ODBC driver and charset (ISO8859, UTF8, UCS2)

Nick Gorham nick at lurcher.org
Thu Sep 15 18:28:47 BST 2016


On 15/09/16 16:52, Edouard Gaulué wrote:
> Dear community,
>
> I'm not sure I've a trouble to fix but I just would like to know if my 
> understanding is correct.
>
> I try to use the Microsoft SQL Server Driver for Linux with unixodbc. 
> I use the Debian Jessie distribution with the stable unixodbc package, 
> so version 2.3.1.
>
> My locale is set to en_US.UTF-8.
>
>
> *** USING sqlcmd ***
>
> Using the sqlcmd tool provided by Microsoft with the driver, I manage 
> to read and write characters outside of ASCII:
>
> 1> UPDATE EXP_F_DOCLIGNES SET EU_ENUMERE='La pièce' WHERE id=1105270
> 2> GO
> (1 rows affected)
>
> 1> SELECT EU_ENUMERE FROM EXP_F_DOCLIGNES WHERE id=1105270
> 2> GO
> EU_ENUMERE
> -------------------------
> La pièce
> (1 rows affected)
>
>
> *** USING isql ***
>
> When I use isql, I manage to read characters outside of ASCII, but not 
> to write them:
>
> SQL> SELECT EU_ENUMERE FROM EXP_F_DOCLIGNES WHERE id=1105270
> +--------------------------+
> | EU_ENUMERE               |
> +--------------------------+
> | La pièce                |
> +--------------------------+
> SQLRowCount returns 0
> 1 rows fetched
>
> SQL> UPDATE EXP_F_DOCLIGNES SET EU_ENUMERE='La pièce' WHERE id=1105270
> SQLRowCount returns 1
>
> SQL> SELECT EU_ENUMERE FROM EXP_F_DOCLIGNES WHERE id=1105270
> +--------------------------+
> | EU_ENUMERE               |
> +--------------------------+
> | La pièce              |
> +--------------------------+
> SQLRowCount returns 0
> 1 rows fetched
>
>
> *** Looking at the traces ***
>
> All my traces show:
> UNICODE Using encoding ASCII 'ISO8859-1' and UNICODE 'UCS-2LE'.
>
> I haven't found any way to make this to switch to UTF-8 from the 
> command line or through odbc.ini or odbcinst.ini. Is there ?
>
>
> *** Compiling unixodbc to use UTF-8 ***
>
> I've changed the debian/rules and add --with-iconv-char-enc=UTF-8, 
> compiled and installed resulting unixodbc and libodbc package.
>
> Now, I observe in the traces:
> UNICODE Using encoding ASCII 'UTF-8' and UNICODE 'UCS-2LE'
>
> And both handle accented chars.
>
>
> *** Analyse ***
>
> sqlcmd maybe integrates something to detect your encoding and to 
> comply with the connexion one. isql doesn't, nor PHP through PDO. But, 
> if default is set to UTF-8 then those applications do the job.
>
>
> *** Questions ***
>
> How can read (SELECT) be OK and not write (INSERT, UPDATE) ? That the 
> first thing I found strange. Why applications understand the output 
> should be in UTF-8 whereas thay are said to be in ISO8859-1. Is it a 
> kind of OS feature, knowing you are displaying UTF-8 ?
>
> As far as I understand, --with-iconv-char-enc=UTF-8 changes the 
> default character encoding but it can be overrided by 
> directives/options, if implemented. Is this true?
>
> If not, what is the risk of compiling with --with-iconv-char-enc=UTF-8 
> ? on a UTF-8 OS ?

Generally unixODBC will pass through what its given. The only time that 
iconv setting is involved is if the driver needs to convert from W to A, 
or A to W. As long as the driver has both A and W entry points the 
driver manager is not involved.

isql is just a simple test app, it will read from standard input based 
on the existing LANG, so as long as you have a UTF8 LANG, and are 
passing UTF8 into it, the druver will recieve UTF8 and then its up to it 
what it does with the data.

-- 
Nick


More information about the unixODBC-support mailing list