How to read a result set from a stored procedure with python

Third Party Software
Highlighted
Enthusiast

How to read a result set from a stored procedure with python

Hello,

I have made a stored procedure like:

REPLACE PROCEDURE TDB.Check_user_role (OUT aanwezige_users_role VARCHAR(300) )

DYNAMIC RESULT SETS 1

SQL SECURITY Creator

BEGIN

DECLARE cur1 CURSOR WITH RETURN ONLY FOR

SELECT us.username, rm.Rolename

FROM dbc.usersv us

INNER JOIN dbc.RoleMembers rm

ON (us.username = rm.grantee )

WHERE us.ownername = 'BELASTING_USERS'

AND rm.ROLENAME LIKE 'DG%'

ORDER BY 1, 2 ;

OPEN cur1;

SET aanwezige_users_role = 'test';

END;

 

The stored procedure is working fine under SQL Assistant and even calling it from python went fine.

Except in Python i only get the OUT value and not the content of "cur1"

The call in python looks like:

 

with udaExec.connect("${dataSourceName}") as session:
    TD_resultset = session.callproc("TDB.Check_user_role",(teradata.OutParam("aanwezige_users_role")))

 

Can anyone help to retrieve the result set of the stored procedure ?

 

1 REPLY
Teradata Employee

Re: How to read a result set from a stored procedure with python

Instead of invoking callproc directly from the session, create a cursor object for the session and invoke callproc via the cursor.