Re: What SQL type for SQLBindParameter for NVARCHAR(MAX)/NTEXT when using UTF-8 locale?

2012/10/19 Sebastien FLAESCH <[email protected]>:
> Hello Frediano,
>
> Summary: Issue fixed when using SQL_WLONGVARCHAR.
>

Good!

> More comments inlined.
>
> On 10/18/2012 09:54 PM, Frediano Ziglio wrote:
>> 2012/10/18 Sebastien FLAESCH<[email protected]>:
>>> 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.
>

Mmm... I'll check why SQL_LONGVARCHAR does not work. Are you using
unixODBC or iODBC? Which version?
You should use TDS_Version=7.2 to get support for (N)VARCHAR(MAX). 8.0
was an old name for 7.1 so 8.0 < 7.2 (my mind got a syntax error :-) )

Thanks for using development version!

>
>>> 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).
>

Yes, but that's why SQL_(W)LONGVARCHAR are designed for. Simply MS
could just map (N)VARCHAR(MAX) to these ODBC types if a version that
support (N)VARCHAR(MAX) is detected.

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

Mmm.... I though this was supported but is not. This will require the
use of TDS_Version=7.3. SQL_SS_TIME2 is supported (returned by
driver), SQL_SS_TIME2_STRUCT no.

>>> Thanks for reading, please let me know what I should use!
>>>
>>> Seb
>>

Frediano