Skip to Content
0

Call HANA stored procedure from Python with table type output parameter

Jan 05 at 09:17 AM

152

avatar image
I created the following procedure on HANA (Express Edition 2 on local VM):
CREATE PROCEDURE MYDB.MYSALES.TestProc(out output1 MYSCHEMA.TT_MYTYPE, out output2 MYSCHEMA.TT_MYTYPE) 
LANGUAGE SQLSCRIPT SQL SECURITY INVOKER READS SQL DATA AS
BEGIN
  output1=SELECT 8 as ATTRIBUTE1,5.0 as ATTRIBUTE2,'Hello' as ATTRIBUTE3 FROM DUMMY;
  output2=SELECT 1 as ATTRIBUTE1,2.0 as ATTRIBUTE2,'Hello2' as ATTRIBUTE3 FROM DUMMY;  
END;

Now, I want to call it and print the values of output parameters "output1" and "output2" using Python and dpapi. As far as I found out, it can be achieved using the cursor method "callproc" and fetching the result using the "fetch" methods of the cursor class:

cursor.callproc('MYDB.MYSCHEMA.TestProc', (0,0))
print(cursor.fetchall())

However, I only get the result for the first parameter in the parameter list (in this case "output1"):

[(8, Decimal('5'), 'Hello')]

How do I get the results of all parameters?

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
Patrick Steffens Jan 05 at 10:15 AM
1

I should've looked at the PEP249 dbapi documentation a bit better...there exists a method nextset() which sets the cursor to the next result set if available (in this case the one for "output2"). The lack of understanding I had here was that a result set contains multiple rows and each table type output paramater is represented by a separate result set. makes sense ;-)

Example code for retrieving both results:

cursor.callproc('MYDB.MYSCHEMA.TestProc', (0,0))
print(cursor.fetchall())
cursor.nextset() # returns "None" if no result set is left so this should be guarded for general usage
print(cursor.fetchall())
Share
10 |10000 characters needed characters left characters exceeded