Calling functions do not work

[Salvora]

Hey,
just wanted to report this.
Environment:
MariaDB 10.11.6
db2rest docker
|:: db2rest version :: v1.4.2 |

|:: Spring-Boot version :: v3.3.5 |

I have created an SQL function,
when I call this function using SQL

SELECT search_series_group('Absolute') AS search_result;

it works

but when I try using db2rest to call it, I get this output every time.

{
  "type": "https://db2rest.com/error/generic-error",
  "title": "Generic Data Access Error",
  "status": 400,
  "detail": "Database ID not found.",
  "instance": "/error",
  "errorCategory": "Data-access-error",
  "timestamp": "2025-01-17T22:20:30.894810660Z"
}

I tried creating the same function as procedure and tried calling it again with db2rest and it worked. So, for some reason function calls do not work while procedure calls work.
Does not work

curl -X 'POST' \
  'http://SS:9532/v1/rdbms/eh/function/search_series_group_function' \
  -H 'accept: */*' \
  -H 'Content-Type: application/json' \
  -d '{
  "series_name": "Absolute"
}'

works

curl -X 'POST' \
  'http://SS:9532/v1/rdbms/eh/procedure/search_series_group_procedure' \
  -H 'accept: */*' \
  -H 'Content-Type: application/json' \
  -d '{
  "series_name": "Absolute"
}'

-- Create the search_series_group function
CREATE OR REPLACE FUNCTION search_series_group_function(series_name VARCHAR(255)) RETURNS JSON
    DETERMINISTIC
    READS SQL DATA
BEGIN
    DECLARE result JSON;

    -- Select matching series group from series_groups or via alternative names
    SELECT JSON_OBJECT(
               'series_group_id', sg.series_group_id,
               'series_group_name', sg.series_group_name,
               'shorthand_series_group_name', sg.shorthand_series_group_name
           )
    INTO result
    FROM series_groups sg
    LEFT JOIN series_group_alternative_names sgan ON sg.series_group_id = sgan.series_group_id
    WHERE sg.series_group_name = input_term
       OR sgan.alternative_series_group_name = input_term
    LIMIT 1;

    -- Return the JSON object or NULL if no matches
    RETURN IFNULL(result, NULL);
END;


CREATE OR REPLACE PROCEDURE search_series_group_procedure(IN series_name VARCHAR(255)) DETERMINISTIC READS SQL DATA BEGIN
DECLARE result JSON;
-- Select matching series group from series_groups or via alternative names
SELECT JSON_OBJECT(
        'series_group_id',
        sg.series_group_id,
        'series_group_name',
        sg.series_group_name,
        'shorthand_series_group_name',
        sg.shorthand_series_group_name
    ) INTO result
FROM series_groups sg
    LEFT JOIN series_group_alternative_names sgan ON sg.series_group_id = sgan.series_group_id
WHERE sg.series_group_name = series_name
    OR sgan.alternative_series_group_name = series_name
LIMIT 1;
-- Return the JSON object or NULL if no matches
SELECT IFNULL(result, NULL) AS search_result;
END;

[kdhrubo]

Ok i re-read this issue and understood it better now. We will triage this issue to see if we can replicate.

[kdhrubo]

@Salvora I noticed a possible error in your function v/s procedure in function you are not using the input series_name. That may cause the function to be not compiled correctly. Offcourse the error message from DB2Rest is not helpful either. I have created another issue to fix these error reporting. Please do take a look and let us know.