Error while calling Hana stored procedure from python sqlalchemy

Question

I am getting an error when i call a stored procedure with table type input and a varchar out param from python using the sqlalchemy lib.Below mentioned is the error

Error while calling procedure from python sqlalchemy.exc.DatabaseError: (pyhdb.exceptions.DatabaseError) invalid argument: Input parameter is inadequate as table parameter:

parameter one is table type in and second is a varchar out

I am adding the code snippet which i am trying with

engine = create_engine("hana+pyhdb://{username}:{password}@{host}:{port}".format(username='username', password='password', host='hostname', port='30015'))

output="

input='[{"RULE_ID":1,"RULE_NAME":null,"SO_SSA":"1074","PO_NUMBER":null,"CODE":"DDD","ROUTE_CODE":"","OPERATING":null,"SHIP_TO":"IND","SHIP_TO_REGION":"MX","SHIP_TO_CUSTOMER_ID":null,"BILL_TO_CUSTOMER_ID":null,"END_TO_CUSTOMER_ID":null,"SLCA":"AKP123","HOLD_NAME":"Futures Approval Hold","SHIPPING_PREFERENCE":null,"EAD":null,"CUSTOMER_REQUEST_TYPE":null,"CRD":null,"CRSD":null,"CURRENT_PROMISE_DATE":null,"CURRENT_PROMISE_DELIVERY_DATE":null,"OPDATE":null,"OPDD":null,"ON_HOLD":null,"FLOW_STATUS_CODE":null,"PICK_RESULT":null,"IS_IN_OTM":null,"BUSINESS_UNIT":null,"REVENUE_FLAG":null,"ACTION_CATEGORY":null,"ACTION_OWNER":null,"SS_REVENUE":null,"CARTONS":null,"INVOICE_ELIGIBILITY_EVENT":null,"SALES_CHANNEL":null,"CREATED_BY":"NEW","CREATION_DATE":"2020-02-11 10:24PM","LAST_UPDATED_BY":"NEW","LAST_UPDATE_DATE":"2020-03-04 10:39PM","FDA_FLAG":null,"POE_FLAG":null,"CONSOLIDATED_FLAG":null,"START_DATE":"2019-01-02","END_DATE":"2020-01-02","ACTIVE":"T"}]'

test=engine.execute('call SCHEMA.PROC(?,?)',(input,output))

I receive the input as a JSON string from the UI.In HANA SP the input is type of Table Type.I am not sure how can i convert the input to a table type

Answer 1

The error message already says it: the expected parameter for the procedure is a table, but that is not what is provided when the procedure is called.

Important here to know is that “table type” for a parameter definition does not mean that a client program can somehow create a set of records, call it a table and pass it to the parameter.

Instead, one can pass actual SAP HANA tables or SQLScript table variables to the table type-parameter.

To circumvent this limitation, client programs have different options:

• create a (temporary) table, fill it with the records that should be passed into the procedure and call the procedure with this table’s name.
• use an anonymous SQLScript block, where the records are first collected into a table variable and then this table variable is passed to the procedure.

That could look something like this:

 DO BEGIN
      tab_var = select ‘some data’ as COL_1, 1234 as COL_2 from dummy union all 
                select ‘some other data’ as COL_1, 4711  as COL_2 from dummy;

     call my_procedure (tab_param => :tab_var );
 END;