|
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) |
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', )
f your stored procedure returns more than one table, you can move to next table (set of rows) using command cursor.nextset()
hi if the StoredProc? changes data is commit() required ?
My python application is on Linux environment accessing a remote MS SQL Server for data, using a stored procedure. What works for me is like what sameh.na...@gmail.com, Jan 26, 2012 above said. My python statement:
cursor.execute("exec OID_getFacultyClassInfo 803381007, '12S'") <--- this works, however if I change the hard coded input as variables, it errors:
cursor.execute("exec OID_getFacultyClassInfo @uid, @term")
pyodbc.ProgrammingError?: ('42000', '42000? FreeTDS?Server?Must declare the scalar variable "@uid". (137) (SQLExecDirectW)')
What should be the correct syntax?
cursor.execute("declare @uid=<value_1>, @term=<value_2>; exec OID_getFacultyClassInfo @uid, @term")
you can replace the <value_1> and <value_2> with real values from within the python code dynalically
Quote: Since 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.
Example? Or is this just a case of having the stored procedure end up putting data into a table then reading that table?
When I run: cursor.execute("""{call myStoredProcedure('abc')}""") I get this 'myStoredProcedure' is not a macro.
Why is Teradata thinking that myStoredProcedure is macro and not a stored procedure?
I am able to run the usual select commands on the database.
I was able to use SP's against MSSQL using cursor.execute("{call dbo.my_procedure('some variable')}")
http://anekajaringmurah.blogspot.com/ http://pusatjaringmurah.blogspot.com/ http://anekajaringpengaman.blogspot.com/ http://agenjaringpengaman.blogspot.com/ http://jaringpengamanfutsal.blogspot.com/ http://jaring-pengamanmurah.blogspot.com/ http://jaringcenter.blogspot.com/ http://agenjaringjakata.blogspot.com/ http://jualjaringpengamanmurah.blogspot.com/ http://jaringsafetyjakarta.blogspot.com/ http://jaringpengaman-murah.blogspot.com/ http://jaringmurah.blogspot.com/ http://jaring-murah.blogspot.com/ http://jaringpengamanmurah.blogspot.com/ http://jaringbangunan.blogspot.com/ http://agenjaringsafety.blogspot.com/ http://sentral-jaring.blogspot.com/ http://sentraljaring.blogspot.com/ http://tokojaringpengaman.blogspot.com/ http://pusatjaringjakarta.blogspot.com/ http://tokojaringpengamanmurah.blogspot.com/ http://jualjaringsafetymurah.blogspot.com/ https://pancasamudera.wordpress.com/ https://pasangjaringfutsal.wordpress.com/ https://jualtambangmurah.wordpress.com/ https://tokojaring.wordpress.com/ https://jualjaringfutsal.wordpress.com/ https://jaringfutsal.wordpress.com/