Stored Procedure issue

[Randy Hopkins]

Have Stored procedure that when executed directly on the i runs and returns a result set. however when trying to run from a python routine using PyODBC we get an error?

[Kevin Adler]

In the future, please put the information directly in the issue instead of attaching documents to it. This just adds more hoops to jump through while hiding the issue.

I’ve removed the document and added the callstack picture from the document here:

‌

It’s also better to not take pictures of text, but just copy and paste the text, since images aren’t searchable.

[Kevin Adler]

From the information given, I can’t really advise why there was no results and I’m unable to recreate it. Can you post your Python code?

[Randy Hopkins]

I am not the developer.. Just the messenger:)

See copies of code below;

Sample 1:

import os, sys

import string, types

import pyodbc as dbi

class Connect:

‌ def __init__(self):

‌ self.dbc = dbi.connect(

‌ driver = '{iSeries Access ODBC Driver}',

‌ system = 'P7DEV.midwestexp.com',

‌ database = 'REDACTED',

‌ uid = 'REDACTED',

‌ pwd = 'REDACTED')

‌ self.cursor = self.dbc.cursor()

‌

‌ def Logout(self):

‌ self.cursor.close()

‌ self.dbc.close()

‌

‌ def Execute(self, sql, fetch='none', params='none'):

‌ try:

‌ if params == 'none':

‌ self.cursor.execute(sql)

‌ else:

‌ self.cursor.execute(sql, params)

‌ except:

‌ return 'error', 'Your SQL Statement Returned an error: {} {}'.format(sys.exc_info()[0], sys.exc_info()[1])

‌

‌ if fetch == 'none':

‌ return 'Ok', ''

‌ elif fetch == 'fetchone':

‌ result = self.cursor.fetchone()

‌ elif fetch == 'fetchall':

‌ result = self.cursor.fetchall()

‌

‌ return result

‌

‌ def Fix(self, text):

‌ text = text.replace("'", "''")

‌ return text

‌

Sample 2

from flask import Blueprint, render_template

from datetime import datetime

import SPTEST.classes.db as db

import SPTEST.modules.homemod as hme

home = Blueprint('home', __name__)

REFRESH_TIME = 30

@home.route('/')

@home.route('/home')

def homeRoute():

‌ # retrieving a sql statement

‌ sql = hme.returnSPData()

‌ data = db.Connect()

‌ results = data.Execute(sql, 'fetchall')

‌ data.Logout()

‌ return render_template(

‌ 'home/index.html',

‌ time_stamp = datetime.now().strftime("%m/%d/%Y %H:%M:%S"),

‌ refresh_time = REFRESH_TIME,

‌ results = results

‌ )

[Kevin Adler]

I can’t see anything from that code that would indicate why it would not have a result.

Looking at the PyODBC code, I see that the execute function calls SQLNumResultCols to get the number of result columns. If this is 0, then it will not call PrepareResults so cur->colinfos will be 0 (NULL). This causes this if code to be taken in the fetch code, resulting in the error seen.

Are you sure this stored procedure is generating a result? If so, it’s possible there’s a problem in the driver, in which case you should open a PMR with IBM.

[Kevin Adler]

No update in over 2 months, closing. Reopen if needed.