Skip to Content
Author's profile photo Chaim Bendelac

Developing with XS Advanced: Add SAP HANA business logic


TinyWorld – Part 4


Add SAP HANA business logic

/wp-content/uploads/2016/03/image001_916485.png

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:

/wp-content/uploads/2016/03/image022_916710.png

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

/wp-content/uploads/2016/03/image023_916775.png

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”.

  /wp-content/uploads/2016/03/image024_916776.png

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

Assigned Tags

      4 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Yeshwanth Udayshankar
      Yeshwanth Udayshankar

      I am getting an error while trying to invoke the procedure (with and without UI). The erro happens only with parameters. Catalog tools version is 1.2.5

      PM (SQL Editor) Executing the SQL statement is not valid...

      Author's profile photo Frank Schuler
      Frank Schuler

      Hello Yeshwanth,

      I got exactly the same issue. Did you get it resolved yet?

      Many thanks in advance

      Frank

      Author's profile photo Sumit Singh
      Sumit Singh

      Hi Everyone ,

      I need help , after create the store procedure its not visible  In the RTT,

      (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”.)

      After refresh also it not visible in the RTT ,

      Please help me on this .

      Thanks,
      Sumit

      Author's profile photo Stefano Cataldo
      Stefano Cataldo

      Hi Everyone ,

      I just try it again and now it worked fine to create the Procedere and test in the run-time-tool. Therefore I change this entry (I posted a question) and give only two hints, in case you run in the same issues:

      • Please comment out the line  --READS SQL DATA AS, in my case I have to do this manually, because this line was copy wrong.
      • In my case I have to replace every inverted commas after copying the text.

      Regards, Stefano