FreeTDS Development Group

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

Hello Craig,

I don't think so: An ODBC program should be able to properly identify
(as much as possible) the original database type used for a column.

We must also distinguish the concept of "variable length" regarding the
number of bytes used to encode characters, versus the actual length of
the string in character units.

Plus the fact that [N]CHAR columns can be blank-padded, while [N]VARCHAR
columns are not and store the actual number of trailing white spaces of
the string, try for ex:

create table tab1 ( c1 char(10), c2 varchar(10) )
insert into tab1 values ( 'abc', 'abc  ' )
select '['+c1+']', '['+c2+']' from tab1

You will see:

[abc       ] [abc  ]

(check SET ANSI_PADDING ON/OFF)

...

UTF-8 is a variable-width encoding:

a  => needs 1 byte
é  => needs 2 bytes
木 => needs 3 bytes
... etc (there can be more than 3 bytes)

In that context, one can see a "variable length" in byte units...

But when counting in characters, 'aé木' is 3 characters, no matter the
encoding used to store this string.

You have also the concept of byte-length semantics versus char-length
semantics: When creating a CHAR(10) is this 10 bytes or 10 characters?
Depending on the database brand, it's bytes (SQL Server), characters
(PostgreSQL), or it can even be a mix and specified explicitly (like
in Oracle DB).

Note: You have then also the concept of "width" or "number of columns":
A latin character takes one column, while an Asian logogram takes 2.
See wcwidth() for more details.

...

In SQL Server, NCHAR and NVARCHAR can store UTF-16 when the SC modifier
is used in the database collation (they store UCS-2 when no SC is used)

In UTF-16, a first range of characters are encoded on 2 bytes, but you
can have "surrogate pairs" encoded on 4 bytes (two 16-bit code units).

In your logic, the NCHAR/UTF-16 type could also be considered as a
"variable length" type and SQLDecribeCol() could return SQL_WVARCHAR
instead of SQL_WCHAR... It must not and does not.

Cheers,
Seb


On 2/27/19 4:32 PM, Craig Jackson wrote:
> I don't know anything about SQL Server 2019, but if they added the option
> to allow UTF-8 encoding of character strings, it would seem natural that
> said strings would be variable-length, even if declared CHAR(n). Strings
> encoded in UTF-8 are inherently variable length.
>
> Craig Jackson
>
> On Wed, Feb 27, 2019 at 10:27 AM Sebastien FLAESCH <sf AT 4js.com> wrote:
>
> > Hi,
> >
> > Just tried with TDS 7.4, it did not help.
> >
> > I have tested the MS ODBC driver 17.3.1.1, it works as expected
> > ( I get 1/SQL_CHAR from SQLDescribeCol() )
> >
> > You may know that it's very easy to setup an SQL Server 2019 with
> > docker, do you?
> >
> > Cheers,
> > Seb
> >
> > On 2/27/19 3:08 PM, Frediano Ziglio wrote:
> > > Hi,
> > >     I would try to use TDS 7.4, not 7.3.
> > > Also I would try to use Microsoft ODBC driver and a network analyzer
> > > (like wireshark).
> > >
> > > Regards,
> > >     Frediano
> > >
> > > Il giorno mar 26 feb 2019 alle ore 11:26 Sebastien FLAESCH
> > > <sf AT 4js.com> ha scritto:
> > > > Testing FreeTDS 1.1rc3 with SQL Server 2019:
> > > >
> > > > I get an unexpected SQL type code with a CHAR(10) column, when the
> > > > database collation is using UTF-8.
> > > >
> > > > Instead of SQL_CHAR (or SQL_WCHAR), I get SQL_WVARCHAR.
> > > >
> > > > I have created a database with this collation:
> > > >
> > > >      Latin1_General_100_CI_AS_SC_UTF8
> > > >
> > > > Then created a table like this:
> > > >
> > > >      CREATE TABLE mytab1 ( col1 CHAR(10) )
> > > >
> > > > and inserted a row:
> > > >
> > > >      INSERT INTO mytab1 VALUES ( 'abc' )
> > > >
> > > > Then in the ODBC program:
> > > >
> > > >      SQLExecDirect(.."SELECT * FROM mytab1"..)
> > > >      SQLDescribCol(...)
> > > >
> > > > Check the TDSDUMP trace in attachment (freetds-2019-1.log)
> > > >
> > > > token.c:1541:tds7_get_data_info:
> > > >            colname = col1
> > > >            type = 39 (varchar)
> > > >            server's type = 231 (x UCS-2 varchar)           <-- 231 ?
> > > >            column_varint_size = 2
> > > >            column_size = 20 (20 on server)
> > > >
> > > >
> > > > The same program, connecting to SQL Server 2017 (were DB collation is
> > Latin1
> > > > without UTF-8):
> > > >
> > > >
> > > > token.c:1541:tds7_get_data_info:
> > > >            colname = col1
> > > >            type = 47 (char)
> > > >            server's type = 175 (xchar)          <-- 175
> > > >            column_varint_size = 2
> > > >            column_size = 10 (10 on server)
> > > >
> > > > With SQL Server 2019, when using another collation (non-UTF-8)
> > > >
> > > >       CREATE TABLE mytab2 ( col1 CHAR(10) COLLATE Latin1_General_CI_AS )
> > > >
> > > > I get the expected tds type code...
> > > >
> > > > Seb
> > > > _______________________________________________
> > > > FreeTDS mailing list
> > > > FreeTDS AT lists.ibiblio.org
> > > > https://lists.ibiblio.org/mailman/listinfo/freetds
> > > _______________________________________________
> > > FreeTDS mailing list
> > > FreeTDS AT lists.ibiblio.org
> > > https://lists.ibiblio.org/mailman/listinfo/freetds
> >
> > _______________________________________________
> > FreeTDS mailing list
> > FreeTDS AT lists.ibiblio.org
> > https://lists.ibiblio.org/mailman/listinfo/freetds
> _______________________________________________
> FreeTDS mailing list
> FreeTDS AT lists.ibiblio.org
> https://lists.ibiblio.org/mailman/listinfo/freetds