#**********************************************************************
# FILENAME : CallSPInsertRec.py
#
# DESCRIPTION :
# Illustrates using a stored procedure to perform several
# actions relating to inserting a record into a table. Returns
# two sets of results plus several integer values relating to
# the table used and the record inserted.
#
# Calls a stored procedure as follows:
#
# {CALL pyInsert_Record (?, ?, ?, ?)}
#
# ODBC USAGE :
# Drops and recreates a procedure 'pyInsert_Record'
#
# Loops asking user to input the values for 4 columns (until
# blank FirstName entered). Stored Procedure is called and
# performs the following:-
#
# Inserts new person record into TestTBL1 using user input
# Returns all records in the table, ordered by ID
# Returns all records that match new record firstname
# Returns number of matching records
# Returns number of records in table
# Returns identity of record inserted
# Returns error status
#
# The results returned are picked off in the same order that
# they are generated (important)
#
import pyodbc
# Function to display the contents of a record
def printRec (rec):
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 "-"
# Create Stored Procedure 'pyInsert_Record' Statement
sqlCreateSP="CREATE PROCEDURE pyInsert_Record (\
@pFirstName nvarchar(256), @pLastName varchar(256),\
@pAddress varchar(256), @pCity varchar(256)) AS \
DECLARE @MatchingRecs INT;\
DECLARE @TotalRecs INT;\
INSERT INTO TestTBL1 (FirstName, LastName, Address, City) \
VALUES (@pFirstName, @pLastName, @pAddress, @pCity); \
SELECT * FROM TestTBL1 ORDER BY PersonID; \
SELECT * FROM TestTBL1 WHERE FirstName=@pFirstName; \
SELECT @MatchingRecs=count(*) FROM TestTBL1 WHERE\
FirstName=@pFirstName; \
SELECT @TotalRecs=count(*) FROM TestTBL1; \
SELECT @MatchingRecs;\
SELECT @TotalRecs;\
SELECT @@IDENTITY;\
SELECT @@ERROR;"
# Drop Stored Procedure Statement
sqlDropSP="IF EXISTS (SELECT * FROM sys.objects \
WHERE type='P' AND name='pyInsert_Record') \
DROP PROCEDURE pyInsert_Record"
# Call Stored Procedure Statement
sqlExecSP="{call pyInsert_Record (?,?,?,?)}"
# Connect to datasource
conn=pyodbc.connect('DSN=DATASOURCE', autocommit=True)
# Create cursor associated with connection
cursor=conn.cursor()
print "\nStored Procedure is : pyInsert_Record"
# 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:
# Data for new record
firstName=raw_input("\nFirst Name : ")
if firstName=='':
quit()
lastName =raw_input("Last Name : ")
address =raw_input("Address : ")
city =raw_input("City : ")
# Call SP and trap Error if raised
try:
cursor.execute(sqlExecSP,firstName,lastName,address,city)
except pyodbc.Error, err:
print 'Error !!!!! %s' % err
# The first set of results is the entire table
print "\nEntire Table :"
# Fetch all rowset from execute
recs=cursor.fetchall()
# Process each record individually
for rec in recs:
printRec(rec)
# The next set of results expected is all records matching
# the FirstName field just added
print "\nMatching Records :"
if cursor.nextset()==True: # value passed IN as as parameter 2
for rec in cursor:
printRec(rec)
# Next comes the number of matching records
if cursor.nextset()==True:
for rec in cursor:
print "\nNumber of records matching '", firstName, "' : ", rec[0],
# Then the total number of records in the table
if cursor.nextset()==True:
for rec in cursor:
print "\nTotal number of records in table : ", rec[0],
# Then the identity of the record just added
if cursor.nextset()==True:
for rec in cursor:
print "\nIdentity of record just added : ", rec[0],
# And finally, the SQL status code
if cursor.nextset()==True:
for rec in cursor:
print "\nSQL Status at end of procedure : ", rec[0]
print ("\n\nComplete.")
# Close and delete cursor
cursor.close()
del cursor
# Close Connection
conn.close()
Further information
