pymssql stored procedures - NEW THREAD

[Aspersieman]

On Tue, 21 Oct 2008 20:44:24 +0200, Mike Hansen <projecktzero at yahoo.com>  
wrote:

> Hi,
>
> I saw your posting on the python list about pymssql and you mentioned  
> that you call stored procedures and get back output parameters. Are you  
> doing this on Windows or Linux? I can't seem to get it to work on Linux.  
> Have you gotten it to work on Linux?
>
> See this discussion on stackoverflow:
>
> http://stackoverflow.com/questions/191644/how-do-you-get-output-parameters-from-a-stored-procedure-in-python
>
> Thanks
>
>
>
>

Hi there

I hope you don't mind - I made this a new thread on the list, so others  
can maybe help out.

Although I use Linux quite often, I'm afraid MS SQL (with python) from  
Linux is something I have not tried yet. :-/

However - here are some things you might try:
	1) On the pymssql site (http://pymssql.sourceforge.net/#platforms) there  
is some documentation on using MS SQL (specifically with pymssql) from  
Linux - you need to use FreeTDS (http://www.freetds.org/) [although you  
probably know this already so... :)]
	2) (The following I have *only* tried on windows xp and server 2003 and  
SQL Server 2000)
	   I have tried various suggested ways to have pymssql (and indeed a  
couple of other python db libraries - which didn't work) - from extensive  
googling etc., return an output parameter for MS SQL server. The only  
method that works, for me at least, is the following - using pymssql:	
		* Assume stored procedure called : "sp_GetUserID" that has an output  
parameter declared as "@id".
		* Instead of using the "callproc" method of a cursor instead I execute  
the stored procedure as a normal sql statement that would return rows.
		import pymssql
		con = pymssql.connect	(host='xxxxx',user='xxxx',
			password='xxxxx',database='xxxxx')
		cur = con.cursor()
		query = "DECLARE @id INT; EXECUTE sp_GetUserID; SELECT @id;"
		cur.execute(query)
		outputparameter = cur.fetchall()
		con.commit()
		con.close()

		* You should now be able to access the output parameter from the list  
object "outputparameter".
		
HTH

Regards

Nicol

Let me know if thats a bit unclear - it's a bit late here :-p


-- 
Buffalo buffalo Buffalo buffalo buffalo buffalo Buffalo buffalo