FreeTDS Development Group

[Frediano Ziglio <freddy77 AT gmail.com>]

Hi,
  so, installed a sql 2019 and tried some queries.
Using the driver provided inside the docker image the "abc" is
returned as "abc       ", so
the CHAR(10) are returned encoded in UTF-8 and the "10" is just the
number of bytes.
Now, why the server decided to return UTF-8 and not convert to NVARCHAR (which
make sense to me for old clients) it's the issue. Maybe they bump the
protocol version,
maybe they are using some other flags/version (like client version
passes through
the pre-login). Unfortunately the login is encrypted so I'll have to
dig a bit more (well,
just question of setup and use the bounce utility, not sure if it
supports pre-login).

Frediano

Il giorno gio 28 feb 2019 alle ore 10:50 Sebastien FLAESCH
<sf AT 4js.com> ha scritto:
>
> 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
> >
>
> _______________________________________________
> FreeTDS mailing list
> FreeTDS AT lists.ibiblio.org
> https://lists.ibiblio.org/mailman/listinfo/freetds