Re: SQLBindParameter() does not work with UTF-8 ?!

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

_______________________________________________
FreeTDS mailing list
[email protected]
http://lists.ibiblio.org/mailman/listinfo/freetds