search

Teradata / PyTd

A Python Module to make it easy to script powerful interactions with Teradata Database in a DevOps friendly way.
MIT License
108 stars 43 forks source link

ODBC connection errors #89

Open ausiddiqui opened 6 years ago

ausiddiqui commented 6 years ago

OS: macOS 10.13.2 Python: 2.7.14 Teradata ODBC Driver for Mac OS X: 16.20.00.0006 unixodbc: Not installed

I am able to test a successful connection using the following operations on command line:

>>>/Library/Application\ Support/teradata/client/16.20/bin/tdxodbc -d "Teradata ODBC DSN"

Enter UserID: myusername
Enter Password:

Connecting with SQLConnect(DSN=Teradata ODBC DSN,UID=myusername,PWD=*)...

.....ODBC connection successful.

ODBC version        = -03.52.0000-
DBMS name           = -Teradata-
DBMS version        = -15.10.0602  15.10.06.02-
Driver name         = -tdataodbc_sbu.dylib-
Driver version      = -16.20.00.0006-
Driver ODBC version = -03.80-

(type quit to terminate adhoc)
Enter SQL string : select top 3 * from mydb.mytable

Executing SQLExecDirect("select top 3 * from mydb.mytable")...

SQL Statement [1]: 3 rows affected.

date_local weekday_local
2005-09-16 FRIDAY
2004-09-05 SUNDAY
2006-12-07 THURSDAY

However, when I try to use the Python functionality I get nowhere. Even without any arguments into tdodbc.connect() it is the same error. 

import teradata as td
td.version.__version__
>>'15.10.0.21'
from teradata import tdodbc

conn = tdodbc.connect(system="Terada ODBC DSN",odbcLibPath="/Library/Application Support/teradata/client/16.20/lib/tdataodbc_sbu.dylib")
---------------------------------------------------------------------------
AttributeError                            Traceback (most recent call last)
<ipython-input-14-48b1060b0e7c> in <module>()
      1 conn = tdodbc.connect(system="Teradata ODBC DSN",
----> 2                       odbcLibPath="/Library/Application Support/teradata/client/16.20/lib/tdataodbc_sbu.dylib")

/Users/ash/miniconda3/envs/py27/lib/python2.7/site-packages/teradata/tdodbc.pyc in __init__(self, dbType, system, username, password, autoCommit, transactionMode, queryBands, odbcLibPath, dataTypeConverter, driver, **kwargs)
    419 
    420         # Initialize connection handle
--> 421         init(odbcLibPath)
    422 
    423         # Build connect string

/Users/ash/miniconda3/envs/py27/lib/python2.7/site-packages/teradata/tdodbc.pyc in init(odbcLibPath)
    365         lock.acquire()
    366         initOdbcLibrary(odbcLibPath)
--> 367         initFunctionPrototypes()
    368         initOdbcEnv()
    369         initDriverList()

/Users/ash/miniconda3/envs/py27/lib/python2.7/site-packages/teradata/tdodbc.pyc in initFunctionPrototypes()
    296     prototype(odbc.SQLBindCol, SQLHANDLE, SQLUSMALLINT, SQLSMALLINT,
    297               SQLPOINTER, SQLLEN, PTR(SQLLEN))
--> 298     prototype(odbc.SQLDrivers, SQLHANDLE, SQLUSMALLINT, PTR(SQLCHAR),
    299               SQLSMALLINT, PTR(SQLSMALLINT), PTR(SQLCHAR), SQLSMALLINT,
    300               PTR(SQLSMALLINT))

/Users/ash/miniconda3/envs/py27/lib/python2.7/ctypes/__init__.pyc in __getattr__(self, name)
    377         if name.startswith('__') and name.endswith('__'):
    378             raise AttributeError(name)
--> 379         func = self.__getitem__(name)
    380         setattr(self, name, func)
    381         return func

/Users/ash/miniconda3/envs/py27/lib/python2.7/ctypes/__init__.pyc in __getitem__(self, name_or_ordinal)
    382 
    383     def __getitem__(self, name_or_ordinal):
--> 384         func = self._FuncPtr((name_or_ordinal, self))
    385         if not isinstance(name_or_ordinal, (int, long)):
    386             func.__name__ = name_or_ordinal

AttributeError: dlsym(0x7f8db4449f40, SQLDrivers): symbol not found

Environment Variables set in ~.zshrc (same as ~.bash_profile)

>>>echo $DYLD_LIBRARY_PATH
/Library/Application Support/teradata/client/ODBC:/Library/Application Support/teradata/client/16.20/lib/

>>>echo $ODBCSYSINI
/Library/Application Support/teradata/client/16.20/odbc

>>>echo $ODBCINST
/Library/Application Support/teradata/client/16.20/odbc/odbcinst.ini

>>>echo $ODBCINI
/Library/Application Support/teradata/client/16.20/odbc/odbc.ini

>>>echo $DYLD_LIBRARY_PATH
/Library/Application Support/teradata/client/16.20/lib/

ODBC.ini Location: ~/Library/ODBC/odbc.ini

[ODBC Data Sources]
MYTDSERVER = Teradata Database ODBC Driver 16.20

[ODBC]
Trace         = 0
TraceAutoStop = 0
TraceFile     =
TraceLibrary  =

[MYTDSERVER]
Driver        = /Library/Application Support/teradata/client/16.20/lib/tdataodbc_sbu.dylib
CharacterSet  = UTF8
DBCName       = myserver.server.com
MechanismName = TDNEGO
Password      = $tdwallet(mypassword)
Username      = myusername
UID = myusername
PWD = mypassword
InstallDir    = /Library/Application Support/teradata/client/16.20/lib/

ODBCINST.ini Location: ~/Library/ODBC/odbcinst.ini

[ODBC Drivers]
Teradata Database ODBC Driver 16.20=Installed

[Teradata Database ODBC Driver 16.20]
Description=Teradata Database ODBC Driver 16.20
Driver=/Library/Application Support/teradata/client/16.20/lib/tdataodbc_sbu.dylib
Setup=/Library/Application Support/teradata/client/16.20/lib/TeradataODBCSetup.bundle/Contents/MacOS/TeradataODBCSetup

[ODBC Connection Pooling]
PerfMon    = 0
Retry Wait =

ODBC.ini file in Teradata Directory: Location:/Library/Application Support/teradata/client/16.20/odbc/odbc.ini

[ODBC]
Trace=no

[ODBC Data Sources]
Teradata ODBC DSN=Teradata Database ODBC Driver 16.20

[Teradata ODBC DSN]
# This key is not necessary and is only to give a description of the data source.
Description=Teradata Database ODBC Driver 16.20

# Driver: The location where the ODBC driver is installed to.
Driver=/Library/Application Support/teradata/client/16.20/lib/tdataodbc_sbu.dylib

# Required: These values can also be specified in the connection string.
DBCName=myserver.server.com
UID=myusername
PWD=mypassword

# Optional
AccountString=
CharacterSet=ASCII
DatasourceDNSEntries=
DateTimeFormat=AAA
DefaultDatabase=
DontUseHelpDatabase=0
DontUseTitles=1
EnableExtendedStmtInfo=1
EnableReadAhead=1
IgnoreODBCSearchPattern=0
LogErrorEvents=0
LoginTimeout=20
MaxRespSize=65536
MaxSingleLOBBytes=0
MaxTotalLOBBytesPerRow=0
MechanismName=
NoScan=0
PrintOption=N
retryOnEINTR=1
ReturnGeneratedKeys=N
SessionMode=System Default
SplOption=Y
TABLEQUALIFIER=0
TCPNoDelay=1
TdmstPortNumber=1025
UPTMode=Not set
USE2XAPPCUSTOMCATALOGMODE=0
Use32KMaxCharColumnSize=0
UseDataEncryption=0
UseDateDataForTimeStampParams=0
UseEmptyCreateParamsColumnforTimeStamp=0
USEINTEGRATEDSECURITY=0
USEREGIONALSETTINGS=1
UseSequentialRetrievalOnly=0
UseXViews=0

ODBCINST.ini file in Teradata Directory: Location:/Library/Application Support/teradata/client/16.20/odbc/odbcinst.ini

[ODBC Drivers]
Teradata Database ODBC Driver 16.20=Installed

[Teradata Database ODBC Driver 16.20]
Description=Teradata Database ODBC Driver 16.20
Driver=/Library/Application Support/teradata/client/16.20/lib/tdataodbc_sbu.dylib
Setup=/Library/Application Support/teradata/client/16.20/lib/TeradataODBCSetup.bundle/Contents/MacOS/TeradataODBCSetup

ODBCINST.ini file in the generic Teradata Directory: Location: /Library/Application Support/teradadata/client/ODBC/odbcinst.ini

[ODBC Drivers]
Teradata Database ODBC Driver 16.20=Installed

[Teradata Database ODBC Driver 16.20]
Description=Teradata Database ODBC Driver 16.20
Driver=/Library/Application Support/teradata/client/ODBC/lib/tdataodbc_sbu.dylib
Setup=/Library/Application Support/teradata/client/ODBC/lib/TeradataODBCSetup.bundle/Contents/MacOS/TeradataODBCSetup
escheie commented 6 years ago

PyTd is expecting to load the ODBC Driver Manager library not the Teradata ODBC Driver library. Looks like you are providing the Teradata OBDC Driver path for odbcLibPath which does not implement the SQLDrivers function.

What is the error you get when you don't specify the odbcLibPath and what do the logs show? Here is what my logs show with it working:

2017-12-18 17:34:37,810 - teradata.tdodbc - INFO - Loading ODBC Library: libiodbc.dylib
2017-12-18 17:34:37,822 - teradata.tdodbc - INFO - Available drivers: Teradata, Teradata Database ODBC Driver 16.10, Teradata Database ODBC Driver 16.20
2017-12-18 17:34:37,823 - teradata.tdodbc - WARNING - More than one driver found for 'Teradata'.  Using 'Teradata Database ODBC Driver 16.20'.  Specify the 'driver' option to select a specific driver.
ausiddiqui commented 6 years ago

Hey thanks for answering so quickly. Even if I were to run 

from teradata import tdodbc
conn = tdodbc.connect()

I get the same exact error. Not sure what other odbc driver I could install? iODBC?

Stupid question, but how do I find the logs for these commands? Can you suggest any other changes to my .ini files and ENV variables, are they pointing to haphazard or wrong things?

escheie commented 6 years ago

You typically shouldn't access the tdodbc submodule directly but instead should use the UdaExec wrapper that sets up the logging context. Logs will print directly to console by default and to a file in a logs directory local to the working directory.

E.g.

import teradata
udaexec = teradata.UdaExec("Hello World!", "1.0")
session = udaexec.connect(method="odbc", system="td_host_name", username="myuser", password="mypass")
for row in session.execute(file="NDARDS_CLOB_USAGE.txt", delimiter=";;"):
   print(row)

iODBC use to be installed by default on OS X but maybe that has changed. Definitely recommend using iODBC Driver Manager.

The Teradata ODBC driver 16.20 documentation states:

Teradata ODBC driver is supported with iODBC driver manager (version 3.52.8) that is installed by default on Apple OS X. The Teradata Tools and Utilities install suite will not install ODBC Driver Manager along with ODBC Driver for Teradata.