Skip to Content.
Sympa Menu

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

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: Norbert Matzke <matzke AT geopp.de>
  • To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] SQLBindParameter() does not work with UTF-8 ?!
  • Date: Fri, 10 Oct 2008 18:32:55 +0200

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





Archive powered by MHonArc 2.6.24.

Top of Page