Skip to main content

Stored Functions

DB2Rest makes it very easy to invoke stored functions for different databases as REST API.

Stored Function Endpoint​

The API endpoint for calling stored function is /{dbId}/function/{funcName}, where dbId is the configured database identifier and funcName is the name of the stored function.

PostgreSQL​

Consider the stored function below:


CREATE OR REPLACE FUNCTION GetMovieRentalRateFunc(movieTitle varchar)
    RETURNS numeric
	LANGUAGE plpgsql
AS $$
	DECLARE
		rentalRate numeric;
	BEGIN
		SELECT rental_rate INTO rentalRate FROM film WHERE title = movieTitle;
		return rentalRate;
	END;
$$;


The GetMovieRentalRateFunc stored function accepts a varchar datatype and returns the numeric rental value. This stored function can be invoked using the following endpoint as shown below.


curl 'http://localhost:8080/v1/rdbms/pgsqldb/function/GetMovieRentalRateFunc' -i -X POST \
-H 'Content-Type: application/json;charset=UTF-8' \
-H 'Accept: application/json' \
-d '       {
"movieTitle": "ACADEMY DINOSAUR"
}
'

The response is shown below:

HTTP/1.1 200 OK
Content-Type: application/json
Content-Length: 20

{"returnvalue":0.99}

MySQL​

Consider the MySQL stored function below:


CREATE FUNCTION GetMovieRentalRateFunc(movieTitle varchar(100))
    RETURNS DECIMAL(4, 2)
    DETERMINISTIC
BEGIN
        DECLARE rentalRate DECIMAL(4, 2);
        SET rentalRate = 0.00;
        SELECT rental_rate INTO rentalRate FROM film WHERE title = movieTitle;
        RETURN (rentalRate);
END;


curl 'http://localhost:8080/v1/rdbms/pgsqldb/function/GetMovieRentalRateFunc' -i -X POST \
-H 'Content-Type: application/json;charset=UTF-8' \
-H 'Accept: application/json' \
-d '       {
"movieTitle": "ACADEMY DINOSAUR"
}
'

The response is shown below:

HTTP/1.1 200 OK
Content-Type: application/json
Content-Length: 20

{"returnvalue":0.99}
info

The example above shows how DB2Rest provides a unified interface to call stored procedure for any database. The API calls remain same irrespective of the database system.

Other Supported Databases​

  • MariaDB
  • Microsoft SQLServer
danger

Oracle Stored Function is not supported as of current version.

PostgreSQL Issues​

In case of PostgreSQL Server version >= 11, ensure that the DB_URL has the escapeSyntaxCallMode parameter set to callIfNoReturn.

DB_URL=jdbc:postgresql://localhost:5432/sakila?escapeSyntaxCallMode=callIfNoReturn

In the absence of this parameter the following error may be shown:

Hint: To call a procedure, use CALL.