NCHAR, VARCHAR, NVARCHAR are returning as CHAR

[r313pp]

When executing select datatypes CHAR, NCHAR, VARCHAR, NVARCHAR are returning as CHAR (type_code=47) type with size = actual_size * 4. It seems like freetds issue or intended behavior, but maybe there is some way to get actual datatypes?

code

import ctds
import platform
from pprint import pprint

conn = ctds.connect('localhost')

cur = conn.cursor()
cur.execute("""
SELECT
    @@VERSION AS ver
    , CAST('char' AS CHAR(10)) AS char10
    , CAST('varchar' AS VARCHAR(10)) AS varchar10
    , CAST('nchar' AS NCHAR(10)) AS nchar10
    , CAST('nvarchar' AS NVARCHAR(10)) AS nvarchar10
""")

row = cur.fetchone()

print(platform.platform())
print('Python %s' % platform.python_version())
print('ctds v%s' % ctds.__version__)
print(ctds.freetds_version)
print()
print(row.ver)
pprint(cur.description[1:])

output

Windows-8.1-6.3.9600-SP0
Python 3.7.2
ctds v1.9.0
freetds v1.00.80

Microsoft SQL Server 2017 (RTM-CU6) (KB4101464) - 14.0.3025.34 (X64) 
	Apr  9 2018 18:00:41 
	Copyright (C) 2017 Microsoft Corporation
	Developer Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: )

(description(name='char10', type_code=47, display_size=40, internal_size=40, precision=0, scale=0, null_ok=True),
 description(name='varchar10', type_code=47, display_size=40, internal_size=40, precision=0, scale=0, null_ok=True),
 description(name='nchar10', type_code=47, display_size=40, internal_size=40, precision=0, scale=0, null_ok=True),
 description(name='nvarchar10', type_code=47, display_size=40, internal_size=40, precision=0, scale=0, null_ok=True))

ctds as of https://github.com/zillow/ctds/tree/9e679608557a00b994718f715e724d17eddd4735

[joshuahlang]

This is (unfortunate) FreeTDS behavior. The type_code is pulled from the DBCOL.Type field directly in Cursor.description here and that is source from FreeTDS's dbcolinfo API here.

There is a 1.1 release of FreeTDS forthcoming which may include some improvements in this area, though I haven't looked to closely at what all is included. If you find a good way to get the expected types out of FreeTDS I'd be happy to include the change as this certainly isn't intended behavior from the ctds library's perspective.

[r313pp]

There is a 1.1 release of FreeTDS forthcoming which may include some improvements in this area

I have tested it against freetds-1.1rc2 - there is no change in this behavior.