|
StoredProcedures
Using stored procedures
CallingThe Python DB API specifies that Cursor objects should have a callproc method for calling stored procedures. This hasn't been implemented yet in pyodbc because there is no way for it to know which parameters are input and which are output. Since there won't be a way anytime soon, I'll probably implement callproc but make all of the parameters input-only. To call a stored procedure right now, pass the call to the execute method using either a format your database recognizes or using the ODBC call escape format. (The ODBC driver will then reformat the call for you to match the given database.) For SQL Server you would use something like this: # SQL Server format
cursor.execute("exec sp_dosomething(123, 'abc')")
# ODBC format
cursor.execute("{call sp_dosomething(123, 'abc')}")ResultsSince we can't use output parameters at this point, you'll need to return results in a result set. Usually this means just ending your stored procedure with a SELECT statement. TipsSome databases will return results for everything you do, including the row counts of any inserts or deletes. If you have these, you can "step over" them using Cursor.nextset(). If you are using SQL Server, you can use SET NOCOUNT ON to disable this. (Much cleaner) |
► Sign in to add a comment
using mysql: code work fine so far for select statements. But for stored proc. it just hangs.
>>> c.execute('call sp.mysp') <pyodbc.cursor object at 0x0221---->
The pre-tested and using store proc. does not work. Is the a bug or user error
using mssql,
cursor.execute('exec GetData? "startIndex" "endIndex" "field1;field2;foo;bar;spam;eggs;...."')
works fine to call a stored procedure called GetData?, but only when the last argument is less than 128 characters.
I believe this is because pyodbc thinks the final "field1;field2;foo;bar;spam;eggs;...." is a single field rather than the stored procedure knowing it's a concatenated string of field names. Is there a way to override pyodbc throwing an error for thinking i'm asking for a field name longer than the allowed length? using RODBC in R with the same string returns the expected results, so I know for a fact it's not the query.... Thanks.
Using MSSQL,
Calling stored procs this way using pyodbc causes Python to hang for a while before returning the result. The proc and what goes on inside it is not the issue, since it is fast when executed using sqlcmd. The proc is not terminated by a "direct" SELECT statement but rather: EXEC sp_executesql <dynamic_query> <params>. Could that be the issue? When I generate <dynamic_query_and_params> within Python and use pyodbc to execute it is as fast as you would expect.
This format is the the one that worked for me on SQL server
# SQL Server format cursor.execute("exec sp_dosomething 123, 'abc'")
I am trying to do the following, sp_helptext sp_copyAlertSettings where "sp_copyAlertSettings" is a procedure. this sort of does what I want, but stores the returned text , along with an assorted escape characters. I am wondering if there exists a better way to do this. As an example: printing the cursor values returned. (u'\nCREATE procedure dbo?.sp_copyAlertSettings?(\r\n', ) (u'\t@sourceNTUsername varchar(255),\r\n', ) (u'\t@sourceProfilename varchar(255),\r\n', ) (u'\t@targetNTUsername varchar(255),\r\n', ) (u'\t@targetProfilename varchar(255),\r\n', ) (u'\t@luversion varchar(512),\r\n', ) (u'\t@luuserid int,\r\n', ) (u'\t@luowningsite varchar(50)\r\n', )