SQL Server 2005 + pyodbc + Stored Procedure

[anier...@gmail.com]

Hi,

I am trying to execute stored procedure from python / pyramid code. I am passing 3 input & 1 output parameter. But I am not able to receive output parameter back.

Here is Stored Procedure 

CREATE PROCEDURE MY_PROC
  @empID char(10),
  @oldEmpList XML,
  @newEmpList XML,
  @Status INT OUTPUT
AS 
  -- sp body
  SET @Status = 1
RETURN 
GO

Here is Python code that calling stored procedure

t = text('EXEC MY_PROC :empID, :oldEmpList, :newEmpList, :Status',
         bindparams=[bindparam('empID', type_=String, value='1234'),
                     bindparam('oldEmpList', type_=TEXT, value='<emp><id>1</id><id>2</id>'),
                     bindparam('newEmpList', type_=TEXT, value='<emp><id>e01</id><id>e02</id>'),
                     bindparam('Status', type_=Integer, value=0, isoutparam=True)])
result = CMS_DBSession.execute(t)
print result.out_parameters

Thanks

Aniruddha

[Michael Bayer]

as far as I know, pyodbc does not support OUT parameters.  The info they have on stored procs is here:

http://code.google.com/p/pyodbc/wiki/StoredProcedures

if you want to work with the Pyodbc cursor directly (or any other cursor, like that of pymssql perhaps):

conn = my_session.connection()

dbapi_conn = conn.connection

cursor = dbapi_conn.cursor()

the “isoutparam” flag is currently only understood by the cx_oracle dialect.

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[Aniruddha]

Thanks for reply. I checked the link & it says if stored procedure end with select statement, it will solve problem. 

In my stored procedure, I am passing XML data, getting all values from that xml & put into temp table. 

CREATE PROCEDURE MY_PROC
  @empID char(10),
  @oldEmpList XML,
  @newEmpList XML,
  @Status INT OUTPUT
AS 

  CREATE TABLE #table_temp(advisory_id INT);
  INSERT INTO #table_temp
  SELECT Tbl.record.value('.','INT')
  FROM @oldEmpList.nodes('/emp/id') AS Tbl(record);
  SELECT * FROM #table_temp;
RETURN 
GO

The above code get me error. If I just put select statement without inserting anything like

   SELECT Tbl.record.value('.','INT')
   FROM @oldEmpList.nodes('/emp/id') AS Tbl(record);

It gives me only first record. My guess is this is because of cursor. 

Is there any workaround for this?

 

Thanks & Regards
Aniruddha Gaikwad

==============================================================

With the new day, comes new strength and new thoughts.

--
You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/tbW_RVIRkGk/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.

[Michael Bayer]

On Apr 5, 2014, at 3:23 AM, Aniruddha <anier...@gmail.com> wrote:

Thanks for reply. I checked the link & it says if stored procedure end with select statement, it will solve problem. 

In my stored procedure, I am passing XML data, getting all values from that xml & put into temp table. 

CREATE PROCEDURE MY_PROC
  @empID char(10),
  @oldEmpList XML,
  @newEmpList XML,
  @Status INT OUTPUT
AS 
  CREATE TABLE #table_temp(advisory_id INT);
  INSERT INTO #table_temp
  SELECT Tbl.record.value('.','INT')
  FROM @oldEmpList.nodes('/emp/id') AS Tbl(record);
  SELECT * FROM #table_temp;
RETURN 
GO

The above code get me error. If I just put select statement without inserting anything like

   SELECT Tbl.record.value('.','INT')
   FROM @oldEmpList.nodes('/emp/id') AS Tbl(record);

It gives me only first record. My guess is this is because of cursor. 

Is there any workaround for this?

with Pyodbc and pretty much any other DBAPI, the cursor object is all we have to work with really.   I don’t know much about selecting into variables and temp tables with SQL Server, only that I’d tread carefully as those are the kinds of places where things don’t work very well.   This is why I gave you access to the cursor directly, just to figure out what works.