TinyWorld – Part 4

Add SAP HANA business logic

Hello again!

In part 3 of the TinyWorld tutorial (Developing with XS Advanced: Create a SAP HANA based client-server application) we established the baseline of our application. Now, we take our first steps of filling it in with some meaningful code, starting with native SAP HANA code (also called stored procedures).

The Web IDE supports the development of SAP HANA stored procedures in the form of .hdbprocedure and .hdbfunction files. The language used by SAP HANA is called SQLScript.

Adding a stored procedure

To illustrate the use of stored procedures, we will create a new .hdbprocedure file to insert a new country to the database, but only if the country does not already exist, and the continent is valid.

Right-click the tinydb/src/ folder, and create a “New > Procedure”. Call it createCountry.  Add the following text to the file, and save it.

PROCEDURE “tinyworld.tinydb::createCountry” (

  IN im_country NVARCHAR(100), im_continent NVARCHAR(100),

  OUT ex_error NVARCHAR(100))

  LANGUAGE SQLSCRIPT SQL

  SECURITY INVOKER AS

  –READS SQL DATA AS

BEGIN

declare noc integer;

select count(*) into noc

  from “tinyworld.tinydb::tinyf.world”

  where “continent” = im_continent;

if :noc = 0 then

  ex_error := ‘ERROR: Continent ‘ || :im_continent || ‘ does not exist!’;

else

  select count(*) into noc

     from “tinyworld.tinydb::tinyf.country”

     where “name” = im_country;

  if :noc > 0 then

     ex_error := ‘ERROR: Country ‘ || :im_country || ‘ already exists!’;

  else

     insert into “tinyworld.tinydb::tinyf.country”

           values (im_country, im_continent);

  end if;

end if;

END;

Now, build the tinydb module again. After it is successfully built switch to another browser tab or window to load the RTT, where we will unit test and debug our procedure.

Unit testing the stored procedure

In the RTT, you can find the new deployed procedure under the HDI container used by the tinydb module. It should be located under “Catalog > <your user name>… > Procedures”. If you cannot find it check that the build was successful, and refresh the tree with the context menu “Refresh”.

When you select the procedure, a viewer opens on the definition. Right-click “> Invoke Procedure with UI”. Enter Spain for IM_COUNTRY and Europe for IM_CONTINENT in the wizard. Then, run the procedure by pressing in the toolbar. Try also with some non-existing continent.

Debugging the stored procedure

Before we can debug the procedure, we must activate the debugger by connecting it to the corresponding HDI container. Open the debug settings from the toolbar:

Select your HDI container in the “Service Name” field, and press the “Toggle Connection” button.

Click “Apply” and then close the dialog.

Once we have the debugger connected to the right service, we can add breakpoints in the code by clicking on the left of the desired line number.

Run the application again, as we did before using the context menu “Invoke Procedure with UI”.

And you can debug your code now, by examining the call stack, the values of parameters and other values. You can follow execution step by step with the “Step over” button, or resume execution until the next breakpoint with the “Resume” button.

Note: debugging of hdbfunctions is not supported yet.

Summary of part 4

We added native SAP HANA procedures to our TinyWorld application. In the next part of this TinyWorld application (Add business logic with Node.js), we will use Node.js to add more business logic to our application.

Other parts of this TinyWorld tutorial:

Part 1:  A TinyWorld Tutorial – Introduction

The basic totorial

Part 2:   Get started with the SAP Web IDE for SAP HANA

Part 3:   Create a SAP HANA based client-server application

Part 4:   Add SAP HANA business logic

Part 5:   Add business logic with Node.js

Advanced topics

Part 6:   Create Node.js unit tests

Part 7:   Under the hood

Part 8:   Source control

Part 9:   Application life cycle

Part 10: Add authentication

Part 11: Add authorization