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

Nick Gorham nick at lurcher.org
Mon Sep 19 13:05:56 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.

i think its possible that sqlcmd is reading the input and converting in 
itself into wide characters, and then calling the W functions the driver 
so UTF8 is not being seen by the driver. And likewise with data coming 
back. isql is a simple test tool that is not specific to any driver so 
just takes a string from the terminal and passes it to the ansi api calls.
>
>
> *** 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 ?

Not sure, I would turn on unixODBC logging and see what the app is doing 
with the driver.
>
> 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 ?

Normally none. The only time the driver manager will use that info is 
when it needs to convert from wide to ansi and vice versa. So for 
example if the driver only has the W functions, and the application 
calls the A points, the the driver manager will call teh W function on 
the applications behalf converting the data using the selected encoding.

I am not sure about the MS driver, but the Easysoft SQL Server driver 
has the ability to set the client side encoding, so the driver knows 1. 
Client character set, 2. Server character set (from the server info), so 
can convert correctly from 1 to 2, and back.

-- 
Nick
>
>
>
>
> _______________________________________________
> unixODBC-support mailing list
> unixODBC-support at mailman.unixodbc.org
> http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support




More information about the unixODBC-support mailing list