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


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