FreeTDS Development Group

[Frediano Ziglio <freddy77 AT gmail.com>]

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