| Issue 143: | postgre bigint type unsupported | |
| 2 people starred this issue and may be notified of changes. | Back to list |
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
Dec 4, 2010
[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
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
(No comment was entered for this change.)
Status:
Investigating
Dec 5, 2010
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
[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
Can you confirm the "bitness" of you Linux & Python builds? i use yum install,not build.
Apr 27, 2012
I am having the same problem.
Apr 27, 2012
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
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? |