FreeTDS Development Group

[Sebastien FLAESCH <sf AT 4js.com>]

Hello Frediano,

Summary: Issue fixed when using SQL_WLONGVARCHAR.

More comments inlined.

On 10/18/2012 09:54 PM, Frediano Ziglio wrote:
> 2012/10/18 Sebastien FLAESCH<sf AT 4js.com>:
> > Hello,
> >
> > I am using a UTF-8 encoding in my Linux FreeTDS client program,
> > and want to insert LOB data into NVARCHAR(MAX)/NTEXT columns.
> >
> > For now, I bind the SQL Parameter with:
> >
> >           ctype = SQL_C_CHAR;
> >           sqltype = SQL_LONGVARCHAR;
> >           precision = 0x10000000;
> >
> > This works find as long as the data is ASCII (and probably also
> > ISO-885901), but when using UTF-8 sequences, I get this error:
> >
> > SQL State: HY000
> > SQL code : 2402
> > [FreeTDS][SQL Server]Error converting characters into server's character set. 
> > Some character(s) could not be converted
>
> Mmm... probably the encoding used by ODBC is not what you expected.
> Try setting the ClientCharset attribute.

I was already using ClientCharset=UTF-8 in the ODBC data source.

> > I guess this is expected, because I use SQL_LONGVARCHAR...
>
> Yes, longvarchar should be fine. I think FreeTDS should be smart
> enough to use NVARCHAR(MAX) from protocol 7.2.

I don't think so:

I am using now SQL_WLONGVARCHAR,
I have FreeTDS 0.92 installed.
I have TDS_Version=8.0 set in the ODBC data source.
But in the SQL Server Profiler, I see an N'@P1 NTEXT' parameter for 
sp_prepexec.


> > For NCHAR/NVARCHAR types, I use SQL_WCHAR / SQL_WVARCHAR SQL types
> > to bind my UTF-8 buffers, and this works fine.
> >
> > But for large objects, what SQL type should I use?
> >
> > I tried with SQL_WVARCHAR and a large size, without success:
> >
> >     [FreeTDS][SQL Server]Invalid string or buffer length
>
> The encoding should be in SQLWCHAR with size in bytes usually (so
> multiple of sizeof(SQLWCHAR)) and probably you still want the long
> version (SQL_WLONGVARCHAR).
>
> > How to specify the precision for a CLOB?
> >
> > As a comparison, with Easysoft SQL Server ODBC driver, you can bind
> > UTF-8 buffers with:
> >
> >           ctype = SQL_C_CHAR;
> >           sqltype = SQL_WVARCHAR;
> >           precision = SQL_SS_LENGTH_UNLIMITED;
>
> I wasn't even aware of this constant!
> I don't understand why this extension is necessary...

I think SQL_SS_LENGTH_UNLIMITED makes sense:

By design, unlike [N]CHAR(x) or [N]VARCHAR(x) types, a LOB type has
no size (it has a maximum size regarding storage limits).

There are other SQL Server specific defines in sqlncli.h such as:

  SQL_SS_TIME2_STRUCT

> > Thanks for reading, please let me know what I should use!
> >
> > Seb
>
> Frediano
> _______________________________________________
> FreeTDS mailing list
> FreeTDS AT lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds