# FILENAME :    CallSPFindName.py
#               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.
#               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

print "\nStored Procedure is : pyFind_ByName"

# Drop SP if exists

# Create SP using Create statement

# Call SP with pairs of param values
for id in params:
        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
            print "-"                   # print - for empty column
        print "Last Name  : ",
        if rec[2]!=None:
            print rec[2][0:10]
            print "-"
        print "Address    : ",
        if rec[3]!=None:
            print rec[3][0:10]
            print "-"
        print "City       : ",
        if rec[4]!=None:
            print rec[4][0:10]
            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
del cursor

# Close Connection

See Also

Oracle is a registered trademark of Oracle Corporation and/or its affiliates.