#**********************************************************************
# FILENAME : CallSPFindName.py
#
# DESCRIPTION :
# This example simulates using parameter arrays with a stored
# procedure by emulating the parameter arrays using tuples in
# Python.
#
# We place the alphabet in a list of values and select records
# based on them, returning any records that have a 'like' match
# on the FirstName field in the table. The tuple index value
# is also returned to indicate which tuple generated the result
# along with the number of matching records.
#
# Stored procedures can only be called though the cursor
# execute() or executemany() methods at this time since
# CallProc(), the intended method, has yet to be implemented.
#
# Input parameters are catered for but output parameters and
# procedure return values are not. Although limited, these can
# be be emulated via result set generating SELECTs, the output
# from which can be accessed though nextset(), fetchone() and
# fetchall() in the program. This is demonstrated by this
# example.
#
# ODBC USAGE :
#
# Emulates parameter array by creating a tuple array containing
# pairs of parameters representing the alphabet to match the
# FirstName column in the table and an index value to indicate
# which tuple was used to generate the result.
#
# Connects to Data Source using Data Source Name
# Creates cursor on the connection
# Drops and recreates a procedure 'pyFind_ByName' which takes
# two parameters, one for each tuple entry.
# For each tuple,
# executes the procedure using cursor.execute()
# retrieves the results using cursor.fetchall() for the
# record details and cursor.nextset() and 'for rec in cursor'
# to retrieve the tuple index which was passed as param 2
# and again to retrieve the number of records returned.
# Closes and deletes cursor and closed connection
import pyodbc
# Call SP with pairs of param values. We could use cursor.executemany() here
# to take the whole tuple list, but this would only give us the results from
# the last param tuple. i.e. the record with FirstName LIKE = z and index 25.
# We want the results for each tuple and since drivers often emulate parameter
# arrays by executing an SQL statement once for each set of parameter values,
# this is effectively what is done here.
params = [ ('a', 0),\
('b', 1),\
('c', 2),\
('d', 3),\
('e', 4),\
('f', 5),\
('g', 6),\
('h', 7),\
('i', 8),\
('j', 9),\
('k', 10),\
('l', 11),\
('m', 12),\
('n', 13),\
('o', 14),\
('p', 15),\
('q', 16),\
('r', 17),\
('s', 18),\
('t', 19),\
('u', 20),\
('v', 21),\
('w', 22),\
('x', 23),\
('y', 24),\
('z', 25),\
]
# Stored Procedure Create Statement
sqlCreateSP="CREATE PROCEDURE pyFind_ByName \
(@pFirstName nvarchar(255), @pPersonIX int) AS \
DECLARE @RecCount INT;\
SELECT PersonID, FirstName, \
LastName, Address, City FROM TestTBL1 WHERE FirstName LIKE @pFirstName+'%' \
ORDER BY PersonID; \
SELECT @pPersonIX;\
SELECT @RecCount=count(*) FROM TestTBL1 WHERE FirstName LIKE @pFirstName+'%';\
SELECT @RecCount;"
# Stored Procedure Drop Statement
sqlDropSP="IF EXISTS (SELECT * FROM sys.objects \
WHERE type='P' AND name='pyFind_ByName') \
DROP PROCEDURE pyFind_ByName"
# Stored Procedure Call Statement
sqlExecSP = "{CALL pyFind_ByName(?, ?)}";
# Connect to datasource
conn=pyodbc.connect('DSN=DATASOURCE', autocommit=True)
# Create cursor associated with connection
cursor=conn.cursor()
print "\nStored Procedure is : pyFind_ByName"
# Drop SP if exists
cursor.execute(sqlDropSP)
# Create SP using Create statement
cursor.execute(sqlCreateSP)
# Call SP with pairs of param values
for id in params:
try:
cursor.execute(sqlExecSP, id)
except pyodbc.Error, err:
print 'Error !!!!! %s' % err
recs = cursor.fetchall() # rows generated by parameter 1
for rec in recs:
print "\nPersonID : ", rec[0]
print "First Name : ", # Comma on end stops new line being output
if rec[1]!=None: # None indicates 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 "-"
if cursor.nextset()==True: # value passed IN as as parameter 2
for rec in cursor: # passed OUT by SELECT @pPersonIX in
print "\nSelection was on (@p1) : ", params[rec[0]][0]
print "Tuple index was (@p2) : ", rec[0]
if cursor.nextset()==True: # additional record count returned by
for rec in cursor: # SELECT @RecCount
print "Records Returned : ", rec[0]
print ("\n\nComplete.")
# Close and delete cursor
cursor.close()
del cursor
# Close Connection
conn.close()
Further information
