tanrj / pyodbc

Automatically exported from code.google.com/p/pyodbc
MIT No Attribution
0 stars 0 forks source link

Data type 113 not supported #168

Open GoogleCodeExporter opened 8 years ago

GoogleCodeExporter commented 8 years ago
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.

Original issue reported on code.google.com by mhecht2...@googlemail.com on 3 Apr 2011 at 7:46

GoogleCodeExporter commented 8 years ago
The Python version is 2.5.5 for 32 bit.

Original comment by mhecht2...@googlemail.com on 3 Apr 2011 at 7:47

GoogleCodeExporter commented 8 years ago
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.

Original comment by mkleehammer on 20 May 2011 at 7:44

GoogleCodeExporter commented 8 years ago
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.

Original comment by mkleehammer on 16 Jul 2011 at 9:03

GoogleCodeExporter commented 8 years ago
How about Postgres? I has an interval datatype:
http://www.postgresql.org/docs/8.0/static/datatype-datetime.html

Original comment by dusan.sm...@gmail.com on 3 Jan 2012 at 12:53

GoogleCodeExporter commented 8 years ago
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).

Original comment by pad...@yahoo.com on 6 Jun 2012 at 1:45

GoogleCodeExporter commented 8 years ago
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

Original comment by mgagnon...@gmail.com on 19 Jan 2013 at 5:55

GoogleCodeExporter commented 8 years ago
I've found my workaround :

def convert28(value):
    return value.pop()

Original comment by mgagnon...@gmail.com on 19 Jan 2013 at 8:39