Skip to main content

Introduction to Named SQL

With named SQL it is possible to write SQL queries in an external file and turn it into DATA API endpoints.

Stored Procedure Endpoint​

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

PostgreSQL​

Consider the stored procedure below:


CREATE OR REPLACE PROCEDURE GetMovieRentalRateProc(movieTitle IN varchar, rentalRate OUT numeric)
	LANGUAGE plpgsql
AS  $$
	BEGIN
		SELECT rental_rate INTO rentalRate FROM film WHERE title = movieTitle;
	END;
$$;


The GetMovieRentalRateProc stored procedure accepts a varchar datatype and returns the numeric rental value. This stored procedure can be invoked using the following endpoint.


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

The returned movie rental rate is shown below:

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

{
    "rentalrate":0.99
}

MySQL​

Consider the MySQL stored procedure below:


CREATE PROCEDURE GetMovieRentalRateProc(IN movieTitle varchar(100), OUT rentalRate DECIMAL(4, 2))
    BEGIN
        SELECT rental_rate INTO rentalRate FROM film WHERE title = movieTitle;
    END;


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

The returned movie rental rate is shown below:

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

{
    "rentalrate":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 Procedure 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.