Table input parameters and scalar output parameters

Question

I have the following table and the following stored procedure (simplified to the bare basics needed to demonstrate the problem):

CREATE TABLE T(C INT);
CREATE PROCEDURE PROC(IN T TABLE(C INT), OUT X INT) AS BEGIN
X = 5;
END;

From HANA Studio, I can CALL PROC(T, ?) and get 5. However, I have not found any good way to do the same from the hdbcli Python driver.

callproc doesn't seem to have any way to accept table parameters. Passing the table name as a string doesn't work:

>>> from hdbcli import dbapi
>>> conn = dbapi.connect(userkey='testkey')
>>> cur = conn.cursor()
>>> cur.callproc('PROC', ('T', '?'))
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/local/lib/python3.6/site-packages/hdbcli/dbapi.py", line 498, in callproc
    ret = self.__callproc(callproc, parameters)
  File "/usr/local/lib/python3.6/site-packages/hdbcli/dbapi.py", line 257, in __callproc
    return self.__cursor.callproc(operation, parameters)
hdbcli.dbapi.Error: (8, 'invalid argument: Input parameter is inadequate as table parameter: line 1 col 13 (at pos 12)')

execute can execute the procedure, but it doesn't seem to provide any way to access the output parameter. There's no result set for it:

>>> cur.execute('CALL PROC(T, ?)')
True
>>> cur.fetchone()
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/local/lib/python3.6/site-packages/hdbcli/dbapi.py", line 434, in fetchone
    raise ProgrammingError(0,"No result set")
hdbcli.dbapi.ProgrammingError: (0, 'No result set')

I've found one extremely awkward workaround, which I'm posting as an answer, but it's horribly verbose and doesn't interact well with default parameter values. Is there a good way to access the output parameter?

score 1 · Answer 1 · answered Jan 03 '19 at 18:42

The one workaround I've found is to wrap the procedure in an anonymous block and stuff the scalar into a SELECT in the anonymous block:

>>> cur.execute('''
... DO BEGIN
... DECLARE X INT;
... T_VAR = SELECT * FROM T;
... CALL PROC(:T_VAR, X);
... SELECT :X FROM DUMMY;
... END''')
True
>>> cur.fetchone()
(5,)

That's a lot of extra typing. We can save a wrapper procedure to do this for us instead of repeating the anonymous block each time, but that can lead to a lot of wrapper procedures if you have a lot of procedures to wrap. Also, it doesn't play well with default values. If you have the following procedure:

CREATE PROCEDURE PROC2(OUT X INT, IN T TABLE(C INT) DEFAULT T) AS BEGIN
X = 5;
END;

You can normally call it as CALL PROC2(?), but you can't call it from inside an anonymous block or stored procedure without passing an explicit value for T:

>>> cur.execute('''
... DO BEGIN
... DECLARE X INT;
... CALL PROC2(X);
... SELECT :X FROM DUMMY;
... END''')
Traceback (most recent call last):
  File "<stdin>", line 6, in <module>
  File "/usr/local/lib/python3.6/site-packages/hdbcli/dbapi.py", line 363, in execute
    ret = self.__execute(operation)
  File "/usr/local/lib/python3.6/site-packages/hdbcli/dbapi.py", line 249, in __execute
    ret = self.__cursor.execute(operation, parameters=parameters, iscall=iscall, scrollable=self._scrollable)
hdbcli.dbapi.Error: (7, 'feature not supported: Only table variable is allowed in input parameter in a nested call: line 1 col 15 (at pos 14)')

That exception is happening due to the T default value.

Table input parameters and scalar output parameters

1 Answers1