Issue 143: postgre bigint type unsupported
Status:  Investigating
Owner: ----
Reported by nmwe...@gmail.com, Dec 4, 2010
postgre:postgresql-odbc-08.03.0400-1PGDG.rhel5
odbc:unixODBC-2.2.11-7.1
pyodbc: 


Python 2.6.2 (r262:71600, Oct 18 2010, 10:26:31) 
[GCC 3.2.3 20030502 (Red Hat Linux 3.2.3-56)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import pyodbc
>>> pyodbc.version
'2.1.9-beta10' or '2.1.8'
>>> conn = pyodbc.connect("DRIVER={PostgreSQL};SERVERNAME=1.1.1.1;Port=5432;UID=aa;PWD=aa;DATABASE=webdb")
>>> rows = aa.execute("SELECT datname as dbname, pg_database_size(datname) as totalBlocks FROM pg_database;")
>>> for i in rows:
...  print i[0],i[1]
... 
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
pyodbc.Error: ('07006', '[07006] [unixODBC]Received an unsupported type from Postgres.;\nUnrecognized key passed to SQLGetInfo. (14) (SQLGetData)')
Dec 4, 2010
#1 nmwe...@gmail.com
pg_database_size(datname) is a bigint type

Dec 4, 2010
#2 nmwe...@gmail.com
[root@localhost src]# isql webdb aa aa
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> SELECT datname as dbname, pg_database_size(datname) as totalBlocks FROM pg_database;
+---------------------------------+---------------------+
| dbname                          | totalblocks         |
+---------------------------------+---------------------+
| template1                       | 4468332             |
| template0                       | 4468332             |
| postgres                        | 4468332             |
| test                            | 4779628             |
| webdb                           | 10989014636         |
+---------------------------------+---------------------+
SQLRowCount returns 5
5 rows fetched
SQL> 
Dec 4, 2010
#3 nmwe...@gmail.com
no pg_database_size(datname)
>>> rows = aa.execute("SELECT datname as dbname FROM pg_database;")
>>> for i in rows:
...   print i[0]
... 
template1
template0
postgres
test
webdb
Dec 5, 2010
Project Member #4 mkleehammer
(No comment was entered for this change.)
Status: Investigating
Dec 5, 2010
Project Member #5 mkleehammer
Interestingly, I'm not seeing this with 64-bit Python on 64-bit Linux:

  library: /home/mkleehammer/pyodbc2/build/lib.linux-x86_64-2.6/pyodbc.so
  odbc:    03.52
  driver:  psqlodbcw.so 08.04.0200
  driver supports ODBC version 03.51
  unicode: 4 sqlwchar: 2

Can you confirm the "bitness" of you Linux & Python builds?

Also, I think it interesting that the error message actually complains about SQLGetInfo, but the only time that is actually used is during the initial connection.  Can you provide an ODBC trace for this?  (You set the trace flag in the /etc/odbcinst.ini file, IIRC.  Must put that on the wiki.)
Dec 5, 2010
#6 nmwe...@gmail.com
[zenoss@localhost ~]$ cat /tmp/sql.log

[ODBC][15484][SQLFreeHandle.c][268]
                Entry:
                        Handle Type = 2
                        Input Handle = 0xd18d6a0
[ODBC][15484][SQLFreeHandle.c][317]
                Exit:[SQL_SUCCESS]
[ODBC][15484][SQLAllocHandle.c][345]
                Entry:
                        Handle Type = 2
                        Input Handle = 0xd143a50
[ODBC][15484][SQLAllocHandle.c][463]
                Exit:[SQL_SUCCESS]
                        Output Handle = 0xd18d6a0
[ODBC][15484][SQLDriverConnectW.c][246]
                Entry:            
                        Connection = 0xd18d6a0            
                        Window Hdl = (nil)            
                        Str In = [DRIVER={PostgreSQL};SERVERNAME=192.168.17.10;Port=5432;UID=aa;PWD=aa;DATABASE=webdb][length = 90]            
                        Str Out = (nil)            
                        Str Out Max = 0            
                        Str Out Ptr = (nil)            
                        Completion = 0
                UNICODE Using encoding ASCII 'ISO8859-1' and UNICODE 'UCS-2LE'

[ODBC][15484][SQLDriverConnectW.c][802]
                Exit:[SQL_SUCCESS_WITH_INFO]                    
                        Connection Out [[NULL]]
[ODBC][15484][SQLSetConnectAttr.c][318]
                Entry:            
                        Connection = 0xd18d6a0            
                        Attribute = SQL_ATTR_AUTOCOMMIT            
                        Value = (nil)            
                        StrLen = -5
[ODBC][15484][SQLSetConnectAttr.c][671]
                Exit:[SQL_SUCCESS]
[ODBC][15484][SQLGetInfo.c][214]
                Entry:            
                        Connection = 0xd18d6a0            
                        Info Type = SQL_DRIVER_ODBC_VER (77)            
                        Info Value = 0x7fff458bee20            
                        Buffer Length = 20            
                        StrLen = 0x7fff458bee5e
[ODBC][15484][SQLGetInfo.c][528]
                Exit:[SQL_SUCCESS]
[ODBC][15484][SQLGetInfo.c][214]
                Entry:            
                        Connection = 0xd18d6a0            
                        Info Type = SQL_DESCRIBE_PARAMETER (10002)            
                        Info Value = 0x7fff458bee50            
                        Buffer Length = 2            
                        StrLen = 0x7fff458bee5e
[ODBC][15484][SQLGetInfo.c][528]
                Exit:[SQL_ERROR]
                DIAG [S1C00] Unrecognized key passed to SQLGetInfo.

[ODBC][15484][SQLAllocHandle.c][510]
                Entry:
                        Handle Type = 3
                        Input Handle = 0xd18d6a0
[ODBC][15484][SQLAllocHandle.c][872]
                Exit:[SQL_SUCCESS]
                        Output Handle = 0xcfcd730
[ODBC][15484][SQLGetTypeInfo.c][164]
                Entry:            
                        Statement = 0xcfcd730            
                        Data Type = SQL_TYPE_TIMESTAMP
[ODBC][15484][SQLGetTypeInfo.c][314]
                Exit:[SQL_SUCCESS]
[ODBC][15484][SQLFetch.c][158]
                Entry:            
                        Statement = 0xcfcd730
[ODBC][15484][SQLFetch.c][340]
                Exit:[SQL_NO_DATA]
[ODBC][15484][SQLGetTypeInfo.c][164]
                Entry:            
                        Statement = 0xcfcd730            
                        Data Type = SQL_VARCHAR
[ODBC][15484][SQLGetTypeInfo.c][314]
                Exit:[SQL_SUCCESS]
[ODBC][15484][SQLFetch.c][158]
                Entry:            
                        Statement = 0xcfcd730
[ODBC][15484][SQLFetch.c][340]
                Exit:[SQL_SUCCESS]
[ODBC][15484][SQLGetData.c][224]
                Entry:            
                        Statement = 0xcfcd730            
                        Column Number = 3            
                        Target Type = 4 SQL_INTEGER            
                        Buffer Length = 4            
                        Target Value = 0x7fff458bee4c            
                        StrLen Or Ind = (nil)
[ODBC][15484][SQLGetData.c][470]
                Exit:[SQL_SUCCESS]                
                        Buffer = [254]                
                        Strlen Or Ind = NULLPTR
[ODBC][15484][SQLFreeStmt.c][140]
                Entry:            
                        Statement = 0xcfcd730            
                        Option = 0
[ODBC][15484][SQLFreeStmt.c][246]
                Exit:[SQL_SUCCESS]
[ODBC][15484][SQLGetTypeInfo.c][164]
                Entry:            
                        Statement = 0xcfcd730            
                        Data Type = Unknown(-9)
[ODBC][15484][SQLGetTypeInfo.c][314]
                Exit:[SQL_SUCCESS]
[ODBC][15484][SQLFetch.c][158]
                Entry:            
                        Statement = 0xcfcd730
[ODBC][15484][SQLFetch.c][340]
                Exit:[SQL_NO_DATA]
[ODBC][15484][SQLGetTypeInfo.c][164]
                Entry:            
                        Statement = 0xcfcd730            
                        Data Type = SQL_BINARY
[ODBC][15484][SQLGetTypeInfo.c][314]
                Exit:[SQL_SUCCESS]
[ODBC][15484][SQLFetch.c][158]
                Entry:            
                        Statement = 0xcfcd730
[ODBC][15484][SQLFetch.c][340]
                Exit:[SQL_NO_DATA]
[ODBC][15484][SQLAllocHandle.c][510]
                Entry:
                        Handle Type = 3
                        Input Handle = 0xd18d6a0
[ODBC][15484][SQLAllocHandle.c][872]
                Exit:[SQL_SUCCESS]
                        Output Handle = 0xcfddeb0
[ODBC][15484][SQLFreeStmt.c][140]
                Entry:            
                        Statement = 0xcfddeb0            
                        Option = 0
[ODBC][15484][SQLFreeStmt.c][246]
                Exit:[SQL_SUCCESS]
[ODBC][15484][SQLExecDirect.c][233]
                Entry:            
                        Statement = 0xcfddeb0            
                        SQL = [SELECT pg_database_size(datname) as sizeUsed FROM pg_database WHERE datname='webdb'][length = 83 (SQL_NTS)]
[ODBC][15484][SQLExecDirect.c][496]
                Exit:[SQL_SUCCESS]
[ODBC][15484][SQLRowCount.c][166]
                Entry:            
                        Statement = 0xcfddeb0            
                        Row Count = 0x7fff458bf1d8
[ODBC][15484][SQLRowCount.c][237]
                Exit:[SQL_SUCCESS]                
                        Row Count = 0x7fff458bf1d8 -> 1
[ODBC][15484][SQLNumResultCols.c][149]
                Entry:            
                        Statement = 0xcfddeb0            
                        Column Count = 0x7fff458bf1de
[ODBC][15484][SQLNumResultCols.c][234]
                Exit:[SQL_SUCCESS]                
                        Count = 0x7fff458bf1de -> 1
[ODBC][15484][SQLDescribeCol.c][231]
                Entry:            
                        Statement = 0xcfddeb0            
                        Column Number = 1            
                        Column Name = 0x7fff458bf080            
                        Buffer Length = 200            
                        Name Length = 0x7fff458bf15e            
                        Data Type = 0x7fff458bf15c            
                        Column Size = 0x7fff458bf154            
                        Decimal Digits = 0x7fff458bf15a            
                        Nullable = 0x7fff458bf158
[ODBC][15484][SQLDescribeCol.c][474]
                Exit:[SQL_SUCCESS]                
                        Column Name = [sizeused]                
                        Data Type = 0x7fff458bf15c -> -5                
                        Column Size = 0x7fff458bf154 -> 19                
                        Decimal Digits = 0x7fff458bf15a -> 0                
                        Nullable = 0x7fff458bf158 -> 1
[ODBC][15484][SQLColAttribute.c][277]
                Entry:            
                        Statement = 0xcfddeb0            
                        Column Number = 1            
                        Field Identifier = SQL_DESC_UNSIGNED            
                        Character Attr = (nil)            
                        Buffer Length = 0            
                        String Length = (nil)            
                        Numeric Attribute = 0x7fff458bf150
[ODBC][15484][SQLColAttribute.c][648]
                Exit:[SQL_SUCCESS]
[ODBC][15484][SQLDescribeCol.c][231]
                Entry:            
                        Statement = 0xcfddeb0            
                        Column Number = 1            
                        Column Name = 0x7fff458bf010            
                        Buffer Length = 300            
                        Name Length = (nil)            
                        Data Type = 0x7fff458bf14e            
                        Column Size = 0x7fff458bf144            
                        Decimal Digits = 0x7fff458bf14c            
                        Nullable = 0x7fff458bf14a
[ODBC][15484][SQLDescribeCol.c][474]
                Exit:[SQL_SUCCESS]                
                        Column Name = [sizeused]                
                        Data Type = 0x7fff458bf14e -> -5                
                        Column Size = 0x7fff458bf144 -> 19                
                        Decimal Digits = 0x7fff458bf14c -> 0                
                        Nullable = 0x7fff458bf14a -> 1
[ODBC][15484][SQLFetch.c][158]
                Entry:            
                        Statement = 0xcfddeb0
[ODBC][15484][SQLFetch.c][340]
                Exit:[SQL_SUCCESS]
[ODBC][15484][SQLGetData.c][224]
                Entry:            
                        Statement = 0xcfddeb0            
                        Column Number = 1            
                        Target Type = -25 SQL_C_SBIGINT            
                        Buffer Length = 8            
                        Target Value = 0x7fff458bf230            
                        StrLen Or Ind = 0x7fff458bf23c
[ODBC][15484][SQLGetData.c][470]
                Exit:[SQL_ERROR]                
                        Buffer = [Data...]                
                        Strlen Or Ind = 0x7fff458bf23c -> 0
                DIAG [07006] Received an unsupported type from Postgres.;
Unrecognized key passed to SQLGetInfo.

[ODBC][15484][SQLGetDiagRec.c][710]
                Entry:                
                        Statement = 0xcfddeb0                
                        Rec Number = 1                
                        SQLState = 0x7fff458bf190                
                        Native = 0x7fff458bf1a8                
                        Message Text = 0x7fff458bed90                
                        Buffer Length = 1023                
                        Text Len Ptr = 0x7fff458bf1ae
[ODBC][15484][SQLGetDiagRec.c][747]
                Exit:[SQL_SUCCESS]                    
                        SQLState = 07006                    
                        Native = 0x7fff458bf1a8 -> 14                    
                        Message Text = [[unixODBC]Received an unsupported type from Postgres.;
Unrecognized key passed to SQLGetInfo.]
[ODBC][15484][SQLFreeStmt.c][140]
                Entry:            
                        Statement = 0xcfddeb0            
                        Option = 0
[ODBC][15484][SQLFreeStmt.c][246]
                Exit:[SQL_SUCCESS]
[ODBC][15484][SQLFreeHandle.c][365]
                Entry:
                        Handle Type = 3
                        Input Handle = 0xcfddeb0
[ODBC][15484][SQLFreeHandle.c][462]
                Exit:[SQL_SUCCESS]
[ODBC][15484][SQLEndTran.c][315]
                Entry:            
                        Connection = 0xd18d6a0            
                        Completion Type = 1
[ODBC][15484][SQLGetInfo.c][214]
                Entry:            
                        Connection = 0xd18d6a0            
                        Info Type = SQL_CURSOR_ROLLBACK_BEHAVIOR (24)            
                        Info Value = 0x7fff458bf2fe            
                        Buffer Length = 8            
                        StrLen = 0x7fff458bf2fc
[ODBC][15484][SQLGetInfo.c][528]
                Exit:[SQL_SUCCESS]
[ODBC][15484][SQLEndTran.c][488]
                Exit:[SQL_SUCCESS]
[ODBC][15484][SQLDisconnect.c][204]
                Entry:            
                        Connection = 0xd18d6a0
[ODBC][15484][SQLDisconnect.c][341]
                Exit:[SQL_SUCCESS]
[ODBC][15484][SQLFreeHandle.c][268]
                Entry:
                        Handle Type = 2
                        Input Handle = 0xd18d6a0
[ODBC][15484][SQLFreeHandle.c][317]
                Exit:[SQL_SUCCESS]
Dec 5, 2010
#7 nmwe...@gmail.com
Can you confirm the "bitness" of you Linux & Python builds?
i use yum install,not build.

Apr 27, 2012
#8 Mike...@gmail.com
I am having the same problem.
Apr 27, 2012
#9 Mike...@gmail.com
SELECT datname as dbname, pg_database_size(datname) as totalBlocks FROM pg_database

Here is the output on the screen:
Traceback (most recent call last):
  File "./check-postgresql-stats.py", line 208, in <module>
    rc = main()
  File "./check-postgresql-stats.py", line 200, in main
    rc = ProcessRequest (server, database, userid, password, device, port)
  File "./check-postgresql-stats.py", line 146, in ProcessRequest
    rows = mycursor1.fetchall()
pyodbc.Error: ('07006', '[07006] [unixODBC]Received an unsupported type from Postgres.;\nUnrecognized key passed to SQLGetInfo. (14) (SQLGetData)')

Here is a snippet from the odbc trace file:
[ODBC][13046][SQLExecDirect.c][233]
		Entry:            
			Statement = 0x2cea540            
			SQL = [SELECT pg_database_size(datname) as totalBlocks FROM pg_database;][length = 65 (SQL_NTS)]
[ODBC][13046][SQLExecDirect.c][496]
		Exit:[SQL_SUCCESS]
[ODBC][13046][SQLRowCount.c][166]
		Entry:            
			Statement = 0x2cea540            
			Row Count = 0x7fff310f8dd8
[ODBC][13046][SQLRowCount.c][237]
		Exit:[SQL_SUCCESS]                
			Row Count = 0x7fff310f8dd8 -> 4
[ODBC][13046][SQLNumResultCols.c][149]
		Entry:            
			Statement = 0x2cea540            
			Column Count = 0x7fff310f8dde
[ODBC][13046][SQLNumResultCols.c][234]
		Exit:[SQL_SUCCESS]                
			Count = 0x7fff310f8dde -> 1
[ODBC][13046][SQLDescribeCol.c][231]
		Entry:            
			Statement = 0x2cea540            
			Column Number = 1            
			Column Name = 0x7fff310f8c80            
			Buffer Length = 200            
			Name Length = 0x7fff310f8d5e            
			Data Type = 0x7fff310f8d5c            
			Column Size = 0x7fff310f8d54            
			Decimal Digits = 0x7fff310f8d5a            
			Nullable = 0x7fff310f8d58
[ODBC][13046][SQLDescribeCol.c][474]
		Exit:[SQL_SUCCESS]                
			Column Name = [totalblocks]                
			Data Type = 0x7fff310f8d5c -> -5                
			Column Size = 0x7fff310f8d54 -> 19                
			Decimal Digits = 0x7fff310f8d5a -> 0                
			Nullable = 0x7fff310f8d58 -> 1
[ODBC][13046][SQLColAttribute.c][277]
		Entry:            
			Statement = 0x2cea540            
			Column Number = 1            
			Field Identifier = SQL_DESC_UNSIGNED            
			Character Attr = (nil)            
			Buffer Length = 0            
			String Length = (nil)            
			Numeric Attribute = 0x7fff310f8d50
[ODBC][13046][SQLColAttribute.c][648]
		Exit:[SQL_SUCCESS]
[ODBC][13046][SQLDescribeCol.c][231]
		Entry:            
			Statement = 0x2cea540            
			Column Number = 1            
			Column Name = 0x7fff310f8c10            
			Buffer Length = 300            
			Name Length = (nil)            
			Data Type = 0x7fff310f8d4e            
			Column Size = 0x7fff310f8d44            
			Decimal Digits = 0x7fff310f8d4c            
			Nullable = 0x7fff310f8d4a
[ODBC][13046][SQLDescribeCol.c][474]
		Exit:[SQL_SUCCESS]                
			Column Name = [totalblocks]                
			Data Type = 0x7fff310f8d4e -> -5                
			Column Size = 0x7fff310f8d44 -> 19                
			Decimal Digits = 0x7fff310f8d4c -> 0                
			Nullable = 0x7fff310f8d4a -> 1
[ODBC][13046][SQLFetch.c][158]
		Entry:            
			Statement = 0x2cea540
[ODBC][13046][SQLFetch.c][340]
		Exit:[SQL_SUCCESS]
[ODBC][13046][SQLGetData.c][224]
		Entry:            
			Statement = 0x2cea540            
			Column Number = 1            
			Target Type = -25 SQL_C_SBIGINT            
			Buffer Length = 8            
			Target Value = 0x7fff310f8d80            
			StrLen Or Ind = 0x7fff310f8d8c
[ODBC][13046][SQLGetData.c][470]
		Exit:[SQL_ERROR]                
			Buffer = [Data...]                
			Strlen Or Ind = 0x7fff310f8d8c -> 0
		DIAG [07006] Received an unsupported type from Postgres.;
Unrecognized key passed to SQLGetInfo.

[ODBC][13046][SQLGetDiagRec.c][710]
		Entry:                
			Statement = 0x2cea540                
			Rec Number = 1                
			SQLState = 0x7fff310f8ce0                
			Native = 0x7fff310f8cf8                
			Message Text = 0x7fff310f88e0                
			Buffer Length = 1023                
			Text Len Ptr = 0x7fff310f8cfe
[ODBC][13046][SQLGetDiagRec.c][747]
		Exit:[SQL_SUCCESS]                    
			SQLState = 07006                    
			Native = 0x7fff310f8cf8 -> 14                    
			Message Text = [[unixODBC]Received an unsupported type from Postgres.;
Unrecognized key passed to SQLGetInfo.]
[ODBC][13046][SQLFreeStmt.c][140]
		Entry:            
			Statement = 0x2cea540            
			Option = 0
[ODBC][13046][SQLFreeStmt.c][246]
		Exit:[SQL_SUCCESS]
[ODBC][13046][SQLFreeHandle.c][365]
		Entry:
			Handle Type = 3
			Input Handle = 0x2cea540
[ODBC][13046][SQLFreeHandle.c][462]
		Exit:[SQL_SUCCESS]
[ODBC][13046][SQLEndTran.c][315]
		Entry:            
			Connection = 0x2cc0da0            
			Completion Type = 1
[ODBC][13046][SQLGetInfo.c][214]
		Entry:            
			Connection = 0x2cc0da0            
			Info Type = SQL_CURSOR_ROLLBACK_BEHAVIOR (24)            
			Info Value = 0x7fff310f925e            
			Buffer Length = 8            
			StrLen = 0x7fff310f925c
[ODBC][13046][SQLGetInfo.c][528]
		Exit:[SQL_SUCCESS]
[ODBC][13046][SQLEndTran.c][488]
		Exit:[SQL_SUCCESS]
[ODBC][13046][SQLDisconnect.c][204]
		Entry:            
			Connection = 0x2cc0da0
[ODBC][13046][SQLDisconnect.c][341]
		Exit:[SQL_SUCCESS]
[ODBC][13046][SQLFreeHandle.c][268]
		Entry:
			Handle Type = 2
			Input Handle = 0x2cc0da0
[ODBC][13046][SQLFreeHandle.c][317]
		Exit:[SQL_SUCCESS]



Feb 5, 2013
#10 Sanders....@gmail.com
I just ran into a similar issue on a SLES 32-bit system. After a bit of looking around, I found an MS issue that is very similar

http://support.microsoft.com/kb/277033
*****
When the Microsoft OLEDB Provider for ODBC driver (MSDASQL) is using an ODBC driver that is ODBC 2.x compliant, and the driver reports a SQL_BIGINT datatype (-5), the ODBC provider (MSDASQL) chooses a C datatype of SQL_C_SBIGINT for use in the SQLBindCol() ODBC API function. However, because SQL_C_SBIGINT is an ODBC 3.x datatype, this generates the following error from the ODBC Driver Manager:
S1003 [Microsoft][ODBC Driver Manager] Driver does not support this parameter
This problem occurs in all releases of the ODBC provider (Msdasql.dll) version 2.50.4403.6 (MDAC 2.5 RTM) and later.
Back to the top | Give Feedback
CAUSE
When the ODBC provider first loads and uses an ODBC driver, the driver is queried to see what version of ODBC it supports, 2.x or 3.x. This information is stored in the connection handle class.

The provider keeps internal structures to do the datatype mapping between SQL datatypes (such as SQL_CHAR) and C datatypes (such as SQL_C_CHAR). The provider is not checking the version that it stored for the ODBC driver before performing the SQL-to-C datatype mapping, and simply maps the SQL datatype of SQL_BIGINT to an ODBC 3.x datatype of SQL_C_SBIGINT. This datatype is not recognized in an ODBC 2.x driver, and causes the ODBC Driver Manager to produce an error. 
*****
On my system, after some initial playing, it appears that the driver only supports ODBC v2.   Could this be the issues?