[freetds] SQLBindParameter() does not work with UTF-8 ?!
Norbert Matzke
matzke at geopp.de
Fri Oct 10 12:32:55 EDT 2008
Norbert Matzke wrote:
> ZIGLIO, Frediano, VF-IT wrote:
>>> ZIGLIO, Frediano, VF-IT wrote:
>>>>> Problem: My client ODBC application needs to bind
>>> parameters which are
>>>>> UTF-8 encoded to SQL insert/update statements.
>>>>> I have set ClientCharset to UTF-8 in freetds.conf.
>>>>>
>>>>> I did some tests with Version 0.82 of FreeTDS and SQL Server 2005.
>>>>> Seemed to work, but not with a short UTF-8 String like '€'.
>>>>> '€' is 3 bytes long in UTF-8 encoding.
>>>>>
>>>>> If I called
>>>>> SQLBindParameter( , ,SQL_C_CHAR, SQL_WVARCHAR, n, 0, , , &ind )
>>>>> with n=1 and *ind=3 I got an error message. iconv was not able to
>>>>> convert '€' and SQL Server complains also ...
>>>>>
>>>>> Calling with n=3 and *ind=3 succeeds but inserts the
>>> string '€ ' into
>>>>> the database because the Server pads with 2 blanks to the
>>>>> requested size
>>>>> of 3 CHARS.
>>>>>
>>>>> Setting *ind=SQL_NTS did not help.
>>>>>
>>>>> I have tested version 0.83dev 2008-10-09 also: It did not work at
>>>>> all using UTF-8 strings with non single byte UTF-8 characters.
>>>>>
>>>>> So I have had a look at what is going on inside the FreeTDS code
>>>>> (ver. 0.83dev) and found that the problem arises due to
>>> insufficient
>>>>> buffer space for the bounded parameter.
>>>>>
>>>>> I solved the problem with very small changes to tds/mem.c and
>>>>> odbc/sql2tds.c (see attached patch). For me it works now.
>>>>>
>>>>> Any comments on this issue ?
>>>>>
>>>>> Best regards,
>>>>> Norbert
>>>>>
>>>>>
>>>> It's strange that every time you implement a feature
>>> someone ask for another... well, this means that previous
>>> works as expected I hope :)
>>>> WVARCHAR is currently in progress. I don't think is
>>> supported by 0.82 and have some problems (there is a failing
>>> test) under CVS HEAD. Test related to your problem should be
>>> utf8, I'll test with your string.
>>>> Actually I'm quite busy so I have no timeline for this issue.
>>>>
>>>> freddy77
>>>>
>>> I was not aware of the fact that utf-8 support is quite new
>>> to FreeTDS.
>>> I have had a look at the documentation before I tried to use it, a few
>>> days ago, and it seemed to me that by setting the Client Charset to
>>> UTF-8 one can use a MS SQL Server like any other UTF-8 capable DMBS --
>>> though MS SQL Server itself is not UTF-8 capable ! This is a very
>>> valuable feature of FreeTDS !
>>>
>>> Besides, if I understand correctly, requesting SQL_WVARCHAR vs.
>>> SQL_VARCHAR as SQL parameter type does not make any diffence
>>> to FreeTDS
>>> ? Only the server will not_do/do a USC-2 --> CP1252 --> UCS-2
>>> conversion with the data ? (assuming CREATE TABLE ... NVARCHAR() ..)
>>>
>>> Norbert
>>>
>> Well... SQL_WVARCHAR have problems. FreeTDS support UTF-8 as client encoding converting as best as it can but using SQL_VARCHAR. There is a big difference between SQL_VARCHAR and SQL_WVARCHAR cause FreeTDS should use VARCHAR or NVARCHAR on server. Currently not supporting SQL_WVARCHAR means that you have to use SQL_VARCHAR which lead to VARCHAR so you can't represent much characters. When FreeTDS will support SQL_WVARCHAR any UCS-2 characters will be available to your client.
>>
>> freddy77
>
> That sounds strange to me. I just did another test and found the
> following behaviour:
>
> - SQLBindParameter(... SQL_C_CHAR, SQL_WVARCHAR, 7, ..., "utf-8 €", ,&9)
> Sending Packet ==> ... P1 NCHAR(7) ...
>
> - SQLBindParameter(... SQL_C_CHAR, SQL_VARCHAR, 7, ..., "utf-8 €", ,&9)
> Sending Packet ==> ... P1 CHAR(7) ...
>
> In both cases the iconv is called freetds internal (for the wire
> conversion) to UC2-2 with source encoding UTF-8 (due to Client Charset
> setting). Data on the wire is the same !
> So it should depend only on the server (and the CREATE TABLE column
> type) which kind of storage conversion is applied.
>
> Norbert
>
Just one further note:
FreeTDS 0.83dev_2008-10-09_patched works with UTF-8 exactly as expected
IMHO.
Of course you have to calculate the number of resulting UCS-2 / UTF-16
characters from your UTF-8 input by your own. But that is the same for
other parameter types: When binding SQL_C_TYPE_TIMESTAMP you must also
give the number of characters of the of ASCII representation (23) of the
timestamp as ColumnSize (tested native MS ODBC Driver).
Fortunately other DBMS obviously don't care about the given ColumnSize
when binding Parameters with inherent size (tested MySQL, SQLite).
So a client programm is compatible with these.
Norbert
More information about the FreeTDS
mailing list