[freetds] What SQL type for SQLBindParameter for NVARCHAR(MAX)/NTEXT when using UTF-8 locale?

[Sebastien FLAESCH]

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

I guess this is expected, because I use SQL_LONGVARCHAR...

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

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;

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

Seb

[Frediano Ziglio]

Post by Sebastien FLAESCH
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.
ctype = SQL_C_CHAR;
sqltype = SQL_LONGVARCHAR;
precision = 0x10000000;
This works find as long as the data is ASCII (and probably also
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.

Post by Sebastien FLAESCH
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.

Post by Sebastien FLAESCH
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?
[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).

Post by Sebastien FLAESCH
How to specify the precision for a CLOB?
As a comparison, with Easysoft SQL Server ODBC driver, you can bind
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...

Post by Sebastien FLAESCH
Thanks for reading, please let me know what I should use!
Seb

Frediano

[Sebastien FLAESCH]

Hello Frediano,

Summary: Issue fixed when using SQL_WLONGVARCHAR.

More comments inlined.

Post by Frediano Ziglio

Post by Sebastien FLAESCH
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.
ctype = SQL_C_CHAR;
sqltype = SQL_LONGVARCHAR;
precision = 0x10000000;
This works find as long as the data is ASCII (and probably also
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.

Post by Frediano Ziglio

Post by Sebastien FLAESCH
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.

Post by Frediano Ziglio

Post by Sebastien FLAESCH
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?
[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).

Post by Sebastien FLAESCH
How to specify the precision for a CLOB?
As a comparison, with Easysoft SQL Server ODBC driver, you can bind
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

Post by Frediano Ziglio

Post by Sebastien FLAESCH
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

[Frediano Ziglio]

Post by Sebastien FLAESCH
Hello Frediano,
Summary: Issue fixed when using SQL_WLONGVARCHAR.

Good!

Post by Sebastien FLAESCH
More comments inlined.

Post by Frediano Ziglio

Post by Sebastien FLAESCH
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.
ctype = SQL_C_CHAR;
sqltype = SQL_LONGVARCHAR;
precision = 0x10000000;
This works find as long as the data is ASCII (and probably also
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.

Post by Frediano Ziglio

Post by Sebastien FLAESCH
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 am using now SQL_WLONGVARCHAR,
I have FreeTDS 0.92 installed.
I have TDS_Version=8.0 set in the ODBC data source.

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!

Post by Sebastien FLAESCH

Post by Frediano Ziglio

Post by Sebastien FLAESCH
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?
[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).

Post by Sebastien FLAESCH
How to specify the precision for a CLOB?
As a comparison, with Easysoft SQL Server ODBC driver, you can bind
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...

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.

Post by Sebastien FLAESCH
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.

Post by Sebastien FLAESCH

Post by Frediano Ziglio

Post by Sebastien FLAESCH
Thanks for reading, please let me know what I should use!
Seb

Frediano