Installation guide

mxODBC - Python ODBC Database Interface

Especially for numeric data, this may both be inefficient and inconvenient, so it's

better to pass in a value which matches the output parameter type such as 0 for

integer or 0.0 for floating point data.

5.6.5 Using Result Sets for passing back Output Data

It is also possible and to pass back data from the stored procedure via standard

result sets which can be fetched from mxODBC using the cursor.execute*()

methods.

This method is often preferred when dealing with larger data chunks, or table

data.

Using result sets to pass back output data

Passing back such data in form of one or more result sets allow for great flexibility

in exchanging data between stored procedures and your Python application, also

let's you implement variable length output parameter lists and special output value

conversions.

This can easily be done by adding a SELECT to the stored procedure which then

returns the data as additional result set:

SELECT OutputParam1, OutputParam2

or even using multiple result sets:

SELECT OutputParam1; SELECT OutputParam2

You can then pick up the data using cursor.nextset() and

cursor.fetchall():

rs1 = c.fetchall()

c.nextset()

rs2 = c.fetchall()

c.nextset()

rs3 = c.fetchall()

Example:

>>> c.execute('select 1; select 2')

>>> c.fetchall()

[(1,)]

>>> c.nextset()

True

>>> c.fetchall()

[(2,)]

MS SQL Server and Sybase ASE Cursors in Stored Procedures

MS SQL Server and Sybase ASE both make the result sets from SELECTs in the

stored procedures available to the mxODBC cursor via

cursor.fetch*().

Example:

CREATE PROCEDURE sp_result_set

@a INTEGER