Passing Parameters to Stored Procedures using PyODBC

Question

I'm using pyODBC to connect to a SQL Server 2005 express database. I've created a stored procedure in SQLServer express that takes 2 string parameters e.g stored_proc(inpu1, input2) these parameters are of type datetime. I have tested the stored proc using management studio and it does return an appropriate result. However when i try to call the stored procedure from python(i'm using Eclipse) I get the error.

pyodbc.DataError: ('22018', '[22018] [Microsoft][SQL Native Client]Invalid character value for cast specification (0) (SQLExecDirectW)')2/9/2011 12:00:03 2/9/2011 12:20:03

The function i'm calling is as follows:

def GetAlarmFrequencyTime(tstart,tend): 

print tstart, tend  
arguments=(tstart,tend)
local_cursor=conn.cursor()
local_cursor.execute("{call dbo.GetAlarmEventHistoryFrequency_TimeRange(tstart,tend)}")

resultset_rows=local_cursor.fetchall()
print "There are" , len(resultset_rows), "records"
for single_row in resultset_rows:
    print "|" ,single_row[0], "|" ,single_row[1],"|" 

local_cursor.close()

The line that's causing the trouble is

local_cursor.execute("{call dbo.GetAlarmEventHistoryFrequency_TimeRange(tstart,tend)}")

can anyone help me understand how I can pass multiple parameters to a stored procedure called using pyODBC. Do I need to convert tstart, and tend to a datetime format first, if so how? I have trued strptime, but even that's not succeeded, though i may be using it wrong

I've been playing a bit and found local_cursor.execute("call dbo.GetAlarmEventHistoryFrequency_TimeRange(?,?)}",[dt_start],[dt_end]) basically the syntax seems to be "call stored_proc(?),[params]. Now I think this is it, but when i run i get an Unhandled Win32 exception in Python.exe. Has anyone got experirence with pyODBC on windows, using Python 2.7? — user595985
– user595985, Commented Oct 20, 2011 at 13:45

user595985 · Accepted Answer · 2011-10-20 14:19:26Z

4

I finally got it, to work without crashing

local_cursor.execute("{call dbo.GetAlarmEventHistoryFrequency_TimeRange(tstart,tend)}")

is the wrong syntax. It should be

local_cursor.execute("{call dbo.GetAlarmEventHistoryFrequency_TimeRange(?,?)}",(tstart),(tend))

Special attention should be made to the special symbols {} and the way the parameters are passed in (tstart),(tend) local_cursor.execute("*{*call dbo.GetAlarmEventHistoryFrequency_TimeRange*(?,?)}",(tstart),(tend)*)

answered Oct 20, 2011 at 14:19

community wiki

user595985

Sign up to request clarification or add additional context in comments.

1 Comment

Muniu Over a year ago

Thanks for your answer. Followed the steps above but I keep getting an error: "error_message": "not all arguments converted during string formatting"

Collectives™ on Stack Overflow

Passing Parameters to Stored Procedures using PyODBC

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related