Hyper SQL stored procedure doesn't return result set

Ignition
1

I’m trying to call a stored procedure in Hyper SQL by using createSProcCall and execSProcCall. However, no result set is returned. The stored procedure works outside of Ignition though.

Hyper SQL uses a cursor to return a result set from the stored procedure. Are cursors not supported in Ignition when calling a stored procedure?

2

Did you import the JDBC driver for HyperSQL in your gateway?

3

Yes and I’m able to connect to the database. The add, update, and delete stored procedures are working.

4

Can you post the stored procedure?

5

This isn’t the actual stored procedure. But it functions the same as the real one.

CREATE PROCEDURE MY_SCHEMA.GET_DATA(
IN IN_ID VARCHAR(50)
)
READS SQL DATA
DYNAMIC RESULT SETS 1
BEGIN ATOMIC
DECLARE RESULTS CURSOR WITH RETURN FOR
SELECT
t.ID,
t.DESCRIPTION
FROM MY_SCHEMA.MY_TABLE t
WHERE
(IN_ID IS NULL OR t.ID = IN_ID)
ORDER BY t.ID
FOR READ ONLY;
OPEN RESULTS;
END;

6

You might find this SO post useful: https://stackoverflow.com/questions/4343078/jdbc-returning-empty-result-set