How to control ODBC Driver version

[karl-schelhammer-td]

TLDR; I would like to know how to control which version of the Teradata ODBC driver is used by SQLAlchemy.

We recently updated our TD system to 16.20 and certain features on the server side are disable like QVCI. This leads to the following error when trying to connect to the database:

user = 'yourAttUID'
host = 'dbServerName'
from getpass import getpass
pswd = getpass('Password:')

engine = create_engine('teradata://'+user+':'+pswd+'@'+host)

# ... table definitions go here, I use deferred reflection

base.prepare(engine)

DatabaseError: (teradata.api.DatabaseError) (9719, '[HY000] [Teradata][ODBC Teradata Driver][Teradata Database](-9719)QVCI feature is disabled.') [SQL: 'SELECT columnname, columntype, columnlength, chartype, decimaltotaldigits, decimalfractionaldigits, columnformat, nullable, defaultvalue, idcoltype \nFROM dbc.ColumnsQV \nWHERE DatabaseName=? AND TableName=?'] [parameters: ('mktbciviews', 'bci_acct_id_sgmnt')] (Background on this error at: http://sqlalche.me/e/4xp6)

The DBA's aren't going to turn on the QVCI feature in the near term because they are still looking into how that affects the platform stability. There is a workaround that involves updating the driver to >= 16.10.0.5.

https://cks.teradata.com/support/general/newcase.nsf/0/B84522DCF8D8E9A5852582E40018B294?OpenDocument

From the above link:

Once QVCI is disabled, either from dbscontrol or by having fix for DR 190227, customer has got two choices:

1. Leaving QVCI disabled in DBS side with the risk of query failure (Failure 9719) every time QVCI is attempted to be used, OR

2. Leaving QVCI disabled in DBS side AND mitigating effects for Failure 9719 on some client applications that use metadata retrieval, by upgrading JDBC / ODBC/.NET Driver to the following versions

• DR 188567 – available in JDBC 15.10.00.37 / JDBC 16.00.00.34 / JDBC 16.10.00.06 / JDBC 16.10.00.07 / JDBC 16.20.00.03 / JDBC 16.20.00.04 (on Developer Exchange)​
• JIRA NET-4210 - available in NET 16.10.01.00)
• JIRA ODBC-18059 - available in ODBC 16.10.0.5.

Teradata preferred approach after disabling QVCI is Option 2.

This brings me to my question: How can I point my connection engine to the new version of the driver. I use ODBC Administrator on Mac and I can confirm that the old driver was deleted and that the 16.20.XX.XX version is installed. I just need to know how to control which version is used in SQLAlchemy.

Any help would be much appreciated!

[tserafim]

I don't have a Mac but I am guessing you can use ODBC Administrator to create a DSN and specify the driver, username, password, server ip,... you want to use.

If that is the case, you need to make sure sqlalchemy uses that DSN(unfortunately, it is not possible to just specify the DNS on the connection string, like it is possible using Microsoft SQL Server dialect)

What you have to do is use a creator argument.

From sqlalchemy docs

• creator – a callable which returns a DBAPI connection. This creation function will be passed to the underlying connection pool and will be used to create all new database connections. Usage of this function causes connection parameters specified in the URL argument to be bypassed.

Even though user/password/host on connection string will be bypassed, you have to provide some data otherwise you´ll get a key error.

from sqlalchemy import create_engine
from teradata import tdodbc

def connect():
    return tdodbc.connect(dsn='dsn_with_specific_driver_config')

engine = create_engine('teradata://invalid_user:invalid_password@unknow_host', creator=connect)

edit: btw, this was tested on Linux

[sandan]

This dialect uses the teradata package in PyTD. We pass extra parameters in the connection string to the OdbcConnection object: https://github.com/Teradata/PyTd/blob/master/teradata/tdodbc.py#L403

You can also try using teradatasqlalchemy which no longer uses QVCI for column info. It doesn't use ODBC either: https://pypi.org/project/teradatasqlalchemy/

[karl-schelhammer-td]

Is the teradatasqlalchemy package a distinct entity from sqlalchemy-teradata?

[karl-schelhammer-td]

@sandan That fixed my connection issue.

I noticed some apparent changes between the teradatasqlalchemy and sqlalchemy-teradata. Namely that the column names are all uppercase which is breaking some other code. Not a huge deal but it would be good to understand all of the subtle differences between the two implementations. Is there a change log out there that details this sort of thing?

Thanks!

[jasheldo]

Hi @sandan. I read in the teradatasqlalchemy documentation that COP discovery is not yet implemented. We're on a COP server. Is COP discovery on the roadmap? The QVCI feature is a huge show stopper for me and materially impacting my work. I have the latest drivers for MacOS but am still hitting that problem with Teradata. I cannot connect to Teradata with teradatasqlalchemy until COP discovery is implemented.

[robing9]

Hi

Hi @sandan. I read in the teradatasqlalchemy documentation that COP discovery is not yet implemented. We're on a COP server. Is COP discovery on the roadmap? The QVCI feature is a huge show stopper for me and materially impacting my work. I have the latest drivers for MacOS but am still hitting that problem with Teradata. I cannot connect to Teradata with teradatasqlalchemy until COP discovery is implemented.

Hi to fix this

Go to "C:***\Python36\Lib\site-packages\sqlalchemy_teradata\dialect.py" Then change the line 222 to dbc_columninfo = 'dbc.ColumnsV'

It should fix it.