| Issue 168: | Data type 113 not supported | |
| 3 people starred this issue and may be notified of changes. | Back to list |
What steps will reproduce the problem?
I try to select an Informaix DB with a column type
INTERVAL MINUTE(3) TO SECOND
What is the expected output? What do you see instead?
I expect to get a column like "17:33" but get instead the error message
pyodbc.Error: ('ODBC data type 113 is not supported. Cannot read column MyIntervalColumn.', 'HY000')
What version of the product are you using? On what operating system?
I'm using the newest Informix ODBC-Driver 3.50 TC8 as 32bit version on Windows 7 64bit.
Please provide any additional information below.
If I use Access 2003 32 bit I get this column as Text, i.e. Access converts the column itselv to a known data type.
Apr 3, 2011
#1
mhecht2...@googlemail.com
May 20, 2011
ODBC assigns integers to represent each type. Database drivers can (but rarely) make their own types and assign their own integers. After a select statement, pyodbc inspects all of the types so it knows how to convert the results to a Python object. As you can tell, 113 is not a standard data type. First, if you need a quick work around, modify your SQL to cast to a known type. (This is SQL Server syntax, but something like this) select cast(a as char(5)) as a, ... Recent versions of pyodbc also allow you to set a function that is called when reading particular values: cnxn.add_output_converter(113, convert113) The problem is what convert113 should do. It will be passed a string object (str) which is the binary value. It should return the Python object you want. The simplest thing to do is to return the same object so you can at least look at it: def convert113(value): return value Long term, if 113 is a standard value I would be happy to add it. If not, perhaps I could come up with some standard conversion functions and you would simply have to add the output converters after connecting. I'll look into this.
Status:
Investigating
Jul 16, 2011
The type is an interval type which I haven't written support for yet. I tried installing Informix but had nothing but trouble. (I made an incorrect choice during the install and could not get it to change, even by uninstalling and reinstalling.) I'll need to find another DB that supports intervals (which are a standard ODBC type) and write it on OS/X or Linux. Suggestions welcome.
Jan 3, 2012
How about Postgres? I has an interval datatype: http://www.postgresql.org/docs/8.0/static/datatype-datetime.html
Jun 5, 2012
One more vote from a Teradata user. Teradata also supports INTERVAL which is not supported by pyodbc. One twist with INTERVAL data type is, there is no one single odbc type. For example, INTERVAL MINUTE TO SECOND is 113, whereas INTERVAL MINUTE is 105, INTERVAL HOUR TO SECOND is 112 and so on. Also, Teradata now supports PERIOD data type which is not supported by pyodbc either. (Let me know if this should be a separate defect).
Jan 19, 2013
I need help about this error :
when i do : for col in cursor..columns(table="ajustinv") : print col
i get :
....
(None, None, 'ajustinv', 'NOCOMPAGNIE', -6, 'TINYINT', 3, 1, 0, 10, 0, None, 'NULL', -6, None, None, 5, 'NO')
...
But, if i do a select on this table like this :
x = cnx.execute("select * from ajustinv")
i get this error :
Traceback (most recent call last):
File "<pyshell#84>", line 1, in <module>
x = cnx.execute("select * from ajustinv")
Error: ('ODBC data type -28 is not supported. Cannot read column NOCOMPAGNIE.', 'HY000')
now the type is not -6 anymore but -28
---
i'd tried to add the convert work around :
def convert28(value) :
return value
cnx.add_output_converter(-28,convert28)
not when i ask to print table now i got a bytearray like this :
(...) , bytearray(b'\x03'), (....)
but i need a integer not bytearray. How i should do to get a integer (or a long as it's described somewhere in pyodbc documentation)
may be my workaround should be to do a better conversion in my convert method but i dont know what i should do, im a beginner in python. Please help me
I must tell you some details about my system :
the drivers is an odbc driver of an weird database : topSpeed database and, since this driver is not available on linux, i'm working on winXP
tank you for your help
Jan 19, 2013
I've found my workaround :
def convert28(value):
return value.pop()
|