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

Edouard Gaulué listes at e-gaulue.com
Thu Sep 15 16:52:37 BST 2016


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 ?





More information about the unixODBC-support mailing list