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

Koenig, Michael Michael.Koenig at blue-yonder.com
Fri Sep 16 09:00:04 BST 2016


Hi Edouard!

Unfortunately, I am not familiar with the MSQL database and its driver. I know, however, that some ODBC drivers depend on the currently set locale (environment variables LCALL and LOCALE are often used). If the locale does not support UTF-8 (default C locale, for example), strange effects may happen with these databases. Sometimes, the drivers also offer options to set the locale for the database session only.

Perhaps this gives you a new angle to tackle your problem.

Cheers

Michael


On 15/09/16 19:28, "unixodbc-support-bounces at mailman.unixodbc.org on behalf of Nick Gorham" <unixodbc-support-bounces at mailman.unixodbc.org on behalf of nick at lurcher.org> wrote:

    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
    _______________________________________________
    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