CODEWITHSUNDEEP
pythonspyderpyodbc
happy_frappy_dingdong
happy_frappy_dingdong

Reputation: 87

Programing error: ('ODBC SQL type 102 is not yet supported. column-index=12 type=102', 'HY106')

I have series of Python and Teradata files that I inherited. Each file is dependent on another. I'm trying to make this a little easier to run and I'm using pyodbc to run the Teradata queries. One of the queries is running fine, but when run another query, it get the following error:

File "...\pandas\io\sql.py", line 1753, in _fetchall_as_list result = cur.fetchall()

ProgrammingError: ('ODBC SQL type 102 is not yet supported. column-index=12 type=102', 'HY106')

The queries are near identical, the only thing changing is a level of grouping (the first query that is working is more granular).

I'm not sure why one works but the other doesn't, but I wonder if it has to do with file that is being imported? I also thought it might have to do with my version of Teradata or Spyder being incompatible, but that wouldn't explain why one query is working but the other isn't. I'm fairly new to Python and I don't see anything on type 102 error. Any feedback would he greatly appreciated.

Upvotes: 2

Views: 3681

Answers (1)

DigitalDuquette
DigitalDuquette

Reputation: 49

So here's the thing, it's telling you the error, just not telling you well. To solve, change the SELECT statement to include one column at a time, when I get an error on a new column I'll convert in the query to a known good data type.

Example:

tableResult = pd.read_sql(
    "SELECT TOP 10 * FROM warehouse.equipment;", 
    cnxn
)

results in an error message. I don't need select *, that's bad practice anyway. Start adding one at a time. Usually it's dates because dates are hard.

tableResult = pd.read_sql(
    "SELECT TOP 10 equipment_id FROM warehouse.equipment;", 
    cnxn
)

Sometimes this is pretty trash too because who's the time? Then I'll fire this query and I'll know to convert:

select
    column_name, 
    data_type, 
    character_maximum_length, 
    collation_name
from information_schema.columns
where
    table_schema in ('warehouse')
    and table_name in ('equipment')
;

Now I can see that one column is returning datetimeoffset. You'll replace this process with your tables for your tech stack, but the answers the same.

Upvotes: 2

Related Questions