New post
Question
· Jul 13, 2020

Pyodbc error upon calling a stored procedure that has ROWSPEC column or more as %Date or %Time

#ODBC #Python #InterSystems IRIS

Pyodbc supports calling stored procedure  , but if the IRIS Stored Procedure ROWSPEC has a column with datatype specified as %Date or %Time, Pyodbc fails to fetch it and throws this error.

"

pyodbc.Error: (' 2201', '[ 2201] [Cache ODBC][State :  22018 ][Native Code 22005]\r\nERROR #388: Unknown error, code 22005 (22005) (SQLGetData)')

"

on printing the cursor execute description, pyodbc reads the column as <int>.

and if the ROWSPEC datatype specification was removed, it fetches successfully.

i'm using the driver "InterSystems IRIS ODBC35"  2019.2.0.107

Discussion (3)1
Log in or sign up to continue
Eduard Lebedyuk · Jul 13, 2020

I'm also using ODBC3.5 and I was unable to reproduce your issue.

Here's what I tried:

import pyodbc
import pandas as pd
cnxn=pyodbc.connect(('DSN=MYDSN'),autocommit=True)
Data=pd.read_sql('SELECT TOP 10 DOB, RandomTime, TS FROM isc_py_test.Person',cnxn)
Data
Data.info()

And here's the output I got:

          DOB RandomTime                  TS
0  1977-07-18   07:49:03 1993-10-31 17:23:25
1  2001-11-08   07:45:05 2005-12-25 04:11:22
2  2004-02-20   23:17:49 1981-08-31 02:08:10
3  1995-11-22   01:46:31 2010-05-20 11:25:31
4  1974-01-09   15:20:03 1974-12-22 13:49:00
5  1987-10-19   23:14:52 1974-10-02 17:48:37
6  1985-03-29   17:47:12 1978-02-24 06:40:51
7  2015-10-21   23:09:15 2006-08-29 16:30:29
8  1972-12-26   15:53:23 1996-12-06 03:13:26
9  1990-09-25   05:53:25 2000-03-22 05:54:57

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 10 entries, 0 to 9
Data columns (total 3 columns):
DOB           10 non-null object
RandomTime    10 non-null object
TS            10 non-null datetime64[ns]
dtypes: datetime64[ns](1), object(2)
memory usage: 320.0+ bytes

Here's the source data I used (import and run Populate).

I recommend PythonGateway for Python workloads with InterSystems IRIS.

Can you share your dataset?

0 0