0
1074
Question Adel Elsayed · Jul 13, 2020

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

#InterSystems IRIS #ODBC #Python

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

Comments

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
Adel Elsayed  Jul 14, 2020 to Eduard Lebedyuk

i call the procedure from pyodbc cursor object but i double checked, i think the issue would be in the stored procedure.

Thanks

0