#********************************************************************** # 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()