[freetds] Status of UTF-8 support in 0.83 dev
Sebastien FLAESCH
sf at 4js.com
Mon Sep 1 10:54:24 EDT 2008
Frediano,
While testing ParameterType = SQL_WVARCHAR, I faced a problem with ColumnSize...
According to MSDN, ColumnSize defines the precision of the type in *characters* (not bytes):
http://msdn.microsoft.com/en-us/library/ms711786(VS.85).aspx
With attached example, when using a CHAR(2) column, I just bind with ParameterType=SQL_WVARCHAR and ColumnSize=2 ...
But I get an error:
SQL State: 42000
SQL code : 8016
Message : [FreeTDS][SQL Server]The incoming tabular data stream (TDS) remote
procedure call (RPC) protocol stream is incorrect.
Parameter 3 (""): Data type 0xE7 has an invalid data length or metadata length.
Please could you have a look at this?
Note in my SQL Native Client driver, I do also use SQL_WVARCHAR and ColumnSize has to be passed as a number of characters.
Thanks!
Seb
Frediano Ziglio wrote:
> Il giorno gio, 14/08/2008 alle 11.53 -0400, James K. Lowden ha scritto:
>> Hi Sebastien,
>>
>>> James K. Lowden wrote:
>>>> "Wide" characters have a fixed width, usually 16 bits. UTF-8 has a
>>>> variable width and for the most part can be treated like ASCII
>>>> (strlen(3) et al.). Declaring UTF-8 as SQL_C_WCHAR incorrectly tells
>>>> the driver there's a character in every two bytes.
>>> Oh take care, actually there are 2 type specifications in
>>> SQLBindParameter:
>>>
>>> SQLRETURN SQLBindParameter(
>>> SQLHSTMT StatementHandle,
>>> SQLUSMALLINT ParameterNumber,
>>> SQLSMALLINT InputOutputType,
>>> SQLSMALLINT ValueType, -- This is the C type
>>> SQLSMALLINT ParameterType, -- This is the SQL equivalent
>>> SQLULEN ColumnSize,
>>> SQLSMALLINT DecimalDigits,
>>> SQLPOINTER ParameterValuePtr,
>>> SQLINTEGER BufferLength,
>>> SQLLEN * StrLen_or_IndPtr);
>>>
>>> For sure, when using (char *) / UTF-8, ValueType *must* be SQL_C_CHAR,
>>> not SQL_C_WCHAR... (that one is for wchar_t).
>>>
>>> I was talking about ParameterType...
>>>
>>> Should it be SQL_W[VAR]CHAR or SQL_[VAR]CHAR when binding UTF-8?
>> Oh, I see. Your question surprises me, then, because IIUC the client-side
>> encoding doesn't matter for ParameterType. ParameterType describes the
>> data type of the parameter as used in the query, not as represented in the
>> client buffer. (I"m just an ODBC student. Correct me if I'm wrong.)
>>
>
> ParameterType specify the type to use with the server so if SQL_VARCHAR
> is used a VARCHAR is sent to the server while using SQL_WVARCHAR an
> NVARCHAR is used. This cause the problem with japanese encoding,
> SQL_VARCHAR is not able to "contain" japanese so the error. Some
> updates:
> - I split my working patch for wide support committing support for
> server N(VAR)CHAR, very short and seems to work correctly with UTF-8
> (the other part is to support *W functions and SQL_C_WCHAR... and it's a
> very very long work)
> - I ported Sebastian tests in a new src/odbc/unittests/utf8.c test, it
> works correctly and it tests data inserted too. I used a trick to force
> client encoding (using SQLDriverConnect instead of SQLConnect)
> - I wrote a workaround for invalid TDS protocol if a conversion error
> happen. Now libTDS send an empty string... not correct but at least you
> get an error from library and you can continue to issue queries. The
> patch was backported to 0.82 too. I'll write a definitive fix for this
> (it require to handle correctly errors and cancel sending query).
>
>> For instance, any of these pairs works:
>>
>> ValueType ParameterType
>> ------------- -------------
>> SQL_C_CHAR SQL_DECIMAL
>> SQL_C_CHAR SQL_VARCHAR
>> SQL_C_DOUBLE SQL_FLOAT
>>
>> For character data parameters, the choice of SQL_W[VAR]CHAR or
>> SQL_[VAR]CHAR is governed by the data type of the parameter as understood
>> by the server. For stored procedures, the parameter type is obvious: it's
>> declared within the procedure text. For placeholders in open SQL text, I
>> would say the type is always SQL_[VAR]CHAR.
>>
>
> No, the client encodes the parameter type as specified by the
> application. The server converts the parameter as needed.
>
>> That is, if your query is,
>>
>> CREATE TABLE t (name nvarchar(30))
>> INSERT t values( ? )
>>
>> You would use SQL_[VAR]CHAR.
>>
>> Now, what to do when what you really want is equivalent to "INSERT t
>> values( N'string' )"? I assume it's still SQL_[VAR]CHAR, but I won't know
>> for sure until either Frediano explains or I figure out how sp_execute
>> works.
>>
>
> sp_execute is a special store procedure witch is embedded in sql server
> and support variable parameters... let's say it's like a printf instead
> of a strcpy. You specify parameters you pass followed by parameters and
> server execute the query.
>
>> One thing you could try is SQLDescribeParam with various combinations
>> using Microsoft's driver. We would follow whatever it says/expects.
>>
>
> SQLDescribeParam is a workaround on mssql... it parse the query to get
> parameters type. Obviously on complicated query it isn't able to tell
> the truth... Assuming query is "SELECT * FROM table WHERE x = ?" it
> build a "SET FMTONLY ON SELECT x FROM table" and get x type... this is
> the reason is still unsupported by our driver... nobody wants to write
> the sql parser :)
>
> bye
> Frediano
>
>
> _______________________________________________
> FreeTDS mailing list
> FreeTDS at lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds
>
-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: odbctest112.c
Url: http://lists.ibiblio.org/pipermail/freetds/attachments/20080901/19bfd596/attachment.c
More information about the FreeTDS
mailing list