#**********************************************************************
# FILENAME : CallSPWithInOutParam.c
#
# DESCRIPTION :
# This example shows how, using SELECT at the end of a stored
# procedure, the generation of basic return values and output
# variables, can be implemented in pyodbc.
#
# Here we want to call a stored procedure that returns a status
# value, takes one input parameter and returns an output
# parameter, in the form:
#
# {? = CALL InOutRet_Params (?, ?)}
#
# In pyodbc the only option available for returning values is
# via data generated by a SELECT, which can then be picked up
# by fetchone() or fetchall(). This example shows how to use
# this method and returning values other than record sets.
#
# ODBC USAGE :
#
#
# Connects to Data Source using Data Source Name
# Creates cursor on the connection
# Drops and recreates a procedure 'pyFind_Record'
# Loops asking user to input a PersonID (until zero or
# invalid number entered).
# Executes the procedure using cursor.execute()
# Calls cursor.fetchall() to retrieve any row found
# For each row, displays column values
# Returns number of matching records in table
# Returns error status
# Closes and deletes cursor and closed connection
#
#
import pyodbc
# If OUTPUT and RETURN values were properly supported in python, the call
# to the procedure could be {?=CALL pyInOutRecs(?,?)} where parameter 1 is
# a return value, parameter 2 could be input and paramater 3 could be output.
# To do this we have to have the call as {CALL pyInOutRecs(?)} with just
# the input parameter. The RETURN value and the OUTPUT parameters are
# returned in a rowset generated by additional SELECT statements. See below.
# These can then be retrieved using cursor.nextset() follows by
# cursor.fetchone() or cursor.fetchall() for each.
sqlCreateSP="CREATE PROCEDURE pyInOutRet_Params (\
@pPersonID int) AS \
DECLARE @MatchingRecs int; \
DECLARE @RetCode int; \
SELECT PersonID, FirstName, LastName, Address, City \
FROM TestTBL1 WHERE PersonID=@pPersonID; \
SELECT @MatchingRecs=count(*) FROM TestTBL1 WHERE \
PersonID=@pPersonID; \
SELECT @MatchingRecs;\
SET @RetCode=@@ERROR; \
SELECT @RetCode;"
# Drop Stored Procedure Statement
sqlDropSP="IF EXISTS (SELECT * FROM sys.objects \
WHERE type='P' AND name='pyInOutRet_Params') \
DROP PROCEDURE pyInOutRet_Params"
# Call Stored Procedure Statement
sqlExecSP="{call pyInOutRet_Params (?)}"
# Connect to data source
conn=pyodbc.connect('DSN=DATASOURCE', autocommit=True)
# Create cursor associated with connection
cursor=conn.cursor()
print "\nStored Procedure is : pyInOutRet_Params"
# Drop SP if exists
cursor.execute(sqlDropSP)
# Create SP using Create statement
cursor.execute(sqlCreateSP)
# Loop - prompt for record details, insert and get results returned
while 1:
# Get PersonId to look up
userInput=raw_input("\nPerson ID : ")
# Check positive integer entered, quit if
# negative or not a number
try:
id=int(userInput)
except ValueError:
id=0
if userInput != "":
print "\nSorry, NAN"
if id<=0:
quit()
# Call SP and trap Error if raised
try:
cursor.execute(sqlExecSP,id)
except pyodbc.Error, err:
print 'Error !!!!! %s' % err
# Fetch all rowset from execute
recs=cursor.fetchall()
if len(recs)==0:
print "\nRecord not found."
else:
# Process each record individually
for rec in recs:
print "\nPersonID : ", rec[0]
print "First Name : ", # Comma on end stops new line being output
if rec[1]!=None: # None appears for empty column
print rec[1][0:10] # Print string from 0 upto 10
else:
print "-" # Print - for empty column
print "Last Name : ",
if rec[2]!=None:
print rec[2][0:10]
else:
print "-"
print "Address : ",
if rec[3]!=None:
print rec[3][0:10]
else:
print "-"
print "City : ",
if rec[4]!=None:
print rec[4][0:10]
else:
print "-"
# Get number of matching records - either 1 or 0
if cursor.nextset()==True:
for rec in cursor:
print "\nMatching Records : ", rec[0]
# And finally, the SQL status code
if cursor.nextset()==True:
for rec in cursor:
print "\nSQL Status : ", rec[0]
print ("\n\nComplete.")
# Close and delete cursor
cursor.close()
del cursor
# Close Connection
conn.close()
Further information
