2

I want to execute the procedure and get the output parameters

import pypyodbc

command = "DECLARE @l_Stat INT \r\n" \
                  "DECLARE @s_Ms VARCHAR(200) \r\n" \
                  "EXEC p_Log_InsertParam_2011v1 " \
                  "@l_TesterId=?, " \
                  "@l_ObiektId=?, " \
                  "@l_RejPId=?, " \
                  "@s_ParamName=?, " \
                  "@f_ParamValue=?, " \
                  "@f_ParamMinValue=?, " \
                  "@f_ParamMaxValue=?, " \
                  "@b_CheckParam=?, " \
                  "@l_Status=@l_Stat output, " \
                  "@s_Msg=@s_Ms output \r\n" \
                  "SELECT @l_Stat, @s_Ms\r\n"
connection = pypyodbc.connect(self.ConnectionString)
cursor = connection.cursor()
params=(453879185, 23192812, 645872, '/APL/CTRL_GZ/PID/SP', 35.0, 0, 0, True)
# params = (testerid,
#          obiektid,
#          rejpid,
#          paramname,
#          paramvalue,
#          paramminvalue,
#          parammaxvalue,
#          checkparam) """
result = cursor.execute(command, params)
pars = result.fetchall()[0]
print pars
if pars.__len__() >= 2:
    l_status = pars[0]
    s_msg = pars[1]
print "{}: {};".format(l_status, s_msg)

It runs well to the line result = cursor.execute(command, params) - procedure execute properly. Problem appears when I try to fetch the result in line pars = result.fetchall()[0]:

u'24000', u'[24000] [Microsoft][ODBC SQL Server Driver]Invalid Cursor State'

How can I get rid of this error?

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
Jaroslaw Matlak
  • 574
  • 1
  • 12
  • 23
  • Is this by any chance connected to character encoding? u24000 might be a [character code for this HAN-symbol](http://en.glyphwiki.org/wiki/u24000). Such *far-east-characters* tend to produce strange troubles used in encodings like `UTF-8`, where their encoding takes three bytes, sometimes even four. Several tools cannot cope with this. – Shnugo Nov 18 '16 at 12:17
  • @Shnugo - I don't think so. – Jaroslaw Matlak Nov 18 '16 at 12:26

1 Answers1

3

I was able to reproduce your issue when my p_Log_InsertParam_2011v1 stored procedure did not have

SET NOCOUNT ON;

as its first executable statement. When I added it at the beginning of my stored procedure the error went away.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • This fixed it for me as well running on Windows. Did not have an issue on Linux with FreeTDS. Using pypyodbc on both machines. – Dengar Apr 11 '19 at 03:29