Accessing Stored Procedures using Python

The Adaptive Server Enterprise extension module for Python adds support for passing input and output parameters to stored procedures.

Use the callproc() method of the Cursor object to call a stored procedure. If there is an error in executing the stored procedure, callproc() throws an exception and you can retrieve the status value using the proc_status attribute. This support is an extension to the Python DBAPI specification.

This is a sample Python application with multiple row results:

import sybpydb
#Create a connection.
conn = sybpydb.connect(user='sa')
# Create a cursor object.
cur = conn.cursor()
# Call the stored procedure
try:
    cur.callproc('myproc')
    continue = True
    while(continue == True):
        row = cur.fetchall()
        continue = cur.nextset()
except sybpydb.Error:
    print("Status=%d" % cur.proc_status)

To specify output parameters, the extension module provides the OutParam constructor. This support is an extension to the Python DBAPI specification. The callproc() method returns a list of all the parameters passed to the method. If there are output parameters, and no result sets generated from the store procedure, the list contains the modified output values as soon as callproc() completes. However, if there are result sets, the list does not contain modified output values until all result sets from the stored procedure have been retrieved using the fetch*() methods and a call to nextset() is made to check if there are any more result sets. The nextset() method must be called even if only one result set is expected.

This is a sample Python application with output parameters:

import sybpydb
#Create a connection.
conn = sybpydb.connect(user='sa')
# Create a cursor object.
cur = conn.cursor()
cur.execute("""
    create procedure myproc
    @int1 int,
    @int2 int output
    as
    begin
        select @int2 = @int1 * @int1
    end
    """)
int_in = 300
int_out = sybpydb.OutParam(int())
vals = cur.callproc('pyproc', (int_in, int_out))
print ("Status = %d" % cur.proc_status)
print ("int = %d" % vals[1])
cur.connection.commit()
# Remove the stored procedure
cur.execute("drop procedure myproc")
cur.close()
conn.close()

More examples of different output parameter types are available in the sample program callproc.py.